Če želite pridobiti vsoto stolpca z uporabo imena stolpca, lahko to storite na 3 preproste načine v Excelu. Raziščimo te načine.
Za razliko od drugih člankov, poglejmo najprej scenarij.
Tukaj imam preglednico prodaje, ki so jo v različnih mesecih naredili različni prodajalci.
Zdaj je naloga pridobiti vsoto prodaje za določen mesec v celici C10. Če spremenimo mesec v B10, se mora vsota spremeniti in vrne vsoto tega meseca, ne da bi pri tem kaj spremenili v formuli.
Metoda 1: Vsota celotnega stolpca v tabeli z uporabo funkcije SUMPRODUCT.
Sintaksa metode SUMPRODUCT za seštevanje ujemajočega se stolpca je:
| = SUMPRODUCT ((stolpci)*(glave = naslov)) |
Stolpci:To je dvodimenzionalno območje stolpcev, ki ga želite sešteti. Ne sme vsebovati glav. V zgornji tabeli je C3: N7.
Glave:To je obseg glave stolpci ki jih želite povzeti. V zgornjih podatkih je C2: N2.
Naslov: To je naslov, ki se mu želite ujemati. V zgornjem primeru je v B10.
Brez odlašanja uporabimo formulo.
| = PODROČJE ((C3: N7)*(C2: N2 = B10)) |
in to se bo vrnilo:
Kako deluje?
Preprosto je. V formuli je izjavaC2: N2 = B10 vrne matriko, ki vsebuje vse vrednosti FALSE, razen tiste, ki se ujema z B10. Zdaj je formula takšna
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,PRAV, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Zdaj se C3: N7 pomnoži z vsako vrednostjo tega niza. Vsak stolpec postane nič, razen stolpca, pomnoženega z TRUE. Zdaj formula postane:
| = PODROČJE ({0,0,0,0,6,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,15,0,0,0,0,0,0,0; 0,0,0,0,8,0,0,0,0,0,0,0}) |
Zdaj se ta niz sešteje in dobimo vsoto stolpca, ki se ujema s stolpcem v celici B10.
Metoda 2: Vsota celotnega stolpca v tabeli z uporabo funkcije INDEX-MATCH.
Sintaksa metode za seštevanje ujemajočega se naslova stolpca v Excelu je:
| = SUM (INDEX (stolpci ,, MATCH (naslov, glave, 0))) |
Vse spremenljivke v tej metodi so enake metodi SUMPRODUCT. Uresničimo ga, da rešimo težavo. To formulo zapišite v C10.
| = SUM (INDEX (C3: N7,, MATCH (B10, C2: N2,0))) |
To se vrne:
Kako deluje?
Formula je rešena navznoter. Najprej funkcija MATCH vrne indeks ustreznega meseca iz območja C2: N2. Ker imamo maj v B1o, dobimo 5. Zdaj formula postane
| = SUM (INDEKS (C3: N7,, 5)) |
Nato funkcija INDEX vrne vrednosti iz 5. stolpca C3: N7. Zdaj formula postane:
| = SUM ({6; 12; 15; 15; 8}) |
In na koncu dobimo vsoto teh vrednosti.
Metoda 3: Vsota celotnega stolpca v tabeli z uporabo imenovanega obsega in funkcije INDIRECT
Vse je preprosto, če svoje obsege poimenujete kot naslove stolpcev. Pri tej metodi moramo najprej poimenovati stolpce kot njihova imena naslovov.
Izberite tabelo z naslovi in pritisnite CTRL+SHIFT+F3. Odprlo se bo pogovorno okno za ustvarjanje imena iz obsegov. Preverite zgornjo vrstico in pritisnite gumb V redu.
Vse stolpce s podatki bo poimenoval kot njihove naslove.
Zdaj bo splošna formula za seštevanje ujemajočega se stolpca:
| = SUM (INDIREKTNO (naslov)) |
Naslov: To je ime stolpca, ki ga želite sešteti. V tem primeru je trenutno B10, ki vsebuje lahko.
Za izvajanje te splošne formule zapišite to formulo v celico C10.
| = SUM (POSREDNO (B10)) |
To vrne vsoto meseca maja:
Druga metoda je podobna tej. Pri tej metodi uporabljamo Excelove tabele in njeno strukturirano poimenovanje. Recimo, če ste zgornjo tabelo poimenovali tabela1. Potem bo ta formula delovala enako kot zgornja formula.
| = SUM (INDIREKTNO ("Tabela1 [" & B10 & "]")) |
Kako deluje?
V tej formuli funkcija INDIRECT vzame sklic na ime in ga pretvori v dejansko referenco imena. Postopek naprej je preprost. Funkcija SUM povzame imenovani obseg.
Torej ja, fantje, tako lahko v Excelu seštejete ujemajoči se stolpec. Upam, da vam bo v pomoč in razlago. Če dvomite o tem članku ali kateri koli drugi temi, povezani z excelom/VBA, vprašajte v spodnjem razdelku za komentarje.
Kako sešteti z ujemanjem vrstice in stolpca v Excelu |SUMPRODUCT je najbolj vsestranska funkcija, ko gre za seštevanje in štetje vrednosti z zapletenimi merili. Splošna funkcija, ki jo je treba sešteti po ujemanju stolpca in vrstice, 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.