BlogGang.com : : คนควน : การสรุปข้อมูลตามเงื่อนไขด้วย Sumproduct

การสรุปข้อมูลตามเงื่อนไขด้วย Sumproduct

สูตร Sumproduct นั้นใช้รวมหรือนับค่าตามเงื่อนไข คือ ถ้าระบุเฉพาะช่วงข้อมูลที่เป็นเงื่อนไข จะเป็นการนับ ถ้าระบุช่วงข้อมูลที่ต้องการผลลัพธ์ไว้ด้วยจะเป็นการรวม

ยกตัวอย่างการใช้ฟังก์ชั่น Sumproduct เพื่อสรุปข้อมูลที่มีลักษณะเป็น Database ใน Sheet1 ตามภาพด้านล่าง

Sumproduct01

ให้เป็นรายงานใน Sheet2 ในลักษณะตามด้านล่าง

Sumproduct02

ซึ่งเราสามารถใช้สูตร 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

Sumproduct03

จะเห็นว่าช่วงข้อมูลเงื่อนไขตามโจทย์นี้มี 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

Last Update : 1 มีนาคม 2556 21:57:47 น.

5 comments

Counter : 3759 Pageviews.

เยี่ยมเลย ครับ แต่ขอความช่วยเหลือด้วยครับ

โดย: เส IP: 222.123.61.167 วันที่: 22 มกราคม 2553 เวลา:12:10:12 น.

สวัสดีครับคุณเส

ไม่ทราบว่าปัญหามีว่าอย่างไรครับ

โดย: คนควน

วันที่: 22 มกราคม 2553 เวลา:17:53:04 น.

หาค่าส่งกลับค่าที่เกิดขึ้นบ่อยหรือทำซ้ำบ่อยที่สุด
แล้วเอาค่าที่ส่งกลับค่าที่เกิดบ่อยมาแยกประเภทอีกทีขอความอนุเคราะห์ช่วยบอกสูตรด้วยครับ

โดย: ประเสริฐ IP: 118.173.224.236 วันที่: 9 มีนาคม 2553 เวลา:11:49:33 น.

มีคอลัมน์ มี 20 คอลัมน์ มีแถว 10 แถว สูตรค่าส่งกลับที่เกิดขึ้นบ่อยหรือซ้ำกันบ่อยๆ ของคอลัมน์ พอทราบแล้ว แต่อยากทราบสูตรค่าส่งกลับที่เกิดขึ้นบ่อยหรือซ้ำกันบ่อยๆ แนวนอน ขอขอบคุณครับ

โดย: ประเสริฐ IP: 118.173.224.236 วันที่: 9 มีนาคม 2553 เวลา:12:22:12 น.

สวัสดีครับ คุณประเสริฐช่วยไปโพสต์ที่ //www.snasui.com/ เป็น Forum ของผมเอง แนบไฟล์ตัวอย่าง อธิบายปัญหา ระบุคำตอบที่ต้องการด้วยครับ

โดย: คนควน

วันที่: 11 มีนาคม 2553 เวลา:19:56:50 น.