ทำความสะอาดข้อมูลใน Excel และ Google Sheets: ลบข้อมูลซ้ำ แก้วันที่ แยกคอลัมน์
ข้อมูลที่ได้มาจากแบบฟอร์ม ระบบหลังบ้าน หรือเพื่อนร่วมงานหลายคน แทบไม่เคยสะอาดตั้งแต่แรก มีชื่อซ้ำ เว้นวรรคเกินท้ายคำ วันที่เขียนคนละแบบ ตัวเลขที่โปรแกรมมองเป็นตัวหนังสือ พอเอาไปสรุปผลหรือทำกราฟ ก็ได้ตัวเลขเพี้ยนโดยไม่รู้ตัว
การทำความสะอาดข้อมูล (Data Cleaning) จึงเป็นงานที่ควรทำก่อนเริ่มวิเคราะห์เสมอ บทความนี้รวมขั้นตอนที่ใช้ได้จริงทั้งใน Excel และ Google Sheets เรียงตามลำดับที่ควรทำ โดยเน้นการแก้ข้อมูลที่เลอะ ไม่ใช่การเขียนสูตรคำนวณ
ชื่อเมนูอาจต่างกันตามเวอร์ชันและภาษาของโปรแกรม ถ้าหาไม่เจอ ให้ค้นหาชื่อฟังก์ชันหรือชื่อคำสั่งในช่องค้นหาของโปรแกรม หรือค้นชื่อฟังก์ชันเป็นภาษาอังกฤษ ซึ่งมักตรงกันทุกเวอร์ชัน
1. ทำสำเนาไฟล์ก่อนเริ่มแก้
กฎข้อแรกของการล้างข้อมูลคืออย่าแก้บนไฟล์ต้นฉบับ ให้ทำสำเนาไว้ก่อน หรือเก็บชีตดิบไว้ในแท็บหนึ่ง แล้วทำงานในอีกแท็บ ถ้าลบหรือแปลงผิดจะย้อนกลับไปดูของเดิมได้
- บันทึกไฟล์ใหม่ด้วยชื่อที่มีคำว่า “ล้างแล้ว” หรือวันที่ต่อท้าย
- ถ้าข้อมูลมาจากระบบอื่น ให้เก็บไฟล์ที่ดึงมาไว้โดยไม่แตะต้อง
- จดสั้น ๆ ว่าแก้อะไรไปบ้างในแท็บแยก จะช่วยให้ทำซ้ำได้เมื่อได้ข้อมูลชุดใหม่
ก่อนลงมือ ให้เลื่อนดูข้อมูลทั้งตารางแบบคร่าว ๆ ใช้ตัวกรอง (Filter) ดูค่าที่ไม่ซ้ำในแต่ละคอลัมน์ จะเห็นทันทีว่าคอลัมน์ไหนมีของแปลกปน เช่น คำว่า “ไม่ระบุ” ปนกับตัวเลข หรือชื่อจังหวัดเขียนสะกดต่างกัน
2. ตัดช่องว่างที่เกินมา
ช่องว่างที่มองไม่เห็นเป็นต้นเหตุของปัญหาที่แก้ยากที่สุดอย่างหนึ่ง เช่น “สมชาย” กับ “สมชาย ” ที่มีเว้นวรรคท้าย ดูเหมือนเดิมแต่โปรแกรมมองว่าเป็นคนละค่า ทำให้ลบซ้ำไม่ออกและค้นหาไม่เจอ
- เพิ่มคอลัมน์ว่างข้างคอลัมน์ที่จะล้าง
- ใช้ฟังก์ชัน TRIM อ้างอิงเซลล์ต้นทาง ฟังก์ชันนี้ตัดช่องว่างหน้าท้ายและลดช่องว่างซ้อนกลางข้อความให้เหลือช่องเดียว
- ลากสูตรลงให้ครบทุกแถว
- คัดลอกคอลัมน์ผลลัพธ์ แล้ววางแบบ “เฉพาะค่า” (Paste Values) ทับของเดิม จากนั้นลบคอลัมน์ช่วยทิ้ง
ถ้า TRIM แล้วยังเหมือนมีช่องว่างเหลือ อาจเป็นช่องว่างพิเศษที่มาจากการคัดลอกจากเว็บ ให้ใช้ฟังก์ชัน SUBSTITUTE แทนที่อักขระนั้นด้วยค่าว่าง หรือใช้เครื่องมือค้นหาและแทนที่ (Find and Replace) แล้ววางช่องว่างที่คัดลอกมาลงในช่องค้นหา
3. ทำให้ข้อความเป็นรูปแบบเดียวกัน
ข้อมูลภาษาอังกฤษมักมีปัญหาตัวพิมพ์เล็กใหญ่ เช่น “bangkok” “Bangkok” “BANGKOK” ซึ่งถูกนับเป็นค่าต่างกันในบางงาน
- UPPER แปลงเป็นตัวพิมพ์ใหญ่ทั้งหมด
- LOWER แปลงเป็นตัวพิมพ์เล็กทั้งหมด
- PROPER ทำให้ตัวแรกของแต่ละคำเป็นตัวใหญ่ เหมาะกับชื่อคน
ส่วนข้อมูลภาษาไทย ปัญหาที่พบบ่อยคือการสะกดไม่ตรงกัน เช่น “กรุงเทพ” “กรุงเทพฯ” “กทม.” ถ้ามีไม่มาก ใช้ค้นหาและแทนที่ให้เป็นคำเดียว ถ้ามีมาก ให้ทำตารางอ้างอิงคำที่ถูกต้อง แล้วใช้ฟังก์ชัน VLOOKUP หรือ XLOOKUP แปลงคำทั้งหมดให้ตรงกับตาราง
ที่สำคัญคือกำหนดมาตรฐานก่อนว่าจะเขียนแบบไหน แล้วทำให้เหมือนกันทั้งชีต ไม่ว่าจะเลือกแบบใด
4. ลบข้อมูลซ้ำอย่างปลอดภัย
ข้อมูลซ้ำทำให้ยอดรวมเกินจริง และทำให้นับจำนวนคนหรือรายการผิด ทั้ง Excel และ Google Sheets มีเครื่องมือลบข้อมูลซ้ำ (Remove Duplicates) อยู่ในเมนู “ข้อมูล” (Data)
- ลบช่องว่างเกินให้เรียบร้อยก่อน ไม่อย่างนั้นข้อมูลที่ซ้ำจริงอาจไม่ถูกจับ
- เลือกช่วงข้อมูลทั้งตาราง รวมแถวหัวตาราง
- เปิดเครื่องมือลบข้อมูลซ้ำ แล้วเลือกว่าจะดูซ้ำจากคอลัมน์ไหน เช่น ซ้ำเมื่ออีเมลเหมือนกัน หรือซ้ำเมื่อทุกคอลัมน์เหมือนกันหมด
- ตรวจผลที่โปรแกรมรายงานว่าลบไปกี่แถว แล้วเทียบกับที่คาดไว้
ข้อควรระวังคือการเลือกคอลัมน์ที่ใช้ตัดสินว่าซ้ำ ถ้าเลือกน้อยเกินไป เช่นดูแค่ชื่อ อาจลบคนละคนที่ชื่อเหมือนกันทิ้งไป ถ้าไม่แน่ใจ ให้ไฮไลต์ข้อมูลซ้ำก่อนด้วยการจัดรูปแบบตามเงื่อนไข (Conditional Formatting) เลือกกฎ “ค่าที่ซ้ำกัน” แล้วตรวจด้วยตาก่อนลบจริง
5. แก้รูปแบบวันที่ที่ไม่ตรงกัน
วันที่เป็นข้อมูลที่ยุ่งยากที่สุด เพราะบางเซลล์เป็นวันที่จริง บางเซลล์เป็นข้อความที่แค่หน้าตาเหมือนวันที่ และบางเซลล์สลับวันกับเดือนจากการตั้งค่าภูมิภาคที่ต่างกัน นอกจากนั้นยังมีเรื่องปี พ.ศ. กับ ค.ศ. ที่ปะปนกัน
- ทดสอบว่าเป็นวันที่จริงหรือไม่: เปลี่ยนรูปแบบเซลล์เป็นตัวเลข ถ้าได้เลขลำดับ แปลว่าโปรแกรมมองเป็นวันที่ ถ้ายังเป็นข้อความเดิม แปลว่าเป็นข้อความ
- แปลงข้อความเป็นวันที่: ใช้ฟังก์ชัน DATEVALUE หรือใช้เครื่องมือแยกข้อความเป็นคอลัมน์ (Text to Columns) แล้วเลือกชนิดคอลัมน์เป็นวันที่
- ถ้าสลับวันกับเดือน: แยกส่วนด้วยฟังก์ชัน LEFT, MID, RIGHT แล้วประกอบใหม่ด้วยฟังก์ชัน DATE
- ปี พ.ศ. กับ ค.ศ.: ตรวจดูปีที่ดูผิดปกติ เช่น ปีที่ห่างจากปัจจุบันมาก แล้วเลือกใช้ระบบเดียวทั้งชีต
หลังแปลงแล้ว ให้จัดรูปแบบเซลล์ (Format Cells) ให้แสดงวันที่แบบเดียวกันทั้งคอลัมน์ ควรเลือกรูปแบบที่ไม่กำกวม เช่น วัน เดือน ปี โดยเขียนชื่อเดือนเป็นคำ จะไม่สับสนระหว่างวันกับเดือน
6. แก้ตัวเลขที่เป็นตัวหนังสือ
สัญญาณที่บอกว่าตัวเลขกลายเป็นข้อความคือ ค่าชิดซ้ายในเซลล์ มีสามเหลี่ยมสีเขียวมุมเซลล์ใน Excel หรือผลรวมออกมาเป็นศูนย์ทั้งที่มีตัวเลขเต็มคอลัมน์ สาเหตุมักมาจากการคัดลอกจากเว็บ มีเครื่องหมายจุลภาคหรือหน่วยติดมา เช่น “1,200 บาท”
- ลบหน่วยหรือสัญลักษณ์ที่ไม่ใช่ตัวเลขด้วยการค้นหาและแทนที่
- ใช้ฟังก์ชัน VALUE แปลงข้อความเป็นตัวเลข หรือคูณด้วย 1 ในคอลัมน์ช่วยก็ได้ผลเหมือนกัน
- วางกลับแบบเฉพาะค่า แล้วตั้งรูปแบบเซลล์เป็นตัวเลข
อีกกรณีคือเลขที่ขึ้นต้นด้วยศูนย์ เช่น เบอร์โทรศัพท์ที่ศูนย์ตัวหน้าหายไป เพราะโปรแกรมมองเป็นตัวเลข ให้ตั้งรูปแบบเซลล์เป็นข้อความก่อนพิมพ์หรือวางข้อมูล เบอร์โทรศัพท์และรหัสไปรษณีย์ควรเป็นข้อความเสมอ เพราะเราไม่เคยเอามาบวกลบกัน
7. แยกและรวมคอลัมน์
ข้อมูลที่อัดมาในเซลล์เดียว เช่น “สมชาย ใจดี” หรือ “ถนนสุขุมวิท, กรุงเทพ” ควรแยกเป็นคอลัมน์ย่อยเพื่อให้กรองและนับได้ง่าย
- Text to Columns (ข้อความเป็นคอลัมน์): ใน Excel อยู่ในเมนูข้อมูล เลือกตัวคั่น เช่น ช่องว่างหรือจุลภาค แล้วโปรแกรมแยกให้ ส่วนใน Google Sheets ใช้เมนู ข้อมูล แล้วเลือกแยกข้อความเป็นคอลัมน์
- ฟังก์ชัน SPLIT: ใน Google Sheets แยกข้อความตามตัวคั่นได้ด้วยสูตร ส่วน Excel รุ่นใหม่มีฟังก์ชัน TEXTSPLIT ที่ทำงานคล้ายกัน
- การรวมคอลัมน์: ใช้เครื่องหมาย & หรือฟังก์ชัน CONCAT และ TEXTJOIN ต่อข้อความจากหลายเซลล์เข้าด้วยกัน
ก่อนแยก ให้แน่ใจว่ามีคอลัมน์ว่างด้านขวาเพียงพอ ไม่อย่างนั้นข้อมูลที่แยกจะเขียนทับคอลัมน์เดิม
8. ตรวจความถูกต้องและป้องกันข้อมูลเลอะตั้งแต่ต้น
ถ้าข้อมูลมาจากคนหลายคนกรอก วิธีที่ดีที่สุดคือป้องกันไม่ให้ข้อมูลเลอะตั้งแต่แรก ด้วยเครื่องมือตรวจสอบข้อมูล (Data Validation)
- รายการแบบเลือก (Drop-down): กำหนดตัวเลือกที่ยอมรับ เช่น สถานะ “รอดำเนินการ” “เสร็จแล้ว” คนกรอกเลือกได้เท่านั้น
- ช่วงตัวเลข: จำกัดให้กรอกตัวเลขในช่วงที่สมเหตุสมผล เช่น อายุหรือจำนวนชิ้น
- วันที่: จำกัดให้กรอกเฉพาะวันที่ในช่วงที่ต้องการ
- ข้อความแจ้งเตือน: ใส่คำอธิบายสั้น ๆ ว่ากรอกอย่างไรจึงถูก
สำหรับข้อมูลที่มีอยู่แล้ว ใช้การจัดรูปแบบตามเงื่อนไขไฮไลต์เซลล์ที่ผิดปกติ เช่น เซลล์ว่างที่ไม่ควรว่าง ตัวเลขที่เกินช่วง หรือสูตรตรวจเองด้วยฟังก์ชัน IF และ COUNTIF เพื่อนับว่ามีค่าที่ไม่อยู่ในรายการอ้างอิงกี่แถว
| ปัญหา | เครื่องมือที่ช่วยได้ |
|---|---|
| ช่องว่างเกิน | TRIM, CLEAN |
| ตัวพิมพ์เล็กใหญ่ไม่ตรงกัน | UPPER, LOWER, PROPER |
| แถวซ้ำ | Remove Duplicates, Conditional Formatting |
| วันที่ไม่ตรงกัน | DATEVALUE, DATE, Text to Columns |
| ตัวเลขเป็นข้อความ | VALUE, Find and Replace |
| ข้อมูลผิดช่วง | Data Validation, IF, COUNTIF |
สรุป
ข้อมูลที่สะอาดทำให้สูตร กราฟ และตารางสรุปทำงานถูกต้อง ลำดับที่แนะนำคือ ทำสำเนา ตัดช่องว่าง ทำข้อความให้เป็นรูปแบบเดียว ลบข้อมูลซ้ำ แก้วันที่และตัวเลข แยกหรือรวมคอลัมน์ แล้วตั้งกฎตรวจข้อมูลไว้กันปัญหาในอนาคต
ขั้นตอนถัดไปที่ทำได้เลย: เปิดไฟล์ข้อมูลที่ใช้บ่อย ลองกรองดูค่าที่ไม่ซ้ำในแต่ละคอลัมน์ แล้วแก้ปัญหาที่เห็นหนึ่งอย่างตามบทความนี้ จากนั้นเพิ่มกฎตรวจข้อมูลในคอลัมน์ที่คนมักกรอกผิด
คำถามที่พบบ่อย
- ใช้ Power Query หรือเครื่องมือแปลงข้อมูลได้ไหม: ได้ เหมาะกับข้อมูลที่ต้องล้างซ้ำทุกเดือน เพราะบันทึกขั้นตอนไว้รันซ้ำได้ แต่ชื่อเมนูต่างกันตามเวอร์ชัน
- ลบแล้วกู้คืนได้ไหม: ใช้ปุ่มเลิกทำได้ทันที ถ้าปิดไฟล์ไปแล้ว ให้เปิดประวัติเวอร์ชันของ Google Sheets หรือไฟล์สำรองที่ทำไว้
- ข้อมูลเยอะมากทำไมช้า: การใช้สูตรหลายหมื่นแถวทำให้ช้า ให้วางแบบเฉพาะค่าหลังแปลงเสร็จเพื่อลดภาระการคำนวณ
