Tutki tätä lukua:
Oletetaan, että haluat tuottaa tästä raportin ikään kuin suodattaisit aluetta. Eli jos suodatat pohjoiseen, näet:
Mutta entä jos haluat kaavan mukaisen version samasta asiasta?
Tässä on tulos, jota etsit sarakkeista I: K:
On selvää, että se on sama raportti, mutta tässä ei ole suodatettuja kohteita. Jos haluat uuden raportin idästä, olisi mukava muuttaa G1: n arvo vain itään:
Näin se tehdään. Ensinnäkin, sitä ei tehdä VLOOKUP: n avulla. Joten valehtelin tämän tekniikan otsikosta!
Saraketta F ei näytetty aiemmin, ja se voidaan piilottaa (tai siirtää muualle, jotta se ei häiritse raporttia).
Sarakkeessa F näkyy rivinumerot siitä, mistä G1 löytyy sarakkeesta A; eli mitkä rivit sisältävät arvon "pohjoinen"? Tämä tekniikka liittyy käyttämällä solun yläpuolella, joten se on aloitettava vähintään rivillä 2. vastaa arvoa ”North” vastaan sarakkeessa A, mutta sen sijaan, että koko sarakkeen, käytä OFFSET toimintoa: OFFSET($A$1,F1,0,1000,1).
Koska F1 on 0, tämä OFFSET(A1,0,0,1000,1)on A1: A1000. (1000 on mielivaltainen, mutta riittävän suuri työn tekemiseen - voit tehdä siitä minkä tahansa muun numeron).
F2: n arvo 2 on ensimmäinen pohjoinen. Haluat myös lisätä takaisin F1: n arvon lopussa, mutta tämä on toistaiseksi nolla.
“Taika” herää eloon solussa F3. Tiedät jo, että ensimmäinen pohjoinen löytyy riviltä 2. Haluat siis alkaa etsiä kahta riviä A1: n alapuolella. Voit tehdä sen määrittämällä 2 OFFSET-funktion toiseksi argumentiksi.
Kaava F3 automaattisesti osoittamaan 2 joka laskettiin solun F2: Kun kopioit kaavan alas, näet =OFFSET($A$1,F2,0,1000,1)mikä on OFFSET($A$1,2,0,1000,1)mikä on A3: A1000. Joten sovitat pohjoisen tätä uutta aluetta vastaan ja se löytää pohjoisen tämän uuden alueen kolmannesta solusta, joten MATCH antaa 3.
Lisäämällä takaisin arvo yllä olevasta solusta, F2, näet 3 plus 2 tai 5, joka on rivi, joka sisältää toisen pohjoisen.
Tämä kaava on täytetty riittävän syvälle kaikkien arvojen saamiseksi.
Se antaa sinulle rivinumerot, joista kaikki pohjoiset tietueet löytyvät.
Kuinka käännät nämä rivinumerot tuloksiin sarakkeissa I - K? Kaikki tehdään yhdellä kaavalla. Anna tämän kaavan I2: =IFERROR(INDEX(A:A,$F2),””). Kopioi oikealle ja sitten alas.
Miksi käyttää IFERRORia? Missä virhe on? Huomaa solu F6 - se sisältää # N / A (minkä vuoksi haluat piilottaa sarakkeen F), koska pohjoisia ei enää ole rivin 15 jälkeen. Jos sarake F on virhe, palauta tyhjä kohta. Muussa tapauksessa poimi arvo sarakkeesta A (ja kun täytät oikealle, B & C).
$ F2 on ehdoton viittaus sarakkeeseen F, joten täyttöoikeus viittaa edelleen sarakkeeseen F.
Tämä vierasartikkeli on peräisin Excel MVP Bob Umlasilta. Se on yksi hänen suosikkitekniikoistaan hänen kirjassaan, Excel Outside the Box.
Excel laatikon ulkopuolella »
