Gunakan INDEX dengan dua fungsi MATCH padanan tepat untuk mendapatkan kuantiti pada persilangan baris aset dengan lajur format. Panduan ini menyediakan lembaran kerja aset seni yang ringkas, menerangkan formula tersebut dan menunjukkan alternatif XLOOKUP bersarang untuk versi Mac yang disokong.

Kaedah carian manakah yang berfungsi dalam Excel untuk Mac?

Untuk tugas ini, anda mempunyai nama aset, seperti Dot Grid, dan format, seperti PNG. Anda mahu mendapatkan satu kuantiti pada persilangan kedua-duanya. Ini biasanya dipanggil carian dua hala.

Microsoft menyenaraikan INDEX dan MATCH sebagai tersedia dalam Excel untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac dan Excel 2021 untuk Mac. Kedua-dua kaedah dalam artikel ini berfungsi dalam skop Mac yang didokumenkan itu. Lihat dokumentasi fungsi INDEX dan dokumentasi fungsi MATCH Microsoft.

XLOOKUP juga didokumenkan untuk edisi Mac tersebut. Microsoft menyatakan secara jelas bahawa fungsi ini tidak tersedia dalam Excel 2016 dan Excel 2019, walaupun edisi tersebut muncul dalam senarai kebolehgunaan yang lebih luas pada halaman itu. Jika edisi anda tidak mempunyai XLOOKUP, gunakan kaedah INDEX dan MATCH. Microsoft: fungsi XLOOKUP

Mulakan dengan INDEX dan MATCH jika anda mahu melihat kedudukan baris dan lajur secara berasingan semasa mempelajarinya. Pilih XLOOKUP bersarang jika anda sudah menggunakan fungsi itu dan lebih suka padanan tepat lalainya.

Sediakan grid kuantiti aset seni yang ringkas

Dalam lembaran kerja kosong, masukkan data sintetik berikut dalam A1:D5. Lajur paling kiri dalam jadual di bawah mewakili lajur lembaran kerja A; jangan tambah lajur tambahan untuk nombor baris. Kuantiti ini hanyalah contoh.

Aset SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Setiap aset berada dalam satu baris dan setiap format berada dalam satu lajur. Untuk contoh ini, gunakan label aset dan pengepala format yang unik. Masukkan sifar angka secara jelas apabila kuantiti diketahui sifar; semak kuantiti yang belum dipastikan sebelum menggunakan grid.

Cipta ruang carian yang berasingan pada lembaran kerja yang sama:

Sel Masukkan
F1 Aset untuk dicari
F2 Dot Grid
G1 Format untuk dicari
G2 PNG
H1 Kuantiti

Ketiga-tiga julat sumber mempunyai fungsi yang berbeza: A2:A5 mengandungi label aset, B1:D1 mengandungi pengepala format dan B2:D5 mengandungi kuantiti sahaja. Meletakkan tajuk di luar julat kuantiti memastikan kedudukan relatif sejajar dengan betul.

Dapatkan kuantiti dengan INDEX dan dua fungsi MATCH

Pilih H2, masukkan formula lengkap ini dan tekan Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Hasilnya sepatutnya 24: kuantiti untuk Dot Grid dalam lajur PNG. Arahan Microsoft untuk Mac menerangkan cara memilih sel hasil, memasukkan formula yang bermula dengan = dan menekan Return. Microsoft: Masukkan formula

Fahami kedua-dua kedudukan

Fungsi MATCH yang pertama mencari kedudukan aset dalam A2:A5; fungsi kedua mencari kedudukan format dalam B1:D1. Fungsi 0 yang terakhir meminta padanan tepat. Tanpa argumen itu, MATCH lalai kepada padanan anggaran yang memerlukan susunan tertentu. Microsoft: fungsi MATCH

Bagi input yang diberikan, dua pengiraan dalaman ialah:

=MATCH(F2,$A$2:$A$5,0)

Hasil: 2.

=MATCH(G2,$B$1:$D$1,0)

Hasil: 2.

Oleh itu, formula penuh memilih baris 2, lajur 2 dalam blok kuantiti:

=INDEX($B$2:$D$5,2,2)

Itulah sel lembaran kerja C3, yang mengandungi 24. INDEX menggunakan kedudukan dalam julat yang diberikan, jadi baris keduanya ialah baris lembaran kerja 3 di sini. Microsoft mendokumenkan bahawa pemberian kedua-dua kedudukan akan mengembalikan nilai pada persilangannya. Microsoft: fungsi INDEX

Kekalkan julat sumber apabila menyalin formula

Tanda dolar mengunci julat sumber. Rujukan input F2 dan G2 kekal relatif, jadi apabila H2 disalin ke bawah hingga H3, formula seterusnya membaca F3 dan G3 sambil menggunakan grid sumber yang sama. Pada Mac, anda juga boleh memilih rujukan dalam bar formula dan menekan Command+T untuk menukar jenis rujukan. Microsoft: Tukar antara rujukan relatif dan mutlak

Contohnya, masukkan Tile Arc dalam F3 dan JPG dalam G3, kemudian salin H2 ke dalam H3. Hasil baharu sepatutnya 6. Ini memberi anda senarai permintaan ringkas tanpa mengubah grid aset asal.

Gunakan XLOOKUP bersarang dalam versi Mac yang disokong

Dengan lembaran kerja dan input carian yang sama, anda boleh memasukkan formula ini dalam H2:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Formula ini mengembalikan 24 untuk Dot Grid dan PNG. Dokumentasi Microsoft menyertakan contoh XLOOKUP bersarang yang melakukan carian menegak dan mendatar untuk mendapatkan nilai pada persilangan. Padanan tepat ialah tetapan lalai. Microsoft: fungsi XLOOKUP

Dalam contoh ini, carian dalaman memilih lajur kuantiti PNG: 18, 24, 9 dan 14. Carian luaran memilih entri yang sejajar dengan Dot Grid. Penggunaan tiga julat sumber yang sama memudahkan perbandingan kaedah ini dengan formula sebelumnya.

Semak hasil sebelum menggunakan grid yang lebih besar

Gunakan kes berikut sebagai semakan penerimaan ringkas. Tukar F2 dan G2 bagi setiap baris, kemudian bandingkan H2 dengan hasil yang dijangka. Pulihkan Dot Grid dan PNG selepas itu jika anda mahu mengekalkan contoh asal.

Input aset Input format Hasil dijangka Perkara yang disemak
Dot Grid PNG 24 Persilangan tengah
Tile Arc JPG 6 Aset pertama dan format terakhir
Soft Square SVG 7 Aset terakhir dan format pertama
Loop Ribbon JPG 0 Kuantiti sifar yang dinyatakan secara jelas
Tile Arcs PNG #N/A Label aset tiada
Dot Grid TIFF #N/A Pengepala format tiada

Jika nombor salah, periksa sempadan julat sebelum menukar kaedah carian. Untuk grid ini, A2:A5 mesti sejajar dengan empat baris B2:D5, manakala B1:D1 mesti sejajar dengan tiga lajurnya. Contohnya, jika A1 dimasukkan hanya dalam julat carian aset, kedudukan yang dikira akan tersasar daripada baris kuantiti yang dimaksudkan.

Apabila melanjutkan grid khusus ini dengan aset lain pada baris 6, kemas kini julat aset kepada A2:A6 dan blok kuantiti kepada B2:D6. Apabila menambah format pada lajur E, kemas kini julat pengepala kepada B1:E1 dan blok kuantiti kepada B2:E5. Kedua-dua perubahan berpasangan ini mengekalkan susun atur yang digunakan oleh formula.

Label pendua juga perlu diberi perhatian. MATCH padanan tepat mengembalikan kedudukan padanan pertama dan tidak membezakan huruf besar daripada huruf kecil; fungsi ini juga mentafsir * dan ? sebagai kad bebas dalam carian teks. Gunakan label unik tanpa aksara tersebut untuk persediaan ini. Microsoft: fungsi MATCH

Paparkan mesej yang berguna apabila label tiada

Selepas carian asas berfungsi, anda boleh menggantikan ralat padanan tiada dengan mesej yang mudah difahami:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA mengembalikan mesej anda apabila formula di dalamnya menghasilkan #N/A; jika tidak, fungsi ini mengekalkan hasilnya. Fungsi ini didokumenkan untuk Microsoft 365 untuk Mac, Excel 2024 untuk Mac dan Excel 2021 untuk Mac. Microsoft: fungsi IFNA

Dalam contoh ini, Tile Arcs menghasilkan Semak aset atau format, manakala Loop Ribbon dengan JPG masih mengembalikan 0. Perbezaan ini memisahkan label yang tiada daripada kuantiti sifar yang direkodkan. Jika kuantiti sumber itu sendiri mengandungi #N/A, pembalut ini juga akan menggantikan ralat tersebut, jadi periksa sel sumber jika label kelihatan betul.

Untuk kegunaan harian, pastikan nama aset unik, kekalkan penjajaran ketiga-tiga julat sumber dan semak beberapa persilangan yang diketahui selepas mengubah grid. Anda kemudian boleh mendapatkan setiap kuantiti aset seni yang diminta dengan menukar input aset dan format.

Sumber