ส่งกลับการรวมในรายการหรือฐานข้อมูล ฟังก์ชัน AGGREGATE สามารถนําฟังก์ชันการรวมที่แตกต่างกันไปใช้กับรายการหรือฐานข้อมูลโดยสามารถเลือกที่จะละเว้นแถวที่ซ่อนอยู่และค่าความผิดพลาดได้อีกด้วย
ไวยากรณ์
รูปแบบที่เป็นการอ้างอิง
AGGREGATE(function_num, options, ref1, [ref2], …)
รูปแบบอาร์เรย์
AGGREGATE(function_num, options, array, [k])
ไวยากรณ์ของฟังก์ชัน AGGREGATE มีอาร์กิวเมนต์ดังนี้
- Function_num จําเป็น ตัวเลข 1 ถึง 19 ซึ่งระบุฟังก์ชันที่ต้องการใช้
| Function_num | ฟังก์ชัน |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SUM |
| 10 | VAR.S |
| 11 | VAR.P |
| 1.2 | MEDIAN |
| 1.3 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
ตัวเลือก จําเป็น ค่าตัวเลขที่กําหนดว่าจะละเว้นค่าใดในช่วงการประเมินสําหรับฟังก์ชัน
หมายเหตุ
ฟังก์ชันจะไม่ละเว้นแถวที่ซ่อนอยู่ ฟังก์ชัน SUBTOTAL หรือ AGGREGATE ที่ซ้อน ถ้าอาร์กิวเมนต์อาร์เรย์มีการคํานวณ เช่น: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
| ตัวเลือก | ลักษณะการทำงาน |
|---|---|
| 0 หรือไม่นับ | ละเว้นฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน |
| 1 | ละเว้นแถวที่ถูกซ่อน ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน |
| 2 | ละเว้นค่าความผิดพลาด ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน |
| 3 | ละเว้นแถวที่ถูกซ่อน ค่าความผิดพลาด ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน |
| 4 | ไม่ละเว้นสิ่งใด |
| 5 | ละเว้นแถวที่ถูกซ่อน |
| 6 | ละเว้นค่าความผิดพลาด |
| 7 | ละเว้นแถวที่ถูกซ่อนและค่าความผิดพลาด |
- อ้างอิง 1 จําเป็น อาร์กิวเมนต์ตัวเลขค่าแรกสําหรับฟังก์ชันซึ่งรับอาร์กิวเมนต์ตัวเลขหลายค่าที่คุณต้องการหาค่าการรวม
-
อ้างอิง 2,... ไม่บังคับ อาร์กิวเมนต์ตัวเลขตั้งแต่ 2 ถึง 253 ที่คุณต้องการหาค่าการรวม
สําหรับฟังก์ชันที่รับอาร์เรย์ Ref1 คืออาร์เรย์ สูตรอาร์เรย์ หรือการอ้างอิงไปยังช่วงของเซลล์ที่คุณต้องการค่าการรวม Ref2 คืออาร์กิวเมนต์ที่สองซึ่งต้องระบุสําหรับบางฟังก์ชัน ฟังก์ชันต่อไปนี้ต้องการอาร์กิวเมนต์ ref2
| ฟังก์ชัน |
|---|
| LARGE(array,k) |
| SMALL(array,k) |
| PERCENTILE.INC(array,k) |
| QUARTILE.INC(array,quart) |
| PERCENTILE.EXC(array,k) |
| QUARTILE.EXC(array,quart) |
ข้อสังเกต
Function_num:
- ทันทีที่คุณพิมพ์อาร์กิวเมนต์ function_num เมื่อคุณใส่ฟังก์ชัน AGGREGATE ลงในเซลล์บนเวิร์กชีต คุณจะได้รับรายการของฟังก์ชันทั้งหมดที่คุณสามารถใช้เป็นอาร์กิวเมนต์ได้
ข้อผิดพลาด:
- ถ้าจําเป็นต้องมีอาร์กิวเมนต์ ref ที่สองแต่ไม่ได้ระบุไว้ AGGREGATE จะส่งกลับ #VALUE! ข้อผิดพลาด
- ถ้าการอ้างอิงอย่างน้อยหนึ่งอย่างเป็นการอ้างอิงสามมิติ AGGREGATE จะส่งกลับ #VALUE! เป็นค่าความผิดพลาด
ชนิดของช่วง
- ฟังก์ชัน AGGREGATE ได้รับการออกแบบสําหรับคอลัมน์ของข้อมูล หรือช่วงแนวตั้ง ซึ่งไม่ได้ออกแบบมาสําหรับแถวของข้อมูล หรือช่วงแนวนอน ตัวอย่างเช่น เมื่อคุณหาผลรวมย่อยของช่วงแนวนอนโดยใช้ตัวเลือก 1 เช่น AGGREGATE(1, 1, ref1) การซ่อนคอลัมน์จะไม่มีผลต่อค่าผลรวมของการรวม แต่การซ่อนแถวในช่วงแนวตั้งจะมีผลต่อการรวม
ตัวอย่าง
คัดลอกข้อมูลตัวอย่างในตารางต่อไปนี้ และวางในเซลล์ A1 ของเวิร์กชีต Excel ใหม่ สำหรับสูตรที่จะแสดงผลลัพธ์ ให้เลือกสูตร กด F2 แล้วกด Enter ถ้าคุณต้องการ คุณสามารถปรับความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมดได้
| #DIV/0! | 82 | |
|---|---|---|
| 72 | 65 | |
| 30 | 95 | |
| #NUM! | 63 | |
| 31 | 53 | |
| 96 | 71 | |
| 32 | 55 | |
| 81 | 83 | |
| 33 | 100 | |
| 53 | 91 | |
| 34 | 89 | |
| สูตร | คำอธิบาย | ผลลัพธ์ |
| =AGGREGATE(4, 6, A1:A11) | คำนวณค่าสูงสุดในขณะที่ละเว้นค่าความผิดพลาดในช่วง | 96 |
| =AGGREGATE(14, 6, A1:A11, 3) | คำนวณค่าที่มากที่สุดในอันดับที่ 3 ในขณะที่ละเว้นค่าความผิดพลาดในช่วง | 72 |
| =AGGREGATE(15, 6, A1:A11) | จะกลับมา #VALUE! ข้อผิดพลาด ทั้งนี้เนื่องจาก AGGREGATE ต้องการอาร์กิวเมนต์ ref ที่สอง เนื่องจากฟังก์ชัน (SMALL) ต้องการ | #VALUE! |
| =AGGREGATE(12, 6, A1:A11, B1:B11) | คำนวณมัธยฐาน ในขณะที่ละเว้นค่าความผิดพลาดในช่วง | 68 |
| =MAX(A1:A2) | จะส่งกลับค่าความผิดพลาด เนื่องจากมีค่าความผิดพลาดอยู่ในช่วงการประเมิน | #DIV/0! |