หน้าแรก › บทความ › Microsoft
Microsoft

ค่าเฉลี่ยถ่วงน้ำหนักใน Excel ด้วย SUMPRODUCT (GPA, ราคาทุนเฉลี่ย)

เผยแพร่ 29 ส.ค. 69

ค่าเฉลี่ยธรรมดา (AVERAGE) ให้ทุกค่ามีน้ำหนักเท่ากัน แต่บางงานแต่ละค่ามีความสำคัญไม่เท่ากัน เช่น เกรดเฉลี่ยที่แต่ละวิชามีหน่วยกิตต่างกัน หรือราคาเฉลี่ยที่ซื้อมาคนละจำนวน กรณีนี้ต้องใช้ ค่าเฉลี่ยถ่วงน้ำหนัก (weighted average) ด้วยสูตร SUMPRODUCT / SUM

สูตรหลัก

=SUMPRODUCT(ค่า, น้ำหนัก) / SUM(น้ำหนัก)

SUMPRODUCT คูณค่ากับน้ำหนักทีละคู่แล้วรวมกัน จากนั้นหารด้วยผลรวมน้ำหนักทั้งหมด

ตัวอย่างที่ 1: เกรดเฉลี่ย (GPA)

B6fx=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)
A B C
1 วิชา เกรด หน่วยกิต
2 คณิต 4.0 3
3 อังกฤษ 3.0 3
4 ประวัติศาสตร์ 3.5 2
5 พลศึกษา 2.0 1
6 GPA 3.33

SUMPRODUCT คิด (4.0×3)+(3.0×3)+(3.5×2)+(2.0×1) = 12+9+7+2 = 30 แล้วหารด้วยหน่วยกิตรวม 9 ได้ 3.33 — ถ้าใช้ค่าเฉลี่ยธรรมดาของ 4 เกรดจะได้แค่ 3.13 เพราะไม่ได้ให้น้ำหนักวิชาที่หน่วยกิตมากกว่า

ตัวอย่างที่ 2: ราคาทุนเฉลี่ยต่อชิ้น

B5fx=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
A B C
1 ครั้งที่ซื้อ ราคา/ชิ้น จำนวน
2 1 100 10
3 2 120 30
4 3 90 10
5 ราคาทุนเฉลี่ย 110

(100×10 + 120×30 + 90×10) / 50 = (1,000 + 3,600 + 900) / 50 = 5,500 / 50 = 110 บาท/ชิ้น ค่าเฉลี่ยธรรมดาของ 100, 120, 90 จะได้ 103 ซึ่งไม่สะท้อนว่าซื้อล็อตราคา 120 มาเยอะสุด

ตัวอย่างที่ 3: ถ่วงน้ำหนักแบบมีเงื่อนไข

F2fx=SUMPRODUCT((A2:A9=E2)*B2:B9*C2:C9) / SUMPRODUCT((A2:A9=E2)*C2:C9)
A B C E F
1 สาขา คะแนน จำนวนโหวต สาขา เฉลี่ยถ่วงน้ำหนัก
2 A 4 50 A 4.2
3 B 5 20
4 A 4.5 30

กรองเฉพาะสาขา A ด้วย (A2:A9=E2) แล้วถ่วงน้ำหนักด้วยจำนวนโหวต ได้คะแนนเฉลี่ยที่สะท้อนน้ำหนักของแต่ละรายการ

ข้อควรรู้

หมายเหตุ: บทความนี้เขียนและเรียบเรียงโดย AI

← กลับหน้าบทความ
Line