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. Pojďme prozkoumat tyto způsoby.
Na rozdíl od jiných článků se nejprve podívejme na scénář.
Zde mám tabulku prodejů provedenou různými prodejci v různých měsících.
Nyní je úkolem získat součet tržeb za daný měsíc v buňce C10. Pokud změníme měsíc v B10, součet by se měl změnit a vrátí součet toho měsíce, aniž by se ve vzorci cokoli změnilo.
Metoda 1: Součet celého sloupce v tabulce pomocí funkce SUMPRODUCT.
Syntaxe metody SUMPRODUCT ke sloupci shody součtů je:
| = SUMPRODUCT ((sloupce)*(záhlaví = nadpis)) |
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.
Záhlaví:Je to rozsah záhlaví sloupce které chcete shrnout. Ve výše uvedených datech je to C2: N2.
Nadpis: Je to nadpis, kterému chcete odpovídat. Ve výše uvedeném příkladu je v B10.
Bez dalšího zdržení použijme vzorec.
| = SUMPRODUCT ((C3: N7)*(C2: N2 = B10)) |
a vrátí se toto:
Jak to funguje?
Je to jednoduché. Ve vzorci prohlášeníC2: N2 = B10 vrací pole, které obsahuje všechny NEPRAVDIVÉ hodnoty kromě jedné, která odpovídá B10. Nyní je vzorec
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,SKUTEČNÝ, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Nyní je C3: N7 vynásoben každou hodnotou tohoto pole. Každý sloupec se stane nulou kromě sloupce, který je vynásoben PRAVDU. Nyní se vzorec stává:
| = SUMPRODUCT ({0,0,0,0,6,0,0,0,0,0,0,0,0; 0,0,0,0,12,0,0,0,0,0,0, 0; 0,0,0,0,15,0,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0,0}) |
Nyní je toto pole sečteno a dostaneme součet sloupce, který odpovídá sloupci v buňce B10.
Metoda 2: Součet celého sloupce v tabulce pomocí funkce INDEX-MATCH.
Syntaxe metody pro součet záhlaví odpovídajících sloupců v aplikaci Excel je:
| = SUM (INDEX (sloupce ,, MATCH (nadpis, záhlaví, 0))) |
Všechny proměnné v této metodě jsou stejné jako metoda SUMPRODUCT. Pojďme to implementovat, abychom problém vyřešili. Napište tento vzorec do C10.
| = SUM (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
To vrací:
Jak to funguje?
Vzorec je vyřešen naruby. Za prvé, funkce MATCH vrací index odpovídajícího měsíce z rozsahu C2: N2. Protože máme květen v B1o, dostaneme 5. Nyní vzorec začíná
| = SUM (INDEX (C3: N7,, 5)) |
Dále funkce INDEX vrací hodnoty z 5. sloupce C3: N7. Nyní se vzorec stává:
| = SUMA ({6; 12; 15; 15; 8}) |
A nakonec dostaneme součet těchto hodnot.
Metoda 3: Součet celého sloupce v tabulce pomocí pojmenovaného rozsahu a funkce NEPŘÍMÉ
Všechno je jednoduché, pokud pojmenujete rozsahy jako záhlaví sloupců. V této metodě musíme nejprve pojmenovat sloupce jako jejich názvy nadpisů.
Vyberte tabulku včetně nadpisů a stiskněte CTRL+SHIFT+F3. Otevře se dialog pro vytvoření názvu z rozsahů. Zkontrolujte horní řádek a stiskněte tlačítko OK.
Pojmenuje všechny datové sloupce jako jejich nadpisy.
Obecný vzorec pro součet odpovídajících sloupců bude nyní:
| = SUM (NEPŘÍMÝ (nadpis)) |
Nadpis: Je to název sloupce, který chcete sečíst. V tomto příkladu je to B10, který nyní může obsahovat.
Chcete -li implementovat tento obecný vzorec, napište tento vzorec do buňky C10.
| = SOUČET (NEPŘÍMÝ (B10)) |
Tím se vrátí součet květnového měsíce:
Další metoda je podobná této. V této metodě používáme tabulky Excelu a je to strukturované pojmenování. Řekněme, že jste výše uvedenou tabulku pojmenovali jako tabulka1. Pak bude tento vzorec fungovat stejně jako výše uvedený vzorec.
| = SUM (NEPŘÍMÝ ("Tabulka1 [" & B10 & "]")) |
Jak to funguje?
V tomto vzorci funkce INDIRECT převezme odkaz na název a převede jej na skutečný odkaz na název. Postup dále je jednoduchý. Funkce SUMA shrnuje pojmenovaný rozsah.
Takže ano, lidi, takto můžete shrnout odpovídající sloupec v aplikaci Excel. Doufám, že vám to pomůže a vysvětlí. Máte -li jakékoli pochybnosti týkající se tohoto článku nebo jiného tématu souvisejícího s Excelem/VBA, zeptejte se v sekci komentáře níže.
Jak sčítat porovnáním řádků a sloupců v aplikaci Excel |SUMPRODUCT je nejuniverzálnější funkcí, pokud jde o součet a počítání hodnot se složitými kritérii. Obecná funkce pro součet shody sloupců a řádků 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.