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

Saya kira sudah cukup lazim diketahui bahwa untuk mendapat nilai tertinggi dan terendah fungsi excel yang dipakai yaitu fungsi MAX dan MIN. Alternatif lain sanggup juga memakai fungsi LARGE dan SMALL dengan cara mengatur argumen atau parameter urutan ke-1 (pertama).

Pada kasus-kasus tertentu terkadang kita ingin mendapat nilai terbesar (paling tinggi) dan terkecil (paling rendah) namun dengan kriteria tertentu. Misalnya pada dikala mengolah data nilai siswa kita ingin mendapat nilai tertinggi untuk siswa pria dan juga ingin mencari nilai terkecil untuk siswa wanita saja.



Nah, dalam masalah semacam ini bagaimana solusinya?

Rumus Mencari Nilai Tertinggi Dengan Kriteria

Ada beberapa rumus excel yang sanggup kita pakai untuk mendapat nilai terbesar dengan kriteria tertentu menyerupai pada teladan masalah ini.

Silahkan baca dan praktekkan tutorial ini secara perlahan-lahan, jangan tergesa-gesa biar 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 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 mendapat nilai tertinggi atau nilai terbesar dengan kriteria tertentu sanggup kita lakukan dengan cara menyusun sebuah array sebagai argumen untuk fungsi MAX.



Rumus Array MAX yang sanggup kita gunakan untuk mendapat nilai tertinggi untuk siswa pria 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 membuktikan bahwa rumus yang kita gunakan yaitu 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 bila 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 yaitu 0 sedangkan untuk True hasil perkalian yaitu nilai sel D2:D11 yang bersesuaian.

Selanjutnya akan didapatkan array gres 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 balasannya 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 info anda tidak sanggup memakai 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 sanggup juga memakai fungsi Gabungan Rumus MAX dan Rumus IF Excel.

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

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

Jika sudah, Sekarang kita lanjut ke bab selanjutnya.

Nilai Tertinggi Dengan Rumus Excel Gabungan MAX-IF

Untuk data excel menyerupai pada teladan di atas, rumus array adonan Max dan If yang dipakai untuk mendapat nilai tertinggi pada siswa pria yaitu menyerupai 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 mendapat hasil yang tepat, rumus adonan Max dan If ini harus kita perlakukan sebagai Array formula menyerupai sebelumnya, dengan cara mengakhiri penulisan rumus dengan menekan Ctrl + Shift + Enter secara bersama-sama.

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

Konstanta teks "L" atau "P" sanggup juga anda rubah menjadi acuan sel yang berisi teks L atau P.

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

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

Nilai Tertinggi Dengan Rumus Excel Gabungan LARGE-IF

Selain memakai rumus array adonan dari fungsi MAX dan IF menyerupai di atas, kita sanggup juga memakai fungsi Large. Rumus ini akan cukup berkhasiat bila selain mendapat nilai terbesar atau tertinggi anda juga menginginkan untuk mendapat nilai terbesar dalam urutan tertentu lainnya. Misalnya anda ingin mendapat nilai 3 besar untuk siswa laki-laki.

Untuk mendapat 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, jadi untuk mendapat hasil yang sempurna anda perlu mengakhiri penulisan rumus ini dengan menekan Ctrl + Shift + Enter secara bersama-sama.

Untuk mendapat 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 bab selanjutnya.

Nilai Tertinggi Dengan Rumus Excel Gabungan SUMPRODUCT-MAX/LARGE

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



Untuk adonan fungsi Sumproduct dan Max misalnya yaitu sebagai berikut:

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

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

Cara kerja rumus ini bahu-membahu persis menyerupai rumus-rumus excel di atas. Namun anda tidak perlu melaksanakan Ctrl + Shift + Enter, jadi cukup diperlakukan menyerupai rumus biasa.

Untuk fungsi Large teladan rumus gabungannya yaitu sebegai berikut:

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

Rumus Mencari Nilai terendah Dengan Kriteria

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

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

Location:

Share :