Excel SUMIFS: vsota samo vrednosti izpolnjuje več kriterijev

Anonim

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.

Preberite, kako lahko funkcijo SUMIFS uporabite v Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 za Mac, Excel 2016 za Mac, Excel za Mac 2011 in Excel Online.

01 od 11

Kako funkcijo SUMIFS deluje

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.

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, ki uporabljajo SUMIFS: prodajna sredstva v Vzhod prodajni regiji, ki je v preteklem letu imela 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.

02 od 11

Vnos podatkov o vadnicah

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

Vnesite podatke v celice D1 do F11 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 11

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, …)

Do 127 Criteria_range / Merila v funkciji so lahko določeni pari.

04 od 11

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 Merila in njihovi ustrezni Criteria_range argumente.

Criteria_range - skupina celic, ki jo funkcija najde za ustreznost Merila prepir.

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

05 od 11

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 na Formule tab.
  3. Kliknite na Math & Trig v skupini Library Library.
  4. Kliknite na SUMIFS na seznamu, da odpre pogovorno okno funkcije SUMIFS.

Excel Online nima zavihka Formule. Če želite uporabljati SUMIFS v programu Excel Online, uporabite gumb Vstavi funkcijo.

  1. Kliknite na celico F12 da postane aktivna celica. Tukaj vnesemo funkcijo SUMIFS.
  2. Kliknite na Vstavi funkcijo gumb. Odpre se pogovorno okno »Vstavi funkcijo«.
  3. Kliknite na Math & Trig na seznamu kategorij.
  4. Na seznamu kliknite SUMIFS, da zaženete funkcijo.

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.

06 od 11

Vnos argumenta Sum_range

The Sum_range argument 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 Skupna prodaja stolpec.

Vadbeni koraki

  1. Kliknite na Sum_range vrstico v pogovornem oknu.
  2. Označite celice Od F3 do F9 na delovnem listu, da dodate te reference celic na Sum_range line.
07 od 11

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 ustvarili manj kot 275 prodaj.

The Criteria_range1 argument označuje obseg celic, ki jih SUMIFS išče, ko poskuša ustrezati prvim merilom - vzhodno prodajno regijo.

Vadbeni koraki

  1. Kliknite na Criteria_range1 vrstico v pogovornem oknu.
  2. Označite celice D3 do D9 na delovnem listu, da vnesete te reference celic kot obseg, ki ga funkcija išče.
08 od 11

Vnos argumenta Criteria1

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

Čeprav dejanski podatki, kot je beseda Vzhod , lahko vnesete v pogovorno okno za ta argument, je običajno najbolje dodati podatke v celico v delovnem listu in nato v pogovorno okno vnesti referenco celice.

Vadbeni koraki

  1. Kliknite na Merila1 vrstico v pogovornem oknu.
  2. Kliknite na celico D12 da vstopite v referenco celice. Funkcija bo poiskala obseg, izbran v prejšnjem koraku, za podatke, ki se ujemajo s temi kriteriji.

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 Merila Argument, funkcija SUMIFS bo iskala ujemanja, do katerih koli podatkov, vnesenih v to celico na delovnem listu.

Torej, potem ko najdete znesek prodaje za Vzhod regiji bo preprosto najti iste podatke za drugo prodajno regijo preprosto s spreminjanjem Vzhod do sever ali Zahod v celici D12. Funkcija bo samodejno posodabljala in prikazala nov rezultat.

09 od 11

Vnos argumenta Criteria_range2

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. Kliknite na Criteria_range2 vrstico v pogovornem oknu.
  2. Označite celice E3 do E9 v delovnem listu, da vnesete te reference celic kot drugi obseg, ki ga funkcija išče.
10 od 11

Vnos argumenta Criteria2

Drugi standard, ki ga želimo ujemati, je, če so podatki v območju E3: E9 manjši od 275 prodajnih nalogov.

Tako kot pri Merila1 argument, bomo v pogovorno okno namesto podatkov sami vnesli referenco celice na lokacijo Criteria2.

Vadbeni koraki

  1. Kliknite na Merila2 vrstico v pogovornem oknu.
  2. Kliknite na celico E12 da vstopite v referenco celice. Funkcija bo poiskala obseg, izbran v prejšnjem koraku, za podatke, ki se ujemajo z merili.
  3. Kliknite v redu da dokončate funkcijo SUMIFS in zaprete pogovorno okno.

Odziv nič (0) se bo pojavil v celici F12 (celica, 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č.

Iskalni kriteriji bodo dodani v naslednjem koraku vadnice.

11 od 11

Dodajanje kriterijev iskanja in dokončanje vadnice

Zadnji korak v vadnici je dodajanje podatkov v celice v delovnem listu, ki je označen kot vsebnik Merila argumente.

Vadbeni koraki

Za pomoč pri tem primeru si oglejte zgornjo sliko.

  1. V celici D12 tip Vzhod in pritisnite Vnesite tipko na tipkovnici.
  2. V celici E12 tip <275 in pritisnite Vnesite tipko na tipkovnici ("<" je simbol za manj kot v Excelu).

Odgovor v $ 119,719.00 se mora pojaviti v celici F12.

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.

Vsota 49.017 $ in 70.702 $ je 119.719 $.

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.

Izbira Urednika