Perlawanan Indeks Excel dengan pelbagai kriteria - Contoh Formula

Tutorial menunjukkan cara mencari dengan pelbagai kriteria dalam Excel menggunakan indeks dan perlawanan dan beberapa cara lain.

Walaupun Microsoft Excel menyediakan fungsi khas untuk carian menegak dan mendatar, pengguna pakar biasanya menggantikannya dengan perlawanan indeks, yang lebih tinggi daripada VLOOKUP dan HLOOKUP dalam pelbagai cara. Antara lain, ia boleh mencari dua atau lebih kriteria dalam lajur dan baris. Tutorial ini menerangkan sintaks dan mekanik dalaman secara terperinci supaya anda dapat dengan mudah menyesuaikan formula untuk keperluan khusus anda. Untuk menjadikan contoh lebih mudah untuk diikuti, anda dialu -alukan untuk memuat turun buku kerja sampel kami.

• Perlawanan indeks dengan pelbagai kriteria
• Perlawanan Indeks Bukan Array dengan Dua atau Lebih Kriteria
• Indeks Memadankan pelbagai kriteria dalam baris dan lajur (pencarian matriks)

Perlawanan Indeks Excel dengan pelbagai kriteria

Apabila bekerja dengan pangkalan data yang besar, anda kadang -kadang mungkin mendapati diri anda dalam keadaan apabila anda perlu mencari sesuatu tetapi tidak mempunyai pengenal unik untuk carian. Dalam kes ini, carian dengan beberapa syarat adalah satu -satunya penyelesaian.

Untuk mencari nilai berdasarkan pelbagai kriteria dalam lajur berasingan, gunakan formula generik ini:

{= Index ( return_range , match (1, ( kriteria1 = range1 ) * ( kriteria2 = range2 ) * (...), 0))}

Di mana:

• Return_range adalah julat dari mana untuk mengembalikan nilai.
• Kriteria1 , Kriteria2 , ... adalah syarat yang perlu dipenuhi.
• Range1 , Range2 , ... adalah julat di mana kriteria yang sepadan harus diuji.

Nota penting! Ini adalah formula array dan ia mesti diselesaikan dengan CTRL Shift Enter . Ini akan melampirkan formula anda dalam {kurungan keriting}, yang merupakan tanda visual formula array dalam Excel. Jangan cuba menaip pendakap secara manual, yang tidak akan berfungsi!

Formula ini adalah versi lanjutan perlawanan indeks ikonik yang mengembalikan pertandingan berdasarkan kriteria tunggal. Untuk menilai pelbagai kriteria, kami menggunakan operasi pendaraban yang berfungsi sebagai pengendali dan dalam formula array. Di bawah, anda akan menemui contoh kehidupan sebenar dan penjelasan terperinci mengenai logik.

Petua. Dalam Excel 365 dan 2021, anda boleh menggunakan formula Xlookup dengan pelbagai kriteria.

Perlawanan Indeks dengan beberapa kriteria - Contoh Formula

Untuk contoh ini, kami akan menggunakan jadual dalam format yang dipanggil "Flat-File" dengan setiap kombinasi kriteria berasingan (item-bulan-bulan dalam kes kami) pada barisnya sendiri. Matlamat kami adalah untuk mendapatkan angka jualan untuk item tertentu di rantau dan bulan tertentu.

Dengan data sumber dan kriteria dalam sel -sel berikut:

• Return_range (jualan) - D2: D13
• Kriteria1 (Sasaran Wilayah) - G1
• Kriteria2 (Bulan Sasaran) - G2
• Kriteria3 (item sasaran) - G3
• Range1 (Kawasan) - A2: A13
• Range2 (Bulan) - B2: B13
• Range3 (Item) - C2: C13

Formula mengambil bentuk berikut:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Masukkan formula, katakan dalam G4, selesaikannya dengan menekan CTRL Shift Enter dan anda akan mendapat hasil berikut:

Bagaimana formula ini berfungsi

Bahagian yang paling rumit adalah fungsi perlawanan, jadi mari kita tahu terlebih dahulu:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Seperti yang anda ingat, perlawanan (lookup_value, lookup_array, [match_type]) mencari nilai carian dalam array carian dan mengembalikan kedudukan relatif nilai itu dalam array.

Dalam formula kami, hujah -hujah adalah seperti berikut:

• Lookup_Value : 1
• Lookup_array : (g1 = a2: a13) * (g2 = b2: b13) * (g3 = c2: c13)
• Match_Type : 0

Hujah ¹ adalah Crystal Clear - Fungsi mencari nombor 1. Argumen 3 ^RD ditetapkan ke 0 bermaksud "padanan tepat", iaitu formula mengembalikan nilai pertama yang ditemui yang sama persis dengan nilai carian.

Persoalannya - mengapa kita mencari "1"? Untuk mendapatkan jawapannya, mari kita lihat dengan lebih dekat pada array carian di mana kita membandingkan setiap kriteria terhadap julat yang sama: rantau sasaran di G1 terhadap semua wilayah (A2: A13), bulan sasaran dalam G2 terhadap semua bulan (B2: B13) dan item sasaran dalam G3 terhadap semua item (C2: C13). Hasil pertengahan adalah 3 tatasusunan yang benar dan palsu di mana benar mewakili nilai yang memenuhi syarat yang diuji. Untuk memvisualisasikan ini, anda boleh memilih ungkapan individu dalam formula dan tekan kekunci F9 untuk melihat apa yang setiap ungkapan menilai:

Operasi pendaraban mengubah nilai sebenar dan palsu menjadi 1 dan 0, masing -masing:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

Dan kerana mendarab dengan 0 selalu memberikan 0, array yang dihasilkan mempunyai 1 hanya dalam baris yang memenuhi semua kriteria:

{0;0;1;0;0;0;0;0;0;0;0;0}

Arahan di atas pergi ke argumen lookup_array perlawanan. Dengan lookup_value 1, fungsi mengembalikan kedudukan relatif baris yang mana semua kriteria adalah benar (baris 3 dalam kes kita). Sekiranya terdapat beberapa 1 dalam array, kedudukan yang pertama dikembalikan.

Nombor yang dikembalikan oleh perlawanan pergi terus ke argumen row_num indeks (array, row_num, [column_num]) fungsi:

=INDEX(D2:D13, 3)

Dan ia menghasilkan hasil $ 115, iaitu nilai 3 ^RD dalam array D2: D13.

Formula Perlawanan Indeks Bukan Array dengan Pelbagai Kriteria

Formula array yang dibincangkan dalam contoh sebelumnya berfungsi dengan baik untuk pengguna yang berpengalaman. Tetapi jika anda membina formula untuk orang lain dan seseorang tidak tahu fungsi array, mereka mungkin secara tidak sengaja memecahkannya. Sebagai contoh, pengguna boleh mengklik formula anda untuk menelitinya, dan kemudian tekan ENTER bukannya CTRL Shift Enter . Dalam kes sedemikian, adalah bijak untuk mengelakkan tatasusunan dan menggunakan formula biasa yang lebih banyak peluru:

Indeks ( return_range , padanan (1, indeks (( kriteria1 = range1 ) * ( kriteria2 = range2 ) * (..), 0, 1), 0))

Untuk dataset sampel kami, formula berjalan seperti berikut:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Bagaimana formula ini berfungsi

Oleh kerana fungsi indeks dapat memproses array secara asli, kami menambah indeks lain untuk mengendalikan array 1 dan 0 yang dicipta dengan mengalikan dua atau lebih array benar/palsu. Indeks kedua dikonfigurasikan dengan argumen 0 row_num untuk formula untuk mengembalikan keseluruhan lajur lajur dan bukannya satu nilai. Oleh kerana ia adalah satu-lajur, kami dapat dengan selamat membekalkan 1 untuk column_num :

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

Arahan ini diserahkan kepada fungsi perlawanan:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

Perlawanan mencari nombor baris yang mana semua kriteria itu benar (lebih tepat lagi, kedudukan relatif baris itu dalam array yang ditentukan) dan melepasi nombor itu kepada argumen row_num indeks pertama:

=INDEX(D2:D13, 3)

Perlawanan indeks dengan pelbagai kriteria dalam baris dan lajur

Contoh ini menunjukkan cara melakukan carian dengan menguji dua atau lebih kriteria dalam baris dan lajur. Sebenarnya, ia adalah kes yang lebih kompleks dari apa yang dipanggil "pencarian matriks" atau "carian dua arah" dengan lebih daripada satu baris header.

Inilah formula perlawanan indeks generik dengan pelbagai kriteria dalam baris dan lajur:

{= Index ( table_array , match ( vlookup_value , lookup_column , 0), match ( hlookup_value1 & hlookup_value2 , lookup_row1 & lookup_row2 , 0))}

Di mana:

Table_array - Peta atau kawasan untuk mencari dalam, iaitu semua nilai data tidak termasuk tajuk lajur dan baris.

VLOOKUP_VALUE - Nilai yang anda cari secara menegak dalam lajur.

Lookup_column - Julat lajur untuk mencari, biasanya tajuk baris.

Hlookup_value1, hlookup_value2, ... - nilai yang anda cari secara mendatar dalam baris.

Lookup_row1, lookup_row2, ... - julat baris untuk mencari, biasanya tajuk lajur.

Nota penting! Untuk formula berfungsi dengan betul, ia mesti dimasukkan sebagai formula array dengan shift CTRL ENTER .

Ia adalah variasi formula carian dua hala klasik yang mencari nilai di persimpangan baris dan lajur tertentu. Perbezaannya ialah anda menggabungkan beberapa nilai dan julat hlookup untuk menilai pelbagai tajuk lajur. Untuk lebih memahami logik, sila pertimbangkan contoh berikut.

Carian Matrix dengan pelbagai kriteria - Contoh Formula

Dalam jadual sampel di bawah, kami akan mencari nilai berdasarkan tajuk baris (item) dan 2 tajuk lajur (Kawasan dan Vendor). Untuk menjadikan formula lebih mudah dibina, mari kita tentukan semua kriteria dan julat:

• TABLE_ARRAY - B3: E4
• Vlookup_value (item sasaran) - H1
• Lookup_column (tajuk baris: item) - A3: A4
• HLOOKUP_VALUE1 (rantau sasaran) - H2
• Hlookup_value2 (vendor sasaran) - H3
• Lookup_row1 (Tajuk Lajur 1: Kawasan) - B1: E1
• Lookup_row2 (tajuk lajur 2: vendor) - B2: E2

Dan sekarang, membekalkan hujah -hujah ke dalam formula generik yang dijelaskan di atas, dan anda akan mendapat hasil ini:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Ingatlah untuk melengkapkan formula dengan menekan shift Ctrl Enter pintasan, dan carian matriks anda dengan pelbagai kriteria akan dilakukan dengan jayanya:

Bagaimana formula ini berfungsi

Seperti yang kita cari secara menegak dan mendatar, kita perlu membekalkan fungsi baris dan lajur untuk fungsi indeks (array, row_num, column_num).

ROW_NUM disampaikan oleh Match (H1, A3: A5, 0) yang membandingkan item sasaran (epal) dalam H1 terhadap tajuk ROW dalam A3: A5. Ini memberikan hasil daripada 1 kerana "epal" adalah item pertama dalam julat yang ditentukan.

Column_num dilaksanakan dengan menggabungkan 2 nilai carian dan 2 array carian: perlawanan (H2 & H3, B1: E1 & B2: E2, 0))

Faktor utama untuk kejayaan ialah nilai carian sepadan dengan tajuk lajur dengan tepat dan digabungkan dengan susunan yang sama. Untuk memvisualisasikan ini, pilih dua hujah pertama dalam formula perlawanan, tekan F9 , dan anda akan melihat apa yang setiap hujah menilai:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

Sebagai "Northvendor 2" adalah elemen kedua dalam array, fungsi kembali 2.

Pada ketika ini, formula perlawanan indeks dua dimensi yang panjang berubah menjadi mudah ini:

=INDEX(B3:E5, 1, 2)

Dan mengembalikan nilai di persimpangan baris pertama dan lajur ke -2 dalam julat B3: E5, yang merupakan nilai dalam sel C3.

Itulah cara mencari kriteria berganda dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Indeks Excel Memadankan pelbagai kriteria (fail .xlsx)

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