Gunakan carian padanan tepat untuk menambah metadata aset pada setiap baris inventori, termasuk ID aset yang berulang. Dalam Excel untuk Mac, anda boleh membina satu hasil limpahan dengan HSTACK dan XLOOKUP, atau menggunakan lajur carian berasingan dalam Excel 2021.

Panduan ini ditujukan kepada pengguna desktop Mac yang mempunyai banyak entri inventori aset seni dan satu baris metadata bagi setiap aset. Tugasnya adalah melampirkan tajuk, format dan palet setiap aset sambil mengekalkan susunan asal, kuantiti dan entri berulang dalam inventori. Semua data contoh di bawah adalah rekaan.

Versi Excel untuk Mac yang manakah menyokong pendekatan ini?

Semak pemasangan anda melalui Excel → About Microsoft Excel. Arahan Microsoft untuk Mac menjelaskan bahawa dialog About aplikasi memaparkan nombor versi dan jenis lesen. Semak kedua-duanya semasa memilih formula. About Office: Apakah versi Office 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 lajur limpahan XLOOKUP berasingan
Excel 2019 atau 2016 Formula ini memerlukan edisi yang lebih baharu

Microsoft menyenaraikan Microsoft 365 untuk Mac dan Excel 2024 untuk Mac dalam dokumentasi HSTACK. Dokumentasi ciri Excel 2021 untuk Mac mengesahkan XLOOKUP dan tatasusunan dinamik. Dokumentasi XLOOKUP secara jelas tidak menyertakan Excel 2016 dan 2019.

Jika anda menggunakan Excel 2021, gunakan kaedah lajur berasingan di bawah. Kaedah ini memberikan hasil pengayaan yang sama tanpa memerlukan HSTACK.

Sediakan inventori dan metadata dengan ID aset yang sama

Cipta dua lembaran kerja dalam buku kerja yang sama bernama Inventory dan Metadata. Masukkan data inventori berikut dalam Inventory!A1:B6. Untuk contoh ini, kekalkan sel ini sebagai julat lembaran kerja biasa.

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

Masukkan metadata dalam 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 inventori yang berulang memang disengajakan: dua entri boleh menerangkan kuantiti berasingan bagi aset yang sama. Lembaran metadata menyediakan perihalan yang dikongsi sekali sahaja. Oleh itu, output anda sepatutnya mengandungi lima baris data, walaupun metadata hanya mengandungi tiga aset.

Gunakan ID aset yang mengenal pasti aset yang sama secara konsisten dalam kedua-dua lembaran. Untuk contoh ini, setiap ID dan medan metadata diisi. Dalam buku kerja anda sendiri, tentukan cara untuk mewakili medan yang tidak diketahui dengan jelas—contohnya, dengan teks Unknown—supaya ia dapat dibezakan daripada padanan aset yang tiada.

Pastikan setiap ID inventori mempunyai tepat satu padanan metadata

Dalam Inventory, masukkan MetadataMatches dalam C1. Masukkan formula ini dalam C2, kemudian salinnya hingga C6:

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

Tafsirkan hasilnya sebelum membina output:

  • 0: ID aset tiada dalam lembaran metadata.
  • 1: aset mempunyai satu baris metadata seperti yang dijangkakan dalam aliran kerja ini.
  • Sebarang nombor yang lebih besar daripada 1: metadata mengandungi ID pendua yang perlu diselesaikan.

COUNTIF mengira sel yang memenuhi kriteria. Bagi data yang diberikan, setiap semakan menghasilkan 1. Gunakan ID ringkas seperti yang ditunjukkan di sini: COUNTIF menganggap tanda bintang dan tanda soal sebagai aksara kad bebas, jadi ID yang mengandungi aksara ini memerlukan pengendalian tambahan. Gunakan fungsi COUNTIF dalam Microsoft Excel

Selesaikan ID metadata pendua sebelum meneruskan. Secara lalai, XLOOKUP memulangkan padanan pertama, jadi hasil yang terisi sahaja tidak dapat mengesahkan bahawa kunci metadata itu unik. Fungsi XLOOKUP

Bina satu tatasusunan diperkaya dalam Microsoft 365 atau Excel 2024

Cipta lembaran kerja bernama Enriched. Masukkan lima tajuk ini dalam A1:E1: AssetID, Quantity, Title, Format, Palette. Kosongkan A2:E6.

Pilih Enriched!A2, masukkan formula lengkap di bawah dan 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)
)

Formula ini menggabungkan dua lajur inventori asal dengan tiga lajur hasil carian. Setiap carian menggunakan lima ID inventori yang sama, supaya medan yang ditambah kekal sejajar dengan entri asal. HSTACK menambahkan tatasusunan secara mendatar mengikut susunan yang diberikan. Fungsi HSTACK

Argumen 0 terakhir dalam setiap carian meminta padanan tepat. Teks MISSING METADATA ialah hasil bagi ID yang tiada padanan. Dengan mengekalkan teks itu supaya kelihatan, anda boleh mengenal pasti padanan yang tidak lengkap dan bukannya membiarkan sel kosong tanpa penjelasan. Fungsi XLOOKUP

Output yang dijangka memenuhi 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 bahawa kedua-dua entri ARC-01 kekal, dengan kuantiti 3 dan 2. Kedua-duanya menerima Arc Study, Vector dan Teal. Metadata telah memperkayakan entri tersebut tanpa menggabungkannya.

Formula ini sengaja mencari satu medan metadata pada satu masa sebelum menggabungkan hasilnya. Dengan cara ini, bentuk output dinyatakan dengan jelas: lima baris inventori, dua lajur asal dan tiga lajur tambahan.

Gunakan tiga lajur carian dalam Excel 2021 untuk Mac

Untuk Excel 2021, gunakan ruang di sebelah inventori asal. Kekalkan semakan bilangan padanan dalam lajur C, kemudian masukkan Title, Format dan Palette dalam D1:F1. Kosongkan D2:F6.

Masukkan formula ini dalam Inventory!D2:

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

Masukkan formula ini dalam Inventory!E2:

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

Masukkan formula ini dalam Inventory!F2:

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

Tekan Return selepas setiap formula. Setiap hasil mengisi lima baris di bawah sel permulaannya. ID aset dan kuantiti anda kekal dalam lajur A:B, kiraan diagnostik kekal dalam C, dan metadata tambahan muncul dalam D:F. Excel 2021 untuk Mac menyertakan fungsi tatasusunan dinamik yang diperlukan untuk pendekatan ini. Perkara baharu dalam Excel 2021 untuk Mac

Sesuaikan buku kerja untuk lebih banyak baris inventori

Contoh ini menggunakan julat tetap supaya setiap rujukan dapat dilihat. Apabila inventori anda bertambah, lanjutkan setiap rujukan inventori hingga baris terakhir yang sama. Contohnya, jika inventori berakhir pada baris 501, formula gabungan memerlukan Inventory!A2:B501 dan tiga contoh Inventory!A2:A501. Lanjutkan semakan bilangan padanan hingga baris yang sama.

Apabila metadata bertambah, lanjutkan semua rujukan metadata bersama-sama. Jika baris terakhirnya menjadi 101, gunakan Metadata!A2:A101 untuk kunci dan julat pemulangan yang sepadan B2:B101, C2:C101 dan D2:D101. Sertakan rekod sebenar dan bukannya menambah blok besar baris yang tidak digunakan.

Bagi buku kerja yang kerap bertambah, pertimbangkan untuk menukar julat sumber kepada jadual Excel dan menggunakan rujukan berstruktur. Microsoft mendokumenkan bahawa rujukan ini dilaraskan apabila baris sumber ditambah atau dibuang. Kekalkan output limpahan dalam grid lembaran kerja di luar sebarang jadual. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan

Jika anda memerlukan pengayaan dalam jadual Excel, gunakan formula hasil tunggal yang berasingan bagi setiap lajur metadata dan baris inventori. Jadual menyokong lajur terhitung yang menggunakan formula sepanjang lajur; limpahan berbilang sel perlu berada di luar jadual. Gambaran keseluruhan jadual Excel

Diagnosis masalah dan sahkan hasil akhir

Jika Excel memaparkan #SPILL!, periksa sel destinasi yang dimaksudkan. Alihkan sebarang kandungan sedia ada yang perlu dikekalkan, dan pastikan formula berada di luar jadual Excel. Hanya sel kiri atas bagi hasil limpahan boleh diedit; ubah formula di situ. Microsoft mendokumenkan sempadan limpahan dan ralat halangan ini. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan

Jika sesuatu baris memaparkan MISSING METADATA, semak semula kiraan padanannya. Nilai sifar bermakna anda perlu menambah rekod metadata yang tiada atau membetulkan ID aset. Periksa ruang kosong yang tertinggal di hadapan atau belakang jika label yang kelihatan serupa tidak berfungsi seperti yang dijangka; Microsoft mengenal pasti ruang ini sebagai punca hasil COUNTIF yang tidak dijangka. Gunakan fungsi COUNTIF dalam Microsoft Excel

Kekalkan kedua-dua lembaran kerja sumber dalam buku kerja yang sama untuk persediaan ini. Jika anda kemudian memindahkan sumber ke buku kerja lain, ambil kira batasan yang didokumenkan oleh Microsoft: tatasusunan dinamik berpaut memerlukan kedua-dua buku kerja dibuka, dan penyegaran apabila sumber ditutup boleh menghasilkan #REF!. Formula tatasusunan dinamik dan tingkah laku tatasusunan limpahan

Sahkan contoh rekaan ini dengan tiga semakan konkrit:

  1. Terdapat lima baris data output mengikut susunan asal inventori.
  2. Kuantiti kekal 3, 5, 2, 4, 1, dengan jumlah 15.
  3. ID berulang menerima metadata yang sama, dan setiap semakan bilangan padanan ialah 1.

Semakan ini mengesahkan hasil yang dimaksudkan: setiap entri inventori mengekalkan kedudukan dan kuantitinya sambil menerima perihalan yang sepadan dengan ID asetnya. Bagi buku kerja yang lebih besar, ulangi semakan yang sama selepas melanjutkan julat atau menambah rekod metadata.

Sumber