คือดีมาก! สรุปผลแบบสอบถามด้วย Power Query และ Power Pivot

“อยากสรุปผลแบบสอบถามด้วย Pivot Table ต้องทำยังไงคะ?” จู่ ๆ คุณนุ่นก็จู่โจมด้วยคำถาม
“ใช้ Pivot Table แบบธรรมดาไม่ได้หรือครับ” ผมคิดแบบง่าย ๆ
“แบบธรรมดาสรุปผลได้แค่รายคน แต่สรุปรายคำถามไม่ได้ค่ะ”
“ลองยกตัวอย่างได้ไหมครับ”
“ได้ค่ะ เช่น อยากสรุปว่าแต่ละคำถามมีคนให้ 1 กี่เปอร์เซ็นต์, 2 กี่เปอร์เซ็นต์, 3 กี่เปอร์เซ็นต์”
“อ้อ”
“ที่ทำอยู่ตอนนี้คือ ใช้ Pivot Table สรุปทีละคำถาม ถ้ามี 10 คำถามก็ต้องทำ Pivot Table 10 รอบ”
“โอ”
“วิธีนี้แก้ปัญหาแบบแถ ๆ ได้ แต่ถ้าต้องการดูภาพรวม หรืออยากสรุปว่าคำถามนี้มีคนให้ 4 และ 5 กี่เปอร์เซ็นต์ ต้องทำแมนวลล้วน ๆ เลยค่ะ”
“คุณนุ่นคงเหนื่อยน่าดู”
“มากค่ะ มีวิธีที่ดีกว่านี้ไหมคะ?”
“มีครับ”
“จริงเหรอ ทำยังไงคะ?” เสียงคุณนุ่นร่าเริงขึ้นมาทันใด

แบบสอบถาม (Questionnaire) ของคุณนุ่นหน้าตาแบบนี้ครับ

มีคำถามทั้งหมด 10 ข้อ (Q01 – Q10) เรียงในแนวคอลัมน์
มีผู้ตอบแบบสอบถาม (Respondent) 20 คน เรียงในแนวแถว (Row)

ตอบโดยให้คะแนน 1-5 แบบ Likert Scale
1 คือ แย่มาก
5 คือ ดีมาก
สังเกตว่าบางคำถามมีคนไม่ตอบ ซึ่งเป็นเรื่องปกติ

หน้าผลลัพธ์ที่คุณนุ่นต้องการคือแบบนี้

ใช่ครับ Pivot Table ไม่มีทางทำได้เลย T_T

อ้าว! แล้วจะทำยังไง?

ต้องหาทางแปลงแบบสอบถามให้เป็นฐานข้อมูลหน้าตาแบบนี้ก่อน

แปลงยังไง?

ใช้ Power Query

พอแปลงเป็นฐานข้อมูลแล้ว สรุปผลด้วย Power Pivot

หมายเหตุ1: วิธีนี้เหมาะกับ Microsoft 365 (Office 365) หรือ Excel 2019 เท่านั้น
(ไม่เหมาะกับ Excel 2010/ 2013/ 2016)

หมายเหตุ2: บทความนี้มีรายละเอียดเยอะมาก อาจไม่สามารถอธิบายละเอียดทุกจุด ขอเน้นสเต็ปการทำเพื่อให้เห็นภาพรวม โดยอาจเสริมรายละเอียดในบางจุด

มาเริ่มกันเลย

เริ่มจากปรับรูปแบบข้อมูลให้เป็น Table โดยคลิกที่ข้อมูลแล้วกด Ctrl+T

ตั้งชื่อ Table เป็น RawData (หรือชื่ออื่นก็ได้)

นำข้อมูลนี้เข้าไปใน Power Query โดยคลิกเซลล์ใด ๆ ใน Table แล้วไปที่ Data/ From Table/Range

โปรแกรมจะเปิดหน้าต่างของ Power Query ขึ้นมา และพบว่าตอนนี้ทำไปแล้ว 2 Steps คือ Source และ Changed Type

ปรับรูปแบบข้อมูลให้เป็นแนวตั้ง โดยคลิกขวาคอลัมน์ Respondent แล้วเลือก Unpivot Other Columns

ว้าว! ได้หน้าตาแบบสอบถามเป็นฐานข้อมูลแล้ว ^__^

จะพบว่าตอนนี้มี 3 Steps นั่นคือเพิ่มขั้นตอน Unpivoted Other Columns เข้ามา

ชื่อคอลัมน์ใหมที่ได้คือ Attribute และ Value ไม่ค่อยสื่อเท่าไร ควรเปลี่ยนเป็น Question, Rating

การเปลี่ยนชื่อคอลัมน์นั้น ใช้วิธีดับเบิลคลิกที่หัวคอลัมน์แล้วพิมพ์ชื่อใหม่ก็ได้
แต่… เป็นการเพิ่มสเต็ปโดยไม่จำเป็น

วิธีที่ดีกว่าคือแก้ M Code ใน Formula Bar

Formula Bar อยู่ตรงไหน?

โดยดีฟอลต์แล้ว Power Query จะไม่แสดง Formula Bar ต้องเปิดใช้งาน
ไปที่ View แล้วติ๊กเลือก Formula Bar จะพบว่ามีแถบเขียนสูตรเหมือน Excel
แถบเขียนสูตรนี้ก็คือ Formula Bar นั่นเอง

สังเกตว่าสูตรใน Formula Bar ของ Step นี้ (Unpivoted Other Columns) คือ

= Table.UnpivotOtherColumns(#”Changed Type”, {“Respondent”}, “Attribute“, “Value“)

คำว่า “Attribute” และ “Value” คือชื่อคอลัมน์ที่สร้างใหม่นั่นเอง ถ้าต้องการเปลี่ยนก็แก้โค้ดตรงนี้ให้เป็น “Question” และ “Rating”
หรือสูตรเต็ม ๆ ที่แก้แล้วคือ

= Table.UnpivotOtherColumns(#”Changed Type”, {“Respondent”}, “Question”, “Rating”)

ด้วยเทคนิคนี้จะพบว่ายังมี 3 Steps เท่าเดิม และได้ชื่อคอลัมน์ตามต้องการ

เปลี่ยนชื่อคิวรี่นี้เป็น Database (หรือชื่ออื่นก็ได้)

ขั้นตอนการแปลงข้อมูลเรียบร้อยแล้ว ต่อไปคือการสรุปข้อมูลด้วย Power Pivot

Power Pivot คืออะไร?

Power Pivot คือการสร้าง Pivot Table จาก Data Model

(อ่านรายละเอียดของ Power Pivot ได้จาก บทความนี้)

เพื่อให้ข้อมูลนี้เข้าไปใน Data Model ต้องใช้ Load To หรือคลิก Home/ Close & Load/ Close & Load To..

เคสนี้ไม่จำเป็นต้องแสดงผลลัพธ์เป็นตารางใน Excel ให้เลือก Only Create Connection
และติ๊กเลือก Add this data to the Data Model เพื่อให้ข้อมูลเข้าไปใน Data Model

จะพบว่าหน้าต่าง Queries & Connections แสดง 192 rows loaded แต่กลับไม่มีผลลัพธ์แสดงออกมาเลย เพราะเลือกเป็น Only Create Connection

“แต่มันขึ้นว่า 192 rows loaded แล้ว 192 rows ไปอยู่ที่ไหน?”

อยู่ใน Data Model ครับ ^__^

เช็คง่าย ๆ ได้ที่ Data/ Go To Power Pivot Window

ถ้ายังไม่เคยเปิดใช้งาน Power Pivot มาก่อน จะมีหน้าต่างขึ้นมาถามว่าต้องการเปิดใช้งาน Data Analysis add-ins ใช้ไหม ให้เลือก Enable

และนี่คือโลกใหม่ที่ชื่อว่า Power Pivot !

บางคนเรียกหน้าต่างนี้ว่า Data Model แต่ผมขอเรียกว่า Power Pivot ละกัน

หน้าต่างนี้ไม่เหมือนกัน Power Query ม้นคือคนละโลก แต่ ‘โลกทั้งสอง’ เชื่อมกันได้

สังเกตว่าข้อมูลจาก Power Query เข้ามาใน Power Pivot แล้ว เพราะตอน Load To เลือกออปชัน Add this data to the Data Model

มาสร้าง Pivot Table ที่เป็น Power Pivot กัน

คลิก Home/ Pivot Table / Pivot Table

จะมีหน้าต่างถามว่าให้สร้าง Pivot Table ในชีตใด เพื่อความสะดวกเลือก New Worksheet (ซึ่งเป็นค่าดีฟอลต์) จากนั้นคลิก OK

จะพบว่ากลับมายังหน้าต่าง Excel ได้หน้าตาเหมือน Pivot Table ธรรมดาเป๊ะ!

แล้วมันเป็น Power Pivot ได้ยังไง?

Power Pivot ก็คือการสร้าง Pivot Table จาก Data Model
ถ้าสร้างจาก Data Model เป็น Power Pivot แน่นอน
จุดเด่นของ Power Pivot ก็คือการคำนวณที่เรียกว่า Measure

จาก Pivot Table Field List จะพบว่ามี 2 ตารางคือ Database และ RawData สังเกตว่า Database มีไอคอนรูปถังข้อมูล แปลว่าตาราง Database อยู่ใน Data Model

ถ้าคลิกตาราง Database จะแสดงคอลัมน์ที่สามารถใช้ได้ ซึ่งคือ Respondent, Question, Rating

เวลาสร้าง Pivot Table ให้ดึงข้อมูลจากตาราง Database เช่น ดึงคำว่า Question มาไว้ที่ Rows จะเกิดการสรุปข้อมูลรายคำถาม

ถ้าต้องการสรุป Rating ห้ามลากคอลัมน์ Rating มาไว้ที่ Value

อ้าว ทำไมล่ะ?

เพราะการทำแบบนี้คือการสร้าง Implicit Measure ซึ่งมีข้อเสียหลายอย่าง
ง่าย ๆ เลย ห้ามทำ!

แล้วจะสรุปข้อมูลได้ยังไง?

การสรุปข้อมูลให้ใช้ Explicit Measure (Measure) คู่กับการเขียนสูตร DAX

อยากนับว่าคำถามนี้มีคนตอบกี่คน ต้องสร้าง Measure ยังไง?

ถ้าพูดถึงการนับ ฟังก์ชันที่นึกถึงคือ COUNT แต่สูตร DAX ไม่ใช้ COUNT เพราะระบุยากว่าจะนับคอลัมน์ไหน
ให้ใช้ COUNTROWS เพื่อนับบรรทัดตารางแทน

เมื่อสักครู่นี้เราคลิก Enable Data Analysis แล้ว จะพบว่าตอนนี้มี Ribbon ใหม่ชื่อ Power Pivot
คลิกที่ Power Pivot/ Measures/ New Measure

จะได้หน้าต่างนี้
นี่คือหน้าต่างสร้าง Measure

ระบุ Measure name เป็น #Response (หรือใช้ชื่ออื่นก็ได้)
ใช้สูตรเป็น

=COUNTROWS(Database)

เลือก Category เป็น Number
เลือก Format เป็น Whole Number (เพื่อกำหนดรูปแบบตัวเลข)

เมื่อคลิก OK จะพบว่าได้คอลัมน์ใหม่ชือ #Response ตัวเลขในคอลัมน์นี้คือการนับว่าแต่ละคำถามมีคนตอบกี่คน

สังเกตว่าในตาราง Database จะมี่ฟิลด์ใหม่ชื่อ fx #Response

ใช่แล้ว ไอคอน fx สื่อว่า #Response เป็น Explicit Measure สามารถนำ Measure นี้เป็น Value ใน Pivot Table ได้เลย

อยากคำนวณว่าในแต่ละคำถามมีคนให้เรต 1 กี่เปอร์เซ็นต์ ต้องสร้าง Measure ยังไง?

ต้องใช้ฟังก์ชัน CALCULATE

CALCULATE คือฟังก์ชันที่ใช้คำนวณ โดยระบุเงื่อนไขที่ต้องการได้
โครงสร้างฟังก์ชันคือ

CALCULATE( Expression, [Filter1], [Filter2] )

Expression คือ การคำนวณที่ต้องการ
[Filter1], [Filter2] คือ เงื่อนไข เช่น เคสนี้คือ Database[Rating]=1

คลิกที่ Power Pivot/ Measures/ New Measure

ระบุชื่อ Measure name เป็น %Rate1 (หรือใช้ชื่ออื่นก็ได้)
ใช้สูตร

=CALCULATE( [#Response],Database[Rating]=1 )

[#Response] ในสูตรคือ Measure ที่สร้างด้วยฟังก์ชัน COUNTROWS นั่นเอง
ใช่ครับ เราสามารถใช้ Measure เป็น Expression ได้ ^__^

พอกด OK จะพบว่าได้ Measure ใหม่ชื่อ %Rate1
ผลลัพธ์ที่ได้คือ จำนวนคำตอบของ Rating = 1

อยากให้ %Rate1 เป็นเปอร์เซ็นต์ เช่น 20% ต้องทำยังไง?

ผลลัพธ์ที่ได้จาก CALCULATE([#Response],Database[Rating]=1) คือการนับ ถ้าอยากได้เป็นเปอร์เซ็นต์ต้องนำไปหาร

หารด้วยอะไร?

ด้วย จำนวนทั้งหมด หรือ [#Response] นั่นเอง ^__^

มาแก้ไขสูตรกัน คลิก Power Pivot/ Measures/ Manage Measures

เลือก %Rate1 แล้วคลิก Edit

เพิ่ม /[#Response] ต่อท้ายในสูตร หรือเปลี่ยนสูตรเป็น

=CALCULATE([#Response],Database[Rating]=1)/[#Response]

เลือก Category เป็น Number
เลือก Format เป็น Percentage
จากนั้น คลิก OK

ได้ %Rate1 เป็นเปอร์เซ็นต์ตามต้องการ

แล้วถ้าอยากคำนวณว่าในแต่ละคำถามมีคนให้เรต 2 กี่เปอร์เซ็นต์ ต้องทำยังไง?

คำถามดีมาก
ลองเดาคำตอบไหมครับ ^__^

ถูกต้องนะคร๊าบ!
สร้าง Measure ด้วย CALCULATE เพิ่ม แต่เปลี่ยนเงื่อนไขเป็น Database[Rating]=2
หรือสูตรเต็ม ๆ คือ

=CALCULATE([#Response],Database[Rating]=2)/[#Response]

ผลลัพธ์ที่ได้คือ

ถ้าต้องการ %Rate3, %Rate4, %Rate5 ก็ทำแบบเดียวกัน

ผลลัพธ์ที่ได้คือ

ปรับความสวยงามของ Pivot Table นิดนึง ^__^

แล้วถ้าอยากสรุปว่าแต่ละคำถามมีคนให้เรต 4 และ 5 กี่คน ต้องทำยังไง?

เพื่อให้เห็นภาพ มาสร้าง Pivot Table อีกตารางดีกว่า ^__^

เริ่่มจากสร้าง Pivot Table แบบปกติ คลิก Insert/ Pivot Table

Pivot Table นี้ต้องเป็น Power Pivot หรือสร้างจาก Data Model ดังนั้นให้เลือกออปชัน Use this workbook’s Data Model
แล้วคลิก OK

ใช้ Question เป็น Rows เช่นเดิม

แต่ต้องสร้าง Measure ใหม่ เพราะใช้ Measure เดิมไม่ได้

เรายังคงใช้ฟังก์ชัน CALCULATE เหมือนเดิม แต่ระบุเงื่อนไขเป็น Database[Rating]=4 ไม่ได้ เพราะต้องการเงื่อนไขเป็น Database[Rating]=5 ด้วย
ดังนั้นต้องนำเงื่อนไขทั้งสองมาเชื่อมกันแบบ “หรือ”

การเชื่อมเงื่อนไขแบบ “หรือ” นั้น ใช้เครื่องหมาย || (Double Pipe) โดยเขียนเป็น
Database[Rating]=4 || Database[Rating]=5

หรือสร้าง Measure name %Rate4&5 ด้วยสูตร

=CALCULATE( [#Response], Database[Rating]=4 || Database[Rating]=5 ) / [#Response]