Excel OFFSET-formel (innholdsfortegnelse)

  • Hva er OFFSET-formel i Excel?
  • Hvordan bruke OFFSET-formel i Excel?

Hva er OFFSET-formel i Excel?

OFFSET Formel i Excel gir en celle eller et område med dynamiske rektangulære celler som en utgave som er en delmengde av det totale gitte området med et spesifisert antall rader, kolonner, høyde og bredde.

Syntaks for OFFSET-formel i Excel

Forklaring av OFFSET-funksjon i Excel:

  • referanse: Utgangspunktet for OFFSET eller vi kan si et område med celler som kan betraktes som basen for OFFSET.
  • rader : Antall rader til OFFSET under eller over basisreferansen.
  • cols: Antall kolonner til OFFSET til høyre eller venstre for basereferansen.
  • høyde: Antall rader i returnert referanse.
  • bredde: Antall kolonner i returnert referanse.

Høyden og bredden er de valgfrie parametrene for denne funksjonen og kan brukes til å bestemme størrelsen på referansen som er opprettet.

Hvordan bruke OFFSET-formel i Excel?

Vi vil nå lære å skrive forskyvningsformel for å få oppslagverdien. La oss forstå forskyvningsformelen med noen eksempler i Excel.

Du kan laste ned denne OFFSET Formula Excel Template her - OFFSET Formula Excel Template

Excel OFFSET-formel - eksempel # 1

Anta at vi har data som gitt nedenfor.

  • Skriv inn følgende formel i celle E2:

= OFFSET (A1, 11, 1)

  • Når du er ferdig med formelen, trykker du på Enter og ser utdataene.

Hva denne formelen gjorde er at den har tatt referanse fra celle A1 (år) og flyttet ned med 11 rader etter A1 (dvs. rad nr. 12) og deretter fått verdien som er koordinat for den 11. raden etter A1 og første kolonne ( kolonne A = 0 kolonne B = 1 i dette tilfellet. Som vi vet, kan vi gå både høyre og venstre). Som er $ 141, 27 millioner.

Excel OFFSET-formel - eksempel # 2

Nå vil vi se et annet eksempel på en referansecelle for et bestemt år. Sett følgende formel i celle E3:

= OFFSET (A1, 11, 0)

  • Når du er ferdig med formelen, trykker du på Enter og ser utdataene.

Hvis du har sett formelen, er den den samme som forrige, den eneste endringen er i kolonnehenvisning. Kolonnehenvisningen 0 (Null) betyr at verdien blir fanget fra kolonne A for 11. rad etter A1 (som er 2010).

Excel OFFSET-formel - eksempel # 3

I dette eksemplet vil vi beregne summen av de siste N-radene ved å bruke Excel OFFSET-funksjonen. Den generelle formelen for å oppsummere de siste N-tallene er:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

For å oppsummere de siste N-observasjonene fra dataene dine, kan du bruke SUM-funksjonen sammen med OFFSET og COUNT-funksjonen sammen. I dette eksemplet vil vi oppsummere de 5 siste datapunktene. Vær oppmerksom på at når vi sier N, er det en generalisering av hvilken som helst heltallverdi. Så du kan beregne for siste 3, siste 4, siste 5 osv., Avhengig av datasettet ditt og kravet ditt.

La oss gjøre det for datasettet vårt:

  • Skriv inn følgende formel i celle E2 i Excel-filen.

= SUM (OFFSET (B1, TELL (B: B), 0, -5))

  • Klikk på Enter for å se utdataene.

Som vi allerede har sett, avgir Excel OFFSET-funksjonen rektangulære dynamiske områder når de får en startreferanse.

  • I dette tilfellet er startreferansen gitt i form av B1 (som et første argument for OFFSET-funksjon).
  • Vi brukte COUNT-funksjon fordi vi trenger summen av observasjoner fra den siste verdien. COUNT-funksjon hjelper OFFSET til å bestemme hvor mange observasjoner (rader) som er der i kolonnen B. Den teller alle tallverdiene i kolonne B (19 er tellingen).
  • Med telling som 19 blir OFFSET-formelen:

= OFFSET (B1, 19, 0, -5)

  • Denne formelen starter forskyvning ved B1 med 19 rader og samme kolonne (0). Deretter hjelper høydeparameter -5 denne funksjonen med å utvide rekkevidden i retning bakover med 5 rader (se negativtegn knyttet til 5).
  • Det dynamiske området for de siste fem observasjonene er gitt som en inngang til sumfunksjon. Hvilket gir ved å summere de fra kolonne B.

Excel OFFSET-formel - eksempel # 4

I dette eksemplet vil vi beregne medianomsetningen for de siste tre årene. OFFSET-funksjonen kan brukes som et argument for å beregne det samme.

  • Sett følgende formel i celle E2:

= MEDIAN (OFFSET (B1, TELL (B: B), 0, -3))

  • Trykk Enter for å se utdataene.

Denne formelen fungerer på linjene som ligner den forrige.

  • OFFSET Oppretter et dynamisk område av de tre siste observasjonene fra kolonne B ved hjelp av COUNT-funksjon (= OFFSET (A1, 19, 0, -3)).
  • Dette kan gis som en utgang til MEDIAN-funksjonen. Som deretter sorterer observasjonene i enten stigende eller synkende rekkefølge og deretter velger den midterste verdien som du ser i utdata ($ 212.07).

Excel OFFSET-formel - eksempel # 5

Her vil vi beregne medianåret ved å bruke MEDIAN og OFFSET-funksjonen.

  • Bruk følgende formel for det samme i celle E3:

= Median (OFFSET (A1, COUNT (A: A), 0, -3))

  • Klikk på Enter for å se utdataene.

Det samme har skjedd her. Formelen tok opp et dynamisk område opprettet av OFFSET-funksjon ved hjelp av COUNT. Og brukte det som et argument til en MEDIAN-funksjon som deretter sorterte årverdiene i stigende eller synkende rekkefølge og deretter gitt utdata som 2017.

Bruk av OFFSET-formelen er å skape dynamiske områder. Det er alltid bedre å jobbe med dynamiske områder i stedet for statiske. Som du kanskje har data som blir lagt til nå og da. For eksempel ansatte mester. Dine ansattes stamdata vil alltid bli økt nå og da, da det må være noen nye ansatte som blir med. Det gir ingen mening å bruke samme formel. I stedet for det, lag et område som er dynamisk ved å bruke OFFSET-funksjonen og se magien. Det vil helt sikkert spare deg for tid.

Ting å huske

  • OFFSET-funksjonen returnerer bare en referanse til et bestemt utvalg av celler i Excel. Det flytter ikke cellene fra sine opprinnelige posisjoner.
  • Som standard fungerer denne funksjonen på den øvre delen (når det gjelder rader) og høyre side (i form av kolonner). Imidlertid kan det forsynes med negative argumenter for å reversere standardfunksjonaliteten. Som -negativt radargument hjelper å motregne dataene fra ned til opp og negativt kolargument hjelper til å utligne dataene til venstre.
  • OFFSET-formelen kan redusere Excel-funksjonaliteten, da det er en formel som blir beregnet på nytt hver gang det er endring i regnearket.
  • #REF-feil vil bli kastet hvis området ditt er utenfor rekkevidden til arbeidsboken.
  • Alle andre funksjoner som forventer en henvisning, kan bruke OFFSET-funksjonen i den.

Anbefalte artikler

Dette er en guide til OFFSET Formula i Excel. Her diskuterer vi hvordan du bruker OFFSET-funksjon i Excel sammen med praktiske eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -

  1. Hvordan pakke inn tekst i Excel?
  2. Enkel guide til sortering i Excel
  3. LOOKUP-funksjon i Excel
  4. Hvordan bruker jeg FIND-funksjon i Excel?

Kategori:

Tips for Excel