Terkadang, daripada hanya menemukan nomor terbesar atau maksimum untuk semua data Anda; Anda perlu mencari angka terbesar dalam subset - seperti angka positif atau negatif terbesar.
Jika jumlah data kecil, tugas mungkin mudah diselesaikan dengan memilih secara manual kisaran yang benar untuk fungsi MAX.
Dalam keadaan lain, seperti sampel data besar yang tidak disortir, memilih kisaran dengan benar dapat terbukti sulit atau bahkan tidak mungkin.
Dengan menggabungkan fungsi IF dengan MAX dalam rumus array, kondisi - seperti angka positif atau negatif saja - dapat dengan mudah diatur sehingga hanya data yang cocok dengan parameter ini yang diuji oleh rumus.
MAX IF Array Formula Breakdown
Rumus yang digunakan dalam tutorial ini untuk menemukan bilangan positif terbesar adalah:
= MAX (IF (A1: B5> 0, A1: B5))
Catatan: Nilai fungsi IF_if_false, yang opsional, dihilangkan untuk mempersingkat rumus. Jika data dalam rentang yang dipilih tidak memenuhi kriteria yang ditetapkan - angka yang lebih besar dari nol - rumus akan mengembalikan nol ( 0 )
Tugas setiap bagian rumus adalah:
- Fungsi IF menyaring data sehingga hanya angka-angka yang memenuhi kriteria yang dipilih diteruskan ke fungsi MAX
- fungsi MAX menemukan nilai tertinggi untuk data yang difilter
- Formula array - ditunjukkan oleh kurung kurawal { } seputar rumus - memungkinkan argumen uji logika fungsi IF untuk mencari seluruh rentang data untuk kecocokan - seperti angka yang lebih besar dari nol - bukan hanya satu sel data
Formula CSE
Rumus array dibuat dengan menekan tombol Ctrl, Bergeser, dan Memasukkan kunci pada keyboard pada saat yang bersamaan setelah rumus diketikkan.
Hasilnya adalah seluruh formula - termasuk tanda yang sama - dikelilingi oleh tanda kurung kurawal. Contohnya adalah:
{= MAX (IF (A1: B5> 0, A1: B5))}
Karena tombol ditekan untuk membuat rumus array, mereka kadang-kadang disebut sebagai CSE formula.
Contoh Formula Excel MAX IF Array
Seperti yang terlihat pada gambar di atas, contoh tutorial ini menggunakan rumus array MAX IF untuk menemukan nilai positif dan negatif terbesar dalam berbagai angka.
Langkah-langkah di bawah ini pertama kali membuat rumus untuk menemukan angka positif terbesar yang diikuti oleh langkah-langkah yang diperlukan untuk menemukan angka negatif terbesar.
Memasukkan Data Tutorial
- Masukkan angka yang terlihat pada gambar di atas ke sel A1 ke B5 dari lembar kerja
- Dalam sel A6 dan A7 ketik label Max Positive dan Max Negatif
Memasukkan MAX IF Nested Formula
Karena kita membuat rumus bertingkat dan rumus array, kita perlu mengetikkan seluruh rumus ke dalam sel worksheet tunggal.
Setelah Anda memasukkan rumus TIDAK tekan Memasukkan kunci pada keyboard atau klik pada sel yang berbeda dengan mouse karena kita perlu mengubah rumus menjadi rumus array.
- Klik pada sel B6 - lokasi di mana hasil rumus pertama akan ditampilkan
- Ketik yang berikut:
= MAX (IF (A1: B5> 0, A1: B5))
Membuat Rumus Array
- Tekan dan tahan tombol Ctrl dan Bergeser kunci pada keyboard
- tekan Memasukkan kunci pada keyboard untuk membuat rumus array
- Jawabannya 45 akan muncul di sel B6 karena ini adalah angka positif terbesar dalam daftar
- Jika Anda mengklik sel B6, rumus array lengkap
{= MAX (IF (A1: B5> 0, A1: B5))}
dapat dilihat di bilah rumus di atas lembar kerja
Menemukan Nomor Negatif Terbesar
Rumus untuk menemukan angka negatif terbesar berbeda dari rumus pertama hanya pada operator perbandingan yang digunakan dalam argumen uji logika fungsi IF.
Karena tujuannya adalah untuk sekarang menemukan angka negatif terbesar, rumus kedua menggunakan operator yang lebih sedikit ( < ), bukan operator yang lebih besar ( > ), untuk menguji hanya data yang kurang dari nol.
- Klik pada sel B7
- Ketik yang berikut:
= MAX (IF (A1: B5 <0, A1: B5))
- Ikuti langkah-langkah di atas untuk membuat rumus larik
- Jawabannya -8 akan muncul di sel B7 karena ini adalah angka negatif terbesar dalam daftar
Mendapatkan #VALUE! untuk sebuah Jawaban
Jika sel B6 dan B7 menampilkan #VALUE! nilai kesalahan daripada jawaban yang ditunjukkan di atas, mungkin karena rumus array tidak dibuat dengan benar.
Untuk memperbaiki masalah ini, klik rumus di bilah rumus dan tekan Ctrl, Bergeser dan Memasukkan kunci pada keyboard lagi.
