Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Funkce Excel OFFSET v aplikaci Excel vytvořit referenční posun od počáteční buňky.
Přehled funkcí OFFSET
Funkce OFFSET začíná definovanou referencí buňky a vrací odkazu buňky specifikovaný počet řádků a sloupců odsazených od původního rozhodčího. Reference mohou být jedna buňka nebo řada buněk. Offset také umožňuje změnit velikost odkazu na daný počet řádků/sloupců.
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a vstupy funkce IFERROR:
| 1 | = OFFSET (reference, řádky, sloupce, výška, šířka) |
odkaz - Počáteční odkaz na buňku, od kterého chcete kompenzovat.
řádky - Počet řádků k odsazení.
cols - Počet sloupců k odsazení.
výška - VOLITELNÉ: Upravte počet řádků v odkazu.
šířka - VOLITELNÉ: Upravte počet sloupců v odkazu.
Co je funkce OFFSET?
Funkce OFFSET je jednou z výkonnějších tabulkových funkcí, protože může být velmi univerzální v tom, co vytváří. Poskytuje uživateli možnost definovat buňku nebo rozsah v různých pozicích a velikostech.
UPOZORNĚNÍ: Funkce OFFSET je jednou z nestálých funkcí. Většinu času, kdy pracujete ve své tabulce, počítač přepočítá vzorec pouze v případě, že vstupy změnily své hodnoty. Nestálá funkce se však přepočítává každý čas, kdy provedete změnu v jakékoli buňce. Je třeba dbát opatrnosti, abyste zajistili, že nezpůsobíte velký čas přepočtu kvůli nadměrnému používání volatilní funkce nebo s mnoha buňkami závislými na výsledku volatilní funkce.
Základní řádkové příklady
Při každém použití funkce OFFSET musíte zadat počáteční bod nebo ukotvení. Podívejme se na tuto tabulku, abychom tomu porozuměli:
Jako kotevní bod použijeme „Bob“ v buňce B3. Pokud bychom chtěli chytit hodnotu těsně pod (Charlie), řekli bychom, že chceme posunout řádek o 1. Náš vzorec by vypadal takto
| 1 | = OFFSET (B3, 1) |
Pokud bychom se chtěli posunout nahoru, byl by to negativní posun. Můžete na to myslet, protože počet řádků klesá, takže musíme odečíst. Abychom tedy dostali výše uvedenou hodnotu (Adam), napsali bychom
| 1 | = OFFSET (B2, -1) |
Základní příklady sloupců
Pokračujeme v myšlence z předchozího příkladu a do tabulky přidáme další sloupec.
Pokud bychom chtěli popadnout učitele pro Boba, mohli bychom použít vzorec
| 1 | = OFFSET (B2, 0, 1) |
V tomto případě jsme řekli, že chceme odsadit nulové řádky (aka zůstat na stejném řádku), ale chceme odsadit 1 sloupec. U sloupců znamená kladné číslo posunutí doprava a záporné číslo posunutí doleva.
OFFSET a MATCH
Předpokládejme, že jste měli několik sloupců dat a chtěli jste dát uživateli možnost vybrat si, ze kterého sloupce má načíst výsledky. Můžete použít funkci INDEX, nebo můžete použít OFFSET. Protože MATCH vrátí relativní polohu hodnoty, budeme se muset ujistit, že kotevní bod je nalevo od naší první možné hodnoty. Zvažte následující rozložení:
V B2 napíšeme tento vzorec:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH bude vypadat jako „únor“ v rozmezí C1: F1 a najde jej ve 2nd buňka. OFFSET pak posune 1 sloupec vpravo od B2 a chytí požadovanou hodnotu 9. Všimněte si, že OFFSET nemá problém použít stejnou buňku, která obsahuje vzorec jako kotevní bod.
POZNÁMKA: Tuto techniku lze použít jako náhradu za VLOOKUP nebo HLOOKUP, pokud chcete vrátit hodnotu zleva/nad rozsah vyhledávání. Důvodem je, že OFFSET může provádět negativní offsety.
OFFSET pro získání dosahu
Můžete použít 4th a 5th argumenty ve funkci OFFSET pro vrácení rozsahu, nikoli pouze jedné buňky. Předpokládejme, že jste chtěli v této tabulce sečíst 3 sloupce.
| 1 | = PRŮMĚR (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
Ve F2 jsme vybrali jméno studenta, pro kterého chceme načíst jejich průměrné výsledky testů. K tomu použijeme vzorec
| 1 | = PRŮMĚR (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH prohledá ve sloupci A naše jméno a vrátí relativní pozici, která je v našem případě 3. Uvidíme, jak se to vyhodnotí. Nejprve se chystá OFFSET dolů 3 řádky od A1 a 1 sloupec po že jo z A1. Tím se dostáváme do buňky B3.
| 1 | = PRŮMĚR (OFFSET (A1, 3, 1, 1, 3)) |
Dále změníme rozsah. Nová řada bude mít B3 jako levou horní buňku. Bude 1 řádek vysoký a 3 sloupce vysoký, což nám dává rozsah B4: D4.
| 1 | = PRŮMĚR (OFFSET (A1,3, 1, 1, 3)) |
Všimněte si toho, že i když můžete oprávněně zadat záporné hodnoty do offsetových argumentů, v argumentech pro velikost můžete použít pouze nezáporné hodnoty.
Naše funkce PRŮMĚR nakonec vidí:
| 1 | = PRŮMĚR (B4: D4) |
Dostaneme tedy naše řešení 86,67
OFFSET s dynamickým SUMEM
Protože OFFSET slouží k nalezení odkazu, nikoli přímo na buňku, je nejužitečnější, když pracujete s daty, do kterých byly přidány nebo odstraněny řádky. Zvažte následující tabulku s celkem na dně
| 1 | = SUM (B2: B4) |
Pokud bychom zde použili základní vzorec SUM „= SUM (B2: B4)“ a poté vložili nový řádek pro přidání záznamu pro Billa, dostali bychom špatnou odpověď
Místo toho se podívejme na to, jak to vyřešit z pohledu Total. Opravdu chceme uchopit vše od buňky B2 do buňky těsně nad naším součtem. Způsob, jakým to můžeme zapsat do vzorce, je provést posunutí řádku o -1. Použijeme to tedy jako vzorec pro náš součet v buňce B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Tento vzorec dělá to, co jsme právě popsali: začněte na B2 a přejděte na 1 buňku nad naší celkovou buňku. Můžete vidět, jak se po přidání Billových údajů náš součet aktualizuje správně.
OFFSET k získání posledních N položek
Řekněme, že zaznamenáváte měsíční tržby, ale chcete se podívat na poslední 3 měsíce. Spíše než abyste museli ručně aktualizovat vzorce, aby se při přidávání nových dat stále upravovaly, můžete použít funkci OFFSET s COUNT.
Již jsme ukázali, jak můžete pomocí OFFSETu uchopit řadu buněk. Abychom zjistili, kolik buněk potřebujeme přesunout, použijeme COUNT, abychom zjistili, kolik čísla jsou ve sloupci B. Podívejme se na naši ukázkovou tabulku.
| 1 | = SUMA (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Pokud bychom začínali na B1 a odsazovali 4 řádky (počet čísel ve sloupci B), skončili bychom na konci našeho rozsahu, B5. Protože však OFFSET nemůže změnit velikost se zápornou hodnotou, musíme provést nějaké úpravy, abychom skončili v B3. Obecná rovnice pro to bude stačit
| 1 | COUNT (…) - N + 1 |
Odečteme počet celého sloupce, odečteme, kolik jich chceme vrátit (protože změníme velikost, abychom je chytili), a pak přidáme 1 (protože v podstatě začínáme náš posun na pozici nula).
Zde vidíte, že jsme nastavili rozsah pro získání součtu, průměru a maxima za posledních N měsíců. V E1 jsme zadali hodnotu 3. V E2 je náš vzorec
| 1 | = SUMA (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Zvýrazněná část je naší obecnou rovnicí, o které jsme právě diskutovali. Nepotřebujeme kompenzovat žádné sloupce. Poté změníme velikost rozsahu na 3 buňky na výšku (určeno hodnotou v E1) a na šířku 1 sloupec. Naše SUMA pak vezme tento rozsah a dá nám výsledek 1 850 $. Ukázali jsme také, že můžete vypočítat průměr maxima ze stejného rozsahu jednoduchým přepnutím vnější funkce z SUM na cokoli, co situace vyžaduje.
OFFSET dynamické validační seznamy
Pomocí techniky ukázané v posledním příkladu můžeme také sestavit pojmenované rozsahy, které by mohly být použity při ověřování dat nebo grafech. To může být užitečné, když chcete nastavit tabulku, ale očekáváte, že naše seznamy/data změní velikost. Řekněme, že náš obchod začíná prodávat ovoce a v současné době máme 3 možnosti.
Abychom vytvořili rozevírací seznam Ověření dat, který můžeme použít jinde, definujeme pojmenovaný rozsah MyFruit jako
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Místo COUNT používáme COUNTA, protože máme co do činění s textovými hodnotami. Z tohoto důvodu bude naše COUNTA o jednu vyšší, protože bude počítat buňku záhlaví do A1 a dávat hodnotu 4. Pokud bychom však vyrovnali o 4 řádky, skončili bychom v buňce A5, která je prázdná. Abychom to upravili, odečteme 1.
Nyní, když máme nastavení pojmenovaného rozsahu, můžeme nastavit určité ověření dat v buňce C4 pomocí typu seznamu se zdrojem:
| 1 | = MyFruit |
Rozevírací seznam zobrazuje pouze naše tři aktuální položky. Pokud do seznamu přidáme další položky a vrátíme se do rozevíracího seznamu, seznam zobrazí všechny nové položky, aniž bychom museli měnit některý ze vzorců.
Upozornění při používání OFFSETU
Jak již bylo zmíněno na začátku tohoto článku, OFFSET je nestálá funkce. Toho si nevšimnete, pokud jej používáte jen v několika buňkách, ale pokud ho začnete využívat ve stovkách výpočtů a rychle si všimnete, že váš počítač tráví znatelné množství času přepočítáváním pokaždé, když provedete jakékoli změny .
Navíc, protože OFFSET nepojmenovává přímo buňky, na které se dívá, je pro ostatní uživatele těžší přijít později a v případě potřeby změnit vzorce.
Místo toho by bylo vhodné použít tabulky (zavedené v Office 2007), které umožňují strukturální odkazy. To pomohlo uživatelům být schopni poskytnout jedinou referenci, která se automaticky přizpůsobila velikosti, jak byla přidávána nebo odstraňována nová data.
Další možností, kterou použít místo OFFSETu, je výkonná funkce INDEX. INDEX vám umožní sestavit všechny dynamické rozsahy, které jsme viděli v tomto článku, aniž by byl problém volatilní funkce.
Další poznámky
Pomocí funkce OFFSET můžete vrátit hodnotu buňky (nebo rozsah buněk) odsazením daného počtu řádků a sloupců od počáteční reference. Když hledáte pouze jednu buňku, vzorce OFFSET dosahují stejného účelu jako vzorce INDEX, s použitím mírně odlišné techniky. Skutečná síla funkce OFFSET spočívá v její schopnosti vybrat rozsah buněk, které mají být použity v jiném vzorci.
Při použití funkce OFFSET definujete počáteční počáteční buňku nebo rozsah buněk. Potom určíte počet řádků a sloupců, které se mají od počáteční buňky odsadit. Můžete také změnit velikost rozsahu; přidat nebo odečíst řádky nebo sloupce.
Zpět na seznam všech funkcí v Excelu
OFFSET v Tabulkách Google
Funkce OFFSET funguje v Tabulkách Google úplně stejně jako v Excelu:
