Excel-kaava: Pura kaikki ottelut auttajasarakkeella -

Sisällysluettelo

Yleinen kaava

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

Yhteenveto

Yksi tapa poimia useita vastaavuuksia Excelissä on käyttää hakemistoa ja MATCH-apusaraketta, joka merkitsee vastaavat tiedot. Tämä välttää edistyneemmän matriisikaavan monimutkaisuuden. Esitetyssä esimerkissä kaava H6: ssa on:

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

missä ct (G3), data (B3: E52) ja auttaja (E3: E52) on nimetty alueiksi.

Selitys

Haaste, jolla haetaan kaavoja, jotka hakevat useamman kuin yhden ottelun, on kaksoiskappaleiden (eli useiden osumien) hallinta. Hakukaavat, kuten VLOOKUP ja INDEX + MATCH, voivat helposti löytää ensimmäisen ottelun, mutta on paljon vaikeampaa etsiä "kaikkia otteluita", kun kriteerit löytävät useamman kuin yhden ottelun.

Tämä kaava käsittelee tätä haastetta käyttämällä apusaraketta, joka palauttaa numeerisen arvon, jota voidaan käyttää useiden vastaavuuksien purkamiseen. Apulaitesarakkeen kaava näyttää tältä:

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

Avustaja-sarake testaa jokaisen datan rivin selvittääkseen, vastaako sarakkeessa C oleva osasto I3: n arvoa ja sarakkeessa D oleva rakennus J3: n arvoa. Molempien loogisten testien on palautettava TOSI, jotta JA palauttaa TOSI.

Kutakin riviä varten AND-funktion tulos lisätään auttajasarakkeen "yllä olevaan arvoon", jotta saadaan laskenta. Tämän kaavan käytännön vaikutus on kasvava laskuri, joka muuttuu vain, kun (uusi) osuma löydetään. Sitten arvo pysyy samana, kunnes seuraava ottelu on löydetty. Tämä toimii, koska TOSI / EPÄTOSI-tulokset palauttavat AND: n avulla pakotetaan 1/0 -arvoihin osana summaoperaatiota. FALSE-tulokset eivät lisää mitään, ja TOSI-tulokset lisäävät 1.

Takaisin poiminta-alueelle Nimi-hakukaava sarakkeessa H näyttää tältä:

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

Työskentelemällä sisältäpäin kaavan INDEX + MATCH osa etsii ensimmäisen löydetyn vastaavuuden nimen käyttämällä sarakkeessa G olevaa rivinumeroa vastaavuusarvona:

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

INDEX vastaanottaa kaikki 3 datasaraketta matriisina (nimetty alue "data"), ja MATCH on määritetty vastaamaan auttajasarakkeen rivinumeroa (nimetty alue "auttaja") täsmällisen vastaavuustilan ollessa (3. argumentti asetettu nollaksi) .

Tässä käy ilmi kaavan fiksuus. Apusarakkeessa on ilmeisesti kaksoiskappaleita, mutta sillä ei ole merkitystä, koska MATCH vastaa vain ensimmäistä arvoa. Suunnittelun mukaan kukin "ensimmäinen arvo" vastaa tietotaulukon oikeaa riviä.

Sarakkeiden I ja J kaavat ovat samat kuin H, lukuun ottamatta sarakkeiden lukumäärää, jota lisätään kulloinkin yhdellä.

IF-käsky, joka kääri INDEX / MATCH-kaavan, suorittaa yksinkertaisen funktion - se tarkistaa jokaisen rivinumeron poiminta-alueella nähdäksesi, onko rivinumero pienempi tai yhtä suuri kuin G3: n arvo (nimetty alue "ct"), joka on kaikkien vastaavien tietueiden kokonaismäärä. Jos näin on, INDEX / MATCH-logiikka suoritetaan. Jos ei, IF antaa tyhjän merkkijonon ("").

G3: n kaava (nimetty alue "ct") on yksinkertainen:

=MAX(helper)

Koska auttaja-sarakkeen enimmäisarvo on sama kuin osumien kokonaismäärä, tarvitsemme MAX-toiminnon.

Huomaa: poiminta-alue on määritettävä manuaalisesti käsittelemään niin paljon tietoja kuin tarvitaan (ts. 5 riviä, 10 riviä, 20 riviä jne.). Tässä esimerkissä se on rajoitettu viiteen riviin, jotta laskentataulukko pysyy tiiviisti.

Olen oppinut tämän tekniikan Mike Girvinin kirjassa Control + Vaihto + Enter.