Do sada smo naučili kako zbrojiti cijeli stupac podudaranja tablice u Excelu. Ali kako zbrojiti vrijednosti kada moramo uskladiti stupac i redak. U ovom ćemo članku naučiti kako zbrojiti odgovarajuće redove i stupce.
Postoje dvije formule za to, ali prvo, dopustimo nam scenarij.
Ovdje imam tablicu koja bilježi prodaju koju su zaposlenici obavili u različitim mjesecima. Ime zaposlenika može se ponavljati. Pogledajte donju sliku:
Moramo dobiti zbroj svibanjskog mjeseca u kojem je prodavač Donald.
Metoda 1: Zbrajanje odgovarajućeg zaglavlja stupca i zaglavlja pomoću funkcije SUMPRODUCT.
The SUMPRODUCT funkcija je najsvestranija funkcija kada se radi o zbrajanju i računanju vrijednosti sa lukavim kriterijima. Opća funkcija za zbrajanje podudaranjem stupca i retka je:
| = SUMPRODUCT ((stupci)*(zaglavlja stupaca = zaglavlje stupca)*(zaglavlja redaka = zaglavlje redaka) |
Kolone:To je dvodimenzionalni raspon stupaca koje želite zbrojiti. Ne smije sadržavati zaglavlja. U gornjoj tablici je C3: N7.
zaglavlja_ stupaca:To je raspon zaglavljastupcima koje želite zbrojiti. U gornjim podacima to je C2: N2.
naslov_ stupca: To je naslov koji želite uskladiti. U gornjem primjeru nalazi se u B13.
Bez daljnjeg odlaganja poslužimo se formulom.
zaglavlja_reda:To je raspon zaglavljaredove koje želite zbrojiti. U gornjim podacima to je B3: B10.
redak_zaglavlje: To je naslov koji želite uskladiti u redovima. U gornjem primjeru je u F13.
Bez daljnjeg odlaganja poslužimo se formulom.
Napišite ovu formulu u ćeliju D13 i dopustite Excelu da učini magiju (magija ne postoji) …
| = PODIZVOD ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Ovo vraća vrijednost:
Sada, kada promijenite mjesec ili prodavača, zbroj će se promijeniti prema naslovu retka i stupcu.
Kako radi?
Ova jednostavna logička logika.
(C2: N2 = B13): Ova naredba će vratiti niz TRUE i FALSE. Sve podudarne vrijednosti u stupcu imaju vrijednost true, a ostale će imati vrijednost false. U ovom slučaju imat ćemo samo jedno True jer raspon C2: N2 sadrži samo jednu instancu svibanj na 5th Mjesto.
(B3: B10 = E13): Ovo će raditi isto kao gore i vratit će niz TRUE i FALSE. Sve podudarne vrijednosti će imati TRUE, a ostale će imati FALSE. U ovom slučaju imat ćemo 2 ISTINE jer raspon B3: B10 ima dvije instance "Donalda".
(C2: N2 = B13)*(B3: B10 = E13): Sada množimo nizove vraćene izrazima. Ovo će implementirati i logiku i dobit ćemo niz 1s i 0s. Sada ćemo imati 2D niz koji će sadržavati 2 1s i ostale 0.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Na kraju množimo 2D niz s 2D tablicom. Ponovno će vratiti niz 0 i brojeve koji odgovaraju kriterijima.
Konačno, PODUZIMAK funkcija će zbrojiti niz što će rezultirati željenim izlazom.
Metoda 2: Zbrajanje odgovarajućeg zaglavlja stupca i zaglavlja pomoću funkcije SUM i IF
Opća formula za zbrajanje odgovarajućih redaka i stupaca pomoću funkcije SUM i IF Excel je:
| = SUM (IF (zaglavlja stupaca = zaglavlje stupca, IF (zaglavlja redaka = zaglavlje redaka, stupci))) |
Sve varijable su iste kao u gore objašnjenoj metodi. Ovdje ih samo treba koristiti drugačijim redoslijedom.
Napišite ovu formulu u ćeliju D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Ovo vraća točan odgovor. U nastavku pogledajte snimak zaslona:
Kako radi?
Logika je ista kao prva metoda SUMPRODCUT samo je mehanizam drugačiji. Ako to ukratko objasnim, unutarnja funkcija IF vraća 2D niz iste dimenzije kao tablica. Ovaj niz sadrži broj dva podudarna retka. Zatim unutarnja funkcija IF odgovara naslovima s dva stupca u ovom nizu i vraća 2D niz koji sadrži samo brojeve koji odgovaraju i stupcu i zaglavlju. Svi ostali elementi niza bit će FALSE.
Konačno, funkcija SUM sažima ovaj niz i dobivamo zbroj.
Pa da, dečki, ovako možete sažeti podudarne retke i stupce iz tablice u Excelu. Nadam se da vam je bilo objašnjenje i korisno. Ako imate bilo kakvih sumnji u vezi s ovom temom ili bilo koje druge sumnje vezane uz Excel/VBA, pitajte u odjeljku komentara ispod.
Kako zbrojiti stupac u Excelu podudaranjem naslova | Ako želite dobiti zbroj stupca samo pomoću naziva stupca, to možete učiniti na 3 jednostavna načina u Excelu. Sintaksa metode SUMPRODUCT za zbrajanje stupca podudaranja 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.