Hvordan bruke Excel OFFSET-funksjonen

Innholdsfortegnelse

Sammendrag

Excel OFFSET-funksjonen returnerer en referanse til et område konstruert med fem innganger: (1) et startpunkt, (2) en radforskyvning, (3) en kolonneforskyvning, (4) en høyde i rader, (5) en bredde i kolonner. OFFSET er praktisk i formler som krever et dynamisk område.

Hensikt

Lag en referanseforskyvning fra gitt startpunkt

Returverdi

En cellehenvisning.

Syntaks

= OFFSET (referanse, rader, cols, (høyde), (bredde))

Argumenter

  • referanse - Startpunktet, levert som en cellereferanse eller et område.
  • rader - Antall rader som skal forskyves under startreferansen.
  • cols - Antall kolonner som skal forskyves til høyre for startreferansen.
  • høyde - (valgfritt) Høyden i rader av den returnerte referansen.
  • bredde - (valgfritt) Bredden i kolonnene til den returnerte referansen.

Versjon

Excel 2003

Bruksanvisninger

Excel OFFSET-funksjonen returnerer et dynamisk område konstruert med fem innganger: (1) et startpunkt, (2) en radforskyvning, (3) en kolonneforskyvning, (4) en høyde i rader, (5) en bredde i kolonnene.

Utgangspunktet ( referanse- argumentet) kan være en celle eller et celleområde. De rader og cols argumenter er antallet celler som til "offset" fra startpunktet. Den høyde og bredde argumenter er valgfrie og bestemme størrelsen av området som blir opprettet. Når høyde og bredde er utelatt, er standardverdien referansehøyden og -bredden .

For eksempel, for å referere til C5 som starter ved A1, er referanse A1, rader er 4 og cols er 2:

=OFFSET(A1,4,2) // returns reference to C5

For å referere til C1: C5 fra A1, er referanse A1, rader er 0, cols er 2, høyde er 5 og bredde er 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Merk: bredde kan utelates, siden den er standard til 1.

Det er vanlig å se OFFSET innpakket i en annen funksjon som forventer et område. For eksempel til SUM C1: C5, begynner ved A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Hovedformålet med OFFSET er å la formler dynamisk tilpasse seg tilgjengelige data eller til brukerinngang. OFFSET-funksjonen kan brukes til å bygge et dynamisk navngitt område for diagrammer eller pivottabeller, for å sikre at kildedata alltid er oppdatert.

Merk: Excel-dokumentasjon sier at høyde og bredde ikke kan være negativ, men negative verdier ser ut til å ha fungert bra siden tidlig på 1990-tallet. OFFSET-funksjonen i Google Sheets tillater ikke en negativ verdi for høyde- eller breddeargumenter.

Eksempler

Eksemplene nedenfor viser hvordan OFFSET kan konfigureres til å returnere forskjellige typer områder. Disse skjermbildene ble tatt med Excel 365, så OFFSET returnerer en dynamisk matrise når resultatet er mer enn en celle. I eldre versjoner av Excel kan du bruke F9-tasten til å sjekke resultatene som returneres fra OFFSET.

Eksempel 1

På skjermen nedenfor bruker vi OFFSET for å returnere den tredje verdien (mars) i den andre kolonnen (vest). Formelen i H4 er:

=OFFSET(B3,3,2) // returns D6

Eksempel 2

I skjermbildet nedenfor bruker vi OFFSET for å returnere den siste verdien (juni) i den tredje kolonnen (Nord). Formelen i H4 er:

=OFFSET(B3,6,3) // returns E9

Eksempel 3

Nedenfor bruker vi OFFSET for å returnere alle verdier i den tredje kolonnen (Nord). Formelen i H4 er:

=OFFSET(B3,1,3,6) // returns E4:E9

Eksempel 4

Nedenfor bruker vi OFFSET for å returnere alle verdiene for mai (femte rad). Formelen i H4 er:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Eksempel 5

Nedenfor bruker vi OFFSET for å returnere verdien for april, mai og juni for regionen West. Formelen i H4 er:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Eksempel 6

Nedenfor bruker vi OFFSET for å returnere april, mai og juni-verdien for vest og nord. Formelen i H4 er:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Merknader

  • OFFSET returnerer bare en referanse, ingen celler flyttes.
  • Både rader og kolonner kan leveres som negative tall for å reversere sin normale forskyvningsretning - negative kolonner forskjøvet til venstre og negative rader forskjøvet ovenfor.
  • OFFSET er en "flyktig funksjon" - den vil beregne på nytt med hvert regnearksendring. Flyktige funksjoner kan gjøre at større og mer komplekse arbeidsbøker kjører sakte.
  • OFFSET viser #REF! feilverdi hvis forskyvningen er utenfor kanten av regnearket.
  • Når høyde eller bredde er utelatt, brukes høyden og bredden på referansen .
  • OFFSET kan brukes med en hvilken som helst annen funksjon som forventer å motta en referanse.
  • Excel-dokumentasjon sier at høyde og bredde ikke kan være negativ, men negative verdier fungerer.

Relaterte videoer

Hvordan lage et dynamisk navngitt område med OFFSET I denne videoen vil vi se på hvordan du lager et dynamisk navngitt område ved hjelp av OFFSET-funksjonen, den vanligste måten å lage dynamiske navneområder med en formel.

Interessante artikler...