For at hente den sidste værdi i en dynamisk liste, vil vi bruge datavalideringsindstillingen sammen med OFFSET og COUNTA -funktionerne i Microsoft Excel 2010/2013.
COUNTA: Det returnerer antallet af celler, der indeholder værdier.
Syntaks for funktionen “COUNTA”: = COUNTA (værdi1, værdi2, værdi3….)
Eksempel: I område A1: A5, cellerne A2, A3 og A5 indeholder værdierne, og cellerne A1 og A4 er tomme. Vælg celle A6, og skriv formlen-
= COUNTA (A1: A5) funktionen vender tilbage 3
OFFSET: Det returnerer en reference til et område, der er forskudt et antal rækker og kolonner fra et andet område eller en celle.
Syntaks for OFFSET funktion: = OFFSET (reference, rækker, kolber, højde, bredde)
Reference:- Dette er den celle eller det område, du vil kompensere fra.
Rækker og kolonner, der skal flyttes: - Hvor mange rækker du vil flytte udgangspunktet og begge disse kan være positive, negative eller nul.
Højde og bredde: - Dette er størrelsen på det område, du vil returnere. Dette er et valgfrit felt.
Lad os tage et eksempel for at forstå offset -funktionen i Excel.
Vi har data i området A1: D10. Kolonne A indeholder produktkode, kolonne B indeholder mængde, kolonne C indeholder pr. Produktomkostning og kolonne D indeholder samlede omkostninger. Vi skal returnere værdien af celle C5 i celle E2.
Følg nedenstående trin.
- Vælg celle E2, og skriv formlen.
- = OFFSET (A1,4,2,1,1)og tryk på Enter på tastaturet.
- Funktionen returnerer værdien af celle C5.
I dette eksempel skal vi hente værdien fra celle C5 til E2. Vores referencecelle er den første celle i området, som er A1 og C5 er 4 rækker nedenfor og 2 kolonner til højre fra A1. Derfor er formlen = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (da 1,1 er valgfri).
Lad os nu tage et eksempel for at hente den sidste værdi i en dynamisk liste.
Vi har lande navne i en række. Hvis vi nu tilføjer flere lande til denne liste, bør den automatisk være tilgængelig på rullelisten.
For at udarbejde en dynamisk liste skal vi oprette en formel, der henter den sidste værdi i kolonnen og automatisk opdateres, når der tilføjes et nyt nummer.
Følg nedenstående trin:-
- Vælg cellen B2.
- Gå til fanen Data, vælg Datavalidering fra gruppen Dataværktøjer.
- Dialogboksen "Datavalidering" vises. Vælg "Brugerdefineret" på fanen "Indstillinger" på rullelisten Tillad.
- Formelboksen aktiveres.
- Skriv formlen i denne boks.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klik på OK.
- På dette stadium er den sidste opdaterede celle A11.
- For at kontrollere, om datavalideringen fungerer korrekt, skal du tilføje et bynavn i celle A12.
Så snart du tilføjer en post i A12, tilføjes den til rullelisten.
Sådan kan du oprette en dynamisk liste og automatisk udfylde nye poster i Microsoft Excel 2010 og 2013.
