Fungsi INDEX boleh digunakan secara bersendirian, tetapi meletakkan fungsi MATCH di dalamnya menghasilkan carian lanjutan.
Fungsi bersarang ini lebih fleksibel daripada VLOOKUP dan boleh menghasilkan hasil lebih cepat.
Artikel ini menerangkan cara menggunakan fungsi INDEX dan MATCH bersama-sama dalam semua versi Excel, termasuk Excel 2019 dan Microsoft 365.
Apakah fungsi INDEX dan MATCH?
INDEX dan MATCH ialah fungsi carian Excel. Walaupun ia adalah dua fungsi berasingan yang boleh digunakan sendiri, ia juga boleh digabungkan untuk mencipta formula lanjutan.
Fungsi INDEX mengembalikan nilai atau rujukan kepada nilai dari dalam pilihan tertentu. Contohnya, ia boleh digunakan untuk mencari nilai dalam baris kedua set data atau dalam baris kelima dan lajur ketiga.
Walaupun INDEX boleh digunakan secara bersendirian, MATCH bersarang dalam formula menjadikannya lebih berguna. Fungsi MATCH mencari item tertentu dalam julat sel dan kemudian mengembalikan kedudukan relatif item dalam julat. Contohnya, ia boleh digunakan untuk menentukan bahawa nama tertentu ialah item ketiga dalam senarai nama.
Image
INDEX dan MATCH Syntax & Arguments
Beginilah cara kedua-dua fungsi perlu ditulis agar Excel memahaminya:
=INDEX(tatasusunan, row_num, [column_num])
array ialah julat sel yang formula akan gunakan. Ia boleh menjadi satu atau lebih baris dan lajur, seperti A1:D5. Ia diperlukan.
row_num ialah baris dalam tatasusunan untuk mengembalikan nilai, seperti 2 atau 18. Ia diperlukan melainkan bilangan_lajur ada.
column_num ialah lajur dalam tatasusunan untuk mengembalikan nilai, seperti 1 atau 9. Ia adalah pilihan.
lookup_value ialah nilai yang anda mahu padankan dalam lookup_array. Ia boleh menjadi nombor, teks atau nilai logik yang ditaip secara manual atau dirujuk melalui rujukan sel. Ini diperlukan.
lookup_array ialah julat sel untuk dilihat. Ia boleh menjadi satu baris atau satu lajur, seperti A2:D2 atau G1:G45. Ini diperlukan.
jenis_padan boleh jadi -1, 0, atau 1. Ia menentukan cara lookup_value dipadankan dengan nilai dalam lookup_array (lihat di bawah). 1 ialah nilai lalai jika hujah ini ditinggalkan.
Jenis Padanan Yang Mana untuk Digunakan
Jenis Padanan
Apa Fungsinya
Peraturan
Contoh
1
Mencari nilai terbesar yang kurang daripada atau sama dengan lookup_value.
Nilai tatasusunan_carian mesti diletakkan dalam tertib menaik (mis., -2, -1, 0, 1, 2; atau A-Z;, atau FALSE, TRUE.
lookup_value ialah 25 tetapi ia tiada daripada lookup_array, jadi kedudukan nombor terkecil seterusnya, seperti 22, dikembalikan.
0
Mencari nilai pertama yang betul-betul sama dengan lookup_value.
Nilai tatasusunan_carian boleh dalam sebarang susunan.
lookup_value ialah 25, jadi ia mengembalikan kedudukan 25.
-1
Mencari nilai terkecil yang lebih besar atau sama dengan lookup_value.
Nilai tatasusunan_carian mesti diletakkan dalam tertib menurun (mis., 2, 1, 0, -1, -2).
lookup_value ialah 25 tetapi ia tiada daripada lookup_array, jadi kedudukan nombor terbesar seterusnya, seperti 34, dikembalikan.
Gunakan 1 atau -1 untuk masa apabila anda perlu menjalankan carian anggaran sepanjang skala, seperti semasa berurusan dengan nombor dan apabila anggaran okey. Tetapi ingat bahawa jika anda tidak menyatakan match_type, 1 akan menjadi lalai, yang boleh memesongkan keputusan jika anda benar-benar mahukan padanan tepat.
Contoh INDEX dan Formula PADANAN
Sebelum kita melihat cara menggabungkan INDEX dan MATCH menjadi satu formula, kita perlu memahami cara fungsi ini berfungsi sendiri.
Contoh INDEKS
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEKS(B1:B2, 1)
Image
Dalam contoh pertama ini, terdapat empat formula INDEX yang boleh kita gunakan untuk mendapatkan nilai yang berbeza:
=INDEX(A1:B2, 2, 2) melihat melalui A1:B2 untuk mencari nilai dalam lajur kedua dan baris kedua, iaitu Stacy.
=INDEX(A1:B1, 1) melihat melalui A1:B1 untuk mencari nilai dalam lajur pertama, iaitu Jon.
=INDEX(2:2, 1) melihat semua dalam baris kedua untuk mencari nilai dalam lajur pertama, iaitu Tim.
=INDEX(B1:B2, 1) melihat melalui B1:B2 untuk mencari nilai dalam baris pertama, iaitu Amy.
Contoh PERLAWANAN
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=PERLAWANAN(13, A1:D1, 0)
Image
Berikut ialah empat contoh mudah fungsi MATCH:
=MATCH("Stacy", A2:D2, 0) sedang mencari Stacy dalam julat A2:D2 dan mengembalikan 3 sebagai hasilnya.
=MATCH(14, D1:D2) sedang mencari 14 dalam julat D1:D2, tetapi memandangkan ia tidak terdapat dalam jadual, MATCH mencari nilai terbesar seterusnya itu kurang daripada atau sama dengan 14, yang dalam kes ini ialah 13, yang berada di kedudukan 1 lookup_array.
=MATCH(14, D1:D2, -1) adalah sama dengan formula di atasnya, tetapi memandangkan tatasusunan tidak dalam tertib menurun seperti yang diperlukan -1, kami mendapat ralat.
=MATCH(13, A1:D1, 0) sedang mencari 13 dalam baris pertama helaian, yang mengembalikan 4 kerana ia adalah item keempat dalam tatasusunan ini.
INDEX-MATCH Contoh
Berikut ialah dua contoh di mana kita boleh menggabungkan INDEX dan MATCH dalam satu formula:
Cari Rujukan Sel dalam Jadual
=INDEX(B2:B5, MATCH(F1, A2:A5))
Image
Contoh ini menyusun formula MATCH dalam formula INDEX. Matlamatnya adalah untuk mengenal pasti warna item menggunakan nombor item.
Jika anda melihat imej, anda boleh melihat dalam baris "Terpisah" bagaimana formula akan ditulis sendiri, tetapi memandangkan kami menyarangkannya, inilah yang berlaku:
MATCH(F1, A2:A5) sedang mencari nilai F1 (8795) dalam set data A2:A5. Jika kita mengira detik lajur, kita dapat melihat ia adalah 2, jadi itulah yang baru diketahui oleh fungsi MATCH.
Susun atur INDEX ialah B2:B5 kerana kami akhirnya mencari nilai dalam lajur itu.
Fungsi INDEX kini boleh ditulis semula seperti ini kerana 2 ialah MATCH didapati: INDEX(B2:B5, 2, [column_num]).
Memandangkan column_num adalah pilihan, kami boleh mengalih keluarnya untuk dibiarkan dengan ini: INDEX(B2:B5, 2).
Jadi sekarang, ini seperti formula INDEX biasa di mana kita mencari nilai item kedua dalam B2:B5, iaitu merah.
Dalam contoh MATCH dan INDEX ini, kami sedang melakukan carian dua hala. Ideanya ialah untuk melihat jumlah wang yang kami peroleh daripada barangan Hijau pada bulan Mei. Ini benar-benar serupa dengan contoh di atas, tetapi formula MATCH tambahan bersarang dalam INDEX.
MATCH(G1, A2:A13, 0) ialah item pertama yang diselesaikan dalam formula ini. Ia mencari G1 (perkataan "Mei") dalam A2:A13 untuk mendapatkan nilai tertentu. Kami tidak nampak di sini, tetapi 5.
MATCH(G2, B1:E1, 0) ialah formula MATCH kedua dan ia benar-benar serupa dengan yang pertama tetapi sebaliknya mencari G2 (perkataan "Hijau") dalam tajuk lajur di B1:E1. Yang ini memutuskan kepada 3.
Kini kami boleh menulis semula formula INDEX seperti ini untuk menggambarkan perkara yang berlaku: =INDEX(B2:E13, 5, 3). Ini melihat dalam keseluruhan jadual, B2:E13, untuk baris kelima dan lajur ketiga, yang mengembalikan $180.
Peraturan PERLAWANAN dan INDEKS
Terdapat beberapa perkara yang perlu diingat semasa menulis formula dengan fungsi ini:
MATCH tidak sensitif huruf besar-besaran, jadi huruf besar dan huruf kecil dianggap sama apabila memadankan nilai teks.
MATCH mengembalikan N/A atas pelbagai sebab: jika match_type ialah 0 dan lookup_value tidak ditemui jika match_type ialah -1 dan lookup_array tidak dalam tertib menurun, jika match_type ialah 1 dan lookup_array bukan dalam menaik tertib dan jika tatasusunan_carian bukan satu baris atau lajur.
Anda boleh menggunakan aksara kad bebas dalam argumen lookup_value jika match_type ialah 0 dan lookup_value ialah rentetan teks. Tanda soal sepadan dengan mana-mana aksara tunggal dan asterisk sepadan dengan mana-mana urutan aksara (cth.g., =MATCH("Jo", 1:1, 0)). Untuk menggunakan MATCH untuk mencari tanda soal atau asterisk sebenar, taip ~ dahulu.
INDEX mengembalikan REF! jika row_num dan column_num tidak menghala ke sel dalam tatasusunan.
Fungsi Excel Berkaitan
Fungsi MATCH serupa dengan LOOKUP, tetapi MATCH mengembalikan kedudukan item dan bukannya item itu sendiri.
VLOOKUP ialah satu lagi fungsi carian yang boleh anda gunakan dalam Excel, tetapi tidak seperti MATCH yang memerlukan INDEX untuk carian lanjutan, formula VLOOKUP hanya memerlukan satu fungsi itu.
Fungsi Microsoft Excel ISBLANK boleh menjadi alat yang hebat untuk mencari lubang dalam pangkalan data anda. Inilah cara untuk menggunakannya sepenuhnya, dengan pemformatan bersyarat dan tidak
Fungsi Excel DAY mengembalikan tarikh sebagai integer, yang berguna dalam beberapa situasi kewangan. Ketahui sintaks, hujah dan cara menggunakan fungsi DAY dalam Excel
Fungsi Excel INDEX ialah formula yang mengambil nilai daripada senarai atau jadual berdasarkan data lokasi yang anda masukkan ke dalam formula. Berikut ialah cara menggunakan formula INDEX, bersama-sama dengan contoh
Gabungkan fungsi AND, OR dan IF dalam Microsoft Excel untuk meningkatkan julat keadaan yang diuji dalam satu sel. Dikemas kini untuk memasukkan Excel 2019
