Pokud váš excelový list obsahuje výpočty založené na měnícím se rozsahu buněk, použijte funkce SUM a OFFSET společně ve vzorci SUMA OFFSET, abyste si zjednodušili úkol 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í SUM a OFFSET
Používáte-li výpočty pro časové období, které se neustále mění – jako je určování tržeb za měsíc – použijte funkci OFFSET v Excelu k nastavení dynamického rozsahu, který se mění podle toho, jak se přidávají údaje o prodeji každý den.
Funkce SUM sama o sobě obvykle dokáže pojmout vložení nových buněk dat do rozsahu, který se sčítá. Jedna výjimka nastane, když jsou data vložena do buňky, kde se funkce aktuálně nachází.
V níže uvedeném příkladu jsou nové údaje o prodeji za každý den přidány na konec seznamu, což nutí, aby se součet neustále posouval o jednu buňku dolů pokaždé, když jsou přidána nová data.
Chcete-li pokračovat v tomto tutoriálu, 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, rozsah buněk použitý jako argument funkce by musel být upraven pokaždé, když jsou přidána nová data.
Souhrnným použitím funkcí SUM a OFFSET se celkový rozsah stane dynamickým a změ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 s každou novou buňkou stále upravuje.
Syntaxe a argumenty
V tomto vzorci se funkce SUM používá k sečtení rozsahu dat zadaných jako argument. Počáteční bod pro tento rozsah je statický a je identifikován jako odkaz na buňku na první číslo, které má být sečteno vzorcem.
Funkce OFFSET je vnořena do funkce SUM a vytváří dynamický koncový bod pro rozsah dat sečtených podle vzorce. Toho je dosaženo nastavením koncového bodu rozsahu na jednu buňku nad umístěním vzorce.
Syntaxe vzorce je:
=SUM(Začátek rozsahu:OFFSET(Reference, řádky, sloupce))
Argumenty jsou:
- Začátek rozsahu: Počáteční bod pro rozsah buněk, který bude sečten funkcí SUM. V tomto příkladu je výchozím bodem buňka B2.
- Reference: 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čí jednu buňku nad vzorcem.
- Rows: Je vyžadován počet řádků nad nebo pod argumentem Reference použitým při výpočtu offsetu. Tato hodnota může být kladná, záporná nebo nastavená na nulu. Pokud je umístění posunu nad argumentem Reference, je hodnota záporná. Pokud je posun níže, je argument Řádky kladný. Pokud je posun umístěn ve stejném řádku, je argument nula. V tomto příkladu offset začíná jeden řádek nad argumentem Reference, takže hodnota argumentu je záporná jedna (-1).
- Cols: Počet sloupců vlevo nebo vpravo od argumentu Reference použitého k výpočtu offsetu. Tato hodnota může být kladná, záporná nebo nastavená na nulu. Pokud je umístění posunu vlevo od argumentu Reference, je tato hodnota záporná. Pokud je offset vpravo, argument Cols je kladný. V tomto příkladu jsou sčítaná data ve stejném sloupci jako vzorec, takže hodnota tohoto argumentu je nula.
Použijte vzorec SUM OFFSET pro údaje o celkovém prodeji
Tento příklad používá vzorec SUM OFFSET k vrácení součtu pro denní prodeje uvedené ve sloupci B listu. Zpočátku byl vzorec zadán do buňky B6 a sečetl údaje o prodeji za čtyři dny.
Dalším krokem je přesunout vzorec SUM OFFSET o jeden řádek níže, aby se uvolnilo místo pro celkové prodeje za pátý den. Toho je dosaženo vložením nového řádku 6, který přesune vzorec na řádek 7.
V důsledku přesunu aplikace Excel automaticky aktualizuje argument Reference do buňky B7 a přidá buňku B6 do rozsahu sečteného vzorcem.
- Vyberte buňku B6, což je umístění, kde se zpočátku zobrazí výsledky vzorce.
-
Vyberte kartu Formulas na pásu karet.
Image -
Vyberte Math & Trig.
Image -
Vybrat SUM.
Image - V dialogovém okně Argumenty funkce umístěte kurzor do textového pole Number1.
-
V listu vyberte buňku B2 a zadejte odkaz na tuto buňku do dialogového okna. Toto umístění je statickým koncovým bodem pro vzorec.
Image - V dialogovém okně Argumenty funkce umístěte kurzor do textového pole Number2.
-
Zadejte OFFSET(B6, -1, 0). Tato funkce OFFSET tvoří dynamický koncový bod pro vzorec.
Image -
Výběrem OK dokončete funkci a zavřete dialogové okno. Součet se zobrazí v buňce B6.
Image
Přidat údaje o prodeji následující den
Přidání údajů o prodejích na další den:
- Klikněte pravým tlačítkem na záhlaví řádku 6.
-
Vyberte Vložit pro vložení nového řádku do listu. Vzorec SUM OFFSET se přesune o jeden řádek dolů do buňky B7 a řádek 6 je nyní prázdný.
Image - Vyberte buňku A6 a zadejte číslo 5, abyste označili, že se zadává celková částka prodeje za pátý den.
-
Vyberte buňku B6, zadejte $1458,25 a stiskněte Enter.
Image - Aktualizace buňky B7 na novou celkovou částku 7137,40 $.
Když vyberete buňku B7, aktualizovaný vzorec se zobrazí v řádku vzorců.
=SUMA(B2:POSUN(B7, -1, 0))
Funkce OFFSET má dva volitelné argumenty: Height a Width, které v tomto příkladu nebyly použity. Tyto argumenty sdělují funkci OFFSET tvar 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, který je v tomto příkladu jeden řádek vysoký a jeden sloupec široký.
