Thep Excel
AGGREGATE – คำนวณแบบละเว้น Error และแถวที่ซ่อน
AGGREGATE เป็นฟังก์ชันสารพัดประโยชน์ที่รวม 19 ฟังก์ชันต่างๆ (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) แต่มีความสามารถพิเศษคือ สามารถข้าม Error ได้ และข้ามแถวที่ถูกซ่อน (Hidden Rows) ได้ เหมาะสำหรับ Dashboard, รายงาน หรือข้อมูลที่อาจมีช่องว่างและข้อมูลผิดพลาด
=AGGREGATE(function_num, options, ref1, [ref2], ...)
Function Metrics
Popularity
6/10
Difficulty
3/10
Usefulness
7/10
Syntax & Arguments
=AGGREGATE(function_num, options, ref1, [ref2], ...)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| function_num | Number (1-19) | Yes | รหัสของฟังก์ชันที่ต้องการใช้: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11=VARP, 12=MEDIAN, 13=MODE, 14=LARGE, 15=SMALL, 16=PERCENTILE, 17=QUARTILE, 18=RANK, 19=AGGREGATE | |
| options | Number (0-7) | Yes | ตัวเลือกการข้าม: 0=ไม่ข้ามอะไร แต่ข้าม SUBTOTAL/AGGREGATE nested, 1=ข้ามแถวที่ซ่อน + nested functions, 2=ข้ามแถวต่างๆ (ใช้ไม่ได้), 3=ข้าม hidden rows + error values, 4=ไม่ข้ามอะไรเลย, 5=ข้ามแถวที่ซ่อนเท่านั้น, 6=ข้าม error values เท่านั้น, 7=ข้าม hidden rows + error values | |
| ref1 | Range / Array | Yes | ช่วงข้อมูลแรกที่ต้องการคำนวณ เช่น A1:A10 หรือ Sales[Amount] | |
| ref2 | Range / Array | Optional | ช่วงข้อมูลที่สอง (ถ้าฟังก์ชันต้องการ 2 ref เช่น Subtotal reference form) – บางฟังก์ชันเช่น LARGE, SMALL, PERCENTILE, QUARTILE ต้องมี ref2 เพื่อระบุตำแหน่ง k |
Examples
หาผลรวมโดยข้าม Error (SUM + Ignore Error)
AGGREGATE(9, 6, A2:A10)
=AGGREGATE(9, 6, A2:A10)
ผลรวมของค่าตัวเลขทั้งหมดในช่วง A2:A10 ไม่รวม Error
นับจำนวนแบบไม่นับแถวที่ซ่อน (COUNT + Ignore Hidden)
AGGREGATE(2, 5, B2:B100)
=AGGREGATE(2, 5, B2:B100)
จำนวนแถวที่ไม่ว่างใน B2:B100 (ไม่นับแถวที่ถูก Filter ออก)
หาค่ามากสุดอันดับ 2 (LARGE + Ignore Error)
AGGREGATE(14, 6, Salary[Amount], 2)
=AGGREGATE(14, 6, Salary[Amount], 2)
ค่าเงินเดือนมากสุดลำดับที่ 2
หาค่าเฉลี่ย พร้อมข้ามแถวที่ซ่อน + Error (AVERAGE + Ignore Hidden + Error)
AGGREGATE(1, 7, D2:D50)
=AGGREGATE(1, 7, D2:D50)
ค่าเฉลี่ยของเฉพาะแถวที่แสดง (ไม่ซ่อนและไม่ error)
FAQs
AGGREGATE vs SUBTOTAL ต่างกันยังไง?
SUBTOTAL ข้าม hidden rows ได้ (11 ฟังก์ชัน) แต่ AGGREGATE เก่งกว่าเพราะข้าม error ได้ด้วย และมี 19 ฟังก์ชัน ไม่ว่าจะ LARGE, SMALL, PERCENTILE ที่ SUBTOTAL ไม่มี
options=0 กับ options=4 ต่างกันยังไง?
options=0 ข้าม nested SUBTOTAL/AGGREGATE แต่ยังข้าม error, options=4 ไม่ข้ามอะไร (รวม error, nested functions ทั้งหมด)
ใช้ได้กับ Excel เวอร์ชันไหน?
AGGREGATE มีอยู่ตั้งแต่ Excel 2010 ขึ้นไป ทั้ง Windows, Mac และ Excel 365
function_num เลขอะไร คือ SUM?
function_num=9 คือ SUM, ลองดูตาราง function_num ด้านบน
AGGREGATE จะ recalculate ทุกครั้งที่ Filter หรือเปลี่ยนข้อมูลไหม?
ใช่ AGGREGATE recalculate automatic เมื่อ ref1/ref2 เปลี่ยน หรือเมื่อซ่อน/แสดงแถว
Resources & Related
Resources
Related posts
Related functions
Additional Notes
AGGREGATE ใน Excel คือฟังก์ชันอเนกประสงค์ที่ยืดหยุ่นกว่า SUM/AVERAGE/COUNT ปกติ เพราะมันสามารถ ‘เมิน Error’ และ ‘เมินแถวที่ซ่อน’ ได้ แน่นอนว่า SUBTOTAL ก็ทำได้บ้าง แต่ AGGREGATE เก่งกว่าเยอะ
ที่เจ๋งคือ AGGREGATE มี 19 ฟังก์ชันให้เลือกใช้งาน ตั้งแต่การหาผลรวม หาค่าเฉลี่ย นับจำนวน หาค่ามัธยฐาน ค่าเบี่ยงเบนมาตรฐาน หาค่าสูงสุดอันดับต่างๆ ค่าต่ำสุดอันดับต่างๆ เปอร์เซ็นไทล์ ควอร์ไทล์ และอื่นๆ อีกมากมาย พร้อมด้วย 7 ตัวเลือกการข้าม (Ignore Options) ที่ให้คุณจัดการกับความยุ่งยากของข้อมูลจริง – ไม่ว่าจะเป็นข้อมูลที่มีค่าผิดพลาด ข้อมูลที่ถูกซ่อนเพราะการกรอง หรือข้อมูลที่ถูกจัดเรียงและซ่อนบางแถวไว้
ส่วนตัวผม AGGREGATE คือ ‘ผู้บัญชาการแห่งการคำนวณ’ เลยครับ ถ้าคุณใช้ Excel เพื่อสร้างแดชบอร์ดหรือรายงานที่มีการซ่อนและแสดงข้อมูล และมักเจอข้อมูลที่มีค่าผิดพลาดปะปนอยู่ การใช้ AGGREGATE จะช่วยให้การคำนวณแม่นยำและไม่ต้องกังวลกับข้อมูลที่ไม่สมบูรณ์ ลองใช้ดูครับแล้วจะรู้ว่าช่วยได้มากแค่ไหน