Excel OFFSET képlet (Tartalomjegyzék)

  • Mi az OFFSET Formula az Excelben?
  • Hogyan kell használni az OFFSET képletet az Excelben?

Mi az OFFSET Formula az Excelben?

Az Excel OFFSET Formula egy cellát vagy dinamikus téglalap alakú cellát ad ki outputként, amely a teljes adott tartomány részhalmaza, meghatározott sorok, oszlopok, magasság és szélesség mellett.

Az OFFSET Formula szintaxisa Excelben

Az OFFSET funkció magyarázata az Excelben:

  • hivatkozás: Az OFFSET kiindulópontja, vagy mondhatjuk egy olyan cellát, amelyek az OFFSET alapjául tekinthetők.
  • sorok : Az OFFSET-hez sorok száma az alap referencia alatt vagy felett.
  • oszlopok : Az OFFSET-hez tartozó oszlopok száma az alap referenciától jobbra vagy balra.
  • magasság: a visszatért referencia sorok száma.
  • szélesség: Az oszlopok száma a visszatért referenciában.

A magasság és a szélesség opcionális paraméterek ehhez a funkcióhoz, és felhasználhatók a létrehozott referencia méretének meghatározására.

Hogyan kell használni az OFFSET képletet az Excelben?

Most megtanuljuk, hogyan kell eltolás-képletet írni a keresési érték eléréséhez. Nézzük meg az eltolódási képletet néhány példaként az Excelben.

Itt letöltheti ezt az OFFSET Formula Excel sablont - OFFSET Formula Excel sablon

Excel OFFSET képlet - 1. példa

Tegyük fel, hogy az alábbiak szerint adunk adatokat.

  • Az E2 cellába írja be a következő képletet:

= OFFSET (A1, 11, 1)

  • Miután elvégezte a képletet, nyomja meg az Enter billentyűt, és nézze meg a kimenetet.

Ez a képlet az volt, hogy az A1 cellából (év) vett hivatkozást, és 11 sorral lejjebb lépett az A1 után (azaz a 12. sor), majd megkapta az értéket, amely az 11. sor koordinátája az A1 és az első oszlop után ( A = 0 oszlop B = 1 oszlop ebben az esetben. Mint tudjuk, jobbra és balra is mehetünk). Amely 141, 27 millió dollár.

Excel OFFSET képlet - 2. példa

Most egy másik példát fogunk látni egy adott évre vonatkozó referenciacelláról. Helyezze a következő képletet az E3 cellába:

= OFFSET (A1, 11, 0)

  • Miután elvégezte a képletet, nyomja meg az Enter billentyűt, és nézze meg a kimenetet.

Ha látta a képletet, az megegyezik az előzővel, az egyetlen változás az oszlop referenciája. A 0 oszlophivatkozás (nulla) azt jelenti, hogy az értéket az A oszlopból rögzítik az A1 után (azaz 2010) 11. sorban.

Excel OFFSET képlet - 3. példa

Ebben a példában az Excel OFFSET függvény segítségével kiszámoljuk az utolsó N sor összegét. Az utolsó N szám összegzésének általános képlete:

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

Az adatokból az utolsó N megfigyelés összegzéséhez használhatja a SUM funkciót, az OFFSET és a COUNT funkciót együtt. Ebben a példában összegezzük az utolsó 5 adatpontot. Felhívjuk figyelmét, hogy amikor N-t mondunk, ez az egész érték általánosítása. Tehát kiszámolhatja az utolsó 3, az utolsó 4, az utolsó 5 stb. Adatait, az adatkészlettől és a követelményektől függően.

Tegyük fel az adatkészletünkre:

  • Írja be az alábbi képletet az Excel fájl E2 cellájába.

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

  • Kattintson az Enter gombra a kimenet megtekintéséhez.

Mint már láttuk, az Excel OFFSET függvény téglalap alakú dinamikus tartományokat ad ki, ha kezdő referenciát kapnak.

  • Ebben az esetben a kiindulási referenciát a B1 vonatkozásában adjuk meg (az OFFSET funkció első érvének).
  • A COUNT függvényt használtuk, mert szükségünk van az utolsó érték megfigyeléseinek összegére. A COUNT függvény segíti az OFFSET-et annak meghatározásában, hogy hány megfigyelés (sor) van a B oszlopban. Megszámolja a B oszlop összes numerikus értékét (19 a szám).
  • Ha 19-nek számít, akkor az OFFSET képlet így alakul:

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

  • Ez a képlet az Eltolásnál a B1 pontnál 19 sorral és ugyanazzal az oszlopmal (0) kezdődik. Ezután az -5 magassági paraméter segíti ezt a funkciót abban, hogy 5 sorral meghosszabbítsa a tartományt hátrafelé (lásd az 5-öshez tartozó negatív jelet).
  • Az utolsó öt megfigyelés dinamikus tartományát az összegösszeg bemeneteként adjuk meg. Melyik a hozam a B oszlop eredményeinek összegzésénél

Excel OFFSET képlet - 4. példa

Ebben a példában kiszámítjuk az elmúlt 3 év medián eladásait. Az OFFSET funkció érvként használható ennek kiszámításához.

  • Helyezze a következő képletet az E2 cellába:

= MÉDIA (OFFSET (B1, COUNT (B: B), 0, -3))

  • A kimenet megjelenítéséhez nyomja meg az Enter billentyűt.

Ez a képlet az előzőhöz hasonló vonalakon működik.

  • OFFSET A B oszlopból az utolsó három megfigyelés dinamikus tartományát hozza létre a COUNT funkció segítségével (= OFFSET (A1, 19, 0, -3)).
  • Ez kiadható a MEDIAN funkcióhoz. Amely ezt követően növekvő vagy csökkenő sorrendbe rendezi a megfigyeléseket, majd kiválasztja a legmagasabb középső értéket, ahogy az outputban látható (212, 07 USD).

Excel OFFSET képlet - 5. példa

Itt kiszámoljuk a medián évet a MEDIAN és az OFFSET Function segítségével.

  • Használja az alábbi képletet ehhez az E3 cellában:

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

  • Kattintson az Enter gombra a kimenet megtekintéséhez.

Ugyanez történt itt. A képlet felvette az OFFSET függvény által létrehozott dinamikus tartományt, COUNT segítségével. Arra szolgált, mint egy MEDIAN függvény, amely az év értékeit növekvő vagy csökkenő sorrendbe rendezte, majd 2017-re adta a outputot.

Az OFFSET képletet a dinamikus tartományok létrehozása jelenti. Mindig jobb statikus tartományok helyett dinamikus tartományokban dolgozni. Minthogy előfordulhat, hogy van olyan adata, amely időnként hozzáadódik. Például a Employee Master. A munkavállalói törzsadatok időnként növekedni fognak, mivel új alkalmazottaknak csatlakozniuk kell. Minden alkalommal, amikor ugyanazt a képletet használja, nincs értelme. Ehelyett hozzon létre egy dinamikus tartományt az OFFSET funkció segítségével, és nézze meg a varázslatot. Ez biztosan időt takarít meg Önnek.

Dolgok, amikre emlékezni kell

  • Az OFFSET függvény csak az Excel celláinak egy adott tartományára való hivatkozást ad vissza. Ez nem mozgatja a cellákat az eredeti helyzetükből.
  • Alapértelmezés szerint ez a funkció működik a felső részen (sorok szempontjából) és a jobb oldalon (az oszlopok tekintetében). Ennek ellenére negatív érvekkel is elláthatjuk az alapértelmezett funkcionalitás megfordítását. Hasonlóan - a negatív sor argumentum elősegíti az adatok lefelé és felfelé történő kiegyenlítését, a negatív oszlop argumentum pedig az adatok balra való kiegyenlítését segíti elő.
  • Az OFFSET képlet lelassíthatja az Excel funkcionalitását, mivel egy képlet újraszámításra kerül minden alkalommal, amikor a munkalap megváltozik.
  • A #REF hiba eldobódik, ha az Ön hatósugara kívül esik a munkafüzet tartományán.
  • Bármely más, referenciát váró funkció képes használni az OFFSET funkciót benne.

Ajánlott cikkek

Ez az útmutató az OFFSET Formula programhoz Excelben. Itt tárgyaljuk, hogyan lehet az OFFSET Function-t használni az Excelben, a gyakorlati példákkal és a letölthető Excel sablonnal együtt. Megnézheti más javasolt cikkeinket -

  1. Hogyan lehet szöveget pakolni az Excel programban?
  2. Egyszerű útmutató a rendezéshez Excelben
  3. LOOKUP függvény Excelben
  4. Hogyan lehet használni a FIND függvényt Excelben?

Kategória:

Excel tippek