วิธีจัดการ Missing Value ง่ายๆด้วยโปรแกรม Excel

บทความที่แล้ว แอดอธิบายปัญหา outliers และการทำ outlier detection ไปแล้ว วันนี้เราจะมาลองดูการทำความสะอาด missing values ใน Excel กันบ้าง ดาวน์โหลดไฟล์ตัวอย่างได้ที่นี่

ฟังชั่นหลักที่ data analyst ใช้นับจำนวน/ ตรวจหา missing values ใน Excel คือ COUNTBLANK() และ ISBLANK() ตามลำดับ สังเกตในสูตรคำว่า “blank” หมายถึง cell ว่างใน worksheet นั้นๆ

Excel template สำหรับนับจำนวน missing values ในแต่ละคอลัมน์

ตรวจสอบ Missing Value

เปิดไฟล์ตัวอย่างขึ้นมา ข้อมูลที่มี missing values จะอยู่ที่ cell A3:E12 เราสามารถใช้ฟังชั่น COUNTBLANK() เพื่อนับจำนวน missing values ในแต่ละคอลัมน์ ลองดูสูตรที่แอดเขียนไว้ใน cell A15:E15

=COUNTBLANK(A3:A12)
=COUNTBLANK(B3:B12)
=COUNTBLANK(C3:C12)
...

คอลัมน์ X1, X2, X4 และ X5 มี missing values (blank cells) ที่เราต้องจัดการ i.e. count > 0

รู้จักกับค่า 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)

Array formula จะทำงานได้ถูกต้องต่อเมื่อเรากด CTRL + SHIFT + ENTER พร้อมกันเท่านั้น
สูตร SUM(ISBLANK(range) * 1) ได้ผลลัพธ์เหมือนกับฟังชั่น COUNTBLANK(range) 100%!

แทนที่ 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 อะไรอีก คอมเมนต์บอกเราได้ในบทความนี้เลย ขอบคุณที่ติดตามอ่านครับ // แอดทอย

DataRockie