Fungsi perlawanan excel dengan contoh formula

Tutorial ini menerangkan cara menggunakan fungsi perlawanan dalam Excel dengan contoh formula. Ia juga menunjukkan bagaimana untuk memperbaiki formula carian anda dengan membuat formula dinamik dengan vlookup dan perlawanan.

Di Microsoft Excel, terdapat banyak fungsi carian/rujukan yang dapat membantu anda mencari nilai tertentu dalam pelbagai sel, dan perlawanan adalah salah satu daripada mereka. Pada asasnya, ia mengenal pasti kedudukan relatif item dalam pelbagai sel. Walau bagaimanapun, fungsi perlawanan boleh melakukan lebih banyak daripada intipati murni.

• Fungsi Perlawanan Excel - Sintaks dan Penggunaan Asas
• Cara Menggunakan Perlawanan Dalam Excel - Contoh Formula
  • Formula perlawanan dengan kad liar
  • Formula perlawanan sensitif kes
  • Bandingkan 2 lajur untuk perlawanan dan perbezaan (perlawanan ISNA)
  • Vlookup dan perlawanan
  • Hlookup dan perlawanan

Fungsi Perlawanan Excel - Sintaks dan Kegunaan

Fungsi perlawanan dalam Excel mencari nilai yang ditentukan dalam pelbagai sel, dan mengembalikan kedudukan relatif nilai tersebut.

Sintaks untuk fungsi perlawanan adalah seperti berikut:

Match (Lookup_Value, lookup_array, [Match_type])

Lookup_Value (diperlukan) - Nilai yang anda ingin cari. Ia boleh menjadi angka, teks atau nilai logik serta rujukan sel.

Lookup_array (diperlukan) - Julat sel untuk mencari.

Match_Type (Pilihan) - Menentukan jenis perlawanan. Ia boleh menjadi salah satu daripada nilai -nilai ini: 1, 0, -1. Hujah Match_type ditetapkan ke 0 mengembalikan hanya perlawanan tepat, manakala dua jenis yang lain membenarkan perlawanan anggaran.

• 1 atau ditinggalkan (lalai) - Perlawanan anggaran (lebih dekat lebih kecil). Cari nilai terbesar dalam array carian yang kurang daripada atau sama dengan nilai carian. Memerlukan menyusun array carian dalam urutan menaik, dari terkecil hingga terbesar atau dari A hingga Z.
• 0 - Perlawanan tepat. Cari nilai pertama dalam array yang sama persis dengan nilai carian. Tiada penyortiran diperlukan.
• -1 - Perlawanan anggaran (lebih dekat lebih besar). Cari nilai terkecil yang lebih besar daripada atau sama dengan nilai carian. Arahan carian harus disusun mengikut urutan menurun, dari terbesar hingga terkecil atau dari z ke A.

Untuk lebih memahami fungsi perlawanan, mari buat formula mudah berdasarkan data ini: Nama pelajar dalam lajur A dan skor peperiksaan mereka dalam lajur B, disusun dari yang terbesar hingga terkecil. Untuk mengetahui di mana pelajar tertentu (katakan, Laura ) berdiri antara lain, gunakan formula mudah ini:

=MATCH("Laura", A2:A8, 0)

Secara pilihan, anda boleh meletakkan nilai carian dalam beberapa sel (E1 dalam contoh ini) dan merujuk bahawa sel dalam formula perlawanan Excel anda:

=MATCH(E1, A2:A8, 0)

Seperti yang anda lihat dalam tangkapan skrin di atas, nama pelajar dimasukkan dalam urutan sewenang -wenangnya, dan oleh itu kami menetapkan argumen Match_Type kepada 0 (padanan tepat), kerana hanya jenis perlawanan ini tidak memerlukan nilai menyusun dalam array carian. Secara teknikal, formula perlawanan mengembalikan kedudukan relatif Laura dalam julatnya. Tetapi kerana skor disusun dari yang terbesar hingga terkecil, ia juga memberitahu kita bahawa Laura mempunyai skor terbaik ^ke -5 di kalangan semua pelajar.

Petua. Dalam Excel 365 dan Excel 2021, anda boleh menggunakan fungsi Xmatch, yang merupakan pengganti pertandingan moden dan lebih kuat.

4 perkara yang harus anda ketahui mengenai fungsi perlawanan

Seperti yang baru saja anda lihat, menggunakan Match in Excel adalah mudah. Walau bagaimanapun, seperti yang berlaku dengan hampir apa -apa fungsi lain, terdapat beberapa spesifikasi yang perlu anda ketahui:

1. Fungsi perlawanan mengembalikan kedudukan relatif nilai carian dalam array, bukan nilai itu sendiri.
2. Perlawanan adalah kes-insensitif , bermakna ia tidak membezakan antara huruf kecil dan huruf besar apabila berurusan dengan nilai teks.
3. Jika array carian mengandungi beberapa kejadian nilai carian, kedudukan nilai pertama dikembalikan.
4. Jika nilai carian tidak dijumpai dalam array carian, ralat #N/A dikembalikan.

Cara Menggunakan Perlawanan Dalam Excel - Contoh Formula

Sekarang anda tahu penggunaan asas fungsi perlawanan Excel, mari kita bincangkan beberapa contoh formula yang melampaui asas -asas.

Perlawanan separa dengan kad liar

Seperti banyak fungsi lain, pertandingan memahami watak -watak wildcard berikut:

• Tanda Soalan (?) - Menggantikan watak tunggal
• Asterisk (*) - Mengganti urutan watak

Nota. Kad liar hanya boleh digunakan dalam formula perlawanan dengan match_type ditetapkan ke 0.

Formula perlawanan dengan wildcards berguna dalam situasi apabila anda ingin memadankan bukan keseluruhan rentetan teks, tetapi hanya beberapa aksara atau sebahagian daripada rentetan. Untuk menggambarkan titik, pertimbangkan contoh berikut.

Katakan anda mempunyai senarai penjual semula serantau dan angka jualan mereka untuk bulan lalu. Anda ingin mencari kedudukan relatif penjual semula tertentu dalam senarai (disusun dengan jumlah jualan dalam perintah menurun) tetapi anda tidak dapat mengingati namanya dengan tepat, walaupun anda ingat beberapa watak pertama.

Dengan mengandaikan nama penjual semula berada dalam julat A2: A11, dan anda mencari nama yang bermula dengan "kereta", formula itu seperti berikut:

=MATCH("car*", A2:A11,0)

Untuk menjadikan formula perlawanan kami lebih serba boleh, anda boleh menaip nilai carian dalam beberapa sel (E1 dalam contoh ini), dan menggabungkan sel itu dengan watak Wildcard, seperti ini:

=MATCH(E1&"*", A2:A11,0)

Seperti yang ditunjukkan dalam tangkapan skrin di bawah, formula kembali 2, yang merupakan kedudukan "Carter":

Untuk menggantikan hanya satu watak dalam nilai carian, gunakan "?" Operator Wildcard, seperti ini:

=MATCH("ba?er", A2:A11,0)

Formula di atas akan sepadan dengan nama " Baker " dan menghidupkan semula kedudukan relatifnya, iaitu 5.

Formula perlawanan sensitif kes

Seperti yang disebutkan pada permulaan tutorial ini, fungsi perlawanan tidak membezakan huruf besar dan huruf kecil. Untuk membuat formula perlawanan sensitif kes, gunakan perlawanan dalam kombinasi dengan fungsi tepat yang membandingkan sel dengan tepat, termasuk kes watak.

Inilah formula sensitif kes generik untuk memadankan data:

Perlawanan (Benar, Tekanan ( Arahan Lookup , Nilai Carian ), 0)

Formula ini berfungsi dengan logik berikut:

• Fungsi yang tepat membandingkan nilai carian dengan setiap elemen array carian. Sekiranya sel -sel yang dibandingkan adalah sama, fungsi kembali benar, sebaliknya palsu.
• Dan kemudian, fungsi perlawanan membandingkan benar (yang merupakan lookup_value ) dengan setiap nilai dalam array yang dikembalikan dengan tepat, dan mengembalikan kedudukan perlawanan pertama.

Sila ingat bahawa ia adalah formula array yang memerlukan menekan CTRL Shift Enter untuk disiapkan dengan betul.

Dengan mengandaikan nilai carian anda dalam sel E1 dan array carian adalah A2: A9, formula adalah seperti berikut:

=MATCH(TRUE, EXACT(A2:A9,E1),0)

Tangkapan skrin berikut menunjukkan formula perlawanan sensitif kes di Excel:

Bandingkan 2 lajur untuk perlawanan dan perbezaan (perlawanan ISNA)

Memeriksa dua senarai untuk perlawanan dan perbezaan adalah salah satu tugas yang paling biasa dalam Excel, dan ia boleh dilakukan dalam pelbagai cara. Formula ISNA/Match adalah salah satu daripada mereka:

Jika (isNA (padanan ( nilai pertama dalam list1 , list2 , 0)), "tidak dalam senarai 1", "")

Untuk apa -apa nilai senarai 2 yang tidak terdapat dalam senarai 1, formula mengembalikan " tidak dalam senarai 1 ". Dan inilah caranya:

• Fungsi perlawanan mencari nilai dari senarai 1 dalam senarai 2. Jika nilai dijumpai, ia mengembalikan kedudukan relatifnya, #N/A ralat sebaliknya.
• Fungsi ISNA dalam Excel hanya satu perkara - cek untuk kesilapan #N/A (yang bermaksud "tidak tersedia"). Jika nilai yang diberikan adalah ralat #N/A, fungsi kembali benar, sebaliknya palsu. Dalam kes kami, benar bermakna nilai dari Senarai 1 tidak dijumpai dalam senarai 2 (iaitu ralat #N/A dikembalikan oleh perlawanan).
• Kerana ia mungkin sangat mengelirukan bagi pengguna anda untuk melihat benar untuk nilai -nilai yang tidak muncul dalam Senarai 1, anda membungkus fungsi jika ISNA untuk memaparkan " tidak dalam senarai 1 " sebaliknya, atau apa sahaja teks yang anda inginkan.

Sebagai contoh, untuk membandingkan nilai dalam lajur b terhadap nilai dalam lajur A, formula mengambil bentuk berikut (di mana B2 adalah sel paling atas):

=IF(ISNA(MATCH(B2,A:A,0)), "Not in List 1", "")

Seperti yang anda ingat, fungsi perlawanan dalam Excel adalah kes-insensitif dengan sendirinya. Untuk membezakan kes watak, menanamkan fungsi yang tepat dalam argumen Lookup_Array , dan ingat untuk menekan CTRL Shift Enter untuk menyelesaikan formula array ini:

=IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0)), "Not in List 1", "")

Tangkapan skrin berikut menunjukkan kedua -dua formula dalam tindakan:

Untuk mempelajari cara lain untuk membandingkan dua senarai dalam Excel, sila lihat tutorial berikut: Cara membandingkan 2 lajur dalam Excel.

Excel vlookup dan perlawanan

Contoh ini menganggap anda sudah mempunyai pengetahuan asas mengenai fungsi excel vlookup. Dan jika anda berbuat demikian, kemungkinan anda telah menghadapi banyak batasannya (gambaran keseluruhan terperinci yang dapat dijumpai dalam mengapa Excel Vlookup tidak berfungsi) dan mencari alternatif yang lebih mantap.

Salah satu kelemahan Vlookup yang paling menjengkelkan ialah ia berhenti berfungsi selepas memasukkan atau memadam lajur dalam jadual carian. Ini berlaku kerana VLOOKUP menarik nilai yang sepadan berdasarkan bilangan lajur pulangan yang anda nyatakan (nombor indeks). Kerana nombor indeks "keras dikodkan" dalam formula, Excel tidak dapat menyesuaikannya apabila lajur baru ditambah atau dipadam dari jadual.

Fungsi perlawanan Excel berkaitan dengan kedudukan relatif nilai carian, yang menjadikannya sesuai untuk argumen col_index_num vlookup. Dalam erti kata lain, bukannya menentukan lajur kembali sebagai nombor statik, anda menggunakan perlawanan untuk mendapatkan kedudukan semasa lajur tersebut.

Untuk membuat perkara lebih mudah difahami, mari kita gunakan jadual dengan skor peperiksaan pelajar sekali lagi (sama seperti yang kita gunakan pada permulaan tutorial ini), tetapi kali ini kita akan mengambil skor sebenar dan bukan kedudukan relatifnya.

Dengan mengandaikan nilai carian adalah dalam sel F1, array meja adalah $ A $ 1: $ C $ 2 (ia adalah amalan yang baik untuk menguncinya menggunakan rujukan sel mutlak jika anda merancang untuk menyalin formula ke sel lain), formula berjalan seperti berikut:

=VLOOKUP(F1, $A$1:$C$8, 3, FALSE)

Argumen 3 ^RD ( col_index_num ) ditetapkan kepada 3 kerana skor matematik yang kita mahu tarik adalah lajur 3 ^RD dalam jadual. Seperti yang dapat anda lihat dalam tangkapan skrin di bawah, formula VLOOKUP biasa ini berfungsi dengan baik:

Tetapi hanya sehingga anda memasukkan atau memadam lajur:

Jadi, kenapa #REF! Ralat? Kerana col_index_num ditetapkan ke 3 memberitahu Excel untuk mendapatkan nilai dari lajur ketiga, sedangkan sekarang terdapat hanya 2 lajur dalam array meja.

Untuk mengelakkan perkara -perkara seperti itu berlaku, anda boleh membuat formula VLOOKUP anda lebih dinamik dengan memasukkan fungsi perlawanan berikut:

MATCH(E2,A1:C1,0)

Di mana:

• E2 adalah nilai carian, yang sama persis dengan nama lajur kembali, iaitu lajur dari mana anda ingin menarik nilai ( skor matematik dalam contoh ini).
• A1: C1 adalah array carian yang mengandungi tajuk meja.

Dan sekarang, sertakan fungsi perlawanan ini dalam argumen col_index_num formula vlookup anda, seperti ini:

=VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE)

Dan pastikan ia berfungsi dengan sempurna tidak kira berapa lajur yang anda tambahkan atau padam:

Dalam tangkapan skrin di atas, saya mengunci semua rujukan sel untuk formula berfungsi dengan betul walaupun pengguna saya memindahkannya ke tempat lain di lembaran kerja. A Anda dapat melihat dalam tangkapan skrin di bawah, formula berfungsi dengan baik selepas memadam lajur; Tambahan pula Excel cukup pintar untuk menyesuaikan rujukan mutlak dengan betul dalam kes ini:

Excel hlookup dan perlawanan

Dengan cara yang sama, anda boleh menggunakan fungsi perlawanan Excel untuk meningkatkan formula HlookUp anda. Prinsip umum pada dasarnya adalah sama seperti dalam kes VLOOKUP: anda menggunakan fungsi perlawanan untuk mendapatkan kedudukan relatif lajur kembali, dan membekalkan nombor itu ke argumen ROW_INDEX_NUM dari formula HLOOKUP anda.

Katakan nilai carian adalah dalam sel B5, array meja adalah B1: H3, nama baris kembali (nilai carian untuk perlawanan) berada dalam sel A6 dan tajuk baris adalah A1: A3, formula lengkap adalah seperti berikut:

=HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE)

Seperti yang baru saja anda lihat, gabungan HlookUp/Vlookup & Match pastinya merupakan peningkatan dalam formula HlookUp dan Vlookup biasa. Walau bagaimanapun, fungsi perlawanan tidak menghilangkan semua batasan mereka. Khususnya, formula perlawanan VLOOKUP masih tidak dapat melihat kirinya, dan perlawanan HLOOKUP gagal mencari di mana -mana baris selain yang paling atas.

Untuk mengatasi batasan di atas (dan beberapa yang lain), pertimbangkan untuk menggunakan gabungan perlawanan indeks, yang menyediakan cara yang sangat kuat dan serba boleh untuk melakukan carian dalam Excel, lebih tinggi daripada VLOOKUP dan HLOOKUP dalam banyak aspek. Contoh panduan dan formula terperinci boleh didapati dalam indeks & perlawanan dalam Excel - alternatif yang lebih baik untuk VLOOKUP.

Inilah cara anda menggunakan formula perlawanan di Excel. Mudah -mudahan, contoh -contoh yang dibincangkan dalam tutorial ini akan membuktikan membantu dalam kerja anda. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Contoh Formula Perlawanan Excel (fail .xlsx)

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