Použijte SUM a OFFSET k nalezení součtů pro dynamické rozsahy dat
Pokud list aplikace Excel obsahuje výpočty založené na měnícím se rozsahu buněk, pomocí funkcí SUM a OFFSET dohromady ve formátu SUM OFFSET zjednodušuje úlohu udržovat výpočty aktuální.
Vytvořte dynamický rozsah pomocí funkcí SUM a OFFSET
Používáte-li výpočty po dobu, která se neustále mění - například celkový prodej za měsíc - funkce OFFSET umožňuje nastavit dynamický rozsah, který se stále mění, když se přidávají údaje o prodeji každý den.
Funkce SUM sama o sobě může obvykle zahrnout nové buňky dat, které jsou vloženy do sčítacího rozsahu.
Jedna výjimka nastane, když jsou data vložena do buňky, kde je funkce aktuálně umístěna.
V ukázkovém obrázku, který je připojen k tomuto článku, jsou nové údaje o prodeji za každý den přidány do dolní části seznamu, což nutí celkový počet, aby se při každém přidávání nových dat neustále posunuli dolů o jednu buňku.
Pokud by byla funkce SUM použita sama pro úplnost dat, bylo by nutné upravit rozsah buněk používaných jako argument funkce při každém přidání nových dat.
Použitím funkcí SUM a OFFSET se však celkový rozsah stává dynamickým. Jinými slovy, změní se tak, aby vyhovovaly novým datům. Přidání nových datových buněk nevyvolává problémy, protože rozsah se nadále přizpůsobuje při přidávání nových buněk.
Syntaxe a argumenty
Přečtěte si obrázek, který doprovází tento článek, který bude následovat spolu s tímto výukovým programem.
V tomto vzorci je funkce SUM použita k doplnění rozsahu poskytnutých dat jako jeho argumentu. Počáteční bod pro tento rozsah je statický a je označen jako odkaz buňky na první číslo, které má být dosaženo vzorem.
Funkce OFFSET je vnořena uvnitř funkce SUM a slouží k vytvoření dynamického koncového bodu v rozsahu dat, který je shrnut podle vzorce. Toho je dosaženo nastavením koncového bodu rozsahu na jednu buňku nad umístěním vzorce.
Syntaxe vzorce:
= SUM (rozsah spuštění: OFFSET (reference, řádky, sloupce))Range Start - (vyžaduje) počáteční bod pro rozsah buněk, které budou doplněny funkcí SUM. Na příkladovém obrázku je to buňka B2.
Odkaz - (povinné) odkaz na buňku použitý pro výpočet koncového bodu rozsahu, který se nachází v řadě řádků a sloupců. V ukázkovém obrázku je referenční argument referencím buňky pro vzorec samotný, protože vždy chceme, aby rozsah ukončil jednu buňku nad vzorec.
Řádky - (povinné) počet řádků nad nebo pod referenčním argumentem používaným při výpočtu posunutí. Tato hodnota může být kladná, záporná nebo nastavena na nulu.
Pokud je umístění offsetu nad referenčním argumentem, je tato hodnota záporná. Pokud je níže, argument Řádek je kladný. Pokud je offset umístěn ve stejném řádku, je tento argument nulový. V tomto příkladu začíná odsazení jeden řádek nad referenčním argumentem, takže hodnota tohoto argumentu je záporná (-1).
Sloupce - (povinné) počet sloupců vlevo nebo vpravo od referenčního argumentu použitého při výpočtu posunutí. Tato hodnota může být kladná, záporná nebo nastavena na nulu
Pokud je poloha offsetu vlevo od referenčního argumentu, je tato hodnota záporná. Pokud vpravo je argument Cols pozitivní. V tomto příkladu jsou data, která jsou uvedena ve stejném sloupci jako vzorec, takže hodnota pro tento argument je nula.
Použití vzorce SUM OFFSET na celkové prodejní údaje
Tento příklad používá vzorec SUM OFFSET k vrácení celkového počtu denních údajů o prodeji uvedených ve sloupci B listu.
Zpočátku byl vzorec vložen do buňky B6 a celkový počet prodejních údajů byl uveden na čtyři dny.
Dalším krokem je přesunout vzorec SUM OFFSET dolů do řádku, aby se uvolnil prostor pro celkový prodej za pátý den.
To je dosaženo vložením nového řádku 6, který přesune vzorec dolů do řádku 7.
V důsledku přesunu aplikace Excel automaticky aktualizuje referenční argument do buňky B7 a přidá buňku B6 do rozsahu sčítaného vzorem.
Zadání formuláře SUM OFFSET
- Klikněte na buňku B6, která je místem, kde se zpočátku zobrazí výsledky vzorce.
- Klikněte na kartu Vzorce v nabídce pásu karet .
- Z roletky zvolte Math & Trig a otevřete rozevírací seznam funkcí.
- Klepnutím na SUM v seznamu vyvoláte dialogové okno funkce .
- V dialogovém okně klikněte na řádek Číslo1 .
- Klepnutím na buňku B2 zadejte tento odkaz do dialogového okna. Toto umístění je statický koncový bod pro vzorec;
- V dialogovém okně klikněte na řádek Číslo2 .
- Zadejte následující funkci OFFSET: OFFSET (B6, -1,0) pro vytvoření dynamického koncového bodu pro vzorec.
- Klepnutím na tlačítko OK dokončete funkci a zavřete dialogové okno.
Celkem 5679,15 dolarů se objeví v buňce B7.
Když klepnete na buňku B3, zobrazí se ve sloupci vzorců nad pracovním listem úplná funkce = SUM (B2: OFFSET (B6, -1,0)) .
Přidání údajů o prodeji následujícího dne
Přidání údajů o prodeji následujícího dne:
- Klepnutím pravým tlačítkem na záhlaví řádku v řádku 6 otevřete kontextovou nabídku.
- V nabídce klikněte na Insert pro vložení nového řádku do listu.
- V důsledku toho se vzorec SUM OFFSET přesune dolů do buňky B7 a řádek 6 je nyní prázdný.
- Klikněte na buňku A6 .
- Zadejte číslo 5, které označuje, že se zadává celkový prodej za pátý den.
- Klikněte na buňku B6.
- Zadejte číslo 1458.25 dolarů a stiskněte klávesu Enter na klávesnici.
Buňka B7 aktualizuje nový počet 7137,40 USD.
Po klepnutí na buňku B7 se v řádku vzorců zobrazí aktualizovaný vzorec = SUM (B2: OFFSET (B7, -1,0)) .
Poznámka : Funkce OFFSET má dva volitelné argumenty: Výška a Šířka, které byly v tomto příkladu vynechány.
Tyto argumenty mohou být použity k vyprávění funkce OFFSET tvaru výstupu, pokud jde o tolik řádků vysokých a tolik sloupců širokých.
Vynecháním těchto argumentů funkce ve výchozím nastavení používá místo toho výšku a šířku referenčního argumentu, což je v tomto příkladu vysoký jeden řádek a jeden sloupec široký.