Jak vytvořit vypočítané sloupce v Excelu

Anonim

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:

  1. 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.
  2. 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.