V tomto článku se naučíme vracet SUMU pouze ze vzorců v Excelu.
Jednoduše řečeno, při práci s částečně vyplněnými údaji. Někdy musíme najít součet hodnoty s podmínkou. Podmínkou je získat součet, kde jsou hodnoty extrahovány pouze pomocí vzorců.
Pro tento článek budeme potřebovat použití následujících funkcí:
- Funkce SUMPRODUCT
- Funkce ISFORMULA
Funkce SUMPRODUCT je v Excelu matematická funkce. Funguje na více rozsahů. Znásobí odpovídající pole a poté je přidá.
Celkem = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Syntax:
| = SUMPRODUCT (pole1, [pole2],…) |
pole: Je to rozsah nebo seznam hodnot.
Funkce ISFORMULA vrací hodnotu TRUE, pokud vzorec existuje v odkazu na buňku, a vrací hodnotu FALSE.
Syntax:
| = ISFORMULA (reference) |
odkaz: odkaz na buňku, kde zkontrolovat vzorec
Nyní z těchto funkcí vytvoříme vzorec. Zde poskytneme data a potřebovali jsme součet výsledků, kde je použit vzorec.
Použijte vzorec:
| = SUMPRODUCT (pole * ISFORMULA (pole)) |
Vysvětlení:
- Funkce ISFORMULA vrací TRUE & FALSE na základě stavu kontrolovaného na buňkách.
- Funkce SUMPRODUCT zohledňuje hodnotu 1 pro každou hodnotu PRAVDA a 0 pro hodnotu NEPRAVDA.
- Součin odebraný mezi odpovídajícími hodnotami bude ignorovat NEPRAVDIVÉ hodnoty, protože hodnoty jsou vynásobeny 0 s. SUM získávají pouze SKUTEČNÉ hodnoty, protože hodnoty se násobí 1 s.
Příklad:
Vyzkoušejte tento vzorec spuštěním na příkladu
Zde máme data s cenou zrn extrahovanou součinem Množství do jeho jednotkové ceny a hodnoty některých cen se zadávají ručně. Pokud tedy potřebuji najít součet cen pro hodnotu, kde vzorec extrahoval celkovou cenu.
Nyní použijeme níže uvedený vzorec k získání SUMU
Vzorec:
| = SUMPRODUCT (E2: E15 * ISFORMULA (E2: E15)) |
Vysvětlení:
- Funkce ISFORMULA vrací hodnotu TRUE & FALSE na základě kontrolované podmínky v buňkách pole od E2 do E15.
- Funkce SUMPRODUCT zohledňuje hodnotu 1 pro každou přijatou SKUTEČNOU hodnotu a 0 pro NEPRAVDU hodnotu, jak je uvedeno níže.
= SUMPRODUKT (E2: E15*
{ SKUTEČNÝ; NEPRAVDIVÉ; NEPRAVDIVÉ; NEPRAVDIVÉ; SKUTEČNÝ; SKUTEČNÝ; NEPRAVDIVÉ; SKUTEČNÝ; NEPRAVDIVÉ; SKUTEČNÝ; SKUTEČNÝ; SKUTEČNÝ; SKUTEČNÝ; SKUTEČNÝ } )
- Součin odebraný mezi odpovídajícími hodnotami bude ignorovat NEPRAVDIVÉ hodnoty, protože hodnoty jsou vynásobeny 0 s. SUM získávají pouze SKUTEČNÉ hodnoty, protože hodnoty jsou vynásobeny 1 s, jak je uvedeno níže.
= SUMPRODUCT ({58,41; 0; 0; 0; 82,84; 95,58; 0; 90,27; 0; 37,8; 78,48; 57,97; 97,72; 77,88}
Zde je pole funkce uvedeno 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 tam, kde vzorec existuje.
Pokud potřebujete najít součet hodnot, které nemají vzorec, použijte funkci NOT s funkcí ISFORMULA.
Použijte vzorec:
| = SUMPRODUCT (E2: E15 * NOT (ISFORMULA (E2: E15))) |
Použijte vzorec a získejte hodnotu podle obrázku níže.
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 funkce vrátí chybu.
Doufám, že tento článek o tom, jak vrátit SUMU pouze ze vzorců v aplikaci Excel, 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
Jak odebrat text v aplikaci Excel od pozice
Ověření textových záznamů
Vytvořte rozevírací seznam v Excelu s barvami
Odeberte z textu v aplikaci Excel úvodní a koncové mezery
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