Excel Formulas Academy · บทเรียน

แยกและทำความสะอาดข้อมูลยุ่งเหยิง

ใช้ SPLIT และ CLEAN เพื่อแยกส่วนและจัดระเบียบข้อความที่นำเข้า

แยกและทำความสะอาดข้อมูลยุ่งเหยิง เป็นบทเรียน Excel Formulas Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน Excel Formulas Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน

แยกเซลล์เดียวออกเป็นหลายเซลล์

ข้อมูลนำเข้าแทบไม่เคยเป็นระเบียบ เซลล์เดียวอาจมี John,Smith,Sales ทั้งที่คุณต้องการสามคอลัมน์แยกกัน

ฟังก์ชัน SPLIT ใน Google ชีตจะแยกสตริงตรงตัวคั่น แล้วกระจายส่วนต่าง ๆ ไปยังเซลล์ที่อยู่ติดกัน

ในบทเรียนสุดท้ายนี้ คุณจะใช้ SPLIT และ CLEAN ร่วมกับทักษะด้านนิพจน์ทั่วไปที่เพิ่งเรียนรู้ เพื่อจัดระเบียบข้อความที่ยุ่งเหยิงให้เรียบร้อยอย่างสมบูรณ์

ไวยากรณ์ของ SPLIT

SPLIT(text, delimiter) จะแบ่งข้อความทุกครั้งที่พบตัวคั่น และส่งออกแต่ละส่วนไปยังเซลล์ของตนเองทางด้านขวา

สูตรด้านล่างจะแยก A2 ด้วยเครื่องหมายจุลภาค John,Smith,Sales จึงกระจายไปเป็นสามเซลล์ ได้แก่ John, Smith และ Sales

=SPLIT(A2, ",")

แยกด้วยตัวคั่นหลายแบบ

โดยค่าเริ่มต้น อักขระแต่ละตัวในสตริงตัวคั่นจะถูกมองเป็นตัวคั่นแยกกัน ดังนั้น SPLIT(A2, ",;") จะแยกด้วยทั้งเครื่องหมายจุลภาคและอัฒภาค

สูตรด้านล่างจะแยกด้วยเครื่องหมายจุลภาคหรือช่องว่างอย่างใดอย่างหนึ่ง ซึ่งมีประโยชน์เมื่อตัวคั่นในแต่ละแถวไม่สม่ำเสมอกัน

=SPLIT(A2, ", ")

เก็บส่วนว่างไว้

SPLIT มีอาร์กิวเมนต์เพิ่มเติม อาร์กิวเมนต์ที่สามควบคุมว่าอักขระตัวคั่นแต่ละตัวจะแยกข้อมูลแยกกันหรือไม่ ส่วนอาร์กิวเมนต์ที่สี่ควบคุมว่าจะลบผลลัพธ์ที่ว่างเปล่าหรือไม่

โดยค่าเริ่มต้น ส่วนว่างจะถูกตัดออก ซึ่งอาจทำให้คอลัมน์เลื่อน สูตรด้านล่างจะเก็บส่วนว่างไว้โดยส่ง FALSE เป็นอาร์กิวเมนต์สุดท้าย เพื่อรักษาการจัดแนวคอลัมน์สำหรับ A,,C

=SPLIT(A2, ",", TRUE, FALSE)

ทำความสะอาดอักขระที่มองไม่เห็นด้วย CLEAN

ข้อความที่คัดลอกจากไฟล์ PDF หรือหน้าเว็บมักมีอักขระควบคุมที่มองไม่เห็นติดมาด้วย ซึ่งทำให้สูตรและการค้นหาทำงานผิดพลาด

ฟังก์ชัน CLEAN จะลบอักขระที่ไม่สามารถพิมพ์ได้ส่วนใหญ่ออกจากสตริง สูตรด้านล่างจะคืนค่า A2 โดยลบสิ่งแปลกปลอมเหล่านั้นออก ทำให้ค่าที่ดูเหมือนเท่ากันแต่ไม่เคยตรงกันสามารถทำงานได้ตามปกติในที่สุด

=CLEAN(A2)

CLEAN ร่วมกับ TRIM และ SPLIT

CLEAN จัดการอักขระควบคุมได้ แต่ไม่จัดการช่องว่างส่วนเกิน ให้ใช้ร่วมกับ TRIM ซึ่งจะลบช่องว่างด้านหน้า ด้านหลัง และช่องว่างที่ซ้ำกัน จากนั้นจึงแยกส่วน

สูตรด้านล่างจะทำความสะอาดอักขระควบคุม ตัดช่องว่าง และแยกผลลัพธ์ด้วยเครื่องหมายจุลภาคในนิพจน์เดียว โปรดอ่านจากด้านในออกมาด้านนอก

=SPLIT(TRIM(CLEAN(A2)), ",")

ตัวอย่างพร้อมวิธีทำ: จัดระเบียบรายการชื่อที่นำเข้า

คอลัมน์ A มีรายการยุ่งเหยิง เช่น Smith , John ที่มีช่องว่างเกินมา คุณต้องการชื่อจริงและนามสกุลที่สะอาดในเซลล์แยกกัน

สูตรด้านล่างจะตัดช่องว่างและแยกด้วยเครื่องหมายจุลภาค หากต้องการทำความสะอาดแต่ละส่วนด้วย คุณสามารถครอบผลลัพธ์แต่ละรายการด้วย TRIM หลังการแยกได้ แต่โดยทั่วไปการทำความสะอาดก่อนก็เพียงพอ

=ARRAYFORMULA(TRIM(SPLIT(A2, ",")))

เมื่อ SPLIT ไม่เพียงพอ ให้ใช้ REGEX

SPLIT ต้องใช้ตัวคั่นแบบตายตัว เมื่อมีตัวคั่นหลายรูปแบบ ให้ทำให้เป็นมาตรฐานด้วย REGEXREPLACE ก่อน แล้วจึงแยกด้วยตัวคั่นมาตรฐาน

สูตรด้านล่างจะแทนที่ลำดับช่องว่าง เครื่องหมายจุลภาค หรืออัฒภาคที่ต่อเนื่องกันด้วยเครื่องหมายไปป์เพียงหนึ่งตัว แล้วจึงแยกด้วยเครื่องหมายไปป์ วิธีนี้จัดการข้อมูลเข้าที่ไม่สม่ำเสมออย่างมากได้อย่างเป็นระเบียบ

=SPLIT(REGEXREPLACE(A2, "[ ,;]+", "|"), "|")

ตัวอย่างพร้อมวิธีทำ: ดึงข้อมูลแล้วจึงแยกส่วน

บางครั้งคุณต้องการเพียงบางส่วนของเซลล์ แล้วจึงแยกส่วนนั้น จาก Tags: red, blue, green คุณต้องการเฉพาะสีต่าง ๆ เป็นเซลล์แยกกัน

สูตรด้านล่างจะดึงทุกอย่างหลังเครื่องหมายโคลอนก่อน แล้วจึงแยกด้วยเครื่องหมายจุลภาค การผสานเครื่องมือนิพจน์ทั่วไปกับการแยกส่วนจะแสดงให้เห็นพลังที่แท้จริงของวิธีนี้

=SPLIT(REGEXEXTRACT(A2, ": (.+)$"), ", ")

ขั้นตอนการทำความสะอาดที่ใช้ซ้ำได้

สำหรับข้อมูลนำเข้าส่วนใหญ่ที่ยุ่งเหยิง ลำดับนี้ใช้ได้ดี:

CLEAN เพื่อลบอักขระควบคุม
TRIM เพื่อแก้ไขช่องว่าง
REGEXREPLACE เพื่อทำให้ตัวคั่นเป็นมาตรฐานหรือลบสิ่งเกินจำเป็น
SPLIT เพื่อแยกข้อมูลออกเป็นคอลัมน์

สูตรด้านล่างจะใช้ทั้งสี่ขั้นตอนตามลำดับกับเซลล์เดียว

=SPLIT(REGEXREPLACE(TRIM(CLEAN(A2)), "[;, ]+", "|"), "|")

แยกเป็นแถวแทนคอลัมน์

โดยค่าเริ่มต้น SPLIT จะกระจายส่วนต่าง ๆ ไปตามคอลัมน์ บางครั้งคุณอาจต้องการรายการแนวตั้งแทน โดยมีหนึ่งค่าต่อแถวและพร้อมสำหรับการกรองหรือนับ

ครอบ SPLIT ด้วย TRANSPOSE เพื่อพลิกแถวที่กระจายออกมาให้เป็นคอลัมน์ สูตรด้านล่างจะแยก A2 ด้วยเครื่องหมายจุลภาคแล้วเรียงผลลัพธ์ในแนวตั้ง เหมาะอย่างยิ่งสำหรับเปลี่ยนรายการที่คั่นด้วยจุลภาคให้เป็นคอลัมน์สำหรับการค้นหาที่เป็นระเบียบ

=TRANSPOSE(SPLIT(A2, ","))

ตรวจสอบความเข้าใจ

ตรวจสอบความเข้าใจเกี่ยวกับการแยกส่วนและการทำความสะอาด

ทบทวน: แยก ทำความสะอาด และจัดระเบียบ

คุณจบหลักสูตรด้วยการผสานเครื่องมือแยกส่วนและทำความสะอาดใน Google ชีต:

SPLIT(text, delimiter) จะแยกเซลล์ออกเป็นคอลัมน์ที่กระจายออกมา
อาร์กิวเมนต์เพิ่มเติมควบคุมตัวคั่นหลายอักขระและส่วนว่าง
CLEAN จะลบอักขระควบคุมที่มองไม่เห็น ส่วน TRIM จะแก้ไขช่องว่าง
ใช้ REGEXREPLACE เพื่อทำให้ตัวคั่นที่ยุ่งเหยิงเป็นมาตรฐานก่อนการแยกส่วน
เชื่อม CLEAN, TRIM, REGEXREPLACE และ SPLIT เพื่อทำความสะอาดตามขั้นตอนที่ใช้ซ้ำได้

ตอนนี้คุณมีชุดเครื่องมือนิพจน์ทั่วไปครบถ้วนแล้ว ได้แก่ จับคู่ ดึงข้อมูล แทนที่ แยกส่วน และทำความสะอาด

คำถามที่พบบ่อย

บทเรียน “แยกและทำความสะอาดข้อมูลยุ่งเหยิง” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “แยกและทำความสะอาดข้อมูลยุ่งเหยิง” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส Excel Formulas Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส Excel Formulas Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “แยกและทำความสะอาดข้อมูลยุ่งเหยิง”

ใช้ SPLIT และ CLEAN เพื่อแยกส่วนและจัดระเบียบข้อความที่นำเข้า คุณปฏิบัติ Excel Formulas Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน Excel Formulas Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน Excel Formulas Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน

บทเรียน “แยกและทำความสะอาดข้อมูลยุ่งเหยิง” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน Excel Formulas Academy นี้ได้ไหม

ได้ บทเรียน Excel Formulas Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ Excel Formulas Academy