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

Rumus MAX-IF & MIN-IF - Saya kira sdh cukup lazim diketahui bahwa untuk mendapatkan nilai tertinggi & terendah fungsi excel yg digunakan adalah fungsi MAX & MIN.

Alternatif lain bisa juga menggunakan fungsi LARGE & SMALL dengan cara mengatur argumen atau parameter urutan ke-1 (pertama).

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



Nah, dlm kasus semacam ini bagaimana solusinya?

Rumus Mencari Nilai Tertinggi Dengan Kriteria

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

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

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

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

1. Fungsi Excel MIN & MAX
2. Fungsi Excel LARGE & 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 sbgai argumen untuk fungsi MAX.



Rumus Array MAX yg bisa kita gunakan untuk mendapatkan nilai tertinggi untuk siswa laki-laki dengan data excel di atas adalah sbgai brkut:

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

Rumus ini harus di akhiri dengan Menekan Ctrl + Shift + Enter yg menandakan bahwa rumus yg 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 & sebaliknya jika tidak berisi L akan bernilai FALSE. Sehingga pada rumus ini (C2:C11="L") akan menghasilkan Array brkut:

{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 se&gkan untuk True hasil perkalian adalah nilai sel D2:D11 yg bersesuaian.

Selanjutnya akan didapatkan array baru brkut:

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

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

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

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

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

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

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

Jika sdh, Sekarang kita lanjut ke bagian selanjutnya.

Nilai Tertinggi Dengan Rumus Excel Gabungan MAX-IF

Untuk data excel sprti pada contoh di atas, rumus array gabungan Max & If yg digunakan untuk mendapatkan nilai tertinggi pada siswa laki-laki adalah sprti brkut:

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

Se&gkan untuk nilai tertinggi atau terbesar perempuan rumus excelnya sbgai brkut:

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

Untuk mendapatkan hasil yg tepat, rumus gabungan Max & If ini harus kita perlakukan sbgai Array formula sprti sebelumnya, dengan cara mengakhiri penulisan rumus dengan menekan Ctrl + Shift + Enter secara bersama-sama.

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

Konstanta teks "L" atau "P" bisa juga anda rubah menjadi referensi sel yg 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 sdh 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 & IF sprti 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 dlm 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 & IF rumus excel yg di pakai adalah sbgai brkut:

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

Seperti rumus sebelumnya, rumus ini juga adalah Array formula, jadi untuk mendapatkan hasil yg tepat anda perlu mengakhiri penulisan rumus ini dengan menekan 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? yg 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 yg bisa anda coba untuk emndapatkan nilai terbesar atau tertinggi dengan kriteria ini. Yakni, dengan menggunakan gabungan fungsi Sumproduct & MAX atau gabungan Sumproduct & Large.



Untuk gabungan fungsi Sumproduct & Max contohnya adalah sbgai brkut:

=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 sprti rumus-rumus excel di atas. Namun anda tidak perlu melakukan Ctrl + Shift + Enter, jadi cukup diperlakukan sprti rumus biasa.

Untuk fungsi Large contoh rumus gabungannya adalah sebegai brkut:

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

Rumus 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 & mengganti penggunaan fungsi LARGE dengan fungsi SMALL. Jadi saya rasa tidak perlu saya contohkan lagi.

Jika memang masih kesulitan slahkan sampaikan di kolom komentar yg tersedia.
Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: