Cara Mencari Nilai Tertinggi dan Terendah dengan Kriteria di Microsoft Excel

Mencari nilai tertinggi dan terendah dalam set data merupakan tugas yang umum dilakukan dalam pengolahan data. Namun, terkadang kita perlu memperhitungkan kriteria tertentu, seperti saat ingin mencari nilai tertinggi atau terendah untuk suatu kelompok atau kategori data. Dalam artikel ini, kita akan membahas berbagai cara untuk mencari nilai tertinggi dan terendah tersebut menggunakan Microsoft Excel.

Mencari Nilai Tertinggi dan Terendah dengan Kriteria di Excel

Berikut adalah langkah-langkah praktis untuk mencari nilai tertinggi dan terendah dengan kriteria tertentu di Microsoft Excel

Mencari Nilai Tertinggi dengan Kriteria Menggunakan Fungsi MAX

Fungsi MAX dan MIN dalam Excel sudah lazim digunakan untuk mencari nilai tertinggi dan terendah dalam satu set data. Namun, ketika kita ingin mencari nilai tertinggi dan terendah dengan kriteria tertentu, kita perlu menggunakan pendekatan yang sedikit berbeda.

Sebagai contoh kita akan menyelesaikan hitung-hitungan tabel gaji seperti pada gambar di bawah ini:

Maka dengan contoh tabel di atas, untuk mencari nilai gaji maksimal untuk kriteria Salesman di sel F3 maka kita bisa gunakan array formula dari fungsi MAX yang dikombinasikan dengan fungsi IF sebagai berikut:

=MAX(IF($B$3:$B$18=$E3;$C$3:$C$18))

Dan seperti yang saya katakan di atas, ini adalah array formula atau formula array. Untuk itu agar formula ini berjalan dengan semestinya maka akhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter (jangan hanya enter). Jika berhasil, maka akan muncul otomatis simbol kurung kurawal ({}) yang mengapit rumusnya. Copy dan paste formula tersebut ke sel dibawahnya. Berikut hasilnya:

Penjelasan Array Formula

Mari kita bahas secara detail array formula di atas:

{=MAX(IF($B$3:$B$18=$E3;$C$3:$C$18))}

Ini adalah rumus array Excel yang dirancang untuk mencari nilai tertinggi dengan kriteria tertentu. Mari kita pecah rumus ini menjadi bagian-bagian yang lebih mudah dipahami:

1. MAX: Ini adalah fungsi Excel yang mengembalikan nilai maksimum dari satu set nilai.
2. IF: Ini adalah fungsi kondisional Excel yang mengevaluasi sebuah kondisi dan mengembalikan nilai berbeda tergantung pada apakah kondisi tersebut benar atau salah.
3. $B$3:$B$18: Ini adalah rentang sel pada kolom B di mana kita ingin memeriksa kriteria. Rentang ini dapat diubah sesuai dengan kebutuhan Anda.
4. $E3: Ini adalah sel di mana kriteria yang diinginkan diletakkan. Dalam rumus ini, kita menggunakan sel E3 sebagai referensi untuk kriteria.
5. $C$3:$C$18: Ini adalah rentang sel pada kolom C yang berisi nilai-nilai yang ingin kita evaluasi dan cari nilai maksimumnya.

Dengan menggabungkan semua ini dalam rumus array, kita melakukan hal berikut:

• Pertama, kita menggunakan fungsi IF untuk memeriksa apakah nilai dalam rentang B3:B18 sama dengan nilai yang ada di sel E3. Jika benar, maka fungsi IF akan mengembalikan nilai yang sesuai dari rentang C3:C18; jika tidak, fungsi IF akan mengembalikan nilai kosong ("").
• Setelah itu, kita menggunakan fungsi MAX untuk menemukan nilai maksimum dari nilai-nilai yang dihasilkan oleh fungsi IF.

Namun, perlu diperhatikan bahwa karena ini adalah rumus array, kita perlu menekan Ctrl + Shift + Enter setelah mengetikkan rumus tersebut, bukan hanya Enter. Ini penting karena kita menggunakan operasi array di dalam rumus, dan tanda kurung kurawal {} akan muncul di sekitar rumus setelah kita menekan Ctrl + Shift + Enter, menunjukkan bahwa ini adalah rumus array.

Mencari Nilai Terendah dengan Kriteria Menggunakan Fungsi MIN

Ketika kita ingin mencari nilai terendah dengan kriteria tertentu, kita dapat menggunakan pendekatan yang serupa dengan yang digunakan untuk mencari nilai tertinggi. Namun, kita akan mengganti fungsi MAX dengan fungsi MIN.

Maka berikut adalah array formula yang bisa digunakana di sel G3:

=MIN(IF($B$3:$B$18=$E3;$C$3:$C$18))

Dalam rumus ini, kita menggunakan fungsi IF untuk membuat sebuah array yang memeriksa kriteria yang ditentukan. Kemudian, kita gunakan fungsi MIN untuk menemukan nilai terendah dari array tersebut.

Jika sudah, seperti yang dilakukan pada array formula MAX di atas, agar formula ini berjalan dengan semestinya maka akhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter (jangan hanya enter). Jika berhasil, maka akan muncul otomatis simbol kurung kurawal ({}) yang mengapit rumusnya. Copy dan paste formula tersebut ke sel dibawahnya. Dan berikut adalah hasilnya:

Penjelasan Array Formula

Mari kita bahas secara detail rumus array Excel berikut ini:

{=MIN(IF($B$3:$B$18=$E3;$C$3:$C$18))}

Ini adalah rumus array Excel yang dirancang untuk mencari nilai terendah dengan kriteria tertentu. Mari kita pecah rumus ini menjadi bagian-bagian yang lebih mudah dipahami:

1. MIN: Ini adalah fungsi Excel yang mengembalikan nilai minimum dari satu set nilai.
2. IF: Ini adalah fungsi kondisional Excel yang mengevaluasi sebuah kondisi dan mengembalikan nilai berbeda tergantung pada apakah kondisi tersebut benar atau salah.
3. $B$3:$B$18: Ini adalah rentang sel pada kolom B di mana kita ingin memeriksa kriteria. Rentang ini dapat diubah sesuai dengan kebutuhan Anda.
4. $E3: Ini adalah sel di mana kriteria yang diinginkan diletakkan. Dalam rumus ini, kita menggunakan sel E3 sebagai referensi untuk kriteria.
5. $C$3:$C$18: Ini adalah rentang sel pada kolom C yang berisi nilai-nilai yang ingin kita evaluasi dan cari nilai minimumnya.

Dengan menggabungkan semua ini dalam rumus array, kita melakukan hal berikut:

• Pertama, kita menggunakan fungsi IF untuk memeriksa apakah nilai dalam rentang B3:B18 sama dengan nilai yang ada di sel E3. Jika benar, maka fungsi IF akan mengembalikan nilai yang sesuai dari rentang C3:C18; jika tidak, fungsi IF akan mengembalikan nilai kosong ("").
• Setelah itu, kita menggunakan fungsi MIN untuk menemukan nilai minimum dari nilai-nilai yang dihasilkan oleh fungsi IF.

Perlu diingat bahwa karena ini adalah rumus array, Anda harus menekan Ctrl + Shift + Enter setelah mengetikkan rumus tersebut, bukan hanya Enter. Ini penting karena kita menggunakan operasi array di dalam rumus, dan tanda kurung kurawal {} akan muncul di sekitar rumus setelah menekan Ctrl + Shift + Enter, menunjukkan bahwa ini adalah rumus array.

Mengurai Besaran Gaji Berdasarkan Posisinya

Dalam situasi di mana kita memiliki set data yang besar dan kompleks, sering kali kita ingin menyaring nilai-nilai tertentu dan mengurutkannya sesuai keinginan kita. Dalam contoh kita, kita akan mencari nilai terendah hingga tertinggi berdasarkan kriteria yang kita tentukan di sel F8 yang dalam hal ini adalah “Data Entry”.

Berikut adalah formula array Excel yang bisa kita gunakan:

{=IFERROR(INDEX(SMALL(IF($B$3:$B$18=$F$8;$C$3:$C$18);ROW($1:$20));MATCH(0;COUNTIF($E$9:E9;SMALL(IF($B$3:$B$18=$F$8;$C$3:$C$18);ROW($1:$20)));0));"")}

Dan karena ini juga array formula maka akhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter (jangan hanya enter). Jika berhasil, maka akan muncul otomatis simbol kurung kurawal ({}) yang mengapit rumusnya. Copy dan paste formula tersebut ke sel dibawahnya. Dan berikut adalah hasilnya:

Penjelasan Array Formula

Mari kita pecah rumus ini menjadi bagian-bagian yang lebih mudah dipahami:

1. IFERROR: Ini adalah fungsi yang mengevaluasi sebuah ekspresi dan mengembalikan nilai tertentu jika ekspresi tersebut menghasilkan kesalahan. Jika tidak, fungsi ini akan mengembalikan nilai yang dievaluasi oleh ekspresi.
2. INDEX: Fungsi ini mengembalikan nilai dari rentang sel berdasarkan nomor baris dan kolom tertentu dalam rentang tersebut.
3. SMALL: Fungsi ini mengembalikan nilai terkecil ke-n dalam satu set nilai.
4. IF: Fungsi ini mengevaluasi sebuah kondisi dan mengembalikan nilai berbeda tergantung pada apakah kondisi tersebut benar atau salah.
5. ROW($1:$20): Ini adalah bagian dari rumus yang menghasilkan array bilangan bulat dari 1 hingga 20. Ini digunakan untuk membuat array bilangan bulat yang akan digunakan dalam rumus.
6. MATCH: Fungsi ini mencari nilai tertentu dalam sebuah rentang dan mengembalikan posisi pertama kemunculan nilai tersebut.
7. COUNTIF: Fungsi ini menghitung jumlah sel dalam rentang yang memenuhi kriteria tertentu.

Rumus ini bekerja sebagai berikut:

• Pertama, kita menggunakan fungsi IF untuk memeriksa apakah nilai dalam rentang B3:B18 sama dengan nilai yang ada di sel F8. Jika benar, maka fungsi IF akan mengembalikan nilai yang sesuai dari rentang C3:C18; jika tidak, fungsi IF akan mengembalikan nilai kosong ("").
• Kemudian, kita menggunakan fungsi SMALL untuk menemukan nilai terkecil dari nilai-nilai yang telah dipilih sebelumnya.
• Fungsi INDEX digunakan untuk mengembalikan nilai-nilai tersebut dalam urutan yang sesuai.
• Selanjutnya, kita menggunakan fungsi MATCH dan COUNTIF untuk mengurutkan nilai-nilai yang telah dipilih dari terendah ke tertinggi.
• Terakhir, kita menggunakan IFERROR untuk menangani kesalahan yang mungkin terjadi dalam proses.

Menghitung Jumlah dengan Besaran Gaji yang Sama

Terakhir, kita akan menyelesaikan hitung-hitungan, berapa banyak sih orang dengan gaji yang sama yang ada di range E10:E18. Untuk menyelesaikan hal tersebut kita bisa menggunakan fungsi COUNTIFS di Excel. Kenapa COUNTIFS karena kita akan menghitung data dengan lebih dari satu kriteria.

Di sel F10 ketikkan rumus COUNTIFS berikut:

=COUNTIFS($C$3:$C$18;E10;$B$3:$B$18;$F$8)

Jika sudah, copy dan paste rumus tersebut ke sel yang ada di bawahnya. Dan berikut adalah hasilnya:

Penjelasan Rumus

Mari kita bahas secara rinci bagian-bagian dari rumus ini:

1. COUNTIFS: Ini adalah fungsi Excel yang digunakan untuk menghitung jumlah sel dalam rentang yang memenuhi beberapa kriteria tertentu.
2. $C$3:$C$18: Ini adalah rentang sel pada kolom C di mana kita ingin mencari nilai yang cocok dengan kriteria pertama.
3. E10: Ini adalah kriteria pertama yang kita berikan kepada fungsi COUNTIFS. Fungsi ini akan menghitung berapa kali nilai di sel tersebut cocok dengan nilai dalam rentang $C$3:$C$18.
4. $B$3:$B$18: Ini adalah rentang sel pada kolom B di mana kita ingin mencari nilai yang cocok dengan kriteria kedua.
5. $F$8: Ini adalah kriteria kedua yang kita berikan kepada fungsi COUNTIFS. Fungsi ini akan menghitung berapa kali nilai di sel tersebut cocok dengan nilai dalam rentang $B$3:$B$18.

Dengan menggunakan rumus COUNTIFS seperti ini, Excel akan menghitung jumlah sel dalam rentang yang memenuhi kedua kriteria yang Anda tentukan. Dalam kasus ini, rumus akan menghitung berapa kali nilai dalam kolom C cocok dengan nilai di sel E10 dan sekaligus nilai dalam kolom B cocok dengan nilai di sel F8.

Penutup

Dengan menggunakan berbagai metode dan fungsi yang ada dalam Excel, kita dapat dengan mudah mencari nilai tertinggi dan terendah dalam set data dengan kriteria tertentu. Semoga artikel mencari nilai tertinggi dan terendah dengan kriteria tertentu ini dapat membantu Anda dalam melakukan analisis data yang lebih mendalam dan efisien menggunakan Excel.

Dan terakhir, bagi yang membutuhkan contoh file Excel Cara Mencari Nilai Tertinggi dan Terendah dengan Kriteria di Microsoft Excel di atas, file tersebut bisa diunduh melalui tautan berikut:

Atau jika ingin menonton cara mencari nilai tertinggi dan terendah yang versi videonya, bisa meluncur ke channel Youtube Depot Excel di sini.