Microsoft Excel Tips and Tricks: การสรุปข้อมูลตามเงื่อนไขด้วย Sumproduct

สูตร 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 ช่วงข้อมูลดังนี้ครับ

1. Sheet1 ช่วง B5:B12


2. Sheet1 ช่วง C5:C12

Sumproduct จะมีส่วนประกอบได้ถึง 30 ส่วนประกอบ โดยแต่ละส่วนประกอบจะคูณกัน เช่น

=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