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

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

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

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

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

สมมติว่าเรามีชุดข้อมูลต่อไปนี้ที่มีข้อมูลเกี่ยวกับผู้เล่นบาสเกตบอลต่างๆ:

ต่อไป เรามากรองข้อมูลเพื่อแสดงเฉพาะแถวที่มีผู้เล่น Western Conference อยู่

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

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

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

หากเราลองใช้ฟังก์ชัน SUMIF() เพื่อรวมค่าในคอลัมน์ Points โดยที่ค่าคอลัมน์ Position เท่ากับ “Guard” จริงๆ แล้วจะส่งกลับผลรวมของคะแนนสำหรับแถวที่เท่ากับ “Guard” ใน SUMIF() การทำงาน. ชุดข้อมูลดั้งเดิม:

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

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

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

เราสามารถยืนยันได้ว่าสิ่งนี้ถูกต้องโดยการคำนวณผลรวมของค่าจุดสำหรับแถวที่คอลัมน์ตำแหน่งเท่ากับ “Guard” ด้วยตนเอง:

ผลรวมแต้มป้องกัน: 12 + 28 + 30 + 15 = 85

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

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

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