Undersøk denne figuren:
Anta at du vil lage en rapport fra dette som om du har filtrert på regionen. Det vil si at hvis du filtrerer på Nord, vil du se:
Men hva om du ønsket en formelbasert versjon av det samme?
Her er resultatet du leter etter i kolonnene I: K:
Det er klart at det er den samme rapporten, men det er ingen filtrerte gjenstander her. Hvis du ønsket en ny rapport om øst, ville det være fint å bare endre verdien i G1 til øst:
Slik gjør du det. Først og fremst er det ikke gjort med VLOOKUP. Så jeg løy om tittelen på denne teknikken!
Kolonne F ble ikke vist før, og den kan skjules (eller flyttes et annet sted slik at den ikke forstyrrer rapporten).
Det som vises i kolonne F er radnumrene der G1 finnes i kolonne A; det vil si hvilke rader som inneholder verdien "Nord"? Denne teknikken innebærer å bruke cellen over, så den må begynne i minst rad 2. Den samsvarer med verdien "Nord" mot kolonne A, men bruk en OFFSET-funksjon i stedet for hele kolonnen : OFFSET($A$1,F1,0,1000,1).
Siden F1 er 0, er OFFSET(A1,0,0,1000,1)dette A1: A1000. (1000 er vilkårlig, men stor nok til å gjøre jobben - du kan gjøre det til et hvilket som helst annet nummer).
Verdien 2 i F2 er der den første "Nord" er. Du vil også legge til verdien av F1 på slutten, men dette er så langt null.
"Magien" blir levende i celle F3. Du vet allerede at det første nord er funnet i rad 2. Så du vil begynne å søke i to rader under A1. Du kan gjøre det ved å spesifisere 2 som det andre argumentet for OFFSET-funksjonen.
Formelen i F3 vil automatisk peke til 2 som ble beregnet i celle F2: Når du kopierer formelen ned, vil du se =OFFSET($A$1,F2,0,1000,1)hvilken OFFSET($A$1,2,0,1000,1)som er A3: A1000. Så du matcher Nord mot dette nye området, og det finner Nord i den tredje cellen i dette nye området, så MATCH gir 3.
Ved å legge tilbake verdien fra cellen over, F2, vil du se 3 pluss 2, eller 5, som er raden som inneholder det andre nord.
Denne formelen er fylt ned langt nok til å få alle verdiene.
Det vil gi deg radnumrene der alle Nord-postene er funnet.
Hvordan oversetter du disse radnumrene til resultatene i kolonnene I til K? Det hele gjøres med en enkelt formel. Skriv inn denne formelen i I2: =IFERROR(INDEX(A:A,$F2),””). Kopier til høyre og kopier deretter ned.
Hvorfor bruke IFERROR? Hvor er feilen? Legg merke til celle F6 - den inneholder # N / A (det er grunnen til at du ønsker å skjule kolonne F) fordi det ikke er flere nordre etter rad 15. Så hvis kolonne F er en feil, returner et tomt. Ellers henter du verdien fra kolonne A (og når den er fylt til høyre, B & C).
$ F2 er en absolutt referanse til kolonne F, slik at utfyllingsretten fortsatt refererer til kolonne F.
Denne gjesteartikkelen er fra Excel MVP Bob Umlas. Det er en av hans favorittteknikker fra boka hans, Excel utenfor boksen.
Excel utenfor boksen »
