Excel OFFSET-formel (indholdsfortegnelse)

  • Hvad er OFFSET-formlen i Excel?
  • Hvordan bruges OFFSET-formlen i Excel?

Hvad er OFFSET-formlen i Excel?

OFFSET Formel i Excel giver en celle eller et interval af dynamiske rektangulære celler som et output, som er en undergruppe af det samlede givne interval med et specificeret antal rækker, kolonner, højde og bredde.

Syntaks af OFFSET-formlen i Excel

Forklaring af OFFSET-funktion i Excel:

  • reference: Udgangspunktet for OFFSET, eller vi kan sige et område af celler, der kan betragtes som basen for OFFSET.
  • rækker : Antallet af rækker til OFFSET under eller over basisreferencen.
  • cols: Antallet af kolonner til OFFSET til højre eller venstre for basisreferencen.
  • højde: Antal rækker i returneret reference.
  • bredde: Antal kolonner i returneret reference.

Højde og bredde er de valgfri parametre til denne funktion og kan bruges til at bestemme størrelsen på den oprettede reference.

Hvordan bruges OFFSET-formlen i Excel?

Vi lærer nu, hvordan man skriver offsetformel for at få opslagværdien. Lad os forstå offsetformlen med nogle eksempler i Excel.

Du kan downloade denne OFFSET formel Excel-skabelon her - OFFSET formel Excel-skabelon

Excel OFFSET-formel - eksempel # 1

Antag, at vi har data som angivet nedenfor.

  • Indtast følgende formel i celle E2:

= OFFSET (A1, 11, 1)

  • Når det er gjort med formlen, skal du trykke på Enter og se output.

Hvad denne formel gjorde, er, at den har henvist fra celle A1 (år) og flyttet ned med 11 rækker efter A1 (dvs. række nr. 12) og derefter fået værdien, der er koordinat for den 11. række efter A1 og første kolonne ( kolonne A = 0 kolonne B = 1 i dette tilfælde. Som vi ved, kan vi gå både til højre og venstre). Hvilket er $ 141, 27 millioner.

Excel OFFSET-formel - eksempel # 2

Nu vil vi se et andet eksempel på en referencecelle for et specifikt år. Sæt følgende formel i celle E3:

= OFFSET (A1, 11, 0)

  • Når det er gjort med formlen, skal du trykke på Enter og se output.

Hvis du har set formlen, er den den samme som i foregående, den eneste ændring er i kolonnehenvisning. Søjlehenvisningen 0 (Nul) betyder, at værdien indfanges fra kolonne A for 11. række efter A1 (hvilket er 2010).

Excel OFFSET-formel - eksempel # 3

I dette eksempel beregner vi summen af ​​de sidste N-rækker ved hjælp af Excel OFFSET-funktionen. Den generelle formel for opsummering af de sidste N-tal er:

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

For at opsummere de sidste N-observationer fra dine data, kan du bruge SUM-funktionen sammen med OFFSET og COUNT-funktionen sammen. I dette eksempel opsummerer vi de sidste 5 datapunkter. Bemærk, at når vi siger N, er det en generalisering af en heltalværdi. Så du kan beregne for sidste 3, sidste 4, sidste 5 osv. Afhængigt af dit datasæt såvel som dit krav.

Lad os få det gjort til vores datasæt:

  • Indsæt følgende formel i celle E2 i excel-filen.

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

  • Klik på Enter for at se output.

Som vi allerede har set, udsender Excel OFFSET-funktionen rektangulære dynamiske intervaller, når de får en startreference.

  • I dette tilfælde leveres startreferencen i form af B1 (som et første argument til OFFSET-funktion).
  • Vi brugte COUNT funktion, fordi vi har brug for summen af ​​observationer fra den sidste værdi. Tællefunktion hjælper OFFSET med at bestemme, hvor mange observationer (rækker) der er i kolonnen B. Den tæller alle de numeriske værdier i kolonne B (19 er tællingen).
  • Med optælling som 19 bliver OFFSET-formlen:

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

  • Denne formel starter modregning ved B1 med 19 rækker og samme kolonne (0). Derefter hjælper højdeparameter -5 denne funktion med at udvide området i bagudretning med 5 rækker (se det negative tegn, der er knyttet til 5).
  • Det dynamiske interval for Sidste fem observationer er angivet som input til sum-funktion. Hvilket udbytte ved at opsummere dem fra kolonne B.

Excel OFFSET-formel - eksempel # 4

I dette eksempel beregner vi medianomsætningen for de sidste 3 år. OFFSET-funktion kan bruges som et argument til at beregne det samme.

  • Sæt følgende formel i celle E2:

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

  • Tryk på Enter for at se output.

Denne formel fungerer på de linjer, der ligner den foregående.

  • OFFSET Opretter et dynamisk interval af de sidste tre observationer fra kolonne B ved hjælp af COUNT-funktion (= OFFSET (A1, 19, 0, -3)).
  • Dette kan gives som et output til MEDIAN-funktionen. Hvilket derefter sorterer observationerne i enten stigende eller faldende rækkefølge og derefter vælger den mest midterste værdi, som du ser i output ($ 212, 07).

Excel OFFSET-formel - eksempel # 5

Her beregner vi medianåret ved hjælp af MEDIAN og OFFSET funktion.

  • Brug følgende formel til det samme i celle E3:

= MEDIAN (FORSKYDNING (A1, COUNT (A: A), 0, -3))

  • Klik på Enter for at se output.

Den samme ting er sket her. Formlen tog et dynamisk interval oprettet med OFFSET-funktion ved hjælp af COUNT. Og brugte det som et argument til en MEDIAN-funktion, der derefter sorterede årværdierne i stigende eller faldende rækkefølge og derefter givet output som 2017.

Anvendelse af OFFSET-formlen er til at skabe de dynamiske intervaller. Det er altid bedre at arbejde på dynamiske intervaller i stedet for statiske. Som du måske har data, der bliver tilføjet nu og da. For eksempel medarbejdermester. Dine medarbejder-stamdata vil altid blive øget hver gang, da der skal være nogle nye medarbejdere, der tilslutter sig. Hver gang at bruge den samme formel giver det ingen mening. I stedet for at oprette et interval, der er dynamisk ved hjælp af OFFSET-funktionen og se magien. Det vil helt sikkert spare dig tid.

Ting at huske

  • OFFSET-funktion returnerer kun en henvisning til en bestemt række celler i Excel. Det flytter ikke cellerne fra deres oprindelige positioner.
  • Som standard fungerer denne funktion på den øverste del (med hensyn til rækker) og højre side (med hensyn til kolonner). Det kan dog forsynes med negative argumenter for at vende standardfunktionaliteten. Ligesom -negativ række-argument hjælper med at udligne dataene fra ned til op og negativt col-argument hjælper med at udligne dataene til venstre.
  • OFFSET-formel kan bremse Excel-funktionaliteten, da det er en formel, der genberegnes hver gang der sker en ændring i regnearket.
  • #REF-fejl bliver kastet, hvis dit interval ligger uden for rækkevidden af ​​din projektmappe.
  • Enhver anden funktion, der forventer en henvisning, er i stand til at bruge OFFSET-funktion i den.

Anbefalede artikler

Dette er en guide til OFFSET-formler i Excel. Her diskuterer vi, hvordan man bruger OFFSET-funktion i Excel sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -

  1. Sådan pakkes tekst i Excel?
  2. Enkel guide til sortering i Excel
  3. LOOKUP-funktion i Excel
  4. Hvordan bruges FIND-funktion i Excel?

Kategori:

Excel-tip