“Power Pivot ต่างกับ Pivot Table ยังไงครับ?” จู่ ๆ พี่มนต์ชัยก็ยกมือถาม

นักเรียนหลายคนในห้องพยักหน้าหงึก ๆ คล้ายส่งสัญญาณว่าเห็นด้วย
ดูเผิน ๆ นี่คือคำถามเบสิก แต่จริง ๆ แล้วตอบยากมาก

“เป็นคำถามที่ดีครับ” ผมตอบพลางส่งยิ้ม ข้างในลอบหลั่งเหงื่อเย็นเยียบ ในหัวประมวลผลอย่างเร่งร้อนเพื่อหาคำตอบ คล้ายหงส์ที่ลอยอยู่บนผิวน้ำอย่างงามสง่า แต่ขาขยับเป็นระวิงอยู่ใต้น้ำ

แล้วผมตอบพี่มนต์ชัยว่าไงน่ะหรือ?
ขอเล่าให้ฟังผ่านบทความนี้ครับ ^__^

ถ้าอธิบายง่ายๆ Power Pivot คือ Pivot Table เวอร์ชันอัพเกรด

แล้วทำไมต้องอัพเกรด Pivot Table ด้วยน่ะหรือ?

ถ้าใครเคยใช้ Pivot Table ถึงจุดนึงจะพบว่า Pivot Table มีข้อจำกัดหลายอย่าง เช่น
– ใช้กับข้อมูลเกินหนึ่งล้านบรรทัดไม่ได้
– ต้องสร้าง Pivot Table จากข้อมูลตารางเดียวเท่านั้น หรืออีกนัยนึงคือสร้าง Pivot Table จากฐานข้อมูลที่มีความสัมพันธ์กันหลายๆตารางไม่ได้
– การสร้างสูตรคำนวณหรือ Calculated Fields สร้างได้เฉพาะการคำนวณแบบง่ายๆ ไม่สามารถใช้กับการคำนวณแบบซับซ้อนได้
– เวลาสร้าง Pivot Table ต้องปรับฟอร์แมตตัวเลขใหม่ทุกครั้ง Pivot Cache จะไม่จำว่าตัวเลขจากคอลัมน์นี้ต้องใส่คอมมาหรือต้องไม่มีจุดทศนิยม เรื่องนี้เหมือนจะเล็กน้อย แต่เอาเข้าจริงแล้วสร้างความเซ็งจิตให้กับผู้ใช้งานอย่างยิ่งยวด T_T
– ถ้าจะสร้างกราฟหรือ Pivot Chart ต้องสร้าง Pivot Table พ่วงเข้ามาด้วย สร้างเฉพาะ Pivot Chart อย่างเดียวไม่ได้
– ไฟล์ใหญ่มาก เพราะมีทั้งฐานข้อมูล คอลัมน์ที่สร้างขึ้นมาเพื่อช่วยคำนวณ (Helper Column) รวมถึง Pivot Cache

แต่ปัญหาเหล่านี้จะหมดไป ถ้าใช้ Power Pivot !!
Power Pivot ถูกออกแบบมาเพื่อแก้ไขปัญหาเหล่านี้ทั้งหมด
หรือสรุปได้ 6 ข้อ ดังนี้ครับ

1. ใช้กับข้อมูลจำนวนมากกว่าหนึ่งล้านบรรทัดได้

ปัญหานึงที่คนใช้งาน Pivot Table ต้องปวดหัวเวลาเจอข้อมูลขนาดใหญ่ก็คือ
Pivot Table รองรับข้อมูลแค่หนึ่งล้านบรรทัด
หรือถ้าจะพูดให้ถูกเป๊ะก็คือ ข้อมูลที่ใช้เป็นฐานข้อมูลของ Pivot Table ต้องมีจำนวนไม่เกินบรรทัดสูงสุดที่ Excel รองรับได้
หรือก็คือ 1,048,576 บรรทัด
(ขอเรียกสั้นๆว่าหนึ่งล้านนะครับ ถ้าเขียนเต็มๆจะยาวและปวดตาเวลาอ่าน ^^)

ในแง่ของคนทำงานแล้ว หนึ่งล้านบรรทัดมันไม่พอหรอก ข้อมูลมีแต่จะเพิ่มขึ้น ล้านเดียวแป๊ปๆก็เต็มแล้ว
การจำกัดแค่ล้านเดียว ทำให้ต้องปรับโครงสร้างข้อมูลเยอะมาก พอปรับโครงสร้างข้อมูล ปัญหาก็ตามมาเป็นกอง

แต่ปัญหานี้จะหมดไป ถ้าใช้ Power Pivot !!
เพราะ Power Pivotไม่ได้จำกัดข้อมูลแค่หนึ่งล้านบรรทัด
จะสองล้าน ห้าล้าน สิบล้าน มาเหอะ ได้หมด !

ทำไม Power Pivot ถึงรองรับข้อมูลได้มากกว่า 1 ล้านบรรทัดน่ะหรือครับ?
เพราะว่าข้อมูลไม่ได้อยู่ใน Excel แต่จะอยู่ใน Power Pivot ซึ่งเป็นอีกหน้าต่างนึงของ Excel
หน้าตาประมาณนี้

เวลาใช้งาน Power Pivot ต้องใช้งานผ่านริบเบิน Power Pivot ซึ่งมีเฉพาะ Excel บางเวอร์ชันเท่านั้น
อ้อ! ถ้าใครใช้ Office 365 ขอแสดงความยินดีด้วย Power Pivot เป็นฟีเจอร์พื้นฐานของ Office 365 ครับ ^__^

(แต่ถ้าใครใช้ Excel 2010/ Excel 2013 โดยส่วนตัวแล้วไม่แนะนำให้ใช้ Power Pivot เพราะถึงแม้จะใช้ได้ แต่จะมีปัญหาตามมาหลายอย่าง)

แม้ Power Pivot จะรองรับข้อมูลขนาดใหญ่ได้ แต่ถ้าข้อมูลเยอะมาก การประมวลผลก็ช้าเป็นเงาตามตัว
มีคนเคยทดลองใช้ Power Pivot กับข้อมูลใหญ่ขนาดร้อยล้านบรรทัด พบว่าใช้ได้ แต่ต้องรอโหลดข้อมูลประมาณหนึ่งชั่วโมง และการปรับนั่นนู่นนี่ก็มีความหน่วงพอควร

โดยส่วนตัวแนะนำว่าถ้าต้องจัดการข้อมูลมหาศาล ให้ใช้คอมพ์ที่มี CPU และ RAM สูงสุดเท่าที่จะหาได้
(เช่น i7 Gen 8th, RAM 16 GB)
อ้อ! Hard Disk ควรเป็น SSD ด้วยนะครับ ^__^

2 สร้าง Pivot Table จากข้อมูลหลายๆตารางได้

ปกติแล้ว การสร้าง Pivot Table ต้องสร้างจากข้อมูลตารางเดียวเท่านั้น
ดูเผินๆเรื่องนี้เหมือนจะไม่ใช่ปัญหา แต่เรื่องนี้จะกลายเป็นปัญหาทันทีถ้าตารางนั้นมีขนาดใหญ่ และต้องการข้อมูลเพิ่ม

ลองนึกภาพว่า เดิมทีตารางฐานข้อมูลของ Pivot Table เป็นข้อมูลการขายสินค้าของทุกสาขาในแต่ละวัน มีจำนวนทั้งสิ้น 200,000 บรรทัด (สองแสนบรรทัด)
ตารางนี้มีรหัสสินค้า แต่ไม่มีประเภทสินค้า (Product Category) การจะมีประเภทสินค้าในตารางนี้ได้แปลว่าต้องเพิ่มคอลัมน์แล้วไปดึงข้อมูลมาจากอีกตารางนึง
(ซึ่งอาจใช้ VLOOKUP หรือ INDEX+MATCH ดึงมาก็ได้)
เพิ่มข้อมูลแค่คอลัมน์เดียวเหมือนจะไม่มีอะไร แต่อย่าลืมว่าข้อมูลนี้มีสองแสนบรรทัด การเพิ่มข้อมูลแม้เพียงคอลัมน์เดียวแปลว่าต้องเพิ่มสองแสนเซลล์!

แล้วถ้าต้องการข้อมูล Brand ของสินค้าด้วยล่ะ?
แปลว่าต้องสร้างคอลัมน์เพิ่มแล้วไปดึงข้อมูล Brand

แล้วถ้าต้องการเพิ่มข้อมูล Product Type ล่ะ?
แปลว่าก็ต้องสร้างคอลัมน์เพิ่มแล้วไปดึงข้อมูล Product Type

แล้วถ้าต้องการเพิ่มข้อมูล Store Type, Country, Continent, Year, Month, Quarter, Week ล่ะ?
แปลว่าต้องเพิ่มคอลัมน์เช่นกัน

สุดท้ายอาจต้องเพิ่มทั้งหมด 20 คอลัมน์ คอลัมน์ละ 200,000 เซลล์ แปลว่าต้องเพิ่มข้อมูล 4,000,000 เซลล์ !
การเพิ่มข้อมูลขนาดนี้ย่อมมีผลต่อขนาดไฟล์ และทำให้ไฟล์คำนวณช้าลงแน่นอน

แต่ปัญหานี้จะไม่เกิดขึ้นถ้าใช้ Power Pivot
เพราะ Power Pivot สร้าง Pivot Table จากหลายตารางที่มีความสัมพันธ์กัน
เราเรียก หลายตารางที่มีความสัมพันธ์กัน ว่า Data Model ซึ่งมีหน้าตาประมาณนี้

เรียกว่ายก MS Access มาไว้ใน MS Excel เลยก็ว่าได้

Pivot Table ของ Power Pivot คือ Pivot Table ที่สร้างจาก Data Model
(บางคนเรียกว่า Data Model Pivot Table)
จากเคสเมื่อกี้ จึงไม่จำเป็นต้องเพิ่ม 20 คอลัมน์ขึ้นมาเลย เพราะข้อมูลเหล่านั้นอยู่ในตาราง Product, Store, Dates ทั้งหมดแล้ว
ไฟล์จะมีขนาดเล็กลง แถมคำนวณได้เร็วขึ้นหลายเท่า ^__^

นอกจากนี้ยังสามารถดึงข้อมูลจากภายนอกได้ (ดึงจากโปรแกรมฐานข้อมูล เช่น SQL Server, MS Access, ไฟล์ txt, หรือข้อมูลจากไฟล์ Excel อื่น) และยังทำงานร่วมกับ Power Query ได้ดี
ชีวิตดี๊ดีเนอะ ^^

3 เขียนสูตรคำนวณได้ซับซ้อนกว่า Calculated Fields

การสร้างสูตรคำนวณใน Pivot Table หรือ Calculated Fields นั้น สร้างได้เฉพาะการคำนวณแบบง่ายๆเช่น บวก ลบ คูณ หาร
ไม่สามารถใช้กับการคำนวณแบบมีเงื่อนไข หรือไม่สามารถใส่ IF ลงไปใน Calculated Fields ได้
(ถึงใส่ IF ได้ก็คำนวณผิด)

อีกทั้งการใช้ Show Value As ยังใช้ได้เฉพาะกรณีไม่มีเงื่อนไข หรือไม่มีการใส่ฟิลเตอร์ใดๆใน Pivot Table ถ้าใส่ฟิลเดอร์ลงไปแม้จะแสดงผลได้ แต่ตัวเลขจะผิดทันที

นอกจากนี้ การคำนวณ Actual, Last Year ใน Pivot Table นั้นยากมากในทางปฏิบัติ เพราะ Actual, Last Year สัมพันธ์กับปีปัจจุบัน
ถ้าปีปัจจุบันคือ 2019 Actual คือ 2019 และ Last Year คือ 2018
แต่ถ้าปีปัจจุบันคือ 2020 Actual จะกลายเป็น 2020 และ Last Year จะกลายเป็น 2019
แนวทางแก้ไขคือ ต้องสร้างคอลัมน์ขึ้นมาช่วยคำนวณ ซึ่งทำให้ไฟล์ใหญ่และคำนวณช้า

แต่ Power Pivot จะไม่เจอปัญหานี้เลย เพราะการสร้างสูตรคำนวณใน Power Pivot นั้นใช้สิ่งที่เรียกว่า Measure
Measure คืออะไร?
ถ้าอธิบายง่ายๆ Measure ก็คือ Calculated Fields ของ Data Model แต่สามารถเขียนสูตรแบบซับซ้อนได้ (แน่นอนว่าใส่ IF ได้ด้วย)

จะสร้างกี่ Measure ก็ไม่มีปัญหา สามารถนำมาใช้ได้กับทุก Pivot Table

สูตรที่ใช้ใน Measure เรียกว่าสูตร DAX
DAX ย่อมาจากคำว่า Data Analysis Expression ซึ่งเป็นสูตรที่ความคล้ายกับฟังก์ชันใน Excel มากๆ

ฟังก์ชันที่มีใน Excel ส่วนใหญ่ก็มีใน DAX แถมยังมีฟังก์ชันเจ๋งๆอีกมากมาย (ที่ไม่มีใน Excel) เช่น
– SUMX
– DISTINCTCOUNT
– CALCULATE
– ALL
– ALLSELECTED

นอกจากนี้ยังมีฟังก์ชันช่วยคำนวณเกี่ยวกับเวลา เช่น Actual, Last Year แบบอัตโนมัติได้ด้วย เช่น
– SAMEPERIODLASTYEAR
– DATEADD
– DATESYTD
ฟังก์ชันกลุ่มนี้เรียกว่า Time Intelligence ซึ่งเป็นฟังก์ชันที่เจ๋งมากๆ เรียกได้ว่าถ้าต้องการผลลัพธ์แบบเดียวกันใน Excel อาจต้องเขียนสูตรยาว 3-4 บรรทัด
แต่ถ้าใช้ฟังก์ชัน Time Intelligence ของ DAX เขียนสูตรแค่ครึ่งบรรทัด จบ!

สูตร DAX นอกจากใช้ใน Power Pivot ได้แล้ว ยังใช้ในโปรแกรม Power BI Desktop ได้ด้วย !
เรียกว่า ยิงปืนนัดเดียว ได้นกสองตัว ^__^
(DAX ใช้ในโปรแกรม SSAS (SQL Server Analysis Service) ได้ด้วยเช่นกัน)

อย่างไรก็ตาม ถ้าใครเคยใช้สูตร DAX ใน Power BI จะพบว่า DAX ใน Power BI ทำงานเร็วกว่า DAX ใน Power Pivot
เพราะลึกๆแล้ว DAX ใน Power Pivot นั้นไม่ใช่ DAX จริง แต่จะเป็นสูตรที่เรียกว่า MDX ซึ่งคำนวณได้ช้ากว่า
แม้ข้างในจะเป็น MDX แต่เวลาเขียน ก็ใช้โครงสร้าง DAX ตามปกติครับ ^__^

4 เซ็ตฟอร์แมตตัวเลขแค่ครั้งเดียว ใช้ได้ทุกตาราง

ปัญหาหนึ่งที่ทำให้ผู้ใช้งาน Pivot Table เบื่อหน่ายมากก็คือ
เวลาสร้าง Pivot Table ใหม่ ต้องเซ็ตฟอร์แมตต้วเลขทุกครั้ง

Pivot Cache จะไม่จำว่าตัวเลขจากคอลัมน์นี้ต้องใส่คอมมา (comma) หรือตัวเลขจากคอลัมน์นี้ต้องไม่มีจุดทศนิยม
แม้ว่าตอนสร้าง Pivot Table ก่อนหน้านี้จะปรับฟอร์แมตตัวเลขให้ใส่คอมมา และปรับทศนิยมเป็นศูนย์ตำแหน่งแล้วก็ตาม แต่พอสร้าง Pivot Table ใหม่ ก็ต้องมานั่งเซ็ตเหมือนเดิมทุกครั้ง
แต่ปัญหานี้จะหมดไปเมื่อใช้ Power Pivot เพราะ Power Pivot สามารถเซ็ตรูปแบบของตัวเลขได้เลย เซ็ตแค่ครั้งเดียว เวลาสร้างตารางใหม่จะได้ฟอร์แมตแบบนี้เป๊ะทุกครั้ง

จะเซ็ตฟอร์แมตตัวเลขใน Measure ก็ได้

หรือเซ็ตทั้งคอลัมน์ใน Data Model ก็ได้เช่นกัน

ทำครั้งเดียว จบ
ชีวิตดี๊ดี ^__^

5 สร้าง Pivot Chart โดยไม่พ่วง Pivot Table

ถ้าใครเคยสร้างแดชบอร์ด (Dashboard) ด้วย Pivot Table

จะพบว่าหนึ่งในปัญหาที่ทำให้หงุดหงุดมากก็คือ
เวลาสร้างกราฟหรือ Pivot Chart ต้องสร้าง Pivot Table พ่วงขึ้นมาด้วย จะสร้างเฉพาะ Pivot Chart อย่างเดียวไม่ได้

ปัญหาก็คือ เวลาสร้างแดชบอร์ด เราต้องการแค่กราฟ พอมี Pivot Table พ่วงมาด้วย ก็ต้องหา “ที่ซ่อน” ให้ Pivot Table
ซึ่ง “ที่ซ่อน” นั้นอาจจะเป็น Pivot Table ในชีตอื่น (ที่ไม่ใช่ชีแดชบอร์ด) แบบนี้

แต่ถ้าสร้าง Pivot Chart ด้วย Power Pivot นั้นชีวิตจะดีขึ้นมาก เพราะสามารถสร้างเฉพาะ Pivot Chart อย่างเดียวได้เลย ไม่ต้องพ่วง Pivot Table มาให้เปลืองใจ

จะสร้างทีเดียว 2 กราฟ หรือ 4 กราฟก็ยังได้

ชีวิต ดี๊ดี (อีกแล้ว) ^^

6 ขนาดไฟล์เล็กกว่ามาก

ถ้าใครเคยสร้าง Pivot Table จากตารางข้อมูลที่มีการสร้างคอลัมน์เพิ่มเพื่อดึงข้อมูลจากตารางอื่น จะพบว่าไฟล์มีขนาดใหญ่มาก
เผลอๆจะใหญ่จนส่งเมลไม่ผ่าน T_T

แต่ปัญหานี้ถูกแก้ได้ด้วย Power Pivot
เพราะนอกจาก Power Pivot จะใช้ Data Model ทำให้ไม่จำเป็นต้องสร้างคอลัมน์เพิ่มเพื่อดึงข้อมูลแล้ว
Power Pivot ยังมีเทคโนโลยีที่เรียกว่า xVelocity (หนังสือบางเล่มใช้ชื่อว่า Columnar Database หรือ VertiPaq) ซึ่งเป็นเทคโนโลยีเฉพาะของไมโครซอฟท์ ทำให้ไฟล์มีขนาดเล็กลงอย่างมาก
จากไฟล์ขนาด 30 MB อาจเหลือแค่ 5 MB !

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

อ้อ! การสร้าง Measure จะช่วยลดขนาดของไฟล์ และทำให้คำนวณเร็วขึ้นด้วยนะครับ ^__^

---

ถ้าเปรียบเทียบหน้าตาของ Pivot Table ที่สร้างจาก Power Pivot กับ Pivot Table แบบธรรมดานั้น ขอบอกว่าไม่ต่างกันเลย
ความแตกต่างก็คือทั้ง 6 ข้อที่เล่าให้ฟังด้านบนนั่นเอง

จากทั้ง 6 ข้อจะพบว่า Power Pivot เจ๋งกว่า Pivot Table มาก
แต่ Power Pivot มีจุดอ่อนที่สำคัญมากข้อนึงคือ
เหมาะกับ Office 365 หรือ Excel 2016/ 2019 เท่านั้น
(Excel 2016/ 2019 มีเฉพาะบางเวอร์ชัน เช่น Professional Plus)

Excel 2010 แม้จะลง Add-Ins Power Pivot ได้ แต่ถ้าใช้ไปสักพักจะพบว่ามีปัญหาจุกจิกเยอะมาก
Excel 2013 ก็เช่นกัน แม้บางรุ่นจะมี Power Pivot (เช่น Excel 2013 Professional Plus) แต่พบว่ามีปัญหาจุกจิกและบักส์เยอะมากจนไม่แนะนำให้ใช้เช่นกัน

ถ้าสร้าง Power Pivot แล้วส่งไฟล์ให้คนที่ใช้ Excel เวอร์ชันที่ไม่มี Power Pivot ล่ะ จะเป็นยังไง?
ขอตอบว่า คนที่รับจะเปิดไฟล์ได้ แต่จะปรับแก้อะไรไม่ได้เลย
พูดง่ายๆก็คือเหมือนได้ Pivot Table ที่เป็นแบบ Paste Value ไปนั่นเอง
(ซึ่งอาจจะเป็นข้อดีในบางมุมก็ได้)

โดยส่วนตัวแล้วอยากให้ใช้ Power Pivot กันเยอะๆ (ถ้าทั้งองค์กรใช้ Office 365)
เพราะอะไรน่ะหรือครับ?
เพราะผมลองแล้ว พบว่ามันเจ๋งมากๆ
มากขนาดไหนน่ะหรือครับ?
มากจนอยากบอกต่อ จนเป็นที่มาของบทความนี้

อยากให้ลองใช้กันครับ ^__^

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^