V tomto článku sa naučíme vracať SUMOVÚ cenu cez pracovné dni v programe Excel.
Jednoducho povedané, pri práci s údajmi naplnenými cenou. Niekedy musíme nájsť súčet hodnôt s podmienkou. Podmienkou je získať súčet cenových hodnôt v oddelených stĺpcoch.
V tomto článku budeme potrebovať nasledujúce funkcie:
- Funkcia SUMPRODUCT
- COLUMN (funkcia)
- Funkcia MOD
Teraz z týchto funkcií vytvoríme vzorec. Tu dostaneme zoznam hodnôt a potrebujeme získať súčet hodnôt uvedených v každom násobku daného n -tého stĺpca.
Použite vzorec:
= SUMPRODUCT ( - (MOD (COLUMN (zoznam) - COLUMN (prvý) + 1, n) = 0), zoznam)
zoznam : zoznam hodnôt v stĺpci
najprv : prvá bunka zoznamu, ktorá vracia číslo stĺpca
n : hodnoty n -tého stĺpca, ktoré sa majú sčítať.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu sme uviedli údaje obsahujúce čísla. Tu musíme nájsť SÚČET čísel v každom násobku 3. stĺpca. Preto sme priradili zoznamy v stĺpci pre vzorec.
Teraz použijeme nasledujúci vzorec na získanie SÚčtu čísel na 3., 6., 9., 12. mieste a tak ďalej, kým sa zoznam neskončí.
Vzorec:
= SUMPRODUCT ( - (MOD (COLUMN (zoznam) - COLUMN (C5) + 1, P5) = 0), zoznam)
zoznam: zoznam hodnôt v stĺpci uvedený ako pomocou pomenovaných rozsahov.
C5: prvá bunka zoznamu, ktorá vracia číslo stĺpca uvedené ako odkaz na bunku
P5: hodnota n uvedená ako referencia bunky
Vysvetlenie:
- MOD (COLUMN (zoznam) -COLUMN (C5)+1, P5) vráti relatívne číslo stĺpca pre každý n interval. Páči sa mi to
{ 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 , 2 , 0 , 1 }
- Vyššie uvedenému poľu bude priradená hodnota 0 a vráti pole True & False.
{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE}
- Funkcia SUMPRODUCT zohľadňuje hodnotu 1 pre každú prijatú PRAVDU hodnotu a 0 pre NEPRAVDU hodnotu, ako je uvedené nižšie.
= 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})
- - zmení TRUE na 1 a FALSE na 0.
- Hodnoty zodpovedajúce 1 s sa sčítajú iba podľa vyššie uvedeného vzorca.
- Teraz funkcia SUMPRODUCT sčíta všetky hodnoty, ako je uvedené nižšie.
= SUMPRODUCT ({0, 0, 51, 0, 0, 30, 0, 0, 34, 0, 0, 56, 0})
SÚčet hodnôt {51 + 30 + 34 + 56} musí vrátiť 171.
Tu je pole pre funkciu uvedené ako pomenovaný rozsah a bunka ako odkaz na bunku. Výsledok získate stlačením klávesu Enter.
Ako môžete vidieť na vyššie uvedenom snímku, súčet hodnôt extrahovaných v každom 3. intervale vráti 171. Hodnoty sú označené pod červenými políčkami, ako je znázornené na obrázku vyššie.
Alebo môžete použiť pole zoznamu podľa nasledujúceho vzorca:
= SUMPRODUCT ( - (MOD (STĹPEC (C5: O5) - COLUMN (C5) + 1, P5) = 0), C5: O5)
Vyššie uvedený vzorec vráti rovnaký výsledok. Existuje iba jeden náhradný zoznam s názvom rozsah je nahradený rozsahom polí (C5: O5).
Ako vidíte, vzorec funguje dobre. Teraz musíme skopírovať vzorec pre ostatné zoznamy. Použite teda CTRL + D alebo v Exceli potiahnite nadol.
Ako vidíte z vyššie uvedeného vzorca, môžete získať podmienené hodnoty.
Poznámky:
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú dĺžku, inak vzorec vráti chybu.
Dúfam, že tento článok o tom, ako vrátiť súčet iba do každého n -tého stĺpca so vzorcami v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o funkciách SUMPRODUCT. Podeľte sa o svoj dotaz nižšie v poli pre komentáre. Pomôžeme vám.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
SUM, ak je dátum medzi : Vráti SÚčet hodnôt medzi danými dátumami alebo obdobím v programe Excel.
Suma, ak je dátum väčší ako daný dátum: Vráti SÚčet hodnôt po danom dátume alebo období v programe Excel.
2 spôsoby súčtu podľa mesiacov v programe Excel: Vráti SÚčet hodnôt v rámci daného konkrétneho mesiaca v programe Excel.
Ako sčítať viac stĺpcov s podmienkou: Vráti SÚčet hodnôt vo viacerých stĺpcoch s podmienkou v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
V programe Excel 2016 preveďte palce na nohy a palce
Pripojte meno a priezvisko v Exceli
Spočítajte bunky, ktoré sa zhodujú s A alebo B
