Thep Excel
AVERAGEIFหาค่าเฉลี่ยแบบมีเงื่อนไข
AVERAGEIF จะหาค่าเฉลี่ยของตัวเลขในช่วงข้อมูลที่ตรงตามเงื่อนไขเพียง 1 ข้อเท่านั้น เช่น หาคะแนนเฉลี่ยของนักเรียนที่สอบผ่าน หรือหาเงินเดือนเฉลี่ยตามแผนก ประสิทธิภาพดีกว่าการใช้ AVERAGEPRODUCT หรือ Array Formula มาก
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| range | Range | ช่วงข้อมูลที่ต้องการตรวจสอบเงื่อนไข (เช่น รายชื่อสินค้า, ชื่อแผนก, เกรดนักเรียน) | |
| criteria | Text/Number/Expression | เงื่อนไขที่ต้องการ (เช่น “Apple”, “>80”, “*Pro*”) รองรับ Wildcard (* ?) และ Operator (>, =, <=, , =) | |
| [average_range]ไม่บังคับ | Range | range | ช่วงข้อมูลตัวเลขที่ต้องการนำมาหาค่าเฉลี่ย (ถ้าไม่ระบุ จะใช้ range มาคำนวณแทน) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
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 เลยนะครับ 😎
How it works
หาราคาเฉลี่ยตามหมวดหมู่
หาว่าสินค้าในหมวด "เครื่องใช้ไฟฟ้า" มีราคาเฉลี่ยเท่าไหร่
หาคะแนนเฉลี่ยเฉพาะคนสอบผ่าน
คำนวณคะแนนเฉลี่ยของนักเรียนที่ได้คะแนนมากกว่า 50 คะแนนขึ้นไป
ยอดขายเฉลี่ยรายเดือน
หายอดขายเฉลี่ยของเดือน "มกราคม" จากข้อมูลหลายปี
Examples
ตัวอย่างที่ 1: หาคะแนนเฉลี่ยเฉพาะคนที่สอบผ่านAVERAGEIF(Scores, ">60")
สมมติว่า Scores = {45, 65, 72, 55, 82, 90}
สูตรจะหาค่าเฉลี่ยของคะแนนเฉพาะที่มากกว่า 60 (ที่สอบผ่าน) = (65+72+82+90)/4 = 78.5
ที่เจ๋งคือแบบนี้ไม่ต้องเขียน IF ลำดับความสำคัญเองหรือใช้ Helper Column ครับ 💡
ตัวอย่างที่ 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 ครับ
ตัวอย่างที่ 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 ทำให้ยืดหยุ่นมาก ไม่ต้องพิมพ์ชื่อเต็ม 😎
ตัวอย่างที่ 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 ก็ได้ผลลัพธ์ต่างกันเลยครับ 💡
ตัวอย่างที่ 5: หาค่าเฉลี่ยเฉพาะค่า Non-BlankAVERAGEIF(Comments, "", SentimentScore)
สมมติว่า:
– Comments = {Great!, , Good, , Excellent}
– SentimentScore = {8, 0, 7, 0, 9}
ใช้ "<>" (ไม่เท่ากับ) ค้นหาเซลล์ที่ไม่ว่าง แล้วหาค่าเฉลี่ยคะแนนความรู้สึก = (8+7+9)/3 = 8
ประโยชน์มากเวลาต้องข้าม Empty Cell โดยอัตโนมัติครับ
ตัวอย่างที่ 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 มากมายครับ
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ AVERAGEIF จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
AVERAGE คืนค่าเฉลี่ยของกลุ่มตัวเลขที่ระบุ โดยจะนับเฉพาะเซลล์ที่มีตัวเลขและค่า 0 เท่านั้น ส่วนเซลล์ว่างหรือข้อความจะถูกข้ามไป ไม่นำมาเป็นตัวหาร ซึ่งหลายคนมักพลาดความแตกต่างระหว่าง 0 กับเซลล์ว่าง ตรงนี้สำคัญมากครับ เพราะมันทำให้ค่าเฉลี่ยที่ได้ต่างกันเลย
หาค่าเฉลี่ยคล้าย AVERAGE แต่ 'นับรวมข้อความและค่าตรรกะด้วย' โดย TRUE=1, FALSE=0 และข้อความตรงที่พิมพ์เข้ามา=0 (AVERAGE ปกติจะข้ามพวกนี้ไปเลย)
AVERAGEIFS คำนวณค่าเฉลี่ยเลขคณิตของเซลล์ที่ตรงตามเงื่อนไขหลายข้อพร้อมกัน โดยใช้ AND Logic (ต้องตรงทุกเงื่อนไข) รองรับเงื่อนไขได้สูงสุด 127 คู่ สามารถใช้กับข้อความ ตัวเลข วันที่ และ wildcard characters ทุก criteria_range ต้องมีขนาดเท่ากับ average_range เหมาะสำหรับการวิเคราะห์ข้อมูลแบบเจาะลึกตามหลายมิติ เช่น วิเคราะห์ยอดขายตามภูมิภาค สินค้า และช่วงเวลา หรือวิเคราะห์คะแนนตามห้อง เพศ และระดับคะแนน
COUNTIF ใช้นับจำนวนเซลล์ที่ตรงตามเงื่อนไขเดียว รองรับเงื่อนไขทั้งตัวเลข ข้อความ และการใช้ Wildcard (*, ?) สำหรับค้นหาแบบ pattern matching ไม่สนใจตัวพิมพ์เล็ก/ใหญ่ (case-insensitive) ข้อจำกัด: criteria ห้ามยาวเกิน 255 ตัวอักษร
COUNTIFS นับจำนวนเซลล์ที่ตรงกับหลายเงื่อนไขพร้อมกัน โดยใช้ AND logic หมายความว่าเงื่อนไขทุกข้อต้องเป็นจริงถึงจะนับ ซึ่งต่างจาก COUNTIF ที่มีได้แค่เงื่อนไขเดียว
.
ข้อดีคือรองรับได้ถึง 127 คู่ criteria_range/criteria ทำให้วิเคราะห์ข้อมูลซับซ้อนได้อย่างมีประสิทธิภาพ รองรับ wildcard characters (* แทนตัวอักษรกี่ตัวก็ได้, ? แทนตัวอักษรหนึ่งตัว) และ operators (>, =, <=, ) สำหรับเปรียบเทียบตัวเลขและวันที่
.
ที่ต้องระวังคือ criteria_range ทุกตัวต้องมีขนาดเท่ากันทุกประการ (rows × columns) มิฉะนั้นจะเกิด #VALUE! error ทันที COUNTIFS เป็นส่วนหนึ่งของ IFS family (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) ที่มี syntax คล้ายกัน เหมาะมากสำหรับสร้าง dashboard แบบ real-time และวิเคราะห์ KPI หลายมิติ
MAXIFS ใช้หาค่าสูงสุดจากช่วงข้อมูลที่ตรงตามเงื่อนไขหนึ่งหรือมากกว่า แตกต่างจาก MAX ที่หาเพียงค่าสูงสุดทั้งหมด MAXIFS มีความยืดหยุ่นในการกรองข้อมูลก่อนหาค่าสูงสุด
MINIFS ช่วยหาค่าต่ำสุดของข้อมูลที่ตรงตามเงื่อนไขที่กำหนด เหมือนการใช้ MIN แต่มีความสามารถในการกรองข้อมูลก่อน
หาค่าเปอร์เซ็นไทล์ที่ k (แบบ Inclusive: 0 ≤ k ≤ 1) ซึ่งรวมค่าขอบ 0% และ 100% ในการคำนวณ
SUMIF จะทำการบวกตัวเลขในเซลล์ที่ตรงตามเงื่อนไขที่ระบุ (1 เงื่อนไข) โดยสามารถตรวจสอบเงื่อนไขจากช่วงข้อมูลหนึ่ง (range) แล้วไปบวกตัวเลขในอีกช่วงข้อมูลหนึ่ง (sum_range) ได้ หรือจะตรวจสอบและบวกในช่วงเดียวกันก็ได้ ที่เจ๋งคือมันทำงานได้เร็วกว่า SUMPRODUCT หรือ Array Formula มาก เลยเป็นตัวเลือกแรกสำหรับการรวมค่าแบบมีเงื่อนไขครับ
SUMIFS บวกค่าจาก sum_range เฉพาะแถวที่ตรงตามเงื่อนไขทุกข้อพร้อมกัน (AND logic) รองรับได้สูงสุด 127 คู่เงื่อนไข สามารถใช้ comparison operators (>, =, <=, ), wildcard characters (*, ?), และ cell references ใน criteria ได้ เหมาะสำหรับการวิเคราะห์ข้อมูลแบบ multi-dimensional filtering เช่น รายงานยอดขายตามภูมิภาค ช่วงเวลา และสถานะพร้อมกัน โดยไม่ต้องใช้ helper columns หรือฟังก์ชันซ้อนซับซ้อน
ส่งกลับค่า P-value จากการทดสอบ F-test เพื่อเปรียบเทียบความแปรปรวนของสองกลุ่มข้อมูล (แนะนำให้ใช้ F.TEST แทน)
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่