For å hente den siste verdien i en dynamisk liste, bruker vi alternativet Datavalidering sammen med OFFSET og COUNTA -funksjonene i Microsoft Excel 2010/2013.
COUNTA: Den returnerer tellingen av antall celler som inneholder verdier.
Syntaks for “COUNTA” -funksjon: = COUNTA (verdi1, verdi2, verdi3….)
Eksempel: I område A1: A5, cellene A2, A3 og A5 inneholder verdiene, og cellene A1 og A4 er tomme. Velg cellen A6 og skriv formelen-
= COUNTA (A1: A5) funksjonen kommer tilbake 3
OFFSET: Den returnerer en referanse til et område som er forskjøvet et antall rader og kolonner fra et annet område eller en celle.
Syntaks for OFFSET -funksjon: = OFFSET (referanse, rader, cols, høyde, bredde)
Henvisning:- Dette er cellen eller området du vil kompensere fra.
Rader og kolonner som skal flyttes: - Hvor mange rader du vil flytte utgangspunktet og begge disse kan være positive, negative eller null.
Høyde og bredde: - Dette er størrelsen på området du vil returnere. Dette er et valgfritt felt.
La oss ta et eksempel for å forstå Offset -funksjonen i Excel.
Vi har data i området A1: D10. Kolonne A inneholder produktkode, kolonne B inneholder mengde, kolonne C inneholder per produktkostnad og kolonne D inneholder total kostnad. Vi må returnere verdien av celle C5 i celle E2.
Følg trinnene nedenfor.
- Velg celle E2 og skriv formelen.
- = OFFSET (A1,4,2,1,1)og trykk Enter på tastaturet.
- Funksjonen vil returnere verdien av celle C5.
I dette eksemplet må vi hente verdien fra celle C5 til E2. Referansecellen vår er den første cellen i området som er A1 og C5 er 4 rader under og 2 kolonner til høyre fra A1. Derfor er formelen = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (siden 1,1 er valgfritt).
La oss nå ta et eksempel for å hente den siste verdien i en dynamisk liste.
Vi har landnavn i et område. Nå, hvis vi legger til flere land til denne listen, bør den være tilgjengelig i rullegardinlisten automatisk.
For å utarbeide en dynamisk liste må vi lage en formel som vil hente den siste verdien i kolonnen og automatisk oppdateres når et nytt tall legges til.
Følg trinnene nedenfor:-
- Velg cellen B2.
- Gå til kategorien Data, velg Datavalidering fra gruppen Dataverktøy.
- Dialogboksen "Datavalidering" vises. I kategorien "Innstillinger" velger du "Egendefinert" fra rullegardinlisten Tillat.
- Formelboksen aktiveres.
- Skriv formelen i denne boksen.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klikk på OK.
- På dette stadiet er den siste oppdaterte cellen A11.
- For å sjekke om datavalideringen fungerer som den skal, legg til et bynavn i celle A12.
Så snart du legger til en oppføring i A12, vil den bli lagt til i rullegardinlisten.
Slik kan du opprette en dynamisk liste og automatisk fylle ut nye oppføringer i Microsoft Excel 2010 og 2013.
