Jak jsem již zmínil v mnoha svých blozích, že SUMPRODUCT je velmi univerzální funkce a lze ji použít k více účelům. V tomto článku uvidíme, jak můžeme tuto funkci použít k počítání hodnot s více nebo kritérii.
Obecný vzorec SUMPRODUCT pro počítání s více nebo kritérii
| = SUMPRODUKT (-((((kritéria1)+(kritéria2)+…)> 0) |
Kritéria 1: Toto je jakékoli kritérium, které vrací pole PRAVDA a NEPRAVDA.
Kritéria 2: Toto je další kritérium, které chcete zkontrolovat. Podobně můžete mít tolik kritérií, kolik chcete.
Výše uvedený obecný vzorec je často upravován tak, aby vyhovoval požadavkům na počítání s více kritérii NEBO. Ale základní vzorec je tento. Nejprve na příkladu uvidíme, jak to funguje, a poté probereme další scénáře, ve kterých budete muset tento vzorec trochu upravit.
Problém:
Cena v buňce by měla být přesně rozdělena na 5 podobných plateb. Při jednoduchém rozdělení ceny 88,04 $ na 5 a zaokrouhlení výsledku na dvě desetinná místa získáme jednotlivé platby ve výši 17,61 $. To by však znamenalo celkovou platbu 88,05 USD, což není přesná částka, kterou začínáme. Proto chceme vypočítat 5 podobných čísel, která se sčítají do celkové částky.
Řešení:
První Vypočítejte první platbu vydělením ceny 5 a zaokrouhlením výsledku.
Použijte funkci ROUND v následujícím vzorci:
= KOLO (B1/5,2)
Krok 2:
Vypočítejte každou zbývající platbu.
Použijte funkce ROUND, SUM a ROW v následujícím vzorci:
= KOLO (($ B $ 1-SUMA ($ B $ 2: B2))/(5-ŘADA ()+ŘADA ($ B $ 2)), 2)
Příklad:
Počítejte uživatele, pokud se kód dealera shoduje nebo rok pomocí SUMPRODUCT
Takže tady máme datovou sadu prodejců. Data obsahují mnoho sloupců. Co musíme udělat, je spočítat počet uživatelů, kteří mají kód „INKA“ nebo rok je „2016“. Ujistěte se, že pokud má někdo obojí (kód jako „inka“ a rok 2016), měl by být započítán jako 1.
Slyšme tedy, že máme dvě kritéria. Používáme výše uvedený vzorec SUMPRODUCT:
| = SUMPRODUKT (-(((Kód = I3)+(Rok = K3))> 0)) |
Zde jsou kód a rok pojmenovány rozsahy. Tím se vrátí 7.
V datech máme 5 záznamů kódu INKA a 4 záznamy roku 2016. Ale 2 záznamy mají kód „INKA“ i rok 2016, respektive rok. A tyto záznamy se počítají jako 1. A takto získáme 7.
Podívejme se, jak to funguje?
Podívejme se tedy na to, jak je vzorec vyřešen krok za krokem, a poté budu diskutovat o tom, jak funguje.
-
- = SUMPRODUKT(-((((Kód = I3)+(Rok = K3))> 0))
- SUMPRODUKT(-((({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0))
- SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …})
- SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Dvojité záporné (-) znaky se používají k převodu booleovských hodnot na 1 s a 0 s. Každá hodnota TRUE v poli je tedy převedena na 1 a FALSE na 0.
Přidání více nebo kritérií k počítání pomocí SUMPRODUCT, takže pokud potřebujete přidat další nebo kritéria pro počítání, můžete do funkce přidat kritéria pomocí znaménka +.
Pokud například chcete do výše uvedeného vzorce přidat další kritéria tak, aby přidala počet zaměstnanců, kteří prodali více než 5 produktů. Vzorec SUMPRODUCT bude jednoduše vypadat takto:
| = SUMPRODUKT (-(((Kód = I3)+(Rok = K3)+(Prodej> 5))> 0)) |
Jednoduchý! ne?
Ale řekněme, že chcete mít dvě kritéria z řady Code. Řekněme, že chcete počítat „INKB“. Jak to tedy uděláte? Jedna metoda používá výše uvedenou techniku, ale to by se opakovalo. Řekněme, že chci přidat dalších 10 kritérií ze stejného rozsahu. V takových případech tato technika není tak chytrá pro počítání s SUMPRODUCT.
Řekněme, že máme data uspořádaná takto.
Kódy kritérií jsou v jednom řádku I2: J2. Uspořádání dat je zde důležité. Vzorec SUMPRODUCT pro nastavení počtu NEBO 3 kritérií bude:
| = SUMPRODUKT (-(((Kód = I2: J2)+(Rok = I3: J3))> 0)) |
Toto je vzorec SUMPRODUCT pro počítání s více kritérii, když je do řádku zapsáno více kritérií z jednoho rozsahu.
Výše uvedený vzorec vrací správnou odpověď, která je 10.
Pokud do J3 napíšete jakýkoli rok, vzorec přidá také počet.
Používá se, když jsou kritéria v jedné řadě. Bude to fungovat, když budou kritéria v jednom sloupci pro stejný rozsah? Ne.
V tomto příkladu máme více kódů k počítání, ale tyto kódy typů jsou zapsány v jednom sloupci. Když použijeme výše uvedený vzorec SUMPRODUCT, dostaneme chybu #N/A. Nebudeme se zabývat tím, jak tato chyba přichází, protože tento článek je příliš dlouhý. Podívejme se, jak to můžeme zajistit.
Aby tento vzorec fungoval, musíte zabalit kritéria kódu do funkce TRANSPOSE. Tím bude vzorec fungovat.
| = SUMPRODUCT (-(((Kód = TRANSPOSE (H3: H4))+(Rok = TRANSPOSE (I3: I4)))> 0)) |
Toto je vzorec pro počítání s více nebo podmínkami ve stejném rozsahu, když jsou kritéria uvedena ve sloupci.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Vzorec funguje pro text i čísla.
- Hodnoty vyhledávání lze zadat jako odkaz na buňku nebo přímo pomocí symbolu uvozovky (") ve vzorci jako argumentů.
Doufám, že jste pochopili, jak rozdělit částku na stejné platby a vyhnout se chybám při dělení / zaokrouhlování v aplikaci Excel. Zde najdete další články o hodnotách vyhledávání v Excelu a funkcích Excelu 2019. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
K vyhledání hodnoty použijte INDEX a MATCH : Funkce INDEX & MATCH k vyhledání hodnoty podle potřeby.
SUM rozsah s INDEX v Excelu : Pomocí funkce INDEX najděte součet hodnot podle potřeby.
Jak používat funkci INDEX v Excelu : Najděte INDEX pole pomocí funkce INDEX vysvětlené na příkladu.
Jak používat funkci MATCH v Excelu : Najděte MATCH v poli pomocí hodnoty INDEX uvnitř funkce MATCH vysvětlené na příkladu.
Jak používat funkci LOOKUP v Excelu : Najděte vyhledávací hodnotu v poli pomocí funkce LOOKUP vysvětlené na příkladu.
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.