Excel

Excel for Healthcare Data

9 August 2022

สอนการใช้งาน Excel

วิธีการใช้งานและรูปภาพทั้งหมดมาจาก Microsoft Excel ที่เป็นส่วนหนึ่งของ Microsoft Office 365 หากคุณใช้ Excel รุ่นเก่ากว่านี้ หรือไม่ใช่รุ่น 365 อาจมีหน้าจอโปรแกรมที่แตกต่าง และอาจไม่มีบางฟังก์ชัน

Work-in-progress

ดูไฟล์ตัวอย่างได้ที่ DEMO.XLS https://1drv.ms/x/s!AnqXyzBKVJbomsxqj2p9mc1oUADNKw?e=qrVcdT

DEMO2.XLS 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

ระบุ Range (โดยการพิมพ์หรือ Drag) ของ Table ใหม่ที่ต้องการ โดยต้อง Overlap กับส่วนเดิม

หรือ Drag ที่บริเวณมุมขวาล่างของ Table เพื่อปรับขนาด

Drag บริเวณเครื่องหมายบวกตรงมุมขวาล่างของ Table

ก็ได้จะ Table ขนาดใหม่ตามที่ต้องการ

Formula ใน Table

เมื่อ Format cells เป็น Table แล้ว การใช้งาน Formula ใน Table จะง่ายขึ้นสำหรับ Formula ที่อ้างอิง Cell อื่นๆ ที่อยู่ใน Row เดียวกัน

โดยแทนที่จะเป็น A1 Notation จะการเป็น [@[Header1]:@[Header2]] แทน ซึ่งจะหมายถึงการอ้างอิงถึงค่าใน Cell ที่มี Header

เมื่อเราใส่ Formula ในบรรทัดแรกแล้ว เราสามารถจะ Fill in cell ที่เหลือใน Column นั้นได้โดยตรง โดยการ Drag ลงมาหรือกดจาก Auto correct ก็ได้

จะเห็นว่า 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

การใช้ `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")),""))

การแยกข้อความให้จาก 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 จาก มากไปหาน้อย

Filter แถวข้อมูล

จะเห็นว่าบางแถวถูกซ่อนแต่แถวมันยังคงอยู่เห็นได้จากจำนวนแถวข้อมูลที่ยังเท่าเดิม

หลังการ Filter แถวข้อมูล จะเห็นว่าข้อมูลยังคงอยู่ 7 แถว เหมือนเดิม

ลบแถวข้อมูล

แนะนำว่าเราอาจ Format สีพื้นหลังเพื่อแสดงให้เห็นแถวที่ Duplicate ก่อน เพื่อที่เราจะได้บอกพอได้ว่าแถวไหนบ้างที่จะถูกลบแถวที่ซ้ำออกไป โดยกดที่ Ribbon > Home > Conditional Formating

จากนั้นเราค่อยทำการลบข้อมูลออกจริง โดยไปที่ Ribbon > Data > Remove duplicates จากนั้นเลือกเฉพาะ Column ที่จะพิจารณาว่ามีข้อมูลซ้ำ อย่างในกรณีนี้คือ SSO

การ Validate ข้อมูล

บางครั้งเราต้องจำกัดการกรอกและ Validate ข้อมูลที่ได้รับมาก่อน ซึ่งใน Excel ก็มีเครื่องมือที่จะช่วย

การจำกัดข้อมูลที่สามารถกรอกได้

เลือก Cell ที่ต้องการแล้วไปที Ribbon > Data > Data Validation จากนั้น เลือก Validation criteria เป็น Allow list จากนั้นกำหนด Source โดยสามารถพิมพ์ Entry เว้นด้วย Comma หรือ จะเลือก Range ของข้อมูลทมี่สามารถใส่ได้ก็ได้

จากนั้นสามารถกำหนด Input message ที่ต้องการให้แสดงเวลาเลือก Cell และเวลากรอกข้อมูลผิดได้ โดยสามารถเลือก Action ได้ 3 แบบ คือ Stop คือต้องแก้ข้อมูลให้ถูกเท่านั้น Warning คือเตือนสามารถยกเลิกหรือยืนยันไปต่อได้ Information คือแจ้งให้ทราบเท่านั้น

ตั้งค่าข้อความเวลาข้อมูลไม่เป็นไปตาม Validation criteria

เวลาข้อมูลผิดพลาดจะขึ้นข้อความแบบนี้

เรายังสามารถให้ 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 ที่ต้องการ

และหากเราไปดูที่ Formatting rules จะเห็นว่ามันเปลี่ยนไป

การใช้ COnditional formating นั้นนอกจากจะทำให้สามารถ Eyeballing ได้แล้วยังทำให้ใช้ Filter by formattinh ได้ด้วย

ข้อมูลประเภท DATE

แนะนำว่าก่อนการบันทึกข้อมูลปะรเภท Date ควรตั้ง Format และ Data Validation ให้ถุกต้อง เพื่อไม่ให้ต้องมาตามแก้ไข

โดยการกรอก Date แนะนำไปที่ Ribbon > Number > กดลูกศรเล็กๆ Number Format แล้วเลือก Date แล้วเลือก Regional setting ที่ต้องการ โดยถ้าจะใช้ พศ ให้เลือก Thai จากนั้น

ถ้าจะ Input ด้วย พศ ให้ tick input date …
ถ้าจะ Input ด้วย คซ ให้ untick input date … จากนั้น Formatting แนะนำให้เลือกแบบที่แสดงเดือนเต็มๆ ป้องกันการสัลบ เดือน/วัน หรือวัน/เดือน

จากนั้นก็ไปตั้ง 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 ซึ่งคำนวนค่าเฉลี่ย 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 อีก

เตรียมแหล่งข้อมูล จากนั้น Format ข้อมูลให้อยู่ในรูปของตาราง (Ribbon > Home > Format as table) และตั้งชื่อให้เรียบร้อย จากนั้นคลิกที่ Cell ของตารางแล้วไปที่ไปที่ Ribbon > Power Pivot > Add to datamodel

ในตัวอย่างผมจะ Add Model 3 ตารางคือ BMI LAB_BMI LIPID_BMI โดยเมื่อครบแล้วจะเห็นว่ามีตารางใน 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

แนะนำให้ใช้ Outline หากต้องการการ Filter ข้อมูลที่ง่าย และ Tabular หากต้องการคัดลอก Values ของ Pivot Table ไปใช้งานต่อ