Perlawanan Indeks Excel vs Vlookup - Contoh Formula

Tutorial ini menunjukkan cara menggunakan fungsi indeks dan perlawanan dalam Excel dan mengapa ia lebih baik daripada VLOOKUP.

Dalam artikel baru -baru ini, kami berusaha untuk menerangkan asas -asas fungsi VLOOKUP kepada pemula dan memberikan contoh yang lebih kompleks dari formula VLOOKUP untuk pengguna lanjutan. Sekarang, saya akan cuba bukan hanya untuk memujuk anda untuk tidak menggunakan VLOOKUP, tetapi sekurang -kurangnya menunjukkan alternatif untuk melakukan carian menegak dalam Excel.

"Kenapa saya perlukan ini?" anda mungkin bertanya. Kerana VLOOKUP mempunyai banyak batasan, ia mungkin menghalang anda daripada mendapatkan hasil yang dikehendaki dalam banyak kes. Sebaliknya, kombinasi perlawanan indeks lebih fleksibel dan mempunyai banyak ciri yang sangat baik yang menjadikannya lebih unggul daripada VOLLOW dalam pelbagai cara.

Fungsi Indeks Excel dan Perlawanan-Asas ----------------------------------------------------------------------------------------------------------------

Oleh kerana tujuan tutorial ini adalah untuk menunjukkan bagaimana untuk melakukan carian menegak alternatif dalam Excel dengan menggabungkan fungsi indeks dan perlawanan, kami tidak akan membincangkan sintaks dan penggunaan secara terperinci. Kami hanya akan meliputi minimum yang diperlukan untuk memahami konsep yang menyeluruh dan kemudian menyelam ke dalam contoh formula yang mendedahkan semua kelebihan menggunakan perlawanan indeks dan bukannya VLOOKUP.

Fungsi Indeks - Sintaks dan Penggunaan

Fungsi indeks Excel mengembalikan nilai dari array berdasarkan nombor baris dan lajur yang anda tentukan. Sintaks fungsi indeks sangat mudah:

Indeks (array, row_num, [column_num]) Berikut adalah penjelasan ringkas setiap parameter:

• Array - Pelbagai sel dari mana anda ingin mengembalikan nilai.
• ROW_NUM - Nombor baris dalam array yang mana anda ingin mengembalikan nilai. Jika ditinggalkan, column_num diperlukan.
• Column_num - Nombor lajur dalam array yang mana anda ingin mengembalikan nilai. Jika ditinggalkan, row_num diperlukan.

Untuk maklumat lanjut, lihat Fungsi Indeks Excel.

Berikut adalah contoh paling mudah formula indeks:

=INDEX(A1:C10,2,3)

Formula ini mencari sel A1 hingga C10 dan mengembalikan nilai sel baris 2 dan lajur 3, iaitu sel C2.

Sangat mudah, bukan? Walau bagaimanapun, apabila anda memproses data sebenar, anda hampir tidak pernah mengetahui nombor baris dan lajur yang anda inginkan, di mana fungsi perlawanan berguna.

Fungsi Perlawanan - Sintaks dan Penggunaan

Fungsi perlawanan Excel mencari nilai dalam pelbagai sel dan mengembalikan kedudukan relatif nilai dalam julat.

Sintaks fungsi perlawanan adalah seperti berikut:

Padanan (Lookup_Value, Lookup_Array, [Match_Type]) - Lookup_Value - Nombor atau nilai teks yang anda cari.

• lookup_array - pelbagai sel yang dicari.
• Match_Type - Menentukan sama ada untuk mengembalikan perlawanan tepat atau perlawanan terdekat:
  • 1 atau Omit (lalai) - Perlawanan anggaran (seterusnya lebih kecil).
  • 0 - Perlawanan tepat. Dalam gabungan indeks/perlawanan, anda hampir selalu memerlukan perlawanan yang tepat, jadi anda menetapkan parameter ketiga fungsi perlawanan ke 0.
  • -1 - Perlawanan anggaran (seterusnya lebih besar).

Sebagai contoh, jika julat B1: B3 mengandungi nilai "New York", "Paris", "London", formula berikut mengembalikan nombor 3, kerana "London" adalah entri ketiga dalam julat:

=MATCH("London",B1:B3,0)

Untuk maklumat lanjut, lihat Fungsi Perlawanan Excel.

Pada pandangan pertama, penggunaan fungsi perlawanan nampaknya dipersoalkan. Siapa yang peduli tentang kedudukan nilai dalam julat? Apa yang kita ingin tahu ialah nilai itu sendiri.

Izinkan saya mengingatkan anda bahawa kedudukan relatif nilai carian (iaitu nombor baris dan lajur) adalah tepat apa yang anda perlukan untuk memberi kepada parameter row_num dan column_num ke fungsi indeks. Anda ingat bahawa indeks Excel dapat mencari nilai di persimpangan baris dan lajur yang diberikan, tetapi ia tidak dapat menentukan baris dan lajur tertentu yang anda inginkan.

Cara menggunakan fungsi perlawanan indeks di Excel

Sekarang anda telah mempelajari asas -asas, saya percaya anda telah mula memahami bagaimana perlawanan dan indeks berfungsi bersama. Ringkasnya, indeks mencari nilai melalui lajur dan lukisan nombor baris, manakala perlawanan menyediakan nombor ini. Itu sahaja!

Untuk mencari menegak, anda hanya menggunakan fungsi perlawanan untuk menentukan nombor baris dan secara langsung memberikan julat lajur untuk indeks:

Indeks ( lajur dari mana nilai itu akan dikembalikan , perlawanan ( lajur ditemui , 0)) masih sukar difahami? Ia mungkin lebih mudah difahami melalui contoh. Katakan anda mempunyai senarai ibu negara dan populasi mereka:

Untuk mencari penduduk modal, seperti ibukota Jepun, gunakan formula perlawanan indeks berikut:

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

Sekarang, mari kita menganalisis apa yang setiap komponen formula ini sebenarnya:

• Fungsi perlawanan mencari nilai carian "Jepun" dalam julat A2: A10 dan mengembalikan nombor 3 kerana "Jepun" adalah yang ketiga dalam array carian.
• Nombor baris diluluskan terus ke parameter ROW_NUM indeks, membimbingnya untuk mengembalikan nilai dari baris.

Oleh itu, formula di atas menjadi indeks mudah (C2: C, 3), yang bermaksud mencari dalam sel C2 hingga C10 dan mengekstrak nilai dari sel ketiga julat itu, iaitu C4, kerana kita mula mengira dari baris kedua.

Tidak mahu kota hardcode dalam formula? Masukkannya ke dalam sel tertentu, seperti F1, memberi rujukan sel kepada perlawanan, dan anda akan mendapat formula carian dinamik:

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

Petua penting! Bilangan baris dalam parameter array indeks sepadan dengan bilangan baris dalam parameter lookup_array perlawanan, jika tidak, formula akan menghasilkan hasil ralat.

Tunggu ... mengapa kita tidak menggunakan formula VLOOKUP berikut? Apa gunanya membuang masa untuk mengetahui misteri indeks perlawanan Excel?

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

Dalam kes ini, ia tidak masuk akal sama sekali :) Contoh mudah ini adalah untuk tujuan demonstrasi hanya supaya anda memahami bagaimana fungsi indeks dan perlawanan berfungsi bersama -sama. Contoh berikut akan menunjukkan kepada anda kuasa sebenar gabungan ini, yang dapat dengan mudah menangani banyak senario kompleks, dan VLOOKUP akan mengalami kesukaran.

Petunjuk:

• Dalam Excel 365 dan Excel 2021, anda boleh menggunakan formula Xmatch indeks yang lebih moden.
• Untuk Helaian Google, lihat contoh formula Perlawanan Indeks dalam artikel ini.

Perbandingan antara perlawanan indeks dan vlookup

Apabila memutuskan fungsi yang digunakan untuk carian menegak, kebanyakan pakar Excel percaya bahawa perlawanan indeks jauh lebih tinggi daripada VLOOKUP. Walau bagaimanapun, ramai orang masih berpegang pada Vlookup, pertama kerana ia lebih mudah, kedua kerana mereka tidak memahami sepenuhnya semua manfaat menggunakan formula perlawanan indeks di Excel. Tanpa pemahaman ini, tiada siapa yang mahu menghabiskan masa belajar tatabahasa yang lebih kompleks.

Di bawah ini, saya akan menunjukkan kelebihan utama indeks perlawanan melalui VLOOKUP, anda boleh memutuskan sama ada ia bernilai menyertai perpustakaan Alat Excel anda.

4 Sebab Utama untuk Menggunakan Perlawanan Indeks dan bukannya VLOOKUP

1. Lihat dari kanan ke kiri. Mana -mana pengguna yang berpengalaman tahu bahawa VLOOKUP tidak dapat melihat ke kiri, yang bermaksud bahawa nilai carian anda harus selalu berada di lajur paling kiri jadual. Perlawanan indeks boleh mencari di sebelah kiri! Contoh berikut menunjukkan bagaimana ia berfungsi: bagaimana untuk mencari nilai di sebelah kiri di Excel.
2. Masukkan atau memadam lajur dengan selamat. Apabila lajur baru dipadam atau ditambah dari jadual carian, formula VLOOKUP boleh merosakkan atau memberikan hasil yang salah kerana sintaks VLOOKUP memerlukan menentukan nombor indeks lajur yang anda ingin mengekstrak data. Sememangnya, apabila anda menambah atau memadam lajur, nombor indeks berubah. Dengan perlawanan indeks, anda menentukan julat lajur kembali, bukan nombor indeks. Oleh itu, anda bebas memasukkan dan memadam sebilangan lajur tanpa bimbang tentang mengemas kini setiap formula yang berkaitan.
3. Tiada had pada saiz nilai carian. Apabila menggunakan fungsi VLOOKUP, jumlah keseluruhan keadaan carian anda tidak boleh melebihi 255 aksara, jika tidak, anda akan mendapat #Value! ralat. Jadi jika dataset anda mengandungi rentetan panjang, perlawanan indeks adalah satu -satunya penyelesaian yang berdaya maju.
4. Kelajuan pemprosesan yang lebih tinggi. Sekiranya jadual anda agak kecil, terdapat sedikit perbezaan yang signifikan dalam prestasi Excel. Walau bagaimanapun, jika lembaran anda mengandungi beratus -ratus atau beribu -ribu baris, dan ratusan atau ribuan formula yang sepadan, indeks perlawanan akan berjalan lebih cepat daripada vlookup, kerana Excel hanya mengendalikan mencari dan mengembalikan lajur, bukan keseluruhan pelbagai jadual. Jika buku kerja anda mengandungi formula array yang kompleks seperti VLOOKUP dan SUM, kesan VLOOKUP pada prestasi Excel mungkin sangat ketara. Kuncinya adalah untuk memeriksa setiap nilai dalam array memerlukan memanggil fungsi VLOOKUP secara berasingan. Oleh itu, lebih banyak nilai array anda mengandungi dan lebih banyak formula array terdapat dalam buku kerja anda, Excel yang lebih perlahan akan dilakukan.

Untuk memahami nuansa antara perlawanan indeks dan xlookup, terokai analisis mendalam dalam panduan ini: Excel Xlookup dan perlawanan indeks.

Perlawanan Indeks Excel - Contoh Formula

Memahami sebab -sebab untuk mempelajari fungsi indeks perlawanan, mari kita pergi ke bahagian yang paling menarik dan lihat bagaimana pengetahuan teoritis dapat digunakan untuk berlatih.

Formula perlawanan indeks yang ditemui dari kanan ke kiri

Seperti yang dinyatakan sebelum ini, VLOOKUP tidak dapat melihat ke kiri. Oleh itu, melainkan jika nilai carian anda berada di lajur paling kiri, hampir mustahil untuk formula VLOOKUP untuk membawa anda hasil yang dikehendaki. Fungsi perlawanan indeks dalam Excel lebih serba boleh dan tidak benar -benar peduli dengan mencari dan mengembalikan lokasi lajur.

Untuk contoh ini, kami akan menambah lajur pangkat di sebelah kiri jadual contoh dan cuba mengetahui kedudukan penduduk Moscow, ibukota Rusia.

Masukkan nilai carian dalam G1, cari di C2: C10 menggunakan formula berikut dan kembalikan nilai yang sama dari A2: A10:

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

petunjuk. Jika anda merancang untuk menggunakan formula perlawanan indeks untuk pelbagai sel, pastikan anda mengunci dua julat dengan rujukan sel mutlak seperti $ A $ 2: $ A $ 10 dan $ C $ 2: $ C $ 10 untuk mengelakkan ubah bentuk apabila menyalin formula.

Cari dalam baris dan lajur menggunakan perlawanan indeks

Dalam contoh di atas, kami menggunakan Perlawanan Indeks sebagai pengganti untuk VLOOKUP klasik, mengembalikan nilai dari julat lajur tunggal yang telah ditetapkan. Tetapi bagaimana jika anda perlu melihat dalam pelbagai baris dan lajur? Dalam erti kata lain, bagaimana jika anda mahu melakukan apa yang dipanggil matriks atau carian dua hala ?

Ini mungkin terdengar rumit, tetapi formula ini sangat mirip dengan fungsi perlawanan indeks Excel asas dengan hanya satu perbezaan. Tebak apa itu?

Ringkasnya, gunakan dua fungsi perlawanan - satu untuk mendapatkan nombor baris dan yang lain untuk mendapatkan nombor lajur. Saya mengucapkan tahniah kepada mereka yang meneka dengan betul :)

Indeks (array, perlawanan ( nilai vlookup , lajur untuk mencari , 0), perlawanan ( nilai hlookup , baris untuk melihat , 0)) Sekarang, mari kita membina formula perlawanan perlawanan indeks untuk mencari penduduk negara tertentu pada tahun tertentu (dalam berjuta -juta).

Masukkan negara sasaran (nilai VLOOKUP) dalam G1 dan masukkan tahun sasaran (nilai HLOOKUP) dalam G2. Formula adalah seperti berikut:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

Bagaimana formula ini berfungsi

Setiap kali anda perlu memahami formula Excel yang kompleks, pecahkannya ke bahagian yang lebih kecil dan lihat apa fungsi setiap individu:

MATCH(G1,A2:A11,0) - Cari nilai ("China") dalam sel G1 dalam A2: A11 dan mengembalikan kedudukannya, iaitu 2.

MATCH(G2,B1:D1,0)) - Cari di B1: D1 untuk mendapatkan kedudukan nilai median sel G2 ("2015"), iaitu 3.

Nombor baris dan lajur di atas diserahkan kepada parameter yang sepadan dengan fungsi indeks:

INDEX(B2:D11, 2, 3)

Akibatnya, anda mendapat nilai di persimpangan baris 2 dan lajur 3 dalam julat B2: D11, iaitu nilai dalam sel D3. Adakah ia mudah? Ya!

Perlawanan Indeks Excel Cari pelbagai syarat

Jika anda mempunyai peluang untuk membaca tutorial Excel VLOOKUP kami, anda mungkin telah menguji formula VLOOKUP dengan pelbagai syarat. Walau bagaimanapun, batasan yang signifikan kaedah ini adalah keperluan untuk menambah lajur pembantu. Berita baiknya ialah fungsi perlawanan indeks Excel juga boleh mencari dua atau lebih syarat tanpa mengubah atau menyusun semula data sumber!

Berikut adalah formula perlawanan indeks umum dengan pelbagai syarat:

{= Index ( return_range , match (1, ( criteria1 = range1 ) ( kriteria2 = range2*), 0))} nota. Ini adalah formula array yang mesti dilakukan menggunakan shift Ctrl Enter pintasan.

Dalam jadual contoh di bawah, katakan anda ingin mencari jumlah berdasarkan dua syarat, pelanggan dan produk .

Formula perlawanan indeks berikut berfungsi dengan baik:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

Di mana C2: C10 adalah julat dari mana nilai akan dikembalikan, F1 adalah kriteria1, A2: A10 adalah julat berbanding dengan kriteria1, F2 adalah kriteria2, dan B2: B10 adalah julat berbanding kriteria2.

Ingatlah untuk memasukkan formula dengan betul dengan menekan CTRL Shift Enter, Excel secara automatik akan melampirkan formula dalam pendakap keriting, seperti yang ditunjukkan dalam tangkapan skrin:

Jika anda lebih suka tidak menggunakan formula array dalam lembaran kerja anda, tambahkan fungsi indeks lain ke formula dan lakukan dengan kunci masuk biasa:

Bagaimana formula ini berfungsi

Formula ini menggunakan kaedah yang sama seperti fungsi perlawanan indeks asas, melihat melalui satu lajur. Untuk menilai pelbagai syarat, anda membuat dua atau lebih tatasusunan nilai-nilai yang benar dan tidak dipadankan dengan setiap keadaan individu, dan kemudian membiak unsur-unsur yang sepadan dengan tatasusunan tersebut. Operasi pendaraban menukarkan benar dan palsu kepada 1 dan 0 dan menghasilkan array di mana 1 sepadan dengan baris yang memenuhi semua syarat. Fungsi perlawanan mencari "1" pertama dalam array untuk mencari nilai 1 dan melepasi kedudukannya ke indeks, yang mengembalikan nilai dalam baris itu dari lajur yang ditentukan.

Formula bukan panah bergantung kepada keupayaan asli fungsi indeks untuk memproses tatasusunan. Indeks kedua dikonfigurasi dengan 0 row_num , jadi ia melewati keseluruhan lajur untuk dipadankan.

Ini adalah penjelasan peringkat tinggi logik formula. Untuk butiran lengkap, lihat Perlawanan Indeks Excel dengan pelbagai syarat.

Perlawanan Indeks Excel dan Rata -Rata, Max, Min

Microsoft Excel mempunyai fungsi khas untuk mencari nilai minimum, maksimum, dan purata dalam julat. Tetapi bagaimana jika anda perlu mendapatkan nilai dari sel lain yang berkaitan dengan nilai -nilai ini? Dalam kes ini, gunakan fungsi maksimum, min, atau purata bersempena dengan perlawanan indeks.

Perlawanan Indeks Digabungkan dengan Max

Untuk mencari nilai maksimum dalam lajur D dan kembalikan nilai dari lajur C dalam baris yang sama, gunakan formula ini:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

Perlawanan Indeks Digabungkan dengan Min

Untuk mencari nilai minimum dalam lajur D dan ekstrak nilai yang berkaitan dari lajur C, gunakan formula berikut:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

Perlawanan indeks digabungkan dengan purata

Untuk mengira nilai yang paling dekat dengan purata dalam D2: D10 dan dapatkan nilai yang sama dari lajur C, gunakan formula berikut:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

Bergantung pada bagaimana data anda dianjurkan, berikan 1 atau -1 kepada parameter ketiga fungsi perlawanan (Match_Type):

• Jika lajur carian anda (dalam lajur kes kami D) disusun dalam urutan menaik , masukkan 1. Formula akan mengira nilai maksimum kurang daripada atau sama dengan nilai purata.
• Jika lajur carian anda disusun mengikut urutan menurun , masukkan -1. Formula akan mengira nilai minimum lebih besar daripada atau sama dengan purata.
• Jika array carian anda mengandungi nilai yang sama dengan purata, anda boleh memasukkan 0 untuk perlawanan yang tepat. Tiada penyortiran diperlukan.

Dalam contoh kami, penduduk dalam lajur D disusun mengikut urutan menurun, jadi kami menggunakan -1 sebagai jenis yang sepadan. Akibatnya, kami mendapat "Tokyo" kerana penduduknya (13,189,000) adalah perlawanan terdekat dengan lebih besar daripada purata (12,269,006).

Anda mungkin ingin tahu bahawa VLOOKUP juga boleh melakukan pengiraan sedemikian, tetapi sebagai formula array: vlookup vs purata, max, min.

Menggunakan perlawanan indeks dalam kombinasi dengan IFNA/IFERROR

Seperti yang anda perhatikan, jika formula perlawanan indeks dalam Excel gagal mencari nilai carian, ia menghasilkan ralat #N/A. Jika anda ingin menggantikan simbol ralat standard dengan sesuatu yang lebih bermakna, bungkus formula perlawanan indeks anda dalam fungsi IFNA. Contohnya:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

Sekarang, jika seseorang memasuki nilai yang tidak wujud dalam jadual carian, formula itu secara jelas akan memaklumkan kepada pengguna bahawa tiada perlawanan yang dijumpai:

Jika anda ingin menangkap semua kesilapan, bukan hanya #n/a, gunakan fungsi iferror dan bukannya IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

Ingatlah bahawa dalam banyak kes, ia mungkin tidak bijak untuk menutup semua kesilapan, kerana mereka memberi amaran kepada anda tentang kemungkinan kesalahan dalam formula.

Inilah cara menggunakan indeks dan perlawanan dalam Excel. Saya harap contoh formula kami akan membantu anda dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun buku kerja latihan

Contoh Perlawanan Indeks Excel (fail .xlsx)

Atas ialah kandungan terperinci Perlawanan Indeks Excel vs Vlookup - Contoh Formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!