Excel สำหรับจัดการข้อมูลสุขภาพ
ตัวอย่างใช้ XLOOKUP, Power Query, PivotTable และ Data Validation เพื่อจับคู่ ตรวจสอบ และทำความสะอาดข้อมูลสุขภาพ
วิธีการใช้งานและรูปภาพทั้งหมดมาจาก Microsoft Excel ที่เป็นส่วนหนึ่งของ Microsoft Office 365 หากคุณใช้ Excel รุ่นเก่ากว่านี้ หรือไม่ใช่รุ่น 365 อาจมีหน้าจอโปรแกรมที่แตกต่าง และอาจไม่มีบางฟังก์ชัน
Work-in-progress
ดูไฟล์ตัวอย่างได้ที่ DEMO.XLS [https://1drv.ms/x/s!AnqXyzBKVJbomsxqj2p9mc1oUADNKw?e=qrVcdT](https://1drv.ms/x/s!AnqXyzBKVJbomsxqj2p9mc1oUADNKw?e=qrVcdT)
DEMO2.XLS [https://1drv.ms/x/s!AnqXyzBKVJboms4X861mn9-MhKIuVQ?e=PlSiXB](https://1drv.ms/x/s!AnqXyzBKVJboms4X861mn9-MhKIuVQ?e=PlSiXB)
Workbook
Workbook-Relation-PowerPivot-PowerQuery.xlsx
คำศัพท์
Range หมายถึงการระบุ Cell ที่อาจจะมี 1 หรือหลาย Cell ก็ได้ โดยใช้ A1 Notation เช่น A1:C3
พื้นฐาน
Relative vs Absolute reference
การใช้งาน Formula ใน Excel นั้นจะมีการอ้างถึง Cell เช่น =A1+B2 ซึ่งการอ้างแบบนี้คือการอ้างแบบ Relative โดยเวลาเรา Drag Cell เช่น Cell C2 ลงมาถึง C7 จะเห็นว่า Formula จะเปลี่ยนไปเรื่อยเป็น =A7+B7 เวลาเราอ้างอิงถึง Cell จะมีการอ้างทั้งแบบ Relative reference ดังรูป โดยเวลาเรา Drag cell นั้น Cell ที่อ้างถึงจะเลื่อนตามไปด้วย
ในขณะที่เป็น Absolute reference นั้น เวลาเรา Drag cell ที่ Formula
การจัดความกว้างของ Cell
สามารถจัด Dimension ของ Row และ Column แบบอัติโนมัติได้
Table
การกำหนด Range ให้กลายเป็น Table นั้นมีประโญชน์คือทำให้การกระทำใดๆ ก็ตามที่มองข้อมูลเป็น Row เหมือนเป็นฐานข้อมูลนั้นทำได้ง่ายขึ้น (ลองนึกถึง DataFrame ใน Pandas หรือ SPSS หรือ Stata หรือ Relational Database )
การกำหนด Cell ทีต้องการให้เป็น Table และการเปลี่ยน Table กับไปเป็น Cell ปกติ
Drag cells ที่ต้องการทำให้เป็น Table จากนั้นกด Format as a table จากนั้นตรวจสอบ Range อีกครั้งและดูว่า Cells มี Header หรือไม่ โดยข้อความใน Header นี้ต้องไม่ซ้ำกัน จากนั้นกด Ok
การปรับเปลี่ยนขนาด Table
สามารถทำได้โดยคลิกที่ Cell ในก็ได้ใน Table แล้วเลือก Table tab จาก Ribbon จากนั้นกด Resize table
ปุ่ม Resize ที่อยู่ตรง Table tab
ระบุ Range (โดยการพิมพ์หรือ Drag) ของ Table ใหม่ที่ต้องการ โดยต้อง Overlap กับส่วนเดิม
หรือ Drag ที่บริเวณมุมขวาล่างของ Table เพื่อปรับขนาด
Drag บริเวณเครื่องหมายบวกตรงมุมขวาล่างของ Table
ก็ได้จะ Table ขนาดใหม่ตามที่ต้องการ
Table ขนาดใหม่
Formula ใน Table
เมื่อ Format cells เป็น Table แล้ว การใช้งาน Formula ใน Table จะง่ายขึ้นสำหรับ Formula ที่อ้างอิง Cell อื่นๆ ที่อยู่ใน Row เดียวกัน
โดยแทนที่จะเป็น A1 Notation จะการเป็น [@[Header1]:@[Header2]] แทน ซึ่งจะหมายถึงการอ้างอิงถึงค่าใน Cell ที่มี Header
Formula ใน Table จะเปลี่ยนแปลงรูปแบบไป
เมื่อเราใส่ Formula ในบรรทัดแรกแล้ว เราสามารถจะ Fill in cell ที่เหลือใน Column นั้นได้โดยตรง โดยการ Drag ลงมาหรือกดจาก Auto correct ก็ได้
Formula ที่ Fill in เรียบร้อย
จะเห็นว่า Formula ของทุก Cell นั้นเหมือนกัน เพราะมันอ้างถึง Cell ที่ยังอยู่ใน Row เดียวกัน แค่ต่าง Column กันเท่านั้น
การทำความสะอาดข้อมูลด้วย Excel
ใช้ CONVERT() ในแปลงหน่วยข้อมูล
รองรับหลายหน่วยวัดเช่นความยาวปริมาณน้ำหนักความเร็วพลังงานเป็นต้น
ตัวอย่างการแปลงหน่วย
การใช้ Find and Replace ในการลบอักขระที่ไม่จำเป็นออกจากข้อความ
บางครั้งเราต้องการลบขระบางอย่างออกจากข้อความทั้งหมดอย่างถาวร การใช้ Find and replace เป็นวิธีการนึงที่ดี ซึ่งเหมาะกับการลบอักขระเช่น U+200B Zero-width space เป็นต้น ให้เปิด Find and Replace (กด Ctrl+F) จากนั้น พิมพ์ข้อความที่ต้องการหาและแทนที โดยในกรณีของ Unicode ที่มองไม่เห็นเช่น U+200B แนะนำให้ใช้ เว็บ https://unicode-table.com/en/200B/ แล้วทำการคัดลอกอักขระมาใส่ใน Find and Replace
อักขระพิเศษ และการใช้ Find and replace
การใช้ CLEAN() TRIM() SUBSTITUTE() UNICHAR() HEX2DEC() เพื่อลบช่องว่างและอักขระส่วนเกิน
เวลาเรารับข้อมูลมาจากผู้ใช้เช่น Google Form / Microsoft Form เรามีความจำเป็นต้องทำความสะอาดข้อมูลก่อน โดยเฉพาะข้อมูลที่เป็น String (ข้อความ) เช่น การลบช่องว่างหรืออักขระส่วนเกินออก
โดยเราจะใช้ Formula เหล่านี้ในการจัดการข้อมูลที่เป็น String
TRIM()
เป็นการลบช่องว่างที่อยู่หน้าและหลังข้อความ และลดช่องว่างระหว่างข้อความให้เหลือ 1 ช่องเท่านั้น
CLEAN()
เป็นการลบอักขระ ASCII ที่มีหมายเลข (Decimal) 0-31 ซึ่งเป็นอักขระที่ print ไม่ได้ ออกไปจากข้อมูล
SUBSTITITE()
เป็นการแทนที่ข้อความที่ที่ค้นหาด้วยข้อความใหม่ทุกตำแหน่งของ String นั้นๆ โดยเราจะใช้ Fuction นี้การลบอักขระอื่นๆ ที่ CLEAN() ไม่ได้ลบ โดยหากเราต้องการลบอักขระพิเษศที่ไม่มีบน Keyboard เราสามารถใช้ Unicode Number แทนได้ โดยใช้ UNICHAR() ซึ่งจะแปลง Unicode Number ที่เป็น Decimal เป็นตัวขระนั้นๆ แต่โดยปกติเวลาเราดูตาราง Unnicode มันจะเป็น Hexdecimal ดังนั้นเราสามารถใช้ HEX2DEC() ในการแปลงเลขฐาน 16 เป็น ฐาน 10 ก่อน ได้
เช่น =UNICHAR(HEX2DEC("0042")) คือ B
ที่นี้ในตัวอย่างถ้าเราจะต้องกำจัดช่องว่างส่วนเกิน และอักขระ U+200B Zero-width space เราจะใช้ Formula เช่น =TRIM(SUBSTITUTE(CLEAN(B2),UNICHAR(HEX2DEC("200B")),""))
การใช้ Formula ในการ Clean
การแยกข้อความให้จาก 1 Column เป็นหลาย Columns
บางครั้งข้อมูล เช่น ชื่อ นามสกุล ดันพิพม์มาใน Cell เดียวกันแต่แยกด้วยช่องว่าง เราสามารถแยกเป็น 2 Cells ได้ ได้โดยใช้ TEXTSPLIT() หรือใช้ Ribbon > Data tab > Text-to-column ก็ได้ (แต่ Text-to-column นั้นไม่สามารถใช้กับ Cell ที่ค่าของมันเกิดจากสูตรได้ ต้องทำการคัดลอกแล้ว Paste values ก่อนถึงจะสามารถใช้ได้ ไม่งั้นมันเป็นการพยายามแยกข้อความจากสูตร ไม่ใช่ข้อความจริงๆ) โดยทั้งสองวิธีไม่สามารถใช้กับ Table ได้ (จะเห็นว่าในตัวอย่างไม่ใช่ Table สังเกตุได้จากไม่มี Table Format tab ที่ Ribbon เวลาคลิก)
การแยกข้อความเป็นหลาย Cell โดยใช้ Formula
จะเห็นว่าข้อความที่แยกจะ Spill ไปยัง Column ถัดไป ที่นี้ลองมาดูการแยกโดยใน Text-to-column
การจัดการแถวที่ข้อมูลซ้ำกัน
โดยการลบนั้นระบบจะลบทั้งแถวที่มีข้อมูลเราใช้ตัดสินใจว่าข้อมูลซ้ำกัน โดยเหลือเฉพาะแถวแรกไส้เท่านั้น ดังนั้นหากเราจะ Keep-first หรือ Keep-last data เราจะต้องทำการเรียงข้อมูลก่อน โดยเรียงโดยใช้ Columns ที่สามารถใช้บอกว่าข้อมูลนี้กรอกเข้ามาก่อนหลังได้ เช่น EntryId EntryDate เป็นต้น
เช่น ต้องการ Keep-last ข้อมูลการฉีดวัคซีน เราก็จะเรียงข้อมูล Entry Id จาก มากไปหาน้อย
เรียก Entry Id จากมากไปน้อย
Filter แถวข้อมูล
Filter แถวข้อมูล
จะเห็นว่าบางแถวถูกซ่อนแต่แถวมันยังคงอยู่เห็นได้จากจำนวนแถวข้อมูลที่ยังเท่าเดิม
หลังการ Filter แถวข้อมูล จะเห็นว่าข้อมูลยังคงอยู่ 7 แถว เหมือนเดิม
ลบแถวข้อมูล
แนะนำว่าเราอาจ Format สีพื้นหลังเพื่อแสดงให้เห็นแถวที่ Duplicate ก่อน เพื่อที่เราจะได้บอกพอได้ว่าแถวไหนบ้างที่จะถูกลบแถวที่ซ้ำออกไป โดยกดที่ Ribbon > Home > Conditional Formating
Format สีพื้นหลัง
จากนั้นเราค่อยทำการลบข้อมูลออกจริง โดยไปที่ Ribbon > Data > Remove duplicates จากนั้นเลือกเฉพาะ Column ที่จะพิจารณาว่ามีข้อมูลซ้ำ อย่างในกรณีนี้คือ SSO
เลือก Columns
ข้อมูลลดเหลือ 5 แถว
การ Validate ข้อมูล
บางครั้งเราต้องจำกัดการกรอกและ Validate ข้อมูลที่ได้รับมาก่อน ซึ่งใน Excel ก็มีเครื่องมือที่จะช่วย
การจำกัดข้อมูลที่สามารถกรอกได้
เลือก Cell ที่ต้องการแล้วไปที Ribbon > Data > Data Validation จากนั้น เลือก Validation criteria เป็น Allow list จากนั้นกำหนด Source โดยสามารถพิมพ์ Entry เว้นด้วย Comma หรือ จะเลือก Range ของข้อมูลทมี่สามารถใส่ได้ก็ได้
จากนั้นสามารถกำหนด Input message ที่ต้องการให้แสดงเวลาเลือก Cell และเวลากรอกข้อมูลผิดได้ โดยสามารถเลือก Action ได้ 3 แบบ คือ Stop คือต้องแก้ข้อมูลให้ถูกเท่านั้น Warning คือเตือนสามารถยกเลิกหรือยืนยันไปต่อได้ Information คือแจ้งให้ทราบเท่านั้น
ตั้งค่าข้อความเวลาเลือก Cell
ตั้งค่าข้อความเวลาข้อมูลไม่เป็นไปตาม Validation criteria
เวลาข้อมูลผิดพลาดจะขึ้นข้อความแบบนี้
เวลาเลือก Stop
เวลาเลือก Warning
เรายังสามารถให้ Excel แสดงข้อมูลที่ผิดพลาดได้ ซึ่งมีประัโยชน์เวลาเราได้ข้อมูลมาแล้วมาใส่ Data Validation ทีหลัง Ribbon > Data > Circle Invalidated Data
นอกจากวิธีด้านบนนี้แ้ลวเรายังสามารถใช้ Conditional Formatting ในการช่วย Validate ข้อมูลได้ด้วย โดยไปที่ Ribbon > Home > Conditional Formatting แล้วเลือก Pre-defined rule หรือเลือก Manage rules เพื่อจัดการและกำหนด Rule เอง
อย่างในตัวอย่างผมเลือก Cell ใด Cell หนึ่งใน Column A จากนั้นใช้ Formula เดียวกันกับ Data validation
จากนั้นเราต้องการ Copy Formatting ไปยัง Cell อื่นๆ ด้วย ให้เลือก Cell ที่มี Format เช่น A9 จากนั้นไปที่ Ribbon > Home > Format painter แล้วลาก Cell ที่ต้องการ
ลาก Cell ที่ต้องการ Apply
และหากเราไปดูที่ Formatting rules จะเห็นว่ามันเปลี่ยนไป
Applied to ที่เปลี่ยนไป
การใช้ COnditional formating นั้นนอกจากจะทำให้สามารถ Eyeballing ได้แล้วยังทำให้ใช้ Filter by formattinh ได้ด้วย
Filter โดยใช้ Format
ข้อมูลประเภท DATE
แนะนำว่าก่อนการบันทึกข้อมูลปะรเภท Date ควรตั้ง Format และ Data Validation ให้ถุกต้อง เพื่อไม่ให้ต้องมาตามแก้ไข
โดยการกรอก Date แนะนำไปที่ Ribbon > Number > กดลูกศรเล็กๆ Number Format แล้วเลือก Date แล้วเลือก Regional setting ที่ต้องการ โดยถ้าจะใช้ พศ ให้เลือก Thai จากนั้น
- ถ้าจะ Input ด้วย พศ ให้ tick input date ....
- ถ้าจะ Input ด้วย คซ ให้ untick input date ...
จากนั้น Formatting แนะนำให้เลือกแบบที่แสดงเดือนเต็มๆ ป้องกันการสัลบ เดือน/วัน หรือวัน/เดือน
Format วันที่
จากนั้นก็ไปตั้ง Data Validation ให้ Date Range อยู่ในช่วงที่ Make sense
Formula
การเว้นบรรทัดในช่อง Formula
เมื่อต้องพิมพ์ Formula ที่ยาวหรือซับซ้อน เช่น การใช้ IF() ซ้อนกันหลายครั้ง
กด ALT+ENTER เพื่อเว้นบรรทัด จะช่วยให้ Formula อ่านได้ง่ายมากขึ้น 
Formula watch window
ใช้้สำหรับการ Watch ค่าใน Cell ที่เราสนใจว่ามีค่าเปลี่ยนแปลงไปอย่างไร ซึ่งมีประโยขน์ถ้าเราจำเป็นต้องดูค่าจาก Cell หลายจุด หลาย Sheet พร้อมกัน
ใน Formula tab เลือก Watch window จะแสดงหน้าต่าง Watch window ขึ้นมา จากนั้นให้กด Add watch แล้วเลือก Cell ที่ต้องการ Watch สำหรับการยกเลิกการ Monitor ให้กด Delete watch
การเพิ่ม Cell ที่ต้องการ Watch
หน้าจอ Watch window
จากนั้นพอเราลองเปลี่ยนข้อมูลดู จะเห็นว่า Cell ซึ่งคำนวนค่าเฉลี่ย BMI ของทุกคน จะมีค่าเปลี่ยนแปลงไปด้วย ทำให้เราไม่ต้องคอยเลื่อนหน้าจอ Sheet ไปดู Cell นั้นๆ เอง
หน้าจอ Watch window ที่แสดงค่าใน Cell ที่เปลี่ยนแปลงไป
การตรวจการทำงานของ Formula
Trace แหล่งข้อมูลและการส่งข้อมูลของ Formula
เราสามารถตรวจสอบการทำงานของ Formula ได้ โดยกดที่ Ribbon > Formula > Trace Precedents กับ Trace Dependents จากนั้นมันจะแสดงลูกศรให้เห็นว่า Formula ในช่องนั้นคำนวณ มาจากข้อมูล ใน Cell ใดบ้างและ จากช่องนี้ถูกส่งไปใช้ ในการคำนวณ ต่อใน Cell ใดบ้างและหากต้องการลบลูกศรสามารถกดที Remove Arrow
ลูกศรจะปรากฎขึ้นเมื่อกด Trace Precedents กับ Trace Dependents
นอกจากนี้สามารถให้ Excel แสดงขั้นตอนการคำนวณ Formula ได้ โดยกดที่ Ribbon > Formula > Evaluate Formula
แสดงขั้นตอนการคำนวน Formula นั้นเป็นลำดับขั้นตอน
การรวมข้อมูลจากหลาย Table
ในหัวข้อนี้จะแสดงการรวมข้อมูลทั้งสองวิธี
การรวมข้อมูลโดยใช้ทั้งสองวิธี
การใช้ XLOOKUP() ในการ Join Table (ซึ่งแนะนำว่าหากมี หลาย Table มากๆ ไม่ควรใช้วิธีนี้)
ใน Excel รุ่นเก่าๆ อาจจะมีแต่ `VLOOKUP()` ซึ่งเป็น Aproximate match ซึ่งแนะนำให้ตั้งเป็น Exact match แทน
ก่อนที่จะ Join table นั้น จำเป็นต้องมี Column ที่บ่งบอกความสัมพันธ์ของของข้อมูล โดยเรียก Id ของของแถวนั้นว่า Primary Key และเรียก Id ของแถวอื่นที่อยู่ในตารางนี้ ที่ใช้บ่งบอกความสัมพันธ์ว่า Foriegn Key
ที่นี้ในตัวอย่าง ผมจะ Join BMI และ FBS ด้วย XLOOKUP จะเห็นว่าข้อมูลมัน SPILL มา Column ด้านข้าง
เมื่อใส่ Formula เสร็จแล้วจะได้ผลลัพธ์ดังรูป ซึ่งเราสาารถนำไป PivotTable / Graph ต่อได้
บาง การแปลงข้อมูลโดยใช้ PowerQuery
ต้องเปิด Add-in PowerQuery ก่อน โดยไปที่ Ribbon > File > Options จากนั้นเลือก Add-ins แล้วเลือก COM-Addin แล้วกด Go จากนั้น Tick ถูกที่ Microsoft PowerQuery แล้วกด Ok จะเห็น Power Query เพิ่มขึ้นมาที่ Ribbon
จากนั้นเลือก Table ที่ต้องการดึงเข้าไปยัง PowerQuery แล้วไปที่ Ribbon > Data > From Range/table แล้วกด
จะเปิดหน้า PowerQuery ขึ้นมาจากนั้น ในตัวอย่างผมจะทำเปลี่ยนข้อมูลจาก Long เป็น Wide Format
จากนั้นเราจะเลือกให้สร้างเฉพาะ Connection ก็ได้ แต่ในตัวอย่างนี้ผมโหลดข้อมูลเข้ามาเป็น Sheet ใหม่
การสร้างความสันพันธ์ของข้อมูล เพื่อใช้ใน Pivot Table และ Power Query
แนะนำอย่างมากให้ทำการเปลี่ยนแหล่งของมูลจาก Range เป็น Table ก่อน เพื่อความง่ายในการอ้างอิงและเวลาเราเพิ่มข้อมูล เราจะไม่ต้องมาเปลี่ยน Range ของ DataSource อีก
ต้องเปิด Add-in PowerQuery ก่อน โดยไปที่ Ribbon > File > Options จากนั้นเลือก Add-ins แล้วเลือก COM-Addin แล้วกด Go จากนั้น Tick ถูกที่ Microsoft PowerQuery แล้วกด Ok จะเห็น Power Query เพิ่มขึ้นมาที่ Ribbon
เตรียมแหล่งข้อมูล จากนั้น Format ข้อมูลให้อยู่ในรูปของตาราง (Ribbon > Home > Format as table) และตั้งชื่อให้เรียบร้อย จากนั้นคลิกที่ Cell ของตารางแล้วไปที่ไปที่ Ribbon > Power Pivot > Add to datamodel
ในตัวอย่างผมจะ Add Model 3 ตารางคือ BMI LABBMI LIPIDBMI โดยเมื่อครบแล้วจะเห็นว่ามีตารางใน PowerPivot 3 ตาราง เห็นไดจาก 3 Tabs ด้านล่าง ให้คลิกที่ Diagram View จากนั้น ลาก ความสัมพันธ์โดยคลิกที่ชื่อ Column แล้ว Drag ไปยัง Column
การวิเคราะห์ข้อมูลด้วย Pivot Table
จำเป็นต้องสร้าง Relationship ของข้อมูลก่อนสร้าง Pivot table ที่ดึงข้อมูลมาจากหลาย Table แต่ถ้าดึงข้อมูลจาก Table/Range ชุดเดียว ไม่ต้องสร้าง Relationship ก็ได้
การเปลี่ยนโหมดการแสดงผล Pivot table
เปลี่ยนการแสดงผล Pivot table โดยไปที่ Ribbon > Design > Report layout
เปลี่ยนการแสดงผลของ Pivot Table
แนะนำให้ใช้ Outline หากต้องการการ Filter ข้อมูลที่ง่าย และ Tabular หากต้องการคัดลอก Values ของ Pivot Table ไปใช้งานต่อ
แสดง Layout ทั้งสามแบบของ Pivot Table