Excel OFFSET funkció (példa + videó)
Mikor kell használni az Excel OFFSET funkciót?
Az Excel OFFSET funkció akkor használható, ha hivatkozást szeretne kapni, amely eltolja a megadott számú sort és oszlopot a kezdőpontból.
Mit ad vissza
Visszaadja azt a hivatkozást, amelyre az OFFSET funkció mutat.
Szintaxis
= OFFSET (hivatkozás, sorok, oszlopok, [magasság], [szélesség])
Beviteli érvek
- referencia - Az a referencia, amelytől eltolni szeretné. Ez lehet cellareferencia vagy szomszédos cellák sora.
- sorok - az eltolható sorok száma. Ha pozitív számot használ, akkor az ellensúlyozza az alábbi sorokat, és ha negatív számot használ, akkor a fenti sorokat.
- cols - az eltolható oszlopok száma. Ha pozitív számot használ, az eltolódik a jobb oldali oszlopokhoz, és ha negatív számot használ, akkor a bal oldali oszlopokhoz.
- [magasság] - ez a szám a visszaadott hivatkozás sorainak számát jelöli.
- [szélesség] - ez a szám a visszaadott hivatkozás oszlopainak számát jelöli.
Az Excel OFFSET függvény alapjainak megértése
Az Excel OFFSET funkció valószínűleg az egyik legzavaróbb funkció az Excelben.
Vegyünk egy egyszerű példát egy sakk játékra. A sakkban van egy darab, amit Rook -nak (más néven toronynak, márkinak vagy rektornak) neveznek.
[Kép jóvoltából: Csodálatos Wikipédia]
Most, mint a többi sakkfigurának, a Rooknak is van egy meghatározott útvonala, amelyen mozoghat a táblán. Mehet egyenesen (jobbra/balra vagy fel/le a táblán), de nem mehet átlósan.
Tegyük fel például, hogy van sakktáblánk az Excelben (az alábbiak szerint), egy adott mezőben (ebben az esetben D5) a Rook csak a négy kiemelt irányba mehet.
Ha most megkérem Önt, hogy vigye a Rokkot a jelenlegi helyzetéből a sárgával kiemelt helyre, mit mondjon az újoncnak?
Meg fogja kérni, hogy tegyen két lépést lefelé és két lépést jobbra… nem?
És ez közel van az OFFSET funkció működéséhez.
Most nézzük meg, mit jelent ez az Excelben. A D5 cellával szeretném kezdeni (ahol a Rook található), majd két sorral lejjebb és két oszloppal jobbra lemenni, és lekérni az értéket az ott található cellából.
A képlet a következő lenne:
= OFFSET (honnan induljon, hány sor lefelé, hány oszlop jobbra)
Amint láthatja, a fenti példában szereplő képlet a J1 cellában = OFFSET (D5,2,2).
A D5 -nél kezdődött, majd két sorral lejjebb és két oszloppal jobbra ment, és elérte az F7 cellát. Ezután visszaadta az F7 cellában lévő értéket.
A fenti példában 3 érvvel néztük meg az OFFSET függvényt. De további két opcionális érv is használható.
Nézzünk itt egy egyszerű példát:
Tegyük fel, hogy az A1 cella hivatkozását (sárga) szeretné használni, és a képletben a kékkel (C2: E4) kiemelt teljes tartományra kíván hivatkozni.
Hogyan tenné ezt billentyűzet használatával? Először lépjen a C2 cellába, majd válassza ki a C2: E4 összes celláját.
Most nézzük meg, hogyan kell ezt megtenni az OFFSET képlet használatával:
= OFFSET (A1,1,2,3,3)
Ha ezt a képletet használja egy cellában, akkor #ÉRTÉK értéket ad vissza! hiba, de ha belép a szerkesztési módba, és kiválasztja a képletet, és megnyomja az F9 billentyűt, látni fogja, hogy az összes kékkel kiemelt értéket visszaadja.
Most nézzük meg, hogyan működik ez a képlet:
= OFFSET (A1,1,2,3,3)
- Az első érv az a cella, ahol el kell kezdeni.
- A második argumentum az 1, amely azt mondja az Excelnek, hogy adjon vissza egy hivatkozást, amelyet 1 sorral eltoltunk.
- A harmadik argumentum a 2, amely azt mondja az Excelnek, hogy adjon vissza egy hivatkozást, amelyet 2 oszlop eltolt.
- A negyedik érv a 3. Ez azt határozza meg, hogy a hivatkozásnak 3 sort kell lefednie. Ezt nevezik magasság érvnek.
- Az ötödik argumentum a 3. Ez azt határozza meg, hogy a hivatkozásnak 3 oszlopot kell lefednie. Ezt nevezzük szélességi argumentumnak.
Most, hogy már hivatkozott egy cellatartományra (C2: E4), használhatja azt más funkciókon belül is (például SUM, COUNT, MAX, AVERAGE).
Remélhetőleg jól ismeri az Excel OFFSET funkció használatát. Most nézzünk néhány gyakorlati példát az OFFSET funkció használatára.
Excel OFFSET függvény - Példák
Íme két példa az Excel OFFSET funkció használatára.
1. példa - Az utolsó kitöltött cella megkeresése az oszlopban
Tegyük fel, hogy bizonyos adatok vannak az alábbi oszlopban:
Az oszlop utolsó cellájának megkereséséhez használja a következő képletet:
= OFFSET (A1, COUNT (A: A) -1,0)
Ez a képlet feltételezi, hogy a megjelenített értékeken kívül nincs más érték, és ezekben a cellákban nincs üres cella. Úgy működik, hogy megszámolja a feltöltött cellák teljes számát, és ennek megfelelően eltolja az A1 cellát.
Például ebben az esetben 8 érték van, tehát a COUNT (A: A) 8. értéket ad vissza. Az A1 cella 7 -el eltolása, hogy megkapjuk az utolsó értéket.
2. példa - Dinamikus legördülő menü létrehozása
Az 1. példában szereplő koncepcióműsorokat kiterjesztheti dinamikus elnevezett tartományok létrehozására. Ezek akkor lehetnek hasznosak, ha a megnevezett tartományokat képletekben használja, vagy legördülő listákat hoz létre, és valószínűleg hozzáadja/törli az adatokat a hivatkozott tartományból.
Íme egy példa:
Vegye figyelembe, hogy a legördülő menü automatikusan beállítja az év hozzáadását vagy eltávolítását.
Ez történik, mivel a legördülő menü létrehozásához használt képlet dinamikus, és azonosítja az esetleges hozzáadásokat vagy törléseket, és ennek megfelelően módosítja a tartományt.
Ezt a következőképpen teheti meg:
- Válassza ki azt a cellát, ahová be szeretné illeszteni a legördülő menüt.
- Lépjen az Adatok -> Adateszközök -> Adatellenőrzés menüpontra.
- Az Adatok érvényesítése párbeszédpanel Beállítások lapján válassza a Lista lehetőséget a legördülő menüből.
- A forrásba írja be a következő képletet: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Kattintson az OK gombra.
Nézzük meg, hogyan működik ez a képlet:
- Az OFFSET függvény első három argumentuma A1, 0 és 0. Ez lényegében azt jelenti, hogy ugyanazt a hivatkozási cellát adja vissza (amely A1).
- A negyedik argumentum a magasságra vonatkozik, és itt a COUNT függvény a lista összes elemét adja vissza. Feltételezi, hogy nincs üres rész a listában.
- Az ötödik argumentum az 1, ami azt jelzi, hogy az oszlop szélességének egynek kell lennie.
További megjegyzések:
- Az OFFSET illékony funkció (óvatosan használja).
- Újraszámítja, amikor az Excel munkafüzet nyitva van, vagy amikor a számítás elindul a munkalapon. Ez megnövelheti a feldolgozási időt és lelassíthatja a munkafüzetet.
- Ha a magasság vagy szélesség értéket kihagyja, akkor azt a referencia értékének kell tekinteni.
- Ha sorok és cols negatív számok, akkor az eltolás iránya megfordul.
Excel OFFSET funkció alternatívái
Az Excel OFFSET funkció néhány korlátozása miatt sokan szeretnének alternatívákat fontolóra venni:
- INDEX függvény: Az INDEX függvény cellahivatkozás visszaadására is használható. Kattintson ide, ha példát szeretne látni arra, hogyan hozhat létre dinamikus elnevezésű tartományt az INDEX funkció használatával.
- Excel táblázat: Ha strukturált hivatkozásokat használ az Excel táblázatban, nem kell aggódnia az új adatok hozzáadása és a képletek módosítása miatt.
Excel OFFSET funkció - Videó bemutató
- Excel VLOOKUP funkció.
- Excel HLOOKUP függvény.
- Excel INDEX függvény.
- Excel INDIRECT függvény.
- Excel MATCH függvény.
