Ako želite dobiti zbroj stupca samo pomoću naziva stupca, to možete učiniti na 3 jednostavna načina u Excelu. Istražimo ove načine.
Za razliku od drugih članaka, pogledajmo prvo scenarij.
Ovdje imam tablicu prodaje koju su radili različiti prodavači u različitim mjesecima.
Sada je zadatak dobiti zbroj mjesečne prodaje u ćeliji C10. Ako promijenimo mjesec u B10, zbroj bi se trebao promijeniti i vraća zbroj tog mjeseca, bez promjene bilo čega u formuli.
Metoda 1: Zbroj cijelog stupca u tablici pomoću funkcije SUMPRODUCT.
Sintaksa metode SUMPRODUCT za zbrajanje stupca podudaranja je:
| = SUMPRODUCT ((stupci)*(zaglavlja = naslov)) |
Kolone:To je dvodimenzionalni raspon stupaca koje želite zbrojiti. Ne smije sadržavati zaglavlja. U gornjoj tablici je C3: N7.
Zaglavlja:To je raspon zaglavlja stupcima koje želite zbrojiti. U gornjim podacima to je C2: N2.
Naslov: To je naslov koji želite uskladiti. U gornjem primjeru nalazi se u B10.
Bez daljnjeg odlaganja poslužimo se formulom.
| = PODIZVOD ((C3: N7)*(C2: N2 = B10)) |
i ovo će se vratiti:
Kako radi?
Jednostavno je. U formuli, izjavaC2: N2 = B10 vraća niz koji sadrži sve FALSE vrijednosti osim one koja odgovara B10. Sada je formula
| =SUMPRODUCT ((C3: N7)*{FALSE, FALSE, FALSE, FALSE,PRAVI, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}) |
Sada se C3: N7 množi na svaku vrijednost ovog niza. Svaki stupac postaje nula osim stupca koji se množi s TRUE. Sada formula postaje:
| = SUMPRODUCT ({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}) |
Sada se ovaj niz sažima i dobivamo zbroj stupca koji odgovara stupcu u ćeliji B10.
Metoda 2: Zbroj cijelog stupca u tablici pomoću funkcije INDEX-MATCH.
Sintaksa metode za zbrajanje naslova odgovarajućeg stupca u Excelu je:
| = SUM (INDEX (stupci ,, MATCH (naslov, zaglavlja, 0))) |
Sve varijable u ovoj metodi iste su kao i metoda SUMPRODUCT. Idemo ga samo implementirati kako bismo riješili problem. Napišite ovu formulu u C10.
| = ZBIR (INDEKS (C3: N7,, MATCH (B10, C2: N2,0))) |
Ovo vraća:
Kako radi?
Formula je riješena iznutra prema van. Prvo, funkcija MATCH vraća indeks odgovarajućeg mjeseca iz raspona C2: N2. Budući da imamo svibanj u B1o, dobivamo 5. Sada formula postaje
| = ZBIR (INDEKS (C3: N7,, 5)) |
Zatim funkcija INDEX vraća vrijednosti iz 5. stupca C3: N7. Sada formula postaje:
| = ZBIR ({6; 12; 15; 15; 8}) |
I na kraju, dobivamo zbroj ovih vrijednosti.
Metoda 3: Zbroj cijelog stupca u tablici pomoću imenovanog raspona i funkcije INDIRECT
Sve postaje jednostavno ako svoje raspone imenujete kao naslove stupaca. U ovoj metodi prvo moramo stupcima dati nazive naslova.
Odaberite tablicu s naslovima i pritisnite CTRL+SHIFT+F3. Otvorit će se dijaloški okvir za stvaranje naziva iz raspona. Provjerite gornji red i pritisnite gumb OK.
Imenovat će sve stupce podataka kao njihove naslove.
Sada će opća formula za zbrajanje odgovarajućeg stupca biti:
| = SUM (INDIREKTNO (naslov)) |
Naslov: To je naziv stupca koji želite zbrojiti. U ovom primjeru, B10 zasad sadrži svibanj.
Da biste implementirali ovu generičku formulu, upišite ovu formulu u ćeliju C10.
| = ZBIR (INDIREKTNO (B10)) |
Ovo vraća zbroj mjeseca svibnja:
Druga metoda je slična ovoj. U ovoj metodi koristimo Excel tablice i strukturirano imenovanje. Recimo ako ste gornjoj tablici dali ime table1. Tada će ova formula raditi isto kao gornja formula.
| = SUM (INDIREKTNO ("Tablica1 [" & B10 & "]")) |
Kako radi?
U ovoj formuli funkcija INDIRECT uzima referencu imena i pretvara je u stvarnu referencu imena. Postupak nadalje je jednostavan. Funkcija SUM sažima imenovani raspon.
Pa da, dečki, ovako možete zbrojiti odgovarajući stupac u Excelu. Nadam se da vam je od pomoći i objašnjenja. Ako imate bilo kakvih nedoumica u vezi ovog članka ili bilo koje druge teme vezane uz Excel/VBA, pitajte u odjeljku komentara ispod.
Kako zbrojiti podudaranje retka i stupca u Excelu |SUMPRODUCT je najsvestranija funkcija kada je u pitanju zbrajanje i brojanje vrijednosti sa lukavim kriterijima. Opća funkcija za zbrajanje podudaranjem stupca i retka je …
SUMIF s 3D referencom u Excelu |Zabavna je činjenica da uobičajeno Excel 3D referenciranje ne radi s uvjetnim funkcijama, poput funkcije SUMIF. U ovom ćemo članku naučiti kako 3D referenciranje raditi s funkcijom SUMIF.
Relativna i apsolutna referenca u Excelu | Referenciranje u excelu važna je tema za svakog početnika. Čak i iskusni korisnici programa Excel rade greške u pozivanju na referentne stranice.
Referenca dinamičkog radnog lista | Dajte referentne listove dinamički pomoću INDIRECT funkcije programa Excel. Ovo je jednostavno…
Proširivanje referenci u Excelu | Proširujuća referenca širi se pri kopiranju prema dolje ili udesno. Za to koristimo znak $ prije broja stupca i retka. Evo jednog primjera…
Sve o apsolutnim referencama | Zadana vrsta reference u Excelu je relativna, ali ako želite da referenca ćelija i raspona bude apsolutna, upotrijebite znak $. Ovdje su svi aspekti apsolutnog upućivanja u Excelu.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će vaš rad još bržim u Excelu.
Funkcija VLOOKUP u Excelu | Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
COUNTIF u Excelu 2016 | Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste računali određenu vrijednost. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.