• Главная
  • Карта сайта
Не найдено

Funkcija SUMMESLE-a, kao i SUMMESLES po dva kriterija

  1. Pretraživanje po oznakama
potajno »11. lipnja 2011 Dmitry 243582 pregleda

Zamislite tablicu u kojoj su nazivi odjela (ili računi ili nešto drugo) navedeni u redovima za redom.

Zbroj stanica po kriteriju
Potrebno je izračunati ukupni iznos za svaki odjel. Mnogi to rade s filtrom i pisanjem olovkom u stanicama.
Iako se to može učiniti jednostavno i jednostavno sa samo jednom funkcijom - SUMMESLI .
SUMMESLES (SUMIF) - Sumiraju stanice koje zadovoljavaju određeno stanje (može se odrediti samo jedan uvjet). Ova se funkcija može koristiti i ako je tablica podijeljena na stupce po razdobljima (mjesečno, u svakom mjesecu, tri stupca - Dohodak | Rashodi | Razlika) i trebate izračunati ukupni iznos za sva razdoblja samo za Dohodak, Rashodi i Razlike.

Postoje ukupno tri argumenta za SUMMESLI: Raspon , Kriterij , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Raspon (A1: A20000) - označava raspon s kriterijima. tj Stupac u kojem se traži vrijednost naznačena argumentom Kriterij .
  • Kriterij (A1) je vrijednost (tekst ili broj, kao i datum) koji se mora naći u dometu . Može sadržavati zamjenske znakove "*" i "?". tj navodeći kriterij "* mass *" za sumiranje vrijednosti u kojima se pojavljuje riječ "masa". U isto vrijeme, riječ "masa" može se pojaviti bilo gdje u tekstu, ili može postojati samo jedna riječ u ćeliji. I navodeći "masu *", sve vrijednosti koje počinju s "masom" zbrajaju se. "?" - zamjenjuje samo jedan znak, tj. navodeći "mas? a" možete sumirati retke s vrijednošću "masa" i vrijednosti "maska", itd.
    Ako je kriterij napisan u ćeliji i još uvijek trebate koristiti zamjenske znakove, možete povezati ovu ćeliju dodavanjem potrebnog. Pretpostavimo da trebate sumirati vrijednosti koje sadrže riječ "ukupno". Riječ "total" zapisana je u ćeliji A1, dok u stupcu A mogu postojati različite vrijednosti pravopisa koje sadrže riječ "total": "zbrojevi za lipanj", "ukupni iznosi za srpanj", "ukupni iznosi za ožujak". Formula bi tada trebala izgledati ovako:
    = LJETOVI (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - znak & (ampersand) kombinira nekoliko vrijednosti u jednu. tj rezultat je "* rezultat *".
    Da biste bolje razumjeli princip rada formula, bolje je koristiti alat Formule za izračunavanje : Kako pregledati korake za izračunavanje formula
    Svi tekstualni kriteriji i kriteriji s logičkim i matematičkim znakovima moraju biti zatvoreni u dvostruke navodnike (= SUMMESLI (A1: A20000; "ukupno"; B1: B20000)). Ako je kriterij broj, navodnici nisu potrebni. Ako želite izravno pronaći upitnik ili zvjezdicu, pred nju stavite tildu (~).
    O tildi i njezinim značajkama možete pronaći u ovom članku: Kako zamijeniti / ukloniti / pronaći zvjezdicu?
  • Sum_Range (B1: B20000) (izborni argument) - određuje raspon sume ili numeričkih vrijednosti koje treba zbrajati.

Kako radi: funkcija pretražuje Raspon za vrijednost određenu argumentom Kriterij , a kada se pronađe podudarnost, zbraja podatke označene argumentom Range_Amount. tj ako imamo naziv odjela u stupcu A i iznos u stupcu B, tada navodeći Odjel za razvoj kao kriterij rezultirat će zbrojem svih vrijednosti stupca B, nasuprot koje se Odjel za razvoj nalazi u stupcu A. U stvari, SumArrangement ne mora biti iste veličine kao argument raspona i to neće uzrokovati pogrešku same funkcije. Međutim, kada se definiraju ćelije za zbrajanje, gornja lijeva stanica argumenta Range_Amount bit će korištena kao početna stanica za zbrajanje, a zatim će se zbrajati ćelije koje odgovaraju veličini i obliku argumentu Range.

Neke značajke
Posljednji argument funkcije (Sum_And_Band: B1: B20000) nije obavezan. To znači da se ne može specificirati. Ako ga ne navedete, funkcija će zbrojiti vrijednosti specificirane argumentom Range . Što je to. Na primjer, morate dobiti zbroj samo onih brojeva koji su veći od nule. U stupcu A iznosa. Tada će funkcija izgledati ovako:
= LJETOVI (A1: A20000; "> 0")

Što treba uzeti u obzir: raspon range_summing i raspon bi trebao biti jednak broju redaka. Inače možete dobiti pogrešan rezultat. Optimalno, ako će izgledati u formulama koje sam dao: raspon i raspon zbrajanja počinju od jednog retka i imaju isti broj linija: A1: A20000; B1: B20000

Zbrajanje dva ili više kriterija
Ali što učiniti kad kriteriji za zbrajanje 2 i više? Pretpostavimo da trebate sumirati samo one iznose koji pripadaju jednom odjelu i to samo za određeni datum. Sretni vlasnici uredskih verzija 2007 i novija mogu koristiti funkciju SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Prvi argument određuje raspon ćelija koje sadrže iznose koji će biti prikupljeni u jedan.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Kriteriji raspona. Određuje raspon ćelija u kojima želite tražiti podudarnost po kriteriju.
$ I $ 3, $ H8 - kriterij. Ovdje, kao u SUMMESLI, dopušteni su zamjenski znakovi * i ? i rade na isti način.

Specifičnosti specificiranja argumenata: najprije je naveden kriterijski raspon (oni su numerirani), zatim je vrijednost (kriterij) naznačena izravno u zarezu, koji se u ovom rasponu mora pronaći - $ A $ 2: $ A $ 50; $ I $ 3. I ništa više. Ne biste trebali pokušati najprije odrediti sve raspone, a zatim kriterije za njih - funkcija će ili dati pogrešku, ili neće sumirati što je potrebno.

Svi uvjeti se uspoređuju prema principu I. To znači da ako su ispunjeni svi navedeni uvjeti. Ako barem jedan uvjet nije ispunjen, funkcija preskače redak i ne dodaje ništa.
Što se tiče LJETOVA, rasponi zbrajanja i kriterija trebaju biti jednaki u broju redaka.

jer SUMMESLIMN se pojavio samo u verzijama programa Excel, počevši od 2007. godine, kako onda nezadovoljni korisnici ranijih verzija mogu biti u takvim slučajevima? Vrlo jednostavno: koristite drugu funkciju - SUMPRODUCT. Neću slikati argumente, jer Ima ih mnogo i sve su to vrijednosti vrijednosti. Ova funkcija množi polja označena argumentima. Pokušat ću opisati opće načelo korištenja ove funkcije za sumiranje podataka o nekoliko uvjeta.
Za rješavanje problema zbrajanja po nekoliko kriterija, funkcija će izgledati ovako:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - datumski raspon. $ I $ 3 je datum kriterija za koji je potrebno zbrojiti podatke.
$ B $ 2: $ B $ 50 - imena odjela. H5 - naziv odjela, podaci o kojima se moraju zbrojiti.
$ C $ 2: $ C $ 50 - raspon s iznosima.

Analiziramo logiku, jer mnogima će biti posve nejasno samo ako pogledate ovu funkciju. Ako je samo zato što u pomoći ova aplikacija nije opisana. Za veću čitljivost smanjite veličinu raspona:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Dakle, izraz ($ A $ 2: $ A $ 5 = $ I $ 3) i ($ B $ 2: $ B $ 5 = H5) su logički i povratni nizovi logičkog FALSE i TRUE. TRUE ako je stanica raspona $ A $ 2: $ A $ 5 jednaka vrijednosti ćelije $ I $ 3 i ćeliji raspona $ B $ 2: $ B $ 5 jednaka je vrijednosti ćelije H5. tj imamo sljedeće:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Kao što možete vidjeti, u prvom nizu postoje dva podudaranja za uvjet, au drugom. Nadalje, ova dva niza se množe (znak množenja (*) je odgovoran za to). Kada dođe do množenja, dolazi do implicitne konverzije nizova FALSE i TRUE u numeričke konstante 0 i 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Kao što znate, kad pomnožimo sa nulom, dobivamo nulu. Rezultat je jedan niz:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Tada se polje {0; 0; 1; 0} množi s nizom brojeva u rasponu $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
I kao rezultat toga, dobivamo 30. Ono što nam je potrebno - dobivamo samo onaj iznos koji zadovoljava kriterij. Ako postoji više od jednog iznosa koji zadovoljava kriterij, onda će se zbrojiti.

Prednost SUMMYROIZV
Ako argumenti imaju znak plus umjesto znaka množenja:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
tada će se uvjeti uspoređivati ​​prema principu OR: tj. ukupni zbrojevi zbrajaju se ako je ispunjen barem jedan uvjet: ili $ A $ 2: $ A $ 5 je jednak vrijednosti ćelije $ I $ 3 ili ćeliji raspona $ B $ 2: $ B $ 5 jednaka je vrijednosti ćelije H5.
To je prednost SUMMPRODUCT u odnosu na SUMMESLIMN. SUMMESLIMN ne može zbrojiti vrijednosti prema principu OR, samo prema principu AND (svi uvjeti moraju biti ispunjeni).

mane
SUMPRODUCT ne može koristiti zamjenske znakove * i ?. Moguće je koristiti preciznije, ali neće biti shvaćeni kao posebni znakovi, već kao zvjezdica i upitnik. Mislim da je to značajan nedostatak. I premda se to može zaobići, koristim druge funkcije unutar SUMPRODUCT - i dalje bi bilo sjajno ako bi funkcija mogla nekako koristiti zamjenske znakove.

U primjeru ćete naći nekoliko primjera funkcija za bolje razumijevanje onoga što je gore napisano.

Preuzmite primjer

Iznos po nekoliko kriterija (41.5 KiB, 10.477 preuzimanja)

Pogledajte i:
Zbrajanje stanica po boji ispune
Zbrajanje stanica prema boji fonta
Zbrajanje stanica po formatu ćelije
Izračunajte količinu stanica po boji ispune
Izračunajte količinu stanica prema boji fonta
Kako sažeti podatke iz nekoliko listova, uključujući i stanje

Članak je pomogao? Podijelite vezu s prijateljima! Video vodiči

{"Donja traka": {"textstyle": "statična", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " 24, "tekstualni efekt": "slide", "texteffecteasing": "easeOutCubic", "texteffectduration": 600, "texteffectslidedirection": "left", "texteffectslidedistance": "left", "texteffectslidedistance"; : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slajd", "texteffectslidedirection1": "desno", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slajd", "texteffectslidedirection2": "desno", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; pozicija: apsolutna; vrh: 0px; lijevo: 0px; širina: 100%; visina: 100% boja pozadine: # 333333; neprozirnost: 0,6; filtar: a lfa (neprozirnost = 60); "," titlecss ":" display: blok; pozicija: relativna; font: bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; boja: #fff; "," descriptioncss ":" prikaz: blok; pozicija: relativna; font: 12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; boja: #fff; margin-top: 8px; "," buttoncss ":" display: block; pozicija: relativna; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentatic": 40}}

Pretraživanje po oznakama

pristup sat jabuka Multex pogled Power Query i Power BI VBA radi u uredniku Upravljanje VBA kodom Besplatni dodaci Datum i vrijeme Grafikoni i grafikoni novine Zaštita podataka Internet Slike i objekti Listovi i knjige Makronaredbe i VBA Dodatke podešavanje otisak Pretraživanje podataka Pravila o privatnosti pošta programi Rad s aplikacijama Rad s datotekama Razvoj aplikacija Tablice sažetka arena Treninzi i webinari financijska formatiranje Formule i funkcije Excelove funkcije VBA funkcije Stanice i rasponi Dionice MulTExa analiza podataka bugova i propusta u Excelu reference Može sadržavati zamjenske znakove "*" i "?
Quot;?
Navodeći "mas?
2007. godine, kako onda nezadovoljni korisnici ranijih verzija mogu biti u takvim slučajevima?
Провайдеры:
  • 08.09.2015

    Batyevka.NET предоставляет услуги доступа к сети Интернет на территории Соломенского района г. Киева.Наша миссия —... 
    Читать полностью

  • 08.09.2015
    IPNET

    Компания IPNET — это крупнейший оператор и технологический лидер на рынке телекоммуникаций Киева. Мы предоставляем... 
    Читать полностью

  • 08.09.2015
    Boryspil.Net

    Интернет-провайдер «Boryspil.net» начал свою работу в 2008 году и на данный момент является одним из крупнейших поставщиков... 
    Читать полностью

  • 08.09.2015
    4OKNET

    Наша компания работает в сфере телекоммуникационных услуг, а именно — предоставлении доступа в сеть интернет.Уже... 
    Читать полностью

  • 08.09.2015
    Телегруп

    ДП «Телегруп-Украина» – IT-компания с 15-летним опытом работы на рынке телекоммуникационных услуг, а также официальный... 
    Читать полностью

  • 08.09.2015
    Софтлинк

    Высокая скоростьМы являемся участником Украинского центра обмена трафиком (UA — IX) с включением 10 Гбит / сек... 
    Читать полностью