Zatím jsme se naučili, jak sečíst celý odpovídající sloupec tabulky v Excelu. Jak ale sečteme hodnoty, když potřebujeme porovnat sloupec a řádek. V tomto článku se naučíme, jak provést součet odpovídajících řádků a sloupců.
K tomu existují dva vzorce, ale nejprve si uveďme scénář.
Zde mám tabulku, která zaznamenává tržby provedené zaměstnanci v různých měsících. Jméno zaměstnance se může opakovat. Viz následující obrázek:
Potřebujeme získat součet květnového měsíce, kde je prodavač Donald.
Metoda 1: Součet odpovídajících záhlaví sloupců a záhlaví řádků Pomocí funkce SUMPRODUCT.
The Funkce SUMPRODUCT je nejuniverzálnější funkcí, pokud jde o součty a počítání hodnot se složitými kritérii. Obecná funkce pro součet shody sloupců a řádků je:
| = SUMPRODUCT ((sloupce)*(sloupce_záhlaví = záhlaví sloupců)*(záhlaví řádků = záhlaví řádků) |
Sloupce:Jedná se o 2rozměrný rozsah sloupců, které chcete sečíst. Nemělo by obsahovat záhlaví. V tabulce výše je to C3: N7.
hlavičky sloupců:Je to rozsah záhlavísloupce které chcete shrnout. Ve výše uvedených datech je to C2: N2.
název_sloupce: Je to nadpis, kterému chcete odpovídat. Ve výše uvedeném příkladu je to v B13.
Bez dalšího zdržení použijme vzorec.
záhlaví řádků:Je to rozsah záhlavířádky které chcete shrnout. Ve výše uvedených datech je to B3: B10.
záhlaví řádku: Je to nadpis, který chcete spojit v řádcích. Ve výše uvedeném příkladu je to ve F13.
Bez dalšího zdržení použijme vzorec.
Napište tento vzorec do buňky D13 a nechte excel dělat magii (nic takového jako magie neexistuje)…
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Tím se vrátí hodnota:
Když nyní změníte měsíc nebo prodejce, součet se změní podle záhlaví řádku a záhlaví sloupce.
Jak to funguje?
Tato jednoduchá booleovská logika.
(C2: N2 = B13): Tento příkaz vrátí pole PRAVDA a NEPRAVDA. Všechny odpovídající hodnoty ve sloupci mají hodnotu true a ostatní budou mít hodnotu false. V tomto případě budeme mít pouze jeden True, protože rozsah C2: N2 obsahuje pouze jednu instanci května v 5th Umístění.
(B3: B10 = E13): To bude fungovat stejně jako výše a vrátí pole PRAVDA a NEPRAVDA. Všechny odpovídající hodnoty budou mít hodnotu TRUE a ostatní budou mít hodnotu FALSE. V tomto případě budeme mít 2 TRUE, protože rozsah B3: B10 má dvě instance "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Nyní vynásobíme pole vrácená příkazy. To bude implementovat a logiku a dostaneme pole 1s a 0s. Nyní budeme mít 2D pole, které bude obsahovat 2 1s a zbytek 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Nakonec vynásobíme 2D pole 2D tabulkou. Vrátí opět pole 0 s a čísla, která odpovídají kritériím.
Nakonec, SUMPRODUKT funkce shrne pole, které bude mít za následek požadovaný výstup.
Metoda 2: Součet záhlaví odpovídajících sloupců a záhlaví řádků pomocí funkce SUMA a IF
Obecný vzorec pro součet odpovídajících řádků a sloupců pomocí funkce SUMA a IF Excel je:
| = SUM (IF (column_headers = header_heading, IF (row_headers = row_heading, columns))) |
Všechny proměnné jsou stejné jako ve výše vysvětlené metodě. Zde je stačí použít v jiném pořadí.
Do buňky D13 napište tento vzorec:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Tím se vrátí správná odpověď. Podívejte se na snímek obrazovky níže:
Jak to funguje?
Logika je stejná jako u první metody SUMPRODCUT, liší se pouze mechanismus. Pokud to vysvětlím ve zkratce, vnitřní IF funkce vrátí 2D pole stejné dimenze jako tabulka. Toto pole obsahuje počet dvou odpovídajících řádků. Potom vnitřní funkce IF odpovídá nadpisům dvou sloupců v tomto poli a vrátí 2D pole, které obsahuje pouze čísla, která odpovídají jak sloupci, tak záhlaví. Všechny ostatní prvky pole budou FALSE.
Nakonec funkce SUMA toto pole shrne a dostaneme náš součet.
Takže jo, lidi, takto můžete shrnout odpovídající řádky a sloupce z tabulky v Excelu. Doufám, že to pro vás bylo vysvětlující a užitečné. Máte -li jakékoli pochybnosti týkající se tohoto tématu nebo máte jiné pochybnosti týkající se aplikace Excel/VBA, zeptejte se v sekci komentáře níže.
Jak sečíst sloupec v Excelu shodou nadpisů | Pokud chcete získat součet sloupce pouhým použitím názvu sloupce, můžete to v aplikaci Excel provést 3 snadnými způsoby. Syntaxe metody SUMPRODUCT ke sloupci shody součtů je:
SUMIF s 3D referencí v aplikaci Excel |Zábavným faktem je, že normální odkazy na 3D Excel nefungují s podmíněnými funkcemi, jako je funkce SUMIF. V tomto článku se naučíme, jak získat 3D referenční práci s funkcí SUMIF.
Relativní a absolutní reference v aplikaci Excel | Odkazování v Excelu je důležitým tématem pro každého začátečníka. Dokonce i zkušení uživatelé aplikace Excel dělají chyby při odkazování.
Reference dynamického listu | Poskytujte referenční listy dynamicky pomocí NEPŘÍMÉ funkce Excel. To je jednoduché…
Rozbalení odkazů v aplikaci Excel | Rozbalená reference se rozbalí při kopírování dolů nebo doprava. K tomu použijeme znak $ před číslem sloupce a řádku. Zde je jeden příklad…
Vše o absolutní referenci | Výchozí typ odkazu v aplikaci Excel je relativní, ale pokud chcete, aby odkaz na buňky a rozsahy byl absolutní, použijte znak $. Zde jsou všechny aspekty absolutního odkazování v Excelu.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 zkratek vám v Excelu práci ještě zrychlí.
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.