Gunakan FILTER untuk mengisolasi kategori aset seni yang persis, lalu XLOOKUP untuk mengembalikan tingkat pada ambang kuantitas terbesar yang tidak melebihi jumlah Anda. Panduan ini menyusun contoh Excel untuk Mac yang lengkap dengan label sintetis dan pengujian batas.

Versi Excel untuk Mac mana yang mendukung rumus ini?

Rumus di bawah menggunakan LET, FILTER, dan XLOOKUP. Dokumentasi Microsoft mencantumkan ketiganya untuk Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, dan Excel 2021 untuk Mac. Microsoft juga menyebutkan fungsi-fungsi ini dalam ikhtisarnya tentang apa yang baru di Excel 2021 untuk Mac.

Excel 2016 dan Excel 2019 tidak dapat mengevaluasi XLOOKUP; Microsoft secara eksplisit mendokumentasikan keterbatasan tersebut. Untuk rumus ini secara persis, gunakan salah satu edisi Mac yang tercantum di atas. Membuka buku kerja yang berisi fungsi tersebut di edisi yang lebih lama tidak menambahkan dukungan fungsi. Lihat dokumentasi XLOOKUP dari Microsoft.

Jika Anda tidak yakin edisi mana yang terinstal, periksa versi dan jenis lisensinya di dialog Tentang aplikasi. Microsoft menjelaskan cara mengetahui detail tersebut dalam Tentang Office: Versi Office mana yang saya gunakan?.

Siapkan kuantitas minimum khusus kategori

Misalkan Anda mencatat jumlah aset grafis yang dapat digunakan kembali dan ingin menetapkan tingkat inventaris deskriptif untuk setiap kategori. Tugas ini memiliki dua aturan: kategori harus cocok, dan kuantitas harus memenuhi nilai minimum suatu tingkat. Semua label dan kuantitas di bawah ini hanya ilustrasi.

Di lembar kerja kosong, masukkan tabel ini di A1:C7. Header berada di baris 1; enam baris data berada di baris 2–7. Masukkan kuantitas minimum sebagai angka dan isi setiap sel kategori dan tingkat.

Kategori Kuantitas minimum Label tingkat
Ubin Tekstur 5 Ringkas
Ubin Tekstur 20 Diperluas
Ubin Tekstur 50 Luas
Cap Bentuk 5 Ringkas
Cap Bentuk 30 Diperluas
Cap Bentuk 80 Luas

Selanjutnya, masukkan Kategori yang diperiksa di E1, Kuantitas di F1, dan Tingkat inventaris di G1. Masukkan Ubin Tekstur di E2 dan angka 34 di F2. Biarkan kolom D kosong untuk memisahkan data referensi dari input.

Anggap kuantitas minimum sebagai batas bawah inklusif. Untuk Ubin Tekstur, Ringkas dimulai dari 5, Diperluas dimulai dari 20, dan Luas dimulai dari 50. Jadi, jumlah 34 termasuk Diperluas. Untuk Cap Bentuk, label yang sama dimulai dari kuantitas berbeda: jumlah 20 masih termasuk Ringkas.

Sebelum menulis rumus, tentukan apakah batas Anda sendiri menunjukkan nilai minimum atau maksimum. Contoh ini menggunakan nilai minimum secara konsisten. Contoh ini juga tidak memasukkan kuantitas di bawah 5 ke tingkat mana pun dan membiarkan tingkat terakhir berlaku hingga di atas nilai awalnya. Itu adalah pilihan desain yang jelas untuk sampel ini, jadi sesuaikan data referensi jika aturan yang Anda maksud berbeda.

Masukkan rumus lengkap berdasarkan kategori dan kuantitas

Pilih G2, tempel rumus lengkap berikut ke bilah rumus, lalu tekan Return. Rumus ini menggunakan tata letak lembar kerja persis seperti di atas; rentang bernama dan kolom bantu tidak diperlukan.

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

Untuk Ubin Tekstur dan 34, hasil yang diharapkan adalah Diperluas. Rumus ini mengembalikan satu label meskipun perhitungan antaranya menggunakan larik. Rumus larik dinamis modern dapat dimasukkan seperti biasa; Microsoft mendokumentasikan konfirmasinya dengan Enter dalam Rumus larik dinamis dan perilaku limpahan.

Cara filter kategori menjaga keselarasan larik

Ekspresi categories=category membuat larik penyertaan bernama keep. Untuk input sampel ini, ekspresi tersebut memilih tiga baris Ubin Tekstur. Kedua pemanggilan FILTER menggunakan larik yang sama, sehingga menghasilkan nilai minimum 5, 20, dan 50 beserta label Ringkas, Diperluas, dan Luas yang sesuai.

Microsoft mendokumentasikan FILTER sebagai fungsi yang memilih data menggunakan larik penyertaan Boolean dengan dimensi yang sama dengan rentang sumber. Penggunaan larik penyertaan yang sama untuk kedua kolom menjaga kesesuaian nilainya. Lihat fungsi FILTER.

LET terluar menetapkan nama yang mudah dibaca untuk rentang sumber, input, dan larik penyertaan. Nama-nama ini hanya berlaku di dalam rumus tersebut; Anda tidak perlu membuatnya secara terpisah di buku kerja. Microsoft menjelaskan perilaku ini dalam dokumentasi fungsi LET.

Cara kuantitas memilih tingkat

Argumen kelima XLOOKUP, -1, meminta kecocokan kuantitas yang persis atau nilai berikutnya yang lebih kecil. Di dalam baris Ubin Tekstur yang difilter, 34 memilih nilai minimum 20 dan mengembalikan Diperluas. Kuantitas persis 50 memilih Luas. Microsoft menjelaskan mode-mode ini dalam dokumentasi fungsi XLOOKUP.

Rumus ini juga membedakan kategori yang tidak dikenal dari jumlah yang berada di bawah tingkat pertama. SUM(--keep) menghitung baris kategori yang cocok. Jika tidak ada, rumus mengembalikan Kategori tidak ditemukan sebelum mencoba pencarian yang difilter. Jika kategori ada tetapi semua nilai minimumnya melebihi jumlah, pencarian mengembalikan Di bawah tingkat pertama.

Uji batas dan isolasi kategori

Ganti E2 dan F2 dengan pasangan berikut, satu per satu. Pemeriksaan ini lebih berguna daripada hanya menguji nilai tengah: pemeriksaan ini menguji ambang pertama, batas persis, perbedaan kategori, dan kategori yang tidak ditemukan.

Kategori di E2 Kuantitas di F2 Hasil yang diharapkan
Ubin Tekstur 0 Di bawah tingkat pertama
Ubin Tekstur 5 Ringkas
Ubin Tekstur 19 Ringkas
Ubin Tekstur 20 Diperluas
Ubin Tekstur 34 Diperluas
Ubin Tekstur 50 Luas
Cap Bentuk 20 Ringkas
Cap Bentuk 34 Diperluas
Cap Bentuk 100 Luas
Sampel Warna 34 Kategori tidak ditemukan

Kedua pengujian dengan jumlah 20 sangat informatif. Ubin Tekstur harus mengembalikan Diperluas, sedangkan Cap Bentuk harus mengembalikan Ringkas. Jika keduanya mengembalikan Diperluas, periksa apakah pencarian kuantitas menggunakan baris kategori yang sudah difilter.

Sepuluh hasil yang diharapkan ini diperiksa dengan perhitungan Python kecil yang menerapkan aturan ambang minimum yang dijelaskan. Rumus Excel diperiksa kesesuaiannya dengan sintaks yang didokumentasikan Microsoft; rumus tersebut tidak dijalankan di Excel.

Periksa juga pesan input: kategori kosong atau kuantitas yang dimasukkan sebagai teks seharusnya menghasilkan Masukkan kategori dan kuantitas numerik; kuantitas numerik negatif seharusnya menghasilkan Gunakan nol atau lebih. Gunakan input bilangan bulat untuk jumlah aset. Rumus memeriksa apakah kuantitas berupa angka dan tidak negatif, tetapi tidak mewajibkan bilangan bulat.

Diagnosis hasil yang tidak terduga dan perluas lembar kerja

Lakukan pemeriksaan ini secara berurutan agar masalah pemformatan tidak mengubah aturan tingkat.

  1. Periksa teks kategori. Bandingkan input dengan label referensi, termasuk spasi. Microsoft menyebut karakter yang tidak terduga dan spasi tersembunyi sebagai masalah umum pencarian dalam panduan pemecahan masalah pencarian.
  2. Periksa sel numerik. Input kuantitas dan nilai minimum harus berupa angka, bukan teks yang tampak seperti angka. Panduan Microsoft yang sama menyebut tipe data yang tidak konsisten sebagai masalah pencarian.
  3. Periksa keselarasan rentang. Rentang kategori, minimum, dan label harus mencakup baris data yang sama. Dalam contoh ini, semua rentang dimulai di baris 2 dan berakhir di baris 7.
  4. Periksa aturan duplikat. Tetapkan satu label tingkat untuk setiap pasangan kategori dan kuantitas minimum. Jika dua baris menetapkan label berbeda untuk batas yang sama, selesaikan ambiguitas tersebut dalam data referensi.
  5. Periksa konvensi batas. Hasil yang tampak satu tingkat terlalu tinggi atau rendah mungkin menunjukkan bahwa nilai referensi Anda adalah nilai maksimum, sedangkan rumus ini mengharapkan nilai minimum.

Untuk pemeriksaan visual, masukkan rumus diagnostik berikut di sel yang tidak digunakan, seperti I2. Dengan Ubin Tekstur di E2, rumus ini seharusnya menampilkan tiga baris lengkap Ubin Tekstur. Ini adalah rumus diagnostik terpisah, bukan pengganti G2.

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

Sediakan ruang agar hasil diagnostik tersebut dapat meluas ke tiga kolom dan tiga baris ke bawah. Hasil larik tunggal akan melimpah ke sel di sebelahnya, dan sel yang terisi dapat menyebabkan #SPILL!. Microsoft menjelaskan perilaku tersebut dalam Rumus larik dinamis dan perilaku limpahan.

Saat menambahkan baris referensi, perluas ketiga rentang sumber dalam rumus utama secara bersamaan. Misalnya, jika baris data terakhir menjadi 10, ubah definisinya menjadi categories,$A$2:$A$10, minimums,$B$2:$B$10, dan labels,$C$2:$C$10. Lalu ulangi pengujian batas untuk kategori yang ditambahkan.

Untuk memproses beberapa baris input, masukkan lebih banyak pasangan kategori dan kuantitas di bawah E2:F2, lalu salin G2 ke bawah. Rentang sumber tetap karena referensinya berisi tanda dolar; nomor baris input akan bertambah. Tetapkan batas khusus kategori secara eksplisit, dan gunakan pengujian kategori berpasangan untuk memastikan setiap label yang dikembalikan mengikuti aturan inventaris yang dimaksud.

Sumber