V tomto článku sa naučíme, ako vytvoriť vypočítané stĺpce v programe Excel.
Scenár
Ak chcete získať súčet stĺpca iba pomocou názvu stĺpca, môžete to v programe Excel urobiť 3 jednoduchými spôsobmi. Pozrime sa na tieto spôsoby.
Na rozdiel od iných článkov sa najskôr pozrime na rôzne scenáre.
Ako vytvoriť vypočítaný stĺpec v tabuľke:
- Vyberte bunku v jednom zo stĺpcov tabuľky alebo prázdnu bunku priamo do posledného stĺpca, v nižšie uvedenom príklade je vybratá bunka G2.
- Zadajte vzorec, ktorý vypočítava tržby na jednotku, zadajte symbol rovná sa (=), vyberte bunku F2, zadajte symbol delenia (/) a potom vyberte bunku E2 a stlačte kláves Enter.
Vzorec sa automaticky skopíruje do všetkých buniek v stĺpci.
Príklad:
Tu mám tabuľku tržieb vykonanú rôznymi predajcami v rôznych mesiacoch.
Teraz je úlohou získať súčet predajov za daný mesiac v bunke C10. Ak zmeníme mesiac v B10, suma by sa mala zmeniť a vrátiť sumu za daný mesiac bez toho, aby sa vo vzorci niečo zmenilo.
Metóda 1: Suma celého stĺpca v tabuľke pomocou funkcie SUMPRODUCT.
Syntax stĺpca súčtu súčtu metódy SUMPRODUCT je:
| = SUMPRODUCT ((stĺpce)*(hlavičky = nadpis)) |
Stĺpce: Je to dvojrozmerný rozsah stĺpcov, ktoré chcete sčítať. Nesmie obsahovať hlavičky. V tabuľke vyššie je to C3: N7.
Hlavičky: Ide o rozsah hlavičiek stĺpce ktoré chcete zhrnúť. Vo vyššie uvedených údajoch je to C2: N2.
Nadpis: Je to nadpis, ktorý chcete priradiť. Vo vyššie uvedenom príklade je to v B10.
Bez ďalšieho zdržania použijeme vzorec.
| =SUMPRODUCT((C3: N7)*(C2: N2 = B10)) |
a vráti sa toto:
Ako to funguje?
Je to jednoduché. Vo vzorci vyhlásenie C2: N2 = B10 vráti pole, ktoré obsahuje všetky NEPRAVDIVÉ hodnoty, okrem tej, ktorá sa zhoduje s B10. Teraz je vzorec
| = SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,PRAVDA, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Teraz je C3: N7 vynásobený každou hodnotou tohto poľa. Každý stĺpec sa stane nulou, okrem stĺpca, ktorý je vynásobený hodnotou PRAVDA. Teraz vzorec znie:
| = 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,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}) |
Teraz je toto pole zhrnuté a dostaneme súčet stĺpca, ktorý sa zhoduje so stĺpcom v bunke B10.
Metóda 2: Suma celého stĺpca v tabuľke pomocou funkcie INDEX-MATCH.
Syntax metódy na zhrnutie záhlavia zodpovedajúcich stĺpcov v programe Excel je:
| =SÚČET(INDEX(stĺpce,,ZÁPAS(nadpis, hlavičky, 0))) |
Všetky premenné v tejto metóde sú rovnaké ako v prípade metódy SUMPRODUCT. Implementujme to len na vyriešenie problému. Napíšte tento vzorec do C10.
| =SÚČET(INDEX(C3: N7 ,,ZÁPAS(B10, C2: N2,0))) |
To vracia:
Metóda 3: Súčet celého stĺpca v tabuľke pomocou pomenovaného rozsahu a funkcie NEPRIAMY
Všetko je jednoduché, ak rozsahy pomenujete ako nadpisy stĺpcov. Pri tejto metóde musíme stĺpce najskôr pomenovať ako názvy nadpisov.
Vyberte tabuľku vrátane nadpisov a stlačte kombináciu klávesov CTRL+SHIFT+F3. Otvorí sa dialógové okno na vytvorenie názvu z rozsahov. Skontrolujte horný riadok a kliknite na tlačidlo OK.
Pomenuje všetky stĺpce údajov ako ich nadpisy.
Teraz bude všeobecný vzorec na súčet zodpovedajúceho stĺpca:
| =SÚČET(NEPRIAMY(nadpis)) |
Nadpis: Je to názov stĺpca, ktorý chcete sčítať. V tomto prípade je to práve B10, ktorý v súčasnosti obsahuje máj.
Ak chcete implementovať tento generický vzorec, napíšte tento vzorec do bunky C10.
| =SÚČET(NEPRIAMY(B10)) |
Vráti sa tak suma za májový mesiac:
Podobný je aj ďalší spôsob. V tejto metóde používame tabuľky programu Excel a je to štruktúrované pomenovanie. Povedzme, že ste vyššie uvedenú tabuľku pomenovali ako tabuľku1. Potom bude tento vzorec fungovať rovnako ako vyššie uvedený vzorec.
| =SÚČET(NEPRIAMY („Tabuľka1 [“ & B10 & „]“)) |
Dúfam, že tento článok o tom, ako vytvoriť vypočítané stĺpce v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Ako sčítať pomocou zhody riadka a stĺpca v programe Excel : SUMPRODUCT je najuniverzálnejšou funkciou, pokiaľ ide o súčty a počítanie hodnôt so zložitými kritériami. Generická funkcia na súčet zhodou so stĺpcom a riadkom je…
SUMIF s 3D referenciou v Exceli : Zábavným faktom je, že bežné 3D referencie v Exceli nefungujú s podmienenými funkciami, ako je napríklad funkcia SUMIF. V tomto článku sa naučíme, ako získať 3D odkazovanie pri práci s funkciou SUMIF.
Relatívna a absolútna referencia v programe Excel: Odporúčanie v Exceli je dôležitou témou pre každého začiatočníka. Dokonca aj skúsení používatelia programu Excel robia chyby pri odkazovaní.
Referencia dynamického pracovného hárka: Poskytujte referenčné hárky dynamicky pomocou funkcie INDIRECT v programe Excel. Toto je jednoduché…
Rozšírenie referencií v programe Excel: Rozbaľujúca sa referencia sa rozbalí pri kopírovaní nadol alebo doprava. Na to použijeme znak $ pred číslom stĺpca a riadka. Tu je jeden príklad…
Všetko o absolútnej referencii: Predvolený typ referencie v programe Excel je relatívny, ale ak chcete, aby referencia buniek a rozsahov bola absolútna, použite znak $. Tu sú všetky aspekty absolútneho odkazovania v programe Excel.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.