หาค่าเฉลี่ยถ่วงน้ำหนักใน Excel ด้วย SUMPRODUCT ไม่ให้จำนวนซื้อหายไป

ซื้อสินค้าชนิดเดียวหลายครั้ง แต่ราคาและจำนวนแต่ละครั้งไม่เท่ากัน ถ้าใช้ AVERAGE เฉลี่ยเฉพาะราคาจะถือว่าทุกครั้งมีน้ำหนักเท่ากัน ทั้งที่บางครั้งซื้อเพียงสองชิ้นและบางครั้งซื้อแปดชิ้น ค่าเฉลี่ยถ่วงน้ำหนัก จะนำจำนวนที่ซื้อเข้ามาคิดด้วย จึงเหมาะกับการสรุปราคาต่อหน่วยของข้อมูลลักษณะนี้

Excel สามารถคำนวณด้วย SUMPRODUCT เพื่อรวมผลคูณระหว่างราคาและจำนวน แล้วหารด้วยจำนวนรวม วิธีนี้ใช้กับข้อมูลยอดขาย คะแนนที่มีน้ำหนัก หรือค่าเฉลี่ยอื่นได้ แต่ต้องเลือกน้ำหนักที่สอดคล้องกับสิ่งที่ต้องการวัด

ตัวอย่างที่ AVERAGE ให้ความหมายต่างกัน

ครั้งที่ซื้อ	ราคาต่อชิ้น B	จำนวน C	มูลค่า
ครั้งที่ 1	100	2	200
ครั้งที่ 2	80	8	640

ถ้าใช้ =AVERAGE(B2:B3) จะได้90 เพราะนำ100กับ80มาเฉลี่ยโดยไม่สนใจจำนวน แต่เงินรวมที่จ่ายคือ840และได้รับสินค้า10ชิ้น ดังนั้นค่าเฉลี่ยต่อชิ้นคือ84 การซื้อราคาต่ำในจำนวนมากจึงทำให้ค่าเฉลี่ยอยู่ใกล้80มากกว่า100

เขียนสูตร SUMPRODUCT

จัดราคาต่อหน่วยไว้ใน B2:B3 และจำนวนไว้ใน C2:C3 ให้ราคากับจำนวนของรายการเดียวกันอยู่แถวเดียวกัน
เลือกเซลล์ผลลัพธ์ เช่น E2
พิมพ์สูตรแล้วกด Enter
ตั้งรูปแบบ Number หรือ Currency ตามรายงาน

=SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3)

SUMPRODUCT คูณ B2กับC2 และ B3กับC3 แล้วรวมเป็น840 ส่วน SUM รวมจำนวนเป็น10 ผลจึงได้84 สูตรนี้ไม่ต้องสร้างคอลัมน์มูลค่าก็ได้ แต่ในงานที่ต้องให้คนอื่นตรวจสอบ คอลัมน์มูลค่าช่วยเห็นที่มาของผลลัพธ์ง่ายขึ้น

ตรวจด้วยวิธีคำนวณทีละแถว

ใส่ =B2*C2 ใน D2 แล้วลากลง D3 จากนั้นใช้ =SUM(D2:D3)/SUM(C2:C3) ผลควรตรงกับสูตร SUMPRODUCT วิธีนี้เหมาะสำหรับตรวจไฟล์ครั้งแรกหรือหาว่าแถวใดมีข้อมูลผิด ก่อนเปลี่ยนกลับเป็นสูตรสั้นเพื่อใช้ในรายงาน

อย่านำค่าเฉลี่ยรายกลุ่มมาเฉลี่ยอีกครั้งโดยไม่ใช้น้ำหนัก เช่นค่าเฉลี่ยราคาของแต่ละสาขา สาขาที่ขาย10ชิ้นกับ1000ชิ้นไม่ควรมีน้ำหนักเท่ากัน ต้องเก็บจำนวนของแต่ละกลุ่มมาคิดด้วย หากเหลือเฉพาะค่าเฉลี่ยแต่ไม่มีจำนวน อาจไม่สามารถหาค่าเฉลี่ยรวมที่ถูกต้องได้

ใช้กับคะแนนที่แบ่งน้ำหนัก

สมมติคะแนนงานใน B2:B4 เป็น80,70,90 และน้ำหนักใน C2:C4 เป็น20%,30%,50% สูตรเดียวกันจะได้82 คะแนน โดยน้ำหนักรวมเป็น100% หากเก็บน้ำหนักเป็น20,30,50ก็ยังใช้สูตรหารด้วยผลรวมน้ำหนักได้ แต่ต้องใช้หน่วยเดียวกันทั้งช่วง

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

การผสม20กับ30%ในคอลัมน์เดียวทำให้สัดส่วนผิดมาก ควรตรวจค่าจริงในแถบสูตร ไม่ดูเฉพาะรูปแบบที่แสดง ถ้าสูตรใช้กับคะแนนที่มีเต็มต่างกัน ต้องแปลงคะแนนให้อยู่ฐานเดียวกันก่อน ไม่ใส่คะแนนเต็ม20และเต็ม100ลงสูตรเดียวโดยไม่ปรับฐาน

ข้อผิดพลาดที่พบบ่อย

#VALUE!: ตรวจว่าช่วงราคาและน้ำหนักมีจำนวนแถวเท่ากัน ไม่เลือก B2:B10 คู่กับ C2:C9
#DIV/0!: น้ำหนักรวมเป็นศูนย์หรือไม่มีข้อมูล อย่าแปลผลเป็นราคาเฉลี่ยศูนย์
ผลต่ำผิดปกติ: ตรวจรายการที่ราคาว่างแต่มีจำนวน หรือราคาที่เก็บเป็นข้อความ เพราะอาจไม่ถูกนับแบบที่คาด
รายการคืนสินค้า: จำนวนติดลบต้องมีความหมายทางธุรกิจชัดเจน ไม่ผสมเข้ากับการซื้อปกติโดยไม่ตรวจวิธีสรุป

ถ้ารายงานยังไม่กรอกข้อมูล สามารถใช้ =IF(SUM(C2:C3)=0,"ยังไม่มีจำนวน",SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3)) เพื่อแสดงข้อความแทนการหารศูนย์ แต่สูตรนี้ไม่ได้แก้ข้อมูลผิด เช่นราคาหายหรือช่วงไม่เท่ากัน ต้องตรวจต้นทางแยกต่างหาก

ปัดทศนิยมเมื่อไร

หากต้องการแสดงสองตำแหน่ง ให้เริ่มจากตั้งรูปแบบเซลล์เป็นทศนิยมสองตำแหน่ง ค่าจริงยังเก็บความละเอียดไว้สำหรับคำนวณต่อ ใช้ ROUND เมื่อกติกาของงานต้องการปัดค่าจริง และระบุว่าเป็นการปัดตอนใด เพราะการปัดก่อนรวมกับหลังรวมอาจให้ยอดต่างกันเล็กน้อย

ตัวอย่างนี้เป็นการเฉลี่ยข้อมูลทั่วไป ไม่ใช่คำอธิบายนโยบายประเมินสินค้าคงเหลือของทุกองค์กร หากนำไปใช้ในระบบบัญชี ให้ใช้วิธีที่องค์กรกำหนดและเก็บข้อมูลรายการให้ตรวจย้อนกลับได้