Gunakan pencarian dengan kecocokan persis untuk menambahkan metadata aset ke setiap baris inventaris, termasuk ID aset yang berulang. Di Excel untuk Mac, Anda dapat membuat satu hasil yang meluas dengan HSTACK dan XLOOKUP, atau menggunakan kolom pencarian terpisah di Excel 2021.

Panduan ini ditujukan bagi pengguna desktop Mac yang memiliki banyak entri inventaris aset seni dan satu baris metadata untuk setiap aset. Tugasnya adalah menambahkan judul, format, dan palet setiap aset sambil mempertahankan urutan asli inventaris, kuantitas, dan entri yang berulang. Semua data contoh di bawah ini bersifat sintetis.

Versi Excel untuk Mac mana yang mendukung pendekatan ini?

Periksa instalasi Anda melalui Excel → Tentang Microsoft Excel. Petunjuk Microsoft untuk Mac menjelaskan bahwa dialog Tentang menampilkan nomor versi dan jenis lisensi aplikasi. Periksa keduanya saat memilih rumus. Tentang Office: Versi Office apa yang saya gunakan?

Edisi Mac Pendekatan dalam artikel ini
Excel untuk Microsoft 365 untuk Mac Satu hasil gabungan menggunakan HSTACK dan XLOOKUP
Excel 2024 untuk Mac Satu hasil gabungan menggunakan HSTACK dan XLOOKUP
Excel 2021 untuk Mac Tiga kolom luapan XLOOKUP terpisah
Excel 2019 atau 2016 Rumus ini memerlukan edisi yang lebih baru

Microsoft mencantumkan Microsoft 365 untuk Mac dan Excel 2024 untuk Mac dalam dokumentasi HSTACK. Dokumentasi fitur Excel 2021 untuk Mac mengonfirmasi XLOOKUP dan larik dinamis. Dokumentasi XLOOKUP secara eksplisit tidak mencakup Excel 2016 dan 2019.

Jika Anda menggunakan Excel 2021, pakai metode kolom terpisah di bawah ini. Metode tersebut memberikan hasil pengayaan yang sama tanpa memerlukan HSTACK.

Siapkan inventaris dan metadata dengan ID aset yang sama

Buat dua lembar kerja dalam buku kerja yang sama bernama Inventory dan Metadata. Masukkan data inventaris berikut di Inventory!A1:B6. Untuk contoh ini, biarkan sel-sel tersebut sebagai rentang lembar kerja biasa.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Masukkan metadata di Metadata!A1:D4:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

ID inventaris yang berulang memang disengaja: dua entri dapat menjelaskan kuantitas terpisah dari aset yang sama. Lembar metadata menyediakan deskripsi bersama satu kali. Jadi, keluaran Anda harus memuat lima baris data, meskipun metadata hanya berisi tiga aset.

Gunakan ID aset yang secara konsisten mengidentifikasi aset yang sama di kedua lembar. Untuk contoh ini, setiap ID dan bidang metadata telah diisi. Dalam buku kerja Anda sendiri, tentukan cara menyatakan bidang yang tidak diketahui secara eksplisit—misalnya, dengan teks Unknown—agar dapat dibedakan dari aset yang tidak memiliki kecocokan.

Periksa apakah setiap ID inventaris memiliki tepat satu kecocokan metadata

Di Inventory, masukkan MetadataMatches di C1. Masukkan rumus ini di C2, lalu salin hingga C6:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Tafsirkan hasilnya sebelum membuat keluaran:

  • 0: ID aset tidak ada di lembar metadata.
  • 1: aset memiliki satu baris metadata seperti yang diharapkan dalam alur kerja ini.
  • Angka apa pun yang lebih besar dari 1: metadata berisi ID duplikat yang perlu dibereskan.

COUNTIF menghitung sel yang memenuhi kriteria. Untuk data yang diberikan, setiap pemeriksaan menghasilkan 1. Gunakan ID sederhana seperti contoh di sini: COUNTIF memperlakukan tanda bintang dan tanda tanya sebagai karakter wildcard, sehingga ID yang berisi karakter tersebut memerlukan penanganan tambahan. Menggunakan fungsi COUNTIF di Microsoft Excel

Selesaikan masalah ID metadata duplikat sebelum melanjutkan. Secara default, XLOOKUP mengembalikan kecocokan pertama, jadi hasil yang terisi saja tidak membuktikan bahwa kunci metadata bersifat unik. Fungsi XLOOKUP

Buat satu larik yang diperkaya di Microsoft 365 atau Excel 2024

Buat lembar kerja bernama Enriched. Masukkan lima judul berikut di A1:E1: AssetID, Quantity, Title, Format, Palette. Biarkan A2:E6 kosong.

Pilih Enriched!A2, masukkan rumus lengkap di bawah ini, lalu tekan Return:

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Rumus ini menggabungkan dua kolom inventaris asli dengan tiga kolom hasil pencarian. Setiap pencarian menggunakan lima ID inventaris yang sama agar bidang tambahan tetap sejajar dengan entri aslinya. HSTACK menambahkan larik secara horizontal sesuai urutan yang diberikan. Fungsi HSTACK

0 terakhir dalam setiap pencarian meminta kecocokan persis. Teks MISSING METADATA menjadi hasil untuk ID yang tidak cocok. Dengan menampilkannya, Anda dapat menemukan kecocokan yang belum lengkap alih-alih membiarkan sel kosong tanpa penjelasan. Fungsi XLOOKUP

Keluaran yang diharapkan menempati Enriched!A2:E6:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Perhatikan bahwa kedua entri ARC-01 tetap ada, dengan kuantitas 3 dan 2. Keduanya memperoleh Arc Study, Vector, dan Teal. Metadata memperkaya entri tersebut tanpa menggabungkannya.

Rumus ini sengaja mencari satu bidang metadata setiap kali sebelum menggabungkan hasilnya. Dengan begitu, bentuk keluaran menjadi jelas: lima baris inventaris, dua kolom asli, dan tiga kolom tambahan.

Gunakan tiga kolom pencarian di Excel 2021 untuk Mac

Untuk Excel 2021, kerjakan langsung di samping inventaris asli. Pertahankan pemeriksaan jumlah kecocokan di kolom C, lalu masukkan Title, Format, dan Palette di D1:F1. Biarkan D2:F6 kosong.

Masukkan rumus ini di Inventory!D2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Masukkan rumus ini di Inventory!E2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Masukkan rumus ini di Inventory!F2:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Tekan Return setelah setiap rumus. Setiap hasil mengisi lima baris di bawah sel awalnya. ID aset dan kuantitas tetap berada di kolom A:B, jumlah diagnostik tetap berada di C, dan metadata tambahan muncul di D:F. Excel 2021 untuk Mac menyertakan perilaku larik dinamis yang diperlukan untuk pendekatan ini. Yang baru di Excel 2021 untuk Mac

Sesuaikan buku kerja untuk baris inventaris yang lebih banyak

Contoh ini menggunakan rentang tetap agar setiap referensi terlihat. Saat inventaris bertambah, perluas setiap referensi inventaris hingga baris terakhir yang sama. Misalnya, jika inventaris berakhir pada baris 501, rumus gabungan memerlukan Inventory!A2:B501 dan tiga referensi Inventory!A2:A501. Perluas pemeriksaan jumlah kecocokan hingga baris yang sama.

Saat metadata bertambah, perluas semua referensi metadata secara bersamaan. Jika baris terakhirnya menjadi 101, gunakan Metadata!A2:A101 untuk kunci dan rentang hasil terkait B2:B101, C2:C101, serta D2:D101. Sertakan data yang benar-benar ada, bukan menambahkan banyak baris yang tidak digunakan.

Untuk buku kerja yang sering bertambah, pertimbangkan untuk mengubah rentang sumber menjadi tabel Excel dan menggunakan referensi terstruktur. Microsoft menjelaskan bahwa referensi tersebut akan menyesuaikan saat baris sumber ditambahkan atau dihapus. Letakkan keluaran yang meluap di kisi lembar kerja, di luar tabel mana pun. Rumus larik dinamis dan perilaku larik yang meluap

Jika Anda memerlukan pengayaan di dalam tabel Excel, gunakan rumus satu hasil terpisah untuk setiap kolom metadata dan baris inventaris. Tabel mendukung kolom terhitung yang menerapkan rumus di seluruh kolom; hasil luapan yang mencakup beberapa sel harus ditempatkan di luar tabel. Gambaran umum tabel Excel

Diagnosis masalah dan verifikasi hasil akhir

Jika Excel menampilkan #SPILL!, periksa sel tujuan yang dimaksud. Pindahkan konten yang sudah ada jika perlu disimpan, dan pastikan rumus berada di luar tabel Excel. Hanya sel kiri atas dari hasil yang meluap yang dapat diedit; ubah rumus di sana. Microsoft mendokumentasikan batas luapan dan kesalahan akibat halangan ini. Rumus larik dinamis dan perilaku larik yang meluap

Jika suatu baris menampilkan MISSING METADATA, periksa kembali jumlah kecocokannya. Nilai nol berarti Anda perlu menambahkan catatan metadata yang hilang atau memperbaiki ID aset. Periksa apakah ada spasi tak sengaja di awal atau akhir saat label yang tampak sama tidak berperilaku seperti yang diharapkan; Microsoft menyebutnya sebagai salah satu penyebab hasil COUNTIF yang tidak terduga. Menggunakan fungsi COUNTIF di Microsoft Excel

Untuk pengaturan ini, simpan kedua lembar sumber dalam buku kerja yang sama. Jika kemudian sumber dipindahkan ke buku kerja lain, perhatikan batasan yang didokumentasikan Microsoft: larik dinamis yang tertaut memerlukan kedua buku kerja tetap terbuka, dan penyegaran saat sumber tertutup dapat menghasilkan #REF!. Rumus larik dinamis dan perilaku larik yang meluap

Verifikasi contoh sintetis dengan tiga pemeriksaan konkret:

  1. Ada lima baris data keluaran dengan urutan yang sama seperti inventaris asli.
  2. Kuantitas tetap 3, 5, 2, 4, 1, dengan total 15.
  3. ID yang berulang memperoleh metadata yang sama, dan setiap pemeriksaan jumlah kecocokan bernilai 1.

Pemeriksaan ini memastikan hasil yang diharapkan: setiap entri inventaris mempertahankan posisi dan kuantitasnya sambil menerima deskripsi yang sesuai dengan ID asetnya. Untuk buku kerja yang lebih besar, ulangi pemeriksaan yang sama setelah memperluas rentang atau menambahkan catatan metadata.

Sumber