บทความที่แล้ว แอดอธิบายปัญหา outliers และการทำ outlier detection ไปแล้ว วันนี้เราจะมาลองดูการทำความสะอาด missing values ใน Excel กันบ้าง ดาวน์โหลดไฟล์ตัวอย่างได้ที่นี่
ฟังชั่นหลักที่ data analyst ใช้นับจำนวน/ ตรวจหา missing values ใน Excel คือ COUNTBLANK() และ ISBLANK() ตามลำดับ สังเกตในสูตรคำว่า “blank” หมายถึง cell ว่างใน worksheet นั้นๆ
ตรวจสอบ Missing Value
เปิดไฟล์ตัวอย่างขึ้นมา ข้อมูลที่มี missing values จะอยู่ที่ cell A3:E12 เราสามารถใช้ฟังชั่น COUNTBLANK() เพื่อนับจำนวน missing values ในแต่ละคอลัมน์ ลองดูสูตรที่แอดเขียนไว้ใน cell A15:E15
=COUNTBLANK(A3:A12)
=COUNTBLANK(B3:B12)
=COUNTBLANK(C3:C12)
...รู้จักกับค่า Boolean
ถัดมา ลองดูที่คอลัมน์ G:K แอดใช้ฟังชั่น ISBLANK() เพื่อตรวจสอบ cell ที่เราต้องการว่า blank หรือเปล่า? โดย ISBLANK() จะส่งค่า TRUE ถ้าเจอ blank cell หรือ FALSE ถ้า cell นั้นมีข้อมูล (หรือ non-blank cell)
=ISBLANK(A3)TRUE/ FALSE ในโปรแกรม Excel (รวมถึงภาษาคอมพิวเตอร์อื่นๆ เช่น Python R) สามารถเปลี่ยนเป็น numeric value ได้ โดยปกติ TRUE จะมีค่าเท่ากับ 1 และ FALSE มีค่าเท่ากับ 0 เราเรียกตัวแปรประเภทนี้ว่า “Boolean“
วิธีเปลี่ยน TRUE เป็น 1 และ FALSE เป็น 0 ในโปรแกรม Excel แค่คูณหนึ่งเข้าไปที่ค่า boolean นั้นๆ เช่น TRUE*1 = 1 หรือ FALSE*1 = 0 ลองพิมพ์สูตรด้านล่างใน cell A20 แล้วกด CTRL+SHIFT+ENTER พร้อมกันเพื่อผูกสูตรแบบ array formula
## CTRL + SHIFT + ENTER to run this formula
=SUM(ISBLANK(A3:A12) * 1)แทนที่ Blank ด้วยค่าเฉลี่ย
ขั้นตอนสุดท้ายคือการแทนที่ missing values (blank cells) ด้วยค่าเฉลี่ยของคอลัมน์นั้นๆ นักสถิติเรียกเทคนิคนี้ว่า “Mean Imputation” ในทางปฏิบัติ เราสามารถแทนที่ missing values ได้หลายแบบ เช่น mean, median, mode หรือ conditional mean ขึ้นอยู่กับประเภทของข้อมูลและรูปแบบการกระจายตัว
พิมพ์สูตร IF() ด้านล่างใน cell M3:Q12 เพื่อทำ mean imputation ด้วยค่าเฉลี่ยที่เราคำนวณไว้ใน cell A18:E18 หรือจะลองทำ median imputation ด้วยฟังชั่น MEDIAN() ก็ได้ 😛
=IF(ISBLANK(A3), column_mean, A3)สรุป
วิธีการทำความสะอาด missing values ง่ายๆด้วยโปรแกรม Excel
- COUNTBLANK() เพื่อนับจำนวน missing values ใน range ที่เราต้องการ
- ISBLANK() เพื่อระบุตำแหน่งของ missing values
- Boolean: TRUE = 1 และ FALSE = 0
- SUM() + (ISBLANK() * 1) เพื่อสร้าง array formula ได้ผลเหมือนกับ COUNTBLANK()
- แทนที่ missing values ด้วยค่าเฉลี่ย เขียนสูตร IF() ง่ายๆ
อยากเรียนเทคนิค Excel for Data Analyst อะไรอีก คอมเมนต์บอกเราได้ในบทความนี้เลย ขอบคุณที่ติดตามอ่านครับ // แอดทอย
