Fungsi xmatch excel dengan contoh formula

Tutorial memperkenalkan fungsi Excel Xmatch baru dan menunjukkan bagaimana ia lebih baik daripada perlawanan untuk menyelesaikan beberapa tugas biasa.

Dalam Excel 365, fungsi Xmatch telah ditambah untuk menggantikan fungsi perlawanan. Tetapi sebelum anda mula menaik taraf formula anda yang sedia ada, adalah bijak untuk memahami semua kelebihan fungsi baru dan bagaimana ia berbeza dari yang lama.

Ringkasnya, fungsi Xmatch adalah sama seperti perlawanan tetapi lebih fleksibel dan mantap. Ia boleh melihat kedua-dua array menegak dan mendatar, cari pertama-to-terakhir atau terakhir-ke-pertama, cari perlawanan yang tepat, anggaran dan separa, dan gunakan algoritma carian binari yang lebih cepat.

• Fungsi Xmatch di Excel
• Formula Xmatch Asas Excel
• Cara Menggunakan Xmatch dalam Excel - Contoh Formula
  • Perlawanan tepat berbanding perlawanan anggaran
  • Perlawanan separa dengan kad liar
  • Xmatch carian terbalik
  • Bandingkan dua lajur untuk perlawanan
  • Formula xmatch indeks
  • Indeks Xmatch Xmatch Formula
  • Formula Xmatch yang sensitif kes
• Xmatch vs Match di Excel

Fungsi Excel Xmatch

Fungsi Xmatch dalam Excel mengembalikan kedudukan relatif nilai dalam array atau pelbagai sel.

Ia mempunyai sintaks berikut:

Xmatch (lookup_value, lookup_array, [match_mode], [search_mode])

Di mana:

Lookup_value (diperlukan) - Nilai untuk dicari.

Lookup_array (diperlukan) - pelbagai atau pelbagai sel di mana untuk mencari.

Match_mode (pilihan) - Menentukan jenis perlawanan yang hendak digunakan:

• 0 atau ditinggalkan (lalai) - Perlawanan tepat
• -1 - Perlawanan tepat atau nilai terkecil seterusnya
• 1 - Perlawanan tepat atau nilai terbesar seterusnya
• 2 - Perlawanan Wildcard (*,?)

Carian_mode (pilihan) - Menentukan arah carian dan algoritma:

• 1 atau ditinggalkan (lalai) - Cari dari pertama hingga terakhir.
• -1 - Cari dalam urutan terbalik dari yang terakhir hingga pertama.
• 2 - Carian Binari Menaik. Memerlukan lookup_array untuk disusun mengikut urutan menaik.
• -2 - Carian Perduaan Descending. Memerlukan lookup_array untuk disusun mengikut urutan menurun.

Carian binari adalah algoritma yang lebih cepat yang berfungsi dengan cekap pada susunan yang disusun. Untuk maklumat lanjut, sila lihat mod carian.

Versi Excel mana yang mempunyai xmatch?

Fungsi Xmatch hanya tersedia dalam Excel untuk Microsoft 365 dan Excel 2021. Dalam Excel 2019, Excel 2016 dan versi terdahulu, fungsi ini tidak disokong.

Formula Xmatch Asas di Excel

Untuk mendapatkan idea umum tentang apa fungsi yang mampu, mari kita membina formula Xmatch ia bentuk yang paling mudah, hanya menentukan dua hujah yang diperlukan dan meninggalkan pilihan pilihan untuk kegagalan mereka.

Katakan, anda mempunyai senarai lautan yang disenaraikan dengan saiz mereka (C2: C6) dan anda ingin mencari pangkat lautan tertentu. Untuk melakukannya, gunakan nama lautan, katakan India , sebagai nilai carian dan keseluruhan senarai nama sebagai array carian:

=XMATCH("Indian", C2:C6)

Untuk menjadikan formula lebih fleksibel, masukkan lautan yang menarik di beberapa sel, katakan F1:

=XMATCH(F1, C2:C6)

Hasilnya, anda mendapat formula Xmatch untuk melihat dalam pelbagai menegak . Output adalah kedudukan relatif nilai carian dalam array, yang dalam kes kita sepadan dengan pangkat lautan:

Formula yang sama berfungsi dengan sempurna untuk array mendatar juga. Yang perlu anda lakukan ialah menyesuaikan rujukan lookup_array :

=XMATCH(B5, B1:F1)

Excel Xmatch Fungsi - Perkara Yang Perlu Diingat

Untuk menggunakan Xmatch dengan berkesan dalam lembaran kerja anda dan mencegah hasil yang tidak dijangka, sila ingat 3 fakta mudah ini:

• Sekiranya terdapat dua atau lebih kejadian nilai carian dalam array carian, kedudukan perlawanan pertama dikembalikan jika argumen carian_mode ditetapkan kepada 1 atau ditinggalkan. Dengan search_mode ditetapkan ke -1, fungsi mencari dalam urutan terbalik dan mengembalikan kedudukan perlawanan terakhir seperti yang ditunjukkan dalam contoh ini.
• Jika nilai carian tidak dijumpai , ralat #N/A berlaku.
• Fungsi Xmatch adalah kes-tidak sensitif secara semula jadi dan tidak dapat membezakan kes huruf. Untuk membezakan huruf kecil dan huruf besar, gunakan formula XMatch yang sensitif ini.

Cara Menggunakan Xmatch dalam Excel - Contoh Formula

Contoh -contoh berikut akan membantu anda mendapatkan lebih banyak pemahaman mengenai fungsi Xmatch dan kegunaan praktikalnya.

Perlawanan tepat berbanding perlawanan anggaran

Tingkah laku yang sepadan dengan xmatch dikawal oleh argumen match_mode pilihan:

• 0 atau ditinggalkan (lalai) - Formula hanya mencari padanan tepat. Jika perlawanan tepat tidak dijumpai, ralat #N/A dikembalikan.
• -1 - Formula mencari padanan tepat terlebih dahulu, dan kemudian untuk item yang lebih kecil seterusnya.
• 1 - Formula mencari padanan tepat terlebih dahulu, dan kemudian untuk item yang lebih besar seterusnya.

Dan sekarang, mari kita lihat bagaimana mod perlawanan yang berbeza mempengaruhi hasil formula. Katakan anda ingin mengetahui di mana kawasan tertentu, katakan 80,000,000 km ² , berdiri di antara semua lautan.

Perlawanan tepat

Jika anda menggunakan 0 untuk match_mode , anda akan mendapat ralat #n/a, kerana formula tidak dapat mencari nilai yang sama dengan nilai carian:

=XMATCH(80000000, C2:C6, 0)

Item terkecil seterusnya

Jika anda menggunakan -1 untuk match_mode , formula akan kembali 3, kerana padanan terdekat lebih kecil daripada nilai carian ialah 70,560,000, dan ia adalah item 3 ^RD dalam array carian:

=XMATCH(80000000, C2:C6, -1)

Item terbesar seterusnya

Jika anda menggunakan 1 untuk Match_mode , formula akan mengeluarkan 2, kerana perlawanan terdekat yang lebih besar daripada nilai carian ialah 85,133,000, iaitu item 2 ^ND dalam array carian:

=XMATCH(80000000, C2:C6, -1)

Imej di bawah menunjukkan semua hasilnya:

Cara Memadankan Teks Separa dalam Excel dengan Wildcard

Fungsi Xmatch mempunyai mod perlawanan khas untuk Wildcards: Argumen Match_mode ditetapkan kepada 2.

Dalam mod perlawanan wildcard, formula xmatch menerima watak -watak wildcard berikut:

• Tanda Soalan (?) Untuk memadankan mana -mana watak tunggal.
• Asterisk (*) untuk memadankan sebarang urutan aksara.

Perlu diingat bahawa kad liar hanya bekerja dengan teks, bukan nombor.

Sebagai contoh, untuk mencari kedudukan item pertama yang bermula dengan "Selatan", formula adalah:

=XMATCH("south*", B2:B6, 2)

Atau anda boleh menaip ekspresi kad liar anda di beberapa sel, katakan F1, dan membekalkan rujukan sel untuk argumen lookup_value :

=XMATCH(F1, B2:B6, 2)

Dengan kebanyakan fungsi Excel, anda akan menggunakan Tilde (~) untuk merawat asterisk (~*) atau tanda tanya (~?) Sebagai watak literal, bukan kad liar. Dengan Xmatch, Tilde tidak diperlukan. Jika anda tidak menentukan mod perlawanan wildcard, xmatch akan menganggapnya? Dan * adalah watak biasa.

Sebagai contoh, formula di bawah akan mencari julat A2: A7 tepat untuk watak asterisk:

=XMATCH("*", A2:A7)

Carian terbalik xmatch untuk mencari perlawanan terakhir

Sekiranya terdapat beberapa kejadian nilai carian dalam pelbagai carian, anda kadang -kadang perlu mendapatkan kedudukan kejadian terakhir .

Arah carian dikawal menjadi hujah ^ke -4 dari xmatch bernama Search_Mode . Untuk mencari dalam urutan terbalik, iaitu dari bawah ke atas dalam array menegak dan dari kanan ke kiri dalam array mendatar, carian_mode harus ditetapkan ke -1.

Dalam contoh ini, kami akan mengembalikan kedudukan rekod terakhir untuk nilai carian tertentu (sila lihat tangkapan skrin di bawah). Untuk ini, sediakan hujah -hujah seperti berikut:

• Lookup_Value - Jurujual Sasaran di H1
• Lookup_Array - Nama Jurujual di C2: C10
• Match_mode adalah 0 atau ditinggalkan (padanan tepat)
• Carian_mode adalah -1 (terakhir-ke-pertama)

Meletakkan empat hujah bersama, kami mendapat formula ini:

=XMATCH(H1, C2:C10, 0, -1)

Yang mengembalikan bilangan jualan terakhir yang dibuat oleh Laura:

Cara membandingkan dua lajur dalam Excel untuk perlawanan

Untuk membandingkan dua senarai untuk perlawanan, anda boleh menggunakan fungsi Xmatch bersama -sama dengan IF dan ISNA:

Jika (isNA (xmatch ( sasaran_list , search_list , 0)), "tiada perlawanan", "perlawanan")

Sebagai contoh, untuk membandingkan Senarai 2 dalam B2: B10 terhadap Senarai 1 dalam A2: A10, formula mengambil bentuk berikut:

=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")

Dalam contoh ini, kita mengenal pasti hanya perlawanan, oleh itu argumen value_if_true dari fungsi IF adalah rentetan kosong ("").

Masukkan formula di atas di sel paling atas (C2 dalam kes kami), tekan Enter , dan ia akan "menumpahkan" ke dalam sel -sel lain secara automatik (ia dipanggil pelbagai tumpahan):

Bagaimana formula ini berfungsi

Di tengah -tengah formula, fungsi Xmatch mencari nilai dari senarai 2 dalam senarai 1. Jika nilai dijumpai, kedudukan relatifnya dikembalikan, jika tidak, ralat #N/A. Dalam kes kami, hasil Xmatch adalah array berikut:

{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}

Arahan ini adalah "diberi makan" kepada fungsi ISNA untuk diperiksa untuk kesilapan #N/A. Untuk setiap #N/A ralat, ISNA kembali benar; Untuk apa -apa nilai lain - palsu. Hasilnya, ia menghasilkan pelbagai nilai logik berikut, di mana True mewakili pertandingan, dan False mewakili perlawanan:

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

Arahan di atas pergi ke ujian logik fungsi IF. Bergantung pada bagaimana anda mengkonfigurasi dua hujah terakhir, formula akan mengeluarkan teks yang sepadan. Dalam kes kami, ia adalah rentetan kosong ("") untuk bukan perlawanan ( value_if_true ) dan "perlawanan dalam senarai 1" untuk perlawanan ( value_if_false ).

Nota. Formula ini hanya berfungsi dalam Excel 365 dan Excel 2021 yang menyokong tatasusunan dinamik. Jika anda menggunakan Excel 2019, Excel 2016 atau versi terdahulu, sila lihat penyelesaian lain: Cara membandingkan dua lajur dalam Excel.

Indeks Xmatch di Excel

Xmatch boleh digunakan dalam kombinasi dengan fungsi indeks untuk mendapatkan nilai dari lajur lain yang berkaitan dengan nilai carian, seperti formula perlawanan indeks. Pendekatan generik adalah seperti berikut:

Indeks ( return_array , xmatch ( lookup_value , lookup_array )

Logiknya sangat mudah dan mudah diikuti:

Fungsi Xmatch mengira kedudukan relatif nilai carian dalam array carian dan lulus ke argumen row_num indeks. Berdasarkan nombor baris, fungsi indeks mengembalikan nilai dari mana -mana lajur yang anda tentukan.

Sebagai contoh, untuk melihat kawasan lautan di E1, anda boleh menggunakan formula ini:

=INDEX(B2:B6, XMATCH(E1, A2:A6))

Indeks xmatch xmatch untuk melaksanakan carian 2 dimensi

Untuk mencari lajur dan baris secara serentak, gunakan indeks bersama -sama dengan dua fungsi Xmatch. Xmatch pertama akan mendapat nombor baris dan yang kedua akan mengambil nombor lajur:

Indeks ( data , xmatch ( lookup_value , menegak _ lookup_array ), xmatch ( nilai lookup , mendatar _ lookup_array ))

Formula ini sama dengan perlawanan perlawanan indeks kecuali anda boleh menghilangkan argumen Match_mode kerana ia mungkir untuk perlawanan tepat.

Contohnya, untuk mendapatkan nombor jualan untuk item tertentu (G1) dalam bulan tertentu (G2), formula adalah:

=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))

Di mana B2: D8 adalah sel data yang tidak termasuk tajuk baris dan lajur, A2: A8 adalah senarai item dan B1: D1 adalah nama bulan.

Formula Xmatch yang sensitif kes

Seperti yang telah disebutkan, fungsi Xmatch Excel adalah kes-tidak sensitif oleh reka bentuk. Untuk memaksanya untuk membezakan kes teks, gunakan Xmatch dalam kombinasi dengan fungsi yang tepat:

Padanan (benar, tepat ( lookup_array , lookup_value ))

Untuk mencari dalam urutan terbalik dari yang terakhir ke yang pertama:

Padanan (benar, tepat ( lookup_array , lookup_value ), 0, -1)

Contoh berikut menunjukkan formula generik ini dalam tindakan. Katakan anda mempunyai senarai ID produk sensitif kes dalam B2: B11. Anda ingin mencari kedudukan relatif item dalam E1. Formula sensitif kes dalam E2 adalah semudah ini:

=XMATCH(TRUE, EXACT(B2:B11, E1))

Bagaimana formula ini berfungsi:

Fungsi yang tepat membandingkan nilai carian terhadap setiap item dalam array carian. Sekiranya nilai dibandingkan adalah sama, termasuk kes aksara, fungsi kembali benar, sebaliknya palsu. Pelbagai nilai logik ini (di mana True mewakili perlawanan tepat) pergi ke argumen lookup_array XMatch. Dan kerana nilai carian adalah benar, fungsi Xmatch mengembalikan kedudukan pertandingan tepat yang pertama atau perlawanan yang tepat, bergantung pada cara anda mengkonfigurasi argumen carian_mode .

Xmatch vs Match di Excel

Xmatch direka sebagai pengganti yang lebih kuat dan serba boleh untuk perlawanan, dan kedua -dua fungsi ini mempunyai banyak persamaan. Walau bagaimanapun, terdapat perbezaan penting.

Tingkah laku lalai yang berbeza

Fungsi perlawanan lalai untuk padanan tepat atau item terkecil seterusnya ( Match_type ditetapkan ke 1 atau ditinggalkan).

Fungsi Xmatch lalai ke padanan tepat ( match_mode ditetapkan ke 0 atau ditinggalkan).

Tingkah laku yang berbeza untuk perlawanan anggaran

Apabila argumen match_mode / match_type ditetapkan kepada 1:

• Padanan carian untuk perlawanan tepat atau terkecil seterusnya. Memerlukan bahawa array carian hendaklah disusun mengikut urutan menaik.
• Xmatch mencari perlawanan tepat atau yang terbesar seterusnya. Tidak memerlukan sebarang penyortiran.

Apabila argumen match_mode / match_type ditetapkan kepada -1:

• Padanan carian untuk perlawanan tepat atau yang terbesar seterusnya. Memerlukan menyusun array carian dalam urutan menurun.
• Xmatch mencari perlawanan tepat atau terkecil seterusnya. Tidak memerlukan sebarang penyortiran.

Carian Wildcard

Untuk mencari perlawanan separa dengan xmatch, anda perlu menetapkan argumen match_mode kepada 2.

Fungsi perlawanan tidak mempunyai pilihan mod perlawanan Wildcard khas. Dalam kebanyakan kes, anda akan mengkonfigurasinya untuk perlawanan tepat ( Match_type ditetapkan ke 0), yang juga berfungsi untuk carian wildcard.

Mod carian

Seperti fungsi Xlookup baru, Xmatch mempunyai argumen carian_mode khas yang membolehkan anda menentukan arah carian :

• 1 atau ditinggalkan (lalai)-Cari pertama-ke-terakhir.
• -1-Carian terbalik terakhir-ke-pertama.

Dan pilih algoritma carian binari , yang sangat cepat dan cekap pada data yang disusun .

• 2 - Carian binari pada data yang disusun menaik.
• -2 - Carian binari pada data yang disusun menurun.

Carian binari , yang juga dikenali sebagai carian separuh interval atau carian logaritma , adalah algoritma khas yang mendapati kedudukan nilai carian dalam array dengan membandingkannya dengan elemen tengah array. Carian binari jauh lebih cepat daripada carian biasa tetapi berfungsi dengan betul hanya pada senarai yang disusun. Pada data yang tidak disusun, ia boleh mengembalikan hasil yang salah yang mungkin kelihatan agak normal pada pandangan pertama.

Sintaks perlawanan tidak menyediakan argumen mod carian sama sekali.

Xmatch mengendalikan array secara asli

Tidak seperti pendahulunya, fungsi Xmatch direka untuk Excel dinamik dan mengendalikan array secara asli, tanpa anda perlu menekan CTRL Shift Enter . Ini menjadikan formula lebih mudah untuk membina dan mengedit, terutamanya apabila menggunakan beberapa fungsi yang berbeza bersama -sama. Bandingkan penyelesaian berikut:

• Formula sensitif kes: xmatch | Perlawanan
• Bandingkan dua lajur atau senarai untuk perlawanan: xmatch | Perlawanan

Xmatch dan ketersediaan perlawanan

Xmatch adalah fungsi baru dan ia hanya tersedia dalam Excel untuk Microsoft 365 dan Excel 2021.

Fungsi perlawanan tersedia dalam mana -mana versi Excel 365 melalui Excel 2007.

Itulah cara menggunakan fungsi Xmatch dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Formula Xmatch Excel (fail .xlsx)

Atas ialah kandungan terperinci Fungsi xmatch excel dengan contoh formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!