Kõik sama kindel summa. Näited summeeritud funktsiooni Excelis kasutamiseks

Antud kriteeriumile (tingimusele) vastavate väärtuste summeerimiseks kasutage funktsiooni SUMIF () , Ingliskeelne versioon SUMIF ().

Funktsioonide süntaks

SUMIF(Vahemik; Seisund; [Summa vahemik])

Vahemik - lahtrivahemik, milles argumendile vastavaid väärtusi otsitakse Seisund... Vahemik võib sisaldada numbreid, kuupäevi, tekstiväärtusi või viiteid teistele lahtritele. Juhul kui teine \u200b\u200bargument on Summa vahemik - jäetakse välja, siis argument Vahemik on ka vahemik, mille ulatuses teostatakse argumenti rahuldavate väärtuste summa Seisund (sel juhul Vahemik peab sisaldama numbreid).

Seisund - kriteerium arvu, avaldise või teksti kujul, mis määrab lisatavad lahtrid. Näiteks argument Seisund võib väljendada kui 32, "õunad" või "\u003e 32".

Summa vahemik - lahtrivahemik, mis sisaldab numbreid, mis summeeritakse, kui vastavad lahtrid on määratud argumendis Vahemiksobi argumendiga Seisund. Summa vahemik - valikuline argument. Kui see välja jätta, tehakse summa argumendis määratud lahtrite ulatuses Vahemik.

Näited

Vaatleme juhtumit, kus argument Summa vahemik välja jäetud. Sel juhul tehakse summa esimeses argumendis määratud lahtrite ulatuses Vahemik (st see peab sisaldama numbreid). Samuti otsib see argumendile vastavaid väärtusi Seisund, mis siis kokku võetakse. Las see olla vahemik B5: B15 , vt näidisfaili.

Lahendame probleemid:

  • leidke kõigi suuremate või võrdsete arvude summa. Vastus: 175. Valem: \u003d SUMIF (B5: B15, "\u003e \u003d 10")
  • leidke kõigi arvude summa, mis on väiksem või võrdne 10. Vastus: 42. Valem: \u003d SUMIF (B5: B15, "<=10")
  • leidke kõigi vahemikus olevate positiivsete arvude summa. Valem: \u003d SUMIF (B5: B15, "\u003e 0")... Funktsiooni SUMPRODUCT () kasutav alternatiivne versioon näeb välja selline: \u003d SUMPRODUCT ((B5: B15) * (B5: B15\u003e 0))

Ülesande vorm tingimused(kriteerium) on piisavalt paindlik. Näiteks valemis \u003d SUMIF (B5: B15; D7 ja D8) kriteerium<=56 задан через ссылку D7&D8 : в D7 sisaldab teksti väärtust<=, а в D8 - number 56 (vt allolevat joonist). Näiteks saab kasutaja lahtris kasutatavat kriteeriumit hõlpsasti muuta D7 ... Samaväärne valem \u003d SUMIF (B5: B15, "<=56") или \u003d SUMIF (B5: B15, "<="&56) või \u003d SUMIF (B5: B15, "<="&D8) või \u003d SUMIF (B5: B15; D7 ja 56).

Funktsiooni SUMIF kasutatakse juhul, kui peate summeerima vahemikus olevad väärtused, mis vastavad määratud kriteeriumile.

Funktsiooni kirjeldus

Funktsiooni SUMIF kasutatakse juhul, kui peate summeerima vahemikus olevad väärtused, mis vastavad määratud kriteeriumile. Näiteks peate arvude veerus lisama ainult väärtused, mis on suuremad kui 5. Selleks võite kasutada järgmist valemit:
\u003d SUMIF (B2: B25, "\u003e 5") Selles näites kontrollitakse summeeritud väärtusi kriteeriumi järgi. Vajaduse korral saab tingimust rakendada ühele vahemikule ja teise vahemiku vastavad väärtused kokku võtta. Näiteks valem:
\u003d SUMIF (B2: B5; "Ivan"; C2: C5) summeerib ainult need väärtused vahemikust C2: C5, mille vastavad väärtused vahemikust B2: B5 on võrdsed väärtusega "Ivan".
Järgmine näide funktsiooni kasutamisest on toodud alloleval joonisel:

Näide SUMIF-i kasutamisest tingimusega kasumi arvutamiseks

Süntaks

\u003d SUMIF (vahemik, tingimus, [summa_vahemik])

Argumendid

vahemiku seisund summa_vahemik

Nõutav argument. Kriteeriumide järgi hinnatav rakkude vahemik. Iga vahemiku lahtrid peavad sisaldama numbreid, nimesid, massiive või numbriviiteid. Tühjad lahtrid ja tekstiväärtusi sisaldavad lahtrid jäetakse vahele.

Nõutav argument. Tingimus arvu, avaldise, lahtriviite, teksti või funktsiooni kujul, mis määrab lahtrid kokku. Näiteks võib tingimust esitada järgmiselt: 32, “\u003e 32”, B5, “32”, “õunad” või TÄNA ().

Tähtis! Kõik tekstitingimused ja loogiliste ja matemaatiliste märkidega tingimused peavad olema lisatud jutumärkidesse (“). Kui tingimus on arv, ei pea te jutumärke kasutama.

Valikuline argument. Lahtrid, millele lisatakse väärtused, kui need erinevad vahemikuna määratud lahtritest. Kui summa_vahemik on välja jäetud, Microsoft Excel summeerib vahemiku argumendis määratud lahtrid (samad lahtrid, millele tingimus kehtib).

Märkused

  • Tingimusargumendis saate kasutada metamärke: küsimärk (?) Ja tärn (*). Küsimärk sobib ükskõik millise tähemärgiga ja tärn sobib tähemärkide järjestusega. Kui soovite leida küsimärgi (või tärniga) otse, peate selle ette panema tilde (~).
  • Funktsioon SUMIF tagastab valed tulemused, kui seda kasutatakse stringide pikkemaks kui 255 tähemärki sobitamiseks stringiga #VALUE!
  • Argument summa_vahemik ei pruugi olla sama suur kui vahemiku argument. Tegelike summeeritavate lahtrite määramisel kasutatakse alglahuks argumendi summa_vahemik vasakut ülemist lahtrit ja seejärel summeeritakse vahemiku argumendi suurusega selle osa lahtrid.

Lisame ridadesse väärtused, mille väljad vastavad kahele kriteeriumile korraga (tingimus JA). Mõelge kriteeriumidele Tekst, arv ja kuupäev. Analüüsime funktsiooni SUMIFS ( ), Ingliskeelne versioon SUMIFS ().

Võtame lähtetabeliks kahe veeru (välja) tabeli: tekst " Puu"Ja numbriline" Kogus laos"(Vaadake näidisfaili).

1. ülesanne (1 tekstikriteerium ja 1 numbriline)

Leiame kindla kaubakastide arvu Puu JA, mis Ülejäänud karbid laos mitte vähem kui miinimum. Näiteks kaubaga kastide arv virsikud (kamber D 2 ), mille laos on ülejäänud karbid\u003e \u003d 6 (kamber E 2 ) ... Peaksime saama tulemuse 64. Loendamist saab rakendada paljude valemitega, anname mitu (vt näidisfaili Lehe tekst ja number):

1. =Summa (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

Funktsiooni süntaks: SUMIF (summa_interval; tingimus_interval1; tingimus1; tingimus_interval2; tingimus2 ...)

  • B2: B13 Summaintervall - summeerimiseks lahtrid, sealhulgas numbreid sisaldavad nimed, massiivid või viited. Tühje väärtusi ja teksti eiratakse.
  • A2: A13ja B2: B13 tingimuse vahemik1; tingimus_interval2; ... esindavad vahemikku 1 kuni 127, milles vastavat tingimust kontrollitakse.
  • D2ja "\u003e \u003d" & E2 tingimus1; tingimus2; ... on 1 kuni 127 tingimust numbri, avaldise, lahtriviite või teksti kujul, mis määravad, millised lahtrid kokku võetakse.


Funktsioonides SUMIF () ja SUMIF () on argumentide järjestus erinev. Argument SUMIFS () summeerimisintervall on esimene argument ja SUMIF-is () kolmas. Nende sarnaste funktsioonide kopeerimisel ja redigeerimisel veenduge, et argumendid oleksid õiges järjekorras.

2. teine \u200b\u200bvõimalus \u003d ALUMPRODUKT ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Vaatame funktsiooni SUMPRODUCT () kasutamist lähemalt:

  • A2: A13 \u003d D2 hindamise tulemus on massiiv (FALSE: FALSE: FALSE: FALSE: TRUE: TRUE: TRUE: FALSE: FALSE: FALSE: FALSE: FALSE) Väärtus TÕENE vastab veeru väärtusele JA kriteerium, s.t sõna virsikud... Massiivi saab näha, tõstes A2-s esile: A13 \u003d D2 ja vajutades seejärel;
  • B2: B13 arvutamise tulemus on (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), s.t. lihtsalt veeru väärtused B ;
  • Massiivide elementaarkorrutamise (A2: A13 \u003d D2) * (B2: B13) tulemus on (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). Numbri korrutamine valega FALSE annab 0; ja väärtuse TRUE (\u003d 1) jaoks saadakse number ise;
  • Analüüsime teist tingimust: Arvutuse tulemus - (B2: B13\u003e \u003d E2) on massiiv (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Veerus olevad väärtused " Laos olevate kastide arv»Kriteeriumile\u003e \u003d E2 vastavad (st\u003e \u003d 6) vastavad 1-le;
  • Lisaks korrutab funktsioon SUMPRODUCT () massiivide elemendid paarikaupa ja summeerib saadud saadused. Saame - 64.

3. Funktsiooni SUMPRODUCT () teine \u200b\u200bkasutusala on valem \u003d ALUMPRODUKT ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Vajadusel saab kuupäevad sisestada otse valemisse \u003d KOKKUVÕTE (B6: B17; A6: A17; "\u003e \u003d 15.01.2010"; A6: A17; "<=06.03.2010")

Valikukriteeriumide kuvamiseks tekstistringis kasutage valemit \u003d "Müügimaht ajavahemikul" & TEXT (D6; "dd.MM.yy") & "kuni" & TEXT (E6; "dd.MM.yy")

Kasutatakse viimases valemis.

4. ülesanne (kuu)

Muudame veidi eelmise probleemi seisukorda: leidke kuu kogumüük (vt näidisfaili Lehtekuu).

Valemid on üles ehitatud sarnaselt ülesandega 3, kuid kasutaja sisestab mitte 2 kuupäeva, vaid kuu nime (eeldatakse, et tabeli andmed on 1 aasta jooksul).


Kolmanda ülesande lahendamiseks näeb konfigureeritud automaatfiltriga tabel välja selline (vt näidisfaili 2. leht Kuupäevad).


Varem tuleb tabel teisendada reale Kokku ja see peab sisaldama.

Tere pärastlõunal, kallid tellijad ja blogisaidi külastajad. Üsna hiljuti leidsime VLOOKUPi valemi ja kohe pärast seda otsustasin kirjutada veel ühest väga kasulikust Exceli funktsioonist - SUMIF... Mõlemad funktsioonid on võimelised "linkima" erinevatest allikatest (tabelitest) pärit võtmevälja andmeid ja teatud tingimustel on need omavahel asendatavad. Samal ajal on nende otstarbes ja kasutamises olulisi erinevusi.

Kui VLOOKUPi eesmärk on lihtsalt andmeid "tõmmata" Exceli ühest kohast teise, siis arvandmete kokkuvõtmiseks vastavalt antud kriteeriumile kasutatakse summat. Selle valemi peamine eesmärk on andmete kokkuvõte vastavalt mõnele tingimusele. Midagi sellist on kirjas abis. Kuid see tööriist töötab ka muudel eesmärkidel. Näiteks kasutan VLOOKUPi asemel sageli valemit SUMIF, kuna see ei tekita # N / A tõrkeid, kui see midagi ei leia. Ja see hõlbustab ja kiirendab tööd oluliselt.

Funktsiooni SUMIF saab edukalt kohandada paljude probleemide lahendamiseks. Seetõttu käsitleme selles artiklis mitte 1 (ühte), vaid 2 (kahte) näidet. Esimene neist on seotud summeerimisega vastavalt antud kriteeriumile, teine \u200b\u200b- andmete „tõmbamisega“ ehk alternatiivina VLOOKUP-ile.

Näide summeerimisest funktsiooni SUMIF abil

Tabel sisaldab positsioone, nende koguseid, samuti kuulumist konkreetsesse kaubarühma (esimene veerg). Kaaluge praegu SUMIF-i lihtsustatud kasutamist, kui peame arvutama summa ainult nende positsioonide jaoks, mille väärtused vastavad teatud tingimusele. Näiteks tahame teada, kui palju müüdi kõrgeimaid positsioone, s.t. need, kelle väärtus ületab 70 ühikut. Selliste kaupade silmadega otsimine ja seejärel käsitsi kokkuvõtete tegemine pole eriti mugav ja kiire, seetõttu on siin funktsioon SUMIF väga sobiv.

Kõigepealt valige lahter, kus summa arvutatakse. Järgmisena helistame funktsiooniviisardile. See on ikoon fx valemiribal. Järgmisena otsige loendist funktsiooni SUMIF ja klõpsake seda. Avaneb dialoogiboks, kus selle konkreetse probleemi lahendamiseks peate täitma kolmest pakutavast ainult kaks (esimest) välja.


Seetõttu nimetasin seda näidet lihtsustatuks. Miks 2 (kaks) 3-st (kolmest)? Sest meie kriteerium on väga liitmisalas.

Väli "Vahemik" tähistab Exceli tabeli ala, kus asuvad kõik algsed väärtused, kust peate midagi valima ja seejärel lisama. Tavaliselt määratakse see hiirega.

Väli "Kriteerium" näitab tingimust, mille järgi valem valitakse. Meie puhul näitame "\u003e 70". Kui te jutumärke ei pane, täidetakse need ise. Kuid üldiselt peaks selline kriteerium olema jutumärkides.

Me ei täida viimast välja "Sum_range", kuna see on juba märgitud esimesele väljale.

Seega võtab funktsioon SUMIF kriteeriumi ja hakkab valitud vahemikust kõiki valitud kriteeriumile vastavaid väärtusi valima. Pärast seda lisatakse kõik valitud väärtused. Nii töötab funktsiooni algoritm.

Pärast funktsioonide nõustaja nõutavate väljade täitmist vajutage klaviatuuril suurt nuppu "Enter" või klõpsake viisardi aknas nuppu "OK". Pärast seda peaks arvutatud väärtus ilmuma sisendfunktsiooni kohale. Minu näites osutus see 224 tükiks. See tähendab, et enam kui 70 tükki müüdud kaupade koguväärtus oli 224 tükki. (Seda näeb viisardi akna vasakus alanurgas juba enne "ok" klõpsamist). See on kõik. See oli lihtsustatud näide, kus kriteerium ja summeerimisvahemik asuvad samas kohas.

Vaatame nüüd näidet, kus kriteerium ei vasta summeerimisvahemikule. See olukord on palju tavalisem. Vaatleme samu tingimuslikke andmeid. Oletame, et peame teadma summat, mis ei ole suurem ega väiksem kui mingi väärtus, vaid konkreetse kaubagrupi, näiteks grupi G kogus.

Selleks valige uuesti lahtris summa tulevane arvutamine ja helistage funktsiooniviisardile. Nüüd, esimeses aknas, näitame vahemiku, mis sisaldab kriteeriumi, mille alusel tehakse valik summeerimiseks, see tähendab, et meie puhul on see veerg tooterühmade nimedega. Edasi kirjutame kriteeriumi ise kas käsitsi, jättes vastavasse väljale kirje "rühm D", või näitame lihtsalt hiirega lahtrit, millel on nõutav kriteerium. Viimane ruut on vahemik, kus kokkuvõtlikud andmed asuvad. Näitame seda hiirega, kuid printimise austajatel pole keelatud käsitsi kirjutada.


Tulemuseks on D-grupist müüdud kaupade kogus - 153 tk.

Niisiis vaatasime, kuidas arvutada üks summa ühe konkreetse kriteeriumi jaoks. Kuid sagedamini tekib probleem siis, kui mitme kriteeriumi jaoks on vaja arvutada mitu summat. See ei saa olla lihtsam! Näiteks peame välja selgitama iga rühma jaoks müüdavate toodete hulga. See tähendab, et mind huvitavad 4 (nelja) rühma (A, B, C ja D) 4 (neli) väärtust. Selleks tehakse gruppide loend tavaliselt kusagil Exceli avatud ruumides eraldi tabeli kujul. On selge, et rühmade nimed peavad täpselt vastama allikatabelis olevate rühmade nimedele. Lisame kohe viimase rea, kus summa on ikkagi null.

Seejärel määratakse esimese rühma valem ja laiendatakse seda kõigile teistele. Siin peate lihtsalt pöörama tähelepanu linkide suhtelisusele. Kriteeriumidega vahemik ja summeerimisvahemik peaksid olema absoluutsed lingid, nii et valemit lohistades ei läheks need "alla", vaid kriteerium ise esiteks peate määrama hiirega (ja mitte käsitsi kirjutama) ja teiseks peaks see olema suhteline seos, seega kuidas igal summal on oma liitmise kriteerium.

Sellise arvutusega funktsiooniviisardi täidetud väljad näevad välja umbes sellised.


Nagu näete, oli esimese A-rühma jaoks müüdud kaupade kogus 161 tükki (joonise vasakus alanurgas). Nüüd vajutage sisestusklahvi ja lohistage valem alla.

Kõik summad arvutati ja nende kogusumma on 535, mis langeb kokku algandmete kogusummaga. See tähendab, et kõik väärtused on kokku võetud, midagi pole vahele jäänud. Viimane SUMIFi kasutamisjuhtum, millega ma isiklikult kokku puutun, kohtab mind üsna tihti.

Näide funktsioonide SUMIF kasutamisest andmete võrdlemiseks

Nagu ma olen mitu korda märkinud, saab funktsiooni SUMIF mõistlikult andmete sidumiseks kasutada. Tõepoolest, kui liitate ühe väärtuse, saate selle väärtuse ise. Seda aritmeetilist omadust ei ole patt heaga kasutada. On isegi võimalik, et Exceli disainerid pole teadlikud oma ajupoja nii barbaarsest kasutamisest.

Lühidalt öeldes on SUMIF-i VLOOKUP-i alternatiivina andmete sidumiseks lihtne kohandada. Miks kasutada SUMIF-i, kui VLOOKUP on olemas? Las ma seletan. Esiteks on SUMIF, erinevalt VLOOKUPist, andmevormingu suhtes tundetu ja ei tekita viga seal, kus te seda kõige vähem ootate; teiseks annab SUMIF antud kriteeriumile vastavate väärtuste puudumisest tingitud vigade asemel 0 (null), mis võimaldab teil arvutada vahemiku kogusummad valemiga SUMIF ilma tarbetute žestideta. Siiski on üks puudus. Kui soovitud tabelis korratakse mõnda kriteeriumit, siis summeeritakse vastavad väärtused, mis ei ole alati "pull-up". Parem olla valvel. Teisest küljest on see sageli vajalik - tõmmake väärtused etteantud kohta üles ja lisage samal ajal dubleeritud positsioonid. Peate lihtsalt teadma funktsiooni SUMIF omadusi ja kasutama seda vastavalt kasutusjuhendile.

Vaatame nüüd näidet selle kohta, kuidas funktsioon SUMIF osutub andmete tõmbamiseks sobivamaks kui VLOOKUP. Olgu teie näite andmeteks mõne toote müük jaanuaris. Tahame teada, kuidas nad veebruaris muutusid. Samal plaadil on mugav teha võrdlust, olles eelnevalt paremale veel ühe veeru lisanud ja selle veebruari andmetega täitnud. Kusagil teises Exceli failis on kogu sortimendi veebruari statistika, kuid me tahame analüüsida täpselt neid positsioone, mille jaoks peame oma tabelisse tõmbama vajalikud väärtused suurest failist koos kõigi toodete müügistatistikaga. Kõigepealt proovime kasutada valemit VLOOKUP. Kriteeriumina kasutame tootekoodi. Tulemus on joonisel.


On selgelt näha, et ühte positsiooni pole pingutatud ja numbrilise väärtuse asemel kuvatakse viga # N / A. Tõenäoliselt ei olnud seda toodet lihtsalt veebruaris müügil ja seetõttu puudub see veebruari andmebaasist. Selle tulemusena kuvatakse viga # N / A kokku. Kui positsioone pole palju, siis pole probleem suur, piisab vea käsitsi eemaldamisest ja summa arvutatakse õigesti ümber. Ridade arvu saab mõõta aga sadades ja loendamine käsitsi reguleerimisele pole päris õige otsus. Nüüd kasutame VLOOKUPi asemel valemit SUMIF.


Tulemus on sama, ainult # N / A SUMIF vea asemel annab see nulli, mis võimaldab teil tavaliselt arvutada summa (või muu näitaja, näiteks keskmine) viimases reas. See on peamine mõte, miks VLOOKUPi asemel tuleks mõnikord kasutada SUMIF-i. Suure hulga positsioonide korral on mõju veelgi märgatavam.

Tegelikkuses on sageli vaja töötada suure hulga andmetega, mis on mitu korda suuremad kui monitori suurus. Vigade tuvastamine ja jälgimine on väga keeruline. Kuid kõik jämedused ilmnevad järgmistes arvutustes. Selliseid näiteid pole mugav artiklis ekraanipiltide abil lahti võtta, seega kutsun teid üles vaatama spetsiaalselt salvestatud videoõpetust funktsiooni SUMIF kohta.

Siin on veel üks õppetund SUMIFist



Soovitame lugeda