Rumus Excel MAX-IF dan MIN-IF, Mencari Nilai Terbesar dan Terkecil Dengan Kriteria

Rumus Excel MAX-IF dan MIN-IF, Mencari Nilai Terbesar dan Terkecil Dengan Kriteria

Rumus Excel Max-If dan Min-If,Nilai Terbesar dan Terkecil Dengan Kriteria

Saya kira sudah cukup lazim diketahui bahwa untuk mendapatkan nilai tertinggi dan terendah fungsi excel yang digunakan adalah fungsi MAX dan MIN. Alternatif lain bisa juga menggunakan fungsi LARGE dan SMALL dengan cara mengatur argumen atau parameter urutan ke-1 (pertama).

Pada kasus-kasus tertentu terkadang kita ingin mendapatkan nilai terbesar (paling tinggi) dan terkecil (paling rendah) namun dengan kriteria tertentu. Misalnya pada saat mengolah data nilai siswa kita ingin mendapatkan nilai tertinggi untuk siswa laki-laki dan juga ingin mencari nilai terkecil untuk siswa perempuan saja.

Mencari Nilai Terbesar Dengan Banyak Kriteria

Nah, dalam kasus semacam ini bagaimana solusinya?


MENCARI NILAI TERTINGGI DENGAN KRITERIA


Ada beberapa rumus excel yang bisa kita pakai untuk mendapatkan nilai terbesar dengan kriteria tertentu seperti pada contoh kasus ini.

Silahkan baca dan praktekkan tutorial ini secara perlahan-lahan, jangan tergesa-gesa agar anda tidak tersesat di hutan belantara kebingungan.

Agar tidak mengulang-ulang pembahasan pada tutorial ini tidak akan saya bahas lagi mengenai cara dasar penggunaan masing-masing fungsi exel yang digunakan.

Silahkan baca terlebih dahulu tutorial detail penggunaan masing-masing fungsi excel yang digunakan pada pembahasan rumus excel kali ini:

  1. Fungsi Excel MIN dan MAX
  2. Fungsi Excel LARGE dan SMALL
  3. Fungsi IF Excel
  4. Fungsi Sumproduct Excel

Rumus Excel MAX Dengan Kriteria


Untuk mendapatkan nilai tertinggi atau nilai terbesar dengan kriteria tertentu bisa kita lakukan dengan cara menyusun sebuah array sebagai argumen untuk fungsi MAX.

Rumus Max Dengan Kriteria

Rumus Array MAX yang bisa kita gunakan untuk mendapatkan nilai tertinggi untuk siswa laki-laki dengan data excel di atas adalah sebagai berikut:

=MAX((C2:C11="L")*(D2:D11))

Rumus ini harus di akhiri dengan Menekan tombol Ctrl+Shift+Enter yang menandakan bahwa rumus yang kita gunakan adalah sebuah rumus Array (CSE) sehingga rumus akan di apit oleh {....} atau curly bracket.

Pada rumus diatas masing-masing nilai sel pada range C2:C11 akan dibandingkan dengan teks L. Jika berisi teks L maka sel tersebut akan bernilai TRUE dan sebaliknya jika tidak berisi L akan bernilai FALSE. Sehingga pada rumus ini (C2:C11="L") akan menghasilkan Array berikut:

{FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}

Array ini kemudian akan dikalikan dengan masing-masing nilai sel pada range (D2:D11). Untuk nilai False hasil perkalian adalah 0 sedangkan untuk True hasil perkalian adalah nilai sel D2:D11 yang bersesuaian.

Selanjutnya akan didapatkan array baru berikut:

{0;0;82;0;61;67;0;0;91;65}

Array inilah yang kemudian menjadi nilai argument yang akan dicari nilai Maximumnya oleh fungsi MAX excel. Dan akhirnya didapatkan nilai 91 sebagai nilai tertinggi atau terbesar untuk siswa laki-laki.

Bagaimana menyusun logika Array ini tentunya akan sangat tergantung pada kretaifitas anda dalam menyusunnya.

Sebagi informasi anda tidak dapat menggunakan fungsi Logika NAD dan OR atau sejenisnya pada array formula semacam ini. Nah solusinya silahkan anda pelajari pada halaman berikut: Cara Menggunakan Fungsi Logika (AND-OR) Pada Array Formula

Apabila cara ini masih terlalu sulit anda bisa juga menggunakan fungsi Gabungan Rumus MAX dan Rumus IF Excel.

Nah, kalau cara menggunakan rumus excel IF tentunya sudah mahir kan?

Sebelum melanjutkan cara kedua pastikan anda sudah cukup menguasai cara menggunakan fungsi MAX dan juga fungsi IF excel. Ubek-ubek aja blog ini untuk tutorialnya.

Jika sudah, Sekarang kita lanjut ke bagian selanjutnya.

Nilai Tertinggi Dengan Rumus Excel Gabungan MAX-IF


Untuk data excel seperti pada contoh di atas, rumus array gabungan Max dan If yang digunakan untuk mendapatkan nilai tertinggi pada siswa laki-laki adalah seperti berikut:

Gabungan Rumus Max dan IF Excel

=MAX(IF(C2:C11="L";D2:D11))

Sedangkan untuk nilai tertinggi atau terbesar perempuan rumus excelnya sebagai berikut:

=MAX(IF(C2:C11="P";D2:D11))

Untuk mendapatkan hasil yang tepat, rumus gabungan Max dan If ini harus kita perlakukan sebagai Array formula seperti sebelumnya, dengan cara mengakhiri penulisan rumus dengan menekan tombol Ctrl+Shift+Enter secara bersama-sama.

Penjelasan lebih lanjut mengenai Array formula bisa anda baca pada halaman berikut : Rumus Array Pada Microsoft Excel

Konstanta teks "L" atau "P" bisa juga anda rubah menjadi referensi sel yang berisi teks L atau P.

Lalu bagaimana jika kita menghendaki nilai terbesar ke-2 atau ke-3 untuk siswa laki-laki?

Nah, tentunya untuk kasus ini fungsi MAX sudah tidak relevan lagi. Lalu harus menggunakan fungsi apa? Yup, benar jawabannya adalah fungsi Large atau Small.

Nilai Tertinggi Dengan Rumus Excel Gabungan LARGE-IF


Selain menggunakan rumus array gabungan dari fungsi MAX dan IF seperti di atas, kita bisa juga menggunakan fungsi Large. Rumus ini akan cukup berguna jika selain mendapatkan nilai terbesar atau tertinggi anda juga menginginkan untuk mendapatkan nilai terbesar dalam urutan tertentu lainnya. Misalnya anda ingin mendapatkan nilai 3 besar untuk siswa laki-laki.

Untuk mendapatkan nilai terbesar pertama untuk siswa laki-laki dengan fungsi LARGE dan IF rumus excel yang di pakai adalah sebagai berikut:

Gabungan Rumus Large dan IF Excel

=LARGE(IF(C2:C11="L";D2:D11);1)

Seperti rumus sebelumnya, rumus ini juga merupakan Array formula, jadi untuk mendapatkan hasil yang tepat anda perlu mengakhiri penulisan rumus ini dengan menekan tombol Ctrl+Shift+Enter secara bersama-sama.

Untuk mendapatkan nilai siswa laki-laki tertinggi ke-2 anda cukup merubah angka 1 pada rumus tersebut dengan angka 2 atau angka lainnya sesuai kebutuhan.

Waduh, ini array formula semua ya? yang tidak harus CSE(Ctrl+Shift+Enter) tidak ada ya? Untuk tahu jawabannya kita lanjut ke bagian selanjutnya.

Nilai Tertinggi Dengan Rumus Excel Gabungan SUMPRODUCT-MAX/LARGE


Kalau anda belum terbiasa dengan Array Formula ada alternatif lain yang bisa anda coba untuk emndapatkan nilai terbesar atau tertinggi dengan kriteria ini. Yakni, dengan menggunakan gabungan fungsi Sumproduct dan MAX atau gabungan Sumproduct dan Large.

Mencari Nilai Tertingi dan Trendah Dengan Banyak Kriteria

Untuk gabungan fungsi Sumproduct dan Max contohnya adalah sebagai berikut:

=SUMPRODUCT(MAX((C2:C11="L")*(D2:D11)))

Rumus diatas digunakan untuk mengetahui nilai tertinggi siswa laki-laki. Untuk siswa perempuan anda cukup merubah teks "L" dengan "P".

Cara kerja rumus ini sebenarnya persis seperti rumus-rumus excel di atas. Namun anda tidak perlu melakukan Ctrl+Shift+Enter, jadi cukup diperlakukan seperti rumus biasa.

Untuk fungsi Large contoh rumus gabungannya adalah sebegai berikut:

=SUMPRODUCT(LARGE((C2:C11="L")*(D2:D11);1))

MENCARI NILAI TERENDAH DENGAN KRITERIA


Cara mendapatkan nilai terendah sama persis dengan cara-cara untuk mendapatkan nilai tertinggi atau terbesar. Anda hanya cukup menyesuiakan fungsi MAX dengan Fungsi MIN dan mengganti penggunaan fungsi LARGE dengan fungsi SMALL. Jadi saya rasa tidak perlu saya contohkan lagi.

Jika memang masih kesulitan silahkan sampaikan di kolom komentar yang tersedia.

0 komentar