วิธีใช้ subtotal กับ sumproduct ใน excel

คุณสามารถใช้สูตรต่อไปนี้เพื่อรวมฟังก์ชัน SUBTOTAL และ SUMPRODUCT ใน Excel:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

สูตรเฉพาะนี้ช่วยให้คุณสามารถรวมผลคูณของค่าของช่วง C2:C11 และช่วง D2:D11 แม้ว่าช่วงของเซลล์นั้นจะถูกกรองด้วยวิธีใดวิธีหนึ่งแล้วก็ตาม

ตัวอย่างต่อไปนี้แสดงวิธีการใช้สูตรนี้ในทางปฏิบัติ

ตัวอย่าง: วิธีใช้ SUBTOTAL กับ SUMPRODUCT ใน Excel

สมมติว่าเรามีชุดข้อมูลต่อไปนี้ซึ่งมีข้อมูลเกี่ยวกับการขายผลิตภัณฑ์ต่างๆ ในร้านขายของชำสองแห่ง:

ต่อไป กรองข้อมูลเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ Store คือ B

เมื่อต้องการทำเช่นนี้ ให้เน้นช่วงเซลล์ A1:D11 จากนั้นคลิกที่แท็บ ข้อมูล ตามริบบิ้นด้านบนแล้วคลิกที่ปุ่ม ตัวกรอง

จากนั้นคลิกลูกศรแบบเลื่อนลงถัดจาก Store และตรวจสอบให้แน่ใจว่าได้ทำเครื่องหมายเฉพาะช่องถัดจาก B แล้วคลิก ตกลง :

ข้อมูลจะถูกกรองโดยอัตโนมัติเพื่อแสดงเฉพาะแถวที่คอลัมน์ Store เท่ากับ B :

หากเราพยายามใช้ฟังก์ชัน SUMRPODUCT() เพื่อรวมผลคูณของค่าคอลัมน์ Sales และ Price ฟังก์ชันจะส่งคืนผลรวมของผลิตภัณฑ์ของทั้งสองคอลัมน์นี้ในชุดข้อมูลดั้งเดิม:

เราจำเป็นต้องใช้สูตรต่อไปนี้แทน:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

ภาพหน้าจอต่อไปนี้แสดงวิธีใช้สูตรนี้ในทางปฏิบัติ:

สูตรนี้ส่งคืนผลรวมที่ถูกต้องของ 77.5

เราสามารถยืนยันได้ว่าสิ่งนี้ถูกต้องโดยการคำนวณผลรวมของผลิตภัณฑ์ของค่าระหว่างคอลัมน์การขายและราคาด้วยตนเอง:

ผลรวมของผลิตภัณฑ์ของมูลค่าระหว่างการขายและราคา: (3*2) + (12*2.5) + (5*3.5) + (8*3) = 77.5 .

แหล่งข้อมูลเพิ่มเติม

บทช่วยสอนต่อไปนี้จะอธิบายวิธีดำเนินการทั่วไปอื่นๆ ใน Excel:

วิธีลบแถวที่ถูกกรองใน Excel
วิธีนับแถวที่ถูกกรองใน Excel
วิธีรวมแถวที่กรองใน Excel