Untuk menjadikan carian inventori Excel memilih peraturan padanan yang paling khusus, kenal pasti dahulu baris yang layak, kemudian kira medan yang dinyatakan. Pulangkan kuantiti daripada baris unik dengan skor tertinggi dan tandakan seri. Panduan ini membina hasil tersebut untuk aset seni sintetik dalam Excel untuk Mac.

Versi Excel untuk Mac manakah yang menyokong pendekatan ini?

Formula di bawah menggunakan LET, XLOOKUP dan pengiraan tatasusunan dinamik. Microsoft mendokumenkan keupayaan ini untuk Excel untuk Microsoft 365 bagi Mac, Excel 2024 bagi Mac dan Excel 2021 bagi Mac. Panduan ciri Excel 2021 bagi Mac secara jelas menyertakan LET, XLOOKUP dan tatasusunan dinamik. Apa yang baharu dalam Excel 2021 untuk Mac, Fungsi LET.

Excel 2016 dan Excel 2019 tidak menyokong XLOOKUP, menurut nota keserasian Microsoft yang jelas. Gunakan salah satu edisi Mac yang disokong untuk formula tepat ini; mengubah cara anda memasukkan formula tidak menyediakan fungsi yang tiada. Formula pemeriksaan pilihan kemudian dalam artikel ini menggunakan FILTER, yang turut didokumenkan untuk tiga edisi Mac yang disokong di atas. Fungsi XLOOKUP, Fungsi FILTER.

Apakah maksud “paling khusus” dalam inventori anda?

Dalam contoh ini, anda mencari kuantiti yang ditetapkan kepada aset seni menggunakan tiga atribut: label aset, kemasan dan saiz. Medan peraturan yang kosong bermaksud peraturan itu menerima sebarang nilai untuk atribut tersebut. Medan yang diisi mesti sama dengan input yang sepadan. Antara peraturan yang layak, peraturan dengan medan terisi paling banyak akan dipilih.

Ini ialah dasar buku kerja yang jelas, dirumuskan untuk contoh ini. Excel tidak membuat kesimpulan tentang dasar itu daripada perihalan anda. Di sini, semua atribut mempunyai pemberat yang sama: peraturan yang menyatakan aset dan kemasan mempunyai kekhususan yang sama dengan peraturan yang menyatakan aset dan saiz. Jika kedua-duanya sepadan dan berkongsi skor tertinggi, formula melaporkan seri.

XLOOKUP biasa tidak dapat menentukan keutamaan ini dengan sendirinya. Microsoft mendokumenkan bahawa fungsi itu memulangkan item padanan pertama secara lalai. Membalikkan carian hanya mengubah hujung tempat carian bermula; ia tidak menyusun peraturan mengikut kekhususan. Oleh itu, formula mengira baris pemenang sebelum meminta XLOOKUP mendapatkan kuantitinya. Fungsi XLOOKUP.

Sediakan peraturan aset seni sintetik

Pada lembaran kerja kosong, masukkan pengepala dan nilai ini dalam A1:D6. Sel kosong dalam jadual di bawah mesti kekal kosong dalam Excel. Semua label dan kuantiti hanyalah ilustrasi.

Baris lembaran kerja A: Aset B: Kemasan C: Saiz D: Kuantiti
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

Peraturan pada baris 2 ialah lalai yang disengajakan: peraturan itu tidak menyatakan sebarang atribut dan mempunyai kekhususan sifar. Baris 3 menyatakan satu atribut, baris 4 menyatakan dua, dan baris 5 menyatakan tiga. Kuantiti ini ialah nilai alternatif yang ditetapkan, jadi carian memulangkan satu nilai. Ia tidak menjumlahkan kuantiti lalai dengan kuantiti pengecualian.

Masukkan pengepala Asset, Finish, Size dan Result dalam H1:K1. Kemudian masukkan Tile dalam H2, Matte dalam I2 dan Small dalam J2. Pastikan ketiga-tiga atribut input diisi. Dalam reka bentuk ini, sel kosong bertindak sebagai syarat tanpa sekatan hanya dalam jadual peraturan; input yang tidak lengkap akan menerima mesej.

Gunakan kuantiti angka, termasuk sifar angka jika sesuai. Hadkan julat kepada baris peraturan sebenar sahaja. Memanjangkannya hingga baris kosong yang tidak digunakan akan memperkenalkan peraturan tambahan yang semuanya kosong di bawah dasar padanan ini.

Masukkan formula untuk memilih peraturan pemenang

Pilih K2, masukkan formula lengkap ini dan tekan Return. Formula ini menggunakan jadual peraturan dan sel input yang ditakrifkan di atas. Pengiraan tatasusunan dinamik dinilai dalam formula; hasil akhir mengisi satu sel. Dokumentasi Microsoft menerangkan bahawa formula tatasusunan dinamik boleh dimasukkan seperti biasa, tanpa kekunci kemasukan tatasusunan lama. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

Dengan input sampel, K2 sepatutnya memulangkan 7. Empat peraturan layak, tetapi hanya peraturan pada baris 5 menyatakan ketiga-tiga atribut.

Cara kelayakan dan kekhususan berfungsi

LET memberikan nama setempat kepada julat dan hasil perantaraan supaya proses pemilihan lebih mudah diperiksa. Microsoft mendokumenkan bahawa nama ini wujud dalam skop formula. Fungsi LET.

Bagi setiap atribut, ungkapan kelayakan menyemak sama ada sel peraturan kosong atau sama dengan input. Penambahan menggabungkan kedua-dua kemungkinan itu, dan perbandingan menukarkan hasilnya kepada ujian logik. Pendaraban tiga ujian atribut mensyaratkan semua syarat dipenuhi. Microsoft menggunakan corak pendaraban yang sama untuk menggabungkan kriteria tatasusunan dengan logik AND dalam contoh FILTER mereka. Fungsi FILTER.

specificity mengira medan peraturan yang diisi. Operator unary berganda menukarkan setiap ujian bukan kosong kepada nombor, lalu menghasilkan skor daripada sifar hingga tiga. IF memberikan skor negatif satu kepada baris yang tidak layak untuk pengiraan kedudukan; kemudian MAX mencari skor tertinggi yang layak. Negatif satu memastikan baris yang tidak layak berada di bawah skor sifar bagi lalai tanpa sekatan. Fungsi IF, Fungsi MAX.

winner menandakan hanya baris yang layak dengan skor tertinggi itu. Formula menyemak keadaan tiada padanan dan berbilang pemenang sebelum memulangkan kuantiti. Semakan angka terakhirnya sengaja menolak kuantiti kosong atau nombor yang disimpan sebagai teks. Microsoft mendokumenkan bahawa ISNUMBER tidak menukarkan nombor teks kepada nilai angka. Sifar angka kekal sebagai hasil yang sah. Fungsi IS.

Semak hasil sebelum mengembangkan lembaran kerja

Tukar H2:J2 kepada setiap kombinasi di bawah. Ini ialah hasil jangkaan berdasarkan peraturan sampel, bukannya pemerhatian inventori yang diukur.

Aset Kemasan Saiz Hasil jangkaan Syarat pemenang
Tile Matte Small 7 Tiga medan dinyatakan
Tile Matte Large 12 Aset dan kemasan
Tile Gloss Small 18 Aset sahaja
Disc Gloss Large 10 Aset dan saiz
Arc Matte Small 30 Lalai tanpa sekatan

Seterusnya, ubah sementara baris 6 kepada aset Tile, kemasan Matte, saiz Small dan kuantiti 9. Setelah input asal dipulihkan, hasilnya sepatutnya menjadi Tie: review rules: kini terdapat dua peraturan yang menyatakan ketiga-tiga atribut padanan. Walaupun kuantitinya sama, hasilnya tetap seri kerana pemilihan peraturan tidak jelas. Selepas itu, pulihkan baris 6 yang asal.

Untuk menguji cabang tiada padanan, ubah sementara empat julat peraturan dalam formula supaya bermula pada baris 3, dengan mengecualikan lalai. Input Arc, Matte, Small sepatutnya memulangkan No matching rule. Selepas selesai, pulihkan julat supaya bermula pada baris 2. Untuk menguji semakan kuantiti, kosongkan D5 sambil menggunakan input asal; jangkakan Check rule quantity, kemudian pulihkan 7.

Periksa peraturan bertindih dan selesaikan seri

Untuk melihat semua peraturan yang layak bagi input semasa, masukkan formula berasingan ini dalam M2. Pastikan M2:P6 kosong terlebih dahulu. Output menunjukkan baris peraturan padanan mengikut urutan asalnya; ia tidak menyusunnya mengikut kedudukan.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER memulangkan baris yang dipilih oleh tatasusunan kemasukan Boolean, dan argumen terakhirnya memberikan mesej jika tiada baris yang layak. Sel sumber yang kosong mungkin dipaparkan sebagai sifar dalam output pemeriksaan ini; terus anggap sel kosong dalam jadual peraturan asal sebagai syarat tanpa sekatan. Fungsi FILTER.

Formula pemeriksaan ini melimpah ke sel bersebelahan. Letakkannya di luar jadual Excel dan pastikan kawasan output kosong. Microsoft mendokumenkan bahawa formula tatasusunan limpahan tidak disokong dalam jadual dan sel output yang telah diisi boleh menyebabkan #SPILL!. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan.

Selesaikan seri dengan membetulkan pendua, mengecilkan skop peraturan bertindih atau menetapkan dasar keutamaan tambahan. Contohnya, peraturan aset bersama kemasan dan aset bersama saiz kedua-duanya mendapat skor dua di sini. Buku kerja tidak dapat menentukan atribut yang lebih penting melainkan anda menetapkan keutamaan itu dalam formula. Memindahkan baris tidak sepatutnya menentukan jawapan berdasarkan dasar yang digunakan dalam artikel ini.

Kembangkan carian tanpa mengubah maksudnya

Untuk aset lain, masukkan tiga atributnya dalam H3:J3 dan salin K2 ke dalam K3. Rujukan input bergerak bersama formula, manakala tanda dolar memastikan julat peraturan kekal tetap. Apabila menambah peraturan selepas baris 6, kembangkan keempat-empat julat dalam formula utama dan kedua-dua dimensi formula pemeriksaan secara konsisten.

Kekalkan setiap atribut dalam lajurnya sendiri dan gunakan label yang konsisten tanpa ruang sampingan. Formula ini membandingkan nilai medan penuh; ia tidak mencari cebisan teks dalam perihalan aset yang digabungkan. Semakan kuantitinya memastikan nilai yang dipilih ialah angka, jadi gunakan sebarang keperluan inventori tambahan—seperti kuantiti bulat bukan negatif—pada data sumber.

Carian yang terhasil mempunyai urutan keputusan yang jelas: wajibkan input lengkap, kenal pasti peraturan yang layak, cari kekhususan tertinggi, tolak seri dan pulangkan kuantiti angka yang dipilih. Urutan ini membolehkan lalai umum dan pengecualian terperinci digunakan bersama sambil memastikan pilihan yang belum diselesaikan tetap kelihatan.

Sumber