SUMMESLE ja SUMMESLESide funktsioon kahe kriteeriumi alusel

  1. Otsi märksõnade järgi
Trikid »11. juuni 2011 Dmitri 243582 vaatamist

Kujutage ette tabelit, kus ridade ridades on loetletud osakondade nimed (või kontod või midagi muud).

Koguge rakud kriteeriumi järgi
Iga osakonna jaoks on vaja arvutada kogusumma. Paljud teevad seda filtriga ja kirjutavad rakkudes pliiatsitega.
Kuigi seda saab teha lihtsalt ja lihtsalt ainult ühe funktsiooniga - SUMMESLI .
KOKKUVÕTE (SUMIF) - koondab teatud tingimustele vastavad rakud (saab määrata ainult ühe tingimuse). Seda funktsiooni saab kasutada ka siis, kui tabel on jagatud veergudeks perioodide kaupa (iga kuu, iga kuu, kolm veergu - tulu | kulu | erinevus) ja peate arvutama kõigi perioodide kogusumma ainult tulude, kulude ja erinevuste alusel.

Kokku on kolm argumenti SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Vahemik (A1: A20000) - näitab kriteeriumide vahemikku. St Veerg, milles otsida kriteeriumi argumendiga märgitud väärtust.
  • Kriteerium (A1) on väärtus (tekst või numbriline, samuti kuupäev), mis tuleb vahemikus leida. Võib sisaldada tähemärke "*" ja "?". St määratledes kriteeriumi "* mass *", et kokku võtta väärtused, milles sõna "mass" esineb. Samal ajal võib sõna „mass” esineda kas tekstis, või rakus võib olla ainult üks sõna. Ja täpsustades "mass *", summeeritakse kõik väärtused, mis algavad "massiga". "?" - asendab ainult ühe märgi, s.t. täpsustades "mas? a", saate kokku võtta read, mille väärtus on "mass" ja väärtus "mask" jne.
    Kui kriteerium on kirjutatud lahtrisse ja peate ikka veel kasutama metamärke, saate sellele lahtrile lingi lisada vajaliku. Oletame, et peate kokku võtma väärtused, mis sisaldavad sõna "kokku". Sõna "kokku" on kirjutatud lahtrisse A1, samas kui veerus A võib esineda erinevaid õigekirja väärtusi, mis sisaldavad sõna "kokku": "juuni kokkuvõtted", "juuli üldsummad", "kogusummad märtsiks". Seejärel peaks valem olema selline:
    = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - & märk (ampersand) ühendab mitu väärtust üheks. St tulemuseks on "* tulemus *".
    Et paremini mõista valemite toimimise põhimõtet, on parem kasutada valemit Arvuta valem : Kuidas vaadata valemite arvutamise samme
    Kõik tekstilised kriteeriumid ja kriteeriumid loogiliste ja matemaatiliste märkidega peavad olema kaetud jutumärkidega (= SUMMESLI (A1: A20000; "kokku"; B1: B20000)). Kui kriteerium on number, ei ole tsitaadid nõutavad. Kui soovite otsida otsemärgi või tärniga otse, peate selle ette panema tilde (~).
    Tilde kohta ja selle funktsioonidest leiate selle artikli: Kuidas asendada / eemaldada / leida tärn?
  • Sum_Range (B1: B20000) (valikuline argument) - määrab summeeritavate summade või numbriliste väärtuste vahemiku.

Kuidas see toimib: funktsioon otsib Kriitilise argumendiga määratud väärtuse vahemikku ja kui leitakse vaste, siis summeeritakse Range_Amount argumenti poolt näidatud andmed. St kui meil on osakonna nimi veerus A ja summa veerus B, siis täpsustatakse arendusosakond kriteeriumina kõigi veeru B väärtuste summa võrra, mis asub vastupidi sellele, mida arendusosakond leidub veerus A. Tegelikult ei pruugi SumArrangement olla sama suur kui Range argument ja see ei põhjusta funktsiooni viga. Summeerimiseks lahtreid määratledes kasutatakse aga alamdomeeni Range_Amount ülemist vasakusse lahtrisse summeerimise algrakku ja seejärel summeeritakse suuruse ja kuju vastavad elemendid vahemiku argumendile.

Mõned funktsioonid
Funktsiooni viimane argument (Sum_And_Band: B1: B20000) on valikuline. See tähendab, et seda ei saa täpsustada. Kui te seda ei määra, lisab funktsioon vahemiku argumenti poolt määratud väärtused. Mis see on. Näiteks peate saama ainult nende numbrite summa, mis on suuremad kui null. Summa A veerus. Seejärel näeb see funktsioon välja:
= SUMMERS (A1: A20000; "> 0")

Mida tuleks arvestada: vahemik_summing ja vahemik peaksid olema võrdsed ridade arvuga. Vastasel juhul võite saada vale tulemuse. Optimaalselt, kui see näeb välja antud valemites: summade ulatus ja ulatus algavad ühest reast ja neil on sama arv ridu: A1: A20000; B1: B20000

Summeerimine kahe või enama kriteeriumi alusel
Aga mida teha, kui 2 ja enam summeerimise kriteeriumid on? Oletame, et peate kokku võtma ainult need summad, mis kuuluvad ühele osakonnale ja ainult teatud kuupäevaks. 2007. aasta ja uuemate kontoriversioonide õnnelikud omanikud saavad kasutada funktsiooni SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Esimene argument määrab lahtrite, mis sisaldavad koguseid, mis kogutakse ühte.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Määrab lahtrite vahemiku, milles soovite kriteeriumi järgi sobitada.
$ I $ 3, $ H8 - kriteerium. Siin, nagu SUMMESLI-s, on lubatud ka metamärgid * ja ? ja nad töötavad samal viisil.

Argumentide täpsustused: esiteks on määratud kriteeriumide vahemik (need on nummerdatud), siis väärtus (kriteerium) on märgitud otse semikooloniga, mis selles vahemikus tuleb leida - $ A $ 2: $ A $ 50; $ I $ 3. Ja mitte midagi muud. Te ei tohiks proovida kõigepealt kõiki vahemikke ja seejärel nende kriteeriume täpsustada - funktsioon annab vea või ei võta kokku vajalikku.

Kõiki tingimusi võrreldakse vastavalt põhimõttele I. See tähendab, et kui kõik loetletud tingimused on täidetud. Kui vähemalt üks tingimus ei ole täidetud, jätab see funktsiooni vahele ja ei lisa midagi.
SUMMERSi puhul peaksid summeerimis- ja kriteeriumide vahemikud olema võrdsed ridade arvuga.

Sest SUMMESLIMN ilmus ainult Exceli versioonides, alates 2007. aastast, siis kuidas on sellistel juhtudel õnnetu varasemate versioonide kasutajad? Väga lihtne: kasutage teist funktsiooni - SUMPRODUCT. Ma ei värvi argumente, sest Neid on palju ja need on kõik väärtuste massiivid. See funktsioon korrutab argumentidega märgitud massiivid. Püüan kirjeldada üldpõhimõtet selle funktsiooni kasutamise kohta, et koondada andmeid mitme tingimuse kohta.
Summeerimisprobleemi lahendamiseks mitme kriteeriumi järgi näeb funktsioon välja järgmiselt:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50
$ A $ 2: $ A - $ vahemik. $ I $ 3 on selle kriteeriumi kuupäev, mille jaoks on vaja andmeid kokku võtta.
$ B $ 2: $ B $ 50 - osakondade nimed. H5 - osakonna nimi, mille andmed tuleb kokku võtta.
$ C $ 2: $ C $ 50 - vahemik summadega.

Me analüüsime loogikat, sest paljude jaoks on see täiesti ebaselge, kui vaadata seda funktsiooni. Kui seda ainult seetõttu, et seda rakendust ei kirjeldata. Suurema loetavuse saavutamiseks vähendage vahemike suurust:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5
Seega on väljend ($ A $ 2: $ A $ 5 = $ I $ 3) ja ($ B $ 2: $ B $ 5 = H5) loogilise FALSE ja TRUE loogilised ja tagasipöörduvad massiivid. TRUE, kui vahemik $ A $ 2: $ A $ on võrdne raku $ I $ 3 väärtusega ja vahemikuga $ B $ 2: $ B $ 5 on võrdne lahtri H5 väärtusega. St meil on järgmised:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Nagu näete, on esimeses massiivis kaks olekut ja teises. Lisaks korrutatakse need kaks massiivi (selle eest vastutab korrutusmärk (*)). Korrutamisel toimub massiivide FALSE ja TRUE kaudne konversioon numbrilisteks konstantideks 0 ja 1, vastavalt ({0; 1; 1; 0} * {0; 0; 1; 0}). Nagu te teate, siis korrutatakse nulliga null. Ja tulemus on üks massiiv:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Seejärel korrutatakse massiivi {0; 0; 1; 0} arvuga vahemikus $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Selle tulemusena saame 30. Mida me vajame - saame ainult selle summa, mis vastab kriteeriumile. Kui kriteeriumile vastab rohkem kui üks summa, siis need kokku liidetakse.

SUMMYROIZV eelised
Kui argumentidel on korrutusmärgi asemel plussmärk:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
siis võrreldakse tingimusi OR põhimõtte kohaselt: s.t. summaarne summa summeeritakse, kui vähemalt üks tingimus on täidetud: kas $ A $ 2: $ A $ 5 on võrdne raku väärtusega $ I $ 3 või vahemikus $ B $ 2: $ B $ 5 on võrdne raku väärtusega H5.
See on SUMMRODUCT eelis SUMMESLIMNi kaudu. SUMMESLIMN ei saa kokku võtta VÕTA põhimõtte kohaseid väärtusi ainult vastavalt JA-põhimõttele (kõik tingimused peavad olema täidetud).

Puudused
SUMPRODUCT ei saa kasutada metamärke * ja ?. Täpsemalt saab neid kasutada, kuid neid ei tajuta erimärkidena, vaid tärnina ja küsimärgina. Ma arvan, et see on märkimisväärne puudus. Ja kuigi seda saab mööda hiilida, kasutan SUMPRODUCTi sees ka muid funktsioone - see oleks siiski tore, kui funktsioon võiks kuidagi kasutada metamärke.

Näites leiad paari näiteid ülesannetest, et paremini mõista, mis on ülal kirjutatud.

Laadige näide alla

Summa mitme kriteeriumi alusel (41,5 KiB, 10 477 allalaadimist)

Vaadake ka:
Rakkude summeerimine täitevärvi järgi
Rakkude summeerimine fondi värvi järgi
Rakkude summeerimine raku vormi järgi
Arvutage rakkude kogus täitevärvi järgi
Arvutage rakkude kogus fondi värvi järgi
Kuidas kokku võtta mitu lehte sisaldavaid andmeid, sealhulgas tingimuste kaupa

Artikkel aitas? Jagage oma sõpradega linki! Video õpetused

{"Bottom bar": {"textstyle": "staatiline", "textpositionstatic": "alt", "textautohide": tõsi, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" vasak "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": vale, "texteffect1": "slide", "texteffectslidedirection1": "õige", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "texteffectduration1": 600 "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, "texteffectdelay2": 1500, " textcss ":" kuva: plokk; polster: 12x; tekst-joondus: vasakule; "," textbgcss ":" kuva: plokk; asend: absoluutne; ülemine: 0px; vasak: 0px; laius: 100%; kõrgus: 100% taustavärv: # 333333, läbipaistmatus: 0,6, filter: a lfa (läbipaistmatus = 60); "," titlecss ":" kuva: plokk; positsioon: suhteline; font: bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; värv: #fff; "," descriptioncss ":" kuva: plokk; positsioon: suhteline; font: 12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; värv: #fff; margin-top: 8px; "," buttoncss ":" kuva: plokk; positsioon: suhteline; margin-top: 8px; "," texteffectresponsive ": tõsi," texteffectresponsivesize ": 640," titlecssrespive ":" font-size: 12px; "," descriptioncssresponsive ":" kuva: pole: oluline; "," buttoncssresponsive ": "", "addgooglefonts": vale, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Otsi märksõnade järgi

Mine Juurdepääs õunakell Multex Outlook Power Query ja Power BI VBA töötab redaktoris VBA koodi haldamine Tasuta lisandmoodulid Kuupäev ja kellaaeg Diagrammid ja graafikud Märkused Andmekaitse Internet Pildid ja objektid Lehed ja raamatud Makrod ja VBA Lisandmoodulid Kohandamine Prindi Otsi andmeid Privaatsuspoliitika Mail Programmid Töö rakendustega Töötage failidega Rakenduste arendamine Kokkuvõtte tabelid Nimekirjad Koolitused ja veebiseminarid Rahaline Vorming Valemid ja funktsioonid Exceli funktsioonid VBA funktsioonid Rakud ja vahemikud MulTExi aktsiad andmete analüüs vead ja tõrked Excelis lingid