หาค่าเฉลี่ยตามหลายเงื่อนไขด้วย AVERAGEIFS

AVERAGEIFS คำนวณค่าเฉลี่ยของค่าที่ตรงกับเงื่อนไขหลายข้อในเวลาเดียวกัน แทนที่จะหาค่าเฉลี่ยของยอดขายทั้งหมด คุณสามารถถามได้ว่า ยอดขายเฉลี่ยของภูมิภาคตะวันออกในเดือนมกราคมเป็นเท่าไร

ฟังก์ชันนี้เติมเต็มชุดฟังก์ชันทั้งสาม: SUMIFS หาผลรวม COUNTIFS นับจำนวน และ AVERAGEIFS หาค่าเฉลี่ย โดยทั้งหมดใช้รูปแบบหลายเงื่อนไขเดียวกัน หากคุณรู้จักฟังก์ชันหนึ่ง ก็แทบจะรู้จักทั้งสามฟังก์ชันแล้ว

AVERAGEIFS ทำงานเหมือนกับ SUMIFS ทุกประการ ค่าที่จะนำมาหาค่าเฉลี่ยจะอยู่ก่อน ตามด้วยคู่เงื่อนไข

• average_range — ตัวเลขที่จะนำมาหาค่าเฉลี่ย
• criteria_range1, criteria1
• criteria_range2, criteria2

เบื้องหลัง ฟังก์ชันนี้จะรวมค่าที่ตรงเงื่อนไขแล้วหารด้วยจำนวนค่าที่ตรงเงื่อนไข ซึ่งโดยหลักก็คือ SUMIFS หารด้วย COUNTIFS ในฟังก์ชันเดียวที่กระชับ

เมื่อคอลัมน์ A เป็น ภูมิภาค คอลัมน์ B เป็น เดือน และคอลัมน์ C เป็น จำนวนเงิน นี่คือตัวอย่างการหาค่าเฉลี่ยยอดขายของภูมิภาคตะวันออกในเดือนมกราคม

จำนวนเงินใน C:C คือค่าที่เราจะนำมาหาค่าเฉลี่ย คู่เงื่อนไขทั้งสองจะกรองแถวให้เหลือเฉพาะภูมิภาคตะวันออกและเดือนมกราคมก่อนคำนวณค่าเฉลี่ย

คุณสามารถกรองค่าที่จะนำมาหาค่าเฉลี่ยตามขนาดของค่าเหล่านั้นได้ สมมติว่าคุณต้องการหาค่าเฉลี่ยเฉพาะยอดขายของภูมิภาคตะวันออกที่มีมูลค่าสูง ซึ่งก็คือยอดขายที่มากกว่า 100

ในที่นี้ คอลัมน์ C ถูกใช้ทั้งเป็นช่วงที่จะหาค่าเฉลี่ยและเป็นช่วงเกณฑ์ AVERAGEIFS สามารถใช้คอลัมน์เดียวกันสำหรับทั้งสองบทบาทได้

หากต้องการสร้างรายงานที่ผู้ใช้กำหนดค่าได้ ให้ใช้อ้างอิงเซลล์แทนการพิมพ์ค่าโดยตรง ใส่ภูมิภาคไว้ใน F1 และจำนวนเงินขั้นต่ำไว้ใน F2

สำหรับการเปรียบเทียบ ให้เชื่อมตัวดำเนินการเข้ากับเซลล์: ">"&F2 หมายถึง มากกว่าค่าใด ๆ ที่อยู่ใน F2 การจับคู่ข้อความธรรมดา เช่น ชื่อภูมิภาค ไม่ต้องมีตัวดำเนินการ ใช้เพียงการอ้างอิงเซลล์

ข้อผิดพลาดสำคัญของ AVERAGEIFS คือ หาก ไม่มีแถวใดตรงกับทุกเงื่อนไข ก็จะไม่มีค่าให้หาค่าเฉลี่ย ฟังก์ชันจึงคืนค่าข้อผิดพลาด #DIV/0!

กรณีนี้แตกต่างจาก SUMIFS ซึ่งคืนค่า 0 และ COUNTIFS ซึ่งคืนค่า 0 ค่าเฉลี่ยของรายการศูนย์รายการไม่สามารถนิยามได้ Excel จึงแสดงข้อผิดพลาดแทนการเดา

ครอบสูตรด้วย IFERROR เพื่อแสดงค่าที่อ่านเข้าใจง่ายเมื่อไม่มีข้อมูลตรงเงื่อนไข แทนที่จะแสดง #DIV/0! ที่ไม่น่าดู ผู้ใช้จะเห็นเครื่องหมายขีดหรือข้อความที่กำหนดไว้

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

รายละเอียดสำคัญคือ AVERAGEIFS ไม่รวมเซลล์ว่างใน average_range เซลล์เหล่านี้จะไม่ถูกนับรวมในผลรวมหรือตัวหาร แต่เซลล์ที่มีค่า 0 เป็นตัวเลขจริงและจะถูกนำมารวม

หากใช้ศูนย์เป็นค่าที่แทนข้อมูลที่หายไป ศูนย์เหล่านี้จะทำให้ค่าเฉลี่ยลดลง คุณอาจเพิ่มเกณฑ์อย่าง C:C, "<>0" เพื่อไม่รวมศูนย์เมื่อเหมาะสม

คุณสามารถเพิ่มเงื่อนไขได้มากเท่าที่ต้องการ โดยให้คอลัมน์ D เป็น พนักงานขาย แล้วค้นหายอดขายเฉลี่ยที่อยู่ในภูมิภาคตะวันออก เกิดขึ้นในเดือนมกราคม และขายโดยมาเรีย

คู่เงื่อนไขแต่ละคู่ที่เพิ่มเข้ามาจะกรองข้อมูลให้แคบลง เนื่องจาก AVERAGEIFS ใช้ตรรกะแบบ AND จึงมีเพียงแถวที่ผ่านเงื่อนไขทั้งสามข้อเท่านั้นที่จะถูกนำมาคำนวณค่าเฉลี่ย

โปรดจำลำดับอาร์กิวเมนต์ทั้งสองแบบให้ถูกต้อง:

• AVERAGEIF: range, criteria, [average_range] — ช่วงสำหรับทดสอบจะอยู่ก่อน ส่วน average_range เป็นทางเลือกและอยู่ท้ายสุด
• AVERAGEIFS: average_range, range1, crit1, ... — average_range จะอยู่เป็นอาร์กิวเมนต์แรกเสมอ

เช่นเดียวกับฟังก์ชันอื่น ๆ การใช้เวอร์ชันที่ลงท้ายด้วย IFS เป็นค่าเริ่มต้นช่วยให้รูปแบบการใช้งานของ SUM, COUNT และ AVERAGE สอดคล้องกัน

AVERAGEIFS มีบทบาทสำคัญในสรุปข้อมูลแบบเปรียบเทียบหลายรายการ วางชื่อภูมิภาคลงในคอลัมน์ F แล้วคำนวณยอดขายเฉลี่ยของแต่ละภูมิภาคด้วยสูตรเดียวที่สามารถลากเติมได้ โดยล็อกคอลัมน์เกณฑ์ไว้

เมื่อใช้ $C:$C เป็น average_range, $A:$A เป็น criteria_range และ $F2 เป็นเซลล์ภูมิภาคแบบสัมพัทธ์ การคัดลอกสูตรลงด้านล่างจะให้ค่าเฉลี่ยของแต่ละภูมิภาคทันที

ลองทบทวนว่าอะไรทำให้ AVERAGEIFS ทำงานแตกต่างจากฟังก์ชันที่เกี่ยวข้อง

ตอนนี้คุณสามารถหาค่าเฉลี่ยของค่าต่าง ๆ ตามเงื่อนไขหลายข้อได้แล้ว:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range จะอยู่เป็นอาร์กิวเมนต์แรก
• หากไม่มีข้อมูลตรงเงื่อนไข จะได้ #DIV/0! — ป้องกันกรณีนี้ด้วย IFERROR
• เซลล์ว่างจะถูกข้าม แต่ศูนย์จะถูกรวมด้วย หากจำเป็น ให้ไม่รวมศูนย์ด้วย "<>0"
• ใช้ ">"&F1 สำหรับกำหนดขีดจำกัดตัวเลขแบบเปลี่ยนตามเซลล์

ถัดไป: การจัดการช่วงวันที่ภายในฟังก์ชันเกณฑ์เหล่านี้