Thep Excel
GETPIVOTDATAฟังก์ชันดึงข้อมูลจาก PivotTable
GETPIVOTDATA ช่วยดึงข้อมูลจาก PivotTable โดยอ้างอิงชื่อฟิลด์และเงื่อนไข แทนที่จะอ้างอิงตำแหน่งเซลล์
.
เหมาะสำหรับการสร้างรายงานแบบไดนามิกและการวิเคราะห์ข้อมูลที่ต้องการความแม่นยำสูง เพราะสูตรจะยังคงทำงานได้ถูกต้องแม้ PivotTable จะเปลี่ยนแปลง 💡
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| data_field | Text | ชื่อของฟิลด์ข้อมูลใน PivotTable ที่ต้องการดึงค่า ต้องใส่ในเครื่องหมายคำพูด (เช่น “Sales”, “Total Amount”) | |
| pivot_table | Range | เซลล์ ช่วงเซลล์ หรือชื่อที่กำหนด (Named Range) ที่อยู่ภายใน PivotTable เพื่อระบุตำแหน่งของ PivotTable ที่ต้องการดึงข้อมูล | |
| [field1]ไม่บังคับ | Text | – | ชื่อฟิลด์แรกที่ใช้กรองข้อมูล (เช่น “Product”, “Region”) ต้องใส่ในเครื่องหมายคำพูด ยกเว้นเป็นชื่อฟิลด์ที่เป็นตัวเลข/วันที่ |
| [item1]ไม่บังคับ | Text/Number/Date | – | รายการ (Item) ที่ตรงกับ field1 เช่นถ้า field1=”Product” item1 อาจเป็น “Hazelnut” ค่าที่ไม่ใช่ตัวเลขหรือวันที่ต้องใส่ในเครื่องหมายคำพูด |
| [field2]ไม่บังคับ | Text | – | ชื่อฟิลด์ที่สองสำหรับกรองข้อมูลซ้อนกับ field1 (ไม่บังคับ) ใช้เมื่อต้องการเจาะจงมากกว่า 1 มิติ เช่น กรองทั้ง Region และ Product พร้อมกัน สามารถเพิ่มคู่ field/item ได้สูงสุด 126 คู่ |
| [item2]ไม่บังคับ | Text/Number/Date | – | รายการ (Item) ที่ตรงกับ field2 ทำงานคู่กันเสมอ ถ้าระบุ field2 ต้องระบุ item2 ด้วย |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
GETPIVOTDATA ใช้สำหรับดึงข้อมูลเฉพาะเจาะจงจาก PivotTable โดยอ้างอิงจากชื่อฟิลด์และเงื่อนไขที่กำหนด แทนที่จะใช้การอ้างอิงเซลล์โดยตรง
ที่เจ๋งคือมันดึงข้อมูลตามโครงสร้างของ PivotTable ไม่ใช่ตำแหน่งเซลล์ ทำให้สูตรยังคงทำงานได้ถูกต้องแม้ว่า PivotTable จะมีการเปลี่ยนแปลงรูปแบบหรือการจัดเรียงข้อมูล
ส่วนตัวผมใช้บ่อยมากเลยครับเวลาทำ Dashboard หรือรายงานที่ต้องดึงค่าจาก PivotTable เพราะมั่นใจได้ว่าสูตรจะไม่พัง 😎
How it works
การวิเคราะห์ยอดขายตามภูมิภาค
ดึงตัวเลขยอดขายเฉพาะของแต่ละภูมิภาค ผลิตภัณฑ์ หรือพนักงานขายจาก PivotTable โดยไม่ต้องเลื่อนหาข้อมูลในตารางขนาดใหญ่
รายงานทางการเงินแบบไดนามิก
สร้างรายงานการเงินที่อัพเดทอัตโนมัติเมื่อ PivotTable มีการเปลี่ยนแปลง ช่วยลดข้อผิดพลาดจากการคัดลอกข้อมูลด้วยมือ
การจัดการข้อมูลพนักงาน
ช่วยทีม HR ดึงข้อมูลเงินเดือน ยอดวันลา หรือสtatistics ของแผนกต่างๆ ได้อย่างรวดเร็วและแม่นยำ
Dashboard แบบ Real-time
สร้าง Dashboard ที่ดึงข้อมูลจาก PivotTable แบบ Dynamic โดยสูตรจะปรับตัวอัตโนมัติเมื่อมีการเปลี่ยนแปลงข้อมูล
Examples
ตัวอย่างที่ 1: ดึงยอดขายรวมของสินค้าเฉพาะGETPIVOTDATA("Sales", PivotTable!$B$4, "Product", "Hazelnut")
ตารางข้อมูล PivotTable มีการสรุปยอดขาย (Sales) จำแนกตามผลิตภัณฑ์ (Product)
สูตรนี้จะดึงยอดขายรวม (Sales) ของสินค้า Hazelnut จาก PivotTable ที่อยู่ในช่วง $B$4 ผลลัพธ์คือ 62,456 บาท
ที่ต้องระวังคือชื่อฟิลด์ "Sales" และ "Product" รวมถึงชื่อสินค้า "Hazelnut" ต้องใส่ในเครื่องหมายคำพูดครับ 😅
ตัวอย่างที่ 2: ดึงยอดขายตามภูมิภาคและผลิตภัณฑ์GETPIVOTDATA("Total Sales", SalesPivot!$A$1, "Region", "East", "Product", "Laptop")
ตารางข้อมูล PivotTable ชื่อ SalesPivot มีการสรุปยอดขาย (Total Sales) จำแนกตามภูมิภาค (Region) และผลิตภัณฑ์ (Product)
สูตรนี้จะดึงยอดขายรวม (Total Sales) ของผลิตภัณฑ์ Laptop ในภูมิภาค East ได้ผลลัพธ์ 450,000 บาท
ที่เจ๋งคือการใช้หลายคู่ field-item ช่วยให้สามารถกรองข้อมูลได้แม่นยำมากขึ้น 💡
ตัวอย่างที่ 3: ดึงค่าเฉลี่ยรายเดือนGETPIVOTDATA("Average Revenue", ReportPivot!$C$3, "Month", "January", "Year", 2025)
ตารางข้อมูล PivotTable ชื่อ ReportPivot มีการคำนวณรายได้เฉลี่ย (Average Revenue) จำแนกตามเดือน (Month) และปี (Year)
สูตรนี้จะดึงค่ารายได้เฉลี่ย (Average Revenue) ของเดือนมกราคม (January) ปี 2025 ได้ผลลัพธ์ 125,000 บาท
สังเกตว่าค่าปี 2025 เป็นตัวเลข จึงไม่ต้องใส่เครื่องหมายคำพูด ในขณะที่ชื่อเดือน "January" ต้องใส่เครื่องหมายคำพูดนะครับ ✨
ตัวอย่างที่ 4: ใช้กับวันที่แบบ Serial NumberGETPIVOTDATA("Orders", PivotTable!$A$3, "Order Date", DATE(2025,3,15))
ตารางข้อมูล PivotTable มีการสรุปจำนวนออเดอร์ (Orders) จำแนกตามวันที่สั่งซื้อ (Order Date)
เมื่อทำงานกับวันที่ใน GETPIVOTDATA ควรใช้ฟังก์ชัน DATE() หรือ Serial Number เพื่อให้ทำงานได้ถูกต้องในทุก Locale
สูตรนี้จะดึงจำนวนออเดอร์ที่เกิดขึ้นในวันที่ 15 มีนาคม 2025 โดยการใช้ DATE(2025,3,15) จะแปลงวันที่เป็น Serial Number ที่ Excel เข้าใจได้ครับ 😎
FAQs
ทำไม GETPIVOTDATA ถึงดีกว่าการอ้างอิงเซลล์โดยตรง?+
GETPIVOTDATA ดึงข้อมูลตามโครงสร้างและชื่อฟิลด์ของ PivotTable ไม่ใช่ตำแหน่งเซลล์
ดังนั้นแม้ว่า PivotTable จะมีการเปลี่ยนรูปแบบ เรียงลำดับใหม่ หรือกรองข้อมูล สูตรก็ยังคงดึงข้อมูลได้ถูกต้อง
ในขณะที่การอ้างอิงเซลล์โดยตรง (เช่น =B5) จะผิดพลาดเมื่อ PivotTable เปลี่ยนแปลง ส่วนตัวผมแนะนำให้ใช้ GETPIVOTDATA เสมอครับ 💡
ทำไมสูตร GETPIVOTDATA ถึงถูกสร้างอัตโนมัติเมื่อคลิกเซลล์ใน PivotTable?+
Excel สร้างสูตร GETPIVOTDATA อัตโนมัติเพื่อให้แน่ใจว่าสูตรจะทำงานได้ถูกต้องแม้ PivotTable จะเปลี่ยนแปลง
หากต้องการหลีกเลี่ยงพฤติกรรมนี้และต้องการอ้างอิงเซลล์โดยตรง ให้พิมพ์ที่อยู่เซลล์ด้วยตนเอง (เช่น =B5) แทนการคลิกเซลล์ครับ 😅
ถ้า GETPIVOTDATA คืนค่า #REF! ต้องแก้ไขอย่างไร?+
ข้อผิดพลาด #REF! เกิดขึ้นเมื่อ: 1) ตำแหน่ง pivot_table ที่ระบุไม่ได้อยู่ใน PivotTable จริง, 2) ฟิลด์หรือรายการที่ระบุไม่ปรากฏใน PivotTable (อาจถูกกรองออก), 3) ชื่อฟิลด์หรือรายการสะกดผิด
ให้ตรวจสอบว่าชื่อฟิลด์และรายการสะกดถูกต้องและปรากฏอยู่ใน PivotTable ส่วนตัวผมมักเจอปัญหานี้เวลาสะกดผิดครับ 😅
GETPIVOTDATA ทำงานกับ OLAP PivotTables ได้หรือไม่?+
ได้ครับ GETPIVOTDATA รองรับ OLAP PivotTables โดยสามารถใช้ชื่อ dimension และ item source ในรูปแบบ hierarchical เพื่อดึงข้อมูลจาก OLAP cube ได้อย่างแม่นยำ
เอาจริงๆ การใช้กับ OLAP ซับซ้อนกว่าปกตินิดหน่อย แต่หลักการเดียวกันครับ 💡
สามารถปิดการสร้างสูตร GETPIVOTDATA อัตโนมัติได้หรือไม่?+
ได้ครับ ไปที่ File > Options > Formulas แล้วยกเลิกการเลือก "Use GetPivotData functions for PivotTable references"
หลังจากนั้นเมื่อคลิกเซลล์ใน PivotTable จะได้สูตรอ้างอิงเซลล์ธรรมดาแทน GETPIVOTDATA แต่ส่วนตัวผมไม่แนะนำนะครับ เพราะ GETPIVOTDATA ปลอดภัยกว่ามาก 😎
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ GETPIVOTDATA จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
HLOOKUP ค้นหาข้อมูลจากแถวแรกของตาราง แล้วคืนค่าจากแถวที่ระบุ ตรงข้ามกับ VLOOKUP ที่ค้นหาแนวตั้ง เหมาะสำหรับตารางที่หัวข้อมูลเรียงแบบแนวนอน
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
VLOOKUP เป็นฟังก์ชันค้นหาแนวตั้งที่ได้รับความนิยมสูงสุดใน Excel โดยค้นหาค่าที่ต้องการในคอลัมน์ซ้ายสุดของตาราง จากนั้นดึงข้อมูลจากคอลัมน์ที่ระบุในแถวเดียวกัน รองรับทั้งการค้นหาแบบตรงทุกตัวอักษร (Exact Match) และแบบประมาณค่า (Approximate Match) สำหรับข้อมูลที่เรียงลำดับ ข้อจำกัดหลักคือต้องค้นหาจากคอลัมน์ซ้ายสุดเท่านั้น ไม่สามารถดึงข้อมูลจากด้านซ้ายของคอลัมน์ค้นหาได้
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่