Mengapa INDEX dan MATCH lebih baik daripada VLOOKUP dalam Excel

Kami sebelum ini telah menerangkan kepada pemula cara menggunakan fungsi asas VLOOKUP (Inggeris VLOOKUP, singkatan untuk "fungsi carian menegak"). Dan pengguna berpengalaman ditunjukkan beberapa formula yang lebih rumit.

Dan dalam artikel ini kami akan cuba memberikan maklumat tentang kaedah lain untuk bekerja dengan carian menegak.

Anda mungkin tertanya-tanya: "Mengapa ini perlu?". Dan ini adalah perlu untuk menunjukkan semua kaedah carian yang mungkin. Di samping itu, banyak sekatan VLOOKUP sering menghalang mendapatkan hasil yang diingini. Dalam hal ini, INDEX( ) MATCH( ) adalah lebih berfungsi dan pelbagai, dan mereka juga mempunyai lebih sedikit sekatan.

PADANAN INDEKS Asas

Memandangkan tujuan panduan ini adalah untuk menunjukkan betapa baiknya ciri ini, kami Mari kita lihat maklumat asas mengenai prinsip operasinya. Dan kami akan menunjukkan contoh, dan juga mempertimbangkan mengapa, ia lebih baik daripada VLOOKUP ().

Sintaks dan Penggunaan Fungsi INDEX

Fungsi ini membantu dalam mencari nilai yang dikehendaki di antara kawasan carian yang ditentukan berdasarkan nombor lajur atau baris. sintaks:

=INDEX(tatasusunan, nombor baris, nombor lajur):

• tatasusunan - kawasan di mana carian akan berlaku;
• nombor baris – nombor baris yang hendak dicari dalam tatasusunan yang ditentukan. Jika nombor baris tidak diketahui, nombor lajur mesti dinyatakan;
• nombor lajur – nombor lajur yang akan ditemui dalam tatasusunan yang ditentukan. Jika nilainya tidak diketahui, nombor baris diperlukan.

Contoh formula mudah:

=INDEX(A1:S10,2,3)

Fungsi ini akan mencari dalam julat dari A1 hingga C10. Nombor menunjukkan baris (2) dan lajur (3) untuk menunjukkan nilai yang dikehendaki. Hasilnya ialah sel C2.

Cukup mudah, bukan? Tetapi apabila anda bekerja dengan dokumen sebenar, anda tidak mungkin mempunyai maklumat mengenai nombor lajur atau sel. Itulah gunanya fungsi MATCH().

Sintaks dan Penggunaan Fungsi MATCH

Fungsi MATCH() mencari nilai yang dikehendaki dan menunjukkan nombor anggarannya dalam kawasan carian yang ditentukan.

Sintaks searchpos() kelihatan seperti ini:

=MATCH(nilai untuk carian, tatasusunan untuk carian, jenis padanan)

• nilai carian – nombor atau teks yang akan ditemui;
• tatasusunan carian - kawasan di mana carian akan berlaku;
• jenis padanan – menentukan sama ada untuk mencari nilai yang tepat atau nilai yang paling hampir dengannya:
  • 1 (atau tiada nilai ditentukan) – mengembalikan nilai terbesar yang sama atau kurang daripada nilai yang ditentukan;
  • 0 – menunjukkan padanan tepat dengan nilai yang dicari. Dalam gabungan INDEX() MATCH() anda hampir selalu memerlukan padanan tepat, jadi kami menulis 0;
  • -1 – Menunjukkan nilai terkecil yang lebih besar daripada atau sama dengan nilai yang dinyatakan dalam formula. Pengisihan dijalankan dalam susunan menurun.

Contohnya, dalam julat B1:B3 New York, Paris, London didaftarkan. Formula di bawah akan menunjukkan nombor 3 kerana London adalah ketiga dalam senarai:

=EXPOSE(London,B1:B3,0)

Bagaimana untuk bekerja dengan fungsi INDEX MATCH 

Anda mungkin sudah mula memahami prinsip di mana kerja bersama fungsi ini dibina. Pendek kata, kemudian INDEX() mencari nilai yang dikehendaki di antara baris dan lajur yang ditentukan. Dan MATCH() menunjukkan nombor nilai ini:

=INDEX(lajur dari mana nilai dikembalikan, MATCH(nilai untuk dicari, lajur untuk dicari, 0))

Masih sukar memahami cara ia berfungsi? Mungkin contoh akan menerangkan dengan lebih baik. Katakan anda mempunyai senarai ibu kota dunia dan penduduknya:

Untuk mengetahui saiz populasi ibu kota tertentu, sebagai contoh, ibu negara Jepun, kami menggunakan formula berikut:

=INDEX(C2:C10, MATCH(Jepun, A2:A10,0))

Keterangan:

• Fungsi MATCH() mencari nilai – “Jepun” dalam tatasusunan A2:A10 dan mengembalikan nombor 3, kerana Jepun ialah nilai ketiga dalam senarai. 
• Angka ini pergi kenombor garisan” dalam formula INDEX() dan memberitahu fungsi untuk mencetak nilai daripada baris ini.

Jadi formula di atas menjadi formula standard INDEKS(C2:C10,3). Formula mencari dari sel C2 hingga C10 dan mengembalikan data daripada sel ketiga dalam julat ini, iaitu, C4, kerana kira detik bermula dari baris kedua.

Tidak mahu menetapkan nama bandar dalam formula? Kemudian tulis dalam mana-mana sel, sebut F1, dan gunakannya sebagai rujukan dalam formula MATCH(). Dan anda berakhir dengan formula carian dinamik:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Penting! Bilangan baris dalam pelbagai INDEX() mestilah sama dengan bilangan baris dalam dianggap tatasusunan dalam MATCH(), jika tidak, anda akan mendapat keputusan yang salah.

Tunggu sebentar, mengapa tidak menggunakan formula VLOOKUP() sahaja?

=VLOOKUP(F1, A2:C10, 3, Salah)

 Apa gunanya membuang masa cuba memikirkan semua kerumitan PADANAN INDEX ini?

Dalam kes ini, tidak kira fungsi mana yang hendak digunakan. Ini hanyalah contoh untuk memahami cara fungsi INDEX() dan MATCH() berfungsi bersama. Contoh lain akan menunjukkan keupayaan fungsi ini dalam situasi di mana VLOOKUP tidak berkuasa. 

INDEX MATCH atau VLOOKUP

Apabila memutuskan formula carian yang hendak digunakan, ramai yang bersetuju bahawa INDEX() dan MATCH() adalah jauh lebih baik daripada VLOOKUP. Walau bagaimanapun, ramai orang masih menggunakan VLOOKUP(). Pertama, VLOOKUP() adalah lebih mudah, dan kedua, pengguna tidak memahami sepenuhnya semua kelebihan bekerja dengan INDEX() dan MATCH(). Tanpa pengetahuan ini, tiada siapa yang akan bersetuju untuk menghabiskan masa mereka mengkaji sistem yang kompleks.

Berikut ialah kelebihan utama INDEX() dan MATCH() berbanding VLOOKUP():

• Cari dari kanan ke kiri. VLOOKUP() tidak boleh mencari dari kanan ke kiri, jadi nilai yang anda cari mestilah sentiasa berada di lajur paling kiri jadual. Tetapi INDEX() dan MATCH() boleh mengendalikan ini tanpa masalah. Artikel ini akan memberitahu anda rupanya dalam amalan: cara mencari nilai yang diingini di sebelah kiri.

1. Penambahan atau pengalihan keluar lajur yang selamat. Formula VLOOKUP() menunjukkan hasil yang salah apabila mengalih keluar atau menambah lajur kerana VLOOKUP() memerlukan nombor lajur yang tepat untuk berjaya. Sememangnya, apabila lajur ditambah atau dialih keluar, nombornya juga berubah. 

Dan dalam formula INDEX() dan MATCH(), julat lajur ditentukan, bukan lajur individu. Akibatnya, anda boleh menambah dan mengalih keluar lajur dengan selamat tanpa perlu mengemas kini formula setiap kali.

1. Tiada had pada volum carian. Apabila menggunakan VLOOKUP(), jumlah kriteria carian mestilah tidak melebihi 255 aksara atau anda akan mendapat #VALUE! Jadi jika data anda mengandungi sejumlah besar aksara, INDEX() dan MATCH() ialah pilihan terbaik.
2. Kelajuan pemprosesan yang tinggi. Jika jadual anda agak kecil, maka anda tidak mungkin melihat sebarang perbezaan. Tetapi, jika jadual mengandungi ratusan atau beribu-ribu baris, dan, oleh itu, terdapat ratusan dan ribuan formula, INDEX () dan MATCH () akan mengatasi lebih cepat daripada VLOOKUP (). Hakikatnya ialah Excel akan memproses hanya lajur yang dinyatakan dalam formula, bukannya memproses keseluruhan jadual. 

Kesan prestasi VLOOKUP() akan menjadi ketara terutamanya jika lembaran kerja anda mengandungi sejumlah besar formula seperti VLOOKUP() dan SUM(). Pemeriksaan berasingan bagi fungsi VLOOKUP() diperlukan untuk menghuraikan setiap nilai dalam tatasusunan. Jadi Excel perlu memproses sejumlah besar maklumat, dan ini melambatkan kerja dengan ketara.

Contoh Formula 

Kami telah mengetahui kegunaan fungsi ini, jadi kami boleh beralih ke bahagian yang paling menarik: penerapan pengetahuan dalam amalan.

Formula untuk mencari dari kanan ke kiri

Seperti yang telah disebutkan, VLOOKUP tidak boleh melaksanakan bentuk carian ini. Jadi, jika nilai yang dikehendaki tidak berada di lajur paling kiri, VLOOKUP() tidak akan menghasilkan hasil. Fungsi INDEX() dan MATCH() adalah lebih serba boleh, dan lokasi nilai tidak memainkan peranan yang besar untuk mereka berfungsi.

Sebagai contoh, kami akan menambah lajur pangkat di sebelah kiri jadual kami dan cuba memikirkan pangkat dari segi populasi yang diduduki ibu negara Negara Kita.

Dalam sel G1, kami menulis nilai yang akan ditemui, dan kemudian menggunakan formula berikut untuk mencari dalam julat C1:C10 dan mengembalikan nilai yang sepadan daripada A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Jika anda bercadang untuk menggunakan formula ini untuk berbilang sel, pastikan anda membetulkan julat menggunakan pengalamatan mutlak (contohnya, $A$2: $A$10 dan $C$2: 4C$10).

INDEX LEBIH TERDEDAH LEBIH TERDEDAH  untuk mencari dalam lajur dan baris

Dalam contoh di atas, kami telah menggunakan fungsi ini sebagai pengganti VLOOKUP() untuk mengembalikan nilai daripada julat baris yang telah ditetapkan. Tetapi bagaimana jika anda perlu melakukan carian matriks atau dua hala?

Kedengarannya rumit, tetapi formula untuk pengiraan sedemikian adalah serupa dengan formula INDEX() MATCH() standard, dengan hanya satu perbezaan: formula MATCH() mesti digunakan dua kali. Kali pertama untuk mendapatkan nombor baris, dan kali kedua untuk mendapatkan nombor lajur:

=INDEX(array, MATCH(nilai carian menegak, lajur carian, 0), MATCH(nilai carian mendatar, baris carian, 0))

Mari lihat jadual di bawah dan cuba buat formula INDEX() EXPRESS() EXPRESS() untuk memaparkan demografi di negara tertentu untuk tahun yang dipilih.

Negara sasaran berada dalam sel G1 (carian menegak) dan tahun sasaran berada dalam sel G2 (carian mendatar). Formula akan kelihatan seperti ini:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Bagaimana formula ini berfungsi

Seperti mana-mana formula kompleks lain, ia lebih mudah difahami dengan memecahkannya kepada persamaan individu. Dan kemudian anda boleh memahami fungsi setiap fungsi individu:

• PERLAWANAN(G1,A2:A11,0) – mencari nilai (G1) dalam julat A2:A11 dan menunjukkan bilangan nilai ini, dalam kes kami ialah 2;
• CARIAN(G2,B1:D1,0) – mencari nilai (G2) dalam julat B1:D1. Dalam kes ini, hasilnya ialah 3.

Nombor baris dan lajur yang ditemui dihantar ke nilai yang sepadan dalam formula INDEX():

=INDEX(B2:D11,2,3)

Hasilnya, kita mempunyai nilai yang berada dalam sel di persimpangan 2 baris dan 3 lajur dalam julat B2:D11. Dan formula menunjukkan nilai yang dikehendaki, iaitu dalam sel D3.

Cari mengikut berbilang syarat dengan INDEX dan MATCH

Jika anda telah membaca panduan kami untuk VLOOKUP(), anda mungkin telah mencuba berbilang formula carian. Tetapi kaedah carian ini mempunyai satu had yang ketara - keperluan untuk menambah lajur tambahan.

Tetapi berita baiknya ialah Dengan INDEX() dan MATCH() anda boleh mencari berbilang syarat tanpa perlu mengedit atau menukar lembaran kerja anda.

Berikut ialah formula carian berbilang syarat umum untuk INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

nota itu: formula ini mesti digunakan bersama dengan pintasan papan kekunci CTRL+SHIFT+ENTER.

Katakan anda perlu mencari nilai yang anda cari berdasarkan 2 syarat: Pembeli и Produk.

Ini memerlukan formula berikut:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Dalam formula ini, C2:C10 ialah julat di mana carian akan berlaku, F1 - syarat ini, A2:A10 — ialah julat untuk membandingkan keadaan, F2 - syarat 2, V2:V10 – julat untuk perbandingan keadaan 2.

Jangan lupa untuk menekan kombinasi pada akhir kerja dengan formula CTRL+SHIFT+ENTER – Excel akan menutup formula secara automatik dengan pendakap kerinting, seperti yang ditunjukkan dalam contoh:

Jika anda tidak mahu menggunakan formula tatasusunan untuk kerja anda, kemudian tambahkan INDEX() lain pada formula dan tekan ENTER, ia akan kelihatan seperti dalam contoh:

Bagaimana formula ini berfungsi

Formula ini berfungsi dengan cara yang sama seperti formula INDEX() MATCH() standard. Untuk mencari berbilang syarat, anda hanya mencipta berbilang syarat Salah dan Benar yang mewakili syarat individu yang betul dan salah. Dan kemudian syarat ini digunakan untuk semua elemen tatasusunan yang sepadan. Formula menukar argumen False dan True kepada 0 dan 1, masing-masing, dan mengeluarkan tatasusunan dengan 1 ialah nilai padanan yang ditemui dalam rentetan. MATCH() akan mencari nilai pertama yang sepadan dengan 1 dan menyerahkannya kepada formula INDEX(). Dan ia, seterusnya, akan mengembalikan nilai yang telah diingini dalam baris yang ditentukan dari lajur yang dikehendaki.

Formula tanpa tatasusunan bergantung pada keupayaan INDEX() untuk mengendalikannya sendiri. INDEX() kedua dalam formula sepadan dengan palsu (0), jadi ia menghantar keseluruhan tatasusunan dengan nilai tersebut kepada formula MATCH(). 

Ini adalah penjelasan yang agak panjang tentang logik di sebalik formula ini. Untuk maklumat lanjut baca artikel "PADANAN INDEKS dengan pelbagai syarat'.

PURATA, MAX dan MIN dalam INDEX dan MATCH

Excel mempunyai fungsi khasnya sendiri untuk mencari purata, maksimum dan minimum. Tetapi bagaimana jika anda ingin mendapatkan data daripada sel yang dikaitkan dengan nilai tersebut? Dalam kes ini PURATA, MAX dan MIN mesti digunakan bersama dengan INDEX dan MATCH.

PADANAN INDEKS dan MAX

Untuk mencari nilai terbesar dalam lajur D dan memaparkannya dalam lajur C, gunakan formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

PADANAN INDEKS dan MIN

Untuk mencari nilai terkecil dalam lajur D dan memaparkannya dalam lajur C, gunakan formula berikut:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

INDEKS CARIAN dan ULAR

Untuk mencari nilai purata dalam lajur D dan paparkan nilai ini dalam C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Bergantung pada cara data anda ditulis, argumen ketiga untuk MATCH() adalah sama ada 1, 0, atau -1:

• jika lajur diisih dalam tertib menaik, tetapkan 1 (maka formula akan mengira nilai maksimum, yang kurang daripada atau sama dengan nilai purata);
• jika isihan menurun, maka -1 (rumus akan mengeluarkan nilai minimum yang lebih besar daripada atau sama dengan purata);
• jika tatasusunan carian mengandungi nilai yang betul-betul sama dengan purata, kemudian tetapkannya kepada 0. 

 Dalam contoh kami, populasi diisih dalam susunan menurun, jadi kami meletakkan -1. Dan hasilnya adalah Tokyo, kerana nilai populasi (13,189) adalah yang paling hampir dengan nilai purata (000).

VLOOKUP() juga boleh melakukan pengiraan sedemikian, tetapi hanya sebagai formula tatasusunan: VLOOKUP dengan AVERAGE, MIN dan MAX.

PADANAN INDEKS dan ESND/IFERROR

Anda mungkin sudah perasan bahawa jika formula tidak dapat mencari nilai yang diingini, ia menimbulkan ralat # N / A. Anda boleh menggantikan mesej ralat standard dengan sesuatu yang lebih bermaklumat. Sebagai contoh, tetapkan hujah dalam formula Dalam ke-XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Dengan formula ini, jika anda memasukkan data yang tiada dalam jadual, borang akan memberikan anda mesej yang ditentukan.

Jika anda ingin menangkap semua ralat, maka kecuali untuk Dalam ke-XNUMX boleh digunakan IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Sesuatu telah berlaku!”)

Tetapi ingat bahawa menyembunyikan ralat dengan cara ini bukanlah idea yang baik, kerana ralat standard melaporkan pelanggaran dalam formula.

Kami harap anda mendapati panduan kami untuk menggunakan fungsi INDEX MATCH() membantu.