Vseeno določen znesek. Primeri uporabe vsote funkcije v excelu

Če želite sešteti vrednosti, ki izpolnjujejo dani kriterij (pogoj), uporabite funkcijo SUMIF () , Angleška različica SUMIF ().

Sintaksa funkcije

SUMIF(Doseg; Stanje; [Obseg_vsote])

Doseg - obseg celic, v katerem se iščejo vrednosti, ki ustrezajo argumentu Stanje... Obseg lahko vsebuje številke, datume, besedilne vrednosti ali sklice na druge celice. V primeru, da je drugi argument Zbirni_razpon - je izpuščeno, potem je argument Doseg je tudi obseg, v katerem se izvaja vsota vrednosti, ki izpolnjujejo argument Stanje (v tem primeru Doseg vsebovati številke).

Stanje - merilo v obliki številke, izraza ali besedila, ki določa, katere celice je treba dodati. Na primer argument Stanje lahko izrazimo kot 32, "jabolka" ali "\u003e 32".

Zbirni_razpon - obseg celic, ki vsebujejo števila, ki bodo povzeta, če so ustrezne celice podane v argumentu Dosegse ujemajo z argumentom Stanje. Zbirni_razpon - neobvezen argument. Če je izpuščeno, se vsota izvede v obsegu celic, določenih v argumentu Doseg.

Primeri

Razmislite o primeru, ko argument Zbirni_razpon izpuščeno. V tem primeru se vsota izvede v obsegu celic, določenih v prvem argumentu Doseg (tj. vsebovati mora številke). Iskal bo tudi vrednosti, ki ustrezajo argumentu Stanje, ki bo nato povzeto. Naj bo domet B5: B15 , glej primer datoteke.

Rešimo naloge:

  • poiščite vsoto vseh števil, večjih ali enakih 10. Odgovor: 175. Formula: \u003d SUMIF (B5: B15, "\u003e \u003d 10")
  • poiščite vsoto vseh števil, manjših ali enakih 10. Odgovor: 42. Formula: \u003d SUMIF (B5: B15, "<=10")
  • poiščite vsoto vseh pozitivnih števil v območju. Formula: \u003d SUMIF (B5: B15, "\u003e 0")... Alternativna različica s funkcijo SUMPRODUCT () je videti takole: \u003d SUMPRODUCT ((B5: B15) * (B5: B15\u003e 0))

Obrazec naloge pogoji(kriterij) je dovolj prilagodljiv. Na primer v formuli \u003d SUMIF (B5: B15; D7 & D8) merilo<=56 задан через ссылку D7&D8 : в D7 vsebuje besedilno vrednost<=, а в D8 - številka 56 (glej sliko spodaj). Uporabnik lahko na primer enostavno spremeni merilo z uporabo v celici D7 ... Enakovredna formula \u003d SUMIF (B5: B15, "<=56") или \u003d SUMIF (B5: B15, "<="&56) ali \u003d SUMIF (B5: B15, "<="&D8) ali \u003d SUMIF (B5: B15; D7 in 56).

Funkcija SUMIF se uporablja, kadar morate vrednosti sešteti v obseg, ki ustreza določenemu kriteriju.

Opis funkcije

Funkcija SUMIF se uporablja, kadar morate vrednosti sešteti v obseg, ki ustreza določenemu kriteriju. Na primer, v stolpec s številkami morate dodati samo vrednosti, večje od 5. Če želite to narediti, lahko uporabite naslednjo formulo:
\u003d SUMIF (B2: B25, "\u003e 5") V tem primeru se vsote vrednosti preverijo glede na merilo. Po potrebi se lahko pogoj uporabi za en obseg in sešteje ustrezne vrednosti iz drugega obsega. Na primer formula:
\u003d SUMIF (B2: B5; "Ivan"; C2: C5) sešteje samo tiste vrednosti iz obsega C2: C5, za katere so ustrezne vrednosti iz obsega B2: B5 enake "Ivan".
Drug primer uporabe funkcije je prikazan na spodnji sliki:

Primer uporabe SUMIF za izračun dobička s pogojem

Sintaksa

\u003d SUMIF (obseg, stanje, [obseg_seštevanja])

Argumenti

stanje dosega sum_range

Zahtevan argument. Obseg celic, ki ga je treba presoditi po kriterijih. Celice v vsakem obsegu morajo vsebovati številke, imena, polja ali sklice na številke. Prazne celice in celice, ki vsebujejo besedilne vrednosti, se preskočijo.

Zahtevan argument. Pogoj v obliki števila, izraza, sklica na celico, besedila ali funkcije, ki določa, katere celice sešteti. Pogoj je lahko na primer predstavljen kot: 32, “\u003e 32”, B5, “32”, “jabolka” ali DANES ().

Pomembno! Vsi besedilni pogoji in pogoji z logičnimi in matematičnimi znaki morajo biti v dvojnih narekovajih (“). Če je pogoj številka, vam ni treba uporabljati narekovajev.

Neobvezen argument. Celice, iz katerih se dodajo vrednosti, če se razlikujejo od celic, določenih kot obseg. Če je obseg_številk izpuščen, Microsoft Excel sešteje celice, podane v argumentu obsega (iste celice, za katere velja pogoj).

Opombe

  • V argumentu pogoja lahko uporabite nadomestne znake: vprašaj (?) In zvezdica (*). Vprašalnik se ujema s katerim koli znakom, zvezdica pa s katerim koli zaporedjem znakov. Če želite neposredno najti vprašaj (ali zvezdico), morate pred njim postaviti tildo (~).
  • Funkcija SUMIF vrne napačne rezultate, če se uporablja za ujemanje nizov, daljših od 255 znakov, z nizom #Vrednost!
  • Argument sum_range ne sme biti enake velikosti kot argument obsega. Pri določanju dejanskih celic, ki jih je treba sešteti, se kot začetna celica uporablja zgornja leva celica argumenta sum_range, nato pa seštejejo celice v delu obsega, ki jih velikost argumenta obsega.

Dodajmo vrednosti v vrstice, katerih polja izpolnjujejo dva merila hkrati (Pogoj IN). Razmislite o besedilnih merilih, številskih in datumskih merilih. Analizirajmo funkcijo SUMIFS ( ), Angleška različica SUMIFS ().

Za izvorno tabelo vzemimo tabelo z dvema stolpcema (poljem): text " Sadje"In številčno" Količina na zalogi"(Glejte primer datoteke).

Problem 1 (1 besedilno merilo in 1 številčno)

Poiščimo število zabojev blaga z določenim Sadje IN, ki Preostale škatle na zalogi najmanj najmanj. Na primer število zabojev z blagom breskve (celica D 2 ), ki imajo preostale škatle v skladišču\u003e \u003d 6 (celica E 2 ) ... Dobiti bi morali rezultat 64. Štetje lahko izvedemo z različnimi formulami, podali jih bomo več (glej primer datoteke Besedilo in številka lista):

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

Sintaksa funkcije: SUMIF (interval_vsote; pogoj_interval1; pogoj1; pogoj_interval2; pogoj2 ...)

  • B2: B13 Sum_Interval - celice za seštevanje, vključno z imeni, nizi ali sklici, ki vsebujejo številke. Prazne vrednosti in besedilo se prezrejo.
  • A2: A13in B2: B13 pogoj_interval1; pogoj_interval2; ... predstavljajo od 1 do 127 obsegov, v katerih se preveri ustrezen pogoj.
  • D2in "\u003e \u003d" & E2 Pogoj1; stanje2; ... so od 1 do 127 pogojev v obliki števila, izraza, sklica na celico ali besedila, ki določajo, katere celice bodo seštete.


Vrstni red argumentov je v funkcijah SUMIF () in SUMIF () drugačen. Argument SUMIFS () interval seštevanja je prvi argument, v SUMIF () pa tretji. Ko kopirate in urejate podobne funkcije, se prepričajte, da so argumenti v pravilnem vrstnem redu.

2. druga možnost \u003d POROČILO ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Oglejmo si podrobneje uporabo funkcije SUMPRODUCT ():

  • Rezultat ocene A2: A13 \u003d D2 je matrika (FALSE: FALSE: FALSE: FALSE: TRUE: TRUE: TRUE: FALSE: FALSE: FALSE: FALSE: FALSE) Vrednost TRUE se ujema z vrednostjo iz stolpca IN merilo, tj. beseda breskve... Niz si lahko ogledate tako, da v A2 označite: A13 \u003d D2 in nato pritisnete;
  • Rezultat izračuna B2: B13 je (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), tj. samo vrednosti iz stolpca B ;
  • Rezultat elementnega množenja nizov (A2: A13 \u003d D2) * (B2: B13) je (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). Množenje števila z FALSE povzroči 0; in za vrednost TRUE (\u003d 1) dobimo številko samo;
  • Analizirajmo drugi pogoj: Rezultat izračuna - (B2: B13\u003e \u003d E2) je matrika (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Vrednosti v stolpcu " Število škatel na zalogi»Ki izpolnjujejo merilo\u003e \u003d E2 (tj.\u003e \u003d 6) ustreza 1;
  • Nadalje funkcija SUMPRODUCT () pomnoži elemente nizov v parih in sešteje nastale produkte. Dobimo - 64.

3. Druga uporaba funkcije SUMPRODUCT () je formula \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Po potrebi lahko datume vnesete neposredno v formulo \u003d POVZEMLJA (B6: B17; A6: A17; "\u003e \u003d 15.01.2010"; A6: A17; "<=06.03.2010")

Če želite v besedilnem nizu prikazati izbirna merila, uporabite formulo \u003d "Obseg prodaje za obdobje od" & TEXT (D6; "dd.MM.ll") & "do" & TEXT (E6; "dd.MM.yy")

Uporablja se v zadnji formuli.

Opravilo 4 (mesec)

Nekoliko spremenimo stanje prejšnje težave: poiščemo skupno prodajo za mesec (glej primer datoteke Mesec listov).

Formule so izdelane podobno kot pri opravilu 3, vendar uporabnik vnese ne 2 datuma, temveč ime meseca (domneva se, da so podatki v tabeli v roku enega leta).


Za rešitev tretje naloge je tabela z nastavljenim samodejnim filtrom videti tako (glejte primer datoteke List 2 Datumi).


Pred tem je treba tabelo pretvoriti in vključiti vrstico Skupaj.

Dober dan, dragi naročniki in obiskovalci spletnega mesta. Pred kratkim smo ugotovili formulo VLOOKUP in takoj zatem sem se odločil, da napišem še eno zelo uporabno funkcijo Excel - SUMIF... Obe funkciji lahko podatke iz različnih virov (tabel) "povežeta" s ključnim poljem in sta pod določenimi pogoji zamenljivi. Hkrati obstajajo velike razlike v njihovem namenu in uporabi.

Če je namen VLOOKUP-a preprosto "povleči" podatke z enega mesta v Excelu na drugo, potem se za seštevanje numeričnih podatkov po danem kriteriju uporablja SUMIF. Glavni namen te formule je povzeti podatke v skladu z nekaterimi pogoji. Nekaj \u200b\u200btakega je zapisano v pomoči. Vendar pa bo to orodje delovalo tudi za druge namene. Na primer, pogosto uporabljam formulo SUMIF namesto VLOOKUP, saj ne ustvari # N / A napak, ko nečesa ne najde. In to močno olajša in pospeši delo.

Funkcijo SUMIF lahko uspešno prilagodite za reševanje najrazličnejših problemov. Zato v tem članku ne bomo obravnavali 1 (enega), temveč 2 (dva) primera. Prvi bo povezan s seštevanjem po danem kriteriju, drugi - s "vlečenjem" podatkov, torej kot alternativa VLOOKUP-u.

Primer seštevanja z uporabo funkcije SUMIF

Tabela vsebuje pozicije, njihove količine, pa tudi pripadnost določeni skupini blaga (prvi stolpec). Za zdaj razmislite o poenostavljeni uporabi SUMIF, ko moramo znesek izračunati samo za tiste pozicije, katerih vrednosti ustrezajo določenemu pogoju. Na primer, želimo vedeti, koliko najvišjih položajev je bilo prodanih, tj. tisti, katerih vrednost presega 70 enot. Iskanje takšnega blaga z očmi ni zelo priročno in hitro, nato pa ga povzemite ročno, zato je funkcija SUMIF tukaj zelo primerna.

Najprej izberite celico, v kateri bo izračunan znesek. Nato pokličemo čarovnika za funkcije. To je ikona fx v vrstici s formulo. Nato na seznamu poiščite funkcijo SUMIF in jo kliknite. Odpre se pogovorno okno, kjer morate za rešitev te težave izpolniti le dve (prvi) polji od treh predlaganih.


Zato sem temu primeru rekel poenostavljeno. Zakaj 2 (dva) od 3 (treh)? Ker je naše merilo v samem obsegu seštevanja.

Polje "Obseg" označuje območje Excelove tabele, kjer se nahajajo vse prvotne vrednosti, med katerimi morate nekaj izbrati in nato dodati. Običajno se nastavi z miško.

Polje "Merilo" označuje pogoj, po katerem bo formula izbrana. V našem primeru označimo "\u003e 70". Če ponudb ne postavite, jih bodo izpolnili sami. Toda na splošno bi moralo biti takšno merilo v narekovajih.

Zadnjega polja "Sum_range" ne izpolnimo, saj je že navedeno v prvem polju.

Tako funkcija SUMIF sprejme merilo in začne izbrati vse vrednosti iz določenega obsega, ki ustrezajo določenemu kriteriju. Po tem se dodajo vse izbrane vrednosti. Tako deluje algoritem funkcije.

Ko izpolnite zahtevana polja v čarovniku za funkcije, pritisnite velik gumb "Enter" na tipkovnici ali v oknu čarovnika kliknite "Ok". Po tem naj se izračunana vrednost prikaže na mestu vhodne funkcije. V mojem primeru se je izkazalo 224 kosov. To pomeni, da je bila skupna vrednost prodanega blaga v več kot 70 kosih 224 kosov. (To je razvidno v spodnjem levem kotu okna čarovnika, še preden kliknete "ok"). To je vse. To je bil poenostavljen primer, kjer sta merilo in obseg seštevanja na istem mestu.

Zdaj pa si oglejmo primer, ko se merilo ne ujema z obsegom seštevanja. Ta položaj je veliko bolj pogost. Upoštevajte enake pogojne podatke. Recimo, da moramo poznati količino, ki ni večja ali manjša od neke vrednosti, ampak količino določene skupine blaga, na primer skupine G.

Če želite to narediti, znova izberite celico s prihodnjim izračunom zneska in pokličite čarovnika za funkcije. Zdaj v prvem oknu označimo obseg, ki vsebuje merilo, po katerem bo opravljena izbira za seštevanje, to je v našem primeru to stolpec z imeni skupin izdelkov. Nadalje samo merilo zapišemo bodisi ročno, pri čemer v ustrezno polje pustimo vnos »skupina D« ali pa z miško preprosto označimo celico z zahtevanim merilom. Zadnje polje je obseg, v katerem so povzeti podatki. Označimo ga z miško, vendar ljubiteljem tiskanja ni prepovedano ročno tipkati.


Rezultat bo količina prodanega blaga iz skupine D - 153 kosov.

Torej smo pogledali, kako izračunati en znesek za določeno merilo. Pogosteje pa težava nastane, ko je treba za več kriterijev izračunati več zneskov. Ne more biti lažje! Na primer, ugotoviti moramo količino prodanih izdelkov za posamezno skupino. To pomeni, da me zanimajo 4 (štiri) vrednosti za 4 (štiri) skupine (A, B, C in D). Za to je seznam skupin običajno sestavljen nekje na odprtih prostorih Excela v obliki ločene tabele. Jasno je, da se morajo imena skupin natančno ujemati z imeni skupin v izvorni tabeli. Takoj dodamo še zadnjo vrstico, kjer je vsota še vedno nič.

Nato je formula za prvo skupino predpisana in razširjena na vse ostale. Tu morate biti pozorni le na relativnost povezav. Obseg z merili in obseg seštevanja naj bodo absolutne povezave, tako da se med vlečenjem formule ne "spuščajo navzdol", temveč sam kriterij, najprej morate določiti z miško (in ne ročno pisati) , in drugič, to bi morala biti relativna povezava, torej, kako ima vsaka vsota svoje merilo seštevanja.

Izpolnjena polja čarovnika za funkcije s takim izračunom bodo videti nekako takole.


Kot lahko vidite, je bila za prvo skupino A količina prodanega blaga 161 kosov (spodnji levi kot slike). Zdaj pritisnite enter in povlecite formulo navzdol.

Izračunani so bili vsi zneski, njihov skupni znesek pa je 535, kar sovpada s skupnim zneskom v prvotnih podatkih. To pomeni, da so bile vse vrednosti povzete, nič ni bilo zgrešenega. Zadnji primer uporabe SUMIF-a se mi osebno zdi precej pogosto.

Primer uporabe funkcije SUMIF za primerjavo podatkov

Kot sem že večkrat ugotovil, lahko funkcijo SUMIF smiselno uporabljamo za vezavo podatkov. Če seštejete eno vrednost, dejansko dobite to vrednost. Te aritmetične lastnosti ni greh uporabiti v dobro. Možno je celo, da se oblikovalci Excela ne zavedajo takšne barbarske uporabe svojih zamisli.

Skratka, SUMIF je enostavno prilagoditi za vezavo podatkov kot alternativo VLOOKUP-u. Zakaj uporabljati SUMIF, če VLOOKUP obstaja? Naj pojasnim. Prvič, SUMIF je za razliko od VLOOKUP neobčutljiv na format podatkov in ne ustvarja napake tam, kjer najmanj pričakujete; drugič, SUMIF namesto napak zaradi pomanjkanja vrednosti po danem kriteriju daje 0 (nič), kar omogoča izračun vsote obsega s formulo SUMIF brez nepotrebnih kretenj. Vendar pa obstaja ena pomanjkljivost. Če se katero koli merilo ponovi v želeni tabeli, se nato seštejejo ustrezne vrednosti, kar ni vedno "pull-up". Bolje, da ste na preži. Po drugi strani je to pogosto potrebno - potegniti vrednosti na določeno mesto in hkrati dodati podvojene položaje. Samo poznati morate funkcijo funkcije SUMIF in jo uporabiti v skladu z navodili za uporabo.

Zdaj pa si oglejmo primer, kako se izkaže, da je funkcija SUMIF primernejša za vlečenje podatkov kot VLOOKUP. Podatki iz vašega primera naj bodo prodaja nekaterih izdelkov v januarju. Zanima nas, kako so se spremenile februarja. Primerno je narediti primerjavo na isti plošči, če ste predhodno na desni dodali še en stolpec in ga napolnili s podatki za februar. Nekje v drugi Excelovi datoteki je statistika za februar za celoten asortiman, vendar želimo analizirati natančno te položaje, za katere moramo v našo tabelo potegniti potrebne vrednosti iz velike datoteke s statistiko prodaje vseh izdelkov. Najprej poskusimo uporabiti formulo VLOOKUP. Kot merilo bomo uporabili kodo izdelka. Rezultat je na sliki.


Jasno je razvidno, da en položaj ni bil zategnjen, namesto številčne vrednosti pa se prikaže napaka # N / A. Najverjetneje tega izdelka februarja preprosto ni bilo v prodaji, zato ga februarske baze ni. Posledično je napaka # N / A prikazana v celoti. Če položajev ni veliko, potem težava ni velika, dovolj je, da napako ročno odstranite in znesek bo pravilno preračunan. Število vrstic pa lahko izmerimo v stotinah in računanje na ročne nastavitve ni povsem prava odločitev. Zdaj pa uporabimo formulo SUMIF namesto VLOOKUP.


Rezultat je enak, le da namesto napake # N / A SUMIF daje nič, kar vam omogoča običajno izračun zneska (ali drugega kazalnika, na primer povprečja) v zadnji vrstici. To je glavna ideja, zakaj bi včasih namesto VLOOKUP uporabljali SUMIF. Pri velikem številu položajev bo učinek še bolj opazen.

V resnici je pogosto treba delati z velikimi količinami podatkov, ki so velikokrat večje od velikosti monitorja. Zelo težko je odkriti in izslediti napake. Toda vsi zastoji se pojavijo v nadaljnjih izračunih. Takšnih primerov ni primerno razstaviti s posnetki zaslona v članku, zato vas vabim, da si ogledate posebej posneto video vadnico o funkciji SUMIF.

Tu je še ena lekcija o SUMIF-u



Priporočamo branje