วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula

จากที่ผมได้ Post ถามหยั่งเชิงใน Facebook ไปว่าใครสนใจวิธีทำลายข้อจำกัดเรื่องหน้าตาของ PivotTable บ้าง? ปราฏว่ามีคนให้ความสนใจเยอะมากๆ ดังนั้นผมเลยขอมาเขียนบทความอธิบายให้เล็กน้อยครับ

PivotTable นั้นสร้างง่ายมาก แต่จัด Layout ไม่ได้ดั่งใจ

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

แต่สิ่งที่มักจะเกิดขึ้นตามมาคือ คำพูดของหัวหน้าที่บอกว่า “รายงานนี้มันหน้าตาไม่สวยเลย ช่วยจัด xxx เว้นบรรทัด yyy แทรกคอลัมน์ zzz หน่อยสิ”

แล้วคุณก็คงตอบกลับไปแบบสุภาพว่า “อ๋อ ทำไม่ได้ครับ…มันเป็นข้อจำกัดของ PivotTable ครับหัวหน้า” ทั้งๆ ที่จริงๆ อาจอยากตะโกนตอบกลับไปว่า “PivotTable มันแทรกบรรทัดไม่ได้เฟ้ย!”

วิธีจัด Layout ที่อาจพอรู้กันอยู่แล้ว

แต่ถ้าคุณอยากจะทำให้รายงานมันสวยได้ดั่งใจจริงๆ ก็มีวิธีที่หลายคนอาจจะรู้อยู่แล้ว ดังนี้

1. Copy Pivot แล้ว Paste Value เพื่อเอาไปทำรายงาน : วิธีนี้ไม่แนะนำเป็นอย่างยิ่ง เพราะเสียความสามารถในการ Refresh ข้อมูลไปเลย ควรทำเฉพาะกรณีที่คิดว่าทำครั้งเดียวในชีวิต ไม่ต้องทำอีกแล้ว 555
2. ใช้ GETPIVOTDATA : ปกติเวลากด = แล้วคลิ๊กไปในพื้นที่ Pivot แต่ละ Cell มันจะดึงข้อมูล Cell นั้นมา โดยสร้างสูตรยาวๆ ที่ขึ้นต้นด้วยคำว่า GETPIVOTDATA มาให้ด้วย
   

   

   • ซึ่งข้อดีคือ ไม่ว่า Pivot Table ต้นทางจะย้ายไปไหน หรือมีการ Filter Row/Column Label บางอย่างจนข้อมูลเลื่อนไปที่อื่น เจ้า GETPIVOTDATA จะยังเอาข้อมูลที่ถูกต้องมาให้
   • ข้อเสียคือ จะต้องกด = จิ้มทีละ Cell หรือเขียนสูตรทีละ Cell ซึ่งลำบากมากกว่าจะได้ครับ
3. กด = แล้วจิ้ม แบบ Cell Reference ปกติ : บางคนไม่ชอบ GETPIVOTDATA เลยไปเลือก Option ให้ Excel ไม่ต้องสร้างเจ้าฟังก์ชันนั้นขึ้นมาให้ (ติ๊ก Generate GetPivotData ออก) โดยอยากให้จิ้มแล้วเป็น Cell Reference ธรรมดาแทน จะได้เขียน/copy สูตรง่ายๆ
   

    

   • ข้อดี : เขียนง่ายมาก แค่ = แล้วจิ้ม จากนั้น Copy มาได้ตามปกติ
     

     

   • ข้อเสีย : เสี่ยงต่อการที่ PivotTable เลื่อนจากที่เดิม ทำให้ได้ข้อมูลที่ผิดกลับไปโดยไม่รู้ตัว
4. เลิกใช้ Pivot หันมาเขียนสูตรแทน : วิธีนี้เรียกว่าเปลี่ยนแนวโดยสิ้นเชิง เขียนสูตรเอาเองดีกว่า ไม่ง้อ Pivot ก็ได้ ไม่ว่าจะเป็นสูตรง่ายๆ อย่าง SUMIFS หรือจะใช้ Data Table มาช่วย หรืออาจจะใช้ Array Formula กรณีที่การคำนวณซับซ้อน
   • ข้อดี : มีความยืดหยุ่นสูงกว่า Pivot
   • ข้อเสีย : เขียนสูตรยาก (บางคนเจอตรงนี้ก็ยอมแพ้แล้ว) และอาจคำนวณช้ากว่า Pivot

แต่วันนี้ผมมีอีกวิธีมาแนะนำครับ นั่นคือ ใช้ความสามารถที่เรียกว่า OLAP CUBE Formula

วิธี OLAP CUBE Formula

วิธีนี้ดีตรงที่ ไม่ต้องเขียนสูตรเอง และยังสามารถอัปเดทผลลัพธ์ได้เมื่อข้อมูลต้นทางเปลี่ยน เช่นเดียวกับ PivotTable เลย แต่ผมจะไม่ขอพูดทฤษฏี ณ ตอนนี้แล้วกันเอาเป็นว่าบทความนี้มาลองทำกันเลยนะครับ

ผมจะขอแบ่งออกเป็น 2 วิธี คือ 1. ใช้ Data Model (Excel 2013 ขึ้นไป) 2. ใช้ PowerPivot (ซึ่งใช้ได้ตั้งแต่ Excel 2010 แต่ต้องลง Add-in) ซึ่งวิธีแรกน่าจะง่ายกว่าสำหรับคนส่วนใหญ่ครับ

ใช้ Data Model (Excel 2013 ขึ้นไป)

มี Step ประมาณนี้

1. ตอนจะสร้าง PivotTable ให้ติ๊กช่อง Add to Data Model ด้วย
   

   

2. สร้าง Pivot ให้สรุปข้อมูลตามปกติ เอาให้คล้ายรายงานที่ต้องการมากที่สุดก่อน
   

   

3. ไปคลิ๊ก OLAP Tools -> Convert to Formulas…
   

   

    
   • เพียงเท่านี้ PivotTable ก็จะถูกแปลงเป็น Cell ที่เต็มไปด้วยสูตร CUBE ซึ่งข้อมูลแต่ละช่องจะแยกออกจากกัน
     

     

4. จัด Layout ตามใจชอบ – คุณจะแทรกคอลัมน์ยังไง โยกข้อมูลยังไง ก็สามารถทำได้แล้ว
   

   

จะเห็นว่า ผมจะปรับแต้งหน้าตายังไงก็ได้เลย ซึ่งอิสระขึ้นมากๆ ครับ ^^

ใช้ PowerPivot (Excel 2010 ขึ้นไป)

มี Step ประมาณนี้

1. ถ้ายังไม่มี PowerPivot ให้ไปโหลดมา install ก่อน => Download ที่นี่
2. Add Data เข้า Power Pivot โดยกดปุ่ม Create Linked Table
   

   

3. สร้าง Pivot จากหน้าต่าง PowerPivot
   

   

4. จากนั้นก็เหมือนแบบวิธี Data Model แล้วล่ะ จัดระเบียบแล้วกด Convert to Formulas โลด!
   

   

5. Pivot จะถุกกระจายกลายเป็นสูตรที่แยกกันคนละช่องเช่นเดียวกับวิธีข้างบนครับ
   

   

ผลลัพธ์ยังสามารถผูกกับ Slicer ได้เช่นเดิม

หากตอนแรก Pivot ของเรามีการผูก Slicer ไว้ หลังจากแปลงเป็น CUBE Formula แล้ว Slicer ก็ยังทำงานได้นะครับ

หลังกด Slicer ผลลัพธ์ก็จะถูกตัดกรองเช่นเดียวกับ PivotTable ปกติเลย!!

แปลว่า เราสามารถเอาวิธีการนี้ ไปสร้าง Dashboard เท่ๆ ได้เลยนะ!!

CUBE เป็นสูตร แต่ยังต้องกด Refresh เช่นเดียวกับ Pivot

เนื่องจากสูตรแบบ CUBE เป็นการเชื่อมต่อกับฐานข้อมูล ดังนั้นเวลาทำงานจริง หากข้อมูลต้นทางเปลี่ยนไป จะต้องมีการกด Refresh ก่อน ผลลัพธ์จึงจะเปลี่ยน เช่นเดียวกับ Pivot Table ซึ่งเรา Refresh ได้ 2 ที่ คือ

Refresh ที่ Data Connections

ซึ่งสามารถ Refresh ได้ที่เครื่องมือ Data -> Refresh All ครับ

อย่าลืม!! ถ้าเราไม่ Refresh ผลลัพธ์จะไม่เปลี่ยนนะ

Refresh ที่ PivotTables

แต่ถ้าใครไม่ชอบวิธีนี้ จะกด Refresh ที่ PivotTables ก็ได้นะ แต่ต้องทำการเก็บ Pivot ไว้อย่างน้อยอันนึงก่อนที่จะแปลงเป็น OLAP Formula นะครับ (ถ้าแปลงไปแล้ว จะกดที่กด Refresh แบบ Pivot ไม่เจอ)

คราวนี้เราจะ Refresh ที่ Pivot ได้ตามปกติแล้ว

จบแล้ว

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