För att hämta det sista värdet i en dynamisk lista använder vi alternativet Datavalidering tillsammans med OFFSET- och COUNTA -funktionerna i Microsoft Excel 2010/2013.
RIKT: Det returnerar antalet celler som innehåller värden.
Syntax för funktionen "COUNTA": = COUNTA (värde1, värde2, värde3 ….)
Exempel: I intervall A1: A5 innehåller cellerna A2, A3 och A5 värdena och cellerna A1 och A4 är tomma. Välj cell A6 och skriv formeln-
= COUNTA (A1: A5) funktionen återkommer 3
OFFSET: Det returnerar en referens till ett intervall som förskjutits ett antal rader och kolumner från ett annat område eller en cell.
Syntax för OFFSET -funktion: = OFFSET (referens, rader, kolumner, höjd, bredd)
Referens:- Detta är cellen eller intervallet från vilket du vill kompensera.
Rader och kolumner att flytta: - Hur många rader du vill flytta utgångspunkten och båda dessa kan vara positiva, negativa eller noll.
Höjd och bredd: - Detta är storleken på intervallet du vill returnera. Detta är ett valfritt fält.
Låt oss ta ett exempel för att förstå funktionen Offset i Excel.
Vi har data i intervallet A1: D10. Kolumn A innehåller produktkod, kolumn B innehåller kvantitet, kolumn C innehåller per produktkostnad och kolumn D innehåller total kostnad. Vi måste returnera värdet av cell C5 i cell E2.
Följ stegen nedan.
- Markera cellen E2 och skriv formeln.
- = OFFSET (A1,4,2,1,1)och tryck på Enter på tangentbordet.
- Funktionen returnerar värdet för cell C5.
I det här exemplet måste vi få värdet från cell C5 till E2. Vår referenscell är den första cellen i intervallet som är A1 och C5 är 4 rader nedan och 2 kolumner till höger från A1. Därför är formeln = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (eftersom 1,1 är valfritt).
Låt oss nu ta ett exempel för att hämta det sista värdet i en dynamisk lista.
Vi har landnamn i en rad. Om vi nu lägger till fler länder i den här listan bör den automatiskt vara tillgänglig i rullgardinsmenyn.
För att förbereda en dynamisk lista måste vi skapa en formel som hämtar det sista värdet i kolumnen och uppdateras automatiskt när ett nytt nummer läggs till.
Följ nedanstående steg:-
- Markera cellen B2.
- Gå till fliken Data, välj Datavalidering i gruppen Dataverktyg.
- Dialogrutan "Datavalidering" visas. På fliken "Inställningar" väljer du "Anpassad" från rullgardinsmenyn Tillåt.
- Formelrutan aktiveras.
- Skriv formeln i den här rutan.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klicka på OK.
- I detta skede är den senaste uppdaterade cellen A11.
- För att kontrollera om datavalideringen fungerar korrekt, lägg till ett stadsnamn i cell A12.
Så snart du lägger till en post i A12 läggs den till i rullgardinsmenyn.
Så här kan du skapa en dynamisk lista och automatiskt fylla i nya poster i Microsoft Excel 2010 och 2013.
