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

Rumus MAX-IF & MIN-IF - admin kira sudah cukup lazim diketahui bahwa untuk mendapatkan nilai tertinggi dan terendah fungsi excel yang digunakan adalah fungsi MAX dan MIN.

Alternatif lain mampu juga menggunakan fungsi LARGE dan SMALL dengan Tutorial mengatur argumen atau parameter urutan ke-1 (pertama).

Pada perkara-kasus tertentu terkadang kita ingin menerima nilai terbesar (paling tinggi) dan terkecil (paling rendah) namun dengan kriteria tertentu. contohnya pada dikala mengolah data nilai siswa kita ingin mendapatkan nilai tertinggi untuk siswa laki-laki dan juga ingin mencari nilai terkecil untuk siswa wanita saja.



Nah, dalam kasus semacam ini bagaimana solusinya?

Rumus Mencari Nilai Tertinggi Dengan Kriteria

Ada beberapa rumus excel yang mampu kita pakai untuk menerima nilai terbesar dengan kriteria tertentu seperti pada teladan kasus ini.

Silahkan baca dan praktekkan tutorial ini secara perlahan-lahan, jangan tergesa-gesa biar 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 yang dipakai.

Silahkan baca terlebih dahulu tutorial detail penggunaan masing-masing fungsi excel yang dipakai 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 Array MAX yang bisa kita gunakan untuk menerima nilai tertinggi untuk siswa laki-laki dengan data excel di atas yaitu sebagai berikut:

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

Rumus ini harus di akhiri dengan Menekan Ctrl + Shift + Enter yang mengambarkan 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. bila berisi teks L maka sel tersebut akan bernilai TRUE dan sebaliknya jikalau 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 ialah 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 jadinya didapatkan nilai 91 sebagai nilai tertinggi atau terbesar untuk siswa pria.

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

Sebagi berita anda tidak mampu memakai fungsi nalar AND dan OR atau sejenisnya pada array formula semacam ini. Nah solusinya silahkan anda pelajari pada halaman berikut: Cara menggunakan Fungsi nalar (AND-OR) Pada Array Formula

Apabila Tutorial ini masih terlalu sulit anda mampu juga menggunakan fungsi adonan Rumus MAX dan Rumus IF Excel.

Nah, kalau cara memakai rumus excel IF tentunya sudah jago kan?

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

Jika sudah, kini kita lanjut ke bagian selanjutnya.

Nilai Tertinggi Dengan Rumus Excel adonan MAX-IF

Untuk data excel seakan-akan pada contoh di atas, rumus array campuran Max dan If yang digunakan untuk menerima nilai tertinggi pada siswa laki-laki adalah seolah-olah berikut:

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

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

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

Untuk mendapatkan hasil yang sempurna, rumus campuran Max dan If ini harus kita perlakukan sebagai Array formula seolah-olah sebelumnya, dengan Tutorial mengakhiri penulisan rumus dengan menekan 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" mampu juga anda rubah menjadi referensi sel yang berisi teks L atau P.

Lalu bagaimana jikalau kita menghendaki nilai terbesar ke-2 atau ke-3 untuk siswa pria?

Nah, tentunya untuk perkara ini fungsi MAX sudah tidak relevan lagi. kemudian harus memakai fungsi apa? Yup, benar jawabannya yaitu fungsi Large atau Small.

Nilai Tertinggi Dengan Rumus Excel campuran LARGE-IF

Selain menggunakan rumus array adonan dari fungsi MAX dan IF seakan-akan di atas, kita bisa juga menggunakan fungsi Large. Rumus ini akan cukup memiliki kegunaan kalau selain menerima nilai terbesar atau tertinggi anda juga menginginkan untuk menerima nilai terbesar dalam urutan tertentu lainnya. misalnya anda ingin mendapatkan nilai 3 besar untuk siswa laki-laki.

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

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

Seperti rumus sebelumnya, rumus ini juga merupakan Array formula, Kaprikornus untuk menerima hasil yang sempurna anda perlu mengakhiri penulisan rumus ini dengan menekan Ctrl + Shift + Enter secara bantu-membantu.

Untuk menerima nilai siswa pria 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 pecahan selanjutnya.

Nilai Tertinggi Dengan Rumus Excel adonan 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. ialah, dengan menggunakan adonan fungsi Sumproduct dan MAX atau gabungan Sumproduct dan Large.



Untuk adonan fungsi Sumproduct dan Max polanya ialah sebagai berikut:

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

Rumus diatas digunakan untuk mengetahui nilai tertinggi siswa pria. Untuk siswa wanita anda cukup merubah teks "L" dengan "P".

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

Untuk fungsi Large pola rumus adonannya yaitu sebegai berikut:

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

Rumus Mencari Nilai terendah Dengan Kriteria

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

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