Untuk menggabungkan label aset berulang tanpa menggandakan baris, beri nomor pada setiap kemunculan label di kedua daftar, lalu cari pasangan label dan kemunculannya. Panduan Excel untuk Mac ini mempertahankan setiap baris di sisi kiri dan menandai kecocokan yang tidak ditemukan.

Apa arti mencocokkan berdasarkan kemunculan?

Misalkan dua daftar aset seni sama-sama berisi beberapa baris berlabel Arc Tile. Tugas Anda adalah memasangkan kuantitas sumber pertama dengan Arc Tile pertama di sisi kiri, kuantitas sumber kedua dengan Arc Tile kedua, dan seterusnya. Label lain dapat muncul di antara baris-baris tersebut; hitungan kemunculan berlaku secara terpisah untuk setiap label.

Relasi yang hanya berdasarkan label bersifat ambigu. Jika satu daftar berisi tiga baris Arc Tile dan daftar lainnya berisi dua, memasangkan setiap kombinasi yang cocok akan menghasilkan enam kombinasi untuk label tersebut. Pencarian biasa memiliki keterbatasan lain: Microsoft mendokumentasikan bahwa XLOOKUP mengembalikan kecocokan pertama yang ditemukan. Karena itu, pencarian yang hanya menggunakan label secara berulang tidak akan berlanjut ke kemunculan berikutnya. Fungsi XLOOKUP

Solusinya adalah membuat kunci pencarian yang lebih spesifik: Arc Tile|1, Arc Tile|2, dan Arc Tile|3. Setiap kunci bernomor mengidentifikasi satu kemunculan. Metode di bawah ini adalah contoh penerapan menggunakan fungsi Excel yang telah didokumentasikan; dokumentasi Microsoft menjelaskan perilaku fungsi-fungsi tersebut.

Gunakan pendekatan ini saat urutan baris menentukan pemasangannya. Jika baris seharusnya dicocokkan berdasarkan pengenal aset atau kolom revisi terpisah, gunakan informasi tersebut. Nomor kemunculan tidak dapat menentukan pasangan dua catatan jika urutannya tidak bermakna.

Versi Excel untuk Mac mana yang mendukung metode ini?

Rumus utama menggunakan COUNTIF, penggabungan teks dengan &, dan XLOOKUP. Microsoft mencantumkan Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac, dan Excel 2021 untuk Mac di halaman fungsi terkait. Dokumentasi XLOOKUP secara eksplisit menyatakan bahwa fungsi tersebut tidak tersedia di Excel 2016 dan Excel 2019. Fungsi XLOOKUP, Menggunakan fungsi COUNTIF di Microsoft Excel

Mulailah dengan rumus yang disalin ke bawah berikut. Variasi selanjutnya mengembalikan tujuh hasil yang sama dengan satu rumus larik yang meluber. Microsoft mendokumentasikan bahwa rumus yang meluber harus ditempatkan di kisi lembar kerja di luar Tabel Excel. Rumus larik dinamis dan perilaku hasil yang meluber

Siapkan dua daftar aset contoh

Gunakan lembar kerja kosong di satu buku kerja. Masukkan daftar sisi kiri berikut di A1:B8. Label dan kuantitas ini hanya contoh; kuantitas sisi kiri tetap menjadi bagian dari daftar asli dan tidak digunakan untuk menentukan kecocokan.

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

Masukkan daftar sumber di G1:H7. Pertahankan urutan baris yang ditampilkan agar hasil Anda sesuai dengan panduan ini.

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

Tambahkan header Occurrence, Join key, dan Matched source quantity di C1, D1, dan E1. Tambahkan Occurrence, Join key, dan Used by left list di I1, J1, dan K1. Biarkan kolom F kosong untuk memisahkan kedua daftar secara visual.

Untuk contoh ini, gunakan label teks yang tidak kosong dan panjangnya kurang dari 255 karakter, dengan kapitalisasi dan spasi yang konsisten serta tanpa karakter wildcard seperti *, ?, atau ~. Masukkan kuantitas sumber numerik untuk setiap baris sumber, termasuk nol jika sesuai. Prasyarat ini menjaga interpretasi rumus sederhana tetap jelas.

Nomori kemunculan dan buat kunci pencarian

Hitung setiap label dari bagian atas daftarnya sendiri

Masukkan rumus ini di C2, tekan Return, lalu salin ke bawah hingga C8:

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

Endpoint pertama, $A$2, tetap. Endpoint kedua meluas saat Anda menyalin rumus ke bawah. Pada setiap baris, rumus menghitung berapa kali label pada baris tersebut telah muncul hingga baris itu. COUNTIF menghitung sel yang memenuhi kriteria yang diberikan. Menggunakan fungsi COUNTIF di Microsoft Excel

Nomor kemunculan sisi kiri yang dihasilkan seharusnya 1, 1, 2, 1, 2, 3, 1. Secara khusus, Arc Tile ketiga mendapat 3 meskipun baris-barisnya dipisahkan oleh label lain.

Masukkan rumus sumber yang sesuai di I2, lalu salin hingga I7:

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

Nomor kemunculan sumber seharusnya 1, 1, 1, 2, 2, 1. Hitung kedua daftar secara terpisah: nomor kemunculan suatu baris adalah posisinya di antara label yang cocok dalam daftarnya sendiri.

Gabungkan label dan nomor kemunculan

Masukkan rumus ini di D2 dan salin hingga D8:

=A2&"|"&C2

Masukkan rumus ini di J2 dan salin hingga J7:

=G2&"|"&I2

Daftar sisi kiri kini berisi Arc Tile|1, Arc Tile|2, dan Arc Tile|3. Sumber hanya berisi dua kunci pertama. Dengan demikian, kemunculan ketiga yang tidak ditemukan terlihat sebelum kuantitas apa pun dikembalikan.

Kembalikan satu kuantitas sumber untuk setiap baris sisi kiri

Masukkan rumus berikut di E2, lalu salin hingga E8:

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

Rumus ini mencari kunci gabungan sumber, mengembalikan kuantitas sumber yang sesuai, dan menampilkan Unmatched jika kunci bernomor tersebut tidak ada. Argumen terakhir 0 secara eksplisit meminta kecocokan persis, yang juga merupakan nilai bawaan XLOOKUP. Fungsi XLOOKUP

Hasil akhir sisi kiri Anda seharusnya:

Label aset Kuantitas kiri Kemunculan Kunci gabungan Kuantitas sumber yang cocok
Arc Tile 4 1 Arc Tile|1 8
Dot Panel 2 1 Dot Panel|1 12
Arc Tile 6 2 Arc Tile|2 9
Line Print 3 1 Line Print|1 7
Dot Panel 5 2 Dot Panel|2 0
Arc Tile 1 3 Arc Tile|3 Unmatched
Ring Card 2 1 Ring Card|1 Unmatched

Jumlah baris sisi kiri tetap tujuh. Arc Tile kedua mendapat 9, bukan mengulang 8 dari kemunculan pertama. Yang ketiga tidak memiliki pasangan sumber. Ring Card juga tidak memiliki pasangan, sedangkan kuantitas nol yang cocok untuk Dot Panel kedua tetap terlihat.

Bedakan Unmatched dari nol. Dalam contoh ini, nol adalah kuantitas sumber yang sebenarnya; menggunakan nol untuk kecocokan yang tidak ada akan menyamarkan perbedaan tersebut. Demikian pula, selesaikan kuantitas sumber yang kosong sebelum menafsirkan hasil agar sel sumber kosong tidak disalahartikan sebagai kuantitas yang tercatat.

Opsional: kembalikan hasil sebagai satu larik

Setelah memeriksa hasil yang disalin ke bawah, kosongkan hanya E2:E8 dan masukkan alternatif ini di E2:

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

Rumus ini menggunakan ketujuh kunci gabungan sisi kiri sebagai masukan pencarian. Dengan data contoh, hasilnya mengisi E2:E8 dalam urutan daftar sisi kiri. Tekan Return; sel keluaran di sebelahnya harus kosong. Microsoft menyebut penempatan otomatis hasil larik ini sebagai peluberan. Rumus larik dinamis dan perilaku hasil yang meluber

Pilih versi yang disalin ke bawah jika Anda ingin setiap baris memiliki rumus yang dapat diperiksa secara terpisah. Pilih versi yang meluber jika Anda ingin satu rumus mengendalikan kolom hasil. Keduanya menggunakan kunci kemunculan yang sama.

Periksa kecocokan yang hilang dan baris sumber yang tidak digunakan

Mempertahankan jumlah baris sisi kiri bukan berarti semua baris sumber telah digunakan. Tambahkan pemeriksaan balik agar kemunculan sumber yang berlebih tetap terlihat. Di K2, masukkan rumus berikut dan salin hingga K7:

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

Untuk contoh ini, lima baris sumber pertama menghasilkan 1, sedangkan Grid Sheet|1 menghasilkan 0. Angka nol tersebut menunjukkan baris sumber yang tidak memiliki pasangan di sisi kiri. Nilai yang lebih besar dari 1 berarti kunci sumber dirujuk oleh beberapa kunci sisi kiri dan perlu diperiksa.

Gunakan pemeriksaan berikut bersama-sama:

  • Pastikan keluaran tepat mencakup tujuh baris sisi kiri asli.
  • Pastikan urutan kuantitasnya: 8, 12, 9, 7, 0, Unmatched, Unmatched.
  • Pastikan Arc Tile ketiga dan Ring Card tetap terlihat sebagai tidak cocok.
  • Pastikan Grid Sheet|1 ditandai tidak digunakan dalam audit sumber.

Urutan yang diharapkan dan hasil sumber yang tidak digunakan diperiksa dengan perhitungan deterministik sederhana atas logika pemasangan berdasarkan kemunculan. Rumus Excel diperiksa berdasarkan sintaks yang didokumentasikan Microsoft; rumus tersebut tidak dijalankan di Excel untuk Mac.

Atasi masalah hasil sebelum memperluas buku kerja

Pemasangan berubah setelah pengurutan

Rumus kemunculan menggunakan urutan baris saat ini. Mengurutkan salah satu daftar lalu menghitung ulang dapat mengubah baris berulang mana yang mendapat kemunculan 1 atau 2. Tentukan urutan yang dimaksud sebelum memberi nomor. Jika diperlukan pemasangan permanen, pertahankan pengenal stabil yang mencatatnya alih-alih hanya mengandalkan posisi yang dihitung ulang.

Label tampak sama tetapi hasil pencocokannya tidak terduga

Microsoft menyebutkan bahwa COUNTIF mengabaikan kapitalisasi, mengenali kriteria wildcard, dan dapat memberikan hasil tak terduga jika ada spasi di awal atau akhir maupun karakter lain yang tidak diinginkan. Microsoft juga mendokumentasikan keterbatasan saat mencocokkan string yang lebih panjang dari 255 karakter. Seragamkan label di kedua daftar sebelum memberi nomor; label pendek dengan format konsisten di atas menghindari kondisi tersebut. Menggunakan fungsi COUNTIF di Microsoft Excel

Rumus larik menampilkan #SPILL!

Periksa apakah ada isi di E3:E8, termasuk rumus sebelumnya yang disalin ke bawah. Pindahkan apa pun yang perlu dipertahankan, lalu kosongkan penghalangnya. Jika rumus berada di dalam Tabel Excel, gunakan pendekatan yang disalin ke bawah atau pindahkan keluaran yang meluber ke luar Tabel. Ini adalah batasan peluberan yang didokumentasikan. Rumus larik dinamis dan perilaku hasil yang meluber

Anda memerlukan pencarian tanpa XLOOKUP

Kunci bantu yang sama dapat digunakan untuk pencarian INDEX/MATCH. Sebagai alternatif untuk kedua rumus di atas, masukkan rumus ini di E2 dan salin hingga E8:

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

Di sini, MATCH bersama 0 menemukan kunci gabungan yang persis, dan INDEX mengembalikan kuantitas pada posisi tersebut. Kunci yang tidak ditemukan sengaja tetap terlihat sebagai #N/A. Microsoft mendokumentasikan kedua fungsi untuk edisi Mac saat ini. Rumus skalar ini tidak memerlukan keluaran yang meluber. Fungsi MATCH, Fungsi INDEX

Baris baru tidak muncul dalam hasil

Panduan ini menggunakan rentang tetap. Saat menambahkan baris, perluas rumus kemunculan dan kunci gabungan, rentang pencarian dan pengembalian sumber, serta rentang audit balik secara bersamaan. Untuk versi yang meluber, perluas juga masukan pencarian sisi kiri melewati D8. Menambahkan data di bawah rentang tersebut saja tidak akan menyertakannya dalam perhitungan.

Setelah urutan keluaran dan audit balik sesuai dengan urutan yang dimaksud, Anda memiliki penggabungan berbasis kemunculan yang dapat digunakan: setiap baris aset sisi kiri mempertahankan posisinya, menerima paling banyak satu kuantitas sumber, dan tetap menampilkan indikasi yang jelas jika kemunculan pasangannya tidak ada.

Sumber