Thep Excel
PIVOTBY – สร้าง Pivot Table ด้วยสูตร Dynamic Array
PIVOTBY เป็นฟังก์ชัน Excel 365 ที่สร้าง Pivot Table แบบไดนามิก โดยกำหนด Row Fields, Column Fields และ Values ผ่านสูตร พร้อมตั้งค่าการแสดงผลรวมและการเรียงลำดับได้ทันที
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])
Function Metrics
Popularity
5/10
Difficulty
4/10
Usefulness
6/10
Syntax & Arguments
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| row_fields | Range | Yes | คอลัมน์ที่ต้องการนำมาเป็นแถว (Row Labels) – แต่ละค่าที่แตกต่างกันจะแสดงเป็นแถวหนึ่ง | |
| col_fields | Range | Yes | คอลัมน์ที่ต้องการนำมาเป็นหัวคอลัมน์ (Column Labels) – แต่ละค่าที่แตกต่างกันจะแสดงเป็นคอลัมน์หนึ่ง | |
| values | Range | Yes | ข้อมูลตัวเลขที่ต้องการนำมาคำนวณ (Values to aggregate) – ต้องเป็นตัวเลขเสมอ | |
| function | Function or Array | Yes | ฟังก์ชันสรุปผล เช่น SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT หรือ HSTACK เพื่อใช้หลายฟังก์ชัน | |
| field_headers | Logical | Optional | TRUE เพื่อแสดงชื่อคอลัมน์ตัวแรก FALSE เพื่อซ่อน (ค่าเริ่มต้น: FALSE) | |
| row_total_depth | Number | Optional | ความลึกของการแสดง Row Totals: 0=ไม่แสดง -1=ทั้งหมด 1,2,3=ระดับนั้นๆ (ค่าเริ่มต้น: -1) | |
| row_sort_order | Number | Optional | ลำดับการเรียง Row: 1=เรียงเหมือน source data -1=เรียงตัวเลขจากมากไปน้อย 2=เรียง A-Z (ค่าเริ่มต้น: 1) | |
| col_total_depth | Number | Optional | ความลึกของการแสดง Column Totals: 0=ไม่แสดง -1=ทั้งหมด 1,2,3=ระดับนั้นๆ (ค่าเริ่มต้น: -1) | |
| col_sort_order | Number | Optional | ลำดับการเรียง Column: 1=เรียงเหมือน source data -1=เรียงตัวเลขจากมากไปน้อย 2=เรียง A-Z (ค่าเริ่มต้น: 1) | |
| filter_array | Array | Optional | อาเรย์เพื่อกรองข้อมูล เช่นมาจาก FILTER function (ค่าเริ่มต้น: ใช้ทั้งหมด) |
How it works
สรุปยอดขายรายภาคและสินค้า
ดูยอดขายแยกตามภาค (Rows) และประเภทสินค้า (Columns) ในตารางเดียว
รายงานสรุปรายเดือนและรายปี
เปรียบเทียบข้อมูลรายเดือน (Rows) ของแต่ละปี (Columns)
วิเคราะห์คะแนนสอบ
ดูคะแนนเฉลี่ยของนักเรียนแยกตามห้อง (Rows) และวิชา (Columns)
Examples
ตัวอย่างที่ 1: Pivot Table พื้นฐาน – สรุปยอดขายรายภาคและสินค้า
PIVOTBY(A2:A20, B2:B20, C2:C20, SUM)
=PIVOTBY(A2:A20, B2:B20, C2:C20, SUM)
ตารางแสดงผลรวมยอดขายแยกตามภาค (แถว) และสินค้า (คอลัมน์)
ตัวอย่างที่ 2: Pivot Table พร้อม Header, Grand Total และจัดเรียงตามยอดขายจากมากไปน้อย
PIVOTBY(Sales[Region], Sales[Product], Sales[Amount], SUM, TRUE, -1, -1, -1, -1)
=PIVOTBY(Sales[Region], Sales[Product], Sales[Amount], SUM, TRUE, -1, -1, -1, -1)
ตารางพร้อม Header แสดง Grand Total และเรียง Row, Column ตามผลรวมจากมากไปน้อย
ตัวอย่างที่ 3: Pivot Table แสดง 2 ค่า (ค่าเฉลี่ยและจำนวน) พร้อมกัน
PIVOTBY(Scores[Room], Scores[Subject], Scores[Value], HSTACK(AVERAGE, COUNT))
=PIVOTBY(Scores[Room], Scores[Subject], Scores[Value], HSTACK(AVERAGE, COUNT))
ตารางแสดงค่าเฉลี่ยและจำนวนนักเรียนในแต่ละช่อง
ตัวอย่างที่ 4: Pivot Table กรองข้อมูลเฉพาะเดือนที่ 1-6 โดย FILTER
PIVOTBY(FILTER(Sales[Region], Sales[Month]
=PIVOTBY(FILTER(Sales[Region], Sales[Month]<=6), FILTER(Sales[Product], Sales[Month]<=6), FILTER(Sales[Amount], Sales[Month]<=6), SUM)
ตารางสรุปยอดขายเฉพาะเดือน 1-6 เท่านั้น
FAQs
PIVOTBY ต่างจาก Pivot Table เมนูธรรมชาติอย่างไร?
ผม Pivot Table เมนูดีตรงที่มี Slicer, Filter, Drill Down ฯลฯ แต่ PIVOTBY มีข้อดีคือ Dynamic (อัปเดตอัตโนมัติ), สามารถใส่ในสูตร อื่นๆ ได้, ไม่ต้องสร้าง pivot field ใหม่ซ้ำแล้วซ้ำเล่า ผมชอบใช้ PIVOTBY เมื่อต้องการรายงานที่เปลี่ยนแปลงพร้อมกับแหล่งข้อมูล
PIVOTBY ต่างจาก GROUPBY อย่างไร?
GROUPBY มีแค่ Row Fields (สรุปเป็นรายการลงมา) ส่วน PIVOTBY มีทั้ง Row และ Column Fields (สรุปเป็นตาราง 2 มิติ) ถ้าต้องการตาราง 2 มิติ ใช้ PIVOTBY ถ้าต้องการรายการแนวตั้ง ใช้ GROUPBY
PIVOTBY รองรับ Slicer ได้ไหม?
ไม่โดยตรง เพราะ PIVOTBY ไม่ใช่ Pivot Table จริงๆ แต่ผมชอบแก้ไขนี้โดยเพิ่ม Dropdown list ที่อ้างอิงไปยังข้อมูลต้นทาง หรือใช้ FILTER ร่วมกับ PIVOTBY แล้วให้ Dropdown ควบคุมเงื่อนไขในสูตร เช่นเลือกเดือนหรือภูมิภาค
ใช้ PIVOTBY กับตาราง Structured Range ได้ไหม?
ได้ครับ ผมใช้ Table reference เช่น Sales[Region], Sales[Product] ได้เลย แม้แต่ Table ที่มีการ update ข้อมูลใหม่ PIVOTBY จะรับรู้และอัปเดตผลลัพธ์โดยอัตโนมัติ
row_sort_order = -1 หมายความว่าอะไร?
ผมอธิบายว่า row_sort_order = -1 หมายถึง เรียง Row ตามผลรวมมากไปน้อย ค่า 1 คือเรียงเหมือนลำดับต้นทาง 2 คือเรียง A-Z ไม่มี ascending/descending สำหรับ A-Z ปกติจะเป็น descending ถ้าต้อง ascending ต้องใช้ SORT ครอบบนนอก PIVOTBY
Resources & Related
Resources
-
Microsoft Support: PIVOTBY function
official -
ExcelJet – PIVOTBY Tutorial
guide -
Microsoft Learn: Dynamic Array Functions
official
Related posts
Related functions
Additional Notes
PIVOTBY คือเครื่องมือเพื่อสร้าง Pivot Table โดยใช้สูตรเพียงบรรทัดเดียว คุณเลือกคอลัมน์ไหนเป็น Row Labels, คอลัมน์ไหนเป็น Column Headers, ข้อมูลตัวเลขไหนจะนำมาคำนวณ และเลือกฟังก์ชันสรุป (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) ก็เสร็จ
ที่เจ๋งคือ PIVOTBY สร้าง Pivot Table แบบไดนามิก ซึ่งหมายความว่าเมื่อข้อมูลต้นทางเปลี่ยน PIVOTBY จะอัปเดตผลลัพธ์โดยอัตโนมัติ ไม่ต้องสร้าง Pivot Table ใหม่ซ้ำแล้วซ้ำเล่า นอกจากนี้ยังสามารถปรับแต่งการแสดงผลรวมแต่ละระดับ (Totals Depth) และการเรียงลำดับได้อย่างอิสระ
ส่วนตัวผม PIVOTBY เป็นฟังก์ชันที่ออกแบบมาเพื่อผู้ชอบทำงานด้วยสูตรมากกว่าการใช้เมนู เพราะมันให้ความยืดหยุ่นและความแม่นยำสูง ผมใช้ PIVOTBY ในรายงานที่ต้องอัปเดตบ่อยๆ หรือเมื่อต้องเชื่อม Dynamic Array ฟังก์ชันอื่นๆ เข้าด้วยกัน อย่างเช่น FILTER ก็ได้ครับ