Řekněme, že máte v tabulce aplikace Excel seznam produktů s jejich cenou. Nyní musíte z dostupných položek vytvořit balíčky, které mohou obsahovat různé produkty. Na konci byste chtěli vypočítat celkovou cenu každého balíčku. Jednou to můžete udělat ručně, ale pokud je to váš běžný úkol, je lepší tento úkol zautomatizovat pomocí elegantních vzorců. A k tomu slouží tento článek. Naučíme se, jak vypočítat ceny balíků nebo skupin položek pomocí jednoho vzorce.
Obecný vzorec:
| = SUMPRODUCT (price_range,-(check_range = "y")) |
cenové rozpětí:Je to rozsah, který obsahuje cenu produktů.
kontrolní_rozsah: Je to rozsah, do kterého chceme dát šek. Pokud je produkt součástí svazku, vložíme y do průřezu svazku a produktu.
"y"= Toto je kontrola, kterou vkládáme do zahrnutí produktu do balíčku."
Ukažme si příklad na vyjasnění konceptu.
Příklad: Vytvořte vzorec pro stanovení cen balíků v aplikaci Excel.
Bereme stejný scénář, o kterém jsme hovořili na začátku. Připravili jsme tabulku v rozsahu B2: F9. Pojďme identifikovat proměnné, které potřebujeme.
Cenové rozpětí:Cenové rozpětí je C2: C9. Protože je to pevné, můžeme buď pojmenovat rozsah, nebo použít jeho absolutní odkaz. V tomto příkladu použiji absolutní referenci $ C $ 2: $ C $ 9.
Rozsah kontroly:Toto je rozsah, který obsahuje šeky (sloupec svazku). Jsou to D3: D9, E3: E9 a F3: f9.
Vložme tyto hodnoty do obecného vzorce.
Pro výpočet ceny balíčku napište tento vzorec do D10.
| = SUMPRODUCT ($ C $ 3: $ C $ 9,-(D3: D9 = "y")) |
Stiskněte Enter. Náklady na balíček 1 máte vypočítané v buňce D10. Zkopírujte tento vzorec do sousedních buněk a vypočítejte cenu všech balíčků.
Jak to funguje?
Vzorec funguje naruby. Takže první -(D3: D9 = "y") je vyřešen.
Tím se vrátí pole 1 a 0 jako. 1 pro každé y a 0 cokoli jiného v rozsahu D3: D9.
| {1;1;0;1;0;1;0} |
Dále je $ C $ 3: $ C $ 9 převedeno na pole, které obsahuje cenu každé položky/produktu.
| {100;200;20;10;12;15;25} |
Nyní to má v sobě funkce SUMPRODUCT.
| = SUMPRODUCT ({100; 200; 20; 10; 12; 15; 25}, {1; 1; 0; 1; 0; 1; 0}) |
Nyní, jak to dělá funkce SUMPRODUCT, vynásobí každou hodnotu v jednom poli na stejné indexované pole v jiném poli a nakonec tyto hodnoty shrne. To znamená, že každá cena, která odpovídá 0 v jiném poli, se změní na 0. {100; 200; 0; 10; 0; 15; 0}. Nyní je toto pole sečteno. To nám dává 325 pro balíček 1. Totéž platí pro všechny balíčky.
Alternativní vzorec:
Alternativním vzorcem je samozřejmě funkce SUMIF a SUMIFS.
| = SUMIF (D3: D9, "y", $ C $ 3: $ C $ 9) |
a
| = SUMIFS ($ C $ 3: $ C $ 9, D3: D9, "y") |
Toto jsou klasické odpovědi, ale vzorec SUMPRODUCT je také rychlejší a efektnější.
Takže jo, lidi, takto můžete snadno vypočítat cenu balíčku v aplikaci Excel. Doufám, že to bylo dostatečně vysvětlující. Pokud jsem zmeškal nějaký bod nebo máte nějaké pochybnosti týkající se tohoto článku nebo jakékoli jiné pochybnosti související s Excelem, zeptejte se ho v sekci komentáře níže.
Počítat celkové shody ve dvou rozsazích v Excelu | Naučte se počítat celkové shody ve dvou rozsazích pomocí funkce SUMPROUDCT.
SUMIFY využívající logiku AND-OR | SUMIFS lze použít i s logikou NEBO. Nedostatečná logika, kterou SUMIFS používá, je logika AND.
SUMPRODUCT s logikou IF | Naučte se používat SUMPRODUCT s logikou IF bez použití funkce IF ve vzorci.
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.
Funkce VLOOKUP v Excelu | 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ů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétní hodnoty nepotřebujete filtrovat data. 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.