Excel-kaava: Pura useita osumia erillisiin sarakkeisiin -

Yhteenveto

Voit purkaa useita osumia erillisiin soluihin erillisissä sarakkeissa käyttämällä taulukkoa, joka perustuu INDEX ja SMALL. Esitetyssä esimerkissä kaava kohdassa F5 on:

(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))

Tämä on matriisikaava ja se on syötettävä painamalla Control + Vaihto + Enter.

Kun olet kirjoittanut kaavan ensimmäiseen soluun, vedä sitä alaspäin ja täytä muut solut.

Selitys

Huomaa: tässä kaavassa käytetään kahta nimettyä aluetta: "nimet" viittaa ryhmään C5: C11 ja "ryhmät" viittaa kohtaan B5: B11. Nämä nimet on määritelty myös yllä olevassa kuvakaappauksessa.

Tämän kaavan ydin on seuraava: Käytämme SMALL-funktiota "n: tä ottelua" vastaavan rivinumeron luomiseen. Kun meillä on rivinumero, siirrämme sen yksinkertaisesti INDEX-funktiofunktioon, joka palauttaa kyseisen rivin arvon.

Temppu on, että SMALL työskentelee taulukon kanssa, jonka IF on dynaamisesti rakentanut tässä bitissä:

IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)

Tämä katkelma testaa nimettyjen alueiden "ryhmät" arvoa E5: ssä. Jos se löytyy, se palauttaa rivinumeron joukosta suhteellisia rivinumeroita, jotka on luotu seuraavasti:

ROW(names)-MIN(ROW(names))+1

Lopputulos on taulukko, joka sisältää numerot, joissa on osuma, ja FALSE, jos ei:

(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)

Tämä taulukko menee PIENIIN. PIENEN (n) k-arvo tulee laajenevalta alueelta:

COLUMNS($E$5:E5)

Kun kopioidaan tulostaulukon yli, alue laajenee, jolloin k (n.) Kasvaa. SMALL-funktio palauttaa jokaisen vastaavan rivinumeron, joka toimitetaan INDEX-funktiolle rivinumerona, taulukossa nimetty alue "nimet".

Käsittelyvirheet

Kun COLUMNS palauttaa arvon k: lle, jota ei ole, SMALL heittää #NUM-virheen. Tämä tapahtuu, kun kaikki ottelut on tapahtunut. Virheen estämiseksi kääritään kaava IFERROR-funktioon virheiden saamiseksi ja palautetaan tyhjä merkkijono ("").

Mielenkiintoisia artikkeleita...