Hur man använder Excel OFFSET-funktionen

Innehållsförteckning

Sammanfattning

Excel OFFSET-funktionen returnerar en referens till ett intervall konstruerat med fem ingångar: (1) en startpunkt, (2) en radförskjutning, (3) en kolumnförskjutning, (4) en höjd i rader, (5) en bredd i kolumner. OFFSET är praktiskt i formler som kräver ett dynamiskt omfång.

Ändamål

Skapa en referensoffset från en given startpunkt

Returvärde

En cellreferens.

Syntax

= OFFSET (referens, rader, kolar, (höjd), (bredd))

Argument

  • referens - Startpunkten, tillhandahållen som cellreferens eller intervall.
  • rader - Antalet rader som ska kompenseras under startreferensen.
  • cols - Antalet kolumner som ska kompenseras till höger om startreferensen.
  • höjd - (valfritt) Höjden i rader i den returnerade referensen.
  • bredd - (valfritt) Bredden i kolumner i den returnerade referensen.

Version

Excel 2003

Användningsanmärkningar

Excel OFFSET-funktionen returnerar ett dynamiskt omfång konstruerat med fem ingångar: (1) en startpunkt, (2) en radförskjutning, (3) en kolumnförskjutning, (4) en höjd i rader, (5) en bredd i kolumner.

Utgångspunkten ( referensargumentet ) kan vara en cell eller ett cellintervall. De rader och COLS argument är antalet celler till "offset" från startpunkten. De höjd och bredd argument är valfria och bestämma storleken av intervallet som skapas. När höjd och bredd utelämnas, är de som standard referenshöjd och -bredd .

Till exempel, för referens C5 som börjar med A1, är referens A1, rader är 4 och kolar är 2:

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

Att referera till C1: C5 från A1, referens är A1, rader är 0, kolar är 2, höjd är 5 och bredd är 1:

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

Obs: bredd kan utelämnas, eftersom det kommer att vara 1.

Det är vanligt att OFFSET är insvept i en annan funktion som förväntar sig ett intervall. Till exempel till SUM C1: C5, börjar med A1:

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

Huvudsyftet med OFFSET är att låta formler dynamiskt anpassa sig till tillgänglig data eller till användarens inmatning. OFFSET-funktionen kan användas för att bygga ett dynamiskt namnområde för diagram eller pivottabeller för att säkerställa att källdata alltid är uppdaterad.

Obs! Excel-dokumentationen anger att höjd och bredd inte kan vara negativa, men negativa värden verkar ha fungerat bra sedan början av 1990-talet. OFFSET-funktionen i Google Sheets tillåter inte ett negativt värde för höjd- eller breddargument.

Exempel

Exemplen nedan visar hur OFFSET kan konfigureras för att returnera olika typer av intervall. Dessa skärmar togs med Excel 365, så OFFSET returnerar en dynamisk matris när resultatet är mer än en cell. I äldre versioner av Excel kan du använda F9-tangenten för att kontrollera resultat som returneras från OFFSET.

Exempel 1

På skärmen nedan använder vi OFFSET för att returnera det tredje värdet (mars) i den andra kolumnen (väst). Formeln i H4 är:

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

Exempel 2

På skärmen nedan använder vi OFFSET för att returnera det sista värdet (juni) i den tredje kolumnen (norr). Formeln i H4 är:

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

Exempel # 3

Nedan använder vi OFFSET för att returnera alla värden i den tredje kolumnen (North). Formeln i H4 är:

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

Exempel 4

Nedan använder vi OFFSET för att returnera alla värden för maj (femte raden). Formeln i H4 är:

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

Exempel # 5

Nedan använder vi OFFSET för att returnera april-, maj- och juni-värdet för regionen West. Formeln i H4 är:

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

Exempel 6

Nedan använder vi OFFSET för att returnera april, maj och juni värde för väst och norr. Formeln i H4 är:

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

Anteckningar

  • OFFSET returnerar bara en referens, inga celler flyttas.
  • Både rader och kolar kan tillhandahållas som negativa tal för att vända sin normala förskjutningsriktning - negativa kolförskjutningar till vänster och negativa rader förskjutna ovan.
  • OFFSET är en "flyktig funktion" - den kommer att räkna om vid varje ändring av kalkylbladet. Flyktiga funktioner kan göra att större och mer komplexa arbetsböcker går långsamt.
  • OFFSET visar #REF! felvärde om förskjutningen ligger utanför kalkylbladets kant.
  • När höjd eller bredd utelämnas används referenshöjd och -bredd.
  • OFFSET kan användas med alla andra funktioner som förväntar sig att få en referens.
  • Excel-dokumentation säger att höjd och bredd inte kan vara negativa, men negativa värden fungerar.

Relaterade videoklipp

Hur man skapar ett dynamiskt namnområde med OFFSET I den här videon tittar vi på hur man skapar ett dynamiskt namnområde med OFFSET-funktionen, det vanligaste sättet att skapa dynamiskt namnintervall med en formel.

Intressanta artiklar...