หนึ่งในปัญหาใหญ่ที่ทำให้ Pivot Table ใช้ไม่ได้ผลก็คือ ข้อมูลไม่ได้ถูกจัดฟอร์แมตให้เป็นฐานข้อมูล หรือก็คือถูกเก็บในแนวขวาง (Crosstab) แทนที่จะเป็นแนวตั้ง!
ข้อมูลแนวขวางที่ว่า หน้าตาประมาณนี้ครับ
ใครที่ทำฟอร์แคส (Forecast) บ่อยๆ ต้องใช้ฟอร์แมตหน้าตาแบบนี้แน่นอน
เรารู้ว่าข้อมูลแนวขวางไม่สามารถนำไปทำอะไรต่อได้ ปัญหาก็คือจะแปลงให้เป็นแนวตั้งได้ยังไงล่ะ?
ถ้าทำแบบแมนวล นั่งก็อปทีละบรรทัด บอกเลยว่าชีวิตเศร้ามาก (ผมเคยมาแล้ว T_T)
ถ้าใครใช้ VBA ปัญหานี้ถือว่า “จิ๊บๆ” ใช้คำสั่งวนลูป 2 ชั้น เขียนโค้ดไม่กี่บรรทัดก็จบแล้ว
ยิ่งตอนกด F5 แล้วนั่งกระดิกเท้าดูคอมพ์ทำงานเอง บอกเลย “ฟิน” สุดๆ ^^
แต่ปัญหาคือคนส่วนใหญ่ไม่ชำนาญ VBA
แล้วจะทำยังไงล่ะ?
คำตอบคือ ใช้ Power Query และก็คือสิ่งที่กำลังจะปอกแบบหมดเปลือกในบทความนี้ครับ ^__^
รับรองว่าเห็นแล้วจะต้องอึ้ง
เพราะมันง่ายมาก ใช้เวลา 2 นาทีเสร็จ แถมไม่ต้องเขียนสูตรสักตัว!
Power Query คือเครื่องมือใหม่ที่มีใน Excel 2016 นั่นคือถ้าใครใช้ Excel 2016 (หรือ Office 365) อยู่แล้ว สามารถใช้งานได้เลย ไม่ต้องลงอะไรเพิ่ม
แต่ถ้าใครใช้ Excel 2010 หรือ Excel 2013 Power Query ไม่ใช่เครื่องมือมาตรฐาน ต้องลง Add-Ins เพิ่มเติม
Add-Ins นี้ลงได้ฟรี ไม่ต้องเสียตังค์สักบาท ดาวน์โหลดได้จากลิงค์นี้เลยครับ
https://www.microsoft.com/en-us/download/details.aspx?id=39379
พอลงแล้ว ก็มาใช้งานกันเล๊ย!
เพื่อให้เห็นภาพการทำงานทั้งหมด ขอสรุปเทคนิคเป็น 5 ขั้นตอน ดังนี้ครับ
- ปรับหัวคอลัมน์ให้มีบรรทัดเดียว
- เปลี่ยนฟอร์แมตเป็น Table
- เปลี่ยนเป็นข้อมูลแนวตั้งด้วยคำสั่ง Unpivot Other Columns
- Split ข้อมูลหัวคอลัมน์ออกจากกัน
- แยกประเภทตัวเลขให้เป็น 2 คอลัมน์ด้วยคำสั่ง Pivot Column
แม้มี 5 ขั้นตอน แต่บอกเลย 2 นาที เสร็จ!
(ขั้นตอนที่ 3-5 ทำใน Power Query)
ไม่เชื่อเหรอ? งั้นมาลองกัน ^__^
1 ปรับหัวคอลัมน์ให้มีบรรทัดเดียว
ข้อมูลเดิมมีหัวคอลัมน์ 2 บรรทัด การจะแปลงโดยเริ่มจากหน้าตาแบบนี้จริงๆก็ทำได้ แต่ขั้นตอนจะยุ่งยากกว่า ขอแนะนำให้ “ยุบ” หัวคอลัมน์ให้เหลือบรรทัดเดียว
เช่น ยุบเป็น Volume/Jan, Volume/Feb, Volume/Mar
เคสนี้เก็บข้อมูลเดือนโดยใช้ตัวอักษรย่อ Jan, Feb, Mar อาจมีความยุ่งยากในภายหลัง ขอแนะนำให้มีตัวเลขกำกับเพื่อบอกว่า Jan = 1, Feb =2, Mar =3 หรือได้หน้าตาเป็น
Volume/Jan/1, Volume/Feb/2, Volume/Mar/3
อาจเขียนสูตรง่ายๆแบบนี้ก็ได้ครับ
2 เปลี่ยนฟอร์แมตเป็น Table
เทคนิคหลักของเราคือ Power Query การจะใช้ Power Query ได้ ควรเปลี่ยนฟอร์แมตข้อมูลให้เป็น Table เสียก่อน
วิธีเปลี่ยนก็ง่ายมากๆ นั่นคือ คลิกที่ข้อมูล (เซลล์ใดก็ได้)/ Insert/ Table
(หรืออาจคลิกที่ข้อมูลแล้วกด Ctrl+T ก็ได้)
เนื่องจากบรรทัดที่เป็นหัวตารางสร้างด้วยสูตร ตอนแปลงเป็นเทเบิลจะมีคำถามว่าข้อมูลจะถูกเปลี่ยนเป็นตัวหนังสือธรรมดา เราโอเคมั๊ย?
ก็ต้องโอเคสิ ^^ กด Yes ไปเลย
ข้อมูลเป็น Table แล้ว!
วิธีสังเกตว่าข้อมูลเป็นเทเบิลหรือไม่ ไม่ใช่ดูว่ามีลายฟ้าสลับขาวนะครับ เพราะลายฟ้าสลับขาวนี้อาจเกิดจากการทำแมนวลก็ได้ วิธีที่ถูกต้องคือ ถ้าคลิกข้อมูลที่เป็นเทเบิล จะมีริบเบินชื่อ Design โผล่ขึ้นมา
ถ้ามีริบเบิน Design ก็แน่ใจว่าเปลี่ยนฟอร์แมตเป็นเทเบิลเรียบร้อยแล้วครับ ^__^
3 เปลี่ยนเป็นข้อมูลแนวตั้งด้วยคำสั่ง Unpivot Other Columns
พอเปลี่ยนฟอร์แมตข้อมูลเป็นเทเบิลแล้ว ขั้นตอนต่อไปคือการใช้คำสั่งใน Power Query ซึ่งการจะใช้คำสั่งของ Power Query ได้ เราต้องนำตารางนี้เข้าไปใน Power Query เสียก่อน
นำเข้าไปง่ายๆแบบนี้ครับ
สำหรับคนที่ใช้ Excel 2016: คลิกข้อมูลใดๆในเทเบิล/ Data/ From Table/Range
สำหรับคนที่ใช้ Excel 2010/ 2013 เมื่อลง Add-Ins Power Query แล้ว จะพบว่ามีริบเบินใหม่ชื่อ Power Query ให้คลิกที่ริบเบินนั้น แล้วเลือกคำสั่ง From Table/Range
(Power Query/ From Table/Range)
พอคลิกแล้ว จะมีหน้าต่างใหม่เกิดขึ้น นั่นคือหน้าต่างที่เรียกว่า Power Query Editor
หน้าตาแบบนี้ครับ
Power Query Editor ก็คือหน้าต่างสำหรับใช้คำสั่งของ Power Query นั่นเอง
เพื่อให้เข้าใจง่าย ผมขอเรียกเจ้า Power Query Editor นี้ว่า “โลกของพาวเวอร์คิวรี” นะครับ ^^
สิ่งที่เราต้องการก็คือ เปลี่ยนข้อมูลแนวขวางให้เป็นแนวตั้ง สามารถทำได้ง่ายมากๆโดยใช้คำสั่ง Unpivot Columns
วิธีก็ง่ายสุดๆ เลือกคอลัมน์ที่ต้องการแปลงทั้งหมด (อาจใช้ปุ่ม Shift ช่วยเลือก) จากนั้นคลิกขวา แล้วเลือกคำสั่ง Unpivot Columns จบ!
ปัญหาคือ เคสนี้มีคอลัมน์ที่ต้องการแปลงจากแนวขวางเป็นแนวตั้งถึง 24 คอลัมน์ (Volume 12 คอลัมน์ และ Price 12 คอลัมน์) การคลิกเลือกอาจไม่ค่อยสะดวกเท่าไรนัก
วิธีที่ง่ายกว่าคือ เลือกคอลัมน์ที่ไม่ต้องการแปลง จากนั้นใช้คำสั่ง Unpivot Other Columns
จากเคสนี้จะพบว่า คอลัมน์ที่ต้องการให้เหมือนเดิมคือคอลัมน์ Customer และ Product ส่วนคอลัมน์อื่นๆต้องการแปลงจากแนวนอนเป็นแนวตั้งทั้งหมด เราก็เลือกเฉพาะคอลัมน์ Customer กับ Product (อาจใช้ปุ่ม Ctrl ช่วยเลือก) แล้วคลิกขวาที่บริเวณหัวคอลัมน์ เลือกคำสั่ง Unpivot Other Columns
Power Query จะเปลี่ยนข้อมูลให้เป็นแนวตั้งภายใน 1 วินาที !
ใครที่เคยเห็นครั้งแรก บอกเลยมี “อึ้ง” แน่นอน ^^
4 Split ข้อมูลหัวคอลัมน์ออกจากกัน
ดูเผินๆเหมือนจะเสร็จแล้ว แต่ยังไม่เสร็จ เพราะหัวคอลัมน์เดิมแม้ถูกเปลี่ยนจากแนวนอนเป็นแนวตั้ง แต่ค่าของมันยังคงเหมือนเดิม นั่นคือยังเป็น Volume/Jan/1, Volume/Feb/2
เราต้องแตกคอลัมน์นี้ออกจากกันเป็น 3 คอลัมน์ หรือแยกคำว่า Volume, Jan และ 1 ออกจากกัน
ขอบอกว่าทำได้ง่ายมั่กๆ ด้วยคำสั่ง Split Column
จากเคสนี้ จะพบว่าการแยกมี “ตัวช่วยแยก” นั่นคือเครื่องหมายสแลช (/) นั่นเอง หรือทำแบบนี้ครับ
คลิกขวาคอลัมน์ที่ต้องการแยก (ในที่นี้คือ Attribute)/ Split Column/ By Delimiter
จะมีหน้าต่างถามว่า ใช้อะไรเป็นตัวช่วยแยก เราก็บอกว่าใช้เครื่องหมายสแลช (/)
(ถ้าอัพเดตเป็นเวอร์ชันล่าสุด โปรแกรมจะเลือกให้เองเลยว่าควรใช้เครื่องหมายใด ฉลาดสุดๆอ่ะ!)
ได้ 3 คอลัมน์แว๊ว!!
ชื่อคอลัมน์ที่ถูกแยก Attribute.2, Attribute.3 อาจดูไม่ค่อยสื่อ ผมขอเปลี่ยนเป็น MonthName กับ MonthNo นะครับ ดับเบิ้ลคลิกที่หัวคอลัมน์แล้วเปลี่ยนได้เลย
ในเคสนี้ ชื่อคอลัมน์ Attribute.1 ผมไม่ได้แก้ไข เพราะคอลัมน์นี้จะถูกปรับให้เป็น 2 คอลัมน์ในขั้นตอนต่อไปครับ ^__^
5 แยกประเภทตัวเลขให้เป็น 2 คอลัมน์ด้วยคำสั่ง Pivot Column
หลังจากแยก Volume/Jan/1 ออกเป็น 3 คอลัมน์แล้ว เราจะพบว่าคอลัมน์ที่เป็นตัวเลขมีเพียงคอลัมน์เดียวคือ Value
คอลัมน์นี้บางบรรทัดเป็นข้อมูลของ Volume แต่บางบรรทัดเป็นข้อมูลของ Price ทั้งสองข้อมูลไม่ควรอยู่ด้วยกัน หรือควรแยกออกจากกันเป็น Volume คอลัมน์นึง และ Price คอลัมน์นึง
แล้วจะแยกยังไงล่ะ?
ถ้าสังเกตดีๆ จะพบว่าข้อมูลใน Attribute.1 เป็นตัวช่วยบอกว่าบรรทัดใดคือ Volume และบรรทัดใดคือ Price
หรือพูดง่ายๆคือเราใช้คอลัมน์ Attribute.1 เป็นตัวช่วยแยก Value ที่เป็น Volume กับ Price ออกจากกันได้
และทำได้ง่ายมากๆ ด้วยคำสั่ง Pivot Column แบบนี้ครับ
คลิกคอลัมน์ที่ใช้เป็นเกณฑ์แยก (ในที่นี้คือ Attribute.1) / Transform/ Pivot Column
(ไม่ใช่ Unpivot Column นะ อย่าคลิกผิดล่ะ ^^)
พอคลิกแล้วจะมีหน้าต่างถามว่า คอลัมน์ที่ต้องการถูกแยกคือคอลัมน์ใด เคสนี้ให้เลือกคอลัมน์ที่เป็นตัวเลข หรือก็คือคอลัมน์ Value จากนั้นคลิกปุ่ม OK
Volume กับ Price ถูกแยกเป็น 2 คอลัมน์แล้ว!
อเมซซิ่งไทยแลนด์เลยใช่ไหมล่ะ! ^^
(ตอนแรกที่ผมเห็น ร้อง “เฮ้ย!” ดังมาก ^^)
จริงๆแล้วเคสนี้จะจบแค่นี้เลยก็ได้ แต่บางคนอาจอยากให้เรียงข้อมูลในคอลัมน์ MonthName จาก Jan – Dec หรือเรียงข้อมูลในคอลัมน์ MonthNo จาก 1-12 ซึ่งตอนนี้ข้อมูลไม่ได้เรียงแบบนั้น
ยิ่งถ้าให้ดี ควรเรียงข้อมูลในคอลัมน์ Customer ก่อน (เช่น CustomerA – CustomerC) จากนั้น เรียงตามคอลัมน์ Product (จาก Product01 – Product04)
ไม่มีปัญหาครับ เพราะเรียงข้อมูลใน Power Query ได้ง่ายมากๆ ด้วยคำสั่ง Sort นั่นเอง ^__^
เริ่มจากเรียงข้อมูลในคอลัมน์ Customer ก่อน
คลิกคอลัมน์ Customer/ Home/ คลิกไอคอน AtoZ จบ!
คราวนี้ก็ทำแบบเดียวกับคอลัมน์ Product (คลิกคอลัมน์ Product/ Home/ คลิกไอคอน AtoZ)
และ MonthNo (คลิกคอลัมน์ MonthNo/ Home/ คลิกไอคอน AtoZ)
ถ้าสังเกตดีๆ จะพบว่าคอลัมน์ที่ถูก Sort จะมีไอคอนแสดงบริเวณ Drop-down ของคอลัมน์นั้นๆด้วย
เท่านี้ก็ได้หน้าตาแบบที่ต้องการแล้ว ^__^
ความเจ๋งของ Power Query ที่ผมชอบมากก็คือ ทุกสเต็ปที่ทำจะมี “บันทึก” ไว้ด้วย
“บันทึก” ที่ว่าอยู่ตรงหน้าต่างด้านขวาที่ชื่อ Applied Steps นั่นเอง
ผมชอบเจ้า Applied Steps นี้มาก เพราะบอกทุกอย่างที่ทำ สามารถย้อนกลับไปดูผลลัพธ์ของสเต็ปใดๆ หรือจะยกเลิกบางขั้นตอน หรือปรับเปลี่ยนบางอย่างก็ได้ทั้งนั้น
มันคูลมาก!
สำหรับเคสนี้ผมถือว่าเสร็จสิ้นกระบวนการแล้ว ขั้นตอนต่อไปก็คือการนำผลลัพธ์ไปแสดงในเอ็กเซลนั่นเอง
ทำได้ง่ายมาก โดยการคลิก Home/ Close & Load/ Close & Load To
พอคลิก Close & Load To จะได้หน้าต่างนี้ขึ้นมา
จริงๆแล้ว เราจะคลิกไอคอน Close & Load ตั้งแต่แรกเลยก็ได้ แต่ผมชอบคลิก Close & Load To มากกว่า เพราะเลือกได้ว่าจะแสดงข้อมูลนี้เป็น Table ในเอ็กเซล หรือจะนำข้อมูลนี้ไปสร้างเป็น Pivot Table หรือเลือกว่าไม่ต้องแสดงผลลัพธ์แต่ให้เชื่อมความสัมพันธ์ไว้ก่อนก็ได้ (Only Create Connection)
(ในบางเคสเราต้องการสร้างความสัมพันธ์ระหว่างตารางในเอ็กเซลกับ Power Query ไว้ก่อน จากนั้นค่อยนำไปทำอะไรเพิ่มเติมกับตารางอื่นได้ เช่น Append, Merge แต่บทความนี้ยาวมากแล้ว ขอยังไม่พูดถึงนะครับ ^__^)
สำหรับเคสนี้ ผมขอเลือกแสดงผลเป็นเทเบิล ผมก็เลือก Table แล้วคลิกปุ่ม OK จะได้ข้อมูลหน้าตาแบบเดียวกันในเอ็กเซลทันที
เสร็จแว๊ว แว๊ว แว๊ว !!
ถ้าสังเกตจะพบว่าด้านขวามือมีหน้าต่างใหม่ชื่อ Queries & Connections
หน้าต่างนี้มีไว้เพื่อบอกว่ามีข้อมูลอะไรบ้างที่ถูกสร้างความสัมพันธ์กับ Power Query
เช่นเคสนี้บอกว่า มีการเชื่อม Table1 (ตารางที่เราปรับฟอร์แมตเป็น Table) เข้าไปใน “โลกของพาวเวอร์คิวรี” และมีข้อมูล 120 บรรทัดถูกโหลดออกมา
(ถ้าใครใช้ Power Query ในเบื้องลึกจะพบว่า Queries & Connections มีความสำคัญมาก)
สิ่งที่เจ๋งมากก็คือ ข้อมูลในตารางผลลัพธ์ (ตารางที่ข้อมูลถูกปรับเป็นแนวตั้ง) เชื่อมกับตารางต้นตอ (ตารางที่ทำเป็นเทเบิล) และจะอัพเดตตอนไหนก็ได้
นั่นแปลว่า ถ้าข้อมูลในตารางต้นตอเปลี่ยน ข้อมูลในตารางผลลัพธ์ก็จะถูกเปลี่ยนไปด้วย !
ไม่เชื่อเหรอ มาลองกัน ^__^
เช่น แก้ Volume ของ CustomerA, Product01 ในเดือน Jan จาก 34 เป็น 10,000
ถ้าต้องการอัพเดตตัวเลข 10,000 นี้ในตารางผลลัพธ์ ก็เพียงแค่ คลิกขวาแล้ว Refresh
ตัวเลข 10,000 จะถูกอัพเดตในตารางผลลัพธ์ทันที !
.
เป็นอย่างไรบ้างครับกับเทคนิคของ Power Query ผมว่ามันสุดยอดมากเลยนะ ^__^
ถ้าเปรียบเทียบกับ VBA แล้ว คนที่ใช้ VBA เก่งๆจะมองว่าเคสแบบนี้ใช้ VBA ง่ายกว่า เพราะแค่เขียนโค้ดไม่กี่บรรทัดก็เสร็จแล้ว วิธีนี้ทำตั้ง 5 สเต็ป
แถมการใช้ VBA ก็มีข้อดีคือใช้ได้กับเอ็กเซลทุกเวอร์ชันด้วย
ผมมองว่าทุกวิธีมีทั้งข้อดีและข้อเสีย อยู่ที่ว่าเราถนัดและเข้าใจในวิธีใด
โดยส่วนตัวแล้วผมชอบวิธี Power Query ด้วยเหตุผล 3 ข้อ
- ถ้าข้อมูลเยอะๆ (เช่นเป็น 100,000 บรรทัด) การใช้ Power Query จะเร็วกว่า VBA อย่างเห็นได้ชัด
- ถ้าข้อมูลเปลี่ยนแปลง แค่คลิกขวาแล้ว Refresh ก็จบเลย ในขณะที่ VBA ต้องรันใหม่ทั้งหมด
- การใช้ VBA จำเป็นต้องมีความรู้พื้นฐานพอควร เช่น เข้าใจการอ้างอิงเซลล์ เข้าใจ syntax ของชุดคำสั่ง แต่ถ้าใช้ Power Query แม้ไม่มีความรู้พื้นฐานก็สามารถทำตามได้ง่ายๆ
แล้วข้อเสียล่ะ?
ข้อเสียของวิธีนี้ในมุมมองของผมมี 2 ข้อคือ
- เหมาะกับ Excel 2016 แม้สามารถใช้ใน Excel 2010/ 2013 ได้แต่ก็ต้องลง Add-Ins เพิ่มเติม ซึ่งอาจไม่ค่อยสะดวก
- คนที่จะใช้เทคนิคนี้ควรเข้าใจข้อดี-ข้อเสียของการปรับฟอร์แมตเป็น Table และมีความรู้พื้นฐานอื่นๆของ Power Query บ้าง เพราะในทางปฏิบัติแล้วจะมีการปรับนู่นนั่นนี่เพิ่มเติมเสมอ
อย่างไรก็ตาม Power Query ถูกออกแบบมาให้ใช้งานง่าย ผมเชื่อว่าถ้าทำความเข้าใจสักหน่อย สามารถใช้ได้ทุกคนแน่นอนครับ ^__^
แถมถ้าใช้ Power Query ใน Excel เป็น ก็จะใช้ Power Query ใน Power BI Desktop เป็นด้วย เพราะ Power Query เป็นเครื่องมือของทั้ง Excel และ Power BI Desktop
เรียกได้ว่า ถ้าชำนาญ Power Query ก็เท่ากับใช้ Power BI Desktop บางส่วนได้แล้ว หรือเท่ากับ
“ยิงปืนนัดเดียว ได้นกสองตัว”
ถ้าเปรียบ VBA เป็นดาบ Power Query ก็คงเป็นหอก
ยากจะบอกว่าดาบดีกว่าหอก หรือหอกดีกว่าดาบ
อยู่ที่ว่ารบที่สนามไหน และกำลังรบกับใคร
แต่ถ้าชำนาญทั้งดาบและหอก ก็มีชัยไปกว่าครึ่งแล้วครับ ^__^
สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาได้เลยครับ
UnpivotCrosstabNestedColumns_180806
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
.
Credit: ขอบคุณเทคนิคดีๆจาก
