V tem članku se bomo naučili, kako ustvariti izračunane stolpce v Excelu.
Scenarij
Č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 z različnimi scenariji.
Kako ustvariti izračunani stolpec v tabeli:
- Izberite celico v enem od stolpcev tabele ali prazno celico desno do zadnjega stolpca, v spodnjem primeru je izbrana celica G2.
- Vstavite formulo, ki izračuna prodajo na enoto, vnesite simbol enak (=), izberite celico F2, vnesite simbol za deljenje (/), nato izberite celico E2 in pritisnite Enter.
Formula se samodejno kopira v vse celice v stolpcu.
Primer:
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 vrniti 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 izjava C2: 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(KAZALO(stolpci,,UJEMA(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(KAZALO(C3: N7 ,,UJEMA(B10, C2: N2,0))) |
To se vrne:
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(POSREDNO(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(NEPOSREDNO ("Tabela1 [" & B10 & "]")) |
Upam, da je ta članek o tem, kako ustvariti izračunane stolpce v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
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 absolutni referenci: 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:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo 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.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.