วิธีพลิกแพลงสูตร SumProduct

สูตร SumProduct เป็นสูตรที่ทำงานแบบ Array อยู่ในตัว มีหน้าที่นำ Range ของตัวเลขแต่ละตำแหน่งจากตารางที่มีขนาดเท่ากัน มาคูณกันทีละเซลล์ตามตำแหน่งที่ตรงกัน จากนั้นจึงบวกรวมผลคูณที่ได้นั้นเข้าด้วยกัน

ตัวอย่าง เช่น เราต้องการหามูลค่าต้นทุนรวมของสินค้า โดยนำต้นทุนต่อหน่วยมาคูณกับจำนวนหน่วย ซึ่งหากไม่รู้จักสูตร SumProduct ก็ต้องนำ Cost*Quantity ทีละรายการ เพื่อให้ได้ตัวเลข Total ตาม Column D จากนั้นจึงสร้างสูตรในเซลล์ F2 =SUM(D3:D5) จึงจะได้ยอดรวมต้นทุนทั้งหมด

ซึ่งแทนที่จะต้องเสียพื้นที่คำนวณหา Total ใน Column D เราสามารถใช้สูตร SumProduct ลัดหาต้นทุนรวมได้โดยใช้สูตรตามนี้

1. =SUMPRODUCT(Cost, Quantity) เป็นการใช้สูตรตามโครงสร้างปกติ โดยใช้เครื่องหมาย comma , คั่นแต่ละ Range ในสูตร หรือ
2. =SUMPRODUCT(Cost*Quantity) เป็นสูตรที่นำแต่ละ Range มาคูณกันเลย ซึ่งการสร้างแบบคูณกันนี้ จะช่วยให้เราสามารถคลิกลากทับการคำนวณในวงเล็บแล้วกดปุ่ม F9 เพื่อเห็นผลการคูณกันของแต่ละตำแหน่งได้ด้วย

จากนั้น ขอย้อนกลับไปเรื่องโครงสร้างภายในสูตร Array IF ตามที่อธิบายไว้แล้วว่า โครงสร้างของสูตร Array IF ประกอบด้วย 2 ส่วนใหญ่ๆ ซึ่งโดยทั่วไปทั้ง 2 ส่วนต้องมีขนาดความสูงความกว้างของ Array เท่ากัน กล่าวคือ

1. ส่วนของ Array ที่ทำหน้าที่คืนค่า True หรือ False
2. ส่วนของ Array ที่เป็นตัวเลขหรือค่าคำตอบเรื่องที่ต้องการ

ขอนำสูตร =SUMPRODUCT(  (Id=G3)*(Name=H3)  *Amount) มาวิเคราะห์แยกโครงสร้างข้างต้น จะพบว่า

1. ส่วนของ Array ที่ทำหน้าที่คืนค่า True หรือ False ได้แก่ ส่วนของ (Id=G3) และ (Name=H3)  ซึ่งนำมาคูณกันเพื่อทำให้เปลี่ยน True เป็นเลข 1 และเปลี่ยน False เป็นเลข 0
2. ส่วนของ Array ที่เป็นตัวเลขหรือค่าคำตอบเรื่องที่ต้องการ ได้แก่ส่วนของ Amount

ดังนั้นหากต้องการปรับสูตรให้คำนวณนับแทนการหายอดรวมของ Amount โดยให้นับว่ามีค่าตามเงื่อนไขทั้งสิ้นกี่รายการ ให้ใช้สูตรใดสูตรหนึ่งต่อไปนี้ก็ได้

1. =SUMPRODUCT(  (Id=G3)*(Name=H3) ) โดยสูตร SumProduct จะทำหน้าที่รวมเลข 1 ที่ได้จากการคูณกันของ Array ภายในวงเล็บว่ามีเลข 1 กี่ตัว นั่นคือนับจำนวนรายการที่มีเงื่อนไขตามต้องการนั่นเอง
2. =SUMPRODUCT(  (Id=G3)*1, (Name=H3)*1)
3. =SUMPRODUCT(  (Id=G3)/1, (Name=H3)/1)
4. =SUMPRODUCT(  (Id=G3)+0, (Name=H3)+0)
5. =SUMPRODUCT(  (Id=G3)-0, (Name=H3)-0)
6. =SUMPRODUCT(  --(Id=G3), --(Name=H3) )

สูตร SumProduct แบบที่ 2-6 เป็นสูตรที่ใช้เครื่องหมาย comma , คั่น จึงจำเป็นต้องกระตุ้นให้เปลี่ยน True เป็นเลข 1 และเปลี่ยน False เป็นเลข 0 โดยนำ True หรือ False ที่ได้จากการตรวจสอบเงื่อนไขไป *1, /1, +0, -0, หรือใส่เครื่องหมายลบลบไว้ข้างหน้าก่อน

ดังนั้นจึงขอแนะให้สร้างสูตร SumProduct แบบนำเงื่อนไขมาคำนวณ(คูณหรือบวก)กันเองก่อน เพราะนอกจากจะแกะหาผลจากการคูณหรือบวกกันจากส่วนของเงื่อนไขที่นำมาคำนวณกันได้แล้ว ยังเป็นสูตรที่สั้นกว่าและมีขั้นตอนการคำนวณน้อยกว่าสูตร SumProduct ที่ใช้แบบเครื่องหมาย comma , คั่นอยู่ภายใน

หมายเหตุ

โดยทั่วไปหากเงื่อนไขที่ใช้ในสูตร Array IF เป็นการตรวจสอบว่าเท่ากันหรือไม่ โดยใช้เครื่องหมาย = ในการเปรียบเทียบว่าเป็น True หรือ False เราสามารถท่องจำไว้เลยดังนี้

• ถ้า Range ที่นำมาตรวจสอบเงื่อนไขเป็น เรื่องเดียวกัน ให้นำผลลัพธ์จากการตรวจสอบเงื่อนไขมา บวกกัน และมีวงเล็บเปิดด้านหน้า 3 ตัว
• ถ้าถ้า Range ที่นำมาตรวจสอบเงื่อนไขเป็น ต่างเรื่องกัน ให้นำผลลัพธ์จากการตรวจสอบเงื่อนไขมา คูณกัน และมีวงเล็บเปิดด้านหน้า 2 ตัว

แต่ถ้าเป็นการตรวจสอบเงื่อนไขประเภทมากกว่าหรือน้อยกว่าหรือผสมกัน แม้ Range ที่นำมาเป็นเงื่อนไขจะเป็นเรื่องเดียวกัน ก็ไม่จำเป็นว่าต้องนำผลลัพธ์จากการตรวจสอบเงื่อนไขมาบวกกันเสมอไป บางกรณีอาจใช้คูณกันก็ได้ ทั้งนี้ขึ้นกับลักษณะการเปรียบเทียบที่ใช้ด้วย โดยขอให้คิดหาวิธีใดก็ได้ที่จะทำให้เกิดเลข 1 ชี้เฉพาะตำแหน่งรายการที่ต้องการให้ได้