Kadang-kadang, bukannya hanya mencari nombor terbesar atau maksimum untuk semua data anda; anda perlu mencari nombor terbesar dalam subset - seperti nombor positif atau negatif yang terbesar.
Jika jumlah data kecil, tugas mungkin mudah dicapai dengan memilih jarak yang betul untuk fungsi MAX secara manual.
Dalam keadaan lain, seperti sampel data yang tidak diselaraskan, memilih rentang dengan betul dapat membuktikan sukar jika tidak mustahil.
Dengan menggabungkan fungsi IF dengan MAX dalam formula tatasusunan, keadaan - seperti nombor positif atau negatif sahaja - dengan mudah boleh ditetapkan supaya hanya data yang sepadan dengan parameter ini diuji oleh formula.
MAX JIKA Pecahan Formula Array
Formula yang digunakan dalam tutorial ini untuk mencari nombor positif terbesar ialah:
= MAX (JIKA (A1: B5> 0, A1: B5))
Catatan: Argumen value_if_false IF, yang merupakan pilihan, ditinggalkan untuk mempersingkat formula. Sekiranya data dalam julat yang dipilih tidak memenuhi kriteria ditetapkan - nombor lebih besar daripada sifar - formula akan mengembalikan sifar ( 0 )
Tugas setiap bahagian formula adalah:
- Fungsi IF menapis data supaya hanya angka-angka yang memenuhi kriteria yang dipilih diteruskan ke fungsi MAX
- fungsi MAX mendapati nilai tertinggi untuk data yang ditapis
- Formula array - ditunjukkan oleh pendakap kerinting { } mengelilingi formula - membolehkan hujah ujian logik fungsi IF untuk mencari seluruh julat data untuk perlawanan - seperti nombor yang lebih besar daripada sifar - bukan sekadar sel tunggal data
Formula CSE
Rumus Array dicipta dengan menekan Ctrl, Shift, dan Masukkan kekunci pada papan kekunci pada masa yang sama apabila formula telah ditaip masuk.
Hasilnya adalah bahawa formula keseluruhan - termasuk tanda yang sama - dikelilingi oleh pendakap kerinting. Contohnya ialah:
{= MAX (JIKA (A1: B5> 0, A1: B5))}
Kerana kunci yang ditekan untuk membuat formula tatasusunan, ia kadang-kadang dirujuk sebagai CSE formula.
Contoh Formula MAX IF Array Excel
Seperti yang dilihat dalam imej di atas, contoh tutorial ini menggunakan formula array MAX IF untuk mencari nilai positif dan negatif terbesar dalam pelbagai nombor.
Langkah-langkah di bawah ini mula mencipta formula untuk mencari nombor positif terbesar diikuti dengan langkah-langkah yang diperlukan untuk mencari nombor negatif terbesar.
Memasuki Data Tutorial
- Masukkan nombor yang dilihat dalam imej di atas ke dalam sel A1 hingga B5 lembaran kerja
- Dalam sel A6 dan A7 taipkan label Max Positif dan Max Negatif
Memasuki MAX IF Nested Formula
Oleh kerana kita mencipta formula bersarang dan formula tatasusunan, kita perlu menaip keseluruhan formula ke dalam satu sel kerja lembaran kerja.
Sebaik sahaja anda telah memasukkan formula JANGAN tekan Masukkan kekunci pada papan kekunci atau klik pada sel yang berbeza dengan tetikus kerana kita perlu mengubah formula menjadi formula tatasusunan.
- Klik pada sel B6 - lokasi di mana hasil formula pertama akan dipaparkan
- Taip yang berikut:
= MAX (JIKA (A1: B5> 0, A1: B5))
Mewujudkan Formula Array
- Tekan dan tahan Ctrl dan Shift kekunci pada papan kekunci
- Tekan butang Masukkan kekunci pada papan kekunci untuk membuat formula tatasusunan
- Jawapan 45 sepatutnya muncul dalam sel B6 kerana ini adalah nombor positif terbesar dalam senarai
- Sekiranya anda mengklik pada sel B6, formula array lengkap
{= MAX (JIKA (A1: B5> 0, A1: B5))}
boleh dilihat di bar formula di atas lembaran kerja
Mencari Nombor Negatif Terbesar
Formula untuk mencari nombor negatif terbesar berbeza dari formula pertama hanya dalam operator perbandingan yang digunakan dalam argumen ujian logik fungsi IF.
Oleh kerana objektif adalah untuk mencari nombor negatif terbesar, formula kedua menggunakan kurang daripada operator ( < ), daripada yang lebih besar daripada pengendali ( > ), untuk menguji hanya data yang kurang daripada sifar.
- Klik pada sel B7
- Taip yang berikut:
= MAX (JIKA (A1: B5 <0, A1: B5))
- Ikuti langkah-langkah di atas untuk membuat formula tatasusunan
- Jawapan -8 sepatutnya muncul dalam sel B7 kerana ini adalah nombor negatif terbesar dalam senarai
Mendapatkan #VALUE! untuk Jawapan
Jika sel B6 dan B7 memaparkan #VALUE! nilai ralat dan bukannya jawapan yang dinyatakan di atas, ini mungkin kerana formula tatasusunan tidak dicipta dengan betul.
Untuk membetulkan masalah ini, klik pada formula dalam bar formula dan tekan Ctrl, Shift dan Masukkan kekunci pada papan kekunci sekali lagi.
