Excel OFFSET-funksjon (innholdsfortegnelse)

  • OFFSET i Excel
  • OFFSET Formel i Excel
  • Hvordan bruker du OFFSET-funksjonen i Excel?

OFFSET i Excel

OFFSET-funksjon i Excel hjelper brukeren med å returnere en celle eller et område med celler som er et spesifisert nei. av rader og kolonner fra en celle. Vi kan spesifisere nei. av rader og nei. av kolonner som skal returneres.

Nedenfor er OFFSET-formelen i Excel:

OFFSET Funksjon i Excel består av følgende argumenter:

  • Henvisning: Det er argumentet vi ønsker å basere forskyvningen fra. Det kan være en cellereferanse eller et område med tilstøtende celler, hvis ikke Offset returnerer #VALUE! Feilverdi.
  • Rader: Det er nei. av rader som skal utlignes. Hvis vi bruker et positivt tall, utlignes det til radene nedenfor, og hvis et negativt tall blir brukt, utlignes det radene over.
  • Kolonner: Den er for nei. av kolonner som skal forskyves. Så det er det samme konseptet som rader, hvis vi bruker et positivt tall, forskyves det til kolonnene til høyre, og hvis et negativt tall blir brukt, så blir det forskyvninger til kolonnene til venstre.
  • Høyde: Dette argumentet er valgfritt. Det skal være et positivt tall. Det er et tall som representerer antall rader i den returnerte referansen.
  • Bredde: Dette er også et valgfritt argument. Det må være et positivt tall. Det er et tall som representerer antall kolonner i den returnerte referansen.

Merknader:

  • OFFSET-funksjonen i Excel returnerer #REF! feilverdi, hvis rader og kolonner forskyver referanse over kanten av regnearket.
  • I OFFSET-funksjonen antas det å være samme høyde eller bredde som en referanse hvis høyde eller bredde er utelatt.
  • OFFSET returnerer en referanse, den flytter ikke noen celler eller utvalg av celler eller endrer utvalget. Den kan brukes med alle funksjoner som forventer et referanseargument.

Hvordan bruke OFFSET-funksjonen i Excel?

OFFSET-funksjonen i Excel er veldig enkel og enkel å bruke. La oss forstå hvordan OFFSET-funksjonen fungerer i Excel med et eksempel på OFFSET-formelen.

Du kan laste ned denne OFFSET-funksjonen Excel-malen her - OFFSET-funksjonen Excel-mal

Eksempel 1

Offsetformelen returnerer en cellereferanse basert på et utgangspunkt, rader og kolonner som vi spesifiserer. Vi kan se det i eksempelet nedenfor:

= OFFSET (A1, 3, 1)

Formelen forteller Excel om å vurdere celle A1 for utgangspunkt (referanse), og flytt deretter 3 rader ned (rader) og 1 kolonne til venstre (kolonnens argument). Etter dette returnerer OFFSET-formelen verdien i celle B4.

Bildet til venstre viser at funksjonsruten og skjermdumpen til høyre viser hvordan vi kan bruke OFFSET-formelen på sanntidsbasis. Forskjellen mellom de to formlene er at den andre (til høyre) inkluderer en cellehenvisning (D1) i radens argument. Men siden celle D1 inneholder nummer 1, og nøyaktig det samme tallet vises i rader-argumentet til den første formelen, vil begge gi et identisk resultat - verdien i B2.

Eksempel # 2 Bruk av OFFSET-funksjon for sum

Som vi vet at OFFSET-funksjonen også kan brukes med den andre funksjonen til Excel, vil vi i dette eksemplet se med SUM-funksjonen.

I bildet ovenfor, antar vi at vi må finne ut summen av merker, så kan vi også bruke OFFSET-funksjonen.

Formelen er nedenfor:

Så, i formel ovenfor er D5 den første cellen der dataene begynner, som er en referanse som startcelle. Etter det er radene og kolonneverdien 0, så vi kan sette den som 0, 0. Så er det 4 som betyr 4 rader under referansen som Excel trenger for å beregne summen for, så er den til slutt 1, det betyr at det er en kolonne som bredde.

Vi kan henvise til bildet ovenfor for å sammenhenge det forklarte eksemplet.

Og nedenfor er resultatet:

Bare vi må trykke på Enter etter å ha skrevet inn en formel i den nødvendige cellen, og så har vi resultatet.

Ting å huske på OFFSET-funksjonen i Excel

  • OFFSET-funksjonen i Excel returnerer bare en referanse, den flytter ikke celler eller utvalg av celler i faktisk.
  • Hvis OFFSET-formelen returnerer et område med celler, henviser radene og kolonnene alltid til øvre venstre celle i det returnerte området.
  • Henvisningen bør omfatte en celle eller et område med celler ellers vil formelen returnere #VALUE-feilen.
  • Hvis radene og kolonnene som er spesifisert over kanten av regnearket, vil offsetformelen i Excel returnere #REF! feil.
  • OFFSET-funksjonen kan brukes i alle andre Excel-funksjoner som godtar en celle- eller rekkeviddehenvisning i sine argumenter.

For eksempel, hvis vi bruker formelen = OFFSET (A1, 3, 1, 1, 3) på egen hånd, vil det gi en #VALUE! Feil, siden et returområde (1 rad, 3 kolonner) ikke passer i en celle. Imidlertid, hvis vi sorterer den i SUM-funksjonen som nedenfor:

= SUM (OFFSET (A1, 3, 1, 1, 3))

Offsetformelen returnerer summen av verdier i en 1-rad med et 3-kolonnes område som er 3 rader under og 1 kolonne til høyre for celle A1, dvs. summen av verdiene i celler B4: D4.

Som vi kan se er summen av 36 + 63 + 82 av uthevede Mar lik 181, som kom som et resultat i G2 etter anvendelse av sum og Offsetformel. (fremhevet i gult)

Bruk av OFFSET-funksjon i Excel

Som vi har sett at hva OFFSET-funksjonen faktisk gjør ved hjelp av eksemplet ovenfor, kan vi ha spørsmål som hvorfor bry deg om sunget OFFSET-formel som er litt sammensatt, hvorfor ikke bare bruke en direkte referanse som sum eller B4: D4?

OFFSET-funksjonen i Excel er veldig bra for nedenfor:

  • Få rekkevidden fra startcellen:

Noen ganger vet vi ikke den nøyaktige eller faktiske adressen til området, vi vet bare hvor det starter fra en bestemt celle. I dette tilfellet kan vi bruke OFFSET-funksjonen, som er enkel å gå.

  • Opprette dynamisk område:

Henvisningene som eksemplet ovenfor B1: C4 er statiske, det betyr at de alltid refererer til et gitt eller fast område. Men i noen tilfeller er oppgavene lettere å utføre med dynamiske områder. Det hjelper spesielt når vi jobber med å endre data. For eksempel kan vi ha et regneark der ny rad eller kolonne settes inn eller legges til hver dag / uke / måned, i dette tilfellet vil OFFSET-funksjonen hjelpe.

Begrensninger og alternativer for OFFSET-funksjonen i Excel

Siden hver formel i Excel har sine egne begrensninger og alternativer, har vi sett hvordan og når vi skal bruke OFFSET-funksjonen i Excel.

Nedenfor er de kritiske begrensningene for OFFSET-funksjonen i Excel:

  • Ressurs-sulten funksjon:

Det betyr at når det skjer endringer i kildedataene, vil formelen bli beregnet på nytt for hele settet, noe som holder Excel opptatt i lengre tid. Hvis det er et lite regneark, vil det ikke påvirke så mye, men hvis det er et antall regneark, kan det ta tid å beregne Excel på nytt.

  • Vanskelighetsgrad i gjennomgang:

Som vi vet er referansene som returneres av Offset-funksjonen dynamiske eller endrede og kan inneholde en stor formel, som kan være vanskelig å rette eller redigere i henhold til krav.

Alternativer til OFFSET-funksjonen i Excel:

  • INDEX-funksjon:

INDEX-funksjonen i Excel kan også brukes til å lage et dynamisk referansespekter. Det er ikke helt det samme som OFFSET. Men som OFFSET, er INDEX-funksjonen ikke så mye ustabil, og derfor vil den ikke senke Excel, som er en begrensning for OFFSET.

  • INDIREKTE funksjon:

Vi kan også bruke INDIRECT-funksjonen til å lage et dynamisk referansespekter fra mange kilder som celleverdier , tekst og de navngitte områdene.

Anbefalte artikler

Dette har vært en guide til OFFSET i Excel. Her diskuterer vi OFFSET-formelen i Excel og Hvordan bruke OFFSET-funksjonen i Excel sammen med praktiske eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -

  1. MS Excel: LOOKUP-funksjon
  2. Beste eksempel på Excel XIRR-funksjon
  3. Fantastisk guide om IKKE funksjon i Excel
  4. IPMT i Excel med eksempler

Kategori:

Tips for Excel