---
title: วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula
url: https://www.thepexcel.com/break-pivot-layout-olap-cube/
type: post
date: 2017-05-03
updated: 2020-04-01
author: Sira Ekabut
categories: [Pivot Table]
tags: [format, pivot, Data Model]
---

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

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

 

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

 

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

 

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

 

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

 ![1](https://www.thepexcel.com/wp-content/uploads/2017/05/error.png) 

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

 

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

 
1. **Copy Pivot แล้ว Paste Value เพื่อเอาไปทำรายงาน** : วิธีนี้ไม่แนะนำเป็นอย่างยิ่ง เพราะเสียความสามารถในการ Refresh ข้อมูลไปเลย ควรทำเฉพาะกรณีที่คิดว่าทำครั้งเดียวในชีวิต ไม่ต้องทำอีกแล้ว 555
2. **ใช้ GETPIVOTDATA** : ปกติเวลากด = แล้วคลิ๊กไปในพื้นที่ Pivot แต่ละ Cell มันจะดึงข้อมูล Cell นั้นมา โดยสร้างสูตรยาวๆ ที่ขึ้นต้นด้วยคำว่า GETPIVOTDATA มาให้ด้วย  
  ![2](https://www.thepexcel.com/wp-content/uploads/2017/05/getpivot.png) 
    - ซึ่งข้อดีคือ ไม่ว่า Pivot Table ต้นทางจะย้ายไปไหน หรือมีการ Filter Row/Column Label บางอย่างจนข้อมูลเลื่อนไปที่อื่น เจ้า GETPIVOTDATA จะยังเอาข้อมูลที่ถูกต้องมาให้
    - ข้อเสียคือ จะต้องกด = จิ้มทีละ Cell หรือเขียนสูตรทีละ Cell ซึ่งลำบากมากกว่าจะได้ครับ
3. **กด = แล้วจิ้ม แบบ Cell Reference ปกติ** : บางคนไม่ชอบ GETPIVOTDATA เลยไปเลือก Option ให้ Excel ไม่ต้องสร้างเจ้าฟังก์ชันนั้นขึ้นมาให้ (ติ๊ก Generate GetPivotData ออก) โดยอยากให้จิ้มแล้วเป็น Cell Reference ธรรมดาแทน จะได้เขียน/copy สูตรง่ายๆ  
  ![3](https://www.thepexcel.com/wp-content/uploads/2017/05/getpivot2.png) 
    - ข้อดี : เขียนง่ายมาก แค่ = แล้วจิ้ม จากนั้น Copy มาได้ตามปกติ  
      ![4](https://www.thepexcel.com/wp-content/uploads/2017/05/getpivot3.png)
    - ข้อเสีย : เสี่ยงต่อการที่ 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 ด้วย  
  ![5](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-01.png)
2. สร้าง Pivot ให้สรุปข้อมูลตามปกติ เอาให้คล้ายรายงานที่ต้องการมากที่สุดก่อน  
  ![6](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-02.png)
3. ไปคลิ๊ก OLAP Tools -> Convert to Formulas…  
  ![7](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-03.png) 
    - เพียงเท่านี้ PivotTable ก็จะถูกแปลงเป็น Cell ที่เต็มไปด้วยสูตร CUBE ซึ่งข้อมูลแต่ละช่องจะแยกออกจากกัน  
      ![8](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-04.png)
4. จัด Layout ตามใจชอบ – คุณจะแทรกคอลัมน์ยังไง โยกข้อมูลยังไง ก็สามารถทำได้แล้ว  
  ![9](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-05.png)

 

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

 ![10](https://www.thepexcel.com/wp-content/uploads/2017/05/final-1024x432.png) 

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

 

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

 
1. ถ้ายังไม่มี PowerPivot ให้ไปโหลดมา install ก่อน => [Download ที่นี่](https://support.office.com/en-us/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045?ui=en-US&rs=en-US&ad=US)
2. Add Data เข้า Power Pivot โดยกดปุ่ม Create Linked Table  
  ![11](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-01fix.png)
3. สร้าง Pivot จากหน้าต่าง PowerPivot  
  ![12](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-02.png)
4. จากนั้นก็เหมือนแบบวิธี Data Model แล้วล่ะ จัดระเบียบแล้วกด Convert to Formulas โลด!  
  ![13](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-03.png)
5. Pivot จะถุกกระจายกลายเป็นสูตรที่แยกกันคนละช่องเช่นเดียวกับวิธีข้างบนครับ  
  ![14](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-04.png)

 

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

 

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

 ![15](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-07.png) ![16](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-08.png) 

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

 ![17](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-09.png) 

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

 

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

 

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

 

### Refresh ที่ Data Connections

 

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

 ![18](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-06.png) 

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

 

### Refresh ที่ PivotTables

 

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

 ![19](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-10.png) 

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

 ![20](https://www.thepexcel.com/wp-content/uploads/2017/05/olap-datamodel-11.png) 

## จบแล้ว

 

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

---

_Source: [https://www.thepexcel.com/break-pivot-layout-olap-cube/](https://www.thepexcel.com/break-pivot-layout-olap-cube/)_