Excel formula: Izdvoji sva podudaranja pomoćnim stupcem -

Sadržaj

Generička formula

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Sažetak

Jedan od načina za izdvajanje više podudaranja u Excelu je upotreba INDEX-a i MATCH-a s pomoćnim stupcem koji označava odgovarajuće podatke. Time se izbjegava složenost naprednije formule niza. U prikazanom primjeru formula u H6 je:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

gdje su ct (G3), podaci (B3: E52) i pomagači (E3: E52) imenovani rasponi.

Obrazloženje

Izazov s pretraživačkim formulama koje dohvaćaju više od jednog podudaranja jest upravljanje duplikatima (tj. Više podudaranja). Formule pretraživanja poput VLOOKUP i INDEX + MATCH mogu lako pronaći prvo podudaranje, ali mnogo je teže pretraživati "sva podudaranja" kada kriteriji pronađu više od jednog podudaranja.

Ova se formula bavi ovim izazovom pomoću pomoćnog stupca koji vraća numeričku vrijednost koja se može koristiti za lako izdvajanje više podudaranja. Formula u pomoćnom stupcu izgleda ovako:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Pomoćni stupac testira svaki redak u podacima kako bi utvrdio odgovara li odjel u stupcu C vrijednosti u I3, a zgrada u stupcu D vrijednosti u J3. Oba logička testa moraju vratiti TRUE da bi AND mogao vratiti TRUE.

Za svaki redak, rezultat funkcije AND dodaje se u "vrijednost iznad" u pomoćnom stupcu kako bi se generirao broj. Praktični učinak ove formule je povećavajući brojač koji se mijenja samo kada se pronađe (novo) podudaranje. Tada vrijednost ostaje ista dok se ne pronađe sljedeće podudaranje. To djeluje jer se rezultati TRUE / FALSE koji se vraćaju po AND prisiljavaju na vrijednosti 1/0 kao dio operacije zbrajanja. FALSE rezultati ne dodaju ništa, a TRUE rezultati dodaju 1.

Povratak u područje izdvajanja, formula pretraživanja za Ime u stupcu H izgleda ovako:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Radeći iznutra prema van, dio formule INDEX + MATCH traži naziv prvog pronađenog podudaranja, koristeći broj retka u stupcu G kao vrijednost podudaranja:

INDEX(data,MATCH($G6,helper,0),1)

INDEX prima sva 3 stupca podataka kao niz (imenovani raspon "podaci"), a MATCH je konfiguriran tako da odgovara broju retka unutar pomoćnog stupca (imenovani raspon "pomoćnik") u načinu točnog podudaranja (3. argument postavljen na nulu) .

Tu postaje očita pametnost formule. Pomoćni stupac očito sadrži duplikate, ali nije važno, jer će se MATCH podudarati samo s prvom vrijednošću. Prema dizajnu, svaka "prva vrijednost" odgovara ispravnom retku u tablici podataka.

Formule u stupcima I i J iste su kao i H, osim broja stupca, koji se u svakom slučaju povećava za jedan.

Izraz IF koji obavija formulu INDEX / MATCH izvodi jednostavnu funkciju - provjerava svaki broj retka u području izvlačenja kako bi utvrdio je li broj retka manji ili jednak vrijednosti u G3 (nazvanom rasponu "ct"), što je ukupan broj svih odgovarajućih zapisa. Ako je tako, pokreće se logika INDEX / MATCH. Ako nije, IF izbacuje prazan niz ("").

Formula u G3 (nazvana raspon "ct") jednostavna je:

=MAX(helper)

Budući da je maksimalna vrijednost u pomoćnom stupcu jednaka ukupnom broju podudaranja, funkcija MAX je sve što trebamo.

Napomena: područje za izdvajanje treba ručno konfigurirati da obrađuje onoliko podataka koliko je potrebno (tj. 5 redaka, 10 redaka, 20 redaka itd.). U ovom je primjeru ograničeno na 5 redaka samo kako bi radni list bio kompaktan.

Ovu tehniku naučio sam u knjizi Mikea Girvina Control + Shift + Enter.