Excel OFFSET -funksjon (eksempel + video)
Når skal du bruke Excel OFFSET -funksjon
Excel OFFSET -funksjonen kan brukes når du vil få en referanse som kompenserer angitt antall rader og kolonner fra utgangspunktet.
Hva den returnerer
Den returnerer referansen som OFFSET -funksjonen peker på.
Syntaks
= OFFSET (referanse, rader, cols, [høyde], [bredde])
Inngangsargumenter
- referanse - Referansen du vil kompensere fra. Dette kan være en cellereferanse eller et område av tilstøtende celler.
- rader - antall rader som skal kompenseres. Hvis du bruker et positivt tall, forskyves det til radene nedenfor, og hvis et negativt tall brukes, forskyves det til radene ovenfor.
- cols - antall kolonner som skal kompenseres. Hvis du bruker et positivt tall, forskyves det til kolonnene til høyre, og hvis et negativt tall brukes, forskyves det til kolonnene til venstre.
- [høyde] - dette er et tall som representerer antall rader i den returnerte referansen.
- [bredde] - dette er et tall som representerer antall kolonner i den returnerte referansen.
Forstå det grunnleggende i Excel OFFSET -funksjon
Excel OFFSET -funksjonen er muligens en av de mest forvirrende funksjonene i Excel.
La oss ta et enkelt eksempel på et sjakkspill. Det er en brikke i Chess kalt en Rook (også kjent som et tårn, markis eller rektor).
[Bilde med tillatelse: Wonderful Wikipedia]
Nå, som alle de andre sjakkbrikkene, har en Rook en fast bane som den kan bevege seg rundt på brettet. Det kan gå rett (til høyre/venstre eller opp/ned på brettet), men det kan ikke gå diagonalt.
Anta for eksempel at vi har et sjakkbrett i Excel (som vist nedenfor), i en gitt boks (D5 i dette tilfellet) kan Rook bare gå i de fire uthevede retningene.
Hvis jeg ber deg om å ta Rook fra den nåværende posisjonen til den som er markert med gult, hva vil du fortelle tårnet?
Du vil be den om å ta to trinn nedover og to trinn til høyre … ikke sant?
Og det er en nær nærhet til hvordan OFFSET -funksjonen fungerer.
La oss nå se hva dette betyr i Excel. Jeg vil starte med cellen D5 (som er der Rook er) og deretter gå to rader ned og to kolonner til høyre og hente verdien fra cellen der.
Formelen vil være:
= OFFSET (fra hvor du skal begynne, hvor mange rader ned, hvor mange kolonner til høyre)
Som du kan se, er formelen i eksemplet ovenfor i celle J1 = OFFSET (D5,2,2).
Den startet ved D5, og gikk deretter to rader ned og to kolonner til høyre og nådde cellen F7. Den returnerte deretter verdien i celle F7.
I eksemplet ovenfor så vi på OFFSET -funksjonen med 3 argumenter. Men det er to valgfrie argumenter som kan brukes.
La oss se på et enkelt eksempel her:
Anta at du vil bruke referansen til celle A1 (i gult), og du vil referere til hele området markert med blått (C2: E4) i en formel.
Hvordan ville du gjort det med et tastatur? Du vil først gå til celle C2, og deretter velge alle cellene i C2: E4.
La oss nå se hvordan du gjør dette ved å bruke OFFSET -formelen:
= OFFSET (A1,1,2,3,3)
Hvis du bruker denne formelen i en celle, vil den returnere en #VERDI! feil, men hvis du kommer inn i redigeringsmodus og velger formelen og trykker på F9, ser du at den returnerer alle verdiene som er markert med blått.
La oss nå se hvordan denne formelen fungerer:
= OFFSET (A1,1,2,3,3)
- Det første argumentet er cellen der den skal starte.
- Det andre argumentet er 1, som ber Excel om å returnere en referanse som har blitt forskjøvet med 1 rad.
- Det tredje argumentet er 2, som ber Excel om å returnere en referanse som har blitt forskjøvet med 2 kolonner.
- Det fjerde argumentet er 3. Dette angir at referansen skal dekke 3 rader. Dette kalles høyde -argumentet.
- Det femte argumentet er 3. Dette angir at referansen skal dekke 3 kolonner. Dette kalles bredde -argumentet.
Nå som du har referansen til en rekke celler (C2: E4), kan du bruke den i en annen funksjon (for eksempel SUMMER, TELL, MAKS, GJENNOMSNITT).
Forhåpentligvis har du en god grunnleggende forståelse for bruk av Excel OFFSET -funksjonen. La oss nå se på noen praktiske eksempler på bruk av OFFSET -funksjonen.
Excel OFFSET -funksjon - eksempler
Her er to eksempler på bruk av Excel OFFSET -funksjon.
Eksempel 1 - Finne den siste fylte cellen i kolonnen
Anta at du har noen data i en kolonne som vist nedenfor:
For å finne den siste cellen i kolonnen, bruk følgende formel:
= OFFSET (A1, COUNT (A: A) -1,0)
Denne formelen forutsetter at det ikke er noen verdier bortsett fra de som vises, og at disse cellene ikke har en tom celle i den. Det fungerer ved å telle det totale antallet celler som er fylt, og forskyver cellen A1 tilsvarende.
For eksempel, i dette tilfellet, er det 8 verdier, så TELLING (A: A) returnerer 8. Vi forskyver cellen A1 med 7 for å få den siste verdien.
Eksempel 2 - Opprette en dynamisk nedtrekksmeny
Du kan utvide konseptutstillingene i eksempel 1 for å lage dynamiske navngitte områder. Disse kan være nyttige hvis du bruker de navngitte områdene i formler eller for å lage rullegardinmenyer, og du sannsynligvis vil legge til/slette data fra referansen som lager det navngitte området.
Her er et eksempel:
Vær oppmerksom på at nedtrekksmenyen justeres automatisk når året legges til eller fjernes.
Dette skjer ettersom formelen som brukes til å lage rullegardinmenyen, er dynamisk og identifiserer tillegg eller sletting og justerer området deretter.
Slik gjør du dette:
- Velg cellen der du vil sette inn rullegardinmenyen.
- Gå til Data -> Dataverktøy -> Datavalidering.
- I dialogboksen Datavalidering velger du Liste i kategorien Innstillinger fra rullegardinmenyen.
- Skriv inn følgende formel i kilden: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Klikk OK.
La oss se hvordan denne formelen fungerer:
- De tre første argumentene i OFFSET -funksjonen er A1, 0 og 0. Dette betyr i hovedsak at den ville returnere den samme referansecellen (som er A1).
- Det fjerde argumentet er for høyde, og her teller funksjonen det totale antallet elementer i listen. Det forutsetter at det ikke er noen tomrom på listen.
- Det femte argumentet er 1, som indikerer at kolonnebredden skal være en.
Ytterligere merknader:
- OFFSET er en flyktig funksjon (bruk med forsiktighet).
- Den beregner på nytt hver gang Excel -arbeidsboken er åpen eller når en beregning utløses i regnearket. Dette kan øke behandlingstiden og redusere arbeidsboken din.
- Hvis verdien for høyde eller bredde er utelatt, blir det tatt som referansen.
- Hvis rader og cols er negative tall, så blir offsetretningen reversert.
Excel OFFSET Funksjonsalternativer
På grunn av noen av begrensningene i Excel OFFSET -funksjonen, vil du mange vurdere alternativer til den:
- INDEX -funksjon: INDEX -funksjonen kan også brukes til å returnere en cellereferanse. Klikk her for å se et eksempel på hvordan du oppretter et dynamisk navngitt område ved hjelp av INDEX -funksjonen.
- Excel -tabell: Hvis du bruker strukturerte referanser i Excel -tabellen, trenger du ikke bekymre deg for at nye data blir lagt til og behovet for å justere formlene.
Excel OFFSET -funksjon - Videoopplæring
- Excel VLOOKUP -funksjon.
- Excel HLOOKUP -funksjon.
- Excel INDEX -funksjon.
- Excel INDIRECT -funksjon.
- Excel MATCH -funksjon.
