Az Excel OFFSET függvény használata -

Összegzés

Az Excel OFFSET függvény visszaadja az öt bemenettel felépített tartomány referenciáját: (1) kiindulópont, (2) soreltolás, (3) oszlopeltolás, (4) magasság sorokban, (5) szélesség oszlopok. Az OFFSET hasznos olyan képletekben, amelyek dinamikus tartományt igényelnek.

Célja

Hozzon létre egy referenciaeltolást az adott kiinduló pontról

Visszatérési érték

Cella hivatkozás.

Szintaxis

= OFFSET (referencia, sorok, oszlopok, (magasság), (szélesség))

Érvek

  • referencia - a kiindulási pont, cella referenciaként vagy tartományként adva
  • sorok - A kezdő referencia alatt eltolandó sorok száma.
  • oszlopok - A kiindulási referenciától jobbra eltolandó oszlopok száma.
  • magasság - (opcionális) A visszaküldött referencia magassága sorokban.
  • szélesség - (opcionális) A visszaküldött referencia oszlopainak szélessége.

Változat

Excel 2003

Használati megjegyzések

Az Excel OFFSET függvény öt bemenettel felépített dinamikus tartományt ad vissza: (1) kiindulópont, (2) soreltolás, (3) oszlopeltolás, (4) magasság sorokban, (5) szélesség oszlopokban.

A kiindulási pont ( referencia- argumentum) lehet egy cella vagy egy cellatartomány. A sorok és oszlopok érvek a sejtek számát, hogy „offset” a kiindulási pont. A magasság és szélesség argumentumok nem kötelezőek, és meghatározzák a létrehozott tartomány méretét. Ha a magasság és a szélesség elmarad, akkor alapértelmezés szerint a referencia magassága és szélessége lesz .

Például, a referencia C5 kezdődően A1, referencia jelentése A1, sorok 4, és cols 2:

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

A hivatkozás C1: C5 A1, referencia jelentése A1, sorok értéke 0, oszlopok jelentése 2, magassága 5, és szélessége 1:

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

Megjegyzés: a szélesség elhagyható, mivel alapértelmezés szerint 1.

Gyakori, hogy az OFFSET egy másik függvénybe van csomagolva, amely egy tartományra számít. Például SUM C1: C5-re, A1-től kezdődően:

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

Az OFFSET fő célja, hogy lehetővé tegye a képletek dinamikus alkalmazkodását a rendelkezésre álló adatokhoz vagy a felhasználói bemenethez. Az OFFSET funkció segítségével dinamikus elnevezett tartomány készíthető a diagramokhoz vagy a pivot táblákhoz, hogy a forrásadatok mindig naprakészek legyenek.

Megjegyzés: Az Excel dokumentációja szerint a magasság és a szélesség nem lehet negatív, de a negatív értékek az 1990-es évek eleje óta jól működnek. A Google Táblázatok OFFSET funkciója nem enged negatív értéket a magasság vagy szélesség argumentumokban.

Példák

Az alábbi példák bemutatják, hogy az OFFSET hogyan konfigurálható különféle tartományok visszaadására. Ezek a képernyők az Excel 365 alkalmazással készültek, így az OFFSET dinamikus tömböt ad vissza, ha az eredmény egynél több cella. Az Excel régebbi verzióiban az F9 billentyűvel ellenőrizheti az OFFSET-ből visszaküldött eredményeket.

1. példa

Az alábbi képernyőn az OFFSET segítségével adjuk vissza a harmadik értéket (március) a második oszlopban (Nyugat). A H4 képlete a következő:

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

2. példa

Az alábbi képernyőn az OFFSET segítségével visszaküldjük a harmadik oszlop (észak) utolsó értékét (június). A H4 képlete a következő:

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

3. példa

Az alábbiakban az OFFSET segítségével adjuk vissza a harmadik oszlop (észak) összes értékét. A H4 képlete a következő:

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

4. példa

Az alábbiakban az OFFSET segítségével adjuk vissza az összes májusi értéket (ötödik sor). A H4 képlete a következő:

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

5. példa

Az alábbiakban az OFFSET-et használjuk a nyugati régió április, május és június értékének visszaadásához. A H4 képlete a következő:

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

6. példa

Az alábbiakban az OFFSET-et használjuk az áprilisi, májusi és júniusi érték nyugati és északi értékeinek visszatérítésére. A H4 képlete a következő:

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

Megjegyzések

  • Az OFFSET csak referenciát ad vissza, egyetlen cellát sem mozgat.
  • Mindkét sorok és oszlopok is szállíthatók a negatív számok, hogy változtasson a normál ofszet irány - negatív oszlopok balra eltolva, és negatív sorok ofszet felett.
  • Az OFFSET egy "volatilis függvény" - minden munkalap-változással újraszámol. Az illékony funkciókkal a nagyobb és összetettebb munkafüzetek lassan futhatnak.
  • Az OFFSET megjeleníti a #REF! hibaérték, ha az eltolás kívül esik a munkalap szélén.
  • A magasság vagy szélesség elhagyása esetén a referencia magasságát és szélességét kell használni.
  • Az OFFSET bármely más funkcióval használható, amely elvárja, hogy referenciát kapjon.
  • Az Excel dokumentációja szerint a magasság és a szélesség nem lehet negatív, de a negatív értékek működnek.

Kapcsolódó videók

Hogyan hozhatunk létre dinamikus nevű tartományt az OFFSET használatával Ebben a videóban megvizsgáljuk, hogyan lehet dinamikus nevű tartományt létrehozni az OFFSET függvény segítségével, amely a képlettel történő dinamikus névtartomány létrehozásának leggyakoribb módja.

érdekes cikkek...