Jak používat funkci Excel OFFSET -

souhrn

Funkce Excel OFFSET vrací odkaz na rozsah vytvořený s pěti vstupy: (1) počáteční bod, (2) posun řádku, (3) posun sloupce, (4) výška v řádcích, (5) šířka v sloupce. OFFSET je užitečný ve vzorcích, které vyžadují dynamický rozsah.

Účel

Vytvořte referenční offset od daného počátečního bodu

Návratová hodnota

Odkaz na buňku.

Syntax

= OFFSET (reference, řádky, sloupky, (výška), (šířka))

Argumenty

  • reference - Výchozí bod, dodávaný jako odkaz na buňku nebo rozsah.
  • řádky - Počet řádků, které mají být posunuty pod počáteční referenci.
  • cols - Počet sloupců, které mají být posunuty napravo od počáteční reference.
  • height - (volitelně) Výška v řádcích vráceného odkazu.
  • width - (volitelně) Šířka ve sloupcích vráceného odkazu.

Verze

Excel 2003

Poznámky k použití

Funkce Excel OFFSET vrací dynamický rozsah vytvořený z pěti vstupů: (1) počáteční bod, (2) posun řádku, (3) posun sloupce, (4) výška v řádcích, (5) šířka ve sloupcích.

Počáteční bod ( referenční argument) může být jedna buňka nebo řada buněk. Argumenty řádky a cols představují počet buněk, které mají být „posunuty“ od počátečního bodu. Argumenty výška a šířka jsou volitelné a určují velikost vytvářeného rozsahu. Když se výška a šířka jsou vynechány, jsou ve výchozím nastavení výšky a šířky reference .

Například pro odkaz na C5 začínající na A1 je odkaz A1, řádky 4 a cols 2:

=OFFSET(A1,4,2) // returns reference to C5

Chcete-li odkazovat na C1: C5 z A1, odkaz je A1, řádky jsou 0, sloupce jsou 2, výška je 5 a šířka je 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Poznámka: Šířku lze vynechat, protože bude mít výchozí hodnotu 1.

Je běžné vidět OFFSET zabalený do jiné funkce, která očekává rozsah. Například na SUM C1: C5, počínaje A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Hlavním účelem OFFSETu je umožnit dynamické přizpůsobení vzorců dostupným datům nebo vstupu uživatele. Funkci OFFSET lze použít k vytvoření dynamického pojmenovaného rozsahu pro grafy nebo kontingenční tabulky, aby byla zdrojová data vždy aktuální.

Poznámka: Dokumentace Excel uvádí, že výška a šířka nemohou být záporné, ale zdá se, že záporné hodnoty fungovaly dobře od počátku 90. let. Funkce OFFSET v Tabulkách Google neumožňuje zápornou hodnotu argumentů výšky nebo šířky.

Příklady

Níže uvedené příklady ukazují, jak lze OFFSET nakonfigurovat tak, aby vracel různé druhy rozsahů. Tyto obrazovky byly pořízeny pomocí aplikace Excel 365, takže funkce OFFSET vrací dynamické pole, pokud je výsledkem více než jedna buňka. Ve starších verzích aplikace Excel můžete použít klávesu F9 ke kontrole výsledků vrácených z OFFSET.

Příklad č. 1

Na obrazovce níže použijeme OFFSET k vrácení třetí hodnoty (březen) ve druhém sloupci (západ). Vzorec v H4 je:

=OFFSET(B3,3,2) // returns D6

Příklad č. 2

Na obrazovce níže použijeme OFFSET k vrácení poslední hodnoty (červen) ve třetím sloupci (sever). Vzorec v H4 je:

=OFFSET(B3,6,3) // returns E9

Příklad č. 3

Níže použijeme OFFSET k vrácení všech hodnot ve třetím sloupci (sever). Vzorec v H4 je:

=OFFSET(B3,1,3,6) // returns E4:E9

Příklad č. 4

Níže použijeme OFFSET k vrácení všech hodnot za květen (pátý řádek). Vzorec v H4 je:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Příklad č. 5

Níže použijeme OFFSET k vrácení hodnoty dubna, května a června pro západní region. Vzorec v H4 je:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Příklad č. 6

Níže použijeme OFFSET k vrácení hodnoty dubna, května a června pro západ a sever. Vzorec v H4 je:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Poznámky

  • OFFSET vrací pouze referenci, žádné buňky nejsou přesunuty.
  • Obě řady a sloupců může být dodáván jako záporná čísla obrátit jejich normální směr odsazení - negativní Sloupce posun vlevo, a negativní řádky kompenzovat výše.
  • OFFSET je „volatilní funkce“ - přepočítá se při každé změně listu. Díky těkavým funkcím mohou větší a složitější sešity běžet pomalu.
  • OFFSET zobrazí #REF! chybová hodnota, pokud je odsazení mimo okraj listu.
  • Když je výška nebo šířka vynechána, použije se výška a šířka odkazu .
  • OFFSET lze použít s jakoukoli jinou funkcí, která očekává přijetí reference.
  • Dokumentace Excel říká, že výška a šířka nemohou být záporné, ale záporné hodnoty fungují.

Související videa

Jak vytvořit dynamický pojmenovaný rozsah pomocí OFFSET V tomto videu se podíváme na to, jak vytvořit dynamický pojmenovaný rozsah pomocí funkce OFFSET, nejběžnější způsob vytvoření rozsahu dynamických jmen pomocí vzorce.

Zajímavé články...