SUMPRODUCT

TLDR สรุปสั้นๆ

SUMPRODUCT ใช้จับคู่แต่ละ Item ของ Array หลายชุดคูณกันแล้วหาผลรวม

คำอธิบาย

ฟังก์ชัน SUMPRODUCT ใช้ในการทำการคูณและหาผลรวมของเซลล์ใน Array หลายชุดที่สัมพันธ์กัน (ซึ่ง PRODUCT แปลว่าคูณ SUM แปลว่าหาผลรวม)

มีครั้งแรกในเวอร์ชันไหน

Excel 2003 หรือ Version ก่อนหน้า

รูปแบบคำสั่ง (Syntax)

=SUMPRODUCT(array1, [array2], [array3], ...)

Arguments

• array1 (Required – Range)
  อาร์เรย์แรกที่ต้องการคูณและหาผลรวม
• array2 (Optional – Range)
  อาร์เรย์ที่สอง (ไม่จำเป็น) ที่ต้องการคูณและหาผลรวม
• array3 (Optional – Range)
  อาร์เรย์ที่สาม (ไม่จำเป็น) ที่ต้องการคูณและหาผลรวม

ตัวอย่างการใช้งาน (Examples)

• Formula:

  =SUMPRODUCT(E3:E6, F3:F6)

  Description: สูตรนี้จะคำนวณยอดขายรวม โดยนำจำนวน (Qty) จาก E3:E6 คูณกับราคาต่อหน่วย (UnitPrice) จาก F3:F6 โดยนำแต่ละรายการคูณกันก่อน แล้วรวมผลลัพธ์ทั้งหมดเข้าด้วยกัน
  Result: 795 (เกิดจาก (5×30)+(10×20)+(8×25)+(7×35)=150+200+200+245)
  
• Formula:

  =SUMPRODUCT(FruitOrder[Qty], FruitOrder[UnitPrice])

  Description: สูตรนี้จะคำนวณยอดขายรวม โดยนำค่าจากตาราง FruitOrder ในคอลัมน์จำนวน (Qty) คูณกับราคาต่อหน่วย (UnitPrice) โดยนำแต่ละรายการคูณกันก่อน แล้วรวมผลลัพธ์ทั้งหมดเข้าด้วยกัน
  Result: 795 (เกิดจาก (5×30)+(10×20)+(8×25)+(7×35)=150+200+200+245)
  
• Formula:

  =SUMPRODUCT((FruitOrder[Product]="apple")*FruitOrder[Qty]*FruitOrder[UnitPrice])

  Description: สูตรนี้จะคำนวณยอดขายรวมเฉพาะสินค้าที่เป็น apple โดยเทคนิคคือ ตรวจสอบว่าแต่ละรายการเป็น “Apple” หรือไม่ (ผลลัพธ์จะเป็น TRUE/FALSE) จากนั้นแปลงเป็น 1 หรือ 0 แล้วไปคูณต่อ (สังเกตว่าทั้งหมดเขียนอยู่ใน array1 ก้อนเดียวได้เลย)
  Result: 395 เกิดจาก (เกิดจาก (1×5×30)+(0×10×20)+(0×8×25)+(1×7×35)=150+245)
  
• Formula:

  =SUMPRODUCT(FruitOrder[Qty], FruitOrder[UnitPrice]) / SUM(FruitOrder[Qty])

  Description: หายอดขายเฉลี่ยต่อชิ้น โดยคำนวณยอดขายรวมจาก SUMPRODUCT ปกติ แล้วหารด้วยจำนวนชิ้นรวม
  Result: 26.5 (เกิดจาก ยอดขายรวมคือ 795 หารด้วย จำนวนชิ้นรวม คือ 30)
  
• Formula:

  =SUMPRODUCT((FruitOrder[Product]="apple")*FruitOrder[Qty]*FruitOrder[UnitPrice]) / SUMPRODUCT((FruitOrder[Product]="apple")*FruitOrder[Qty])

  Description: หายอดขายเฉลี่ยต่อชิ้นเฉพาะ apple โดยคำนวณยอดขายรวมของ apple จาก SUMPRODUCT ปกติ แล้วหารด้วยจำนวนชิ้นรวมของ apple
  Result: 32.917 (เกิดจาก ยอดขายรวม เฉพาะ apple คือ 395 หารด้วย จำนวนชิ้นรวมเฉพาะ apple คือ 12)
  
  

Tips & Tricks

เราสามารถเขียนการคำนวณ array operations หลายอัน (เช่น บวก ลบ คูณ หาร) อยู่ใน array1 ก้อนเดียวได้เลย แต่จะต้องระวังเรื่องลำดับการคำนวณให้ดี โดยเฉพาะการใช้เครื่องหมายเปรียบเทียบซึ่งปกติจพคำนวณลำดับสุดท้าย ทำให้เราจะต้องบังคับใส่วงเล็บให้คำนวณมันก่อน นอกจากนี้ ควรหลีกเลี่ยงการใช้ฟังก์ชันนี้กับการอ้างอิงทั้งคอลัมน์อย่าง SUMPRODUCT(E:E,F:F) เพื่อเพิ่มประสิทธิภาพ

ข้อควรระวัง (Cautions)

ฟังก์ชัน SUMPRODUCT ต้องการให้ array ที่นำมาคำนวณมีขนาดเท่ากันทั้งหมด ถ้าไม่เช่นนั้นจะเกิดข้อผิดพลาด #VALUE! และควรระวังในการใช้ฟังก์ชันกับข้อมูลที่ไม่ได้มีตัวเลข ฟังก์ชันจะปฏิบัติกับค่าเหล่านั้นเสมือนเป็นศูนย์

ฟังก์ชันที่เกี่ยวข้อง

• SUM
• PRODUCT

References

• Microsoft Official Documentation
• MyExcelOnline Blog
• Excel Easy Examples

ขอบคุณที่เข้ามาอ่านนะครับ ❤️