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

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

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

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

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

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

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

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

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

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

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

สูตรนี้ส่งคืนค่าเฉลี่ยที่ถูกต้องของ 21.25

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

คะแนนการป้องกันเฉลี่ย: (12 + 28 + 30 + 15) / 4 = 21.25 .

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

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

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