Gunakan FILTER untuk mengasingkan kategori aset seni yang tepat, kemudian XLOOKUP untuk memulangkan peringkat pada ambang kuantiti terbesar yang tidak melebihi jumlah anda. Panduan ini membina contoh lengkap Excel untuk Mac menggunakan label sintetik dan ujian sempadan.

Versi Excel untuk Mac yang manakah menyokong formula ini?

Formula di bawah menggunakan LET, FILTER, dan XLOOKUP. Dokumentasi Microsoft menyenaraikan ketiga-tiganya untuk Excel for Microsoft 365 for Mac, Excel 2024 for Mac dan Excel 2021 for Mac. Microsoft turut mengenal pasti fungsi-fungsi ini dalam gambaran keseluruhan perkara baharu dalam Excel 2021 for Mac.

Excel 2016 dan Excel 2019 tidak dapat menilai XLOOKUP; Microsoft mendokumentasikan had ini secara jelas. Untuk formula khusus ini, gunakan salah satu edisi Mac yang disenaraikan di atas. Membuka buku kerja yang mengandungi fungsi itu dalam edisi lebih lama tidak menambah sokongan fungsi. Lihat dokumentasi XLOOKUP Microsoft.

Jika anda tidak pasti edisi yang dipasang, semak versi dan jenis lesennya dalam dialog Perihal aplikasi. Microsoft menerangkan cara mengenal pasti butiran ini dalam Perihal Office: Versi Office manakah yang saya gunakan?.

Sediakan kuantiti minimum khusus mengikut kategori

Katakan anda menyimpan kiraan aset grafik yang boleh digunakan semula dan mahu menetapkan peringkat inventori yang deskriptif untuk setiap kategori. Tugas ini mempunyai dua peraturan: kategori mesti sepadan dan kuantiti mesti memenuhi nilai minimum sesuatu peringkat. Semua label dan kuantiti di bawah ialah contoh.

Pada lembaran kerja kosong, masukkan jadual ini dalam A1:C7. Tajuk berada pada baris 1; enam baris data berada pada baris 2–7. Masukkan kuantiti minimum sebagai nombor dan isikan setiap sel kategori dan peringkat.

Kategori Kuantiti minimum Label peringkat
Jubin Tekstur 5 Ringkas
Jubin Tekstur 20 Diperluas
Jubin Tekstur 50 Menyeluruh
Cop Bentuk 5 Ringkas
Cop Bentuk 30 Diperluas
Cop Bentuk 80 Menyeluruh

Seterusnya, masukkan Kategori untuk disemak dalam E1, Kuantiti dalam F1, dan Peringkat inventori dalam G1. Letakkan Jubin Tekstur dalam E2 dan nombor 34 dalam F2. Biarkan lajur D kosong untuk memisahkan data rujukan daripada input.

Anggap kuantiti minimum sebagai sempadan bawah yang inklusif. Bagi Jubin Tekstur, Ringkas bermula pada 5, Diperluas bermula pada 20, dan Menyeluruh bermula pada 50. Oleh itu, kiraan sebanyak 34 tergolong dalam Diperluas. Bagi Cop Bentuk, label yang sama bermula pada kuantiti berbeza: kiraan sebanyak 20 masih tergolong dalam Ringkas.

Sebelum menulis formula, tentukan sama ada sempadan anda sendiri mewakili nilai minimum atau maksimum. Contoh ini menggunakan nilai minimum sepanjang masa. Kuantiti di bawah 5 tidak tergolong dalam mana-mana peringkat, manakala peringkat terakhir berterusan melebihi kuantiti permulaannya. Itu ialah pilihan reka bentuk khusus untuk contoh ini, jadi laraskan data rujukan jika peraturan yang anda inginkan berbeza.

Masukkan formula lengkap kategori dan kuantiti

Pilih G2, tampal formula lengkap berikut ke dalam bar formula, kemudian tekan Return. Formula ini menggunakan susun atur lembaran kerja tepat seperti di atas; julat bernama atau lajur pembantu 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
        )
      )
    )
  )
)

Bagi Jubin Tekstur dan 34, hasil yang dijangka ialah Diperluas. Formula ini memulangkan satu label walaupun pengiraan perantaranya menggunakan tatasusunan. Formula tatasusunan dinamik moden dimasukkan seperti biasa; Microsoft mendokumentasikan pengesahan dengan Enter dalam Formula tatasusunan dinamik dan tingkah laku limpahan.

Cara penapis kategori memastikan tatasusunan kekal sejajar

Ungkapan categories=category menghasilkan tatasusunan kemasukan bernama keep. Bagi input contoh ini, ungkapan tersebut memilih tiga baris Jubin Tekstur. Kedua-dua panggilan FILTER menggunakan tatasusunan yang sama, lalu menghasilkan nilai minimum 5, 20 dan 50 bersama label sepadan Ringkas, Diperluas dan Menyeluruh.

Microsoft mendokumentasikan FILTER sebagai fungsi yang memilih data menggunakan tatasusunan kemasukan Boolean yang dimensinya sepadan dengan julat sumber. Menggunakan tatasusunan kemasukan yang sama untuk kedua-dua lajur memastikan padanan antara data kekal terpelihara. Lihat fungsi FILTER.

Fungsi LET di bahagian luar menetapkan nama yang mudah dibaca untuk julat sumber, input dan tatasusunan kemasukan. Nama-nama ini hanya wujud dalam formula ini; anda tidak perlu menciptanya secara berasingan dalam buku kerja. Microsoft menerangkan tingkah laku ini dalam dokumentasi fungsi LET.

Cara kuantiti memilih peringkat

Argumen kelima XLOOKUP, iaitu -1, meminta padanan kuantiti tepat atau nilai seterusnya yang lebih kecil. Dalam baris Jubin Tekstur yang ditapis, 34 memilih nilai minimum 20 lalu memulangkan Diperluas. Kuantiti tepat 50 memilih Menyeluruh. Microsoft mentakrifkan mod ini dalam dokumentasi fungsi XLOOKUP.

Formula ini turut membezakan kategori yang tidak diketahui daripada kiraan di bawah peringkat pertama. SUM(--keep) mengira baris kategori yang sepadan. Jika tiada baris, formula memulangkan Kategori tidak ditemui sebelum cuba membuat carian yang ditapis. Jika kategori wujud tetapi semua nilai minimumnya melebihi kiraan, carian memulangkan Di bawah peringkat pertama.

Uji sempadan dan pengasingan kategori

Gantikan E2 dan F2 dengan pasangan berikut, satu demi satu. Semakan ini lebih berguna daripada menguji nilai pertengahan sahaja: semakan ini menguji ambang pertama, sempadan tepat, perbezaan kategori dan kategori yang tiada.

Kategori dalam E2 Kuantiti dalam F2 Hasil yang dijangka
Jubin Tekstur 0 Di bawah peringkat pertama
Jubin Tekstur 5 Ringkas
Jubin Tekstur 19 Ringkas
Jubin Tekstur 20 Diperluas
Jubin Tekstur 34 Diperluas
Jubin Tekstur 50 Menyeluruh
Cop Bentuk 20 Ringkas
Cop Bentuk 34 Diperluas
Cop Bentuk 100 Menyeluruh
Sampel Warna 34 Kategori tidak ditemui

Dua ujian yang menggunakan kiraan 20 amat mendedahkan. Jubin Tekstur mesti memulangkan Diperluas, manakala Cop Bentuk mesti memulangkan Ringkas. Jika kedua-duanya memulangkan Diperluas, periksa sama ada carian kuantiti menggunakan baris kategori yang telah ditapis.

Sepuluh hasil yang dijangka ini disemak menggunakan pengiraan kecil Python yang melaksanakan peraturan ambang minimum yang dinyatakan. Formula Excel itu sendiri disemak berdasarkan sintaks yang didokumenkan oleh Microsoft; formula itu tidak dijalankan dalam Excel.

Semak juga mesej input: kategori kosong atau kuantiti yang dimasukkan sebagai teks sepatutnya memulangkan Masukkan kategori dan kuantiti angka; kuantiti angka negatif sepatutnya memulangkan Gunakan sifar atau lebih. Gunakan input nombor bulat untuk kiraan aset. Formula ini menyemak bahawa kuantiti ialah angka dan bukan negatif, tetapi tidak menguatkuasakan nombor bulat.

Diagnosis hasil yang tidak dijangka dan lanjutkan lembaran kerja

Ikut turutan semakan ini supaya masalah pemformatan tidak menyebabkan perubahan pada peraturan peringkat.

  1. Semak teks kategori. Bandingkan input dengan label rujukan, termasuk ruang. Microsoft mengenal pasti aksara yang tidak dijangka dan ruang tersembunyi sebagai masalah carian yang lazim dalam panduan penyelesaian masalah carian.
  2. Semak sel angka. Input kuantiti dan nilai minimum hendaklah mengandungi nombor, bukannya teks yang kelihatan seperti nombor. Panduan Microsoft yang sama mengenal pasti jenis data yang tidak konsisten sebagai masalah carian.
  3. Semak penjajaran julat. Julat kategori, minimum dan label mesti meliputi baris data yang sama. Dalam contoh ini, setiap julat bermula pada baris 2 dan berakhir pada baris 7.
  4. Semak peraturan pendua. Berikan satu label peringkat bagi setiap pasangan kategori dan kuantiti minimum. Jika dua baris menetapkan label berbeza untuk sempadan yang sama, selesaikan kekaburan itu dalam data rujukan.
  5. Semak konvensyen sempadan. Hasil yang kelihatan satu peringkat terlalu tinggi atau rendah mungkin menunjukkan bahawa nilai rujukan anda ialah nilai maksimum, sedangkan formula ini menjangkakan nilai minimum.

Untuk pemeriksaan visual, masukkan formula diagnostik berikut dalam sel yang tidak digunakan seperti I2. Dengan Jubin Tekstur dalam E2, formula itu sepatutnya memaparkan tiga baris Jubin Tekstur yang lengkap. Ini ialah formula diagnostik berasingan, bukan pengganti untuk G2.

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

Sediakan ruang supaya hasil diagnostik itu dapat berkembang merentasi tiga lajur dan ke bawah tiga baris. Hasil tatasusunan kendiri akan melimpah ke sel bersebelahan, dan sel yang berisi boleh menyebabkan #SPILL!. Microsoft menerangkan tingkah laku itu dalam Formula tatasusunan dinamik dan tingkah laku limpahan.

Apabila menambah baris rujukan, lanjutkan ketiga-tiga julat sumber dalam formula utama serentak. Contohnya, jika baris data terakhir menjadi 10, tukar takrif kepada categories,$A$2:$A$10, minimums,$B$2:$B$10, dan labels,$C$2:$C$10. Kemudian ulangi ujian sempadan untuk kategori yang ditambah.

Untuk memproses beberapa baris input, masukkan lebih banyak pasangan kategori dan kuantiti di bawah E2:F2, kemudian salin G2 ke bawah. Julat sumber kekal tetap kerana rujukannya mengandungi tanda dolar; nombor baris input akan meningkat. Pastikan sempadan khusus kategori dinyatakan dengan jelas, dan gunakan ujian pasangan kategori untuk mengesahkan bahawa setiap label yang dipulangkan mengikut peraturan inventori yang dimaksudkan.

Sumber