Pokud váš excelový list obsahuje výpočty, které jsou založeny na měnícím se rozsahu buněk, použijte funkce SUM a OFFSET společně ve vzorci SUM OFFSET pro zjednodušení úlohy udržovat výpočty aktuální. Pokyny v tomto článku platí pro Excel pro Microsoft 365, Excel 2019, Excel 2016, Excel 2013 a Excel 2010.
Vytvořte dynamický rozsah pomocí funkcí SUMA a OFFSET
Pokud používáte výpočty pro časové období, které se neustále mění – například určování prodeje za měsíc – použijte funkci OFFSET v aplikaci Excel k nastavení dynamického rozsahu, který se mění, když se přidávají údaje o prodeji každého dne. Sama o sobě může funkce SUM obvykle pojmout vkládání nových buněk dat do sčítaného rozsahu. Jedna výjimka nastane, když jsou data vložena do buňky, kde je funkce aktuálně umístěna. V níže uvedeném příkladu jsou nové údaje o prodeji pro každý den přidány do dolní části seznamu, což nutí součet, aby se při přidávání nových údajů neustále posouval dolů o jednu buňku. Chcete-li pokračovat v tomto kurzu, otevřete prázdný list aplikace Excel a zadejte ukázková data. Váš list nemusí být formátován jako v příkladu, ale nezapomeňte zadat data do stejných buněk.
Pokud se k součtu dat použije pouze funkce SUM, bude třeba při každém přidání nových dat upravit rozsah buněk použitých jako argument funkce. Při společném použití funkcí SUM a OFFSET se rozsah, který je sečtený, stává dynamickým a mění se tak, aby vyhovoval novým buňkám dat. Přidání nových buněk dat nezpůsobuje problémy, protože rozsah se při přidávání každé nové buňky nadále upravuje.
Syntaxe a argumenty
V tomto vzorci se funkce SUM používá k sečtení rozsahu dat poskytnutých jako argument. Počáteční bod pro tento rozsah je statický a je identifikován jako odkaz na buňku prvního čísla, které má být sečteno podle vzorce. Funkce OFFSET je vnořená uvnitř funkce SUMA a vytváří dynamický koncový bod rozsahu dat součtu vzorce. Toho lze dosáhnout nastavením koncového bodu rozsahu na jednu buňku nad umístěním vzorce. Syntaxe vzorce je: = SUMA (Začátek rozsahu: OFFSET (reference, řádky, sloupce))
Argumenty jsou:
- Začátek dosahu: Výchozí bod pro rozsah buněk, které budou sečteny funkcí SUMA. V tomto příkladu je výchozím bodem buňka B2.
- Odkaz: Požadovaný odkaz na buňku použitý k výpočtu koncového bodu rozsahu. V příkladu je argument Reference odkazem na buňku vzorce, protože rozsah končí o jednu buňku nad vzorcem.
- Řádky: Je vyžadován počet řádků nad nebo pod argumentem Reference použitým při výpočtu posunutí. Tato hodnota může být kladná, záporná nebo nastavená na nulu. Pokud je umístění odsazení nad argumentem Reference, je hodnota záporná. Pokud je posun níže, argument Řádky je kladný. Pokud je posun umístěn ve stejném řádku, argument je nula. V tomto příkladu začíná offset o jeden řádek nad argumentem Reference, takže hodnota argumentu je záporná (-1).
- Plk: Počet sloupců vlevo nebo vpravo od argumentu Reference použitého k výpočtu posunutí. Tato hodnota může být kladná, záporná nebo nastavená na nulu. Pokud je umístění odsazení nalevo od argumentu Reference, je tato hodnota záporná. Pokud je posun doprava, je Colsův argument kladný. V tomto příkladu jsou součtovaná data ve stejném sloupci jako vzorec, takže hodnota tohoto argumentu je nula.
K výpočtu celkových údajů o prodeji použijte vzorec SUM OFFSET
Tento příklad používá vzorec SUM OFFSET k vrácení součtu denních prodejních čísel uvedených ve sloupci B listu. Zpočátku byl vzorec zadán do buňky B6 a obsahoval údaje o prodeji za čtyři dny. Dalším krokem je přesunutí vzorce SUM OFFSET o jeden řádek dolů, aby se vytvořil prostor pro celkový prodej za pátý den. Toho je dosaženo vložením nového řádku 6, který přesune vzorec na řádek 7. Výsledkem přesunu je, že Excel automaticky aktualizuje argument Reference na buňku B7 a přidá buňku B6 do rozsahu sečteného vzorcem.
-
Vyberte buňku B6, což je místo, kde se původně zobrazí výsledky vzorce.
-
Vybrat Vzorce záložka pásu karet.
-
Vybrat Math & Trig.
-
Vybrat SOUČET.
-
V Argumenty funkcí v dialogovém okně umístěte kurzor do Číslo 1 Textové pole.
-
V listu vyberte buňku B2 zadejte tento odkaz na buňku v dialogovém okně. Toto umístění je statickým koncovým bodem vzorce.
-
V Argumenty funkcí v dialogovém okně umístěte kurzor do Číslo 2 Textové pole.
-
Enter OFFSET (B6, -1,0). Tato funkce OFFSET tvoří dynamický koncový bod vzorce.
-
Vybrat OK pro dokončení funkce a zavření dialogového okna. Celkem se objeví v buňce B6.
Přidejte údaje o prodeji z následujícího dne
Přidání údajů o prodeji z následujícího dne:
-
Klikněte pravým tlačítkem na záhlaví řádku pro řádek 6.
-
Vybrat Vložit pro vložení nového řádku do listu. Vzorec SUM OFFSET se posune o jeden řádek dolů do buňky B7 a řádek 6 je nyní prázdný.
-
Vyberte buňku A6 a zadejte číslo 5 k označení, že se zadává celkový prodej za pátý den.
-
Vyberte buňku B6, vstoupit 1458,25 $, poté stiskněte Enter.
-
Buňka B7 se aktualizuje na nový celkem 7137,40 USD.
Když vyberete buňku B7, aktualizovaný vzorec se zobrazí v řádku vzorců. = SUM (B2: OFFSET (B7, -1,0))
Funkce OFFSET má dva volitelné argumenty: Výška a Šířka, které nebyly použity v tomto příkladu. Tyto argumenty informují funkci OFFSET o tvaru výstupu z hlediska počtu řádků a sloupců. Vynecháním těchto argumentů funkce místo toho použije výšku a šířku argumentu Reference, což je v tomto příkladu jeden řádek vysoký a jeden sloupec široký.