Power Pivot + DAX เบื้องต้น — สร้าง Data Model และ Relationships ให้ Excel ทำงานหลายตาราง
ถึงจุดที่ PivotTable ธรรมดาเริ่มไม่พอ
คุณเคยเจอสถานการณ์แบบนี้ไหมครับ ข้อมูลการขายอยู่คนละไฟล์กับข้อมูลสินค้า อยากทำ PivotTable สรุปยอดขายรายหมวด ก็ต้องไปค่อยๆ VLOOKUP ดึงชื่อสินค้า หมวดหมู่ มาวางไว้ในตารางเดียวให้บวมโต กว่าจะได้ตารางที่สรุปได้ก็เสียเวลาไปครึ่งวัน แถมพอข้อมูลใหม่เข้ามา ก็ต้องมาลากสูตรใหม่ให้ยุ่งยากอีก
Power Pivot เกิดขึ้นมาเพื่อแก้ปัญหาแบบนี้โดยตรงครับ มันเปลี่ยนวิธีคิดจากการ “รวมทุกอย่างไว้ในตารางเดียว” มาเป็น “ให้ Excel รู้จักความสัมพันธ์ระหว่างตาราง แล้วคำนวณข้ามตารางให้เลย” แนวคิดนี้เรียกว่า Data Model ซึ่งเป็นพื้นฐานของเครื่องมือวิเคราะห์ข้อมูลระดับมืออาชีพใน Excel
บทความนี้ผมจะพาคุณรู้จัก Data Model, Relationships และ DAX เบื้องต้น พร้อมตัวอย่างจริงที่ใช้ต่อยอดจาก PivotTable และ Power Query ที่เคยเขียนไปก่อนหน้า โดยไม่ต้องเขียนโค้ดหรือติดตั้งอะไรเพิ่มเติม เพราะ Power Pivot เป็นฟีเจอร์ที่มาพร้อม Excel อยู่แล้ว
เปิดไฟล์ตัวอย่างทำไปด้วยนะครับ ผมเตรียมตารางใบสั่งซื้อและตารางสินค้าไว้ให้ลองเล่นแล้ว จะได้เห็นว่าข้อมูลสองตารางแยกกันอยู่ แต่จะสรุปข้ามกันได้อย่างไร
บทความนี้เหมาะกับคนที่พอใช้ PivotTable และ Power Query เป็นแล้ว แต่เริ่มเจองานที่ข้อมูลซับซ้อนขึ้น เช่น ต้องดึงข้อมูลจากหลายตารางมาสรุปในรายงานเดียว ถ้าคุณกำลังคิดว่า “ทำไมต้อง VLOOKUP ทุกครั้ง” บทความนี้คือคำตอบครับ
PivotTable ธรรมดากับ Power Pivot ต่างกันตรงไหน
ความต่างหลักๆ อยู่ที่แหล่งข้อมูลครับ PivotTable ธรรมดาเรามักสร้างจากตารางเดียว หรือหลายตารางที่ “รวมกันแล้ว” ด้วยสูตร ส่วน Power Pivot ทำงานบน Data Model ซึ่งเป็นพื้นที่เก็บข้อมูลแบบ In-Memory ที่ให้เราใส่ตารางหลายตารางเข้าไปพร้อมกัน โดยไม่ต้องมารวมหรือทำ VLOOKUP ให้ตารางบวม
Data Model คืออะไร? มองง่ายๆ มันคือ “ฐานข้อมูลจิ๋ว” ที่ฝังอยู่ในไฟล์ Excel ครับ เราเพิ่มตารางเข้าไปได้หลายตาราง เช่น ตารางใบสั่งซื้อ ตารางสินค้า ตารางลูกค้า แล้วลากเส้นเชื่อมความสัมพันธ์ระหว่างกัน เมื่อถึงเวลาสรุป ก็ให้ PivotTable อ่านจาก Data Model ได้โดยตรง
ข้อดีที่เห็นชัดคือไม่ต้องคัดลอกข้อมูลซ้ำซ้อน ตารางใบสั่งซื้อมีแค่รหัสสินค้า รหัสลูกค้า ก็พอแล้ว ส่วนชื่อสินค้า หมวดหมู่ อยู่ในตารางสินค้าของมันเอง PivotTable จะดึงมาให้อัตโนมัติผ่านความสัมพันธ์ครับ
การเริ่มต้นใช้งานทำไม่ยากครับ ถ้าคุณยังไม่เห็นแท็บ Power Pivot ใน Excel ให้ไปที่ 1.File > 2.Options > 3.Add-ins เลือก COM Add-ins แล้วติ๊ก 4.Microsoft Power Pivot for Excel พอกดปุ่ม 5.Go แล้ว6.ติ๊กหน้า Analysis ToolPak และกดปุ่ม 7.OK จากนั้นแท็บ Power Pivot ก็จะโผล่ขึ้นมาใน Ribbon ทันที
💡 เคล็ดลับ: ถ้าข้อมูลคุณเป็นตารางเดียวจบ ไม่ซับซ้อน PivotTable ธรรมดาก็เพียงพอแล้วครับ ไม่จำเป็นต้องดึง Power Pivot เข้ามาให้เปลือง เริ่มใช้เมื่อมีตั้งแต่ 2 ตารางขึ้นไปและต้องสรุปข้ามตารางบ่อยๆ
การเพิ่มตารางเข้า Data Model ทำได้สองทางหลักๆ ครับ ทางแรกคือใช้คำสั่ง Add to Data Model จากแท็บ Power Pivot หรือจาก Table Tools ทางที่สองคือผ่าน Power Query ซึ่งเมื่อโหลดข้อมูลเสร็จให้เลือก Load To แล้วติ๊ก Add this data to the Data Model ได้เลย วิธีนี้เหมาะกับงานที่ต้อง Transform ข้อมูลก่อนด้วยครับ
ในตัวอย่างของเราในภาพด้านล่าง เราจะ
- เลือกช่วง (range) ข้อมูลที่เราจะใช้
- กดปุ่ม Add to Data Model
- เช็คว่าเป็นช่วง (range) ที่เราเลือก หากข้อมูลเรามีหัวตารางเหมือนตัวอย่าง ให้ติ๊ก My data has headers
- กดปุ่ม OK
อีกข้อดีที่หลายคนมองข้ามคือเรื่องความจุครับ Data Model บีบอัดข้อมูลในหน่วยความจำ ทำให้เราทำงานกับตารางที่มีหลักแสนหลักล้านแถวได้คล่องกว่า PivotTable ธรรมดาที่อ่านจากชีตโดยตรง นี่คือเหตุผลที่งาน BI หรือ Data Analytics ระดับองค์กรนิยมใช้ Data Model เป็นแกนกลางของรายงานครับ
สร้าง Relationships เชื่อมตารางเข้าด้วยกัน
หัวใจของ Data Model คือ Relationships หรือความสัมพันธ์ระหว่างตารางครับ ความสัมพันธ์ที่พบบ่อยที่สุดคืองแบบ One-to-Many (1 ต่อหลาย) เช่น ตารางสินค้าหนึ่งรายการ มีใบสั่งซื้อหลายใบที่อ้างถึง — สินค้า 1 ตัว ถูกสั่งซื้อหลายครั้ง
ตัวอย่างง่ายๆ ในไฟล์ตัวอย่างครับ:
| ตาราง | ฟิลด์หลัก | บทบาท |
|---|---|---|
| ใบสั่งซื้อ (Orders) | รหัสสินค้า, จำนวน, ราคา | ตารางฝั่ง Many |
| สินค้า (Products) | รหัสสินค้า, ชื่อ, หมวดหมู่ | ตารางฝั่ง One |
ลองนึกภาพร้านค้าปลีกครับ ตารางสินค้ามีรหัสสินค้าไม่ซ้ำกัน เช่น PA-001 กาแฟดำ PA-002 ชาเขียว ส่วนตารางใบสั่งซื้อมีหลายแถวที่อ้างถึง PA-001 ซ้ำๆ เพราะลูกค้าซื้อกาแฟหลายครั้ง ความสัมพันธ์แบบ 1 ต่อหลายจึงเกิดขึ้น: สินค้า 1 รหัส ถูกอ้างถึงในใบสั่งซื้อหลายใบ
สมมติเดือนนี้มีใบสั่งซื้อ 20 ใบ อ้างถึงสินค้าแค่ 6 รายการ ถ้าใช้วิธีเดิมคุณต้องคัดลอกชื่อสินค้ากับหมวดหมู่ลงไปในทุกใบสั่งซื้อให้ซ้ำกัน 20 แถว แต่ด้วย Data Model คุณแค่เก็บตารางสินค้า 6 แถวไว้ แล้วให้ความสัมพันธ์จัดการส่วนที่เหลือเองครับ
การลากเส้นใน Diagram View จะเห็นลูกศรชี้จากฝั่ง One ไปฝั่ง Many ให้เรารู้ทันทีว่าตารางไหนเป็นตัวหลัก ตัวไหนเป็นตัวอ้างอิง ถ้าลากผิดด้าน Excel จะแจ้งเตือนและไม่ยอมสร้างความสัมพันธ์ให้ ซึ่งช่วยป้องกันความผิดพลาดตั้งแต่แรกครับ
ถ้าเป็นงาน HR ก็คล้ายกันครับ เช่น ตารางพนักงานหนึ่งคน อยู่แผนกเดียว แต่ตารางบันทึกเวลาทำงานมีหลายแถวต่อพนักงานหนึ่งคน เราก็สร้างความสัมพันธ์ระหว่างรหัสพนักงานของสองตารางได้แบบเดียวกัน
การสร้างความสัมพันธ์ใน Power Pivot ทำได้ 2 ทางครับ: เข้าแท็บ Power Pivot > Manage แล้วไปที่ Diagram View จะเห็นตารางเป็นกล่องๆ ให้ลากจากคอลัมน์รหัสสินค้าของตารางสินค้าไปหาคอลัมน์รหัสสินค้าของตารางใบสั่งซื้อ หรือจะใช้คำสั่ง Create Relationship แล้วเลือกตารางกับคอลัมน์ให้ตรงกันก็ได้
⚠️ ข้อควรระวัง: ฟิลด์ที่ใช้เชื่อมต้องมีค่าตรงกัน เช่น รหัสสินค้าในตารางสินค้าต้องเป็นตัวเลขเหมือนกับรหัสสินค้าในใบสั่งซื้อ ถ้าฝั่งหนึ่งเป็นตัวเลข อีกฝั่งเป็นข้อความ จะเชื่อมไม่ติดหรือได้ผลลัพธ์แปลกๆ ครับ
เมื่อสร้างความสัมพันธ์เสร็จ การสร้าง PivotTable จาก Data Model จะเห็นตารางทั้งสองอยู่ในรายการฟิลด์พร้อมกัน คราวนี้ลากหมวดหมู่จากตารางสินค้าไปวางเป็นแถว และลากยอดรวมจากตารางใบสั่งซื้อไปวางเป็นค่า ก็ได้สรุปยอดขายรายหมวดโดยไม่ต้อง VLOOKUP สักสูตรครับ
DAX เบื้องต้น สามฟังก์ชันที่ควรรู้
เมื่อทำงานบน Data Model สูตรที่ใช้คำนวณไม่ใช่ Excel Formula ธรรมดา แต่เป็น DAX (Data Analysis Expressions) ครับ หน้าตาคล้ายสูตร Excel แต่คิดต่างกันตรงที่มันทำงานบน “บริบท” ของข้อมูล ไม่ใช่อ่านค่าเซลล์ตรงๆ เริ่มจากสามฟังก์ชันที่ใช้บ่อยที่สุด
CALCULATE — ใช้แก้ไขบริบทการคำนวณ เช่น อยากได้ยอดขายเฉพาะหมวดเครื่องดื่ม ไม่ต้องกรอง PivotTable ด้วยมือ สูตรคือ =CALCULATE(SUM(Orders[จำนวนเงิน]), Products[หมวดหมู่]="เครื่องดื่ม") ผลลัพธ์คือยอดขายเฉพาะหมวดเครื่องดื่มเท่านั้น แม้ PivotTable จะแสดงยอดรวมทุกหมวดอยู่ก็ตาม ถ้าอยากได้หลายเงื่อนไขก็ใส่คั่นด้วยเครื่องหมายจุลภาค เช่น เฉพาะหมวดเครื่องดื่มในเดือนมิถุนายน ครับ
SUMX — ใช้คำนวณแบบวนทีละแถวแล้วรวมผล เช่น ยอดขายรวมที่คำนวณจาก จำนวน x ราคา ต่อใบสั่งซื้อ สูตรคือ =SUMX(Orders, Orders[จำนวน] * Orders[ราคา]) ต่างจาก SUM ตรงที่ SUMX วนแต่ละแถวคำนวณก่อนแล้วค่อยรวม ทำให้คิดยอดที่ต้องคูณสองฟิลด์ข้ามกันได้
RELATED — ใช้ดึงค่าจากตารางที่สัมพันธ์กันเข้ามาใช้ เช่น อยากได้หมวดหมู่ของสินค้าแต่ละรายการในตารางใบสั่งซื้อ สูตรคือ =RELATED(Products[หมวดหมู่]) เหมือน VLOOKUP เวอร์ชัน DAX แต่ไม่ต้องเขียนเลขชี้ช่วงให้ยุ่งยากครับ
ทั้งสามฟังก์ชันทำงานร่วมกับความสัมพันธ์ที่เราสร้างไว้ใน Data Model ครับ เช่น ถ้าอยากรู้ยอดขายรวมเฉพาะหมวดเครื่องดื่ม ก็เขียน CALCULATE ด้วยเงื่อนไขจากตารางสินค้าได้เลย โดยไม่ต้องกรองข้อมูลต้นทางให้ยุ่งยาก เพราะ DAX มองเห็นทุกตารางที่เชื่อมกันอยู่แล้ว
| ฟังก์ชัน | ใช้ทำอะไร | ตัวอย่าง |
|---|---|---|
| CALCULATE | เปลี่ยนเงื่อนไขการคำนวณ | ยอดขายเฉพาะหมวดเครื่องดื่ม |
| SUMX | คำนวณทีละแถวแล้วรวม | จำนวน x ราคา ต่อใบสั่งซื้อ |
| RELATED | ดึงค่าจากตารางข้างเคียง | หมวดหมู่สินค้าจากตาราง Products |
💡 เคล็ดลับ: จำง่ายๆ ว่า CALCULATE ใช้เปลี่ยนเงื่อนไขการรวม SUMX ใช้คูณหรือคำนวณข้ามคอลัมน์ทีละแถว RELATED ใช้ดึงข้อมูลจากตารางข้างเคียง สามตัวนี้ครอบคลุมงานสรุป 90% ของคนทำงานจริงครับ
ลองนึกภาพตามครับ สมมติคุณมี Measure ชื่อ ยอดขายรวม = SUM(Orders[จำนวนเงิน]) เมื่อวางลงใน PivotTable แล้วลากหมวดหมู่จากตารางสินค้าเป็นแถว Excel จะคำนวณยอดขายรวมให้ใหม่ทุกหมวดโดยอัตโนมัติผ่านความสัมพันธ์ นี่คือสิ่งที่ทำได้ยากถ้าใช้สูตร Excel ธรรมดาที่ต้องเตรียมตารางรวมก่อนล่วงหน้าครับ
Measure vs Calculated Column เลือกแบบไหนดี
DAX เขียนได้สองรูปแบบที่คนมักสับสนครับ: Measure และ Calculated Column
Measure คือสูตรที่คำนวณตอนเราใช้งาน เช่น วางลงใน Values ของ PivotTable แล้วมันจึงจะคำนวณตามบริบทที่แสดงอยู่ ตัวอย่างเช่น ยอดขายรวม, ยอดขายเฉลี่ยต่อใบ เหมาะกับค่าที่เป็น Aggregate หรือตัวชี้วัดที่ต้องเปลี่ยนตามการกรอง
การสร้าง Measure ทำได้โดยคลิกขวาที่ตารางใน Diagram View แล้วเลือก New Measure หรือใช้คำสั่งจากแท็บ Home ใน Power Pivot จากนั้นตั้งชื่อและเขียนสูตร เช่น ยอดขายรวม := SUM(Orders[จำนวนเงิน]) แล้วนำไปวางใน PivotTable ได้เลย ครับ
Calculated Column คือคอลัมน์ที่คำนวณตอนโหลดข้อมูลและเก็บค่าลงในตารางเลย เช่น คอลัมน์ “จำนวนเงิน” ที่คำนวณจาก จำนวน x ราคา เป็นคอลัมน์ถาวร เหมาะกับค่าที่ต้องการใช้ซ้ำเป็นแถวหรือใช้เป็นฟิลด์ใน PivotTable
หลักเลือกง่ายๆ ครับ:
| สถานการณ์ | ควรใช้ | เหตุผล |
|---|---|---|
| ยอดขายรวมที่ต้องเปลี่ยนตามการกรอง | Measure | คำนวณตามบริบทตลอด ไม่กินพื้นที่ |
| คอลัมน์คิดเงินต่อรายการ | Calculated Column | เป็นค่าประจำแถว ใช้ซ้ำได้ |
| ตัวชี้วัด KPI หลายมุมมอง | Measure | ยืดหยุ่น เปลี่ยนเงื่อนไขได้เรื่อยๆ |
| ต้องการฟิลด์สำหรับกรองหรือจัดกลุ่ม | Calculated Column | ใช้เป็น Rows หรือ Filter ได้ตรงๆ |
หลายคนสงสัยว่าแล้วจะรู้ได้ยังไงว่าสิ่งที่สร้างไปเป็นแบบไหน ดูง่ายๆ ครับ: Measure จะอยู่ในรายการฟิลด์ของ PivotTable โดยมีไอคอนรูปเครื่องคิดเลข ต่างจาก Calculated Column ที่จะไปโผล่เป็นคอลัมน์ในตาราง Data Model ปกติ ดูแค่นี้ก็แยกออกทันทีครับ
ถ้าอยากได้คำตอบสั้นๆ ให้ยึดหลักง่ายๆ แบบนี้ครับ: ถ้าค่าต้องรวมหรือต้องเปลี่ยนตามการกรอง ใช้ Measure; ถ้าค่าเป็นคุณสมบัติประจำแถว ใช้ Calculated Column เช่น ส่วนลดต่อรายการเป็นคุณสมบัติของใบสั่งซื้อแต่ละใบ ควรเป็น Calculated Column แต่ยอดขายรวมหรือยอดขายเฉลี่ยต่อลูกค้า ควรเป็น Measure เพราะต้องเปลี่ยนตามการกรองตลอดเวลา
⚠️ ข้อควรระวัง: อย่าใส่ Calculated Column เยอะเกินจำเป็น เพราะมันคำนวณและเก็บค่าตอนโหลดทุกครั้ง ถ้าตารางใหญ่เป็นแสนแถว แต่ละคอลัมน์ก็กินเวลาและหน่วยความจำ ถ้าเป็นค่าที่รวมแล้วค่อยคิด ใช้ Measure จะเบากว่าครับ
⚠️ข้อควรระวังเมื่อเริ่มใช้ DAX
DAX เป็นคอนเซปต์ใหม่สำหรับคนที่เคยชินกับสูตร Excel ธรรมดา ระวังสองเรื่องนี้บ่อยที่สุดครับ
อย่าคิดแบบ SUMIF หรือ VLOOKUP ตรงๆ DAX ทำงานบนบริบทของข้อมูล (Row Context กับ Filter Context) ไม่ใช่อ่านเซลล์เหมือนสูตรปกติ สูตรเดียวกันอาจให้ผลต่างกันตามแถวหรือการกรองที่แสดงอยู่ ดังนั้นเวลาผลลัพธ์แปลกๆ ให้ลองดูว่าบริบทที่กำลังแสดงส่งผลอะไรบ้าง
ระวังเรื่อง Row Context กับ Filter Context สลับกัน Row Context คือบริบทตอนวนทีละแถว (แบบ SUMX หรือ Calculated Column) ส่วน Filter Context คือบริบทที่ถูกกรองจาก PivotTable หรือ CALCULATE ถ้าใช้ฟังก์ชันผิดที่ มักได้ค่าซ้ำหรือค่าที่ไม่ตรงความหมาย เช่น ใส่ SUMX ใน Measure ที่มี Filter Context ซ้อนกัน อาจนับซ้ำได้ครับ
📌 ข้อควรจำ: เวลา DAX ให้ค่าผิด ให้ถามตัวเอง 3ข้อ: ตารางเชื่อมความสัมพันธ์ถูกไหม ฟิลด์ที่เป็นตัวเลขเป็นชนิดตัวเลขจริงไหม และสูตรคำนวณบนบริบทไหนอยู่ — ตรวจ 3 อย่างนี้เจอปัญหาเกือบทุกครั้งครับ
สรุปและชวนเล่นไฟล์ตัวอย่าง
Power Pivot เปลี่ยนวิธีทำงานกับข้อมูลหลายตารางของ Excel ไปอย่างสิ้นเชิงครับ แทนที่จะเสียเวลารวมตารางและ VLOOKUP ให้ยุ่งยาก เราแค่สร้าง Data Model ใส่ตารางที่เกี่ยวข้อง เชื่อม Relationships ให้เรียบร้อย แล้วใช้ DAX วัดผลตามที่ต้องการ — PivotTable ก็สรุปข้ามตารางได้ทันที
บทความนี้คุณได้รู้จักแล้วว่า PivotTable ธรรมดากับ Power Pivot ต่างกันยังไง Data Model คืออะไร วิธีสร้าง Relationships แบบ One-to-Many ฟังก์ชัน DAX เบื้องต้นอย่าง CALCULATE, SUMX, RELATED และวิธีเลือกใช้ Measure กับ Calculated Column ให้เหมาะกับงาน
ลองเปิดไฟล์ตัวอย่างแล้วเล่นตามดูนะครับ ในชีตข้อมูลดิบลองสังเกตว่าตารางใบสั่งซื้อกับตารางสินค้าแยกกันอยู่ ชีต Data Model ให้ลองลากเส้นความสัมพันธ์ดู และชีตผลลัพธ์จะมีตัวอย่าง Measure แบบจำลองให้เทียบตัวเลข ถ้าเข้าใจสามอย่างนี้ได้ ต่อไปคุณก็พร้อมต่อยอดไปเขียน DAX ซับซ้อนขึ้นเองได้แล้วครับ
ใครที่เริ่มจากบทความ Power Query มาก่อน จะเห็นว่าทั้งสองทำงานเป็นทีมกันครับ Power Query จัดการดึงข้อมูลและแปลงรูปให้สะอาด จากนั้นส่งต่อเข้า Data Model เพื่อสร้างความสัมพันธ์ แล้วค่อยใช้ DAX วัดผล — ครบวงจรในการทำรายงานวิเคราะห์ข้อมูลใน Excel โดยไม่ต้องพึ่งเครื่องมือภายนอกครับ 🚀