En introduktion till Dynamic Ranges
VLOOKUP -funktionen används ofta för att hitta information som lagras i tabeller i Excel. Så till exempel om vi har en lista med människors namn och ålder:
Och då kan vi i en närliggande cell använda funktionen VLOOKUP för att bestämma Pauls ålder:
Hittills är detta en ganska standard. Men vad händer om vi behöver lägga till några fler namn på listan? Den uppenbara tanken skulle vara att ändra intervallet i VLOOKUP. Men i en riktigt komplex modell kan det finnas flera referenser till VLOOKUP. Det betyder att vi skulle behöva ändra varje referens - förutsatt att vi visste var de var.
Men Excel erbjuder ett alternativt sätt - kallat ett DYNAMISKT intervall. Detta är ett intervall som automatiskt utökar en uppdatering. Detta är perfekt om dina listor ständigt expanderar (t.ex. försäljningsdata från månad till månad).
För att skapa ett dynamiskt intervall måste vi ha ett intervallnamn - så vi kallar vårt AGE_DATA. Metoden för att ställa in dynamiska intervall skiljer sig mellan Excel 2007 och tidigare versioner av Excel:
I Excel 2007 klickar du på "Definiera namn" under formler:
I tidigare versioner av Excel klickar du på "Infoga" och sedan på Namn ".
I popup -rutan anger du namnet på vårt dynamiska område - som är "ÅLDERDATA":
I rutan märkt "refererar till" måste vi ange intervallet för våra data. Detta kommer att uppnås som används av en OFFSET -funktion. Detta har 5 argument:
= OFFSET (Referens, rader, Cols, höjd, bredd)
- Referensen är adressen till det Övre vänstra hörnet av vårt sortiment - i detta fall cell B5
- Raderna är antalet rader från överst till vänster som vi vill att intervallet ska vara - vilket blir 0 i det här fallet
- Cols är antalet rader från ÖVERST VÄNSTER som vi vill att intervallet ska vara - vilket kommer att vara 0 i det här fallet
- Räckviddens höjd - se nedan för detta
- Bredden på intervallet - detta är 2 har vi har TVÅ kolumner i vårt sortiment (personernas namn och deras ålder)
Nu måste höjden på intervallet variera beroende på antalet poster i vår tabell (som för närvarande är 7).
Naturligtvis vill vi ha ett sätt att räkna upp raderna i vår tabell som uppdateras automatiskt - så ett sätt att göra detta är att använda COUNTA -funktionen. Detta räknar bara upp antalet icke tomma celler i ett intervall. Eftersom våra namn finns i kolumn B är antalet poster i våra data COUNTA (B: B).
Observera att om du skulle placera detta i en cell skulle du få värdet 8 - eftersom det inkluderar rubrikens namn. Men att det är oväsentligt.
Så i rutan "Refererar till" lägger vi:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
Och klicka på OK -knappen. Vårt dynamiska omfång är nu skapat.
Återgå nu till VLOOKUP -formlerna och ersätt intervallet $ B: 4: $ C11 med namnet på vårt nya dynamiska område AGE_DATA så att vi har:
Hittills har ingenting förändrats. Men om vi lägger till några fler namn i vårt bord:
Och i cellen där vi hade Paul, ersätt det med ett nytt namn som Pedro (som inte fanns på originallistan):
Och vi ser att Excel automatiskt har återvänt Pedros ålder - även om vi inte har ändrat VLOOKUP -formlerna. I stället har omfattningen av det dynamiska intervallet ökat för att inkludera de extra namnen.
Dynamiska intervall är mycket användbara när vi har ökande datamängder - särskilt när VLOOKUP- och PIVOT -tabeller krävs.
