เวลาต้องหาค่าเฉลี่ยแต่ไม่ใช่ค่าเฉลี่ยของข้อมูลทั้งหมด อยากเจาะเฉพาะบางหมวดหรือบางเงื่อนไข สูตร AVERAGE ธรรมดาจะช่วยไม่ได้แล้ว บทความนี้พาไปรู้จัก AVERAGEIF และ AVERAGEIFS สองสูตรที่ช่วยหาค่าเฉลี่ยแบบมีเงื่อนไข พร้อมตัวอย่างจำลองให้เห็นภาพจริงจากสถานการณ์ในชีวิตประจำวัน
AVERAGEIF คืออะไร ใช้งานยังไง
AVERAGEIF คือสูตรที่หาค่าเฉลี่ยของตัวเลข โดยนับเฉพาะแถวที่ตรงตามเงื่อนไขที่กำหนดไว้เท่านั้น รูปแบบสูตรคือ
=AVERAGEIF(range, criteria, [average_range])
โดย range คือช่วงข้อมูลที่จะใช้ตรวจสอบเงื่อนไข, criteria คือเงื่อนไขที่ต้องการ และ average_range คือช่วงตัวเลขที่จะเอามาหาค่าเฉลี่ย (ถ้าไม่ใส่ Excel จะใช้ range เดิมมาคำนวณเลย)
ตัวอย่างใช้งานจริง: หาค่าใช้จ่ายเฉลี่ยเฉพาะหมวด “อาหาร”
สมมติมีตารางบันทึกรายจ่ายประจำเดือนของครอบครัว อยากรู้ว่าเฉลี่ยแล้วจ่ายค่าอาหารครั้งละเท่าไหร่ โดยไม่นับหมวดอื่นปนเข้ามา
=AVERAGEIF(A2:A7,"อาหาร",B2:B7)| A | B | |
|---|---|---|
| 1 | หมวด | จำนวนเงิน |
| 2 | อาหาร | 350 |
| 3 | ค่าน้ำค่าไฟ | 1,200 |
| 4 | อาหาร | 250 |
| 5 | เดินทาง | 400 |
| 6 | อาหาร | 300 |
| 7 | เดินทาง | 150 |
| 8 | 300 |
สูตรจะไล่ดูคอลัมน์ A ทีละแถว เจอคำว่า “อาหาร” แถวไหนก็เอาตัวเลขในคอลัมน์ B ของแถวนั้นมารวมกันแล้วหารด้วยจำนวนแถวที่เจอ ได้ผลลัพธ์ 300 บาท ซึ่งเป็นค่าเฉลี่ยของ 350, 250 และ 300 เท่านั้น ไม่ปนกับหมวดค่าน้ำค่าไฟหรือค่าเดินทางเลย
AVERAGEIFS คืออะไร ต่างจาก AVERAGEIF ยังไง
ถ้าต้องการหาค่าเฉลี่ยที่ต้องเช็คหลายเงื่อนไขพร้อมกัน เช่น ทั้งหมวดหมู่และช่วงเวลา ต้องใช้ AVERAGEIFS แทน จุดที่ต้องสังเกตให้ดีคือ AVERAGEIFS สลับตำแหน่ง average_range มาไว้ข้างหน้าสุด ต่างจาก AVERAGEIF ที่ใส่ไว้ท้ายสุด รูปแบบสูตรคือ
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
ตัวอย่างใช้งานจริง: หาคะแนนเฉลี่ยวิชาคณิตศาสตร์เฉพาะห้อง ม.1
สมมติมีตารางคะแนนสอบของนักเรียนหลายวิชาหลายห้อง อยากรู้คะแนนเฉลี่ยวิชาคณิตศาสตร์ เฉพาะนักเรียนห้อง ม.1 เท่านั้น
=AVERAGEIFS(C2:C7,A2:A7,"คณิตศาสตร์",B2:B7,"ม.1")| A | B | C | |
|---|---|---|---|
| 1 | วิชา | ห้อง | คะแนน |
| 2 | คณิตศาสตร์ | ม.1 | 78 |
| 3 | คณิตศาสตร์ | ม.2 | 65 |
| 4 | วิทยาศาสตร์ | ม.1 | 82 |
| 5 | คณิตศาสตร์ | ม.1 | 90 |
| 6 | คณิตศาสตร์ | ม.1 | 84 |
| 7 | คณิตศาสตร์ | ม.2 | 70 |
| 8 | 84 |
สูตรจะเช็คสองเงื่อนไขพร้อมกันคือ คอลัมน์ A ต้องเป็น “คณิตศาสตร์” และคอลัมน์ B ต้องเป็น “ม.1” แถวไหนตรงทั้งสองเงื่อนไขถึงจะถูกเอาคะแนนในคอลัมน์ C มาเฉลี่ย ได้ผลลัพธ์ 84 คะแนน ซึ่งเป็นค่าเฉลี่ยของ 78, 90 และ 84 เท่านั้น จะเห็นว่า AVERAGEIFS ใส่เงื่อนไขเพิ่มได้เรื่อยๆ เป็นคู่ criteria_range, criteria ต่อท้ายได้ไม่จำกัด ถ้าจะเช็คแค่เงื่อนไขเดียวก็ใช้ AVERAGEIF ตามปกติได้เลย ไม่จำเป็นต้องใช้ AVERAGEIFS ให้ซับซ้อนเกินความจำเป็น สำหรับใครที่ยังไม่คุ้นกับสูตรแบบมีเงื่อนไขมาก่อน แนะนำให้อ่านพื้นฐานที่ SUMIF, COUNTIF และ IF สูตรเงื่อนไขที่ต้องรู้ ก่อน จะช่วยให้เข้าใจ AVERAGEIF/AVERAGEIFS ได้เร็วขึ้นเพราะใช้หลักการเดียวกัน
ข้อควรระวังที่เจอบ่อย
มีจุดที่มักพลาดกันบ่อยเวลาใช้ AVERAGEIF และ AVERAGEIFS ดังนี้
| ปัญหา | สาเหตุ/วิธีแก้ |
| ขึ้น #DIV/0! | ไม่มีแถวไหนตรงเงื่อนไขเลย ทำให้หารด้วยศูนย์ ควรครอบด้วย IFERROR เช่น =IFERROR(AVERAGEIF(A2:A7,"เครื่องดื่ม",B2:B7),"ไม่มีข้อมูล") อ่านวิธีใช้เพิ่มเติมได้ที่ สูตร IFERROR ดักจับ Error ใน Excel |
| ใช้ wildcard ในเงื่อนไขข้อความ | ใส่ * แทนตัวอักษรกี่ตัวก็ได้ เช่น "อาหาร*" จะจับคำที่ขึ้นต้นด้วย “อาหาร” ทั้งหมด และใช้ ? แทนตัวอักษร 1 ตัว เช่น "ห้อง?" |
| average_range กับ range ขนาดไม่เท่ากัน | ต้องเลือกช่วงให้มีจำนวนแถวเท่ากันเสมอ ไม่งั้นผลลัพธ์จะคลาดเคลื่อนหรือ Error |
สรุป
AVERAGEIF ใช้เมื่อต้องการหาค่าเฉลี่ยแบบมีเงื่อนไขเดียว ส่วน AVERAGEIFS ใช้เมื่อต้องเช็คหลายเงื่อนไขพร้อมกัน โดยจำไว้ว่า AVERAGEIFS จะสลับเอา average_range มาไว้ตำแหน่งแรกสุดต่างจาก AVERAGEIF เข้าใจหลักการนี้แล้วจะนำไปปรับใช้กับงบรายจ่าย คะแนนสอบ หรือข้อมูลอื่นๆ ในชีวิตประจำวันได้สะดวกขึ้นมาก
