Excel

ฟังก์ชัน Excel SUMPRODUCT

Excel Sumproduct Function

ฟังก์ชัน Excel SUMPRODUCTสรุป

ฟังก์ชัน SUMPRODUCT คูณช่วงหรืออาร์เรย์เข้าด้วยกัน และส่งคืนผลรวมของผลิตภัณฑ์ ฟังดูน่าเบื่อ แต่ SUMPRODUCT เป็นฟังก์ชันอเนกประสงค์อย่างเหลือเชื่อที่สามารถใช้ในการนับและรวมเช่น COUNTIFS หรือ SUMIFS แต่มีความยืดหยุ่นมากกว่า สามารถใช้ฟังก์ชันอื่นๆ ภายใน SUMPRODUCT ได้อย่างง่ายดายเพื่อขยายฟังก์ชันการทำงานให้ดียิ่งขึ้น





วัตถุประสงค์ คูณแล้วรวมอาร์เรย์ ส่งกลับค่า ผลลัพธ์ของอาร์เรย์แบบทวีคูณและผลรวม ไวยากรณ์ =SUMPRODUCT (array1, [array2], ...) อาร์กิวเมนต์
  • array1 - อาร์เรย์หรือช่วงแรกที่จะคูณแล้วบวก
  • array2 - [ไม่บังคับ] อาร์เรย์หรือช่วงที่สองที่จะคูณแล้วบวก
เวอร์ชั่น Excel 2003 บันทึกการใช้งาน

ฟังก์ชัน SUMPRODUCT ใช้งานได้กับอาร์เรย์ แต่ไม่ต้องการไวยากรณ์อาร์เรย์ปกติ (Ctrl + Shift + Enter) เพื่อป้อน จุดประสงค์ของฟังก์ชัน SUMPRODUCT คือการคูณแล้วรวมอาร์เรย์ หากมีเพียงหนึ่งอาร์เรย์ SUMPRODUCT จะรวมรายการในอาร์เรย์ สามารถจัดหาได้ถึง 30 อาร์เรย์

เมื่อคุณพบ SUMPRODUCT ครั้งแรก อาจดูน่าเบื่อ ซับซ้อน และไร้จุดหมาย แต่ SUMPRODUCT เป็นฟังก์ชันอเนกประสงค์ที่ใช้งานได้หลากหลายอย่างน่าอัศจรรย์ เนื่องจากจะจัดการอาร์เรย์ได้อย่างสวยงามและไม่มีการร้องเรียน คุณสามารถใช้เพื่อประมวลผลช่วงของเซลล์ด้วยวิธีที่ชาญฉลาดและสวยงาม (ดูลิงก์ไปยังตัวอย่างสูตรในหน้านี้)





เพื่อแสดงให้เห็นว่า SUMPRODUCT ทำงานอย่างไร ต่อไปนี้คือตัวอย่างทั่วไปบางส่วน

SUMPRODUCT สำหรับผลรวมและการนับตามเงื่อนไข

สมมติว่าคุณมีข้อมูลการสั่งซื้อใน A2:B6 โดยมีสถานะอยู่ในคอลัมน์ A ยอดขายในคอลัมน์ B:



ถึง NS
1 สถานะ ฝ่ายขาย
2 ออก 75
3 อะไร 100
4 TX 125
5 อะไร 125
6 TX 150

การใช้ SUMPRODUCT คุณสามารถ นับ ยอดขายรวมสำหรับ Texas ('TX') ด้วยสูตรนี้:

 
= SUMPRODUCT (--(A2:A6='TX'))

และคุณสามารถ ผลรวม ยอดขายรวมสำหรับ Texas ('TX') ด้วยสูตรนี้:

 
= SUMPRODUCT (--(A2:A6='TX'),B2:B6)

หมายเหตุ: อย่าสับสนกับค่าลบสองเท่า นี่เป็นเคล็ดลับทั่วไปที่ใช้ในสูตร Excel ขั้นสูงเพื่อบังคับค่า TRUE และ FALSE ให้เป็นค่า 1 และ 0 ดูเพิ่มเติมด้านล่าง...

วิธีทำ bins ใน excel

สำหรับตัวอย่างผลรวมข้างต้น นี่คือการแสดงเสมือนของสองอาร์เรย์ที่ประมวลผลครั้งแรกโดย SUMPRODUCT:

array1 array2
เท็จ 75
เท็จ 100
จริง 125
เท็จ 125
จริง 150

แต่ละอาร์เรย์มี 5 รายการ อาร์เรย์แรกมีค่า TRUE / FALSE ที่เกิดจากนิพจน์ A2:A6='TX' และอาร์เรย์ที่สองคือเนื้อหาของ B2:B6 แต่ละรายการในอาร์เรย์แรกจะถูกคูณด้วยรายการที่สอดคล้องกันในอาร์เรย์ที่สอง อย่างไรก็ตาม ในสถานะปัจจุบัน ผลลัพธ์ของ SUMPRODUCT จะเป็นศูนย์ เนื่องจากค่า TRUE และ FALSE จะถือเป็นศูนย์ เราต้องการให้รายการใน array1 เป็นตัวเลข โดยจะต้อง 'บังคับ' ให้เป็นหนึ่งและศูนย์ นี่คือที่มาของ double-negative

ด้วยการใช้ค่าลบคู่ -- (double unary สำหรับประเภททางเทคนิคของคุณ) เราสามารถบังคับ TRUE/FALSE ให้เป็นค่าตัวเลขหนึ่งและศูนย์ดังที่แสดงในการแสดงเสมือนด้านล่าง คอลัมน์สุดท้าย 'ผลิตภัณฑ์' แสดงถึงผลลัพธ์ของการคูณสองอาร์เรย์เข้าด้วยกัน ผลลัพธ์ที่ได้คือ 275 คือค่าที่ SUMPRODUCT ส่งกลับ

array1 array2 ผลิตภัณฑ์
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
ซำ 275

การใช้ไวยากรณ์วงเล็บปีกกาสำหรับอาร์เรย์ ตัวอย่างจะมีลักษณะดังนี้หลังจากการบีบบังคับ:

ข้อใดต่อไปนี้เป็นช่วงที่ตั้งชื่อที่ถูกต้องใน excel:
 
= SUMPRODUCT ({0,0,1,0,1},{75,100,125,125,150}) 

และเช่นนี้หลังจากการคูณ:

 
= SUMPRODUCT ({0,0,125,0,150})

ตัวอย่างนี้ ขยายแนวคิดข้างต้นพร้อมรายละเอียดเพิ่มเติม

SUMPRODUCT พร้อมฟังก์ชั่นอื่นๆ

SUMPRODUCT สามารถใช้ฟังก์ชันอื่นๆ ได้โดยตรง คุณอาจเห็น SUMPRODUCT ใช้กับฟังก์ชัน LEN เพื่อนับจำนวนอักขระทั้งหมดในช่วง หรือกับฟังก์ชันเช่น ISBLANK, ISTEXT เป็นต้น ซึ่งปกติแล้วไม่ใช่ฟังก์ชันอาร์เรย์ แต่เมื่อกำหนดช่วง ฟังก์ชันเหล่านี้จะสร้าง 'อาร์เรย์ผลลัพธ์' เนื่องจาก SUMPRODUCT สร้างขึ้นเพื่อทำงานกับอาร์เรย์ มันจึงสามารถคำนวณกับอาร์เรย์ได้โดยตรง วิธีนี้เป็นวิธีที่ดีในการประหยัดพื้นที่ในเวิร์กชีต โดยไม่จำเป็นต้องใช้คอลัมน์ 'ตัวช่วย'

ตัวอย่างเช่น สมมติว่าคุณมีค่าข้อความที่แตกต่างกัน 10 ค่าใน A1:A10 และคุณต้องการนับจำนวนอักขระทั้งหมดสำหรับค่าทั้งหมด 10 ค่า คุณสามารถเพิ่มคอลัมน์ตัวช่วยในคอลัมน์ B ที่ใช้สูตรนี้: LEN(A1) เพื่อคำนวณอักขระในแต่ละเซลล์ จากนั้นคุณสามารถใช้ SUM เพื่อบวกตัวเลขทั้งหมด 10 ตัว อย่างไรก็ตาม เมื่อใช้ SUMPRODUCT คุณสามารถเขียนสูตรดังนี้:

 
= SUMPRODUCT ( LEN (A1:A10))

เมื่อใช้กับช่วงเช่น A1:A10 LEN จะคืนค่าอาร์เรย์ 10 ค่า จากนั้น SUMPRODUCT จะรวมค่าทั้งหมดและส่งคืนผลลัพธ์ โดยไม่ต้องใช้คอลัมน์ตัวช่วย

ดูตัวอย่างวิธีอื่นๆ ในการใช้ SUMPRODUCT ด้านล่าง

หมายเหตุ:

  1. SUMPRODUCT ถือว่ารายการที่ไม่ใช่ตัวเลขในอาร์เรย์เป็นศูนย์
  2. อาร์กิวเมนต์อาร์เรย์ต้องมีขนาดเท่ากัน มิฉะนั้น SUMPRODUCT จะสร้าง #VALUE! ค่าความผิดพลาด
  3. การทดสอบเชิงตรรกะภายในอาร์เรย์จะสร้างค่า TRUE และ FALSE ในกรณีส่วนใหญ่ คุณจะต้องบังคับสิ่งเหล่านี้ให้มีค่าเท่ากับ 1 และ 0
  4. SUMPRODUCT สามารถใช้ผลลัพธ์ของฟังก์ชันอื่นๆ ได้โดยตรง (ดูตัวอย่างสูตรด้านล่าง)


^