ซ่อมคอมพิวเตอร์นอกสถานที่ บางกะปิ รามคำแหง

วันพุธที่ 18 กรกฎาคม พ.ศ. 2555

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


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


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


ภาพการใช้สูตร Sumproduct สรุปข้อมูล Sumproduct_CreateReport


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

0 ความคิดเห็น:

แสดงความคิดเห็น