Exceli valem: ekstraktige kõik vasted abistaja veeruga -

Lang L: none (table-of-contents)

Üldine valem

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

Kokkuvõte

Üks võimalus Excelis mitme vaste väljavõtmiseks on kasutada INDEXi ja MATCH-i abiveeruga, mis tähistab vastavaid andmeid. See väldib keerukama massiivi valemi keerukust. Näidatud näites on H6 valem:

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

kus ct (G3), andmed (B3: E52) ja abistaja (E3: E52) nimetatakse vahemikeks.

Selgitus

Väljakutse koos valemitega, mis toovad rohkem kui ühe vaste, on duplikaatide (st mitme vaste) haldamine. Otsinguvalemid nagu VLOOKUP ja INDEX + MATCH leiavad hõlpsasti esimese vaste, kuid "kõigi vastete" otsimine on palju raskem, kui kriteeriumid leiavad rohkem kui ühe vaste.

See valem tegeleb selle väljakutsega, kasutades abiveergu, mis tagastab arvulise väärtuse, mida saab kasutada mitme vaste hõlpsaks eraldamiseks. Abistaja veerus olev valem näeb välja selline:

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

Abistaja veerg testib andmete igat rida, et näha, kas veerus C olev osakond vastab väärtusele I3 ja hoone veerus D väärtusele J3. Mõlemad loogilised testid peavad tagastama TÕENE, et JA tagastaks TÕENE.

Iga rea jaoks lisatakse funktsiooni AND tulemus abistaja veerus olevale väärtusele "ülal", et genereerida loendus. Selle valemi praktiline mõju on kasvav loendur, mis muutub ainult siis, kui leitakse (uus) vaste. Siis jääb väärtus samaks, kuni leitakse järgmine vaste. See töötab, sest TÕENE / VÄÄR Tulemused tagastavad AND-ga sunnitud summaoperatsiooni osana väärtusele 1/0. FALSE tulemused ei lisa midagi ja TRUE tulemused lisavad 1.

Tagasi kaevandamisalale näeb veerus H oleva nime valem välja järgmine:

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

Seestpoolt töötades otsib valemi osa INDEX + MATCH esimese leitud vaste nime, kasutades vaste väärtusena veerus G olevat rea numbrit:

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

INDEX saab kõik kolm andmeveeru massiivina (nimega vahemik "andmed") ja MATCH on konfigureeritud nii, et see sobituks abistaja veerus oleva rea numbriga (nimega vahemik "abistaja") täpse vaste režiimis (3. argumendiks on null) .

Siin ilmneb valemi nutikus. Abistaja veerg sisaldab ilmselgelt duplikaate, kuid see pole oluline, sest MATCH sobib ainult esimese väärtusega. Kujunduse järgi vastab iga "esimene väärtus" andmetabeli õigele reale.

Veergudes I ja J olevad valemid on samad kui H, välja arvatud veeru number, mida suurendatakse igal juhul ühe võrra.

IF-lause, mis ümbritseb valemit INDEX / MATCH, täidab lihtsat funktsiooni - see kontrollib iga väljavõteala rea numbrit, et näha, kas rea number on väiksem või võrdne väärtusega G3 (nimega vahemik "ct"), mis on kõigi sobivate kirjete koguarv. Kui jah, käivitatakse INDEX / MATCH loogika. Kui ei, siis väljastab IF tühja stringi ("").

G3 valem (nimega vahemik "ct") on lihtne:

=MAX(helper)

Kuna abistaja veeru maksimaalne väärtus on sama kui kogu vastete arv, on vaja ainult funktsiooni MAX.

Märkus: ekstraheerimisala tuleb konfigureerida käsitsi nii palju andmeid vajama kui vaja (st 5 rida, 10 rida, 20 rida jne). Selles näites on see piiratud viie reaga, et tööleht oleks kompaktne.

Õppisin seda tehnikat Mike Girvini raamatust Control + Shift + Enter.