วิธีใช้ฟังก์ชัน AGGREGATE ใน Excel | (พร้อมตัวอย่าง)

ฟังก์ชัน AGGREGATE ใน Excel

ฟังก์ชัน AGGREGATE ใน excelจะส่งคืนการรวมของตารางข้อมูลหรือรายการข้อมูลที่กำหนดฟังก์ชันนี้ยังมีอาร์กิวเมนต์แรกเป็นหมายเลขฟังก์ชันและอาร์กิวเมนต์เพิ่มเติมสำหรับช่วงของชุดข้อมูลควรจำหมายเลขฟังก์ชันเพื่อให้ทราบว่าจะใช้ฟังก์ชันใด .

ไวยากรณ์

มีไวยากรณ์สองแบบสำหรับสูตร AGGREGATE:

  1. ไวยากรณ์อ้างอิง

= AGGREGATE (function_num, ตัวเลือก, ref1, ref2, ref [3], ... )

  1. ไวยากรณ์อาร์เรย์

= AGGREGATE (function_num, ตัวเลือก, อาร์เรย์, [k])

Function_numคือตัวเลขที่แสดงถึงฟังก์ชันเฉพาะที่เราต้องการใช้ซึ่งเป็นตัวเลขตั้งแต่ 1-19

ตัวเลือก:นอกจากนี้ยังเป็นค่าตัวเลขตั้งแต่ 0 ถึง 7 และกำหนดว่าจะละเว้นค่าใดในขณะคำนวณ

Ref1, ref2, ref [3]:  เป็นอาร์กิวเมนต์ในขณะที่ใช้ไวยากรณ์อ้างอิงซึ่งเป็นค่าตัวเลขหรือค่าที่เราต้องการใช้ในการคำนวณอาร์กิวเมนต์ที่เหลืออย่างน้อยสองอาร์กิวเมนต์เป็นทางเลือกที่จำเป็น

Array:เป็นอาร์เรย์ของค่าที่เราต้องการดำเนินการโดยใช้ในไวยากรณ์อาร์เรย์ของฟังก์ชัน AGGREGATE ใน excel

K:เป็นอาร์กิวเมนต์ที่เป็นทางเลือกและเป็นค่าตัวเลขซึ่งจะใช้เมื่อใช้ฟังก์ชันเช่น LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC หรือ QUARTILE.EXC ใน Excel

ตัวอย่าง

คุณสามารถดาวน์โหลดเทมเพลต Excel ของฟังก์ชัน AGGREGATE ได้ที่นี่ - เทมเพลต Excel ของฟังก์ชัน AGGREGATE

ตัวอย่าง - # 1

สมมติว่าเรามีรายการของตัวเลขและเราจะคำนวณค่าเฉลี่ยจำนวนที่เป็นจำนวนเซลล์ที่มีค่าจำนวนจำนวนเซลล์ที่ไม่ว่างค่าสูงสุดขั้นต่ำผลิตภัณฑ์และผลรวมของค่าตัวเลขที่กำหนด ค่าต่างๆแสดงไว้ด้านล่างในตาราง:

ก่อนอื่นให้เราคำนวณค่าเฉลี่ยในแถวที่ 9 สำหรับค่าที่กำหนดทั้งหมด สำหรับค่าเฉลี่ย function_ num คือ

ในคอลัมน์ C ค่าทั้งหมดจะได้รับและเราจะไม่ต้องเพิกเฉยต่อค่าใด ๆ ดังนั้นเราจะเลือกตัวเลือกที่ 4

และการเลือกช่วงของค่า C1: C8 เป็นอาร์เรย์ของค่าตัวเลข

เนื่องจาก ' k'เป็นอาร์กิวเมนต์ที่เป็นทางเลือกและใช้เมื่อมีการใช้ฟังก์ชันเช่น LARGE, SMALL ใน Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC หรือ QUARTILE.EXC แต่ในกรณีนี้เรากำลังคำนวณค่าเฉลี่ยดังนั้นเรา จะละเว้นค่าของ k

ดังนั้นค่าเฉลี่ยคือ

ในทำนองเดียวกันสำหรับช่วง D1: D8 เราจะเลือกตัวเลือก 4 อีกครั้ง

สำหรับช่วง E1: E8 เซลล์ E6 จะมีค่าความผิดพลาดหากเราจะใช้สูตร AGGREGATE เดียวกันเราจะได้รับข้อผิดพลาด แต่เมื่อใช้ตัวเลือกที่เหมาะสม AGGREGATE ใน Excel จะให้ค่าเฉลี่ยของค่าที่เหลือโดยไม่สนใจข้อผิดพลาด ค่าใน E6

ในการละเว้นค่าความผิดพลาดเรามีตัวเลือก 6

ในทำนองเดียวกันสำหรับช่วง G1: G8 เราจะใช้ตัวเลือก 6 (ละเว้นค่าความผิดพลาด)

ตอนนี้สำหรับช่วง H3 ถ้าเราใส่ค่า 64 และซ่อนแถวที่สามและใช้ตัวเลือก 5 เพื่อละเว้นแถวที่ซ่อนอยู่ AGGREGATE ใน Excel เราจะให้ค่าเฉลี่ยสำหรับค่าตัวเลขที่มองเห็นได้เท่านั้น

เอาต์พุตโดยไม่ต้องซ่อนแถวที่ 3

เอาต์พุตหลังจากซ่อนแถว 3

เราใช้สูตร AGGREGATE สำหรับการดำเนินการอื่น ๆ

ตัวอย่าง - # 2

สมมติว่าเรามีตารางสำหรับรายได้ที่สร้างขึ้นในวันที่ต่างกันจากช่องต่างๆตามที่ระบุด้านล่าง

ตอนนี้เราต้องการตรวจสอบรายได้ที่สร้างขึ้นสำหรับช่องต่างๆ ดังนั้นเมื่อเราใช้ฟังก์ชัน sum เราจะได้รับรายได้ทั้งหมดที่สร้างขึ้น แต่ในกรณีที่เราต้องการตรวจสอบรายได้ที่สร้างขึ้นสำหรับช่องทางออร์แกนิกหรือช่องทางโดยตรงหรืออื่น ๆ เมื่อเราใช้ตัวกรองใน excel สำหรับสิ่งเดียวกันฟังก์ชัน sum จะเสมอ ให้ผลรวมทั้งหมด

เราต้องการให้เมื่อเรากรองช่องเราจะได้ผลรวมของค่าที่มองเห็นได้ดังนั้นแทนที่จะใช้ฟังก์ชัน SUM เราจะใช้ฟังก์ชัน AGGREGATE เพื่อให้ได้ผลรวมของค่าที่มองเห็นได้เมื่อตัวกรองเป็น นำไปใช้

ดังนั้นการแทนที่สูตร SUM ด้วยฟังก์ชัน AGGREGATE ด้วยรหัสตัวเลือก 5 (ไม่สนใจแถวและค่าที่ซ่อนอยู่) ที่เรามี

ตอนนี้เมื่อเราจะใช้ตัวกรองสำหรับแชแนลต่างๆระบบจะแสดงรายได้สำหรับแชแนลนั้นเฉพาะเมื่อแถวที่เหลือซ่อนอยู่

รายได้ทั้งหมดที่สร้างขึ้นสำหรับช่องทางตรง:

รายได้ทั้งหมดที่สร้างขึ้นสำหรับแชแนลทั่วไป:

รายได้ทั้งหมดที่สร้างขึ้นสำหรับช่องทางการชำระเงิน:

ดังนั้นเราจึงสามารถเห็นฟังก์ชัน AGGREGATE คำนวณค่าผลรวมที่แตกต่างกันสำหรับรายได้ที่สร้างขึ้นสำหรับแชแนลต่างๆเมื่อมีการกรอง ดังนั้นฟังก์ชัน AGGREGATE จึงสามารถใช้แบบไดนามิกเพื่อแทนที่ฟังก์ชันต่างๆสำหรับเงื่อนไขที่แตกต่างกันโดยไม่ต้องใช้สูตรเงื่อนไข

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

เมื่อเราใช้ตัวเลือก 5 เราได้รับข้อผิดพลาดสำหรับ SUM ของรายได้ทั้งหมดตอนนี้เพื่อที่จะละเว้นข้อผิดพลาดที่เราต้องใช้ตัวเลือก 6

การใช้ตัวเลือก 6 เราจะได้ผลรวมโดยไม่สนใจค่าความผิดพลาด แต่เมื่อเราใช้ตัวกรองตัวอย่างเช่นกรองตามค่าช่องทางโดยตรงเราจะได้ผลรวมเดียวกันโดยไม่สนใจข้อผิดพลาด แต่ในขณะเดียวกันเราก็ต้องละเว้นค่าที่ซ่อนอยู่ด้วย

ดังนั้นในกรณีนี้เราจะใช้อ็อพชัน 7 ที่ละเว้นค่าความผิดพลาดและในเวลาเดียวกันกับแถวที่ซ่อนอยู่

สิ่งที่ต้องจำ

  • ฟังก์ชัน AGGREGATE ไม่รู้จักค่าฟังก์ชัน _ num ที่มากกว่า 19 หรือน้อยกว่า 1 และในทำนองเดียวกันสำหรับหมายเลขตัวเลือกจะไม่รู้จักค่าที่มากกว่า 7 และน้อยกว่า 1 หากเราระบุค่าอื่นใดจะให้ #VALUE ! ข้อผิดพลาด
  • ยอมรับค่าตัวเลขและส่งกลับค่าตัวเลขเป็นเอาต์พุตเสมอ
  • AGGREGATE ใน Excel มีข้อ จำกัด จะไม่สนใจเฉพาะแถวที่ซ่อนอยู่ แต่ไม่ละเว้นคอลัมน์ที่ซ่อนอยู่