Formula generik
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Ringkasan
Untuk mendapatkan nilai maksimum dalam kumpulan data berdasarkan lebih dari satu kriteria, Anda dapat menggunakan rumus array berdasarkan fungsi MAX dan IF. Dalam contoh yang ditunjukkan, formula dalam I6 adalah:
(=MAX(IF(color=G6,IF(item=H6,price))))
Dengan warna "merah" dan item "topi" hasilnya adalah $ 11.00
Catatan: Ini adalah formula array dan mesti dimasukkan menggunakan Ctrl + Shift + dimasukkan
Penjelasan
Contoh ini menggunakan julat bernama berikut: "color" = B6: B14, "item" = C6: C14, dan "price" = E6: E14. Tujuannya adalah untuk mencari harga maksimum untuk warna dan barang tertentu.
Formula ini menggunakan dua fungsi IF bersarang, dibungkus dalam MAX untuk mengembalikan harga maksimum dengan dua kriteria. Bermula dengan ujian logik pernyataan IF pertama, color = G6, nilai dalam julat bernama "color" (B6: B14) diperiksa terhadap nilai dalam sel G6, "merah". Hasilnya adalah susunan seperti ini:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Dalam ujian logik untuk pernyataan IF kedua, item = H6, nilai dalam item julat bernama (C6: C14) diperiksa terhadap nilai dalam sel H6, "topi". Hasilnya adalah susunan seperti ini:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
"Nilai jika benar" untuk pernyataan IF ke-2 adalah julat "harga" yang dinamakan (E6: E14), yang merupakan susunan seperti ini:
(11;8;9;12;9;10;9;8;7)
Harga dikembalikan untuk setiap item dalam julat ini hanya apabila hasil dari dua susunan pertama di atas adalah BENAR untuk item dalam kedudukan yang sepadan. Dalam contoh yang ditunjukkan, susunan terakhir di dalam MAX kelihatan seperti ini:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Perhatikan satu-satunya harga yang "bertahan" adalah harga yang warnanya "merah" dan item "topi".
Fungsi MAX kemudian mengembalikan harga tertinggi, secara automatik mengabaikan nilai PALSU.
Sintaks alternatif menggunakan logik boolean
Anda juga boleh menggunakan formula susunan berikut, yang hanya menggunakan satu fungsi IF bersama dengan logik boolean:
(=MAX(IF((color=G6)*(item=H6),price)))
Kelebihan sintaks ini adalah lebih mudah untuk menambahkan kriteria tambahan tanpa menambahkan fungsi IF bersarang tambahan. Sekiranya anda memerlukan logik ATAU, gunakan penambahan dan bukannya pendaraban antara keadaan.
Dengan MAXIFS
Fungsi MAXIFS, diperkenalkan di Excel 2016, dirancang untuk mengira maksimum berdasarkan satu atau lebih kriteria tanpa memerlukan formula array. Dengan MAXIFS, formula dalam I6 adalah:
=MAXIFS(price,color,G6,item,H6)
Catatan: MAXIFS secara automatik akan mengabaikan sel kosong yang memenuhi kriteria. Dengan kata lain, MAXIFS tidak akan memperlakukan sel kosong yang memenuhi kriteria sebagai sifar. Sebaliknya, MAXIFS akan mengembalikan sifar (0) jika tiada sel yang sesuai dengan kriteria.
