„Excel“

Indeksuokite ir sutapkite keliuose stulpeliuose

Index Match Multiple Columns

„Excel“ formulė: indeksuokite ir suderinkite kelis stulpeliusBendroji formulė | _+_ | Santrauka

Norėdami ieškoti vertės, suderindami kelis stulpelius, galite naudoti masyvo formulė remiantis MMULT, TRANSPOSE, COLUMN ir INDEX. Pateiktame pavyzdyje H4 formulė yra tokia:



{= INDEX (rng1, MATCH (1, MMULT (--(rng2=critera), TRANSPOSE ( COLUMN (rng2)^0)),0))}

kur yra „vardai“ pavadintas diapazonas C4: E7, o „grupės“ yra pavadintas diapazonas B4: B7. Formulė grąžina grupę, kuriai priklauso kiekvienas vardas.

kaip naudojate skaičiavimo funkciją programoje „Excel“

Pastaba: tai yra masyvo formulė ir turi būti įvestas naudojant valdymo poslinkio įvestį.





Paaiškinimas

Dirbant iš vidaus, šioje formulėje naudojami loginiai kriterijai:

 
{= INDEX (groups, MATCH (1, MMULT (--(names=G4), TRANSPOSE ( COLUMN (names)^0)),0))}

kur pavadinimai yra pavadintas diapazonas C4: E7. Tai sukuria TRUE / FALSE rezultatą kiekvienai duomenų vertei, o dvigubas neigiamas priverčia TRUE FALSE reikšmes 1 ir 0, kad gautų tokį masyvą:



 
--(names=G4)

Šį masyvą sudaro 4 eilutės po 3 stulpelius, atitinkančios „vardų“ struktūrą.

Antrasis masyvas sukurtas naudojant šią išraišką:

 
{0,0,01,0,00,0,00,0,0}

Funkcija COLUMN naudojama norint sukurti skaitinį masyvą su 3 stulpeliais ir 1 eilute, o TRANSPOSE konvertuoja šį masyvą į 1 stulpelį ir 3 eilutes. Padidinus iki nulio galios, visi masyvo skaičiai tiesiog paverčiami į 1. Tada funkcija MMULT naudojama matricos daugybai atlikti:

kaip rasti liekanų „Excel“
 
 TRANSPOSE ( COLUMN (names)^0))

ir rezultatas patenka į MATCH funkciją kaip masyvas, o 1 kaip paieškos vertė:

rasti ir pakeisti funkciją programoje „Excel“
 
 MMULT ({0,0,01,0,00,0,00,0,0},{111})

Funkcija MATCH grąžina pirmosios atitikties poziciją, kuri atitinka pirmosios atitinkančios eilutės eilutę, atitinkančią pateiktus kriterijus. Tai įtraukiama į INDEX kaip eilutės numerį, o įvardytas diapazonas „grupės“ yra masyvas:

 
 MATCH (1,{0100},0)

Galiausiai, INDEX grąžina „Lokį“, grupei „Adomas“.

„Literal“ yra kriterijų

Norėdami patikrinti konkrečias teksto reikšmes, o ne tikslią atitiktį, galite naudoti funkcijas ISNUMBER ir SEARCH kartu. Pavyzdžiui, jei norite suderinti langelius, kuriuose yra „obuolys“, galite naudoti:

 
= INDEX (groups,2)

Ši formulė paaiškinta čia .

Autorius Dave'as Brunsas


^