Dalam dunia kerja, terutama pada bagian administrasi, keuangan, hingga analisis penjualan, kita sering kali perlu menghitung nilai rata-rata berdasarkan kriteria tertentu. Namun, lebih sering lagi kita menghadapi situasi di mana kita harus menghitung rata-rata dengan pengecualian, seperti mengecualikan satu produk tertentu, satu kategori tertentu, atau data yang tidak relevan. Selain itu, analisis biasanya juga dibatasi oleh rentang tanggal agar hasil perhitungan lebih akurat.
Artikel ini akan membahas tiga cara menghitung rata-rata dengan pengecualian serta batas tanggal di Excel, mulai dari cara modern untuk Excel 2021 hingga cara konvensional untuk Excel versi lama. Ketiga metode ini memanfaatkan dataset berupa data penjualan motor.
Melalui tiga pendekatan ini, Anda dapat memilih metode mana yang paling sesuai dengan versi Excel dan kebutuhan perhitungan Anda.
Contoh Dataset Penjualan
Pada contoh data, kita memiliki tiga kolom utama:
- B3:B22 → Tanggal
- C3:C22 → Unit Terjual (motor)
- D3:D22 → Jumlah Penjualan
Produk yang terjual berupa jenis motor seperti Honda Beat, Honda Vario, dan Yamaha NMAX. Kita juga memiliki parameter:
- G3 → Produk yang ingin dikecualikan (misal: Honda Beat)
- F7 → Tanggal awal
- G7 → Tanggal akhir
Tujuan kita adalah menghitung rata-rata dengan pengecualian berdasarkan:
- Produk yang tidak sama dengan sel G3
- Tanggal berada di antara F7 hingga G7
- Nilai yang dihitung adalah jumlah penjualan pada kolom D
Dengan demikian, kita akan membahas tiga metode unggulan:
- FILTER + AVERAGE (Excel 2021/365) → Cara modern
- AVERAGEIFS (Excel 2010 ke atas) → Cara klasik
- LET + FILTER (Excel 2021/365) → Cara terstruktur dan profesional
1. Menggunakan AVERAGE + FILTER
Metode pertama adalah metode paling modern, paling fleksibel, dan paling mudah dipahami ketika kita ingin menghitung rata-rata dengan pengecualian. Dengan memanfaatkan fungsi FILTER, kita dapat menyaring data berdasarkan beberapa kondisi dan menghitung rata-rata dari hasil penyaringan tersebut.
Rumus yang digunakan
=AVERAGE(FILTER(D3:D22;(C3:C22<>G3)*(B3:B22>=F7)*(B3:B22<=G7)))Dan jika benar maka hasilnya adalah sebagai berikut:
Penjelasan Rumus
Rumus ini berfungsi dengan cara:
- FILTER(D3:D22; …) → Menyaring kolom jumlah penjualan (D)
- (C3:C22<>G3) → Membuang baris yang memiliki produk sesuai sel pengecualian (misalnya Honda Beat)
- (B3:B22>=F7) → Mengambil baris yang tanggalnya lebih besar atau sama dengan tanggal awal
- (B3:B22<=G7) → Mengambil baris yang tanggalnya lebih kecil atau sama dengan tanggal akhir
- AVERAGE(…) → Menghitung rata-rata dari data yang sudah disaring
Mengapa metode ini direkomendasikan?
Metode ini sangat mudah disesuaikan dan menjadi pilihan utama ketika kita ingin menghitung rata-rata dengan pengecualian pada dataset besar. Karena bersifat dinamis, FILTER akan otomatis menyesuaikan banyaknya baris data yang memenuhi syarat, tanpa memerlukan pemisahan range secara manual.
Kapan metode ini cocok digunakan?
- Saat Anda menggunakan Excel 2021 atau Microsoft 365
- Saat dataset besar dan memerlukan perhitungan cepat
- Saat Anda ingin menghindari rumus panjang model lama
- Ketika fleksibilitas menjadi prioritas utama
Pada gambar yang Anda lampirkan, hasil metode ini tampil di kolom “Excel 2021” dengan hasil 123,13 sesuai filter terhadap data penjualan.
Metode ini adalah salah satu cara paling cepat untuk menghitung rata-rata dengan pengecualian, terutama bila Anda harus sering memfilter data berdasarkan tanggal dan kategori tertentu.
2. Menggunakan AVERAGEIFS
Bagi Anda yang masih memakai Excel versi lebih lama seperti Excel 2010 atau Excel 2013, metode sebelumnya tidak bisa digunakan. Karena itu, metode kedua menggunakan fungsi AVERAGEIFS, yang sudah tersedia sejak Excel 2007.
Rumus untuk Excel versi lama
=AVERAGEIFS($D$3:$D$22;$C$3:$C$22;"<>"&$G$3;$B$3:$B$22;">="&F7;$B$3:$B$22;"<="&G7)Dan berikut adalah hasilnya:
Penjelasan Rumus
- $D$3:$D$22 → Range yang akan dihitung rata-ratanya
- $C$3:$C$22;”<>”&$G$3 → Produk yang TIDAK SAMA dengan produk pengecualian
- $B$3:$B$22;”>=”&F7 → Tanggal minimal
- $B$3:$B$22;”<=”&G7 → Tanggal maksimal
Apa kelebihan metode ini?
- Kompatibel hampir di semua versi Excel modern
- Mudah digunakan bagi pemula
- Mendukung banyak kriteria
Kekurangan metode ini
- Tidak sefleksibel FILTER
- Tidak bisa digunakan untuk operasi array dinamis
- Tidak menampilkan hasil filtered data (hanya menghasilkan rata-rata)
Namun, metode ini tetap menjadi standar umum untuk menghitung rata-rata dengan pengecualian, terutama untuk pengguna Excel versi lama.
Di contoh tabel yang Anda gunakan, hasil rumus AVERAGEIFS juga menghasilkan 123,13, sehingga hasilnya konsisten dengan metode modern.
3. Menggunakan LET + FILTER (Metode Terstruktur dan Profesional)
Metode ketiga adalah metode paling rapi dan profesional karena menggunakan fungsi LET untuk mendefinisikan variabel. Metode ini memudahkan analisis yang lebih kompleks dan mempercepat kinerja karena Excel hanya memproses setiap variabel satu kali.
Rumus LET untuk Excel 2021
=LET(
tgl; B3:B22;
produk; C3:C22;
jual; D3:D22;
kriteria; (produk<>G3)*(tgl>=F7)*(tgl<=G7);
AVERAGE(FILTER(jual;kriteria))
)Berikut hasilnya:
Penjelasan Rumus
- tgl menyimpan range tanggal
- produk menyimpan range unit terjual
- jual menyimpan range jumlah penjualan
- kriteria menyimpan gabungan syarat pengecualian dan rentang tanggal
- FILTER(jual;kriteria) menyaring data sesuai kriteria
- AVERAGE(…) menghitung nilai rata-rata berdasarkan data hasil filter
Keuntungan metode ini:
- Rumus jauh lebih rapi dan mudah dibaca
- Mudah diubah hanya dengan mengubah variabel
- Performa lebih baik untuk data besar
- Cocok untuk template analisis penjualan
Cara ini juga termasuk salah satu metode paling ideal untuk menghitung rata-rata dengan pengecualian, terutama untuk nol error dan standar dokumentasi rumus yang baik.
Kesimpulan – Mana Metode Terbaik?
| Versi Excel | Metode | Direkomendasikan Karena |
|---|---|---|
| Excel 2021 / 365 | FILTER + AVERAGE | Paling modern, dinamis, dan fleksibel |
| Semua versi Excel | AVERAGEIFS | Kompatibilitas terbaik dan mudah |
| Excel 2021 / 365 | LET + FILTER | Struktur profesional, rapi, cepat |
Ketiga metode di atas sama-sama mampu menghitung rata-rata dengan pengecualian berdasarkan rentang tanggal. Anda bisa memilih metode mana yang paling sesuai dengan versi Excel dan kebutuhan analisis Anda.
Download Template Gratis
Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Memahami Cara Menghitung Rata-rata dengan Pengecualian dan Rentang Tanggal di Excel ini. File Excel bisa didownload melalui tautan berikut:
Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!
—
0 Komentar