Bendroji formulė
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Santrauka
Norėdami ieškoti vertės suderindami kelis stulpelius, galite naudoti masyvo formulę, pagrįstą MMULT, TRANSPOSE, COLUMN ir INDEX. Parodytame pavyzdyje formulė H4 yra:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
kur „vardai“ yra pavadintas diapazonas C4: E7, o „grupės“ - pavadintas diapazonas B4: B7. Formulė grąžina grupę, kuriai priklauso kiekvienas vardas.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su valdymo poslinkio įvedimu.
Paaiškinimas
Dirbant iš vidaus, šioje formulėje naudojami loginiai kriterijai yra šie:
--(names=G4)
kur pavadinimai yra pavadintas diapazonas C4: E7. Tai sukuria TIKRĄ / NETIKRĄ rezultatą kiekvienai duomenų vertei, o dvigubas neigiamas vertė TIESOS NETIESA reikšmes 1 ir 0, kad gautų tokį masyvą:
(0,0,0;1,0,0;0,0,0;0,0,0)
Šis masyvas yra 4 eilutės po 3 stulpelius, atitinkantys „vardų“ struktūrą.
Su šia išraiška sukuriamas antrasis masyvas:
TRANSPOSE(COLUMN(names)^0))
Funkcija COLUMN naudojama sukurti skaitinį masyvą su 3 stulpeliais ir 1 eilute, o TRANSPOSE paverčia šį masyvą į 1 stulpelį ir 3 eilutes. Pakėlus iki nulio galios, visi masyvo skaičiai tiesiog paverčiami į 1. Tada MMULT funkcija naudojama matricos dauginimui atlikti:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
ir gautas eina į funkciją MATCH kaip masyvas, o paieškos reikšmė yra 1:
MATCH(1,(0;1;0;0),0)
Funkcija MATCH grąžina pirmosios atitikties poziciją, kuri atitinka pirmos atitikimo eilutės, atitinkančios pateiktus kriterijus, eilutę. Tai įtraukiama į INDEX kaip eilutės numerį, o masyvas yra pavadintas diapazonas „grupės“:
=INDEX(groups,2)
Galiausiai, INDEX grąžina „Lokį“, grupei Adomas priklauso.
Pažodiniame yra kriterijai
Norėdami patikrinti, ar yra tikslios teksto vertės, o ne tiksli atitiktis, galite naudoti funkcijas ISNUMBER ir SEARCH kartu. Pavyzdžiui, norėdami suderinti langelius, kuriuose yra „obuolys“, galite naudoti:
=ISNUMBER(SEARCH("apple",data))
Ši formulė paaiškinta čia.
