Thep Excel
AVERAGEIF – หาค่าเฉลี่ยแบบมีเงื่อนไข
AVERAGEIF จะหาค่าเฉลี่ยของตัวเลขในช่วงข้อมูลที่ตรงตามเงื่อนไขเพียง 1 ข้อเท่านั้น เช่น หาคะแนนเฉลี่ยของนักเรียนที่สอบผ่าน หรือหาเงินเดือนเฉลี่ยตามแผนก ประสิทธิภาพดีกว่าการใช้ AVERAGEPRODUCT หรือ Array Formula มาก
=AVERAGEIF(range, criteria, [average_range])
Function Metrics
Popularity
8/10
Difficulty
4/10
Usefulness
9/10
Syntax & Arguments
=AVERAGEIF(range, criteria, [average_range])
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| range | Range | Yes | ช่วงข้อมูลที่ต้องการตรวจสอบเงื่อนไข (เช่น รายชื่อสินค้า, ชื่อแผนก, เกรดนักเรียน) | |
| criteria | Text/Number/Expression | Yes | เงื่อนไขที่ต้องการ (เช่น “Apple”, “>80”, “*Pro*”) รองรับ Wildcard (* ?) และ Operator (>, =, <=, , =) | |
| average_range | Range | Optional | range | ช่วงข้อมูลตัวเลขที่ต้องการนำมาหาค่าเฉลี่ย (ถ้าไม่ระบุ จะใช้ range มาคำนวณแทน) |
How it works
หาราคาเฉลี่ยตามหมวดหมู่
หาว่าสินค้าในหมวด "เครื่องใช้ไฟฟ้า" มีราคาเฉลี่ยเท่าไหร่
หาคะแนนเฉลี่ยเฉพาะคนสอบผ่าน
คำนวณคะแนนเฉลี่ยของนักเรียนที่ได้คะแนนมากกว่า 50 คะแนนขึ้นไป
ยอดขายเฉลี่ยรายเดือน
หายอดขายเฉลี่ยของเดือน "มกราคม" จากข้อมูลหลายปี
Examples
ตัวอย่างที่ 1: หาคะแนนเฉลี่ยเฉพาะคนที่สอบผ่าน
AVERAGEIF(Scores, ">60")
.
สูตรจะหาค่าเฉลี่ยของคะแนนเฉพาะที่มากกว่า 60 (ที่สอบผ่าน) = (65+72+82+90)/4 = 78.5
.
ที่เจ๋งคือแบบนี้ไม่ต้องเขียน IF ลำดับความสำคัญเองหรือใช้ Helper Column ครับ 💡
=AVERAGEIF(Scores, ">60")
78.5
ตัวอย่างที่ 2: หาเงินเดือนเฉลี่ยตามแผนก
AVERAGEIF(Department, "Sales", Salary)
– Department = {Sales, IT, Sales, HR, Sales, IT}
– Salary = {40000, 50000, 48000, 38000, 45000, 52000}
.
สูตรจะตรวจสอบ Department หาแถวที่เป็น "Sales" (แถว 1, 3, 5) แล้วหาค่าเฉลี่ยเงินเดือน = (40000+48000+45000)/3 = 44333
.
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากตอนทำ HR Analytics หรือ Department Performance Report ครับ
=AVERAGEIF(Department, "Sales", Salary)
45000
ตัวอย่างที่ 3: ใช้ Wildcard หาค่าเฉลี่ยสินค้าตระกูล
AVERAGEIF(ProductNames, "*Pro*", Sales)
– ProductNames = {iPad Pro, MacBook, iPad Air, MacBook Pro}
– Sales = {10000, 8000, 6000, 9000}
.
ใช้ "*Pro*" ค้นหาสินค้าที่มีคำว่า Pro อยู่ตรงไหนก็ได้ (iPad Pro, MacBook Pro) แล้วหาค่าเฉลี่ยยอดขาย = (10000+9000)/2 = 9500
.
ที่เจ๋งคือ Wildcard ทำให้ยืดหยุ่นมาก ไม่ต้องพิมพ์ชื่อเต็ม 😎
=AVERAGEIF(ProductNames, "*Pro*", Sales)
8500
ตัวอย่างที่ 4: ใช้ cell reference เปลี่ยนเกณฑ์ได้เอง
AVERAGEIF(Sales, ">"&E2, Amount)
– E2 = 5000 (เกณฑ์ที่ User ตั้ง)
– Sales = {3000, 5500, 6000, 8000}
– Amount = {1000, 2000, 3000, 4000}
.
สูตรจะหาค่าเฉลี่ย Amount ของแถวที่ Sales > 5000 = (2000+3000+4000)/3 = 3000
.
ที่เจ๋งคือ ทำให้ฟอร์มูลยืดหยุ่น User แค่เปลี่ยนค่าใน E2 ก็ได้ผลลัพธ์ต่างกันเลยครับ 💡
=AVERAGEIF(Sales, ">"&E2, Amount)
7500
ตัวอย่างที่ 5: หาค่าเฉลี่ยเฉพาะค่า Non-Blank
AVERAGEIF(Comments, "", SentimentScore)
– Comments = {Great!, , Good, , Excellent}
– SentimentScore = {8, 0, 7, 0, 9}
.
ใช้ "<>" (ไม่เท่ากับ) ค้นหาเซลล์ที่ไม่ว่าง แล้วหาค่าเฉลี่ยคะแนนความรู้สึก = (8+7+9)/3 = 8
.
ประโยชน์มากเวลาต้องข้าม Empty Cell โดยอัตโนมัติครับ
=AVERAGEIF(Comments, "<>", SentimentScore)
7.5
ตัวอย่างที่ 6: หาค่าเฉลี่ยหลายช่วงราคา (nested)
(AVERAGEIF(Price, "=100", Quantity))/2
– Price = {80, 150, 90, 200}
– Quantity = {2, 5, 4, 7}
.
สูตรจะหาค่าเฉลี่ย Quantity ของราคา <100 = (2+4)/2 = 3 และ >=100 = (5+7)/2 = 6
.
แล้วหาค่าเฉลี่ยรวมของทั้งสอง = (3+6)/2 = 4.5
.
ส่วนตัวผมใช้วิธีนี้ตอนต้องการ Nested Calculation มากมายครับ
=(AVERAGEIF(Price, "<100", Quantity) + AVERAGEIF(Price, ">=100", Quantity))/2
4.5
FAQs
AVERAGEIF ต่างจาก AVERAGEIFS อย่างไร?
AVERAGEIF รองรับได้ 1 เงื่อนไข ส่วน AVERAGEIFS รองรับหลายเงื่อนไข (AND logic)
.
ตัวอย่าง: AVERAGEIF หาค่าเฉลี่ยยอดขายเฉพาะสินค้า A ได้ แต่ AVERAGEIFS หาค่าเฉลี่ยยอดขายสินค้า A ในภาคเหนือได้
.
ส่วนตัวผมแนะนำให้ใช้ AVERAGEIFS เสมอนะครับ เพราะยืดหยุ่นกว่า และถึงแม้จะมีเงื่อนไขเดียวก็ใช้ AVERAGEIFS ได้เลย จะได้ไม่ต้องเปลี่ยนฟอร์มูลทีหลัง 💡
ถ้าไม่มีข้อมูลตรงเงื่อนไขจะเป็นอย่างไร?
จะแสดง Error #DIV/0! (Division by Zero) เพราะหารด้วย 0 แนะนำให้ครอบด้วย IFERROR เพื่อจัดการกรณีนี้
.
ตัวอย่าง: =IFERROR(AVERAGEIF(A1:A10, “xxx”, B1:B10), “ไม่พบข้อมูล”)
.
ที่เจ๋งคือวิธีนี้ให้ผู้ใช้เห็นข้อความที่เข้าใจง่าย แทนที่จะ Error ที่งง 😎
AVERAGEIF เป็น case-sensitive หรือไม่?
ไม่ครับ AVERAGEIF ไม่แยกตัวพิมพ์ใหญ่/เล็ก เช่น “Apple”, “APPLE”, “apple” ถือเป็นค่าเดียวกัน
.
ที่เจ๋งคือทำให้สะดวกในการใช้งาน ไม่ต้องกังวลเรื่องตัวพิมพ์เวลาค้นหาครับ
ใช้ Wildcard ได้อย่างไร?
ใช้ * แทนตัวอักษรกี่ตัวก็ได้ (“*Pro*” = มีคำว่า Pro อยู่ที่ไหนก็ได้) และ ? แทนตัวอักษรเดียว (“A?” = A ตามด้วยตัวเดียว)
.
ที่ต้องระวังคือถ้าต้องการค้นหา * หรือ ? จริงๆ ให้ใช้ ~ นำหน้า เช่น “~*” จะค้นหา * ตัวอักษรจริงๆ ครับ
ถ้า average_range ขนาดไม่เท่ากับ range จะเป็นอย่างไร?
Excel จะปรับขนาด average_range ให้เท่ากับ range โดยเริ่มจากจุดเริ่มต้นของ average_range
.
แต่ที่ต้องระวังคือวิธีนี้อาจทำให้ผลลัพธ์ผิดพลาดได้ ควรเลือกช่วงให้ขนาดเท่ากันเสมอครับ 😅
AVERAGEIF ใช้กับไฟล์ที่ปิดอยู่ (Closed Workbook) ได้ไหม?
ได้ครับ AVERAGEIF ทำงานได้แม้ไฟล์ต้นทางจะปิดอยู่ (Excel รุ่นใหม่รองรับแล้ว)
.
ส่วนตัวผมใช้เทคนิคนี้บ่อยมากตอนต้องรวมข้อมูลจากหลายไฟล์ โดยไม่ต้องเปิดทุกไฟล์พร้อมกันครับ 😎
Resources & Related
Resources
Related functions
Additional Notes
AVERAGEIF เป็นฟังก์ชันที่ใช้หาค่าเฉลี่ยของตัวเลขที่ตรงตามเงื่อนไขเดียวที่กำหนด (available ใน Excel 2007+) รองรับ Wildcard (* ?) สำหรับค้นหาข้อความ
ที่เจ๋งคือ AVERAGEIF สามารถตรวจสอบเงื่อนไขจากช่วงข้อมูลหนึ่ง (range) แล้วไปหาค่าเฉลี่ยตัวเลขในอีกช่วงข้อมูลหนึ่ง (average_range) ได้ เหมาะมากเวลาต้องการ average แบบมีเงื่อนไข
ส่วนตัวผมใช้บ่อยมากครับ โดยเฉพาะตอนวิเคราะห์ข้อมูล Sales, Student Grades, Product Performance เป็นต้น ที่ต้องระวังคือถ้าต้องการหลายเงื่อนไขให้ใช้ AVERAGEIFS แทน และลำดับ argument ของ AVERAGEIF คือ (range, criteria, average_range) ซึ่งเหมือน SUMIF เลยนะครับ 😎