V tomto článku se naučíme Jak vytvořit vypočítané sloupce v Excelu.
Scénář
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 různé scénáře.
Jak vytvořit vypočítaný sloupec v tabulce:
- Vyberte buňku v jednom ze sloupců tabulky nebo prázdnou buňku přímo do posledního sloupce, v příkladu níže je vybraná buňka G2.
- Vložte vzorec, který vypočítá tržby na jednotku, zadejte symbol rovná se (=), vyberte buňku F2, zadejte symbol dělení (/) a poté vyberte buňku E2 a stiskněte klávesu Enter.
Vzorec se automaticky zkopíruje do všech buněk ve sloupci.
Příklad:
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átit součet za daný měsíc, aniž by se cokoli ve vzorci 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.
=SUMPRODUKT((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:
=SOUČET(INDEX(sloupce,,ZÁPAS(záhlaví, 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.
=SOUČET(INDEX(C3: N7 ,,ZÁPAS(B10, C2: N2,0))) |
To vrací:
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í:
=SOUČET(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.
=SOUČET(NEPŘÍMÝ („Tabulka 1 [“ & B10 & „]“))) |
Doufám, že tento článek o tom, jak vytvořit vypočítané sloupce v aplikaci Excel, je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Jak sečíst 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 Excelu : 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:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
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.
Jak používat funkci COUNTIF v aplikaci Excel : 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.