Excel SUMIFS: vsota samo vrednosti izpolnjuje več kriterijev

Funkcija SUMIFS razširi uporabnost funkcije SUMIF tako, da vam omogoča, da določite od 2 do 127 meril in ne samo eno kot v SUMIF.

Običajno SUMIFS deluje z vrsticami podatkov, ki se imenujejo zapisi. V zapisu so vsi podatki v vsaki celici ali polju v vrstici povezani - na primer ime podjetja, naslov in telefonska številka.

SUMIFS išče specifična merila v dveh ali več poljih v zapisu in samo, če ugotovi, da je ujemanje za vsako navedeno polje povzete podatki za ta zapis.

01 od 10

Kako funkcijo SUMIFS deluje

V SUMIF korak za korakom smo se ujemali z enotnim merilom prodajnih agentov, ki so v enem letu prodali več kot 250 naročil.

V tem tutorialu bomo postavili dva pogoja s pomočjo SUMIFS - prodajnih posrednikov v vzhodni prodajni regiji, ki sta v preteklem letu imeli manj kot 275 prodaj.

Nastavitev več kot dveh pogojev je mogoče narediti tako, da določite dodatne argumente Criteria_range in Criteria za SUMIFS.

Po korakih v spodnjih tutorijih spodaj gre skozi ustvarjanje in uporabo funkcije SUMIFS, ki je prikazana na zgornji sliki.

Vadnice Teme

• Vnos podatkov o vadnicah
• Sintaksa funkcije SUMIFS
• Zagon funkcije SUMIFS
• Argument Sum_range
• Argument Criteria_range1
• Kriterij1 Argument
• Argument Criteria_range2
• Kriterij2 Argument

02 od 10

Vnos podatkov o vadnicah

Prvi korak pri uporabi funkcije SUMIFS v Excelu je vnos podatkov.

Vnesite podatke v celice D1 do F11 iz delovnega lista Excel, kot je prikazano na zgornji sliki.

Funkcija SUMIFS in iskalni kriteriji (manj kot 275 naročil in prodajnih agentov iz vzhodne prodajne regije) bodo dodane v vrstico 12 pod podatki.

Navodila za tutorial ne vključujejo korakov oblikovanja za delovni list.

To ne bo motilo dokončanja vadnice. Vaš delovni list se bo razlikoval od prikazanega primera, vendar vam bo funkcija SUMIFS dala enake rezultate.

03 od 10

Sintaksa funkcije SUMIFS

V Excelu se sintakso funkcije nanaša na postavitev funkcije in vključuje ime, oklepaj in argumente funkcije.

Sintaksa za funkcijo SUMIFS je:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Opomba: V funkciji je mogoče določiti do 127 kriterijev Criteria_range / Criteria .

Argumenti SUMIFS funkcije

Argumenti funkcije povejo funkcijo, za katere pogoje se preskušajo, in kakšen obseg podatkov se izračuna, ko so ti pogoji izpolnjeni.

Vsi argumenti v tej funkciji so potrebni.

Sum_range - podatki v tem obsegu celic se zberejo, ko se najde zadetek med vsemi določenimi kriteriji in njihovimi ustreznimi argumenti Criteria_range .

Criteria_range - skupina celic, ki jo funkcija najde za ujemanje z ustreznim argumentom Kriteriji .

Merila - ta vrednost se primerja s podatki v ustreznem kriteriju . Za ta argument lahko vnesemo dejanske podatke ali sklicevanje na celice na podatke.

04 od 10

Zagon funkcije SUMIFS

Čeprav je mogoče funkcijo SUMIFS vnesti v celico v delovnem listu , mnogi ljudje lažje uporabljajo pogovorno okno funkcije za vstop v funkcijo.

Vadbeni koraki

1. Kliknite na celico F12, da postane aktivna celica . Tukaj vnesemo funkcijo SUMIFS.
2. Kliknite zavihek Formule .
3. Na traku kliknite ikono Math & Trig, da odprete spustni seznam funkcij.
4. Na seznamu kliknite SUMIFS, da odpre pogovorno okno SUMIFS.

Podatki, ki jih vnesemo v prazne črte v pogovornem oknu, bodo tvorili argumente funkcije SUMIFS.

Ti argumenti povedo funkciji, za katere pogoje testiramo, in kakšen obseg podatkov se vsota, ko so ti pogoji izpolnjeni.

05 od 10

Vnos argumenta Sum_range

Argument Sum_range vsebuje podatke o celicah podatkov, ki jih želimo dodati.

Ko funkcija najde tekmo med vsemi podanimi argumenti Criteria in Criteria_range za zapis, je polje skupne vsote tega zapisa vključeno v skupno.

V tej vadnici so podatki za argument Sum_range v stolpcu Skupna prodaja .

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico Sum_range .
2. Označite celice F3 do F9 na delovnem listu, da dodate te reference celic na linijo Sum_range .

06 od 10

Vnos argumenta Criteria_range1

V tem tutorialu poskušamo v vsakem podatkovnem zapisu ujemati dve merili:

1. Prodajni zastopniki iz vzhodne prodajne regije.
2. Prodajni zastopniki, ki so letos dosegli manj kot 275 prodaj.

Argument Criteria_range1 označuje obseg celic, ki jih bo SUMIFS iskal pri iskanju prvih meril - vzhodno prodajno regijo.

Vadbeni koraki

1. V pogovornem oknu kliknite na linijo Criteria_range1 .
2. Označite celice D3 do D9 na delovnem listu, da vnesete te reference celic kot obseg, ki ga funkcija išče.

07 od 10

Vnos argumenta Criteria1

V tem vadnici so prva merila, ki jih želimo ujemati, če so podatki v območju D3: D9 enaki vzhodu .

Čeprav lahko dejanske podatke - kot je beseda Vzhod - v pogovornem oknu za ta argument vnesete, je najbolje, da podatke dodate v celico v delovnem listu in nato v pogovorno okno vnesete referenco celice .

Vadbeni koraki

1. V pogovornem oknu kliknite na linijo Criteria1 .
2. Kliknite celico D12, da vnesete referenco celice. Funkcija bo poiskala obseg, izbran v prejšnjem koraku, za podatke, ki se ujemajo s temi kriteriji.
3. Iskalni izraz (vzhod) bo dodan v celico D12 v zadnjem koraku vadnice.

Kako citiranje celic poveča vsestranskost SUMIFS

Če je referenca celic, kot je D12, vnesena kot argument kriterija , bo funkcija SUMIFS iskala ujemanja, do katerih koli podatkov, vnesenih v to celico na delovnem listu .

Torej po iskanju prodajnega zneska za vzhodno regijo bo preprosto najti iste podatke za drugo prodajno regijo preprosto s spremembo vzhoda na sever ali zahod v celico D12. Funkcija bo samodejno posodabljala in prikazala nov rezultat.

08 od 10

Vnos argumenta Criteria_range2

Kot smo že omenili, v tem tutorialu poskušamo v vsakem podatkovnem zapisu ujeti dve merili:

1. Prodajni zastopniki iz vzhodne prodajne regije.
2. Prodajni zastopniki, ki so letos dosegli manj kot 275 prodaj.

Argument Criteria_range2 označuje obseg celic, ki jih SUMIFS išče, ko se poskuša ujemati z drugimi merili - prodajnimi agenti, ki so letos prodali manj kot 275 naročil.

Vadbeni koraki

1. V pogovornem oknu kliknite na linijo Criteria_range2 .
2. Highligt celice E3 do E9 v delovnem listu, da vnesete te reference celic kot drugi obseg, ki ga funkcija išče.

09 od 10

Vnos argumenta Criteria2

V tem vadnici so druga merila, za katera se želimo ujemati, če so podatki v območju E3: E9 manjši od 275 prodajnih nalogov.

Kot pri argumentu Criteria1 bomo v pogovorno okno namesto podatkov sami vnesli referenco celice na lokacijo Criteria2.

Vadbeni koraki

1. V pogovornem oknu kliknite vrstico Criteria2 .
2. Kliknite celico E12, da vnesete referenco celice. Funkcija bo poiskala obseg, izbran v prejšnjem koraku, za podatke, ki se ujemajo s temi kriteriji.
3. Kliknite V redu, da dokončate funkcijo SUMIFS in zaprete pogovorno okno.
4. Odziv nič (0) se bo pojavil v celici F12 - celici, kjer smo vstopili v funkcijo - ker še nismo dodali podatkov na polja Criteria1 in Criteria2 (C12 in D12). Dokler mi ne storimo, se funkcija ne dodaja nič, tako da skupni znesek ostane nič.
5. Iskalni kriteriji bodo dodani v naslednjem koraku vadnice.

10 od 10

Dodajanje kriterijev iskanja in dokončanje vadnice

Zadnji korak v vadnici je dodajanje podatkov v celice v delovnem listu, ki je opredeljen kot vsebnik, ki vsebuje argumente Kriterij .

Vadbeni koraki

Za pomoč pri tem primeru si oglejte zgornjo sliko.

1. V celici D12 tip East in pritisnite tipko Enter na tipkovnici.
2. V celici E12 tip <275 in pritisnite tipko Enter na tipkovnici ("<" je simbol za manj kot v Excelu).
3. Odgovor v $ 119,719.00 se mora pojaviti v celici F12.
4. Samo dve zapisi, ki sta v vrsticah 3 in 4, ustrezata obema kriterijoma, zato se funkcija izračuna le skupni prodaji za ta dva zapise.
5. Vsota 49.017 $ in 70.702 $ je 119.719 $.
6. Ko kliknete celico F12, celotno funkcijo
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) se prikaže v vrstici s formulo nad delovnim listom .