ส่งกลับการรวมในรายการหรือฐานข้อมูล ฟังก์ชัน 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 |
ละเว้นแถวที่ถูกซ่อนและค่าความผิดพลาด |
-
Ref1 จำเป็น อาร์กิวเมนต์ตัวเลขตัวแรกสําหรับฟังก์ชันที่ใช้อาร์กิวเมนต์ตัวเลขหลายตัวที่คุณต้องการหาค่าการรวม
-
Ref2,... ไม่จำเป็น อาร์กิวเมนต์ตัวเลขที่ 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! |
