Talán az egyik leghasznosabb és legtrükkösebb függvénye az Excelnek az Eltolás (angolul: OFFSET) függvény. Nehéz megérteni, de annál hasznosabb, érdemes időt szánni rá. Hogyan néz ki és mire jó?
ELTOLÁS függvény
Töltsd le a feladatot innen!
Az ELTOLÁS függvény (2007-es verzióban OFSZET néven keresd) argumentumai:
=ELTOLÁS(hivatkozás; sorok; oszlopok; [magasság]; [szélesség])
=ELTOLÁS(ebből a cellából indulva; ennyi sort mozogj le/fel; ennyi oszlopot mozogj jobbra/balra; [az eredményül adott tartomány magassága]; [az eredményül adott tartomány szélessége])
Mit csinál tulajdonképpen?
- Egy cellahivatkozást ad eredményül
- Egy meghatározott cellából vagy tartományból indulva
- meghatározott számú sorral lejjebb/feljebb
- meghatározott számú oszloppal jobbra/balra
Hogy mi?? Bennem is ez fogalmazódott meg, mikor először találkoztam az eltolás függvénnyel. Nem egyszerű, de nagyon hasznos. A használhatóságáról a végén, előbb ismerkedjünk meg vele.
ELTOLÁS függvény lépésről lépésre
Mit csinál tulajdonképpen?
- Egy cellahivatkozást ad eredményül
- Egy meghatározott cellából vagy tartományból indulva
- meghatározott számú sorral lejjebb/feljebb
- meghatározott számú oszloppal jobbra/balra
Hogy mi?? Bennem is ez fogalmazódott meg, mikor először találkoztam az eltolás függvénnyel. Nem egyszerű, de nagyon hasznos. A használhatóságáról a végén, előbb ismerkedjünk meg vele.
- A hivatkozásnál add meg, hogy honnan indulva mozogjon a függvény, jelen esetben ez a B18-as cella, ahol a Név található (ebben az oszlopban szeretnék ugyanis keresni)
- Második argumentumként azt kell megadnod, hogy hány sort mozogjon le/fel. Ha pozitív számot adsz meg, lefelé mozog az Excel, ha negatívat, akkor felfelé. A példában én 2-t adtam meg, vagyis a B18-as cellától indulva az alatta lévő 2. sorban járunk most.
- Meg kell adnod, hogy hány oszlopot mozogjon jobbra. A példában ez 0, mert én ugyanebben az oszlopban szeretnék maradni.
- Mivel a magasság és a szélesség nem kötelező, nem is írtam be. Alapértelmezettként egyébként akkora tartományt ad eredményül, amekkora a hivatkozás.
Nézzük a példát!
A B18-as cellából indulva menj lefelé 2 cellát, jobbra nullát. Ekkor a végeredmény „Klára”.
Ez persze egy elég egyszerű példa, viszonylag ritkán fogod használni ilyen egyszerűen. Attól egyszerű, hogy egyértelműen meg tudtad mondani, hogy Te a 2. sorban lévő értéket szeretnéd látni.
ELTOLÁS függvény gyakorlati példa
Tegyük fel, hogy egy listában az utolsó értéket szeretnéd megtalálni, ráadásul ezt dinamikusan is megteheted, így ha frissül az adatsorod, az utolsó érték is automatikusan frissül.
A rendelések közül mindig az utolsó szeretném jobb felül látni.
Ez egy kicsit trükkösebb, itt egymásba ágyazott függvényeket kell használnunk.
Első lépés: Gépeld be a függvény nevét: =ELTOLÁS(
Második lépés: Add meg a hivatkozást, vagyis azt, hogy honnan induljon a függvény, ez a C31-es cella. =ELTOLÁS(C31;
Harmadik lépés: Meg kell adnod, hogy hány sort menjen lefelé.
Ez a trükkös rész, hiszen valahogy meg kell mutatnunk az Excel-nek, hogy az utolsót vegye. Ehhez meg kell számoltatnunk vele, hogy hány olyan sor van, ami szöveget tartalmaz. Ehhez használjuk a DARAB2 függvényt. (TIPP: ha számok vannak a tartományban, akkor a DARAB függvényt használd!). Gépeld be a DARAB2 függvényt:
=ELTOLÁS(C31;DARAB2(
Ennek argumentumaként csak a tartományt kell megadnod. Azért, hogy dinamikusan változó legyen a függvény, adj meg jóval nagyobb tartományt, hiszen a listád bővülni fog.
=ELTOLÁS(C31;DARAB2(C31:C1000)… Például 1000. sorig.
Még egy trükk, hogy ebből 1-et ki kell vonni, hiszen a 8 adatot tartalmazó oszlop első adatától indulva 8at haladva lefelé a 9. adatot kapod, ami egy üres cella.
=ELTOLÁS(C31;DARAB2(C31:C1000)-1;
Itt tehát a sorokat nem egy konkrét számként adod meg, hanem megszámoltatod egy másik függvénnyel, hogy hányadik sorig menjen lefelé az ELTOLÁS függvény.
Negyedik lépés: Add meg, hogy hány oszlopot menjen jobbra vagy balra! Ez jelen esetben 0, hiszen ugyanabban az oszlopban szeretnék maradni.
Ötödik lépés: Megadhatod magasságnak és szélességnek az 1-et, de ha kihagyod az sem baj, automatikusan 1-nek veszi az Excel.
A kész függvény tehát így néz ki: =ELTOLÁS(C31;DARAB2(C31:C1000)-1;0;1;1)
Próbáld ki, hogy a könyv alá beírsz még adatokat, látni fogod, hogy a függvény automatikusan módosul.
Nem is volt olyan nehéz, ugye? :)
Ha tetszett, kérlek, oszd meg másokkal is!
ISMERD MEG INGYEN AZ EXCEL KIMUTATÁS FUNKCIÓJÁT
Annyira fontos funkció az Excelben a kimutatások ismerete, hogy szeretném, ha Te is megismerhetnéd és használnád a munkád során, így most ajándékként megkapod tőlem! Hét rövid videóból Te magad is végigmehetsz a lépéseken, hogy jobban megértsd működését. Vágj bele most és használd bátran a munkádban! 🙂
