Untuk menggabungkan label aset berulang tanpa menggandakan baris, nomborkan kejadian setiap label dalam kedua-dua senarai, kemudian cari pasangan label dan kejadian itu. Panduan Excel untuk Mac ini mengekalkan setiap baris sebelah kiri dan menandakan padanan yang tiada.

Apakah maksud pemadanan mengikut kejadian?

Katakan dua senarai aset seni masing-masing mengandungi beberapa baris berlabel Arc Tile. Tugas anda ialah melampirkan kuantiti sumber pertama pada Arc Tile pertama di sebelah kiri, kuantiti sumber kedua pada yang kedua, dan seterusnya. Label lain boleh muncul antara baris tersebut; kiraan kejadian adalah berasingan bagi setiap label.

Hubungan berdasarkan label sahaja adalah samar-samar. Jika satu senarai mengandungi tiga baris Arc Tile dan satu lagi mengandungi dua, memadankan setiap gabungan yang sepadan akan menghasilkan enam gabungan bagi label itu. Carian biasa mempunyai satu lagi batasan: Microsoft menyatakan bahawa XLOOKUP mengembalikan padanan pertama yang ditemuinya. Oleh itu, mengulangi carian berdasarkan label sahaja tidak akan beralih kepada kejadian seterusnya. Fungsi XLOOKUP

Penyelesaiannya ialah menjadikan kunci carian lebih khusus: Arc Tile|1, Arc Tile|2 dan Arc Tile|3. Setiap kunci bernombor mengenal pasti satu kejadian. Kaedah di bawah ialah pembinaan contoh menggunakan fungsi Excel yang didokumenkan; dokumentasi Microsoft menerangkan cara fungsi tersebut berfungsi.

Gunakan pendekatan ini apabila susunan baris menentukan pemadanan. Jika baris sepatutnya dipadankan berdasarkan pengecam aset atau medan semakan berasingan, gunakan maklumat itu. Nombor kejadian tidak dapat menentukan rekod mana yang sepadan jika susunan tidak membawa makna.

Versi Excel untuk Mac manakah yang menyokong kaedah ini?

Formula utama menggunakan COUNTIF, penggabungan dengan & dan XLOOKUP. Microsoft menyenaraikan Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac dan Excel 2021 untuk Mac pada halaman fungsi yang berkaitan. Dokumentasi XLOOKUP menyatakan dengan jelas bahawa fungsi itu tidak tersedia dalam Excel 2016 dan Excel 2019. Fungsi XLOOKUP, Gunakan fungsi COUNTIF dalam Microsoft Excel

Mulakan dengan formula carian yang diisi ke bawah di bawah. Variasi kemudian mengembalikan tujuh hasil yang sama dengan satu formula tatasusunan yang melimpah. Microsoft mendokumenkan bahawa formula limpahan diletakkan dalam grid lembaran kerja di luar Jadual Excel. Formula tatasusunan dinamik dan tingkah laku limpahan

Sediakan dua senarai aset sintetik

Gunakan lembaran kerja kosong dalam satu buku kerja. Masukkan senarai sebelah kiri berikut dalam A1:B8. Label dan kuantiti ini hanyalah contoh; kuantiti sebelah kiri kekal sebagai sebahagian daripada senarai asal dan tidak digunakan untuk menentukan padanan.

Label aset Kuantiti sebelah kiri
Arc Tile 4
Dot Panel 2
Arc Tile 6
Line Print 3
Dot Panel 5
Arc Tile 1
Ring Card 2

Masukkan senarai sumber dalam G1:H7. Kekalkan susunan baris yang ditunjukkan supaya hasil anda sepadan dengan panduan ini.

Label aset Kuantiti sumber
Dot Panel 12
Arc Tile 8
Line Print 7
Arc Tile 9
Dot Panel 0
Grid Sheet 11

Tambahkan pengepala Kejadian, Kunci cantuman dan Kuantiti sumber sepadan dalam C1, D1 dan E1. Tambahkan Kejadian, Kunci cantuman dan Digunakan oleh senarai kiri dalam I1, J1 dan K1. Biarkan lajur F kosong untuk memisahkan senarai secara visual.

Untuk contoh ini, gunakan label teks yang tidak kosong dan panjangnya kurang daripada 255 aksara, dengan huruf besar dan jarak yang konsisten serta tanpa aksara kad bebas seperti *, ? atau ~. Masukkan kuantiti sumber berangka bagi setiap baris sumber, termasuk sifar jika sesuai. Prasyarat ini menjadikan tafsiran formula ringkas tidak samar-samar.

Nomborkan kejadian dan bina kunci carian

Kira setiap label dari bahagian atas senarainya sendiri

Masukkan formula ini dalam C2, tekan Return dan salinkannya ke bawah hingga C8:

=COUNTIF($A$2:A2,A2)

Titik hujung pertama, $A$2, kekal tetap. Titik hujung kedua berkembang apabila anda menyalin ke bawah. Pada setiap baris, formula mengira bilangan kemunculan label baris itu setakat ini. COUNTIF mengira sel yang memenuhi kriteria yang diberikan. Gunakan fungsi COUNTIF dalam Microsoft Excel

Nombor kejadian sebelah kiri yang terhasil sepatutnya ialah 1, 1, 2, 1, 2, 3, 1. Khususnya, Arc Tile yang ketiga mendapat 3 walaupun barisnya dipisahkan oleh label lain.

Masukkan formula sumber yang sepadan dalam I2, kemudian salinkannya hingga I7:

=COUNTIF($G$2:G2,G2)

Nombor kejadian sumber sepatutnya ialah 1, 1, 1, 2, 2, 1. Kira kedua-dua senarai secara berasingan: nombor kejadian sesuatu baris ialah kedudukannya dalam kalangan label sepadan dalam senarainya sendiri.

Gabungkan label dan kejadian

Masukkan formula ini dalam D2 dan salinkannya hingga D8:

=A2&"|"&C2

Masukkan formula ini dalam J2 dan salinkannya hingga J7:

=G2&"|"&I2

Senarai sebelah kiri kini mengandungi Arc Tile|1, Arc Tile|2 dan Arc Tile|3. Sumber hanya mengandungi dua kunci pertama. Ini menunjukkan kejadian ketiga yang tiada sebelum sebarang kuantiti dikembalikan.

Kembalikan satu kuantiti sumber bagi setiap baris sebelah kiri

Masukkan formula berikut dalam E2, kemudian salinkannya hingga E8:

=XLOOKUP(D2,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Formula itu mencari kunci cantuman sumber, mengembalikan kuantiti sumber yang sepadan dan memaparkan Unmatched apabila kunci bernombor itu tiada. 0 yang terakhir meminta padanan tepat secara jelas, yang juga merupakan tetapan lalai XLOOKUP. Fungsi XLOOKUP

Hasil sebelah kiri yang lengkap sepatutnya seperti berikut:

Label aset Kuantiti sebelah kiri Kejadian Kunci cantuman Kuantiti sumber sepadan
Arc Tile 4 1 Arc Tile\ 1
Dot Panel 2 1 Dot Panel\ 1
Arc Tile 6 2 Arc Tile\ 2
Line Print 3 1 Line Print\ 1
Dot Panel 5 2 Dot Panel\ 2
Arc Tile 1 3 Arc Tile\ 3
Ring Card 2 1 Ring Card\ 1

Masih terdapat tujuh baris sebelah kiri. Arc Tile yang kedua menerima 9, bukannya mengulangi 8 daripada kejadian pertama. Yang ketiga tiada pasangan sumber. Ring Card juga tiada pasangan, manakala sifar yang sepadan bagi Dot Panel kedua kekal kelihatan.

Kekalkan Unmatched berbeza daripada sifar. Dalam contoh ini, sifar ialah kuantiti sumber sebenar; menggunakan sifar untuk padanan yang tiada akan menyembunyikan perbezaan itu. Begitu juga, selesaikan kuantiti sumber yang kosong sebelum mentafsir hasil supaya sel sumber kosong tidak disalah anggap sebagai kuantiti yang direkodkan.

Pilihan: kembalikan hasil sebagai satu tatasusunan

Selepas menyemak hasil yang diisi ke bawah, kosongkan E2:E8 sahaja dan masukkan alternatif ini dalam E2:

=XLOOKUP(D2:D8,$J$2:$J$7,$H$2:$H$7,"Unmatched",0)

Ini membekalkan ketujuh-tujuh kunci cantuman sebelah kiri sebagai input carian. Dengan data sampel, hasilnya memenuhi E2:E8 mengikut susunan senarai kiri. Tekan Return; sel output bersebelahan mestilah kosong. Microsoft menerangkan peletakan automatik hasil tatasusunan ini sebagai limpahan. Formula tatasusunan dinamik dan tingkah laku limpahan

Pilih versi yang diisi ke bawah jika anda lebih suka formula yang boleh diperiksa secara berasingan pada setiap baris. Pilih versi limpahan jika anda lebih suka satu formula mengawal lajur hasil. Kedua-duanya bergantung pada kunci kejadian yang sama.

Semak padanan yang tiada dan baris sumber yang tidak digunakan

Mengekalkan bilangan baris kiri tidak bermakna setiap baris sumber telah digunakan. Tambahkan semakan songsang supaya kejadian sumber tambahan kekal kelihatan. Dalam K2, masukkan formula berikut dan salinkannya hingga K7:

=COUNTIF($D$2:$D$8,J2)

Untuk sampel ini, lima baris sumber pertama mengembalikan 1, manakala Grid Sheet|1 mengembalikan 0. Sifar itu mengenal pasti baris sumber yang tiada pasangan sebelah kiri. Nilai yang lebih besar daripada 1 bermakna kunci sumber dirujuk oleh berbilang kunci kiri dan perlu disiasat.

Gunakan semakan ini bersama-sama:

  • Pastikan output memenuhi tepat tujuh baris asal di sebelah kiri.
  • Pastikan urutan kuantiti: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Pastikan Arc Tile ketiga dan Ring Card kekal jelas sebagai tiada padanan.
  • Pastikan Grid Sheet|1 ditandakan sebagai tidak digunakan dalam audit sumber.

Urutan yang dijangka dan hasil sumber yang tidak digunakan disemak menggunakan pengiraan kecil dan deterministik bagi logik pemadanan kejadian. Formula Excel itu sendiri diperiksa berdasarkan sintaks yang didokumenkan oleh Microsoft; formula tersebut tidak dijalankan dalam Excel untuk Mac.

Selesaikan masalah hasil sebelum mengembangkan buku kerja

Pemadanan berubah selepas pengisihan

Formula kejadian menggunakan susunan baris semasa. Mengisih mana-mana senarai dan mengira semula boleh mengubah baris berulang yang menerima kejadian 1 atau 2. Tentukan susunan yang dimaksudkan sebelum penomboran. Jika pemadanan kekal diperlukan, simpan pengecam stabil yang merekodkannya dan bukannya bergantung semata-mata pada kedudukan yang dikira semula.

Label kelihatan serupa tetapi menghasilkan padanan yang tidak dijangka

Microsoft menyatakan bahawa COUNTIF tidak membezakan huruf besar dan kecil, mengenali kriteria kad bebas, serta boleh memberikan hasil yang tidak dijangka dengan ruang di hadapan atau di belakang atau aksara lain yang tidak diingini. Microsoft juga mendokumenkan batasan apabila memadankan rentetan yang melebihi 255 aksara. Seragamkan label dalam kedua-dua senarai sebelum menomborkannya; label pendek yang diformat secara konsisten di atas mengelakkan keadaan tersebut. Gunakan fungsi COUNTIF dalam Microsoft Excel

Formula tatasusunan memaparkan #SPILL!

Semak kandungan sedia ada dalam E3:E8, termasuk formula yang diisi ke bawah sebelum ini. Alihkan apa-apa yang perlu disimpan, kemudian kosongkan halangan itu. Jika formula berada di dalam Jadual Excel, gunakan pendekatan yang diisi ke bawah atau alihkan output limpahan ke luar Jadual. Ini ialah sekatan limpahan yang didokumenkan. Formula tatasusunan dinamik dan tingkah laku limpahan

Anda memerlukan carian tanpa XLOOKUP

Kunci pembantu yang sama boleh memacu carian INDEX/MATCH. Sebagai alternatif kepada mana-mana formula di atas, masukkan formula ini dalam E2 dan salinkannya hingga E8:

=INDEX($H$2:$H$7,MATCH(D2,$J$2:$J$7,0))

Di sini, MATCH bersama 0 mencari kunci cantuman yang tepat, dan INDEX mengembalikan kuantiti pada kedudukan itu. Kunci yang tiada sengaja dikekalkan sebagai #N/A supaya kekal kelihatan. Microsoft mendokumenkan kedua-dua fungsi untuk edisi Mac semasa. Formula skalar ini tidak memerlukan output limpahan. Fungsi MATCH, Fungsi INDEX

Baris baharu tiada dalam hasil

Panduan ini menggunakan julat tetap. Apabila menambah baris, lanjutkan formula kejadian dan kunci cantuman, julat carian serta pemulangan sumber, dan julat audit songsang bersama-sama. Bagi versi limpahan, lanjutkan juga input carian sebelah kiri melepasi D8. Menambah data di bawah julat ini sahaja tidak akan memasukkannya dalam pengiraan.

Setelah urutan output dan audit songsang sepadan dengan susunan yang dimaksudkan, anda mempunyai cantuman berasaskan kejadian yang boleh digunakan: setiap baris aset sebelah kiri mengekalkan kedudukannya, menerima paling banyak satu kuantiti sumber, dan mengekalkan petunjuk yang jelas apabila kejadian padanannya tiada.

Sumber