V tomto článku se naučíme vracet SUMOVOU cenu do pracovních dnů v Excelu.
Jednoduše řečeno, při práci s cenami vyplněnými daty. Někdy musíme najít součet hodnoty s podmínkou. Podmínkou je získat součet cenových hodnot v oddělených sloupcích.
Pro tento článek budeme potřebovat použití následujících funkcí:
- Funkce SUMPRODUCT
- Funkce COLUMN
- Funkce MOD
Nyní z těchto funkcí vytvoříme vzorec. Zde dostaneme seznam hodnot a potřebujeme získat součet hodnot v každém násobku daného n -tého sloupce.
Použijte vzorec:
= SUMPRODUCT ( - (MOD (COLUMN (seznam) - COLUMN (první) + 1, n) = 0), seznam)
seznam : seznam hodnot podél sloupce
za prvé : první buňka seznamu, která vrací číslo sloupce
n : hodnoty n -tého sloupce k sečtení.
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde jsme poskytli data obsahující čísla. Zde musíme najít součet čísel v každém násobku 3. sloupce. Za tímto účelem jsme přidělili seznamy ve sloupci pro vzorec.
Nyní použijeme níže uvedený vzorec k získání SUMU čísel na 3., 6., 9., 12. a tak dále, dokud seznam nekončí
Vzorec:
= SUMPRODUCT ( - (MOD (COLUMN (seznam) - COLUMN (C5) + 1, P5) = 0), seznam)
seznam: seznam hodnot ve sloupci udaný jako pomocí pojmenovaných rozsahů.
C5: první buňka seznamu, která vrací číslo sloupce uvedené jako odkaz na buňku
P5: hodnota n daná jako reference buňky
Vysvětlení:
- MOD (COLUMN (seznam) -COLUMN (C5)+1, P5) vrací relativní číslo sloupce pro každý n interval. Jako
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Výše uvedené pole bude spojeno s hodnotou 0 a vrátí pole True & False.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- Funkce SUMPRODUCT zohledňuje hodnotu 1 pro každou přijatou SKUTEČNOU hodnotu a 0 pro NEPRAVDU hodnotu, jak je uvedeno níže.
= SUMPRODUCT ( - ({0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0}), {40, 36, 51, 33, 42, 30, 92, 67, 34, 54, 69, 56, 50})
- - převede TRUE na 1 a FALSE na 0.
- Hodnoty odpovídající 1 s se sčítají pouze podle výše uvedeného vzorce.
- Nyní funkce SUMPRODUCT sečte všechny hodnoty, jak je uvedeno níže.
= SUMPRODUCT ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
SUMA hodnot {51 + 30 + 34 + 56} musí vrátit 171.
Zde je pole funkce uvedeno jako pojmenovaný rozsah a buňka jako odkaz na buňku. Stisknutím klávesy Enter získáte výsledek.
Jak vidíte na výše uvedeném snímku, součet hodnot extrahovaných v každém 3. intervalu vrací 171. Hodnoty jsou označeny pod červenými rámečky, jak je znázorněno na výše uvedeném snímku.
Nebo můžete použít pole seznamu pomocí následujícího vzorce:
= SUMPRODUCT ( - (MOD (COLUMN (C5: O5) - COLUMN (C5) + 1, P5) = 0), C5: O5)
Výše uvedený vzorec vrátí stejný výsledek. Existuje pouze jeden náhradní seznam pojmenovaný rozsah je nahrazen rozsahem pole (C5: O5).
Jak vidíte, vzorec funguje dobře. Nyní musíme zkopírovat vzorec pro ostatní seznamy. Použijte tedy CTRL + D nebo přetáhněte možnost Excel.
Jak vidíte z výše uvedeného vzorce, můžete získat podmíněné hodnoty.
Poznámky:
- Funkce SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
- Pole argumentů musí mít stejnou délku, jinak vzorec vrátí chybu.
Doufám, že tento článek o tom, jak vrátit SUM pouze pro každý n -tý sloupec se vzorci v Excelu, je vysvětlující. Další články o funkcích SUMPRODUCT najdete zde. Sdílejte prosím svůj dotaz níže v poli pro komentáře. Pomůžeme vám.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
SUM, pokud je datum mezi : Vrátí SUMU hodnot mezi danými daty nebo obdobím v Excelu.
Součet, pokud je datum větší než dané datum: Vrátí SUMU hodnot po daném datu nebo období v aplikaci Excel.
2 způsoby součtu podle měsíce v Excelu: Vrátí SUMU hodnot v rámci daného konkrétního měsíce v aplikaci Excel.
Jak sečíst více sloupců s podmínkou: Vrátí SUMU hodnot napříč více sloupci s podmínkou v Excelu
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity
Upravte rozevírací seznam
Absolutní reference v Excelu
Pokud s podmíněným formátováním
Pokud se zástupnými znaky
Vlookup podle data
Převod palců na stopy a palce v aplikaci Excel 2016
Připojte jméno a příjmení v Excelu
Počítejte buňky, které odpovídají A nebo B
