หัวหน้าถามว่า "พนักงานขายได้เฉลี่ยคนละเท่าไหร่" ตอบไปตามสูตร AVERAGE แล้วตัวเลขต่ำผิดปกติ — เพราะมีคนที่เพิ่งเริ่มงานยอดเป็น 0 หรือสูงผิดปกติเพราะมีดีลใหญ่ดีลเดียว
ค่าเฉลี่ยใช้ง่าย แต่ตีความผิดง่ายที่สุด บทความนี้รวม AVERAGE, AVERAGEIF, AVERAGEIFS และ MEDIAN พร้อมตัวอย่างที่ตรวจใน Google Sheets จริง ว่าช่องแบบไหนถูกนับ ช่องแบบไหนถูกข้าม
ตระกูลค่าเฉลี่ย ใช้ตอนไหน
| อยากรู้ | สูตร | หมายเหตุ |
|---|---|---|
| ค่าเฉลี่ยธรรมดา | =AVERAGE(B2:B100) | ข้ามช่องว่างและข้อความ แต่นับ 0 |
| เฉลี่ยเฉพาะที่ตรงเงื่อนไข | =AVERAGEIF(A2:A100,"A",C2:C100) | ช่วงตรวจ, เงื่อนไข, ช่วงที่เฉลี่ย |
| เฉลี่ยหลายเงื่อนไข | =AVERAGEIFS(C2:C100,A2:A100,"A",B2:B100,"ต.ค.") | ช่วงที่เฉลี่ยขึ้นก่อน |
| เฉลี่ยไม่นับ 0 | =AVERAGEIF(B2:B100,">0") | ตัดคนที่ยังไม่มียอด |
| ค่ากลาง | =MEDIAN(C2:C100) | ไม่โดนค่าสุดโต่งดึง |
ตัวอย่าง 1: ช่องว่าง 0 และข้อความ ถูกนับไม่เหมือนกัน
ภาพจากไฟล์ Google Sheets จริง · เปิดไฟล์ตัวอย่างนี้ (ดูอย่างเดียว — ไฟล์ → ทำสำเนา เพื่อลองแก้)
ดูเป็นตาราง (ก๊อปตัวเลขได้)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | พนักงาน | ยอดขาย | วิธีหาค่าเฉลี่ย | ผล | |
| 2 | สมชาย | 52,000 | AVERAGE | 37,666.67 | |
| 3 | สุดา | 61,000 | AVERAGE ไม่นับ 0 | 56,500.00 | |
| 4 | มานพ | นับช่องที่มีตัวเลข | 3 | ||
| 5 | วิภา | 0 | |||
| 6 | ก้อง | ลา |
สูตรในช่องสีฟ้า (ช่วงเช่น C2:C4 = พิมพ์ที่ช่องแรกแล้วลากลง):
- E2
=AVERAGE(B2:B6) - E3
=AVERAGEIF(B2:B6,">0") - E4
=COUNT(B2:B6)
AVERAGE ข้ามช่องว่าง (มานพ) และข้อความ (ก้อง) แต่นับ 0 (วิภา) เป็นตัวเลข จึงหาร 3 คน ได้ 37,666.67 — ถ้าวิภายังไม่มียอดเพราะเพิ่งเริ่มงาน ค่าเฉลี่ยที่ใช้ได้จริงคือ 56,500
คำถามที่ต้องตอบก่อนเลือกสูตร: "0 ในตารางนี้คือยอดจริง หรือแค่ยังไม่มีข้อมูล?" ถ้าเป็นยอดจริง (ขายไม่ได้เลยทั้งเดือน) AVERAGE ธรรมดาถูกแล้ว ถ้าแค่ยังไม่มีข้อมูล ให้ปล่อยช่องว่าง หรือใช้ AVERAGEIF ตัด 0 ออก
ตัวอย่าง 2: เฉลี่ยตามกลุ่ม และเมื่อค่าเฉลี่ยหลอกตา
ภาพจากไฟล์ Google Sheets จริง · เปิดไฟล์ตัวอย่างนี้ (ดูอย่างเดียว — ไฟล์ → ทำสำเนา เพื่อลองแก้)
ดูเป็นตาราง (ก๊อปตัวเลขได้)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ทีม | เดือน | ยอด | อยากรู้ | ผล | |
| 2 | A | ต.ค. | 50,000 | เฉลี่ยทั้งหมด | 92,600 | |
| 3 | A | ต.ค. | 54,000 | ค่ากลาง (MEDIAN) | 54,000 | |
| 4 | B | ต.ค. | 61,000 | เฉลี่ยทีม A | 50,666.67 | |
| 5 | A | ก.ย. | 48,000 | ทีม A เดือน ต.ค. | 52,000 | |
| 6 | B | ก.ย. | 250,000 |
สูตรในช่องสีฟ้า (ช่วงเช่น C2:C4 = พิมพ์ที่ช่องแรกแล้วลากลง):
- F2
=AVERAGE(C2:C6) - F3
=MEDIAN(C2:C6) - F4
=AVERAGEIF(A2:A6,"A",C2:C6) - F5
=AVERAGEIFS(C2:C6,A2:A6,"A",B2:B6,"ต.ค.")
ดีลใหญ่ 250,000 ของทีม B ดึงค่าเฉลี่ยขึ้นไป 92,600 ทั้งที่ 4 ใน 5 รายการไม่ถึง 62,000 — MEDIAN (ค่าที่อยู่ตรงกลางเมื่อเรียงลำดับ) บอกยอด "ปกติ" ได้ตรงกว่า
ใช้ทั้งสองตัวคู่กันในรายงาน: AVERAGE บอกภาพรวมของเงิน MEDIAN บอกว่ารายการ "ปกติ" อยู่ที่เท่าไหร่ ถ้าสองตัวต่างกันมาก แปลว่ามีค่าผิดปกติที่ควรดูแยก
ค่าเฉลี่ยที่ใช้ในงานจริงบ่อย ๆ
- 1
ยอดเฉลี่ยต่อวัน ของเดือนที่ยังไม่จบ
=SUM(ยอดเดือนนี้)/DAY(TODAY())หารด้วยจำนวนวันที่ผ่านมาแล้ว ไม่ใช่ 30 - 2
ราคาเฉลี่ยต่อชิ้น (ถ่วงน้ำหนัก)
=SUM(ยอดเงิน)/SUM(จำนวนชิ้น)— อย่าเฉลี่ยราคาของแต่ละบิล เพราะบิลเล็กกับบิลใหญ่จะถูกนับเท่ากัน - 3
ค่าเฉลี่ยเคลื่อนที่ 7 วัน
ในแถวที่ 8 ใส่=AVERAGE(B2:B8)แล้วลากลง ช่วงจะเลื่อนตามทีละวัน เห็นแนวโน้มโดยไม่โดนวันเดียวที่ขึ้นลงแรงหลอก
ข้อผิดพลาดที่เจอบ่อย
ได้ #DIV/0!
ค่าเฉลี่ยต่ำเกินจริง
ตัวเลขบางช่องไม่ถูกนับ
เฉลี่ยของค่าเฉลี่ย
อยากได้แดชบอร์ดยอดขายที่คิดค่าเฉลี่ยรายทีม รายเดือนให้เอง?
คำถามที่ถามบ่อย
AVERAGE นับช่องว่างไหม?
ไม่นับ AVERAGE ข้ามช่องว่างและข้อความ แต่นับช่องที่เป็น 0 จึงทำให้ค่าเฉลี่ยต่ำลงถ้ามี 0 ที่จริง ๆ คือยังไม่มีข้อมูล
หาค่าเฉลี่ยไม่นับ 0 ยังไง?
ใช้ =AVERAGEIF(B2:B100,">0") ระบบจะเฉลี่ยเฉพาะช่องที่มากกว่า 0
AVERAGEIF กับ AVERAGEIFS ต่างกันยังไง?
AVERAGEIF ใช้เงื่อนไขเดียว (ช่วงตรวจ, เงื่อนไข, ช่วงที่เฉลี่ย) AVERAGEIFS ใช้หลายเงื่อนไข และใส่ช่วงที่จะเฉลี่ยเป็นตัวแรก
ค่าเฉลี่ยกับค่ากลาง (MEDIAN) ต่างกันยังไง?
ค่าเฉลี่ยคือผลรวมหารจำนวน โดนค่าที่สูงหรือต่ำผิดปกติดึงได้ง่าย MEDIAN คือค่าที่อยู่ตรงกลางเมื่อเรียงลำดับ จึงบอกค่า "ปกติ" ได้ดีกว่าเมื่อมีค่าสุดโต่ง
AVERAGE ใน Excel เหมือน Google Sheets ไหม?
เหมือนกันทุกอย่าง รวมถึง AVERAGEIF AVERAGEIFS และ MEDIAN