Cara menggunakan subtotal dengan averageif di excel

Anda bisa menggunakan rumus berikut untuk menggabungkan fungsi SUBTOTAL dan AVERAGE di Excel:

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

Rumus khusus ini memungkinkan Anda menghitung rata-rata nilai dalam rentang C2:C11 di mana nilai terkait dalam rentang B2:B11 sama dengan “Simpan” bahkan setelah rentang sel tersebut difilter dengan satu atau lain cara. yang lainnya.

Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.

Contoh: Cara menggunakan SUBTOTAL dengan AVERAGEIF di Excel

Misalkan kita mempunyai kumpulan data berikut yang berisi informasi tentang berbagai pemain bola basket:

Selanjutnya, mari kita filter data untuk hanya menampilkan baris tempat pemain Wilayah Barat berada.

Untuk melakukan ini, sorot rentang sel A1:C11 . Kemudian klik pada tab Data di sepanjang pita atas dan klik tombol Filter .

Kemudian klik panah drop-down di sebelah Conference dan pastikan hanya kotak di sebelah West yang dicentang, lalu klik OK :

Data akan secara otomatis disaring untuk menampilkan hanya baris dimana kolom Conference sama dengan West :

Jika kita mencoba menggunakan fungsi AVERAGEIF() untuk menghitung rata-rata nilai kolom Poin di mana nilai kolom Posisi sama dengan “Penjaga”, ini sebenarnya akan mengembalikan rata-rata poin untuk baris yang sama dengan “Penjaga” “. ” dalam kumpulan data asli:

Sebagai gantinya, kita perlu menggunakan rumus berikut:

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

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini dalam praktik:

Rumus ini mengembalikan rata-rata yang benar sebesar 21.25 .

Kami dapat mengonfirmasi kebenarannya dengan merata-ratakan nilai poin secara manual untuk baris yang kolom Posisinya sama dengan “Penjaga”:

Rata-rata poin penjagaan: (12 + 28 + 30 + 15) / 4 = 21.25 .

Sumber daya tambahan

Tutorial berikut menjelaskan cara melakukan operasi umum lainnya di Excel:

Cara menghapus baris yang difilter di Excel
Cara menghitung baris yang difilter di Excel
Bagaimana cara menambahkan baris yang difilter di Excel