Funkce COUNTIFS a SUMIFS patří mezi nejpoužívanější funkce v Excelu. Jedna věc ale dokáže spoustu uživatelů pořádně potrápit. A to je vyjádření podmínky NEBO v těchto funkcích. Věděli jste, že to můžete udělat rovnou třemi způsoby? A každý se může hodit pro jinou příležitost.
Excelový soubor ke stažení:
V dnešním videu si ukážeme hned tři různé způsoby, jak podmínku NEBO vytvořit. Začneme tím nejjednodušším, potom si ukážeme elegantní řešení pomocí složených závorek, a nakonec se podíváme i na maticový přístup.
Tak jdeme na to.
V příkladu máme jednoduchou
tabulku, kde máme produkty, pobočky, na kterých se produkt prodal a tržby.
Naším úkolem je spočítat, kolik se prodalo celkem košilí, sukní a bund. A
zároveň jaké byly celkové tržby těchto tří produktů. Všechny tyto produkty se
vyskytují v jednom sloupci Produkt. To znamená, že pro správný výpočet
musíme použít logické vyjádření NEBO ve funkcích COUNTIFS a SUMIFS.
Klasický způsob
Klasický způsob, jak by toto většina uživatelů v Excelu řešila by byla, že by posčítali jednotlivé funkce. Začneme s počty. Funkce COUNTIFS, kde nejprve spočítáme výskyt pro první produkt. Takže jako oblast kritérií označíme sloupec Produkt a jako kritérium první produkt. A jelikož máme druhou podmínku, tedy druhý produkt, ve stejném sloupci, tak nemůžeme jednoduše další kritérium vložit do té samé funkce, ale musíme k této funkci přičíst druhou funkci COUNTIFS, kde označíme opět sloupec Produkt a jako kritérium označíme druhý produkt. A jelikož máme i třetí produkt, tak k těmto dvěma funkcím přičteme i třetí funkci COUNTIFS, kde spočítáme výskyt pro třetí produkt. Když toto potvrdíme, tak se vrátí správný výsledek.
Na tomto způsobu není nic špatného a jedná se o klasický způsob, který použije většina uživatelů Excelu. Výhodou je, že se odkazujeme ve funkcích na buňky s produkty, takže můžeme výběr produktu lehce měnit pouze upravením buněk. Nicméně nevýhoda je, že musíme funkce COUNTIFS neustále opakovat a kupit je za sebou, což v případě, že jich máme hodně může být zdlouhavé.
To samé by samozřejmě platilo i pro funkci SUMIFS. Abychom se dopočítali správného výsledku, tak bychom museli použít tři funkce SUMIFS, které bychom sečetli.
Věděli jste ale, že existují i další dva způsoby, které vám dovolí spočítat to samé, ale pouze s jednou funkcí?
Složené závorky
První alternativní možností je použít ve funkcích složené závorky. Pomocí složených závorek totiž vytvoříme matici hodnot. Funkce COUNTIFS by vypadala tak, že napíšeme funkci, kde jako oblast kritérií označíme sloupec Produkt. A v parametru kritérium otevřeme složenou závorku. V této závorce vypíšeme produkty, které chceme počítat. A jelikož se jedná o textové hodnoty, tak musejí být tyto hodnoty v uvozovkách. Nakonec zavřeme složenou závorku a zavřeme funkci COUNTIFS. Tento zápis způsobí to, že funkce COUNTIFS spočítá hodnoty pro hodnoty uvedené ve složených závorkách.
To si můžete lehce ověřit, když označíte funkci a podíváte se, co funkce vrací pomocí klávesy F9. Vidíte tři hodnoty. Teď stačí tyto hodnoty sečíst pomocí funkce SUMA. Takže funkci COUNTIFS zabalíme do funkce SUMA.
Starší verze Excelu před rokem 2021 musejí tento výpočet potvrdit klávesami CSE. Novějším Excelům stačí klávesa ENTER. A máme správný výsledek. Tento zápis je o dost kratší, a to díky složeným závorkám. Nicméně nevýhodou je, že ve složených závorkách se nemůžete odkazovat na buňky, můžete v nich použít pouze konstanty. Takže vypsat jednotlivé hodnoty, ať už číselné nebo textové.
Stejný postup se složenými závorkami můžeme použít i ve funkci SUMIFS.
Matice
Existuje ale i třetí způsob, velmi podobný složeným závorkám. Použijeme funkci COUNTIFS, kde opět jako oblast kritérií označíme sloupec Produkt. A v parametru kritérium jednoduše označíme všechny produkty, jako jedno pole neboli matici. Opět, když se podíváme, co tento zápis vrací, tak vidíme, že se opět vrátí tři hodnoty. A to jsou počty pro označené produkty. Stejně jako v případě se složenými závorkami musíme i tyto hodnoty sečíst. Takže funkci zabalíme do funkce SUMA. A stejně jako v minulém případě i zde starší verze Excelu potvrdí vzorec klávesami CSE.
Výhodou tohoto přístupu oproti složeným závorkám je to, že se v něm můžeme odkázat na buňky a nemusíme hodnoty do složených závorek vypisovat.
Stejný postup můžete použít i ve funkci SUMIFS.
