Formula generik
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Ringkasan
Untuk mencari nilai dengan INDEX dan MATCH, menggunakan pelbagai kriteria, anda boleh menggunakan formula array. Dalam contoh yang ditunjukkan, formula dalam H8 adalah:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Catatan: ini adalah formula larik, dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Penjelasan
Ini adalah formula yang lebih maju. Untuk asas, lihat Cara menggunakan INDEX dan PERTANDINGAN.
Biasanya, formula INDEX MATCH dikonfigurasi dengan set MATCH untuk melihat melalui julat satu lajur dan memberikan padanan berdasarkan kriteria yang diberikan. Tanpa menggabungkan nilai dalam kolom pembantu, atau dalam rumus itu sendiri, tidak ada cara untuk menyediakan lebih dari satu kriteria.
Rumus ini mengatasi had ini dengan menggunakan logik boolean untuk membuat susunan satu dan sifar untuk mewakili baris yang sesuai dengan semua 3 kriteria, kemudian menggunakan MATCH untuk mencocokkan 1 yang pertama dijumpai. Susunan sementara dan sifar dihasilkan dengan coretan ini:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Di sini kita membandingkan item di H5 dengan semua item, ukuran di H6 dengan semua ukuran, dan warna di H7 dengan semua warna. Hasil awal adalah tiga susunan hasil BENAR / SALAH seperti ini:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Petua: gunakan F9 untuk melihat hasil ini. Cukup pilih ungkapan di bar formula, dan tekan F9.
Operasi matematik (pendaraban) mengubah nilai BENAR BENAR menjadi 1s dan 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Selepas pendaraban, kami mempunyai satu array seperti ini:
(0;0;1;0;0;0;0)
yang dimasukkan ke fungsi MATCH sebagai array pencarian, dengan nilai pencarian 1:
MATCH(1,(0;0;1;0;0;0;0))
Pada ketika ini, formula adalah formula INDEX MATCH standard. Fungsi MATCH mengembalikan 3 ke INDEX:
=INDEX(E5:E11,3)
dan INDEX mengembalikan hasil akhir $ 17.00.
Visualisasi susun atur
Susunan yang dijelaskan di atas sukar untuk dilihat. Gambar di bawah menunjukkan idea asas. Lajur B, C, dan D sesuai dengan data dalam contoh. Lajur F dibuat dengan mengalikan tiga lajur bersama. Ini adalah array yang diserahkan kepada MATCH.
Versi bukan larik
Adalah mungkin untuk menambahkan INDEX lain pada formula ini, mengelakkan keperluan memasukkan sebagai formula larik dengan kawalan + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Fungsi INDEX dapat menangani array secara semula jadi, jadi INDEX kedua hanya ditambahkan untuk "menangkap" array yang dibuat dengan operasi logik boolean dan mengembalikan array yang sama sekali lagi ke MATCH. Untuk melakukan ini, INDEX dikonfigurasikan dengan baris sifar dan satu lajur. Tipuan baris sifar menyebabkan INDEX mengembalikan lajur 1 dari array (yang sudah menjadi satu lajur).
Mengapa anda mahukan versi bukan larik? Kadang kala, orang lupa memasukkan formula larik dengan kawalan + shift + enter, dan formula mengembalikan hasil yang tidak betul. Jadi, formula bukan larik lebih "kalis peluru". Walau bagaimanapun, pertukaran adalah formula yang lebih kompleks.
Catatan: Di Excel 365, tidak perlu memasukkan formula susunan dengan cara khas.
