Dakle, već smo naučili što je 3D referenca 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.
Opća formula za SUMIF s 3D referencom u Excelu
Izgleda komplicirano, ali nije (toliko).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Range_range"), kriteriji, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" naziv_druga_na_nazivima_tablica "'":To je imenovani raspon koji sadrži nazive listova. Ovo je veoma važno.
"raspon_kriterija":To je tekstualna referenca kriterija koji sadrži raspon. (Trebalo bi biti isto na svim listovima za 3-D referentni rad.)
kriteriji:To je jednostavno uvjet koji želite staviti za zbrajanje. To može biti tekstualna referenca ili referenca ćelije.
"sum_range":To je tekstualna referenca raspona zbroja. (Trebalo bi biti isto na svim listovima za 3-D referentni rad.)
Dosta je bilo teorije, idemo na 3D referenciranje s funkcijom SUMIF.
Primjer: Zbroj po regijama iz više listova pomoću 3D referenciranja programa Excel:
Uzimamo iste podatke koje smo uzeli u jednostavnom primjeru 3D referenciranja. U ovom primjeru imam pet različitih listova koji sadrže slične podatke. Svaki list sadrži podatke za mjesec dana. U glavnom listu želim zbroj jedinica i prikupljanje po regijama sa svih listova. Učinimo to prvo za Jedinice. Jedinice su u rasponu D2: D14 na svim listovima.
Sada, ako koristite normalnu 3D referenciranje s funkcijom SUMIF,
= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)
Vratit će #VREDNOST! pogreška. Zato ga ne možemo koristiti. Koristit ćemo gore spomenutu generičku formulu.
Koristeći gornju generičku 3D referencirajuću SUMIF formulu u Excelu, upišite ovu formulu u ćeliju C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Months & "'!" & "D2: D14"))) |
Ovdje mjeseci je imenovani raspon koji sadrži nazive listova. Ovo je ključno.
Kada pritisnete enter, dobit ćete točan ispis.
Kako radi?
Jezgra formule su INDIRECT funkcija i imenovani raspon. Ovdje niz"'" & Mjeseci & "'!" & "A2: A14"prevodi u niz referenci raspona svakog lista u imenovani raspon.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Ovaj niz sadrži tekstualna referencaraspona, a ne stvarnih raspona. Budući da se radi o tekstualnoj referenci, funkcija INDIRECT može ga koristiti za pretvaranje u stvarne raspone. To se događa za obje funkcije INDIRECT. Nakon rješavanja tekstova unutar funkcija INDIRECT (držite se čvrsto), formula izgleda ovako:
| = SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Master! B3, INDIREKTNO ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"}))) |
Sada stupa na snagu funkcija SUMIF (ne INDIREKTNA, kao što ste možda pretpostavili). Uvjet je usklađen s prvim rasponom"'Jan'! A2: A14". Ovdje funkcija INDIRECT radi dinamički i pretvara ovaj tekst u stvarni raspon (zato ako prvo pokušate riješiti INDIRECT pomoću tipke F9, nećete dobiti rezultat). Sljedeće sažima podudarne vrijednosti u rasponu"'Jan'! D2: D14".To se događa za svaki raspon u nizu. Konačno, imat ćemo niz vraćen funkcijom SUMIF.
| = PODIZVOD ({97; 82; 63; 73}) |
Sada SUMPRODUCT radi ono što najbolje radi. Zbraja ove vrijednosti i naša 3D SUMIF funkcija radi.
Pa da, dečki, ovako možete postići 3D SUMIF funkciju. Ovo je malo složeno, slažem se s tim. U ovoj 3D formuli postoji mnogo prostora za pogreške. Predlažem da upotrijebite funkciju SUMIF na svakom listu u određenoj ćeliji, a zatim koristite uobičajene 3D reference za zbrajanje tih vrijednosti.
Nadam se da sam vam dovoljno objasnio. Ako imate bilo kakvih sumnji u vezi s Excel -om koji se odnosi na bilo koji drugi upit vezan uz Excel/VBA, pitajte u odjeljku komentara u nastavku.
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 $. Evo svih aspekata apsolutnih referenci 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 da radite još brže na 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.