Carian Pelbagai Kriteria Dengan INDEX-MATCH
Padankan beberapa lajur serentak untuk mengenal pasti satu baris
Carian Pelbagai Kriteria Dengan INDEX-MATCH ialah pelajaran Excel Formulas Academy percuma di CoddyKit. Ini ialah pelajaran 3 daripada 4. Sebanyak 3 pelajaran dalam laluan pembelajaran ini boleh dibaca sepenuhnya secara percuma — selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan praktikal dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Excel Formulas Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Excel Formulas Academy merangkumi sejumlah 4 pelajaran.
Apabila Satu Kunci Tidak Mencukupi
Kadangkala satu lajur tidak dapat mengenal pasti sesuatu baris secara unik. Anda mungkin memerlukan harga produk dalam saiz tertentu atau gaji pekerja di jabatan tertentu.
Ini memerlukan carian berbilang kriteria: pemadanan berdasarkan dua lajur atau lebih serentak untuk menentukan satu baris dengan tepat.
INDEX-MATCH mengendalikannya dengan kemas dengan menggabungkan syarat menjadi satu ujian padanan, tanpa memerlukan lajur bantuan tambahan.
Pendekatan Lajur Bantuan
Model mental paling mudah ialah menggabungkan lajur kunci anda menjadi satu. Tambahkan lajur bantuan yang mencantumkan produk dan saiz, kemudian lakukan carian biasa terhadapnya.
Contohnya, sel bantuan mungkin mengandungi =A2&"|"&B2, yang menghasilkan "Shirt|Large". Kemudian anda MATCH "Shirt|Large" terhadap lajur gabungan itu.
Kaedah ini berfungsi, tetapi menjadikan helaian anda berserabut. Bahagian seterusnya menunjukkan cara melangkau lajur bantuan sepenuhnya.
=A2 & "|" & B2Memadankan Dua Syarat Serentak
Helah utamanya: darabkan dua ujian syarat di dalam MATCH.
(A2:A10=G1) menghasilkan tatasusunan TRUE/FALSE untuk kriteria pertama. (B2:B10=G2) melakukan perkara yang sama untuk kriteria kedua. Apabila didarabkan, (A2:A10=G1)*(B2:B10=G2) menghasilkan 1 hanya apabila kedua-duanya TRUE dan 0 di tempat lain.
MATCH kemudian mencari nilai 1 untuk menemukan baris yang memenuhi kedua-dua syarat.
=(A2:A10=G1) * (B2:B10=G2)Mengapa Pendaraban Bermaksud AND
Dalam hamparan, TRUE bertindak sebagai 1 dan FALSE sebagai 0. Mendarabkan kedua-duanya meniru AND logik:
- 1 darab 1 = 1 (kedua-dua syarat dipenuhi)
- 1 darab 0 = 0
- 0 darab 1 = 0
- 0 darab 0 = 0
Jadi hanya baris yang memenuhi kedua-dua kriteria menghasilkan 1. Setiap baris lain menjadi 0. Angka 1 tunggal itu menandakan baris yang kita kehendaki.
Mencari Baris Dengan MATCH
Sekarang balut tatasusunan yang didarabkan dalam MATCH dan cari nilai tepat 1.
MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) mengembalikan kedudukan baris pertama yang kedua-dua syaratnya TRUE.
Jika gabungan padanan berada pada baris data keempat, MATCH mengembalikan 4. Kedudukan itulah yang diperlukan INDEX untuk mendapatkan jawapan.
=MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0)Mengembalikan Nilai Dengan INDEX
Berikan hasil MATCH itu kepada INDEX pada lajur yang sebenarnya anda kehendaki, contohnya harga dalam C2:C10.
Formula lengkapnya bermaksud: daripada C2:C10, kembalikan nilai pada baris yang produknya sama dengan G1 dan saiznya sama dengan G2.
Ini ialah carian berbilang kriteria sebenar tanpa lajur bantuan dan tanpa menyusun semula data anda.
=INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0))Memasukkannya Dengan Betul
Formula ini menilai tatasusunan syarat. Dalam Excel moden dan Google Sheets, anda hanya perlu menekan Enter dan formula itu akan berfungsi.
Dalam Excel lama (sebelum tatasusunan dinamik), anda mesti mengesahkannya sebagai formula tatasusunan dengan Ctrl+Shift+Enter, yang menambah kurungan berlingkar. Jika hasil anda salah atau memaparkan ralat dalam Excel lama, langkah pengesahan itu biasanya merupakan perkara yang terlepas.
=INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0))Menambah Syarat Ketiga
Memerlukan tiga kriteria? Darabkan sahaja satu lagi ujian. Andaikan anda juga mahu memadankan warna dalam lajur D dengan input G3.
Setiap faktor (range=criterion) tambahan mengecilkan hasil dengan lebih lanjut. Hanya baris yang semua syaratnya TRUE mengekalkan hasil darab 1; mana-mana FALSE menjadikan keseluruhan hasil darab 0.
Corak ini boleh diperluas kepada sebanyak mana lajur yang anda perlukan.
=INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2)*(D2:D10=G3), 0))Contoh Lengkap
Data: A = produk, B = saiz, C = harga. Anda mahu harga "Shirt" dalam saiz "Large".
- G1 = "Shirt", G2 = "Large".
- Tatasusunan syarat menghasilkan 1 hanya pada baris Shirt+Large, katakan baris 4.
- MATCH(1, ..., 0) mengembalikan 4.
- INDEX(C2:C10, 4) mengembalikan harga baris itu.
Ubah mana-mana input dan formula itu akan mencari semula baris yang betul serta-merta.
=INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0))Perkara yang Perlu Dielakkan dan Keselamatan
Ingat perkara berikut:
- Julat yang sama: setiap julat syarat dan lajur INDEX mesti mempunyai ketinggian yang sama.
- Tiada padanan: jika tiada baris memenuhi semua kriteria, MATCH mengembalikan #N/A. Balut keseluruhan formula dengan
IFERROR. - Pendua: jika lebih daripada satu baris sepadan, MATCH hanya mengembalikan yang pertama. Jadikan kriteria anda cukup khusus supaya hasilnya unik.
=IFERROR(INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0)), "No match")SUMPRODUCT sebagai Alternatif
Jika beberapa baris boleh sepadan dan anda lebih suka menjumlahkan nilainya daripada mendapatkan satu nilai, SUMPRODUCT ialah alternatif yang kemas kepada INDEX-MATCH yang dimasukkan sebagai tatasusunan.
Ia mendarabkan tatasusunan syarat dengan lajur nilai dan menjumlahkan hasilnya, jadi hanya baris yang memenuhi kedua-dua kriteria menyumbang kepada jumlah. Ctrl+Shift+Enter tidak diperlukan kerana SUMPRODUCT mengendalikan tatasusunan secara asli.
Gunakan INDEX-MATCH untuk mendapatkan satu nilai padanan; gunakan SUMPRODUCT untuk mengagregatkan semua padanan.
=SUMPRODUCT((A2:A10=G1) * (B2:B10=G2) * C2:C10)Semakan Pantas
Uji pengetahuan anda tentang carian berbilang kriteria.
Imbas Kembali Pelajaran
Untuk carian berbilang kriteria dengan INDEX-MATCH:
- Darabkan tatasusunan syarat bersama-sama:
(A=G1)*(B=G2)memberikan 1 hanya pada tempat semua syarat dipenuhi (logik AND). MATCH(1, ..., 0)mencari kedudukan baris tersebut.INDEX(returnCol, position)mengembalikan nilainya.
Tambahkan lebih banyak faktor *(range=criterion) untuk syarat tambahan, pastikan semua julat mempunyai ketinggian yang sama, sahkan dengan Ctrl+Shift+Enter dalam Excel versi lama, dan lindungi formula dengan IFERROR.
=INDEX(C2:C10, MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0))
Pelajari Excel dengan tutor kecerdasan buatan — percuma
Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.
- Kursus
- 30
- Pelajaran
- 120
Soalan Lazim
Adakah pelajaran “Carian Pelbagai Kriteria Dengan INDEX-MATCH” percuma?
Ya — sebanyak 3 pelajaran dalam laluan pembelajaran Excel Formulas Academy, termasuk “Carian Pelbagai Kriteria Dengan INDEX-MATCH”, boleh dibaca sepenuhnya secara percuma di web ini. Selepas itu, CoddyKit PRO membuka akses kepada semua pelajaran, serta latihan interaktif dengan penyunting kod terbina dalam dan tutor kecerdasan buatan yang tersedia 24/7. Kursus Excel Formulas Academy merangkumi sejumlah 4 pelajaran.
Apakah yang akan saya pelajari dalam “Carian Pelbagai Kriteria Dengan INDEX-MATCH”?
Padankan beberapa lajur serentak untuk mengenal pasti satu baris Anda berlatih Excel Formulas Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.
Adakah saya memerlukan pengalaman untuk memulakan Excel Formulas Academy?
Tiada pengalaman terdahulu diperlukan. Pembelajaran Excel Formulas Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 3 daripada 4.
Berapa lamakah pelajaran “Carian Pelbagai Kriteria Dengan INDEX-MATCH” diambil?
Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.
Bolehkah saya menulis dan menjalankan kod dalam pelajaran Excel Formulas Academy ini?
Ya. Setiap pelajaran Excel Formulas Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.
Semua pelajaran dalam kursus ini
- Carian Dua Hala Dengan INDEX-MATCH-MATCH
- Mencari Nilai Padanan Terakhir
- Carian Pelbagai Kriteria Dengan INDEX-MATCH
- Padanan Anggaran untuk Jadual Bertingkat