Formula umum
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Ringkasan
Untuk mendapatkan nilai maksimum dalam kumpulan data yang didasarkan pada lebih dari satu kriteria, Anda bisa menggunakan rumus array berdasarkan fungsi MAX dan IF. Pada contoh yang ditampilkan, rumus di I6 adalah:
(=MAX(IF(color=G6,IF(item=H6,price))))
Dengan warna "merah" dan item "topi" hasilnya $ 11.00
Catatan: Ini adalah rumus array dan harus dimasukkan menggunakan Ctrl + Shift + dimasukkan
Penjelasan
Contoh ini menggunakan rentang bernama berikut: "color" = B6: B14, "item" = C6: C14, dan "price" = E6: E14. Tujuannya adalah menemukan harga maksimum untuk warna dan item tertentu.
Rumus ini menggunakan dua fungsi IF bertumpuk, dibungkus di dalam MAX untuk mengembalikan harga maksimum dengan dua kriteria. Dimulai dengan tes logika dari pernyataan IF pertama, color = G6, nilai dalam rentang bernama "color" (B6: B14) diperiksa terhadap nilai di sel G6, "red". Hasilnya adalah array seperti ini:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Dalam uji logika untuk pernyataan IF kedua, item = H6, nilai dalam item rentang bernama (C6: C14) diperiksa terhadap nilai di sel H6, "hat". Hasilnya adalah array seperti ini:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Nilai jika benar" untuk pernyataan IF ke-2 adalah rentang bernama "harga" (E6: E14), yang berupa larik seperti ini:
(11;8;9;12;9;10;9;8;7)
Harga dikembalikan untuk setiap item dalam rentang ini hanya jika hasil dari dua larik pertama di atas BENAR untuk item di posisi yang sesuai. Dalam contoh yang ditunjukkan, larik terakhir di dalam MAX terlihat seperti ini:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Perhatikan bahwa satu-satunya harga yang "bertahan" adalah yang berada pada posisi di mana warnanya "merah" dan itemnya adalah "topi".
Fungsi MAX kemudian mengembalikan harga tertinggi, secara otomatis mengabaikan nilai FALSE.
Sintaks alternatif menggunakan logika boolean
Anda juga dapat menggunakan rumus array berikut, yang hanya menggunakan satu fungsi IF bersama dengan logika boolean:
(=MAX(IF((color=G6)*(item=H6),price)))
Keuntungan dari sintaks ini adalah lebih mudah untuk menambahkan kriteria tambahan tanpa menambahkan fungsi IF bertingkat. Jika Anda membutuhkan logika OR, gunakan penambahan sebagai ganti perkalian antar kondisi.
Dengan MAXIFS
Fungsi MAXIFS, yang diperkenalkan di Excel 2016, dirancang untuk menghitung jumlah maksimum berdasarkan satu atau beberapa kriteria tanpa memerlukan rumus array. Dengan MAXIFS, rumus di I6 adalah:
=MAXIFS(price,color,G6,item,H6)
Catatan: MAXIFS secara otomatis akan mengabaikan sel kosong yang memenuhi kriteria. Dengan kata lain, MAXIFS tidak akan memperlakukan sel kosong yang memenuhi kriteria sebagai nol. Di sisi lain, MAXIFS akan mengembalikan nol (0) jika tidak ada sel yang cocok dengan kriteria.
