Thep Excel
ฟังก์ชัน SUMPRODUCT ใน Excel
คูณข้อมูลจากหลายช่วงแล้วรวมผลลัพธ์ รองรับเงื่อนไขซับซ้อนและการคำนวณมีเงื่อนไข
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| array1 | required | ช่วงข้อมูล หรือ เมทริกซ์แรกที่ต้องการคูณ สามารถเป็นตัวเลข ข้อความ หรือนิพจน์ (เช่น B2:B10 หรือ (C2:C10>100)) |
| [array2] | optional | ช่วงข้อมูลที่สองที่ต้องการคูณกับ array1 สามารถเพิ่มได้จนถึง 255 ช่วง |
| [array3] | optional | ช่วงข้อมูลเพิ่มเติม หากต้องการ |
Additional Notes
SUMPRODUCT คืออะไร
SUMPRODUCT เป็นฟังก์ชันที่คูณข้อมูลจากหลายช่วงตามตำแหน่งเดียวกัน แล้วรวมผลลัพธ์ทั้งหมด ฟังก์ชันนี้ยืดหยุ่นมากและใช้ได้ทั้งการคำนวณแบบง่าย ๆ ไปจนถึงการสรุปข้อมูลที่มีเงื่อนไขซับซ้อน
สิ่งที่ทำให้ SUMPRODUCT พิเศษคือมันจัดการกับเงื่อนไขหลายตัวได้ดี และรองรับ OR logic (เงื่อนไข “หรือ”) ซึ่ง SUMIFS ไม่สามารถทำได้ตามตัว
เมื่อไหร่ต้องใช้ SUMPRODUCT
- คูณข้อมูลสองช่วง (เช่น ราคา × จำนวน)
- รวมข้อมูลที่มีหลายเงื่อนไข (เช่น ยอดขายของสินค้า A ในภูมิภาค B ในเดือน C)
- ใช้ OR logic ในเงื่อนไข (เช่น “สีแดง หรือ สีน้ำเงิน”)
- นับจำนวนข้อมูลที่ตรงกับเงื่อนไข
- คำนวณความยาวรวม หรือฟังก์ชันเชิงซ้อนโดยไม่ต้องสร้างคอลัมน์เสริม
How it works
หาค่าเฉลี่ยถ่วงน้ำหนัก (Weighted Average)
คำนวณเกรดเฉลี่ยหรือราคาทุนเฉลี่ย โดยนำ (คะแนน*หน่วยกิต) หรือ (ราคา*จำนวน) มารวมกันแล้วหารด้วยผลรวมหน่วยกิต/จำนวน
รวมยอดขายแบบมีเงื่อนไขซับซ้อน
หาผลรวมยอดขายสินค้า A หรือ B ในเดือนมกราคม (เงื่อนไข OR ระหว่างคอลัมน์ ซึ่ง SUMIFS ทำยาก)
นับจำนวนแบบมีเงื่อนไข
ใช้นับจำนวนรายการที่ตรงตามเงื่อนไขตรรกะหลายข้อ
Examples
ตัวอย่างที่ 1: คำนวณยอดขายรวม (คูณ ราคา × จำนวน)SUMPRODUCT(B2:B5,C2:C5)
SUMPRODUCT คูณค่าตำแหน่งเดียวกันของ B2:B5 (ราคา) กับ C2:C5 (จำนวน) ทีละคู่ แล้วรวมผลคูณทั้งหมดเข้าด้วยกัน เหมือนสูตร =B2*C2+B3*C3+B4*C4+B5*C5 แต่เขียนสั้นกว่าและไม่ต้องสร้างคอลัมน์เสริมสำหรับคูณทีละแถว
ตัวอย่างที่ 2: รวมยอดขายตามเงื่อนไข (สินค้าของ agent ที่ระบุ)SUMPRODUCT((A2:A6="สมชาย")*(B2:B6))
(A2:A6="สมชาย") คืน array ของ TRUE/FALSE ตามตำแหน่งที่ agent เป็นสมชาย แล้วคูณกับ B2:B6 ทำให้แถวที่ไม่ใช่สมชาย (FALSE=0) ไม่ถูกนับ SUMPRODUCT จึงรวมยอดเฉพาะแถวที่ agent ตรงเงื่อนไข ได้ผลรวมยอดขายของสมชายเท่านั้น
ตัวอย่างที่ 3: หลายเงื่อนไข (และ AND logic)SUMPRODUCT((A2:A7="เหนือ")*(B2:B7=3)*(C2:C7))
การคูณเงื่อนไขสามตัวเข้าด้วยกัน (A2:A7="เหนือ")*(B2:B7=3)*(C2:C7) ทำหน้าที่เหมือน AND logic — แถวจะถูกนับก็ต่อเมื่อทั้งภูมิภาคเป็นเหนือ และเดือนเป็น 3 พร้อมกัน (TRUE*TRUE=1) ถ้าเงื่อนไขใดเงื่อนไขหนึ่งเป็น FALSE ผลคูณจะเป็น 0 ทันที แถวนั้นจึงไม่ถูกรวม
ตัวอย่างที่ 4: OR logic (หรือ เงื่อนไข "หรือ")SUMPRODUCT(((A2:A7="แดง")+(A2:A7="น้ำเงิน"))*(B2:B7))
การบวกสองเงื่อนไข ((A2:A7="แดง")+(A2:A7="น้ำเงิน")) ทำหน้าที่เหมือน OR logic ซึ่งเป็นสิ่งที่ SUMIFS ทำไม่ได้ตรงๆ แถวที่เป็นสีแดงหรือสีน้ำเงินจะได้ค่า 1 (TRUE) ส่วนสีอื่นได้ 0 แล้วคูณกับ B2:B7 เพื่อรวมยอดเฉพาะสองสีนี้
ตัวอย่างที่ 5: นับจำนวนข้อมูล (COUNTIF แบบ SUMPRODUCT)SUMPRODUCT((A2:A8="สมชาย")*1)
(A2:A8="สมชาย")*1 แปลงค่า TRUE/FALSE ให้เป็นตัวเลข 1/0 แล้ว SUMPRODUCT รวมค่าทั้งหมดเข้าด้วยกัน ผลลัพธ์ที่ได้คือจำนวนแถวที่เป็นสมชาย เท่ากับการใช้ =COUNTIF(A2:A8,"สมชาย") แต่ SUMPRODUCT ยืดหยุ่นกว่าเมื่อต้องเช็คหลายเงื่อนไขพร้อมกัน
ตัวอย่างที่ 6: เงื่อนไขตัวเลข (มากกว่า, น้อยกว่า)SUMPRODUCT((B2:B6>3)*(C2:C6))
(B2:B6>3) คืน TRUE/FALSE ตามว่าค่าใน B2:B6 มากกว่า 3 หรือไม่ แล้วคูณกับ C2:C6 ทำให้เฉพาะแถวที่ B มากกว่า 3 เท่านั้นที่ถูกรวมเข้าผลลัพธ์ แถวที่ B<=3 จะได้ 0 เพราะ FALSE*ตัวเลข = 0 ไม่ถูกนับเข้าผลรวม
FAQs
SUMPRODUCT ต่างจาก SUMIFS อย่างไร?+
SUMIFS จำกัดอยู่แค่ AND logic เท่านั้น (ทุกเงื่อนไขต้องจริงพร้อมกัน) ส่วน SUMPRODUCT ทำได้ทั้ง AND (คูณเงื่อนไข) และ OR (บวกเงื่อนไข) รวมถึงรองรับนิพจน์ที่ซับซ้อนกว่า เช่น LEN, ISNUMBER, MOD โดยไม่ต้องสร้างคอลัมน์เสริม ส่วนตัวผมใช้ SUMIFS ก่อนถ้าเงื่อนไขง่าย แล้วค่อยสลับมา SUMPRODUCT เมื่อ SUMIFS ทำไม่ได้ครับ
ทำไมสูตร SUMPRODUCT บาง cell ต้องมี — หรือ *1 ด้วย?+
เพราะเงื่อนไขในวงเล็บ เช่น (A2:A6="สมชาย") จะได้ array เป็น TRUE/FALSE ซึ่ง SUMPRODUCT บางกรณีต้องการให้เป็นตัวเลขก่อนถึงจะคูณ/รวมได้ถูกต้อง การใส่ — (double negative) หรือ *1 คือการแปลง TRUE1 และ FALSE0 ให้ชัดเจน
SUMPRODUCT บนทั้งคอลัมน์ (เช่น A:A) ทำได้ไหม แล้วทำไมช้า?+
ทำได้ แต่ไม่แนะนำ เพราะ SUMPRODUCT ต้องประมวลผลทุกแถวในคอลัมน์ (มากกว่าล้านแถว) ทำให้ไฟล์หน่วงอย่างเห็นได้ชัด ควรระบุ range ให้ชัดเจนแทน เช่น A2:A10000 จะเร็วกว่ามาก
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ ฟังก์ชัน SUMPRODUCT ใน Excel จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
คูณเมทริกซ์ 2 ตัวตามกฎพีชคณิตเชิงเส้น (matrix multiplication) ใช้ทำโมเดลถ่วงน้ำหนักหลายมิติ วิเคราะห์ระบบสมการ และงานวิศวกรรม/การเงินที่ต้องคูณตารางข้อมูลหลายชุดพร้อมกัน
PRODUCT คูณตัวเลขทั้งหมดในช่วงข้อมูลหรือรายการอาร์กิวเมนต์ แทนที่จะต้องพิมพ์ A1*A2*A3*A4 ให้ใช้ =PRODUCT(A1:A4) เท่านั้น
SERIESSUM คำนวณผลรวมของอนุกรมกำลัง (Power Series) โดยใช้ค่า x กำลังต่างๆ กับสัมประสิทธิ์ ใช้ในการประมาณค่า sine, cosine, exponential และอื่นๆ
SUM รวมเฉพาะข้อมูลที่มี Data Type เป็นตัวเลข (Number) เท่านั้น ไม่สนใจข้อความและค่า Logic ทำให้ไม่ต้องกลัวว่าจะรวมข้อมูลผิดถ้ามีข้อความปนอยู่ในช่วง รองรับสูงสุด 255 พารามิเตอร์ และอัปเดตอัตโนมัติเมื่อข้อมูลเปลี่ยน เป็นฟังก์ชันพื้นฐานที่ใช้บ่อยที่สุดในงาน Excel
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 หรือฟังก์ชันซ้อนซับซ้อน
SUMSQ คำนวณผลรวมของกำลังสองของตัวเลข ใช้บ่อยในการวิเคราะห์สถิติและค่าคลาดเคลื่อน
SUMX2MY2 คำนวณผลรวมของผลต่างกำลังสอง (x² – y²) จากสองช่วงข้อมูล เหมาะสำหรับการเปรียบเทียบสถิติและวิเคราะห์ความแตกต่างระหว่างข้อมูลคู่
SUMX2PY2 คำนวณ x² + y² สำหรับข้อมูลจับคู่สองชุด แล้วรวมผลทั้งหมด ใช้สำหรับสถิติและการวิเคราะห์ข้อมูล
SUMXMY2 หาผลรวมของกำลังสองของผลต่าง (Sum of Squares of Differences) ระหว่างสองชุดข้อมูล คำนวณ SUM((x-y)^2) ได้อย่างคล่องแคล่ว
COUNTBLANK นับจำนวนเซลล์ว่างในช่วงข้อมูล เหมาะสำหรับตรวจสอบความสมบูรณ์ของข้อมูล
FREQUENCY นับจำนวนค่าที่ตกอยู่ในแต่ละช่วงที่กำหนด และคืนค่าเป็น Array แนวตั้ง เหมาะสำหรับสร้าง Histogram และวิเคราะห์การกระจายข้อมูล
ทดสอบว่าตัวเลขสองจำนวนเท่ากันหรือไม่ โดยคืนค่า 1 ถ้าเท่ากัน และ 0 ถ้าไม่เท่ากัน (Kronecker Delta Function)
ฟังก์ชันที่คูณค่าในคอลัมน์ของฐานข้อมูลเมื่อตรงตามเงื่อนไขที่กำหนด เป็นเครื่องมือสำหรับคำนวณผลคูณแบบมีเงื่อนไขในข้อมูลขนาดใหญ่
SEARCH ค้นหาตำแหน่งของคำที่ต้องการในข้อความหลัก ถ้าเจอคืนตัวเลขตำแหน่งที่พบ ถ้าไม่เจอคืน #VALUE! ต่างจาก FIND ตรงที่ไม่แยกแยะตัวพิมพ์เล็ก-ใหญ่ (A=a) และใช้ wildcard (* กับ ?) ค้นหาแบบมีรูปแบบได้ เอาไปต่อกับ ISNUMBER ก็กลายเป็นสูตรเช็คว่าเซลล์มีคำนี้อยู่หรือไม่ ซึ่งเป็นวิธีที่ใช้กันบ่อยที่สุดในงานจัดหมวดหมู่หรือกรองข้อมูลจากคำสำคัญ
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่