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 kritérii NEBO.
Obecný vzorec SUMPRODUCT pro počítání s více nebo kritérii
| = SUMPRODUCT (-((((Kritéria 1)+(Kritéria 2)+… )>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.
Příklad: Počet uživatelů v případě kódu dealera nebo rokuZápasy 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.
Tady tedy 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. Tyto záznamy se počítají jako 1. A takto získáme 7.
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)) |
| 1=>SUMPRODUKT(-((({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE; …})> 0)) |
| 2=>SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; …}) |
| 4=>SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
V prvním kroku je hodnota I3 ("INKA") porovnána s každou buňkou v rozsahu kódu. Tím se vrátí pole PRAVDA a NEPRAVDA. PRAVDA pro každý zápas. Abych ušetřil místo, neukázal jsem všechny TRUE-FALSE. Podobně je hodnota K3 (2016) spárována s každou buňkou v rozmezí let.
V dalším kroku přidáme tato dvě pole, jejichž výsledkem je nové pole číselných hodnot. Jak možná víte, v aplikaci Excel je TRUE považováno za 1 a FALSE za 0. Když se tedy sečtou PRAVDA a PRAVDA, dostaneme 2 a zbytek pochopíte.
V dalším kroku zkontrolujeme, která hodnota je v poli větší než 0. To opět převede pole na skutečné falešné pole. Pro každou hodnotu 0, kterou dostaneme, je hodnota False a zbytek převedena na hodnotu true. Naší odpovědí je nyní počet SKUTEČNÝCH hodnot v poli. Jak je ale spočítáme? Zde je postup.
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.
V posledním kroku SUMPRODUCT shrnuje toto pole a dostaneme odpověď jako 7.
Přidání více nebo kritérií pro počítání pomocí SUMPRODUCT
Pokud tedy potřebujete přidat další nebo kritéria pro počítání, můžete kritéria přidat pouze 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 Kód rozsah. Ř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, který se počítá s více kritérii, když je za sebou napsáno více kritérií z jednoho rozsahu.
Tím se vrátí správná 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 jednom řádku. Bude to fungovat, když jsou 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 k této chybě došlo, protože tento článek bude 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.
Takže jo, kámo, doufám, že jsem měl dost jasno a dávalo to smysl. Doufám, že to slouží vašemu účelu být tady. Pokud tento vzorec váš problém nevyřešil, dejte mi vědět své požadavky v sekci komentáře níže. Budu více než šťastný, že vám jakýmkoli způsobem pomůžu. Můžete zmínit jakékoli Excel/VBA související s pochybnostmi. Do té doby se stále učte, pokračujte v excelování.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu. Tuto funkci lze použít k provádění více úkolů. Jedná se o jednu z nejuniverzálnějších funkcí.
COUNTIFS s rozsahem dynamických kritérií : Abychom mohli počítat s rozsahem dynamických kritérií, jednoduše použijeme funkci NEPŘÍMÝ. Tato funkce může
COUNTIFS S NEBO Pro více kritérií : Pomocí funkce NEBO spočítejte buňky, které mají více kritérií. K vložení logiky NEBO do funkce COUNTIFS nebudete muset používat funkci NEBO.
Použití IF s funkcemi AND / OR v Microsoft Excelu : Tyto logické funkce se používají k provádění výpočtů více kritérií. S IF se funkce OR a AND používají k zahrnutí nebo vyloučení shod.
Jak používat funkci NEBO v aplikaci Microsoft Excel : Funkce slouží k zahrnutí všech hodnot PRAVDA do více kritérií.
Jak počítat buňky, které obsahují to nebo ono, v Excelu v Excelu : K buňkám, které obsahují to či ono, můžeme použít funkci SUMPRODUCT. Takto provedete tyto výpočty.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| 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.
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.