Thep Excel
AGGREGATEคำนวณแบบละเว้น Error และแถวที่ซ่อน
AGGREGATE เป็นฟังก์ชันสารพัดประโยชน์ที่รวม 19 ฟังก์ชันต่างๆ (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) แต่มีความสามารถพิเศษคือ สามารถข้าม Error ได้ และข้ามแถวที่ถูกซ่อน (Hidden Rows) ได้ เหมาะสำหรับ Dashboard, รายงาน หรือข้อมูลที่อาจมีช่องว่างและข้อมูลผิดพลาด
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| function_num | Number (1-19) | รหัสของฟังก์ชันที่ต้องการใช้: 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) | ตัวเลือกการข้าม: 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 | ช่วงข้อมูลแรกที่ต้องการคำนวณ เช่น A1:A10 หรือ Sales[Amount] |
| [ref2]ไม่บังคับ | Range / Array | ช่วงข้อมูลที่สอง (ถ้าฟังก์ชันต้องการ 2 ref เช่น Subtotal reference form) – บางฟังก์ชันเช่น LARGE, SMALL, PERCENTILE, QUARTILE ต้องมี ref2 เพื่อระบุตำแหน่ง k |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
AGGREGATE ใน Excel คือฟังก์ชันอเนกประสงค์ที่ยืดหยุ่นกว่า SUM/AVERAGE/COUNT ปกติ เพราะมันสามารถ ‘เมิน Error’ และ ‘เมินแถวที่ซ่อน’ ได้ แน่นอนว่า SUBTOTAL ก็ทำได้บ้าง แต่ AGGREGATE เก่งกว่าเยอะ
ที่เจ๋งคือ AGGREGATE มี 19 ฟังก์ชันให้เลือกใช้งาน ตั้งแต่การหาผลรวม หาค่าเฉลี่ย นับจำนวน หาค่ามัธยฐาน ค่าเบี่ยงเบนมาตรฐาน หาค่าสูงสุดอันดับต่างๆ ค่าต่ำสุดอันดับต่างๆ เปอร์เซ็นไทล์ ควอร์ไทล์ และอื่นๆ อีกมากมาย พร้อมด้วย 7 ตัวเลือกการข้าม (Ignore Options) ที่ให้คุณจัดการกับความยุ่งยากของข้อมูลจริง – ไม่ว่าจะเป็นข้อมูลที่มีค่าผิดพลาด ข้อมูลที่ถูกซ่อนเพราะการกรอง หรือข้อมูลที่ถูกจัดเรียงและซ่อนบางแถวไว้
ส่วนตัวผม AGGREGATE คือ ‘ผู้บัญชาการแห่งการคำนวณ’ เลยครับ ถ้าคุณใช้ Excel เพื่อสร้างแดชบอร์ดหรือรายงานที่มีการซ่อนและแสดงข้อมูล และมักเจอข้อมูลที่มีค่าผิดพลาดปะปนอยู่ การใช้ AGGREGATE จะช่วยให้การคำนวณแม่นยำและไม่ต้องกังวลกับข้อมูลที่ไม่สมบูรณ์ ลองใช้ดูครับแล้วจะรู้ว่าช่วยได้มากแค่ไหน
Examples
หาผลรวมโดยข้าม Error (SUM + Ignore Error)AGGREGATE(9, 6, A2:A10)
function_num=9 หมายถึง SUM, options=6 หมายถึงข้าม error values เท่านั้น – ดีเมื่อข้อมูลมี #DIV/0! หรือ #N/A ในบางช่อง ฟังก์ชัน SUM ปกติจะคืน error แต่ AGGREGATE จะคำนวณเฉพาะที่ปกติ
นับจำนวนแบบไม่นับแถวที่ซ่อน (COUNT + Ignore Hidden)AGGREGATE(2, 5, B2:B100)
function_num=2 คือ COUNT, options=5 คือ ignore hidden rows – ใช้เมื่อคุณกรอง (Filter) ข้อมูล คุณต้องการนับเฉพาะแถวที่แสดง ไม่ใช่แถวทั้งหมดของตาราง
หาค่ามากสุดอันดับ 2 (LARGE + Ignore Error)AGGREGATE(14, 6, Salary[Amount], 2)
function_num=14 คือ LARGE, ref2=2 คือหา 2 ตัวมากสุด, options=6 คือข้าม error – เหมาะสำหรับหา Top N values พร้อมกับจัดการ error ในตารางเดียว
หาค่าเฉลี่ย พร้อมข้ามแถวที่ซ่อน + Error (AVERAGE + Ignore Hidden + Error)AGGREGATE(1, 7, D2:D50)
function_num=1 คือ AVERAGE, options=7 คือข้าม hidden rows AND error values – ใช้สำหรับ Dashboard ที่มีการ Filter ข้อมูลอยู่เสมอ
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ AGGREGATE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
AVERAGE คืนค่าเฉลี่ยของกลุ่มตัวเลขที่ระบุ โดยจะนับเฉพาะเซลล์ที่มีตัวเลขและค่า 0 เท่านั้น ส่วนเซลล์ว่างหรือข้อความจะถูกข้ามไป ไม่นำมาเป็นตัวหาร ซึ่งหลายคนมักพลาดความแตกต่างระหว่าง 0 กับเซลล์ว่าง ตรงนี้สำคัญมากครับ เพราะมันทำให้ค่าเฉลี่ยที่ได้ต่างกันเลย
COUNT นับเฉพาะเซลล์ที่มี Data Type เป็นตัวเลข (Number) โดยเพิกเฉยเซลล์ว่าง ข้อความ ค่า Logic และ error values โดยอัตโนมัติ รวมถึงตัวเลขลบ เปอร์เซ็นต์ วันที่ เวลา เศษส่วน และผลลัพธ์จากสูตรที่คืนค่าเป็นตัวเลข ทำให้ไม่ต้องกังวลว่าจะนับข้อมูลผิดถ้ามีข้อความปนอยู่ในช่วง
COUNTA นับจำนวนเซลล์ที่มีข้อมูลทุกประเภท ไม่ว่าจะเป็นตัวเลข ข้อความ ค่า Logic (TRUE/FALSE) Error Values หรือแม้แต่ข้อความว่าง ("") ที่เกิดจากสูตร
.
เรียกได้ว่าเป็นเครื่องมือหลักในการตรวจสอบความสมบูรณ์ของข้อมูล หรือนับจำนวนรายการโดยไม่สนใจว่าข้อมูลจะเป็น Data Type ใดก็ตาม
LARGE คืนค่าตัวเลขที่มากที่สุดในลำดับที่ k จากช่วงข้อมูล (array) ถ้า k=1 จะได้ค่าเดียวกับ MAX ถ้า k=n จะได้ค่าที่น้อยที่สุด (MIN) ใช้สำหรับจัดอันดับข้อมูลหรือดึงค่า Top N ออกมาวิเคราะห์
MAX คืนค่าสูงสุดจากชุดข้อมูลที่มี Data Type เป็นตัวเลข เพิกเฉยเซลล์ว่าง ข้อความ และค่า Logic โดยอัตโนมัติ ทำให้ไม่ต้องกังวลว่าจะมีข้อมูลประเภทอื่นปนอยู่ในช่วง เหมาะสำหรับหาค่าสูงสุดเช่น คะแนนสูงสุด ยอดขายสูงสุด หรือวันที่ล่าสุด และยังใช้เทคนิค Clamp (จำกัดค่า) โดยการใส่ 0 เป็น argument แรกเพื่อบังคับให้ค่าลบกลายเป็น 0 ได้อีกด้วย
MEDIAN คืนค่าที่อยู่ตำแหน่งตรงกลางของชุดข้อมูลที่เรียงลำดับแล้ว หากมีจำนวนข้อมูลเป็นเลขคี่ จะได้ค่าตรงกลางพอดี แต่ถ้าเป็นเลขคู่ จะนำ 2 ค่าตรงกลางมาหาค่าเฉลี่ย เหมาะสำหรับหาค่ากลางของข้อมูลที่มีค่าสุดโต่ง (Outliers) ปะปนอยู่ (เช่น เงินเดือน หรือราคาบ้าน)
MIN คืนค่าต่ำสุดจากชุดข้อมูลที่มี Data Type เป็นตัวเลข เพิกเฉยเซลล์ว่าง ข้อความ และค่า Logic โดยอัตโนมัติ ซึ่งทำให้ไม่ต้องกลัวว่าจะมีข้อมูลปนมารบกวนผลลัพธ์ ใช้ได้กับตัวเลขทั่วไป วันที่ (ค่าน้อยสุด = วันเก่าสุด) และระยะเวลา สามารถใช้ร่วมกับ MATCH เพื่อหาตำแหน่ง หรือใช้ร่วมกับ MAX เพื่อจำกัดค่าอยู่ในช่วงที่กำหนด
หาค่าต่ำสุดของรายการ พร้อมนับรวมข้อความและค่าตรรกะ ต่างจาก MIN ที่ไม่สนใจข้อความ
MINIFS ช่วยหาค่าต่ำสุดของข้อมูลที่ตรงตามเงื่อนไขที่กำหนด เหมือนการใช้ MIN แต่มีความสามารถในการกรองข้อมูลก่อน
SMALL คืนค่าตัวเลขที่น้อยที่สุดในลำดับที่ k จากช่วงข้อมูล (array) ถ้า k=1 จะได้ค่าเดียวกับ MIN ถ้า k=n จะได้ค่าที่มากที่สุด (MAX) ใช้สำหรับจัดอันดับข้อมูลหรือดึงค่า Bottom N ออกมาวิเคราะห์
MODE คืนค่าตัวเลขที่เกิดขึ้นบ่อยที่สุดในกลุ่มข้อมูล หากมีค่าที่ความถี่สูงสุดเท่ากันหลายค่า MODE จะคืนค่าตัวแรกที่พบ และถ้าไม่มีค่าซ้ำกันเลย จะคืนค่า #N/A (ใน Excel รุ่นใหม่แนะนำให้ใช้ MODE.SNGL หรือ MODE.MULT แทน)
คำนวณค่าเปอร์เซ็นไทล์ที่ k ของชุดข้อมูล เช่น P90 หรือ P50 เหมาะสำหรับวิเคราะห์การกระจายตัวของข้อมูลและกำหนดเกณฑ์มาตรฐาน
STDEV คำนวณส่วนเบี่ยงเบนมาตรฐาน (Standard Deviation) สำหรับกลุ่มตัวอย่าง โดยใช้วิธี n-1
SUBTOTAL คำนวณผลรวมย่อยหรือสถิติอื่นๆ ที่สามารถ "ตัดแถวซ่อนออก" ได้อัตโนมัติ ต่างจาก SUM ที่รวมทุกอย่าง
SUM รวมเฉพาะข้อมูลที่มี Data Type เป็นตัวเลข (Number) เท่านั้น ไม่สนใจข้อความและค่า Logic ทำให้ไม่ต้องกลัวว่าจะรวมข้อมูลผิดถ้ามีข้อความปนอยู่ในช่วง รองรับสูงสุด 255 พารามิเตอร์ และอัปเดตอัตโนมัติเมื่อข้อมูลเปลี่ยน เป็นฟังก์ชันพื้นฐานที่ใช้บ่อยที่สุดในงาน Excel
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่