Text Data Cleaning

อ.ดร. สมศักดิ์ จันทร์เอม

วิทยาลัยนานาชาตินวัตกรรมดิจิทัล มหาวิทยาลัยเชียงใหม่

26 มีนาคม 2569

เหตุการณ์สมมุติ

คุณเป็น นักวิเคราะห์ข้อมูล (Data Analyst) ของบริษัทแห่งหนึ่ง โดยคุณได้รับชุดข้อมูลพนักงาน (Employee Dataset) ซึ่งมีปัญหาหลายอย่าง เช่น

  • ข้อมูลไม่สม่ำเสมอ (inconsistencies)
  • มีช่องว่างเกิน (extra spaces)
  • การใช้ตัวพิมพ์ใหญ่/เล็กไม่ตรงกัน (mixed cases)
  • ค่าข้อมูลบางส่วนอยู่ในรูปแบบที่ไม่ถูกต้อง (improperly formatted values)

หน้าที่ของคุณคือการ ทำความสะอาด (Clean) และ แปลงข้อมูล (Transform) โดยใช้ฟังก์ชันต่าง ๆ ใน Excel เพื่อเตรียมข้อมูลให้พร้อมสำหรับการสร้างรายงาน (Reporting)

ข้อมูลพนักงานดิบ (Raw Employee Data)

Download from GOOGLE DRIVE

Employee_ID Full_Name Department_Code
1001 Mr. John Smith. IT01
1002 Ms. SARAH KIM mk02
1003 Dr. Somchai Wong hr03
1004 MRS. Anne GREEN Fn04
1005 ms. lena wong. HR03
1006 MR. PETER jackson mk02
1007 Dr. james o’neill it01
1008 Mrs. AMY chen fn04
1009 mr. george WASHINGTON HR03
1010 Ms. Emily Taylor mk02

1. ลบช่องว่างส่วนเกินด้วยฟังก์ชัน TRIM()

ปัญหา (Problem): คอลัมน์ Full_Name มีช่องว่างไม่สม่ำเสมอ ทั้งหน้าคำและระหว่างคำ

งานที่ต้องทำ (Task): สร้างคอลัมน์ Clean_Name ที่ลบช่องว่างส่วนเกินออก

สูตร (Formula):

=TRIM(B2)

ตัวอย่างผลลัพธ์ (Example Output) สำหรับแถว 2: Mr. John Smith.

2. แก้ตัวพิมพ์ใหญ่/เล็กให้ถูกต้องด้วย PROPER()

ปัญหา (Problem): ชื่อพนักงานมีการใช้ตัวพิมพ์ใหญ่/เล็กไม่สม่ำเสมอ

งานที่ต้องทำ (Task): สร้างคอลัมน์ Proper_Name ให้เป็นตัวอักษรขึ้นต้นด้วยพิมพ์ใหญ่เสมอ

สูตร (Formula):

=PROPER(TRIM(B2))

ตัวอย่างผลลัพธ์: Ms. Sarah Kim

3. แยกคำนำหน้าชื่อ (Title) ด้วย TEXTBEFORE()

ปัญหา (Problem): ต้องการแยกคำนำหน้า เช่น Mr., Ms., Dr. ออกจากชื่อเต็ม

งานที่ต้องทำ (Task): สร้างคอลัมน์ Title เพื่อดึงเฉพาะคำนำหน้า

สูตร (ใช้ได้ใน Excel 365):

=TEXTBEFORE(E2, " ")

ตัวอย่างผลลัพธ์: Ms.

4. แยกชื่อจริงและนามสกุลด้วย TEXTSPLIT()

งานที่ต้องทำ (Task): จากคอลัมน์ Proper_Name ให้แยกชื่อจริงและนามสกุลออกเป็นสองคอลัมน์

สูตร (Formula):

=TEXTSPLIT(C2, " ")

จะได้ผลลัพธ์เป็น {First Name | Last Name} เช่น Sarah | Kim

5. สร้างอีเมลบริษัทโดยใช้ LOWER() และ CONCAT() / TEXTJOIN()

ปัญหา (Problem): ต้องการสร้างอีเมลในรูปแบบ first.last@company.com

งานที่ต้องทำ (Task): สร้างคอลัมน์ Email โดยให้เป็นตัวพิมพ์เล็กทั้งหมด

สูตร (Formula):

=LOWER(D2 & "." & E2 & "@company.com")

โดยกำหนดให้ D2 = First Name และ E2 = Last Name

ตัวอย่างผลลัพธ์: sarah.kim@company.com

6. จัดรูปแบบรหัสแผนกและค้นหาชื่อแผนกด้วย UPPER() + XLOOKUP()

ปัญหา (Problem): รหัสแผนก (Department_Code) มีตัวพิมพ์ใหญ่/เล็กปะปนกัน และต้องการแสดงชื่อแผนกเต็ม (Full Department Name)

  1. แปลงรหัสให้เป็นตัวพิมพ์ใหญ่ทั้งหมด
=UPPER(F2)
  1. ค้นหาชื่อแผนกจากรหัสด้วย XLOOKUP

สมมุติตารางอ้างอิงอยู่ในช่วง H2:I5

Code Department
IT01 IT
MK02 Marketing
HR03 Human Resources
FN04 Finance

สูตร (Formula):

=XLOOKUP(G2, H2:H5, I2:I5, "Not Found")

ผลลัพธ์ (Result): Marketing