Excel formula: Izvleči vsa ujemanja s pomožnim stolpcem -

Kazalo

Splošna formula

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

Povzetek

Eden od načinov za pridobivanje več zadetkov v Excelu je uporaba INDEX in MATCH s pomožnim stolpcem, ki označuje ujemajoče se podatke. S tem se izognemo zapletenosti naprednejše formule matrike. V prikazanem primeru je formula v H6:

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

kjer so ct (G3), podatki (B3: E52) in pomočnik (E3: E52) imenovani obsegi.

Pojasnilo

Izziv pri iskalnih formulah, ki pridobijo več kot eno ujemanje, je upravljanje dvojnikov (tj. Več ujemanj). Iskalne formule, kot sta VLOOKUP in INDEX + MATCH, lahko zlahka najdejo prvo ujemanje, vendar je veliko težje iskati "vsa ujemanja", če kriteriji najdejo več kot eno ujemanje.

Ta formula obravnava ta izziv z uporabo pomožnega stolpca, ki vrne številsko vrednost, s katero lahko enostavno izvlečete več zadetkov. Formula v pomožnem stolpcu je videti tako:

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

Pomožni stolpec preskusi vsako vrstico v podatkih, da ugotovi, ali se oddelek v stolpcu C ujema z vrednostjo v I3, stavba v stolpcu D pa z vrednostjo v J3. Oba logična testa morata vrniti TRUE, da IN in TRUE.

Za vsako vrstico se rezultat funkcije AND doda "vrednosti zgoraj" v pomožnem stolpcu, da ustvari štetje. Praktični učinek te formule je naraščajoči števec, ki se spremeni le, ko najdete (novo) ujemanje. Nato vrednost ostane enaka, dokler ne najdete naslednjega ujemanja. To deluje, ker so rezultati TRUE / FALSE, ki jih vrne AND, prisiljeni v vrednosti 1/0 kot del operacije seštevanja. FALSE rezultati ne dodajo nič, TRUE rezultati pa 1.

Nazaj v območju za pridobivanje je iskalna formula za Ime v stolpcu H videti tako:

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

Del formule INDEX + MATCH, ki deluje od znotraj navzven, poišče ime prvega najdenega ujemanja, pri čemer kot vrednost ujemanja uporabi številko vrstice v stolpcu G:

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

INDEX prejme vse tri stolpce podatkov kot matriko (imenovan obseg "podatki"), MATCH pa je konfiguriran tako, da se v načinu natančnega ujemanja ujema s številko vrstice znotraj pomožnega stolpca (imenovani obseg "pomočnik") (3. argument je nastavljen na nič) .

Tu postane očitna formula. Pomožni stolpec očitno vsebuje dvojnike, vendar ni pomembno, ker se bo MATCH ujemal samo s prvo vrednostjo. Vsaka "prva vrednost" po svoji zasnovi ustreza pravilni vrstici v podatkovni tabeli.

Formuli v stolpcih I in J sta enaki H, razen številke stolpca, ki se v vsakem primeru poveča za eno.

Stavek IF, ki zavije formulo INDEX / MATCH, izvaja preprosto funkcijo - preveri vsako številko vrstice v območju za ekstrakcijo, da ugotovi, ali je številka vrstice manjša ali enaka vrednosti v G3 (imenovano območje "ct"), kar je skupno število vseh ujemajočih se zapisov. V tem primeru se zažene logika INDEX / MATCH. Če ni, IF prikaže prazen niz ("").

Formula v G3 (imenovano območje "ct") je preprosta:

=MAX(helper)

Ker je največja vrednost v pomožnem stolpcu enaka skupnemu številu ujemanj, je vse, kar potrebujemo, funkcija MAX.

Opomba: območje za ekstrakcijo je treba ročno konfigurirati tako, da obdeluje toliko podatkov, kot je potrebno (tj. 5 vrstic, 10 vrstic, 20 vrstic itd.). V tem primeru je omejeno na 5 vrstic, da je delovni list kompakten.

Te tehnike sem se naučil v knjigi Mike Girvin Control + Shift + Enter.