Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel OFFSET funkció az Excelben, hogy referenciaeltolást hozzon létre egy kezdeti cellából.
OFFSET funkció áttekintése
Az OFFSET függvény meghatározott cellahivatkozással indul, és egy cellahivatkozást ad vissza az eredeti hivatkozástól eltolt meghatározott számú sor és oszlop segítségével. A hivatkozások lehetnek egy cella vagy cellatartomány. Az eltolás lehetővé teszi a hivatkozás átméretezését is adott számú sorban/oszlopban.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
IFERROR funkció szintaxisa és bemenetei:
| 1 | = OFFSET (hivatkozás, sorok, oszlopok, magasság, szélesség) |
referencia - A kezdeti cellahivatkozás, amelytől eltolni szeretné.
sorok - Az eltolható sorok száma.
cols - Az eltolható oszlopok száma.
magasság - Választható: Állítsa be a referencia sorainak számát.
szélesség - Választható: Állítsa be a hivatkozás oszlopainak számát.
Mi az OFFSET funkció?
Az OFFSET funkció az egyik legerősebb táblázatkezelő funkció, mivel meglehetősen sokoldalú lehet a létrehozásban. Lehetővé teszi a felhasználó számára, hogy célt vagy tartományt határozzon meg különböző helyzetekben és méretekben.
FIGYELEM: Az OFFSET funkció az egyik illékony funkció. A táblázatkezelés legtöbbször a számítógép csak akkor számítja újra a képletet, ha a bemenetek megváltoztatták értékeiket. Az illékony függvény azonban újraszámítja minden amikor megváltoztatja bármelyik cellát. Óvatosan kell eljárni annak biztosítása érdekében, hogy ne okozzon hosszú újraszámítási időt az illékony funkció túlzott használata vagy az illékony funkció eredményétől függő sok cella miatt.
Példák az alapvető sorokra
Az OFFSET funkció minden használatakor meg kell adnia egy kiindulási pontot vagy horgonyt. Ennek megértéséhez nézzük meg ezt a táblázatot:
A B3 cellában lévő „Bob” -t fogjuk használni rögzítési pontként. Ha szeretnénk megragadni az alatta lévő értéket (Charlie), azt mondanánk, hogy 1 -el szeretnénk eltolni a sort. A képletünk így nézne ki
| 1 | = OFFSET (B3, 1) |
Ha feljebb akarunk lépni, az negatív elmozdulás lenne. Gondolhat erre úgy, hogy a sorok száma csökken, ezért le kell vonni. Így a fenti érték (Ádám) megszerzéséhez írnánk
| 1 | = OFFSET (B2, -1) |
Alapvető oszloppéldák
Folytatva az előző példából származó ötletet, hozzáadunk egy másik oszlopot a táblázatunkhoz.
Ha meg akarjuk ragadni Bobnak a tanárt, használhatjuk a képletet
| 1 | = OFFSET (B2, 0, 1) |
Ebben az esetben azt mondtuk, hogy nulla sort akarunk eltolni (aka ugyanazon a soron maradni), de 1 oszlopot. Az oszlopoknál a pozitív szám azt jelenti, hogy jobbra, míg a negatív számok balra.
OFFSET és MATCH
Tegyük fel, hogy több adatsorral rendelkezett, és lehetőséget akart adni a felhasználónak arra, hogy kiválassza, melyik oszlopból szeretné lekérni az eredményeket. Használhatja az INDEX funkciót, vagy az OFFSET funkciót. Mivel a MATCH visszaadja az érték relatív pozícióját, meg kell győződnünk arról, hogy a rögzítési pont az első lehetséges értékünk bal oldalán található. Vegye figyelembe a következő elrendezést:
A B2 -be ezt a képletet írjuk:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
A MATCH „februárban” fog kinézni a C1: F1 tartományban, és megtalálja a 2 -bennd sejt. Az OFFSET ezután 1 oszlopot eltol a B2 -től jobbra, és megkapja a kívánt 9 -es értéket. Ne feledje, hogy az OFFSET -nek nincs problémája ugyanazt a cellát használni, amely a rögzítési pontként tartalmazza a képletet.
MEGJEGYZÉS: Ez a technika a VLOOKUP vagy a HLOOKUP helyettesítésére használható, ha a keresési tartomány bal/feletti értékét szeretné visszaadni. Ennek az az oka, hogy az OFFSET képes negatív eltolásokra.
OFFSET egy tartomány eléréséhez
Használhatja a 4th és 5th argumentumokat az OFFSET függvényben, hogy egy tartomány helyett egy cellát adjon vissza. Tegyük fel, hogy 3 oszlopot akart összegezni ebben a táblázatban.
| 1 | = ÁTLAG (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
Az F2 -ben olyan diák nevét választottuk ki, akinek le akarjuk kérni az átlagos teszteredményeit. Ehhez a képletet fogjuk használni
| 1 | = ÁTLAG (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
A MATCH megkeresi az A oszlopban a nevünket, és visszaadja a relatív pozíciót, ami példánkban 3. Lássuk, hogyan fogják ezt értékelni. Először is, az OFFSET megy le- 3 sor az A1 -től, és 1 oszlop a jobb az A1 -től. Ezzel a B3 cellába kerülünk.
| 1 | = ÁTLAG (OFFSET (A1, 3, 1, 1, 3)) |
Ezután átméretezzük a tartományt. Az új tartomány bal felső cellája a B3 lesz. 1 sor magas és 3 oszlop magas lesz, így megadjuk a B4: D4 tartományt.
| 1 | = ÁTLAG (OFFSET (A1,3, 1, 1, 3)) |
Ne feledje, hogy bár jogosan helyezhet negatív értékeket az eltolási argumentumokba, a nem-negatív értékeket csak a méretezési argumentumokban használhatja.
A végén az ÁTLAG funkciónk a következőket látja:
| 1 | = ÁTLAG (B4: D4) |
Így megkapjuk a 86,67 -es megoldást
OFFSET dinamikus SUM -mal
Mivel az OFFSET hivatkozás keresésére szolgál, ahelyett, hogy közvetlenül a cellára mutatna, ez a leghasznosabb, ha olyan adatokkal van dolgában, amelyek sorokat adtak hozzá vagy töröltek. Tekintsük az alábbi táblázatot alul a Total összeggel
| 1 | = SUM (B2: B4) |
Ha itt egy SUM alapképletet használtunk volna: "= SUM (B2: B4)", majd beszúrtunk egy új sort Bill rekordjának hozzáadásához, akkor rossz válaszunk lenne
Ehelyett gondoljunk arra, hogyan oldhatjuk meg ezt a Total szemszögéből. Nagyon szeretnénk mindent megragadni a B2 cellától a celláig pont a mi összlétszámunk fölött. Ezt úgy írhatjuk be egy képletbe, hogy -1 sor eltolást végzünk. Így ezt használjuk a B5 cella összesített képletének:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Ez a képlet azt teszi, amit leírtunk: kezdje a B2 -től, és menjen 1 cellára a teljes sejt felett. Láthatja, hogy Bill adatainak hozzáadása után a teljes összeg megfelelően frissül.
OFFSET az utolsó N elem beszerzéséhez
Tegyük fel, hogy havi eladásokat rögzít, de szeretné látni az elmúlt 3 hónapot. Ahelyett, hogy kézzel kellene frissítenie képleteit, hogy az új adatok hozzáadása során folyamatosan módosuljon, használja az OFFSET funkciót a COUNT használatával.
Már bemutattuk, hogyan használhatja az OFFSET -et egy sejtvonal megragadásához. Annak meghatározásához, hogy hány cellát kell eltolnunk, a COUNT segítségével határozzuk meg, hogy hányat számokat a B. oszlopban vannak. Nézzük meg a mintatáblázatunkat.
| 1 | = ÖSSZES (ELTOLÁS ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Ha a B1 -nél kezdenénk, és eltolnánk 4 sort (a B oszlopban lévő számok száma), akkor a tartományunk alján, B5 -ben végeznénk. Mivel azonban az OFFSET nem képes negatív értékkel átméretezni, bizonyos módosításokat kell végrehajtanunk, hogy a B3 -ba kerüljünk. Ennek általános egyenlete lesz
| 1 | SZÁM (…) - N + 1 |
Megszámoljuk az egész oszlop számát, kivonunk bármennyit is vissza szeretnénk adni (mivel átméretezzük, hogy megragadjuk őket), majd hozzáadunk 1 -et (mivel lényegében a nulláról kezdjük az eltolást).
Itt láthatja, hogy beállítottunk egy tartományt az elmúlt N hónap összegének, átlagának és max. Az E1 -ben a 3. értéket adtuk meg. Az E2 -ben a képletünk
| 1 | = ÖSSZES (ELTOLÁS ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
A kiemelt szakasz az általános egyenletünk, amelyet most tárgyaltunk. Nincs szükségünk oszlopok eltolására. Ezután átméretezzük a tartományt, hogy 3 cella magas legyen (az E1 érték határozza meg) és 1 oszlop széles legyen. A SUM ezt a tartományt veszi, és 1850 dolláros eredményt ad nekünk. Azt is megmutattuk, hogy kiszámíthatja ugyanezen tartomány maximális értékeinek átlagát, ha egyszerűen átkapcsolja a külső függvényt a SUM -ról a kívánt helyzetre.
OFFSET dinamikus érvényesítési listák
A legutóbbi példában bemutatott technikával felépíthetünk elnevezett tartományokat is, amelyek felhasználhatók az adatok érvényesítésében vagy diagramokban. Ez hasznos lehet, ha táblázatot szeretne beállítani, de azt várja, hogy listáink/adataink mérete megváltozik. Tegyük fel, hogy üzletünk elkezd gyümölcsöt árulni, és jelenleg 3 választási lehetőségünk van.
Ahhoz, hogy egy adatellenőrzési legördülő menüt készítsünk, amelyet máshol is használhatunk, definiáljuk a MyFruit nevű tartományt
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
COUNT helyett COUNTA -t használunk, mivel szöveges értékekkel foglalkozunk. Emiatt azonban a COUNTA eggyel magasabb lesz, mivel megszámolja a fejléc -cellát az A1 -ben és 4 -es értéket ad. Ha 4 sorral eltoljuk, akkor az A5 cellába kerülünk, ami üres. Ennek beállításához vonjuk le az 1 -et.
Most, hogy megvan a Named Range beállítása, beállíthatunk néhány adatellenőrzést a C4 cellában egy Lista típus használatával, forrással:
| 1 | = MyFruit |
Vegye figyelembe, hogy a legördülő menüben csak a három aktuális tétel látható. Ha ezután további elemeket adunk hozzá a listánkhoz, és visszamegyünk a legördülő menübe, a lista az összes új elemet megjeleníti anélkül, hogy módosítanunk kellene valamelyik képletet.
Figyelmeztetések az OFFSET használatával
Amint azt a cikk elején említettük, az OFFSET egy illékony függvény. Ezt nem fogja észrevenni, ha csak néhány cellában használja, de ha több száz számításba kezd, és gyorsan észreveszi, hogy a számítógép észrevehetően sok időt tölt újraszámítással minden egyes módosításkor .
Ezenkívül, mivel az OFFSET nem nevezi meg közvetlenül a nézett cellákat, más felhasználóknak nehezebb később jönniük, és szükség esetén módosítaniuk kell a képleteket.
Ehelyett célszerű olyan táblázatokat használni (amelyeket az Office 2007 -ben vezettek be), amelyek strukturális hivatkozásokat tesznek lehetővé. Ez segített abban, hogy a felhasználók egyetlen hivatkozást adhassanak, amelynek mérete automatikusan módosul az új adatok hozzáadása vagy törlése során.
A másik lehetőség az OFFSET helyett az erőteljes INDEX funkció. Az INDEX lehetővé teszi az összes dinamikus tartomány létrehozását, amelyeket ebben a cikkben láttunk, anélkül, hogy változékony függvény lenne.
További megjegyzések
Az OFFSET függvény segítségével cellaértéket (vagy cellatartományt) adhat vissza úgy, hogy adott számú sort és oszlopot eltol a kezdő hivatkozásból. Ha csak egyetlen cellát keres, az OFFSET képletek ugyanazt a célt érik el, mint az INDEX képletek, kissé eltérő technikával. Az OFFSET függvény valódi ereje abban rejlik, hogy képes kiválasztani egy cellatartományt egy másik képletben.
Az OFFSET funkció használatakor definiál egy kezdő kezdő cellát vagy cellatartományt. Ezután meg kell adnia, hogy hány sort és oszlopot kell eltolni az eredeti cellától. Átméretezheti a tartományt is; sorok vagy oszlopok hozzáadása vagy kivonása.
Térjen vissza az Excel összes funkciójának listájához
OFFSET a Google Táblázatokban
Az OFFSET funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
