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 si najskôr pozrime scenár.
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 súčet za daný mesiac bez toho, aby sa niečo vo vzorci 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ásenieC2: 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:
| = SUM (INDEX (stĺpce ,, MATCH (nadpisy, 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.
| = SUM (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
To vracia:
Ako to funguje?
Vzorec je vyriešený naruby. Najprv funkcia MATCH vráti index zodpovedajúceho mesiaca z rozsahu C2: N2. Pretože máme máj v B1o, dostaneme 5. Teraz vzorec začína
| = SUM (INDEX (C3: N7,, 5)) |
Ďalej funkcia INDEX vráti hodnoty z 5. stĺpca C3: N7. Teraz vzorec znie:
| = SUM ({6; 12; 15; 15; 8}) |
A nakoniec dostaneme súčet týchto hodnôt.
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.
| = SUM (NEPRIAMY ("Tabuľka1 [" & B10 & "]")) |
Ako to funguje?
V tomto vzorci funkcia INDIRECT preberá odkaz na meno a prevádza ho na skutočný odkaz na názov. Ďalší postup je jednoduchý. Funkcia SUM sumarizuje pomenovaný rozsah.
Takže áno, chlapci, takto môžete zhrnúť zodpovedajúci stĺpec v programe Excel. Dúfam, že vám to pomôže a vysvetlí. Ak máte akékoľvek pochybnosti týkajúce sa tohto článku alebo akejkoľvek inej témy súvisiacej s programom Excel/VBA, opýtajte sa ho v sekcii komentárov nižšie.
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:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám v Exceli prácu ešte zrýchli.
Funkcia 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.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nepotrebujete filtrovať svoje údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
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.