V tomto článku sa naučíme vrátiť SUMU iba zo vzorcov v programe Excel.
Jednoducho povedané, pri práci s čiastočne vyplnenými údajmi. Niekedy musíme nájsť súčet hodnôt s podmienkou. Podmienkou je získať súčet, kde sú hodnoty extrahované iba pomocou vzorcov.
V tomto článku budeme potrebovať nasledujúce funkcie:
- Funkcia SUMPRODUCT
- Funkcia ISFORMULA
Funkcia SUMPRODUCT je matematická funkcia v programe Excel. Funguje na viacerých rozsahoch. Znásobí zodpovedajúce polia a potom ich pridá.
Spolu = (A1 * B1 * C1 *… + A2 * B2 * C2 * … + …)
Syntax:
| = SUMPRODUCT (pole1, [pole2], …) |
pole: Je to rozsah alebo zoznam hodnôt.
Funkcia ISFORMULA vracia hodnotu TRUE, ak vzorec existuje v odkaze na bunku, a v opačnom prípade vracia hodnotu FALSE.
Syntax:
| = ISFORMULA (odkaz) |
odkaz: odkaz na bunku, kde sa má skontrolovať vzorec
Teraz z týchto funkcií vytvoríme vzorec. Tu poskytneme údaje a potrebovali sme súčet výsledkov, kde sa používa vzorec.
Použite vzorec:
| = SUMPRODUCT (pole * ISFORMULA (pole)) |
Vysvetlenie:
- Funkcia ISFORMULA vracia hodnotu TRUE & FALSE na základe stavu, ktorý sa kontroluje v bunkách.
- Funkcia SUMPRODUCT zvažuje hodnotu 1 pre každú PRAVDU hodnotu a 0 pre NEPRAVDU.
- Produkt prevzatý medzi zodpovedajúcimi hodnotami bude ignorovať NEPRAVDIVÉ hodnoty, pretože hodnoty sa vynásobia 0 s. SUMA získavajú iba SKUTOČNÉ hodnoty, pretože hodnoty sa vynásobia 1 s.
Príklad:
Skúsme tento vzorec spustiť na príklade
Tu máme údaje, v ktorých je Cena zŕn extrahovaná súčinom Množstva do jeho Jednotkovej ceny a Niektoré hodnoty ceny sú zadané ručne. Ak teda potrebujem nájsť súčet ceny pre hodnotu, z ktorej vzorec extrahoval celkovú cenu.
Teraz použijeme nižšie uvedený vzorec na získanie SUMU
Vzorec:
| = SUMPRODUCT (E2: E15 * ISFORMULA (E2: E15)) |
Vysvetlenie:
- Funkcia ISFORMULA vracia hodnotu TRUE & FALSE na základe stavu, ktorý sa kontroluje v bunkách poľa od E2 do E15.
- Funkcia SUMPRODUCT zohľadňuje hodnotu 1 pre každú prijatú PRAVDU hodnotu a 0 pre NEPRAVDU hodnotu, ako je uvedené nižšie.
= SUMPRODUCT (E2: E15*
{PRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA; PRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; PRAVDA; PRAVDA; PRAVDA; PRAVDA; TRUE})
- Produkt prevzatý medzi zodpovedajúcimi hodnotami bude ignorovať NEPRAVDIVÉ hodnoty, pretože hodnoty sa vynásobia 0 s. SUMA získavajú iba SKUTOČNÉ hodnoty, pretože hodnoty sa vynásobia 1 s, ako je uvedené nižšie.
= SUMPRODUCT ({58,41; 0; 0; 0; 82,84; 95,58; 0; 90,27; 0; 37,8; 78,48; 57,97; 97,72; 77,88}
Tu je pole pre funkciu uvedené ako odkaz na bunku. Výsledok získate stlačením klávesu Enter.
Ako vidíte na vyššie uvedenom obrázku, súčet hodnôt extrahovaných tam, kde vzorec existuje.
Ak potrebujete nájsť súčet hodnôt, ktoré nemajú vzorec, použite funkciu NOT s funkciou ISFORMULA.
Použite vzorec:
| = SUMPRODUCT (E2: E15 * NOT (ISFORMULA (E2: E15))) |
Použite vzorec a získajte hodnotu podľa obrázku na obrázku nižšie.
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 funkcia vráti chybu.
Dúfam, že tento článok o tom, ako vrátiť súčet iba zo vzorcov 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
Ako odstrániť text v programe Excel z pozície
Overovanie textových záznamov
Vytvorte rozbaľovací zoznam v Exceli s farbami
Odstráňte úvodné a koncové medzery z textu 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
