Doteraz sme sa naučili, ako sčítať celý zodpovedajúci stĺpec tabuľky v programe Excel. Ako však sčítame hodnoty, keď potrebujeme zladiť stĺpce a riadky. V tomto článku sa naučíme, ako urobiť súčet zodpovedajúcich riadkov a stĺpcov.
Na to existujú dva vzorce, ale najskôr si uvedieme scenár.
Tu mám tabuľku, ktorá zaznamenáva tržby vykonané zamestnancami v rôznych mesiacoch. Meno zamestnancov sa môže opakovať. Pozrite sa na nasledujúci obrázok:
Potrebujeme získať súčet májového mesiaca, kde je predavač Donald.
Metóda 1: Sčítanie hlavičky zodpovedajúceho stĺpca a hlavičky riadka pomocou funkcie SUMPRODUCT.
The Funkcia SUMPRODUCT je najuniverzálnejšia funkcia, pokiaľ ide o hodnoty súčtu a počítania s náročnými kritériami. Generická funkcia na súčet zhodou so stĺpcom a riadkom je:
| = SUMPRODUCT ((stĺpce)*(stĺpce_hlavičky = nadpis_stĺpca)*(záhlavia_riadkov = záhlavia riadkov) |
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 stĺpcov:Ide o rozsah hlavičiekstĺpce ktoré chcete zhrnúť. Vo vyššie uvedených údajoch je to C2: N2.
hlavička stĺpca: Je to nadpis, ktorý chcete priradiť. Vo vyššie uvedenom príklade je to v B13.
Bez ďalšieho zdržania použijeme vzorec.
záhlavia riadkov:Ide o rozsah hlavičiekriadky ktoré chcete zhrnúť. Vo vyššie uvedených údajoch je to B3: B10.
riadok_záhlavia: Je to nadpis, ktorý chcete priradiť v riadkoch. Vo vyššie uvedenom príklade je to vo F13.
Bez ďalšieho zdržania použijeme vzorec.
Napíšte tento vzorec do bunky D13 a nechajte excel urobiť kúzlo (nič také ako mágia neexistuje) …
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Tým sa vráti hodnota:
Teraz, keď zmeníte mesiac alebo predajcu, suma sa zmení podľa nadpisu riadka a záhlavia stĺpca.
Ako to funguje?
Táto jednoduchá booleovská logika.
(C2: N2 = B13): Tento príkaz vráti pole TRUE a FALSE. Všetky zodpovedajúce hodnoty v stĺpci majú hodnotu true a ostatné budú mať hodnotu false. V tomto prípade budeme mať iba jednu hodnotu True, pretože rozsah C2: N2 obsahuje iba jednu inštanciu máj o 5th Poloha.
(B3: B10 = E13): To bude fungovať rovnako ako vyššie a vráti pole PRAVDA a NEPRAVDA. Všetky zodpovedajúce hodnoty budú mať hodnotu TRUE a ostatné budú mať hodnotu FALSE. V tomto prípade budeme mať 2 TRUE, pretože rozsah B3: B10 má dve inštancie „Donald“.
(C2: N2 = B13)*(B3: B10 = E13): Teraz vynásobíme polia vrátené príkazmi. Toto sa implementuje a bude logické a dostaneme pole 1 s a 0 s. Teraz budeme mať 2D pole, ktoré bude obsahovať 2 1 s a zvyšok 0 s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Nakoniec znásobíme 2D pole 2D tabuľkou. Vráti opäť pole 0 s a čísla, ktoré zodpovedajú kritériám.
Nakoniec, SUMPRODUCT funkcia zhrnie pole, ktoré bude mať za následok požadovaný výstup.
Metóda 2: Sčítanie hlavičky zodpovedajúceho stĺpca a hlavičky riadka pomocou funkcie SUMA a IF
Obecný vzorec na súčet zodpovedajúcich riadkov a stĺpcov pomocou funkcie SUMA a IF Excel je:
| = SUM (IF (column_headers = header_heading, IF (row_headers = row_heading, columns)))) |
Všetky premenné sú rovnaké ako vo vyššie uvedenej metóde. Tu ich stačí použiť v inom poradí.
Napíšte tento vzorec do bunky D13:
| = SÚČET (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Tým sa vráti správna odpoveď. Pozrite si snímku obrazovky nižšie:
Ako to funguje?
Logika je rovnaká ako pri prvej metóde SUMPRODCUT, iba mechanizmus je iný. Ak to stručne vysvetlím, vnútorná funkcia IF vráti 2D pole rovnakej dimenzie ako tabuľka. Toto pole obsahuje počet dvoch zhodných riadkov. Potom sa vnútorná funkcia IF zhoduje s nadpismi dvoch stĺpcov v tomto poli a vráti 2D pole, ktoré obsahuje iba čísla zodpovedajúce stĺpcom aj nadpisom. Všetky ostatné prvky poľa budú FALSE.
Nakoniec funkcia SUMA sumarizuje toto pole a dostaneme náš súčet.
Takže áno, chlapci, takto môžete zhrnúť zodpovedajúce riadky a stĺpce z tabuľky v programe Excel. Dúfam, že to bolo pre vás vysvetľujúce a užitočné. Ak máte akékoľvek pochybnosti o tejto téme alebo máte ďalšie pochybnosti súvisiace s programom Excel/VBA, opýtajte sa ich v sekcii komentárov nižšie.
Ako sčítať stĺpce v programe Excel zhodou nadpisov | 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. Syntax stĺpca súčtu súčtu metódy SUMPRODUCT 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.