U ovom ćemo članku naučiti kako koristiti funkciju IF umjesto funkcija SUMPRODUCT i SUMIFS u Excelu.
Scenarij:
Jednostavnim riječima, pri radu s dugo raštrkanim skupom podataka ponekad moramo pronaći zbroj brojeva s nekim kriterijima iznad njega. Na primjer, pronalaženje zbroja plaća u određenom odjelu ili postavljanje više kriterija po datumu, imenima, odjelima ili čak brojevima podataka poput plaća ispod vrijednosti ili količine iznad vrijednosti. Za to obično koristite funkciju SUMPRODUCT ili SUMIFS. Ali ne biste vjerovali, istu funkciju obavljate s osnovnom funkcijom programa Excel funkciju IF.
Kako riješiti problem?
Sigurno razmišljate kako je to moguće izvesti logičke operacije nad nizovima tablica pomoću IF funkcije. IF funkcija u Excelu je vrlo korisna, proći će vas kroz neke teške zadatke u Excelu ili bilo kojem drugom jeziku za kodiranje. IF funkcija testira uvjete na nizu koji odgovaraju potrebnim vrijednostima i vraća rezultat kao niz koji odgovara True uvjetima kao 1 i False kao 0.
Za ovaj problem koristit ćemo sljedeće funkcije:
- SUM funkcija
- IF funkcija
Trebat će nam ove gore navedene funkcije i neki osnovni smisao rada s podacima. logički uvjeti na nizove mogu se primijeniti pomoću logičkih operatora. Ovi logički operatori rade i na tekstu i na brojevima. Ispod je generička formula. { } kovrčave zagrade čarobni su alat za izvođenje formula polja s funkcijom IF.
Opća formula:
| { = SUM (IF ((logičko_1) * (logičko_2) *… * (logičko_n), zbir_marica)) } |
Napomena: Za kovrčave aparatiće ( { } ) Koristiti Ctrl + Shift + Enter pri radu s nizovima ili rasponima u Excelu. To će prema zadanim postavkama generirati kovrčave zagrade na formuli. NE pokušavajte kodirati znakove kovrčavih zagrada.
Logično 1: testira uvjet 1 na nizu 1
Logično 2: testira uvjet 2 na nizu 2 i tako dalje
sum_array: niz, izvodi se zbir operacija
Primjer :
Sve ovo može biti zbunjujuće za razumijevanje. Dakle, isprobajmo ovu formulu tako da je pokrenemo na dolje prikazanom primjeru. Ovdje imamo podatke o isporučenim proizvodima u različite gradove zajedno s poljima odgovarajućih kategorija i količinama. Ovdje imamo podatke i moramo pronaći količinu kolačića poslanih u Boston gdje je količina veća od 40.
Tablica podataka i tablica kriterija prikazani su na gornjoj slici. U svrhu razumijevanja koristili smo imenovane raspone za korištene nizove. Imenovani rasponi navedeni su u nastavku.
Ovdje:
Grad definiran za niz A2: A17.
Kategorija definirana za niz B2: A17.
Količina definirana za niz C2: C17.
Sada ste spremni postići željeni rezultat pomoću formule ispod.
Upotrijebite formulu:
| { = SUM (IF ((Grad = "Boston") * (Kategorija = "Kolačići") * (Količina> 40), Količina)) } |
Objašnjenje:
- City = "Boston": provjerava vrijednosti u rasponu gradova kako bi se podudarale s "Boston".
- Kategorija = "Kolačići": provjerava da li se vrijednosti u rasponu kategorija podudaraju s "kolačićima".
- Količina> 40: provjerava vrijednosti u rasponu količina na ma
- Količina je niz gdje je potreban zbroj.
- IF funkcija provjerava sve kriterije i zvjezdica char (*) množi sve rezultate niza.
= SUM (IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Sada IF funkcija vraća samo količine koje odgovaraju 1s, a ostatak se zanemaruje.
- Funkcija SUM vraća SUM.
Sada se količina koja odgovara 1 zbraja samo da bi se dobio rezultat.
Kao što vidite, vraća se količina 43, ali tri su narudžbe kolačića isporučene u "Boston" u količinama 38, 36 i 43. Trebao nam je zbir količine gdje je količina veća od 40. Dakle, formula vraća samo 43. Sada upotrijebite druge kriterije da biste dobili SUM Količina za Grad: "Los Angeles" & Kategorija: "Šipke" & Količina bila manja od 50.
Koristite formulu
| { = SUM (IF ((Grad = "Los Angeles") * (Kategorija = "Šipke") * (Količina <50), Količina)) } |
Kao što vidite, formula kao rezultat vraća vrijednosti 86. To je zbroj 2 naloga koji zadovoljavaju uvjete s količinom 44 i 42. Ovaj članak ilustrira kako zamijeniti ugniježđenu formulu IF s jednim IF u formuli niza. To se može koristiti za smanjenje složenosti složenih formula. Međutim, ovaj bi se problem mogao lako riješiti pomoću funkcije SUMIFS ili SUMPRODUCT.
Upotreba funkcije SUMPRODUCT:
Funkcija SUMPRODUCT vraća zbroj odgovarajućih vrijednosti u nizu. Dakle, dobit ćemo nizove koji vraćaju 1s a vrijednosti True naredbe i 0s vrijednosti False izraza. Dakle, posljednji zbroj bit će odgovarajući tamo gdje sve izjave stoje Tačno.
Koristite formulu:
| = SUMPRODUCT ( - (Grad = "Boston"), - (Kategorija = "Kolačići"), - (Količina> 40), Količina) |
-: operacija koja se koristi za pretvaranje svih TRUE u 1s i False u 0.
Funkcija SUMPRODUCT ponovno provjerava SUM količine koju vraćaju gore objašnjena funkcija SUM i IF.
Slično, za drugi primjer rezultat ostaje isti.
Kao što vidite, funkcija SUMPRODUCT može izvesti isti zadatak.
Ovdje su sve napomene o primjeni formule.
Bilješke:
- Zbir_masa u formuli radi samo s brojevima.
- Ako formula vraća pogrešku #VALUE, provjerite moraju li u formuli biti prisutni uvijeni zagradi kako je prikazano u primjerima u članku.
- Negacija (-) char mijenja vrijednosti, TRUE ili 1s u FALSEs ili 0s i FALSEs ili 0s u TRUEs ili 1s.
- Operacije poput jednake ( = ), manje od jednakog ( <= ), veće od ( > ) ili nije jednako () može se izvesti unutar primijenjene formule, samo s brojevima.
Nadamo se da je ovaj članak o tome kako koristiti funkciju IF umjesto funkcija SUMPRODUCT i SUMIFS u Excelu objašnjen. Više članaka o formulama zbrajanja potražite ovdje. Ako su vam se svidjeli naši blogovi, podijelite ih sa svojim početnicima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu.
ZBIRAJTE ako je datum između : Vraća ZBOR vrijednosti između zadanih datuma ili razdoblja u Excelu.
Zbroj ako je datum veći od navedenog datuma: Vraća ZBOR vrijednosti nakon zadanog datuma ili razdoblja u Excelu.
2 načina zbrajanja po mjesecima u Excelu: Vraća ZBOR vrijednosti unutar određenog određenog mjeseca u Excelu.
Kako zbrojiti više stupaca s uvjetom: Vraća ZBOR vrijednosti u više stupaca sa uvjetom u excelu.
Popularni članci:
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija 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.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. 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.