ถ้าคอลัมน์ตัวเลขมี Error ปนอยู่ (เช่น #DIV/0!) สูตร SUM หรือ AVERAGE ธรรมดาจะพังไปทั้งคอลัมน์ทันที AGGREGATE คือฟังก์ชันรวมทุกอย่างในตัวเดียว ทั้ง SUM, AVERAGE, MAX, MIN, COUNT และอีก 19 ฟังก์ชัน พร้อมตัวเลือกให้ข้าม Error หรือแถวที่ซ่อนอยู่ได้ โดยไม่ต้องใช้สูตรอาเรย์แบบ Ctrl+Shift+Enter
รูปแบบสูตร
=AGGREGATE(function_num, options, ref1, [ref2])
- function_num — ตัวเลข 1-19 ระบุฟังก์ชันที่ต้องการใช้ เช่น 9 = SUM, 1 = AVERAGE, 4 = MAX, 5 = MIN, 2 = COUNT
- options — ตัวเลข 0-7 ระบุว่าจะข้ามอะไรบ้าง เช่น 6 = ข้าม Error values, 5 = ข้ามแถวที่ซ่อน, 7 = ข้ามทั้ง Error และแถวที่ซ่อน
- ref1 — ช่วงข้อมูลที่ต้องการคำนวณ
ตัวอย่าง: รวมยอดขายโดยข้าม Error
=AGGREGATE(9,6,B2:B6)| A | B | |
|---|---|---|
| 1 | สาขา | ยอดขาย |
| 2 | สาขา A | 1,200 |
| 3 | สาขา B | #DIV/0! |
| 4 | สาขา C | 850 |
| 5 | สาขา D | #N/A |
| 6 | สาขา E | 640 |
| 7 | รวม | 2,690 |
ถ้าใช้ =SUM(B2:B6) ตรงๆ ทั้งคอลัมน์จะขึ้น Error ทันทีเพราะมี #DIV/0! และ #N/A ปนอยู่ แต่ AGGREGATE ที่ตั้งค่า options เป็น 6 (ข้าม Error) จะรวมเฉพาะตัวเลขที่ใช้งานได้ ได้ผลลัพธ์ 1,200+850+640 = 2,690 โดยไม่ต้องกรอง Error ออกด้วยมือก่อน
ตารางเลขฟังก์ชันที่ใช้บ่อย
| function_num | ฟังก์ชัน |
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 9 | SUM |
| 14 | LARGE |
| 15 | SMALL |
ตารางเลข options ที่ใช้บ่อย
| options | ความหมาย |
| 0 | ข้ามเซลล์ที่ซ่อนด้วย Nested SUBTOTAL/AGGREGATE เท่านั้น |
| 5 | ข้ามแถวที่ซ่อนไว้ (Hidden Rows) |
| 6 | ข้าม Error values |
| 7 | ข้ามทั้งแถวที่ซ่อนและ Error values |
AGGREGATE ต่างจาก SUBTOTAL อย่างไร
SUBTOTAL ก็ข้ามแถวที่ซ่อนได้เหมือนกัน แต่ทำไม่ได้กับ Error values และรองรับฟังก์ชันน้อยกว่า AGGREGATE จึงเหมาะกับงานที่ข้อมูลมีทั้ง Error และแถวซ่อนปนกัน ส่วน SUBTOTAL ยังเหมาะกับตารางที่ผ่าน Filter หรือ AutoFilter อยู่แล้ว
ข้อควรรู้
- AGGREGATE ไม่รองรับการอ้างอิงข้ามชีตหลายแผ่นพร้อมกัน (3D Reference) ต่างจาก SUM ปกติ
- ถ้าจำเลข function_num ไม่ได้ พิมพ์
=AGGREGATE(แล้ว Excel จะมีลิสต์ให้เลือกฟังก์ชันแบบ dropdown อัตโนมัติ - ดูเพิ่มเติมที่ SUM ใน Excel และ Group/Outline และ SUBTOTAL ใน Excel
สรุป
AGGREGATE เป็นฟังก์ชันรวมทุกฟังก์ชันสถิติพื้นฐานไว้ในตัวเดียว พร้อมตัวเลือกข้าม Error หรือแถวที่ซ่อนได้ในสูตรเดียว รูปแบบคือ =AGGREGATE(function_num, options, ref) เหมาะกับตารางข้อมูลจริงที่มักมี Error หรือแถวซ่อนปนอยู่เสมอ
