การสรุปข้อมูลตามเงื่อนไขด้วย Sumproduct
สูตร Sumproduct นั้นใช้รวมหรือนับค่าตามเงื่อนไข คือ ถ้าระบุเฉพาะช่วงข้อมูลที่เป็นเงื่อนไข จะเป็นการนับ ถ้าระบุช่วงข้อมูลที่ต้องการผลลัพธ์ไว้ด้วยจะเป็นการรวม
ยกตัวอย่างการใช้ฟังก์ชั่น Sumproduct เพื่อสรุปข้อมูลที่มีลักษณะเป็น Database ใน Sheet1 ตามภาพด้านล่าง
ให้เป็นรายงานใน Sheet2 ในลักษณะตามด้านล่าง
ซึ่งเราสามารถใช้สูตร Sumproduct ในเซลล์ D6 ของ Sheet2 ดังนี้ครับ
=Sumproduct(--(Sheet1!$B$5:$B$12=$C6),--(Sheet1!$C$5:$C$12=D$5),Sheet1!$D$5:$D$12)
Enter > Copy ไปด้านขวาและลงด้านล่าง
ภาพสาธิิตการแกะสูตร Sumproduct
จะเห็นว่าช่วงข้อมูลเงื่อนไขตามโจทย์นี้มี 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;FALSE;FALSE;FALSE;FALSE}),--({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}),{5;2;8;6;1;3;2;4})
การจะให้แสดงผลตามด้านบนให้ใช้เมาส์ลากคลุมแต่ละส่วนประกอบแล้วกดแป้น F9
คราวนี้ลากคลุมใหม่แต่ให้คลุมแต่ละส่วนประกอบโดยหากส่วนประกอบใดมี -- อยู่ด้านหน้าก็ให้ลากคลุมไปด้วย จากสูตรด้านบนจะได้เป็น
=SUMPRODUCT({1;0;0;0;0;0;0;0},{1;0;0;0;0;0;0;0},{5;2;8;6;1;3;2;4})
จากสูตรด้านบนหากนำส่วนประกอบที่ 1 คูณส่วนประกอบที่ 2 คูณส่วนประกอบที่ 3 ผลลัพธ์จะได้ 5
นั่นคือ 1*1*5 ได้เท่ากับ 5 สำหรับส่วนประกอบอื่น ๆ ได้ 0
Create Date : 19 ธันวาคม 2552 |
|
5 comments |
Last Update : 1 มีนาคม 2556 21:57:47 น. |
Counter : 3763 Pageviews. |
|
|
|