Doslej smo se naučili, kako sešteti celoten ujemajoči se stolpec tabele v Excelu. Kako pa seštejemo vrednosti, ko se moramo ujemati s stolpcem in vrstico. V tem članku se bomo naučili, kako narediti vsoto ujemajočih se vrstic in stolpcev.
Za to obstajata dve formuli, najprej pa poglejmo scenarij.
Tukaj imam tabelo, ki beleži prodajo zaposlenih v različnih mesecih. Imena zaposlenih se lahko ponovijo. Oglejte si spodnjo sliko:
Dobiti moramo vsoto majskega meseca, ko je prodajalec Donald.
1. način: povzetek ujemajoče se glave stolpca in vrstice z uporabo funkcije SUMPRODUCT.
The PODROČJE Funkcija je najbolj vsestranska funkcija, ko gre za seštevanje in štetje vrednosti z zapletenimi merili. Splošna funkcija, ki jo je treba sešteti z ujemanjem stolpca in vrstice, je:
| = SUMPRODUCT ((stolpci)*(stolpci_glavci = naslov stolpca)*(vrstice_glavci = vrstica_glavja) |
Stolpci:To je dvodimenzionalno območje stolpcev, ki ga želite sešteti. Ne sme vsebovati glav. V zgornji tabeli je C3: N7.
stolpci_glavja:To je obseg glavestolpci ki jih želite povzeti. V zgornjih podatkih je C2: N2.
naslov_ stolpca: To je naslov, ki se mu želite ujemati. V zgornjem primeru je v B13.
Brez odlašanja uporabimo formulo.
vrstica_glavja:To je obseg glavevrstice ki jih želite povzeti. V zgornjih podatkih je B3: B10.
row_heading: To je naslov, ki ga želite ujemati v vrsticah. V zgornjem primeru je v F13.
Brez odlašanja uporabimo formulo.
To formulo zapišite v celico D13 in pustite Excelu, da naredi magijo (čarobnosti ni) …
| = PODROČJE ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
To vrne vrednost:
Ko spremenite mesec ali prodajalca, se bo vsota spremenila glede na naslov vrstice in naslov stolpca.
Kako deluje?
Ta preprosta logična logika.
(C2: N2 = B13): Ta stavek bo vrnil niz TRUE in FALSE. Vse ujemajoče se vrednosti v stolpcu imajo true, druge pa false. V tem primeru bomo imeli samo eno vrednost True, saj obseg C2: N2 vsebuje le en primerek maj pri 5th Lokacija.
(B3: B10 = E13): To bo delovalo enako kot zgoraj in vrnilo bo niz TRUE in FALSE. Vse ujemajoče se vrednosti imajo TRUE, druge pa FALSE. V tem primeru bomo imeli 2 resnici, saj ima obseg B3: B10 dva primerka "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Zdaj množimo matrike, ki jih vrnejo stavki. To bo izvedlo in logiko in dobili bomo niz 1 in 0. Zdaj bomo imeli 2D matriko, ki bo vsebovala 2 1s in ostalo 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Na koncu pomnožimo 2D matriko z 2D tabelo. Spet bo vrnil niz 0 in številke, ki ustrezajo kriterijem.
Končno, PODROČJE funkcija bo povzela matriko, kar bo povzročilo želeni izhod.
2. način: povzemanje ujemajoče se glave stolpca in vrstice z uporabo funkcij SUM in IF
Splošna formula za seštevanje ujemajočih se vrstic in stolpcev s funkcijo SUM in IF Excel je:
| = VSE |
Vse spremenljivke so enake kot pri zgoraj opisani metodi. Tukaj jih je treba uporabiti v drugačnem vrstnem redu.
V celico D13 zapišite to formulo:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
To vrne pravilen odgovor. Oglejte si spodnji posnetek zaslona:
Kako deluje?
Logika je enaka prvi metodi SUMPRODCUT, le mehanizem je drugačen. Če na kratko razložim, notranja funkcija IF vrne 2D matriko iste dimenzije kot tabela. Ta matrika vsebuje število dveh ujemajočih se vrstic. Nato se notranja funkcija IF ujema z naslovi dveh stolpcev v tem nizu in vrne 2D matriko, ki vsebuje samo številke, ki se ujemajo tako s stolpcem kot z naslovom. Vsi drugi elementi matrike bodo FALSE.
Končno funkcija SUM povzame to matriko in dobimo naš vsoto.
Torej, fantje, tako lahko v Excelu povzamete ujemajoče se vrstice in stolpce iz tabele. Upam, da vam je bilo razlagalno in koristno. Če dvomite o tej temi ali dvomite o Excelu/VBA, vprašajte v spodnjem razdelku za komentarje.
Kako sešteti stolpec v Excelu z ujemanjem naslova | Če želite pridobiti vsoto stolpca z uporabo imena stolpca, lahko to storite na 3 preproste načine v Excelu. Sintaksa metode SUMPRODUCT za seštevanje ujemajočega se stolpca je:
SUMIF s 3D referenco v Excelu |Zabavno dejstvo je, da običajno sklicevanje v Excelu 3D ne deluje s pogojnimi funkcijami, kot je funkcija SUMIF. V tem članku se bomo naučili, kako 3D referenciranje deluje s funkcijo SUMIF.
Relativna in absolutna referenca v Excelu | Sklicevanje v excelu je pomembna tema za vsakega začetnika. Tudi izkušeni uporabniki excela delajo napake pri sklicevanju.
Sklic dinamičnega delovnega lista | Referenčne liste podajte dinamično s funkcijo INDIRECT v Excelu. To je preprosto…
Razširitev referenc v Excelu | Naraščajoča referenca se pri kopiranju navzdol ali desno razširi. Za to uporabljamo znak $ pred številko stolpca in vrstice. Tukaj je en primer…
Vse o absolutnih referencah | Privzeta referenčna vrsta v Excelu je relativna, če pa želite, da so sklice na celice in obsege absolutne, uporabite znak $. Tu so vsi vidiki absolutnega sklicevanja v Excelu.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. Teh 50 bližnjic bo vaše delo v Excelu še pospešilo.
Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.