หาค่าเฉลี่ยตามเกณฑ์ด้วย AVERAGEIF

คุณได้หาผลรวมด้วย SUMIF และนับจำนวนด้วย COUNTIF แล้ว สมาชิกที่สามของกลุ่มนี้คือ AVERAGEIF ซึ่งใช้หาค่าเฉลี่ยเฉพาะค่าที่ตรงตามเงื่อนไข

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

AVERAGEIF ใช้โครงสร้างสามส่วนเช่นเดียวกับ SUMIF:

• ช่วง เซลล์ที่จะตรวจสอบ
• เกณฑ์ เงื่อนไข
• ช่วงค่าเฉลี่ย เซลล์ที่จะนำมาหาค่าเฉลี่ย

สูตรจะตรวจดูช่วง ค้นหาค่าที่ตรงกัน แล้วหาค่าเฉลี่ยจากเซลล์ที่สอดคล้องกันในช่วงค่าเฉลี่ย

เมื่อมีภูมิภาคอยู่ใน A2:A10 และจำนวนเงินอยู่ใน B2:B10 การหาค่าเฉลี่ยยอดขายของภูมิภาคตะวันออกจะตรวจสอบคอลัมน์ A ว่ามีตะวันออกหรือไม่ แล้วหาค่าเฉลี่ยจากค่าที่ตรงกันในคอลัมน์ B

เบื้องหลังนั้นสูตรกำลังคำนวณ SUMIF หารด้วย COUNTIF แต่ AVERAGEIF รวมการทำงานดังกล่าวไว้ในสูตรเดียวที่อ่านได้ง่าย

หากคอลัมน์ที่ตรวจสอบเป็นคอลัมน์เดียวกับที่ต้องการหาค่าเฉลี่ย ก็ไม่จำเป็นต้องระบุอาร์กิวเมนต์ตัวที่สาม หากต้องการหาค่าเฉลี่ยเฉพาะคะแนนใน B2:B10 ที่มากกว่า 70:

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

เช่นเดียวกับฟังก์ชัน IF อื่น ๆ ให้เก็บเงื่อนไขไว้ในเซลล์เพื่อความยืดหยุ่น หาก D1 มีชื่อภูมิภาคอยู่ ให้อ้างอิงเกณฑ์ไปที่ D1

ตอนนี้เซลล์เดียวจะเป็นตัวกำหนดค่าเฉลี่ย เมื่อเปลี่ยน D1 จากตะวันออกเป็นตะวันตก ระบบจะคำนวณใหม่ทันที เหมาะอย่างยิ่งสำหรับตารางสรุปหรือตัวเลือกบนแดชบอร์ด

AVERAGEIF รองรับตัวดำเนินการภายในเครื่องหมายอัญประกาศเช่นเดียวกับฟังก์ชันในกลุ่มเดียวกัน หากต้องการหาค่าเฉลี่ยเฉพาะยอดขายที่อย่างน้อย 100:

• ">=100" ตั้งแต่ 100 ขึ้นไป
• "<50" น้อยกว่า 50
• "<>0" ไม่รวมศูนย์

ตัวเลือกสุดท้ายนี้ใช้บ่อย เพราะช่วยหาค่าเฉลี่ยโดยไม่นับรายการที่เป็นศูนย์ ซึ่งไม่เช่นนั้นจะดึงค่าเฉลี่ยให้ต่ำลง

หากต้องการใช้ค่าเกณฑ์จากเซลล์ ให้เชื่อมตัวดำเนินการกับเซลล์ด้วยเครื่องหมายแอมเพอร์แซนด์ หาก D1 มีค่าตัดอยู่ ให้หาค่าเฉลี่ยของทุกค่าที่มากกว่าค่านั้น

& จะสร้างข้อความเงื่อนไขจาก ">" และค่าใน D1 นี่คือเทคนิคการเชื่อมแบบเดียวกับที่ใช้ใน SUMIF และ COUNTIF

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

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

นำสูตรไปครอบด้วย IFERROR เพื่อแสดงข้อความที่เป็นมิตรแทน #DIV/0! เมื่อไม่มีค่าที่ตรงกัน

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

AVERAGEIF จะหาค่าเฉลี่ยเฉพาะเซลล์ที่มีตัวเลขในกลุ่มค่าที่ตรงกัน เซลล์ที่ว่างจริง ๆ ในช่วงค่าเฉลี่ยจะถูกข้าม จึงไม่ถูกนับเป็นศูนย์

เรื่องนี้สำคัญ เพราะค่าที่หายไปจะไม่ดึงค่าเฉลี่ยเข้าหาศูนย์เหมือนค่า 0 ที่ระบุไว้จริง หากต้องการไม่รวมศูนย์ด้วย ให้เพิ่มเกณฑ์ "<>0" ในคอลัมน์ค่า

สร้างสรุปที่เป็นระเบียบโดยใส่แต่ละหมวดหมู่ไว้ในเซลล์ D2, D3 และ D4 จากนั้นเขียน AVERAGEIF หนึ่งสูตรที่อ้างอิงเซลล์ป้ายกำกับ แล้วลากสูตรลงมา

แต่ละแถวจะแสดงค่าเฉลี่ยของหมวดหมู่ของตนเอง พร้อมกับยอดรวมจาก SUMIF และจำนวนจาก COUNTIF ที่เรียนไปก่อนหน้านี้ ทั้งหมดนี้รวมกันเป็นรายงานขนาดกะทัดรัดที่ขับเคลื่อนด้วยสูตรโดยสมบูรณ์

ทดสอบความเข้าใจเกี่ยวกับการทำงานของ AVERAGEIF

ขณะนี้คุณสามารถหาค่าเฉลี่ยแบบมีเงื่อนไขด้วย AVERAGEIF ได้แล้ว ประเด็นสำคัญมีดังนี้:

• ลำดับคือ ช่วง, เกณฑ์, ช่วงค่าเฉลี่ย โดยไม่ต้องระบุอาร์กิวเมนต์สุดท้ายหากต้องการหาค่าเฉลี่ยจากช่วงที่ตรวจสอบเอง
• รองรับข้อความ ตัวเลข และตัวดำเนินการ เช่น ">=100"
• หากไม่มีค่าที่ตรงกัน จะเกิดข้อผิดพลาด #DIV/0! ดังนั้นควรป้องกันด้วย IFERROR
• เซลล์ว่างจะถูกข้าม ไม่ถือเป็นศูนย์

ถัดไปจะเรียนรู้การจับคู่ข้อความบางส่วนโดยใช้อักขระตัวแทน