Indeks Padanan Pelbagai Kriteria Baris dan Lajur
Kita semua menggunakan VLOOKUP hari demi hari untuk mengambil data dan juga kita menyedari hakikat bahawa VLOOKUP dapat mengambil data dari kiri ke kanan, jadi nilai pencarian harus selalu berada di sebelah kiri lajur hasil. Namun, kami mempunyai beberapa alternatif yang boleh digunakan sebagai alternatif kepada fungsi VLOOKUP di excel. Dengan teknologi canggih, kita dapat menggunakan formula INDEX + MATCH ini untuk memadankan pelbagai kriteria untuk baris dan lajur. Oleh itu, artikel khas ini akan membawa anda secara terperinci mengenai teknik ini.
Bagaimana Menggunakan Formula INDEX + MATCH untuk Memadankan Pelbagai Kriteria?
Di sini kami menerangkan cara menggunakan formula padanan indeks + untuk memadankan pelbagai kriteria untuk baris dan lajur dengan contoh.
Anda boleh memuat turun Templat Excel Pencocokan Pelbagai Indeks Indeks ini di sini - Templat Excel Pencocokan Indeks Pelbagai KriteriaContoh # 1 - Rumusan INDEX + MATCH
Bukan sebahagian besar fungsi pencarian pengguna excel melampaui VLOOKUP, sebabnya mungkin banyak. Bagaimanapun, mari kita perkenalkan formula ini dengan mudah sebelum kita melangkah ke peringkat lanjutan.
Contohnya, lihat struktur data di bawah ini di excel.
Kami mempunyai nama "Sales Rep" dan nilai penjualan masing-masing. Sebaliknya, kami mempunyai senarai drop-down "Rep Sale" di sel D2.
Berdasarkan pilihan yang kami buat dari senarai drop-down jumlah penjualan mesti muncul di sel E2.
Masalahnya ialah kita tidak dapat menerapkan formula VLOOKUP kerana nilai pencarian "Sales Rep" berada di sebelah kanan lajur hasil "Sales" jadi dalam kes ini kita dapat menggunakan formula nilai pencarian gabungan INDEX + MATCH.
INDEX mencari nilai nombor baris yang disebut dalam julat A2: A11 dan dalam julat ini, kita perlu memberikan dari baris mana kita memerlukan nilai penjualan. Nilai baris ini didasarkan pada nama "Sales Rep" yang dipilih dalam daftar drop-down di excel, jadi fungsi MATCH mencari nombor baris "Sales Rep" dalam julat B2: B11 dan mengembalikan nombor baris dari nilai yang sesuai .
Contoh # 2 - Pelbagai Kriteria dalam Formula INDEX + MATCH
Sekarang kita mempunyai struktur data seperti yang berikut.
Kami mempunyai nilai penjualan bulanan "Sales Rep". Dari jadual ini, kita memerlukan hasil yang dinamik seperti di sel A15 saya telah membuat senarai drop-down "Sales Rep" dan di sel B14 saya telah membuat senarai drop-down "Bulan".
Berdasarkan pemilihan yang dibuat di kedua sel ini, formula kita harus mengambil data dari jadual di atas.
Sebagai contoh, jika saya memilih "Rep 8" dan "Apr" maka harus menunjukkan nilai penjualan "Rep 8" untuk bulan "Apr".
Jadi, dalam kes ini, kita perlu memadankan baris dan lajur. Ikuti langkah di bawah untuk menerapkan formula agar sesuai dengan baris dan lajur.
Langkah 1: Buka fungsi INDEX di sel B15.
Langkah 2: Argumen pertama fungsi INDEX adalah "Array" iaitu dari julat sel mana kita memerlukan hasilnya. Jadi, dalam kes ini, kita memerlukan nilai jualan jadi pilih julat sel dari B2 hingga G11.
Langkah 3: Argumen seterusnya fungsi INDEX dari baris mana julat yang dipilih kita memerlukan hasilnya. Dalam kes ini, kita perlu sampai pada angka baris "Sales Rep" berdasarkan pilihan yang dibuat di sel drop down sel A15. Oleh itu, untuk mendapatkan nombor baris secara dinamik berdasarkan pemilihan fungsi MATCH terbuka.
Langkah 4: LIHAT NILAI fungsi MATCH adalah "Sales Rep" jadi pilih sel A15 sebagai rujukan.
Langkah 5: Lookup Array akan menjadi julat nama "Sales Rep" di jadual utama. Oleh itu, pilih julat seperti A2 hingga A11.
Langkah 6: Jenis Pertandingan fungsi MATCH akan tepat jadi masukkan sifar sebagai nilai argumen.
Langkah 7: Argumen seterusnya fungsi INDEX adalah "Column Number" iaitu dari julat sel yang dipilih dari lajur mana kita memerlukan hasilnya. Ini bergantung pada bulan yang kita pilih dari senarai drop-down sel B14. Jadi untuk mendapatkan nombor lajur secara automatik buka fungsi MATCH yang lain.
Langkah 8: Nilai carian masa ini akan menjadi nama bulan jadi pilih sel B14 sebagai rujukan.
Langkah 9: Susunan carian akan merangkumi sel bulan dalam jadual utama iaitu dari B1 hingga G1.
Langkah 10: Argumen terakhir adalah jenis padanan, pilih "Padanan Tepat" sebagai kriteria. Tutup dua tanda kurung dan tekan butang enter untuk mendapatkan hasilnya.
Seperti yang dapat kita lihat di atas, kita telah memilih "Rep 6" dan "Apr" sebagai bulan dan formula kita telah mengembalikan nilai penjualan untuk bulan "Apr" untuk "Rep 6".
Catatan: Sel berwarna kuning adalah rujukan untuk anda.
Perkara yang Perlu Diingat
- Kombinasi INDEX + MATCH boleh menjadi lebih hebat daripada formula VLOOKUP.
- INDEX & MATCH dapat memadankan tajuk baris dan lajur dan mengembalikan hasilnya dari jadual tengah.
- PERTANDINGAN dapat mengembalikan nombor baris dan nombor lajur tajuk meja kedua-dua baris & lajur.
