Fungsi MAX mengembalikan nilai tertinggi dari sekumpulan nilai.
Untuk mencari nilai tertinggi dengan kriteria di Excel, kita menggunakan kombinasi antara MAX dan IF, walau pun agak panjang.
Rumus MAXIFS Excel adalah cara lebih cepat untuk mencari nilai tertinggi dengan kriteria tertentu.
Setiap screenshot pada artikel ini bisa ditemui di file berikut.
File: rumus-maxifs-excel.xlsx.
Apa Itu Fungsi MAXIFS pada Excel?
Fungsi MAXIFS pada Excel adalah fungsi untuk mencari nilai tertinggi dari sekumpulan nilai berdasarkan satu atau lebih kriteria.
MAXIFS hanya tersedia di Excel 2019 dan 365.
Pengguna Excel di bawah kedua versi tersebut, seperti Excel 2010 atau Excel 2013 atau bahkan lebih awal, tidak dapat menggunakannya.
Sintaks
MAXIFS(max_range; criteria_range1; criteria1; [criteria_range2; criteria2]; …)
- max_range (wajib) adalah range yang akan dicari nilai tertingginya.
- criteria_range1 (wajib) adalah range dengan nilai-nilai yang akan dievaluasi. Isi criteria_range1 berkesesuaian dengan nilai-nilai di max_range.
- criteria1 (wajib) adalah kriteria yang digunakan untuk menyaring nilai-nilai di criteria_range.
- criteria_range2 sampai criteria_range127 (opsional) adalah range dengan informasi tambahan yang berkesuaian dengan criteria_range1.
- criteria1 sampai criteria127 (opsional) adalah kriteria tambahan yang digunakan untuk menyaring nilai-nilai di criteria_range2 sampai criteria_range127.
Saat menggunakan rumus MAXIFS, dimensi max_range, criteria_range1, criteria_range2, dan seterusnya harus sama besar. Bila max_range terdiri dari satu kolom dan lima baris, range lainnya juga harus sama.
Cara Menggunakan Rumus MAXIFS Excel
Rumus MAXIFS: Dasar
Seperti terlihat dari sintaks, bentuk paling sederhana rumus MAXIFS hanya menyertakan max_range, criteria_range1, dan criteria1.
=MAXIFS(B2:B11; A2:A11; E1)
Rumus di atas mencari penjualan terbesar Catherine.
Kolom Total Penjualan terletak pada range B2:B11. Ini adalah max_range.
criteria_range1, yaitu A2:A11, adalah kolom Penjual, yaitu kolom di mana nama-nama penjual berada.
criteria1 adalah E1.
Dengan referensi E1, MAXIFS akan mencari Catherine di range A2:A11. Excel akan memberikan kandidat di max_range yang sesuai, yaitu B2, B6, B8, dan B10. Setelahnya, MAXIFS memilih yang terbesar di antara keempat nilai tersebut, yaitu 5,411,000,000, pada cell B2.
Sebagai alternatif, rumus bisa dituliskan langsung menggunakan nilai yang diperbandingkan.
=MAXIFS(B2:B11; A2:A11; "Catherine")
Berhubung Catherine adalah nilai teks, petik dua perlu ditambahkan di depan dan belakangnya.
Catherine atau E1 diperbandingkan dengan range A2:A11 menggunakan operator logika sama dengan (=), yaitu operator logika default untuk kriteria.
Excel akan mengasumsikan bahwa operasi adalah sama dengan bila operator tidak dituliskan secara spesifik.
Bila mau menuliskan operator, maka rumusnya akan seperti di bawah ini.
=MAXIFS(B2:B11; A2:A11; "="&E1)
=MAXIFS(B2:B11; A2:A11; "=Catherine")
Penggunaan tanda ampersand (&) bertujuan untuk memisahkan operator dengan referensi cell.
Kriteria di fungsi MAXIFS tidak case sensitive. Artinya, MAXIFS menganggap catherine, Catherine, catherINE, dan variasi lainnya sama saja.
Rumus MAXIFS dengan Operator Logika Pembanding
Excel mengenal beberapa operator logika untuk pembanding antara satu nilai dengan nilai lain, yaitu:
- Sama dengan (=).
- Tidak sama dengan (<>).
- Lebih besar dari (>).
- Lebih besar dari atau sama dengan (>=).
- Lebih kecil dari (<).
- Lebih kecil dari atau sama dengan (<=).
Semua operator ini bisa digunakan di dalam kriteria MAXIFS.
| Operator | Rumus | Keterangan |
|---|---|---|
| = | =MAXIFS(C2:C11; A2:A11; "=Catherine") | Cari penjualan terbesar dari Catherine. |
| <> | =MAXIFS(C2:C11; A2:A11; "<>Catherine") | Cari penjualan terbesar yang bukan oleh Catherine. |
| > | =MAXIFS(C2:C11; C2:C11; ">5000000000") | Cari penjualan terbesar di atas 5,000,000,000. |
| < | =MAXIFS(C2:C11; C2:C11; "<5000000000") | Cari penjualan terbesar di bawah 5,000,0000. |
| = | =MAXIFS(C2:C11; B2:B11; "=1") | Mengembalikan nilai terbesar dari penjualan satu unit. |
| >= | =MAXIFS(C2:C11; B2:B11; ">=2") | Mengembalikan nilai terbesar dari penjualan dua unit atau lebih. |
| <= | =MAXIFS(C2:C11; B2:B11; "<=2") | Mengembalikan nilai terbesar dari maksimal dua unit penjualan. |
Rumus MAXIFS untuk Kriteria Range
Kriteria range atau rentang adalah kriteria yang mencari nilai-nilai yang berada di antara dua nilai atau lebih.
Contoh: Rentang usia 13 sampai 18 tahun. Usia 16 masuk dalam rentang tersebut, sementara 12 dan 19 tidak.
Saat menggunakan fungsi MAXIFS pada Excel, rentang biasanya berupa angka atau variasi dari angka, seperti tanggal atau waktu.
Rentang di antara dua nilai sebenarnya terdiri dari dua kriteria, yaitu nilai-nilai yang lebih besar dari batas bawah DAN yang lebih kecil dari batas atas.
MAXIFS mendukung penggunaan dua, tiga, empat kriteria atau lebih (sampai maksimal 127).
Untuk membuat rentang, gunakan operator logika pembanding.
=MAXIFS(B2:B11; B2:B11; ">2000000000"; B2:B11; "<=3000000000")
Rumus di atas mencari penjualan (B2:B11) terbesar yang berada di antara 2,000,000,000 sampai 3,000,000,000.
=MAXIFS(B2:B11; A2:A11; ">=2024-01-01"; A2:A11; "<=2024-01-31")
Rumus di atas mencari penjualan terbesar yang berada pada tanggal-tanggal mulai dari 1 Januari 2024 sampai 31 Januari 2024.
Rumus MAXIFS untuk Tanggal Terbesar
Tanggal di Excel adalah variasi angka.
Excel menyimpan tanggal dalam bentuk angka. Sebagai contoh, nilai angka dari 1 Januari 2024 adalah 45292.
Karenanya, menggunakan MAXIFS untuk mencari tanggal terbesar dengan kriteria tertentu bisa dilakukan.
Rumus berikut akan mengembalikan tanggal kapan terakhir kali Wahyu jualan berdasarkan data yang ada.
=MAXIFS(A2:A11; B2:B11; "Wahyu")
MAXIFS akan mengembalikan tanggal dalam bentuk angka. Pada gambar di atas, nilai yang dikembalikan MAXIFS adalah 45377.
Kita perlu format angka tersebut dengan format tanggal agar Excel menampilkannya sebagai 26/03/2024 atau format tanggal lain yang berlaku di mana spreadsheet dibuka.
Fungsi MAXIFS dengan Kriteria Teks
Contoh-contoh yang sudah ada di atas menggunakan kriteria teks dalam bentuk nilai teks secara utuh. Contoh: =MAXIFS(A2:A11; B2:B11; "Wahyu").
Untuk data sangat besar atau memiliki teks panjang, mencari nilai teks utuh bisa merepotkan.
Dengan MAXIFS, kita dapat menggunakan wild card untuk mencari teks hanya menggunakan bagian tertentu dari badan teks.
- Tanda asterisk (*) mewakili satu atau lebih karakter yang kita abaikan.
- Tanda tanya (?) mewakili satu karakter apa pun yang kita abaikan.
- Tanda tilde (~) menghilangkan kemampuan satu tanda asterisk atau tanda tanya yang ditulis segera setelahnya.
Catatan: Cell yang kami tebalkan adalah cell yang sesuai dengan angka terbesar di range B2:B11 dari semua kandidat yang tersaring.
| Rumus | Penjelasan | Kandidat Cell |
|---|---|---|
=MAXIFS(B2:B11; A2:A11; "Jack") | Mencari klien dengan nama Jack. | Tidak ada. |
=MAXIFS(B2:B11; A2:A11; "Jack*") | Mencari klien dengan nama yang berawalan Jack. | A3, A4, A5, A6, A7, A9, A10, A11. |
=MAXIFS(B2:B11; A2:A11; "*Jack") | Mencari klien dengan nama yang diakhiri oleh Jack. | A2. |
=MAXIFS(B2:B11; A2:A11; "jack*n") | Klien dengan nama yang diawali oleh Jack dan diakhiri oleh huruf n. | A4, A5, A6, A7, A9. |
=MAXIFS(B2:B11; A2:A11; "Jack?") | Klien dengan nama yang hanya memiliki satu karakter setelah Jack. | A10, A11 |
=MAXIFS(B2:B11; A2:A11; "?Jack") | Klien dengan nama hanya satu karakter yang mendahului Jack. | Tidak ada. |
=MAXIFS(B2:B11; A2:A11; "jack?n") | Nama klien yang memiliki satu karakter di antara jack dan n. | A4. |
=MAXIFS(B2:B11; A2:A11; "~*") | Nama yang hanya terdiri dari satu asterisk. | Tidak ada. |
=MAXIFS(B2:B11; A2:A11; "*~**") | Nama yang memiliki asterisk. | A6, A8, A11. |
=MAXIFS(B2:B11; A2:A11; "*~?*") | Nama yang memiliki tanda tanya. | A3, A8. |
=MAXIFS(B2:B11; A2:A11; "*~?~**") | Nama yang memiliki tanda tanya dan asterisk tertulis secara berurut. | A8. |
Rumus MAXIFS dengan Kriteria Or
Kami ingin mencari penjualan terbesar di Januari 2024 dan Maret 2024.
=MAXIFS(B2:B11; A2:A11; ">="&E2; A2:A11; "<="&E3; A2:A11; ">="&E6; A2:A11; "<="&E7)
Kami menulis rumus di atas di mana dua kriteria pertama menyaring tanggal-tanggal Januari dan dua terakhir menyaring tanggal-tanggal Maret.
Tapi, rumus di atas mengembalikan nilai nol. Ingat bagaimana ekspresi logika And bekerja.
Kita ingin kedua set kriteria tanggal masing-masing bernilai benar tanpa keduanya saling berhubungan. Untuk itu kita perlu menggunakan Or.
Rumus MAXIFS dengan ekspresi logika Or dimungkinkan dengan menggabungkan satu rumus MAXIFS dengan rumus MAXIFS lain.
=MAX(MAXIFS(B2:B11; A2:A11; ">="&E2; A2:A11; "<="&E3); MAXIFS(B2:B11; A2:A11; ">="&E6; A2:A11; "<="&E7))
MAXIFS pertama mengambil nilai penjualan terbesar di bulan Januari 2024. MAXIFS kedua di bulan Maret 2024.
Kedua hasilnya dievaluasi oleh fungsi MAX.
Rumus MAXIFS Bertingkat
Rumus MAXIFS bertingkat adalah rumus dengan satu fungsi MAXIFS yang berada di dalam fungsi MAXIFS lain.
=MAXIFS(B2:B11; B2:B11; "<"&MAXIFS(B2:B11; A2:A11; "*"))
Rumus di atas mencari penjualan kedua terbesar.
Rumus tersarang, yaitu MAXIFS(B2:B11; A2:A11; "*"), mencari penjualan terbesar untuk penjual mana pun (asterisk mewakili karakter-karakter apa pun yang ada di nama penjual).
Hasil MAXIFS tersarang kemudian dibandingkan dengan B2:B11 pada MAXIFS utama dengan operator lebih kecil (<).
Kombinasi MAXIFS dan VLOOKUP
Rumus kombinasi VLOOKUP dan MAXIFS berikut mencari tanggal kapan nilai penjualan terbesar terjadi.
=VLOOKUP(MAXIFS(B2:B11; A2:A11; "*"); B2:C11; 2; FALSE)
MAXIFS(B2:B11; A2:A11; "*") mencari nilai penjualan terbesar. Hasilnya diambil oleh VLOOKUP sebagai referensi ke tabel B2:C11 untuk mencari tanggal yang relevan dengan hasil bersangkutan.
