V tomto článku se naučíme SUMA IF pomocí hodnot vyhledávání v aplikaci Excel.
Jednoduše řečeno, při práci s různými datovými tabulkami někdy potřebujeme vypočítat hodnoty v hodnotách založených na první tabulce v jiné tabulce. Podmínkou je získat součet hodnot v první tabulce pomocí hodnot druhé tabulky
Pro tento článek budeme potřebovat použití následujících funkcí:
- Funkce SUMPRODUCT
- Funkce SUMIF
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 SUMIF v aplikaci Excel vrací SUMA čísel splňujících zadaná kritéria. Rozsah SUM a rozsah kritérií mohou být stejné nebo různé.
Obecný vzorec Excel SUMIF:
= SUMIF (rozsah_kritérií, rozsah, rozsah_součtu)
rozsah_kritérií: rozsah, kde je použita podmínka.
kritéria: podmínka, která se má použít na rozsah_kritérií.
rozsah_sumu: rozsah součtů na základě kritérií.
Nyní z těchto funkcí vytvoříme vzorec. Zde poskytneme data a potřebovali jsme součet výsledků, kde hodnota odpovídá hodnotě ve vyhledávací tabulce.
Obecný vzorec:
= SUMPRODUCT (SUMIF (výsledek, záznamy, sum_nums))
výsledek : hodnoty, které mají být spárovány ve vyhledávací tabulce
záznam : hodnoty v první tabulce
sum_nums : hodnoty k součtu podle odpovídajících hodnot.
Vyzkoušejte tento vzorec spuštěním na příkladu
Zde vypočítáme celkový počet bodů fotbalového týmu pomocí tabulky bodů nebo vyhledávací tabulky.
Zde výsledek (B8: B10) daný jako pojmenovaný rozsah a body (C8: C10) daný jako pojmenovaný rozsah.
Nyní použijeme níže uvedený vzorec k získání SUMU bodů za měsíc 1 v buňce F3
Použijte vzorec:
= SUMPRODUCT (SUMIF (výsledek, B3: E3, body))
Vysvětlení:
- Funkce SUMIF odpovídá výsledku s hodnotami v B3: E3.
- Poté se body odpovídající výsledku vrátí do funkce SUMPRODUCT.
- Funkce SUMPRODUCT vrací funkci součet hodnot zadaných jako pole.
Jak zde vidíte, celkový počet bodů, které tým získal v prvním měsíci, je 7.
3 (Š) + 0 (D) + 1 (H) + 3 (Š) = 7 bodů
Zkopírujte vzorec do jiných buněk, vyberte buňky, které převezmou první buňku, kde je vzorec již použit, použijte klávesovou zkratku Ctrl + D nebo použijte možnost přetažení buňky v aplikaci Excel.
Jak vidíte na výše uvedeném snímku, body získané ve 4 různých měsících.
Získejte SUMU všech bodů po dobu 4 měsíců.
To znamená, že tým za 4 měsíce získal celkem 18 bodů.
Alternativní metoda
Místo použití funkce SUMPRODUCT použijte metodu SUMA a pole.
Použijte vzorec:
{= SUM (SUMIF (výsledek, B3: E3, body))}
Curly braces je metoda pole navrhující, aby funkce vypočítala všechny vrácené hodnoty pomocí funkce SUMIF.
Poznámka: Ne ručně použijte symboly složených závorek. Použijte Ctrl + Shift + Enter použít metodu pole v aplikaci Excel.
Jak vidíte z výše uvedeného vzorce, můžete získat celkový počet bodů pomocí tabulky podmíněného vyhledávání.
Poznámky:
- Vzorec funguje pouze s čísly.
- Vzorec funguje pouze v případě, že ve vyhledávací tabulce nejsou žádné duplikáty
- 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
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: 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