Formula generik
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Ringkasan
Untuk mencari nilai dengan mencocokkan beberapa lajur, anda boleh menggunakan formula larik berdasarkan MMULT, TRANSPOSE, COLUMN, dan INDEX. Dalam contoh yang ditunjukkan, formula dalam H4 adalah:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
di mana "nama" adalah julat bernama C4: E7, dan "kumpulan" adalah julat bernama B4: B7. Rumus mengembalikan kumpulan yang menjadi milik setiap nama.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan shift enter.
Penjelasan
Berfungsi dari dalam ke luar, kriteria logik yang digunakan dalam formula ini adalah:
--(names=G4)
di mana nama adalah julat bernama C4: E7. Ini menghasilkan hasil BENAR / SALAH untuk setiap nilai dalam data, dan negatif dua kali ganda menyatukan nilai BENAR BENAR menjadi 1 dan 0 untuk menghasilkan susunan seperti ini:
(0,0,0;1,0,0;0,0,0;0,0,0)
Susunan ini terdiri daripada 4 baris dengan 3 lajur, yang sepadan dengan struktur "nama".
Susunan kedua dibuat dengan ungkapan ini:
TRANSPOSE(COLUMN(names)^0))
Fungsi COLUMN digunakan untuk membuat susunan numerik dengan 3 lajur dan 1 baris, dan TRANSPOSE mengubah susunan ini menjadi 1 lajur dan 3 baris. Menaikkan ke kekuatan sifar hanya menukar semua nombor dalam larik menjadi 1. Fungsi MMULT kemudian digunakan untuk melakukan pendaraban matriks:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
dan hasilnya masuk ke fungsi MATCH sebagai array, dengan 1 sebagai nilai pencarian:
MATCH(1,(0;1;0;0),0)
Fungsi MATCH mengembalikan kedudukan pertandingan pertama, yang sesuai dengan baris kriteria perjumpaan pertemuan baris pertama yang dibekalkan. Ini dimasukkan ke INDEX sebagai nombor baris, dengan julat "kumpulan" yang dinamakan sebagai tatasusunan:
=INDEX(groups,2)
Akhirnya, INDEX mengembalikan "Bear", kumpulan yang menjadi milik Adam.
Literal mengandungi kriteria
Untuk memeriksa nilai teks tertentu dan bukannya padanan tepat, anda boleh menggunakan fungsi ISNUMBER dan SEARCH. Contohnya, untuk memadankan sel yang mengandungi "epal", anda boleh menggunakan:
=ISNUMBER(SEARCH("apple",data))
Formula ini dijelaskan di sini.
