สูตรวิเคราะห์ข้อมูล — SUMPRODUCT, AGGREGATE, GETPIVOTDATA

เมื่อสูตรพื้นฐานเริ่มตอบไม่ทันใจ

ถ้าคุณทำงานกับยอดขายหรือข้อมูลหลายหมื่นแถว ผมเชื่อว่าหลายครั้งคุณต้องประกอบสูตรยาวๆ ขึ้นมา เช่น COUNTIFS ต่อกับ SUMIFS อีกที แล้วยังต้องคอยจัดการกับค่า error ที่โผล่กลางตารางจนได้คำตอบผิด ดูเผินๆ เหมือนแก้ได้ เพียงแต่ทุกอย่างมันช้าและเปลืองเซลล์

ในบทความนี้ผมจะพาคุณรู้จักสูตร “สายวิเคราะห์ข้อมูล” 3 ตัวที่ช่วยทำงานแทนหลายสูตรพื้นฐานได้ในเซลล์เดียว ได้แก่ SUMPRODUCT, AGGREGATE และ GETPIVOTDATA แต่ละตัวมีจุดเด่นต่างกัน เอามาใช้คู่กันแล้วคุณจะสรุปข้อมูลได้เร็วขึ้นมาก

ผมเตรียมไฟล์ตัวอย่างไว้ให้ครับ เปิดทำไปด้วยกันเลยนะ ภายในมีชีตเดียวคือ ข้อมูลสด ที่เก็บยอดขายประจำวัน 150 แถว คอลัมน์ วันที่, จังหวัด, สินค้า, ยอดขาย, จำนวน สำหรับชีต PivotTable และ Dashboard คุณจะสร้างเองใน Excel ตามขั้นตอนที่ผมอธิบายด้านล่างครับ

🛠️ สร้าง PivotTable เองก่อน (ทำตามขั้นตอนนี้ครับ)

ก่อนจะใช้ GETPIVOTDATA คุณต้องมี PivotTable อยู่ก่อน นี่คือขั้นตอนสร้างจากชีต ข้อมูลสด ที่เตรียมไว้ในไฟล์ตัวอย่าง:

ขั้นตอนที่ 1: เลือกข้อมูลต้นทาง

1. ไปที่ชีต ข้อมูลสด
2. กด Ctrl + A เพื่อเลือกทั้งตาราง (หรือคลิกเซลล์ไหนก็ได้ในตาราง แล้วกด Ctrl + *)
3. ตรวจสอบว่าระบุช่วงถูกต้อง: ข้อมูลสด!$A$1:$E$151 (หัวข้อ + 150 แถวข้อมูล)

ขั้นตอนที่ 2: สร้าง PivotTable

1. เมนู Insert → PivotTable
2. ด้วย “Choose where you want the PivotTable report” เลือก New Worksheet
3. ตั้งชื่อชีตใหม่ว่า PivotTable → กด OK

ขั้นตอนที่ 3: จัดวางฟิลด์ (Field List)

ในแผง PivotTable Fields ด้านขวา ให้ลากฟิลด์ดังนี้:

พื้นที่ (Area)	ฟิลด์ที่ลากเข้าไป
Filters	(เว้นว่างไว้ กรองได้ภายหลัง)
Columns	จังหวัด
Rows	สินค้า
Values	ยอดขาย (ค่าเริ่มต้นจะเป็น Sum of ยอดขาย)

💡 เคล็ดลับ: ถ้าอยากดูจำนวนรายการด้วย ให้ลาก จำนวน เข้าไปใน Values อีกตัว (จะกลายเป็น Count of จำนวน)

ขั้นตอนที่ 4: จัดรูปแบบให้อ่านง่าย

1. คลิกขวาที่ตัวเลขใน Pivot → Number Format → เลือก Number / Currency ใส่คอมม่า 2 ตำแหน่ง
2. แท็บ Design → Report Layout → Show in Tabular Form (ทำให้ดูเป็นตารางแบน)
3. Subtotals → Do Not Show Subtotals (ถ้าไม่ต้องการยอดรวมย่อย)
4. ปรับความกว้างคอลัมน์ ให้อ่านง่าย

ขั้นตอนที่ 5: ทดสอบ GETPIVOTDATA

ตอนนี้คุณมี PivotTable เรียบร้อยแล้ว กลับไปที่ชีต Dashboard (หรือสร้างชีตใหม่) แล้วพิมพ์:

=GETPIVOTDATA("ยอดขาย", PivotTable!$A$1, "สินค้า", "กาแฟ")

PivotTable!$A$1 = เซลล์มุมซ้ายบนของ PivotTable (header “สินค้า”) — คลิกที่นั่น Excel จะใส่ให้อัตโนมัติ

GETPIVOTDATA ดึงค่าจาก PivotTable แบบอัตโนมัติ

GETPIVOTDATA เป็นฟังก์ชันที่หลายคนหลงลืมครับ แต่ผมว่าเป็นตัวช่วยสรุปข้อมูลแบบ dynamic ที่ดีมาก เวลาคุณทำ PivotTable เสร็จ ถ้าพิมพ์เครื่องหมายเท่ากับแล้วคลิกที่เซลล์ใน Pivot Excel จะสร้างสูตร GETPIVOTDATA ขึ้นมาให้เอง เช่น

=GETPIVOTDATA("ยอดขาย", $A$1, "สินค้า", "กาแฟ")

ความหมายคือดึงค่าจากฟิลด์ยอดขาย จาก Pivot ที่มี anchor เซลล์อยู่ที่ A1 โดยกรองให้สินค้าเป็นกาแฟ ผลลัพธ์คือยอดขายรวมของกาแฟตามที่ Pivot คำนวณไว้

จุดเด่นคือสูตรนี้ผูกกับ PivotTable โดยตรงครับ ถ้าคุณกรอง Pivot หรือแก้ข้อมูลต้นทาง ตัวเลขใน GETPIVOTDATA จะเปลี่ยนตามอัตโนมัติโดยไม่ต้องแก้อะไรเลย เหมาะมากสำหรับแดชบอร์ดที่อยากให้ตัวเลขที่เปลี่ยนไปตามข้อมูลตลอดเวลา

ในไฟล์ตัวอย่าง ผมสร้างชีต PivotTable มาสรุปยอดขายรวมของแต่ละสินค้าและจังหวัด แล้วในชีต Dashboard ผมใช้ GETPIVOTDATA ดึงยอดขายของกาแฟออกมาแสดง ผลลัพธ์คือตัวเลขที่ผูกกับ Pivot โดยตรง ไม่ต้องกลัวว่าค่าจะตกหล่นตอนแก้ข้อมูล

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

💡 เคล็ดลับ: ถ้าอยากได้สูตรแบบสั้นๆ ที่ควบคุมเองได้ ให้พิมพ์ GETPIVOTDATA ด้วยมือแทนการคลิกครับ แต่ต้องเช็คให้แน่ใจว่าชื่อฟิลด์และ anchor เซลล์ถูกต้อง ไม่เช่นนั้นจะดึงค่าผิดไป

ทำความรู้จัก SUMPRODUCT เจ้าของแนวคิด array

SUMPRODUCT แปลตรงตัวว่าคูณเสร็จแล้วเอามารวมครับ หลักการคือมันเอาแต่ละแถวมาคูณกัน แล้วบวกรวมทั้งหมด เช่น ถ้าคุณมีคอลัมน์ B เป็นราคาต่อชิ้น และคอลัมน์ C เป็นจำนวนขาย

=SUMPRODUCT(B2:B8, C2:C8)

สูตรนี้จะคำนวณ B2*C2 + B3*C3 … ไปเรื่อยๆ จนถึงแถวสุดท้าย แล้วรวมออกมาเป็นคำตอบเดียว นี่คือวิธีคิดยอดขายรวมแบบไม่ต้องสร้างคอลัมน์ช่วยเลย

พลังที่แท้จริงของ SUMPRODUCT อยู่ที่การคูณด้วยเงื่อนไขครับ ผมขออธิบายแนวคิดของ array สักหน่อย ใน Excel เมื่อคุณเขียนเงื่อนไขเช่น C2:C8="กาแฟ" มันจะให้ผลลัพธ์เป็นอาร์เรย์ของ TRUE/FALSE และเมื่อคูณกับตัวเลข TRUE จะกลายเป็น 1 ส่วน FALSE เป็น 0

ในไฟล์ตัวอย่าง ผมมีข้อมูลยอดขาย 150 แถวในชีต ข้อมูลสด คอลัมน์มี วันที่, จังหวัด, สินค้า, ยอดขาย, จำนวน วิธีลิสต์ราคาเหมือนจริงคือสินค้า 3 อย่างคือ กาแฟ, ชานม, เค้ก ขายใน 4 จังหวัดคือ กรุงเทพฯ, เชียงใหม่, ภูเก็ต, ขอนแก่น

ถ้าผมอยากรู้ยอดขายรวมของกาแฟแบบไม่ต้องใช้ SUMIFS ก็เขียนได้แบบนี้

=SUMPRODUCT((C2:C151="กาแฟ") * (D2:D151))

ส่วนเงื่อนไขนี้คูณกันเป็น 1 เฉพาะแถวที่สินค้าเป็นกาแฟ พอคูณกับคอลัมน์ยอดขาย มันจะรวมเฉพาะแถวนั้นออกมา ผลลัพธ์คือยอดขายทั้งหมดของกาแฟในทุกจังหวัดและทุกวันที่ครับ

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

SUMPRODUCT ใช้แทน COUNTIFS และ SUMIFS

จุดที่คนชอบมากที่สุดคือ SUMPRODUCT ทำหน้าที่ของทั้ง COUNTIFS และ SUMIFS ได้ในสูตรเดียวครับ สองฟังก์ชันนี้ถ้าอยากใช้หลายเงื่อนไขก็ยังได้ แต่ SUMPRODUCT เก็บทุกอย่างไว้ในที่เดียวกันดูเกะกะน้อยกว่า

ลองนับจำนวนแถวที่ขายชานมในจังหวัดเชียงใหม่กันครับ ผมเขียนแบบนี้

=SUMPRODUCT((C2:C151="ชานม") * (B2:B151="เชียงใหม่"))

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

=SUMPRODUCT((C2:C151="ชานม") * (B2:B151="เชียงใหม่") * (D2:D151))

ลองเทียบกับ SUMIFS ปกติครับ =SUMIFS(D2:D151, C2:C151, "ชานม", B2:B151, "เชียงใหม่") ได้คำตอบเหมือนกัน เพียงแต่รูปแบบต่างกันเรื่องการเขียนครับ

อีกส่วนหนึ่งที่หลายคนไม่รู้คือ SUMPRODUCT คำนวณข้าม error ได้บางกรณี เพราะมันละเลยค่า error ในการคูณ ถ้าคอลัมน์หนึ่งมี #N/A อยู่ SUMPRODUCT จะให้ผลลัพธ์เป็น error ต่อเมื่อทั้งสองตัวเป็น error ด้วยกัน ไม่งั้นมันช่วยกรองให้คำนวณเฉพาะข้อมูลที่ครบถ้วนครับ

ผมลองกดคำนวณค่าจริงในไฟล์ดูแล้ว สรุปยอดขายชานมในเชียงใหม่ได้ราว 18,590 บาท ส่วนนับจำนวนแถวชานมเชียงใหม่อยู่ที่ 15 รายการ ถ้าเพิ่มเงื่อนไขวันที่ตามต้องการก็แค่คูณเงื่อนไขข้างต้นอีกตัวหนึ่งเท่านั้นเอง

💡 เคล็ดลับ: สูตร SUMPRODUCT เหมาะกับตารางข้อมูลดิบที่ยาวๆ ครับ เพราะคุณตั้งช่วงไว้กว้างๆ แล้วแก้เฉพาะเงื่อนไขก็ได้ผลลัพธ์ใหม่ทันที ไม่ต้องไปลากสูตรไล่ทีละคอลัมน์

AGGREGATE สูตรรวมที่ข้าม error และแถวที่ซ่อน

AGGREGATE เป็นฟังก์ชันรวมขั้นสูงที่ทำงานคล้าย SUBTOTAL แต่ยืดหยุ่นกว่าเยอะครับ ตัวมันรับ argument สองชุด คือ function_num ที่บอกว่าจะคำนวณอะไร และ options ที่บอกว่าจะจัดการกับ error หรือแถวที่ซ่อนยังไง

โครงสร้างคือ AGGREGATE(function_num, options, ...ข้อมูล) ตัวอย่างเช่น ถ้าอยากได้ค่าเฉลี่ยที่ข้าม error

=AGGREGATE(1, 6, F2:F20)

ตรงนี้ function_num 1 คือค่าเฉลี่ย ส่วน options 6 แปลว่าข้ามทั้งค่า error และแถวที่ซ่อนไว้ ในไฟล์ตัวอย่างผมลองทำให้คอลัมน์ยอดขายบางแถวมีค่า #N/A เข้ามาแทรกไว้ ถ้าใช้ AVERAGE ธรรมดาจะได้ error ทั้งตาราง แต่ AGGREGATE กลับคำนวณให้เสร็จเลย

ผมเตรียม list ของ function_num ที่ใช้บ่อยไว้ให้ครับ เช่น 1 คือ AVERAGE, 4 คือ MAX, 5 คือ MIN, 6 คือ PRODUCT, 9 คือ SUM และ 12 คือ MEDIAN

ส่วน options ที่นิยมมี 0 หมายถึงไม่สนใจอะไรเลย 4 คือข้ามค่าว่าง 5 คือข้ามแถวที่ซ่อน และ 6 คือข้ามทั้ง error กับแถวที่ซ่อน

จุดที่ AGGREGATE ต่างจาก SUBTOTAL คือมันข้ามแถวที่ซ่อนตามเลขแถว ไม่ใช่ตามฟังก์ชันการกรองครับ หมายความว่าคุณควบคุมได้ชัดเจนว่าให้คิดเฉพาะส่วนที่มองเห็นหรือรวมทุกแถว แถมยังรองรับฟังก์ชันอย่าง LARGE และ SMALL ได้อีกด้วย

ในไฟล์ผมใช้ AGGREGATE 9 (SUM) แบบ options 6 เพื่อหายอดขายรวมทั้งตารางโดยข้าม error ผลลัพธ์ที่ได้คือ 365,625 บาท โดยไม่ต้องไปลบ error ทิ้งก่อนเลย นี่คือความสะดวกที่สูตรพื้นๆ ให้ไม่ได้

⚠️ ข้อควรระวัง: เลข function_num กับ options จำยากนิดหน่อยครับ ถ้าใส่ผิดค่าจะได้ผลลัพธ์ที่ไม่ตรงกับที่คิด ผมแนะนำให้เปิด reference ใน Excel ดูประกอบ หรือจดคำอธิบายไว้ข้างเซลล์ด้วยเผื่อส่งงานต่อ

เอามาใช้ร่วมกันบนแดชบอร์ดจริง

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

โครงสร้างมีหัวข้อหลักสองสามช่อง สมมติผมอยากหาคำตอบหลายคำถามพร้อมกัน เช่น ยอดขายรวมของชานมในขอนแก่น, ค่าเฉลี่ยต่อรายการของทุกสินค้า, และยอดขายรวมของเค้กที่ดึงจาก Pivot

ผมใช้ SUMPRODUCT หายอดชานมในขอนแก่นพร้อมเงื่อนไขสองมิติ แล้วใช้ AGGREGATE หาค่าเฉลี่ยที่ข้าม error สุดท้ายใช้ GETPIVOTDATA ดึงยอดขายเค้กจาก Pivot มาแสดง ตัวเลขแต่ละชุดมาจากแหล่งข้อมูลที่ต่างกันแต่แสดงผลอยู่ในตารางเดียวกัน สะดวกมากตอนส่งรายงานครับ

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

📌 ข้อควรจำ: เทคนิคที่ผมแนะนำทั้งหมดนี้เป็นการทำงานที่คุณทำใน Excel ตรงๆ ครับ ไม่ใช่ฟีเจอร์ที่จะสร้างขึ้นจากไฟล์ตัวอย่างอัตโนมัติ ไฟล์ที่เตรียมให้ถือเป็นต้นแบบให้ลองทำตามเท่านั้น

สรุปและชวนลองทำเล่น

ทั้งสามสูตรถือเป็นอาวุธสำคัญของงานวิเคราะห์ข้อมูลครับ สรุปสั้นๆ ได้ดังนี้

ฟังก์ชัน	ใช้ทำอะไร	จุดเด่น
SUMPRODUCT	คูณและรวมอาร์เรย์, ทำเงื่อนไขหลายตัว	แทนทั้ง COUNTIFS และ SUMIFS ในสูตรเดียว
AGGREGATE	รวมค่าพร้อมจัดการ error/แถวที่ซ่อน	ข้าม error ได้โดยไม่ต้องล้างข้อมูล
GETPIVOTDATA	ดึงค่าจาก PivotTable	ผูกกับ Pivot อัปเดตตามอัตโนมัติ

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

เมื่อคล่องแล้วลองออกแบบแดชบอร์ดของตัวเองที่รวมทั้ง 3 สูตรเข้าด้วยกันดู รับรองว่างานสรุปข้อมูลที่เคยใช้เวลานานจะเร็วขึ้นเป็นกองเลยครับ ขอให้สนุกกับการเขียนสูตรนะครับ 😁