Excel SUM a OFFSET Formula

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

Pokud používáte výpočty po určitou dobu, která se neustále mění - například celkový prodej za měsíc - funkce OFFSET v aplikaci Excel vám umožňuje nastavit dynamický rozsah, který se mění podle údajů o prodeji každého dne.

Funkce SUM sama o sobě může obvykle zahrnout vložení nových buněk dat 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.

Ve výše uvedeném příkladu obrázku jsou nové údaje o prodeji za každý den přidány ve spodní části seznamu, čímž se celkový počet neustále posune dolů o jednu buňku při každém přidání nových dat.

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 výše, 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))

• Rozsah Start: Je zapotřebí 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: Požadovaná referenční buňka použitá pro výpočet koncového bodu rozsahu je umístěna 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 chcete, aby rozsah ukončil jednu buňku nad vzorec.
• Řádky: Počet řádků nad nebo pod referenčním argumentem používaný při výpočtu posunutí je požadován. Tato hodnota může být kladná, záporná nebo nastavena na nulu. Pokud je poloha offsetu vyšší než Odkaz argument je hodnota záporná. Pokud je níže, argument Řádek je kladný. Pokud je offset umístěn ve stejném řádku, argument je nulový. V tomto příkladu začíná odsazení jeden řádek nad referenčním argumentem, takže hodnota tohoto argumentu je záporná (-1).
• Cols: Počet sloupců vlevo nebo vpravo od referenčního argumentu použitého pro výpočet posunutí je požadován. 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 na buňkuB7 a přidá buňkuB6 na rozsah sčítaný vzorec.

Zadání formuláře SUM OFFSET

1. Klikněte na buňku B6, která je místem, kde se zpočátku zobrazí výsledky vzorce.
2. Klikněte na tlačítkoVzorce kartu nabídky pásu.
3. VybratMath & Trigz pásu k otevření rozevíracího seznamu funkcí.
4. Klikněte naSOUČET v seznamu otevřete dialogové okno funkce.
5. V dialogovém okně klikněte na tlačítkoČíslo 1řádek.
6. Klikněte na buňku B2 zadejte tento odkaz do dialogového okna. Toto umístění je statický koncový bod pro vzorec.
7. V dialogovém okně klikněte na tlačítkoČíslo 2řádek.
8. Zadejte následující funkci OFFSET: OFFSET (B6, -1,0) pro vytvoření dynamického koncového bodu pro vzorec.
9. Klikněte na tlačítko OK dokončete funkci a zavřete dialogové okno.

Celková hodnota se zobrazí v buňce B7.

Přidání údajů o prodeji následujícího dne

Přidání údajů o prodeji následujícího dne:

1. Klepnutím pravým tlačítkem na záhlaví řádku v řádku 6 otevřete kontextovou nabídku.
2. V nabídce klikněte naVložit vložit nový řádek do listu.
3. V důsledku toho se vzorec SUM OFFSET přesune dolů do buňky B7 a řádek 6 je nyní prázdný.
4. Klikněte na buňku A6.
5. Zadejte číslo5 aby bylo uvedeno, že celkový objem prodeje za pátý den je zadán.
6. Klikněte na buňku B6.
7. Zadejte číslo$1458.25 a stiskněte tlačítkoZadejte na klávesnici.

B7 aktualizuje nový součet$7137.40.

Po klepnutí na buňku B7 aktualizovaný vzorec= SUM (B2: OFFSET (B7, -1,0)) se zobrazí v řádku vzorců.

Funkce OFFSET má dva volitelné argumenty: Výška a šířka , které nebyly v tomto příkladu použity.

Tyto argumenty mohou být použity k vyprávění funkce OFFSET tvaru výstupu z hlediska tolika řádků vysokých a tolika 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ý.