สูตร Sumproduct นั้นใช้รวมหรือนับค่าตามเงื่อนไข คือ ถ้าระบุเฉพาะช่วงข้อมูลที่เป็นเงื่อนไข จะเป็นการนับ ถ้าระบุช่วงข้อมูลที่ต้องการผลลัพธ์ไว้ด้วยจะเป็นการรวม
ยกตัวอย่างการใช้ฟังก์ชั่น Sumproduct เพื่อสรุปข้อมูลที่มีลักษณะเป็น Database ใน Sheet1 ให้เป็นรายงานตาม Sheet2 ตามภาพด้านล่าง
ภาพการใช้สูตร Sumproduct สรุปข้อมูล
ซึ่งเราสามารถใช้สูตร Sumproduct ในเซลล์ D6 ของ Sheet2 ดังนี้ครับ
=Sumproduct(--(Sheet1!$B$5:$B$12=$C6),--(Sheet1!$C$5:$C$12=D$5),Sheet1!$D$5:$D$12)
Enter > Copy ไปด้านขวาและลงด้านล่าง
จะเห็นว่าช่วงข้อมูลเงื่อนไขตามโจทย์นี้มี 2 ช่วงข้อมูลดังนี้ครับ
- Sheet1 ช่วง B5:B12
- Sheet1 ช่วง C5:C12
Sumproduct ใน Excel 2003 จะมีส่วนประกอบ 30 ส่วนประกอบ หากเป็น Excel 2007 ขึ้นไปจะมีถึง 255 ส่วนประกอบ โดยแต่ละส่วนประกอบจะคูณกัน เช่น
=Sumproduct({1,3},{5,6}) <== การเขียนล้อมด้วยปีกกาเปรียบได้กับการเลือกช่วงเซลล์หรือเรียกอีกอย่างว่า Array
หมายถึงนำ 1*5+3*6 ผลลัพธ์จะได้ 23
การสังเกตว่าช่วงใดเป็นเงื่อนไขในสูตร Sumproduct ดูได้จากส่วนประกอบที่มีเครื่องหมายเปรียบเทียบทั้งหลายครับ ส่วนประกอบใดที่ไม่มีเครื่องหมายเปรียบเทียบแสดงว่าใช้เป็นผลลัพธ์
จากสูตรที่ Sheet2 เซลล์ E6 มีดังนี้
=SUMPRODUCT(--(Sheet1!$B$5:$B$12=$C6),--(Sheet1!$C$5:$C$12=E$5),Sheet1!$D$5:$D$12)
นั่นหมายถึงว่า ให้นำ --(Sheet1!$B$5:$B$12=$C6) คูณกับ --(Sheet1!$C$5:$C$12=E$5) คูณกับ Sheet1!$D$5:$D$12
จะเห็นว่ามี 2 ช่วงที่มีเครื่องหมาย = อยู่ด้วย แสดงให้เห็นว่ามีช่วงเงื่อนไขอยู่ 2 เงื่อนไข จะสังเกตจะเห็นว่าการเปรียบเทียบนั้นเป็นการเอาช่วงเซลล์เปรียบเทียบกับเซลล์เดี่ยว ผลลัพธ์หากเท่ากันจะได้ True ถ้าไม่เท่ากันจะได้ False
ส่วนเครื่องหมาย -- ที่นำหน้าช่วงที่เป็นเงื่อนไข เป็นการแปลง True ให้เป็น 1 แปลง False ให้เป็น 0
ที่ต้องแปลงก่อนเพราะโปรแกรมมองว่า True และ False ใน Sumproduct เป็น Text จะต้องแปลงให้เป็นตัวเลขเสียก่อนถึงจะเอาไปคำนวณต่อได้
การแกะสูตร
ต่อไปนี้จะเป็นการดูการคำนวณทีละส่วนประกอบในสูตรที่ Sheet2 เซลล์ E6 กันอีกรอบครับ
จากสูตร
=SUMPRODUCT(--(Sheet1!$B$5:$B$12=$C6),--(Sheet1!$C$5:$C$12=E$5),Sheet1!$D$5:$D$12)
จะได้เป็น
=SUMPRODUCT(--({TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}),--({FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}),{3;10;15;20;5;30;35;40})
การจะให้แสดงผลตามด้านบนให้ใช้เมาส์ลากคลุมแต่ละส่วนประกอบแล้วกดแป้น F9
คราวนี้ลากคลุมใหม่แต่ให้คลุมแต่ละส่วนประกอบโดยหากส่วนประกอบใดมี -- อยู่ด้านหน้าก็ให้ลากคลุมไปด้วย จากสูตรด้านบนจะได้เป็น
=SUMPRODUCT({1;0;0;0;1;0;0;0},{0;1;0;0;1;1;0;0},{3;10;15;20;5;30;35;40})
จากสูตรด้านบนหากนำส่วนประกอบที่ 1 คูณส่วนประกอบที่ 2 คูณส่วนประกอบที่ 3 ผลลัพธ์จะได้ 5 มาจากค่าในตำแหน่งที่ 5 ของแต่ละส่วนประกอบมาคูณกัน
นั่นคือ 1*1*5 ได้เท่ากับ 5 สำหรับส่วนประกอบอื่น ๆ เมื่อนำตำแหน่งเดียวกันในแต่ละส่วนประกอบคุณกันแล้วได้ผลลัพธ์เป็น 0
Video แสดงการใช้ Sumproduct หาผลรวมโดยไม่ต้องใช้ Column ช่วย
Revised: January 28, 2017 at 16:39