Sådan bruges Excel OFFSET-funktionen

Indholdsfortegnelse

Resumé

Excel OFFSET-funktionen returnerer en reference til et interval konstrueret med fem indgange: (1) et startpunkt, (2) en rækkeforskydning, (3) en kolonneforskydning, (4) en højde i rækker, (5) en bredde i kolonner. OFFSET er praktisk i formler, der kræver et dynamisk område.

Formål

Opret en referenceforskydning fra det givne startpunkt

Returneringsværdi

En cellehenvisning.

Syntaks

= OFFSET (reference, rækker, søjler, (højde), (bredde))

Argumenter

  • reference - Startpunktet, leveret som en cellereference eller et interval.
  • rækker - Antallet af rækker, der skal forskydes under startreferencen.
  • cols - Antallet af kolonner, der skal forskydes til højre for startreferencen.
  • højde - (valgfrit) Højden i rækker af den returnerede reference.
  • bredde - (valgfrit) Bredden i kolonner i den returnerede reference.

Version

Excel 2003

Brugsanvisninger

Excel OFFSET-funktionen returnerer et dynamisk område konstrueret med fem indgange: (1) et startpunkt, (2) en rækkeforskydning, (3) en kolonneforskydning, (4) en højde i rækker, (5) en bredde i kolonner.

Udgangspunktet ( referenceargumentet ) kan være en celle eller et celleområde. De rækker og cols argumenter er antallet af celler til "offset" fra udgangspunktet. De højde og bredde argumenter er valgfri og bestemme størrelsen af det område, der er oprettet. Når højde og bredde er udeladt, er de standard referencens højde og bredde .

For eksempel til reference C5, der starter ved A1, er reference A1, rækker er 4 og cols er 2:

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

For at henvise til C1: C5 fra A1, er reference A1, rækker er 0, cols er 2, højde er 5 og bredde er 1:

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

Bemærk: bredde kan udelades, da den som standard er 1.

Det er almindeligt at se OFFSET pakket i en anden funktion, der forventer et interval. For eksempel til SUM C1: C5, der starter ved A1:

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

Hovedformålet med OFFSET er at give formler mulighed for dynamisk at justere til tilgængelige data eller til brugerinput. OFFSET-funktionen kan bruges til at opbygge et dynamisk navngivet område til diagrammer eller drejetabeller for at sikre, at kildedata altid er opdateret.

Bemærk: Excel-dokumentation angiver, at højde og bredde ikke kan være negativ, men negative værdier ser ud til at have fungeret fint siden begyndelsen af ​​1990'erne. OFFSET-funktionen i Google Sheets tillader ikke en negativ værdi for højde- eller breddeargumenter.

Eksempler

Eksemplerne nedenfor viser, hvordan OFFSET kan konfigureres til at returnere forskellige slags intervaller. Disse skærmbilleder blev taget med Excel 365, så OFFSET returnerer et dynamisk array, når resultatet er mere end en celle. I ældre versioner af Excel kan du bruge F9-tasten til at kontrollere resultater, der returneres fra OFFSET.

Eksempel 1

På skærmen nedenfor bruger vi OFFSET til at returnere den tredje værdi (marts) i den anden kolonne (vest). Formlen i H4 er:

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

Eksempel 2

På skærmen nedenfor bruger vi OFFSET til at returnere den sidste værdi (juni) i den tredje kolonne (Nord). Formlen i H4 er:

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

Eksempel 3

Nedenfor bruger vi OFFSET til at returnere alle værdier i den tredje kolonne (Nord). Formlen i H4 er:

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

Eksempel 4

Nedenfor bruger vi OFFSET til at returnere alle værdier for maj (femte række). Formlen i H4 er:

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

Eksempel 5

Nedenfor bruger vi OFFSET til at returnere april-, maj- og juni-værdien for West-regionen. Formlen i H4 er:

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

Eksempel 6

Nedenfor bruger vi OFFSET til at returnere værdien for april, maj og juni for vest og nord. Formlen i H4 er:

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

Bemærkninger

  • OFFSET returnerer kun en reference, ingen celler flyttes.
  • Både rækker og cols kan leveres som negative tal for at vende deres normale forskydningsretning - negative cols forskudt til venstre og negative rækker forskudt ovenfor.
  • OFFSET er en "flygtig funktion" - den genberegner ved hver regnearksændring. Flygtige funktioner kan få større og mere komplekse projektmapper til at køre langsomt.
  • OFFSET viser #REF! fejlværdi, hvis forskydningen er uden for regnearkets kant.
  • Når højde eller bredde er udeladt, anvendes referencens højde og bredde .
  • OFFSET kan bruges med enhver anden funktion, der forventer at modtage en reference.
  • Excel-dokumentation siger, at højde og bredde ikke kan være negativ, men negative værdier fungerer.

Lignende videoer

Sådan oprettes et dynamisk navngivet område med OFFSET I denne video vil vi se på, hvordan man opretter et dynamisk navngivet område ved hjælp af OFFSET-funktionen, den mest almindelige måde at oprette dynamiske navneområde med en formel på.

Interessante artikler...