Excel OFFSET funkció (Tartalomjegyzék)
- OFFSET Excelben
- OFFSET képlet Excelben
- Hogyan lehet használni az OFFSET függvényt az Excelben?
OFFSET Excelben
Az Excel OFFSET funkciója segíti a felhasználót a megadott számú cella vagy cellatartomány visszaadásában. sorok és oszlopok egy cellából. Megadhatjuk a nem-et. sorok száma és nem. a visszatérő oszlopok száma.
Az alábbiakban látható az OFFSET képlet Excelben:
OFFSET függvény az Excelben a következő argumentumokból áll:
- Referencia: Ez az érv, amelyre alapozni akarjuk az eltolást. Lehet, hogy egy cellareferencia vagy a szomszédos cellák sorozata, ha nem az Offset, akkor a #VALUE! Hibaérték.
- Sorok: Ez a nem. sorok közötti eltoláshoz. Ha pozitív számot használunk, akkor az eltolódik az alábbi sorokhoz, és ha negatív számot használ, akkor ellensúlyozza a fenti sorokat.
- Oszlopok: Nem. oszlopok száma az eltoláshoz. Tehát ugyanaz a koncepció, mint a Sorok, ha pozitív számot használunk, akkor eltolódik a jobb oldali oszlopokhoz, és ha negatív számot használunk, akkor eltolódik a bal oldali oszlopokhoz.
- Magasság: Ez az érv választható. Pozitív számnak kell lennie. Ez egy szám, amely a visszaadott hivatkozásban szereplő sorok számát jelöli.
- Szélesség: Ez egy opcionális argumentum is. Pozitív számnak kell lennie. Ez egy szám, amely jelzi az oszlopok számát a visszaküldött referenciában.
Megjegyzés:
- Az Excel OFFSET funkciója visszatér a #REF! hibaérték, ha a sorok és oszlopok eltolják a hivatkozást a munkalap szélén.
- Az OFFSET függvényben feltételezhető, hogy ugyanazzal a magassággal vagy szélességgel rendelkezik, mint a referencia, ha a magasságot vagy a szélességet kihagyják.
- Az OFFSET egy referenciát ad vissza, valójában nem mozgat semmilyen cellát vagy cellatartományt, és nem változtatja meg a kiválasztást. Bármely függvényhez használható, amelyre hivatkozási argumentum vár.
Hogyan lehet használni az OFFSET funkciót az Excelben?
OFFSET Az Excel funkció nagyon egyszerű és könnyen használható. Értelmesse az OFFSET függvény működését az Excelben néhány OFFSET képletpélda segítségével.
Itt letöltheti ezt az OFFSET Function Excel sablont - OFFSET Function Excel sablon1. példa
Az Ofszet képlet a megadott kiindulási pont, sorok és oszlopok alapján adja vissza a cellareferenciát. Az alábbi példában láthatjuk:
= OFFSET (A1, 3, 1)
A képlet arra utasítja az Excel-t, hogy vegye figyelembe az A1 cellát a kiindulási ponthoz (referencia), majd mozgassa 3 sort le (sorok) és 1 oszlop balra (oszlopok argumentum). Ezt követően az OFFSET képlet visszaadja az értéket a B4 cellában.
A bal oldali kép azt mutatja, hogy a funkció útvonala és a jobb oldalon lévő képernyőkép azt szemlélteti, hogyan tudjuk az OFFSET képletet valós idejű adatokban használni. A két képlet közötti különbség az, hogy a második (jobb oldalon) tartalmaz egy cella hivatkozást (D1) a sor argumentumában. Mivel azonban a D1 cella tartalmazza az 1. számot, és pontosan ugyanaz a szám jelenik meg az első képlet sorok argumentumában, mindkettő azonos eredményt ad - a B2-ben megadott értéket.
2. példa Az OFFSET funkció használata az összeghez
Mint tudjuk, hogy az OFFSET függvény az Excel másik funkciójával is használható, ezért ebben a példában a SUM függvényt fogjuk látni.
Tegyük fel, hogy a fenti képen meg kell határoznunk a pontok összegét, majd használhatjuk az OFFSET funkciót is.
Az alábbi képlet:
Tehát a fenti képletben a D5 az első cella, ahol az adatok kezdődnek, amely referenciaként szolgál kiinduló cellaként. Ezután a sorok és oszlopok értéke 0, tehát 0, 0 lehet. Akkor 4, azaz 4 sor jelenik meg azon referencia alatt, amelyre az Excelnek ki kell számítania az összeget, majd végre 1, az azt jelenti, hogy szélességében 1 oszlop van.
A fenti képen hivatkozhatunk a magyarázott példára.
És lent az eredmény:
Csak meg kell nyomnunk az Enter billentyűt, miután beírta a képletet a kívánt cellába, és megkaptuk az eredményt.
Ne feledje, az Excel OFFSET funkciójáról
- Az Excel OFFSET funkciója csak egy referenciát ad vissza, nem mozgat semmilyen cellát vagy sejttartományt a valóságban.
- Ha az OFFSET Formula egy sor cellát ad vissza, akkor a sorok és oszlopok mindig a bal felső cellára utalnak a visszatérő tartományban.
- A hivatkozásnak tartalmaznia kell egy cellát vagy a cellák tartományát, különben a képlet a #VALUE hibát adja vissza.
- Ha a táblázat szélén megadott sorok és oszlopok az Excel eltolási képletével jelenítik meg a #REF! hiba.
- Az OFFSET funkció bármely más Excel függvényben használható, amely argumentumokban elfogadja a cellák vagy tartományok hivatkozásait.
Például, ha önmagában = OFFSET (A1, 3, 1, 1, 3) képletet használunk, akkor az #VALUE! Hiba, mivel a visszatérési tartomány (1 sor, 3 oszlop) nem fér el a cellában. Ha azonban összerendezzük a SUM függvénybe az alábbiak szerint:
= SUM (OFFSET (A1, 3, 1, 1, 3))
Az eltolás képlet egy sorban az értékek összegét egy 3 oszloptartományban adja vissza, amely 3 sor alatt és 1 oszlop az A1 cellától jobbra, azaz a B4: D4 cellákban szereplő összes érték.
Mint láthatjuk, hogy a kiemelt Mar összesen 36 + 63 + 82 értéke 181, ami a G2 eredményeként jött létre az összeg és az eltolás képlet alkalmazása után. (sárgával kiemelve)
Az OFFSET függvény használata Excelben
Mint láttuk, hogy az OFFSET funkció a fenti példa segítségével valójában mi történik, kérdéseink merülhetnek fel: Miért zavarnunk egy kissé összetett OFFSET képletet, miért nem használunk egyszerűen egy közvetlen hivatkozást, mint például az összeg vagy a B4: D4?
Az OFFSET függvény Excelben nagyon jó az alábbiakhoz:
- A tartomány lekérdezése a kezdő cellától:
Időnként nem tudjuk a tartomány pontos vagy tényleges címét, csak azt tudjuk, honnan kezdődik egy adott cellából. Ebben az esetben használhatjuk az OFFSET funkciót, amely könnyen használható.
- Dinamikus tartomány létrehozása:
A referenciák, mint például a fenti B1: C4 példa, statikusak, vagyis mindig egy adott vagy rögzített tartományra vonatkoznak. De bizonyos esetekben a feladatokat könnyebb elvégezni dinamikus tartományokkal. Különösen akkor segít, ha az adatok megváltoztatásával dolgozunk. Létrehozhatunk például egy táblázatot, amelybe minden nap / héten / hónapban új sort vagy oszlopot illesztünk be vagy adunk hozzá, ebben az esetben az OFFSET funkció segít.
Az OFFSET funkció korlátozásai és alternatívái az Excel programban
Mivel az Excel minden egyes formulájának megvannak a saját korlátozásai és alternatívái, láttuk, hogy miként és mikor kell használni az OFFSET függvényt az Excelben.
Az alábbiakban bemutatjuk az OFFSET funkció kritikus korlátozásait az Excelben:
- Erőforrás-éhes funkció:
Ez azt jelenti, hogy amikor megváltozik a forrásadatok, a képletet újra kiszámítják a teljes készletre, ami hosszabb ideig foglalkoztatja az Excel-t. Ha van egy kis táblázat, akkor ez nem befolyásolja ennyit, de ha vannak több táblázat, akkor az Excel időbe telik az újbóli kiszámítás.
- Nehézségi áttekintés:
Mint tudjuk, az Offset függvény által visszaadott hivatkozások dinamikusak vagy változóak, és tartalmazhatnak egy nagy képletet, amelyet nehéznek találhat a követelményeknek megfelelő javítás vagy szerkesztés.
Az OFFSET függvény alternatívái az Excelben:
- INDEX funkció:
Az Excel INDEX funkciója felhasználható dinamikus referenciatartomány létrehozására. Nem pontosan ugyanaz, mint az OFFSET. De az OFFSET-hez hasonlóan az INDEX funkció sem annyira ingatag, tehát nem lassítja az Excel működését, ami korlátozza az OFFSET-t.
- KÖZVETLEN funkció:
Az INDIRECT funkcióval dinamikus referencia-tartományt is létrehozhatunk számos forrásból, például cellaértékekből , szövegből és a megnevezett tartományokból.
Ajánlott cikkek
Ez egy útmutató az OFFSET programhoz Excelben. Itt tárgyaljuk az OFFSET képletet Excelben és az OFFSET függvény használatát Excelben, valamint a gyakorlati példákat és a letölthető Excel sablont. Megnézheti más javasolt cikkeinket -
- MS Excel: LOOKUP funkció
- A legjobb példa az Excel XIRR funkcióra
- Csodálatos útmutató az NEM működéshez az Excel programban
- IPMT Excelben példákkal