Anda boleh menggunakan fungsi Excel MATCH untuk mencari kedudukan relatif bagi nilai tertentu dalam julat sel atau tatasusunan.
Fungsi MATCH adalah serupa dengan fungsi VLOOKUP kerana kedua-duanya dikategorikan di bawah Fungsi Carian/Rujukan Excel. VLOOKUP mencari nilai tertentu dalam lajur dan mengembalikan nilai dalam baris yang sama manakala fungsi MATCH mencari nilai tertentu dalam julat dan mengembalikan kedudukan nilai tersebut.
Fungsi Excel MATCH mencari nilai yang ditentukan dalam julat sel atau tatasusunan dan mengembalikan kedudukan relatif penampilan pertama nilai tersebut dalam julat. Fungsi MATCH juga boleh digunakan untuk mencari nilai tertentu dan mengembalikan nilai yang sepadan dengan bantuan fungsi INDEX (sama seperti Vlookup). Mari lihat cara menggunakan fungsi Excel MATCH untuk mencari kedudukan nilai carian dalam julat sel.
Fungsi Excel MATCH
Fungsi MATCH ialah fungsi terbina dalam Excel dan ia digunakan terutamanya untuk mencari kedudukan relatif nilai carian dalam lajur atau baris.
Sintaks Fungsi MATCH:
=MATCH(lookup_value, lookup_array,[match_type})di mana:
nilai_cari – Nilai yang anda ingin cari dalam julat sel tertentu atau dalam tatasusunan. Ia boleh menjadi nilai berangka, nilai teks, nilai logik atau rujukan sel yang mempunyai nilai.
lookup_array – Tatasusunan sel di mana anda sedang mencari nilai. Ia mestilah satu lajur atau satu baris.
padanan_jenis – Ia adalah parameter pilihan yang boleh ditetapkan kepada 0,1, atau -1 dan lalainya ialah 1.
- 0 mencari padanan tepat, apabila ia tidak ditemui, mengembalikan ralat.
- -1 mencari nilai terkecil yang lebih besar daripada atau sama dengan lookup_value apabila tatasusunan carian dalam tertib menaik.
- 1 mencari nilai terbesar yang kurang daripada atau sama dengan nilai look_up apabila tatasusunan carian dalam tertib menurun.
Cari Kedudukan Padanan Tepat
Katakan, kami mempunyai set data berikut di mana kami ingin mencari kedudukan nilai tertentu.
Dalam jadual ini, kami ingin mencari kedudukan nama bandar (Memphis) dalam lajur (A2:A23), jadi kami menggunakan formula ini:
=MATCH("memphis",A2:A23,0)Argumen ketiga ditetapkan kepada '0' kerana kami ingin mencari padanan tepat nama bandar. Seperti yang anda lihat bahawa nama bandar "memphis" dalam formula adalah dalam huruf kecil manakala dalam jadual huruf pertama nama bandar adalah dalam huruf besar (Memphis). Namun, formula dapat mencari kedudukan nilai yang ditentukan dalam julat yang diberikan. Ini kerana fungsi MATCH tidak peka huruf besar-besaran.
Nota: Jika lookup_value tidak ditemui dalam julat carian atau jika anda menentukan julat carian yang salah, fungsi akan mengembalikan ralat #N/A.
Anda boleh menggunakan rujukan sel dalam hujah pertama fungsi dan bukannya nilai langsung. Formula di bawah mencari kedudukan nilai dalam sel F2 dan mengembalikan hasil dalam sel F3.
Cari Kedudukan Padanan Anggaran
Terdapat dua cara anda boleh mencari padanan anggaran atau tepat bagi nilai carian dan mengembalikan kedudukannya.
- Satu cara ialah mencari nilai terkecil yang lebih besar daripada atau sama (padanan terbesar seterusnya) dengan nilai yang ditentukan. Ia boleh dicapai dengan menetapkan hujah terakhir (match_type) fungsi sebagai '-1'
- Cara lain ialah nilai terbesar yang kurang daripada atau sama (padanan terkecil seterusnya) dengan nilai yang diberikan. Ia boleh dicapai dengan menetapkan match_type fungsi sebagai '1'
Perlawanan Terkecil Seterusnya
Jika fungsi tidak dapat mencari padanan tepat kepada nilai yang ditentukan apabila jenis padanan ditetapkan kepada '1', ia mencari nilai terbesar yang kurang sedikit daripada nilai yang ditentukan (yang bermaksud nilai terkecil seterusnya) dan mengembalikan kedudukannya . Untuk ini berfungsi, anda perlu mengisih tatasusunan dalam tertib menaik, jika tidak ia akan mengakibatkan ralat.
Dalam contoh, kami menggunakan formula di bawah untuk mencari padanan terkecil seterusnya:
=PERLAWANAN(F2,D2:D23,1)Apabila formula ini tidak dapat mencari padanan tepat untuk nilai dalam sel F2, ia menunjukkan kepada kedudukan (16) nilai terkecil seterusnya iaitu 98.
Perlawanan Terbesar Seterusnya
Apabila jenis padanan ditetapkan kepada '-1' dan fungsi MATCH tidak dapat mencari padanan tepat, ia mencari nilai terkecil yang lebih besar daripada nilai yang ditentukan (yang bermaksud nilai terbesar seterusnya) dan mengembalikan kedudukannya. Tatasusunan carian mesti diisih dalam susunan menurun untuk kaedah ini jika tidak ia akan mengembalikan ralat.
Sebagai contoh, masukkan formula berikut untuk mencari padanan terbesar seterusnya kepada nilai carian:
=PERLAWANAN(F2,D2:D23,-1)Fungsi MATCH ini mencari nilai dalam F2 (55) dalam julat carian D2:D23, dan apabila ia tidak menemui padanan tepat, ia mengembalikan kedudukan (16) nilai terbesar seterusnya iaitu 58.
Perlawanan Wildcard
Kad liar boleh digunakan dalam fungsi MATCH hanya apabila match_type ditetapkan kepada '0' dan nilai carian ialah rentetan teks. Terdapat kad bebas yang boleh anda gunakan dalam fungsi MATCH: asterisk (*) dan tanda soal (?).
- Tanda soal (?) digunakan untuk memadankan mana-mana aksara atau huruf tunggal dengan rentetan teks.
- Asterisk (*) digunakan untuk memadankan sebarang bilangan aksara dengan rentetan.
Sebagai contoh, kami menggunakan dua kad liar ‘?’ dalam lookup_value (Lo??n) bagi fungsi MATCH untuk mencari nilai yang sepadan dengan rentetan teks dengan mana-mana dua aksara (di tempat kad bebas). Dan fungsi mengembalikan kedudukan relatif nilai padanan dalam sel E5.
=MATCH("Lo??n",A2:A22,0)
Anda boleh menggunakan kad bebas (*) dengan cara yang sama seperti (?), tetapi asterisk digunakan untuk memadankan sebarang bilangan aksara manakala tanda soal digunakan untuk memadankan mana-mana aksara tunggal.
Contohnya, jika anda menggunakan 'sp*', fungsi itu boleh dipadankan dengan pembesar suara, kelajuan atau spielberg, dsb. Tetapi jika fungsi itu menemui nilai berbilang/pendua yang sepadan dengan nilai carian, ia hanya akan mengembalikan kedudukan nilai pertama.
Dalam contoh, kami memasukkan "Kil*o" dalam argumen lookup_value. Jadi fungsi MATCH() mencari teks yang mengandungi 'Kil' di permulaan, 'o' di penghujung, dan sebarang bilangan aksara di antaranya. 'Kil*o' sepadan dengan Kilimanjaro dalam tatasusunan dan oleh itu fungsi mengembalikan kedudukan relatif Kilimanjaro, iaitu 16.
INDEKS dan PADANAN
Fungsi MATCH jarang digunakan secara bersendirian. Mereka sering berpasangan dengan fungsi lain untuk mencipta formula yang berkuasa. Apabila fungsi MATCH digabungkan dengan fungsi INDEX, ia boleh melakukan carian lanjutan. Ramai orang masih lebih suka menggunakan VLOOKUP untuk mencari nilai, kerana ia lebih mudah tetapi INDEX MATCH lebih fleksibel dan lebih pantas daripada VLOOKUP.
VLOOKUP hanya boleh mencari nilai secara menegak iaitu lajur manakala kombo INDEX MATCH boleh melakukan carian menegak dan mendatar.
Fungsi INDEX digunakan untuk mendapatkan nilai pada lokasi tertentu dalam jadual atau julat. Fungsi MATCH mengembalikan kedudukan relatif sesuatu nilai dalam lajur atau baris. Apabila digabungkan, MATCH mencari nombor baris atau lajur (lokasi) bagi nilai tertentu dan fungsi INDEX mendapatkan semula nilai berdasarkan nombor baris dan lajur tersebut.
Sintaks fungsi INDEX:
=INDEX(array,row_num,[col_num],)Bagaimanapun, mari kita lihat bagaimana INDEX MATCH berfungsi dengan contoh.
Dalam contoh di bawah, kami ingin mendapatkan semula markah 'Kuiz2' untuk pelajar 'Anne'. Untuk melakukannya, kami akan menggunakan formula di bawah:
=INDEX(B2:F20,MACH(H2,A2:A20,0),3)INDEX memerlukan nombor baris dan lajur untuk mendapatkan semula nilai. Dalam formula di atas, fungsi MATCH bersarang mencari nombor baris (kedudukan) bagi nilai 'Anne' (H2). Kemudian kami membekalkan nombor baris itu kepada fungsi INDEX dengan julat B2:F20 dan nombor lajur (3), yang kami tentukan. Dan fungsi INDEX mengembalikan skor '91'.
Carian dua hala dengan INDEX dan MATCH
Anda juga boleh menggunakan fungsi INDEX dan MATCH untuk mencari nilai dalam julat dua dimensi (carian dua hala). Dalam contoh di atas, kami menggunakan fungsi MATCH untuk mencari nombor baris sesuatu nilai, tetapi kami memasukkan nombor lajur secara manual. Tetapi kita boleh mencari kedua-dua baris dan lajur dengan menyarangkan dua fungsi MATCH, satu dalam argumen row_num dan satu lagi dalam argumen column_num bagi fungsi INDEX.
Gunakan formula ini untuk carian dua hala dengan INDEX dan MATCH:
=INDEX(A1:F20,MATCH(H2,A2:A20,0),MACH(H3,A1:F1,0))Seperti yang kita tahu, fungsi MATCH boleh mencari nilai secara mendatar dan menegak. Dalam formula ini, fungsi MATCH kedua dalam argumen colum_num mencari kedudukan Kuiz2 (4) dan membekalkannya kepada fungsi INDEX. Dan INDEX mendapatkan semula markah.
Kini, anda tahu cara menggunakan fungsi Match dalam Excel.