AGGREGATE · Excel · คลังสูตร 9Expert

AGGREGATE คืออะไร

สรุปผลด้วยฟังก์ชันที่เลือกจาก 19 ตัว โดยเลือกข้ามค่า Error แถวที่ซ่อน หรือบรรทัดรวมย่อยได้

ใช้เมื่อไหร่

สรุปยอดหรือค่าเฉลี่ยของคอลัมน์ที่มี #DIV/0! หรือ #N/A ปนอยู่
สรุปเฉพาะแถวที่มองเห็นหลังซ่อนหรือกรอง และข้าม Error ไปพร้อมกัน
หาอันดับที่ k หรือค่ามากสุดตามเงื่อนไขใน Excel 2016 ที่ยังไม่มี MAXIFS

รูปแบบสูตร

=AGGREGATE(function_num, options, ref1, [ref2], …)

Argument แต่ละตัว

ชี้ที่ argument ในรูปแบบสูตรด้านบนเพื่อไฮไลต์แถวนั้น

Argument	จำเป็นไหม	ความหมาย
function_num	จำเป็น	เลข 1–19 เลือกฟังก์ชัน เช่น 9 = SUM, 14 = LARGE
options	จำเป็น	เลข 0–7 เลือกว่าจะข้ามอะไร เช่น 5 = แถวที่ซ่อน, 6 = ค่า Error, 7 = ทั้งสองอย่าง
ref1	จำเป็น	ช่วงแรกที่ต้องการสรุป ฟังก์ชัน 1–13 ต้องเป็นช่วงเซลล์
[ref2], …	ไม่บังคับ	ช่วงถัดไป รวมได้สูงสุด 253 ช่วง
array, [k]	สำหรับ 14–19	array = ช่วงหรือการคำนวณ · k = อันดับ (14, 15) ค่าเปอร์เซ็นไทล์ 0–1 เช่น 0.9 (16, 18) หรือเลขควอร์ไทล์ (17, 19) ต้องใส่เสมอ ไม่งั้นได้ #VALUE!

ลองเลย: ตัวอย่างที่เปลี่ยนค่าได้

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

ปิยะพงษ์ยังไม่มีเป้า E10 จึงเป็น #DIV/0! AVERAGE ได้ Error ตาม แต่ AGGREGATE ข้ามไปได้

I6

=AGGREGATE(1,6,E5:E10)

เลือกฟังก์ชันและสิ่งที่จะข้าม function_num 1 คือ AVERAGE · options 6 คือข้ามค่า Error

แถว	A	B	C	D	E
4	พนักงานขาย	ภูมิภาค	เป้ายอดขาย (บาท)	ยอดขายจริง (บาท)	% ของเป้า
5	เอกชัย บัวทอง	ใต้	12,000,000	12,706,667	105.9%
6	ธีรพงษ์ ฉัตรแก้ว	ตะวันออกเฉียงเหนือ	11,000,000	10,668,812	97.0%
7	สุภาพร หงษ์ทอง	ตะวันออก	9,000,000	9,700,599	107.8%
8	นันทิดา กิจเจริญ	กลาง	10,000,000	9,123,730	91.2%
9	กมลชนก พงษ์พานิช	เหนือ	7,000,000	7,651,296	109.3%
10	ปิยะพงษ์ ศรีวงศ์	กลาง		1,250,000	#DIV/0!
11
12
13
14
15
16
17
18
19

E5:E10

ช่องกรอกและผลลัพธ์

AVERAGE ธรรมดา I5

เฉลี่ย % ของเป้า (ข้าม Error) I6

% ของเป้าต่ำสุด (ข้าม Error) I7

ขั้นตอนการคำนวณ (4)

คลิกช่องสีเขียวมะนาวในตารางเพื่อดูสูตรของแถวนั้น · คลิกตัวเลขเพื่อแก้ค่า · แก้สูตรได้ที่แถบสูตร หรือพิมพ์ = ในช่องว่าง

100%

ข้อมูลสมมติของสยามเฟรช ชุดเดียวกับชีต “1 ข้ามค่า Error” ในไฟล์ฝึก (ปี ค.ศ.)

อ่านสูตร: AGGREGATE(1,6,E5:E10) เลข 1 คือ AVERAGE และ 6 คือข้ามค่า Error
ลองพิมพ์เป้า 1,000,000 ใน C10 แล้วดูว่า AVERAGE กลับมาคำนวณได้ และทั้งสามช่องนับครบ 6 คน
ระวัง: ข้าม Error แล้วตัวเลขดูเรียบร้อย แต่ต้นเหตุยังอยู่ ควรรู้ก่อนว่า Error เกิดจากอะไร

ตัวอย่างทั้งหมด 4 แบบ

1เฉลี่ย % ของเป้าโดยข้าม #DIV/0! · ปิยะพงษ์ยังไม่มีเป้า E10 จึงเป็น #DIV/0! AVERAGE ได้ Error ตาม แต่ AGGREGATE ข้ามไปได้
2ข้ามทั้งแถวที่ซ่อนและค่า Error · ซ่อนสาขาภาคกลาง (แถว 5, 6, 12) เพื่อดูเฉพาะต่างจังหวัด ส่วนสาขาระยองยังไม่มีบิล E8 จึงเป็น #DIV/0!
3สาขาที่ยอดต่อบิลสูงสุดอันดับ k · 14 คือ LARGE และ 15 คือ SMALL ใส่ k เป็นอันดับที่ต้องการ แล้วข้าม #DIV/0! ของสาขาระยอง
4ยอดสูงสุดและต่ำสุดของภาคที่เลือก · หารยอดด้วยเงื่อนไข แถวที่ไม่ตรงกลายเป็น #DIV/0! แล้ว options 6 ข้ามไป เหลือเฉพาะภาคที่เลือก

ข้อผิดพลาดที่พบบ่อย

#VALUE!สาเหตุ: ใช้ฟังก์ชัน 14–19 แต่ไม่ได้ใส่ kแก้: ใส่ k เป็น argument ที่ 4 เช่น =AGGREGATE(14,6,E5:E12,1)

ยังได้ Errorสาเหตุ: options ที่เลือกไม่ข้ามค่า Error (0, 1, 4, 5)แก้: ใช้ options 2, 3, 6 หรือ 7

แถวที่ซ่อนยังถูกนับสาเหตุ: options เป็น 0, 2, 4, 6 หรือ array เป็นการคำนวณ ซึ่งไม่ข้ามแถวที่ซ่อนแก้: ใช้ options 1, 3, 5 หรือ 7 กับช่วงเซลล์ตรง ๆ

ซ่อนคอลัมน์แล้วผลไม่เปลี่ยนสาเหตุ: AGGREGATE ออกแบบมาสำหรับข้อมูลแนวตั้งแก้: จัดข้อมูลเป็นแนวตั้ง แล้วซ่อนหรือกรองเป็นแถว

เคล็ดลับจากห้องเรียน

พิมพ์ =AGGREGATE( แล้ว Excel แสดงรายการ function_num ให้เลือก ไม่ต้องจำเลขทั้ง 19 ตัว

ค่ามากสุดตามเงื่อนไขใน Excel 2016 ใช้ =AGGREGATE(14,6,ยอด/(เงื่อนไข),1) แทน MAXIFS ได้

ข้าม Error แล้วควรนับด้วยว่ามีกี่ช่อง เช่น =SUMPRODUCT(--ISERROR(E5:E12)) จะได้รู้ว่าต้องตามแก้กี่รายการ

ใช้กับ Excel ของคุณได้ไหม

เวอร์ชัน	Excel 2016	Excel 2019	Excel 2021	Excel 2024	Microsoft 365
AGGREGATE

AGGREGATE ใช้ได้ทุกเวอร์ชันที่ยังใช้กันอยู่ จึงไม่ต้องมีสูตรทางเลือก

options 0–7 ข้ามอะไรบ้าง (แถวที่ถูกกรองออกนับเป็นแถวที่ซ่อน)

options	แถวที่ซ่อน	ค่า Error	SUBTOTAL/AGGREGATE ที่ซ้อนในช่วง
0 · 4	ไม่ข้าม	ไม่ข้าม	0 ข้าม · 4 ไม่ข้าม
1 · 5	ข้าม	ไม่ข้าม	1 ข้าม · 5 ไม่ข้าม
2 · 6	ไม่ข้าม	ข้าม	2 ข้าม · 6 ไม่ข้าม
3 · 7	ข้าม	ข้าม	3 ข้าม · 7 ไม่ข้าม

ใน Power BI ใช้อะไร

แบบ 6 · ไม่มีใน DAX ใช้วิธีอื่นแทนยอดรวมของ visual, ALLSELECTED

ยอดรวมใน Matrix คำนวณ Measure ใหม่โดยไม่มีตัวกรองของแถว สัดส่วนต่อยอดที่เลือกใช้ ALLSELECTED

DAX · ALLSELECTED DAX · SUM

ไฟล์ฝึก AGGREGATE

9Expert-AGGREGATE-Practice-v1.2.xlsx

รุ่น 1.2 · 24/9/2026 · 28 KB · ไม่มี Macro · ข้อมูลชุดเดียวกับตัวอย่างบนเว็บ · มีเฉลยและตรวจคำตอบอัตโนมัติ

เริ่มต้นที่นี่1 ข้ามค่า Error2 ซ่อนแถวและ Error3 อันดับที่ k4 มากสุดตามเงื่อนไขแบบฝึกหัดเฉลยสรุปย่อ

โจทย์ในชีตแบบฝึกหัด

ข้อ 1รวมยอดสุทธิทั้งหมด โดยข้ามค่า Error
ข้อ 2ยอดสุทธิสูงสุดอันดับ 2
ข้อ 3ยอดสุทธิต่ำสุดอันดับ 2
ข้อ 4ยอดสุทธิสูงสุดของภาคกลาง (หารยอดด้วยเงื่อนไขภาค)
ข้อ 5ยอดสุทธิต่ำสุดของช่องทางในช่อง C11

วิดีโอประกอบ

คลิปเรื่อง AGGREGATE จากช่อง YouTube @9expert

ในเว็บจริงจะฝังคลิปพร้อมตั้งเวลาเริ่มตรงหัวข้อนี้ ในต้นแบบเปิดผลค้นหาในช่องแทน

เปิดใน YouTube

แบบทดสอบ 3 ข้อ

1. =AGGREGATE(9,6,E5:E12) หมายถึงอะไร

2. ต้องการข้ามทั้งแถวที่ซ่อนและค่า Error ใช้ options เท่าไร

3. =AGGREGATE(14,6,E5:E12) โดยไม่ใส่ k จะได้อะไร

ไปต่อ

SUBTOTAL · หน้าเต็ม IFERROR · หน้าเต็ม LARGE · หน้าเต็ม SMALL · หน้าเต็ม MAXIFS · หน้าเต็ม

อยากใช้สูตรเหล่านี้กับงานจริงทั้งระบบ

ต่อยอดใน Microsoft Excel Advanced: สูตรสรุปผลแบบมีเงื่อนไข สูตรสืบค้นข้อมูล สูตรใหม่ใน Microsoft 365 และการจัดการ Error (2 วัน)

ดูหลักสูตร

หน้านี้ช่วยได้ไหม