Kombinirana upotreba sumifa (vlookup)
Sumif s VLOOKUP-om kombinacija je dviju različitih uvjetnih funkcija, SUMIF se koristi za zbrajanje stanica na temelju nekog uvjeta koji uzima argumente opsega koji ima podatke, a zatim kriterije ili uvjet i stanice za zbrajanje, umjesto kriterija koje koristimo VLOOKUP kao kriterij kada je u više stupaca dostupna velika količina podataka.
SUMIF je funkcija predstavljena u programu Excel iz verzije 2007. za zbrajanje različitih vrijednosti koja se podudaraju s kriterijima. VLOOKUP je jedna od najboljih formula za prikupljanje podataka iz drugih tablica. Kada postoji više uvjeta i stupaca, sumif (vlookup) koristi se za izvođenje višestrukih izračuna na excel listu. Nedostatak funkcije SUMIF koja vraća jedini broj prevladava se pomoću VLOOKUP-a. VLOOKUP pomaže vratiti bilo koju vrstu podataka iz tablice na temelju odgovarajućih kriterija.
Obrazloženje
SUMIF funkcija: Funkcija trigonometrije i matematike zbraja vrijednosti kada je utvrđeni uvjet istinit. Zbirna vrijednost dobiva se na temelju samo jednog kriterija.
Kad se bavimo funkcijom SUMIF u Excelu, koristi se sljedeća formula
- Raspon: To je raspon stanica korištenih za procjenu utvrđenih kriterija
- Kriteriji: Uvjet je zbrajanja vrijednosti. To može biti referenca stanice, broj i još jedna excel funkcija. Kada želimo kombinirati SUMIF i VLOOKUP, funkcija vlookup bit će unesena na mjesto kriterija
- Raspon zbroja : To je raspon ćelija naveden za zbrajanje numeričkih vrijednosti.
Sada je formula izmijenjena u
Formula = SUMIF (Raspon, Vlookup (vrijednost pretraživanja, niz tablice, indeksni broj stupca, [traženje raspona]), [raspon zbroja])
- Vrijednost pretraživanja: Određuje vrijednost koju treba pretraživati u tablici. To može biti referenca ili vrijednost.
- Polje tablice: To je raspon tablice koji sadrži dva ili više od dva stupca.
- Broj indeksa stupca: To je relativni indeks stupca koji se navodi za vraćanje potrebnih podataka iz određenog stupca.
- [Pregled raspona]: Ili je 0 ili 1 da biste odredili treba li vratiti točnu vrijednost ili približnu vrijednost. Ali, to nije obavezno za korisnika. 0 označava točno podudaranje, a 1 približno podudaranje.
Kako koristiti SUMIF s funkcijom VLOOKUP?
Kombinirana upotreba sumifa (vlookup) korisna je u pretraživanju podataka na temelju pojedinih kriterija. U Excel-u se na više načina koriste za izračun izračunavanjem pretraživanjem podataka. Oni se zajednički koriste u poslovnom okruženju za obavljanje različitih zadataka za donošenje dobrih odluka. Da bismo zajedno učinkovito koristili ove funkcije,
Prvo treba unijeti funkciju SUMIF koristeći dvije metode.
Prva metoda: Formulu treba tipkati s tipkovnice kao što je prikazano na donjoj slici.
Druga metoda: SUMIF funkcija može se umetnuti s kartice "Formula" kako je prikazano na slici.
Nakon unosa funkcije SUMIF, formula za VLOOKUP unosi se unutar funkcije SUMIF zamjenom elementa 'Kriteriji'. Svi parametri VLOOKUP-a, uključujući vrijednost pretraživanja, polje tablice, indeksni broj stupca i pretraživanje raspona. Treba ih priložiti u zagrade kako bi se izbjegle pogreške u formuli. Raspon vrijednosti koje se zbrajaju uključeni su u elemente raspona zbroja funkcije SUMIF. Napokon su pritisnute tipke CTRL, SHIFT i ENTER kako bi olakšale vrijednosti kao niz.
Primjeri
Ovdje možete preuzeti ovaj SUMIF s VLOOKUP Excel predloškom - SUMIF s VLOOKUP Excel predloškomPrimjer # 1 - Upotreba sumifa (vlookup) za određivanje neke vrijednosti
Ovaj primjer pokazuje kako zajedno koristiti sumif (vlookup) za pronalaženje zbroja prodaje u istom mjesecu u različitim godinama. Sljedeći se podaci uzimaju u obzir za ovaj primjer, kao što je prikazano na snimci zaslona.
Razmatrana tablica pregleda prikazana je kako je spomenuto u nastavku. Obuhvatio je referentne vrijednosti za mjesece od siječnja do prosinca.
Nakon unosa podataka u glavnu tablicu i tablicu pretraživanja, funkcija SUMIF koristi se za određivanje ukupne prodaje ostvarene u različitim mjesecima u godini. Ovdje se uzima u obzir vrijednost pretraživanja mjesec. Formula koja kombinira sumif (vlookup) prikazana je kao,
Ukupna prodaja u mjesecu siječnju utvrđena je kao 17263,3 . Kada smo promijenili vrijednost izgleda u drugi mjesec, generira se odgovarajuća ukupna prodaja.
Primjer # 2 - Određivanje zbroja na temelju podudaranja kriterija u različitim radnim listovima
U ovom se primjeru tablica pretraživanja i glavna tablica uzimaju u različite listove, a ne u jedan list. Podaci tablice pretraživanja prikazani su na donjem snimku zaslona.
Podaci glavne tablice prikazani su na donjem snimku zaslona.
Da bi se utvrdila ukupna prodaja, ime prodavača uzima se kao vrijednost pretraživanja, a identifikacijski podaci zaposlenika koriste se u referentne svrhe. Formula se unosi kako je prikazano u nastavku i uočava se malo promjena u formuli u usporedbi s prvim primjerom.
U ovom se primjeru, umjesto odabira niza pretraživanja, spominje samo Lookup_table. Pritiskom na tri tipke, uključujući CTRL, SHIFT i ENTER, dobivaju se točni rezultati.
Kada se ime prodavača promijeni u imena spomenuta u traženju, zbroj prodaje varira i daje novi rezultat.
Prednosti
Slijede prednosti ovog korištenja funkcija.
- Vrijednosti možemo lako izvući iz druge tablice za obavljanje izračuna.
- Određivanje zbroja vrijednosti predstavljenih u rasponu koji udovoljava kriterijima spomenutim u različitim aspektima poslovanja.
Stvari koje treba zapamtiti
- Tijekom upotrebe funkcije VLOOKUP, broj indeksa stupca ne smije biti manji od 1 kako bi se izbjegle pogreške.
- Indekse treba dati stupcima tablice pretraživanja označavanjem brojevima 1, 2, 3 i tako dalje.
- CTRL + SHIFT + ENTER u Excelu treba koristiti umjesto unosa Key jer se VLOOKUP unosi kao formula niza.
- Za izdvajanje vrijednosti i određivanje zbroja vrijednosti polja potrebno je definirati dvije tablice, uključujući Main i Lookup.
- SUMIF pruža točne rezultate samo za numeričke podatke, neće raditi za drugu vrstu podataka.