En introduksjon til Dynamic Ranges
VLOOKUP -funksjonen brukes ofte til å finne informasjon som er lagret i tabeller i Excel. Så for eksempel hvis vi har en liste over folks navn og alder:
Og så kan vi i en celle i nærheten bruke funksjonen VLOOKUP for å bestemme Pauls alder:
Så langt er dette en ganske standard. Men hva skjer hvis vi trenger å legge til noen flere navn på listen? Den åpenbare tanken ville være å endre rekkevidden i VLOOKUP. I en virkelig kompleks modell kan det imidlertid være flere referanser til VLOOKUP. Dette betyr at vi måtte endre hver referanse - forutsatt at vi visste hvor de var.
Imidlertid gir Excel en alternativ måte - kalt et DYNAMISK område. Dette er et område som automatisk utvider oppdateringer. Dette er perfekt hvis listene dine utvides for alltid (f.eks. Salgsdata fra måned til måned).
For å sette opp et dynamisk område må vi ha et områdenavn - så vi kaller vårt AGE_DATA. Tilnærmingen for å sette opp dynamiske områder varierer mellom Excel 2007 og tidligere versjoner av Excel:
I Excel 2007 klikker du på "Definer navn" under formler:
I tidligere versjoner av Excel klikker du på "Sett inn" og deretter navn ".
I popup -boksen skriver du inn navnet på vårt dynamiske område - som er "ALDERDATA":
I boksen merket "Henviser til" må vi angi omfanget av dataene våre. Dette er en oppnåelse som brukes av en OFFSET -funksjon. Dette har 5 argumenter:
= FORSKYTT (referanse, rader, kolonner, høyde, bredde)
- Referansen er adressen til TOP LEFT -hjørnet i vårt sortiment - i dette tilfellet celle B5
- Radene er antall rader fra TOPP VENSTRE som vi vil at området skal være - som vil være 0 i dette tilfellet
- Cols er antallet rader fra TOPP VENSTRE som vi vil at området skal være - som vil være 0 i dette tilfellet
- Høyden på området - se nedenfor for dette
- Bredden på området - dette er 2 har vi har TO kolonner i vårt område (personens navn og alder)
Nå må høyden på området variere avhengig av antall oppføringer i tabellen vår (som for øyeblikket er 7).
Selvfølgelig vil vi ha en måte å telle opp radene i tabellen vår som oppdateres automatisk - så en måte å gjøre dette på er å bruke COUNTA -funksjonen. Dette teller bare opp antallet ikke -tomme celler i et område. Siden navnene våre er i kolonne B, er antall oppføringer i dataene våre COUNTA (B: B).
Vær oppmerksom på at hvis du skulle sette dette i en celle, ville du få verdien 8 - ettersom den inkluderer overskriftens navn. Imidlertid at det er uvesentlig.
Så i boksen "Henviser til" legger vi:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
Og klikk på OK -knappen. Vårt dynamiske område er nå opprettet.
Gå nå tilbake til VLOOKUP -formlene og erstatt området $ B: 4: $ C11 med navnet på vårt nye dynamiske område AGE_DATA, så vi har:
Så langt har ingenting endret seg. Men hvis vi legger til noen flere navn i bordet vårt:
Og i cellen der vi hadde Paul, erstatt den med et nytt navn som Pedro (som ikke var på den opprinnelige listen):
Og vi ser at Excel automatisk har returnert Pedros alder - selv om vi ikke har endret VLOOKUP -formlene. I stedet har omfanget av det dynamiske området økt til å inkludere ekstra navn.
Dynamiske områder er veldig nyttige når vi har økende datamengder - spesielt når det er nødvendig med VLOOKUP- og PIVOT -tabeller.
