1. Iskanje po oznakah

Predstavljajte si tabelo, v kateri so imena oddelkov (ali računi ali kaj drugega) navedena v vrsticah zapored.

Zbiramo celice po kriteriju
Za vsak oddelek je treba izračunati skupni znesek. Mnogi to počnejo s filtrom in pisanjem s pisali v celicah.
Čeprav je to mogoče narediti enostavno in preprosto s samo eno funkcijo - SUMMESLI .
SUMMESLES (SUMIF) - Sestavljajo celice, ki izpolnjujejo dano stanje (lahko je podan le en pogoj). To funkcijo lahko uporabite tudi, če je tabela razdeljena na stolpce po obdobjih (mesečno, v vsakem mesecu, tri stolpce - Prihodki | Stroški | Razlika) in izračunate skupni znesek za vsa obdobja samo z dohodkom, odhodki in razliko.

Skupaj za SUMMESLI obstajajo trije argumenti: Razpon , Merilo , Obseg_summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Razpon (A1: A20000) - označuje obseg z merili. Tj Stolpec, v katerem želite poiskati vrednost, navedeno z argumentom merila .
• Merilo (A1) je vrednost (besedilo ali število, pa tudi datum), ki jo je treba najti v območju . Lahko vsebuje nadomestne znake »*« in »?«. Tj z navedbo merila "* mass *", ki povzema vrednosti, v katerih se pojavlja beseda "masa". Hkrati se lahko beseda »masa« pojavi kjerkoli v besedilu ali pa je v celici lahko samo ena ta beseda. Z navedbo "mase *" se vse vrednosti, ki se začnejo z "maso", seštejejo. "?" - nadomesti samo en znak, tj. z navedbo "mas? a" lahko povzamemo vrstice z vrednostjo "masa" in vrednostjo "maska" itd.
  Če je merilo zapisano v celico in še vedno potrebujete nadomestne znake, lahko s to željo ustvarite povezavo s to celico. Recimo, da morate povzeti vrednosti, ki vsebujejo besedo "skupaj". Beseda "total" je zapisana v celici A1, v stolpcu A pa so lahko različne vrednosti črkovanja, ki vsebujejo besedo "total": "vsote za junij", "vsote za julij", "vsote za marec". Formula mora biti taka:
  = Poletje (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - znak & znak (znak &) združuje več vrednosti v eno. Tj rezultat bo "* rezultat *".
  Za boljše razumevanje načela delovanja formul je bolje uporabiti orodje za izračun formule : Kako si ogledate korake za izračun formul
  Vsa besedilna merila in merila z logičnimi in matematičnimi znaki morajo biti v dvojnih narekovajih (= SUMMESLI (A1: A20000; "skupaj"; B1: B20000)). Če je merilo število, navedbe niso potrebne. Če želite neposredno najti vprašaj ali zvezdico, morate pred njo postaviti tildo (~).
  O tildi in njenih funkcijah lahko najdete v tem članku: Kako zamenjati / odstraniti / najti zvezdico?
• Sum_Range (B1: B20000) (izbirni argument) - določa obseg vsot ali številskih vrednosti, ki jih je treba seštevati.

Kako deluje: funkcija išče območje za vrednost, ki jo podaja argument Criterion , in ko je najdeno ujemanje, sešteje podatke, ki jih je navedel argument argumenta Range_Amount. Tj če imamo v stolpcu A ime oddelka in znesek v stolpcu B, potem določimo razvojni oddelek kot merilo, kar bo imelo za posledico vsoto vseh vrednosti stolpca B, nasproti katere je razvojni oddelek v stolpcu A. Pravzaprav SumArrangement ne sme biti enake velikosti kot argument Range in to ne bo povzročilo napake same funkcije. Vendar pa se pri določanju celic za seštevanje zgornja leva celica argumenta Range_Amount uporabi kot začetna celica za seštevanje, nato pa se seštejejo celice, ki ustrezajo velikosti in obliki argumentu Range.

Nekatere funkcije
Zadnji argument funkcije (Sum_And_Band: B1: B20000) ni obvezen. To pomeni, da ga ni mogoče določiti. Če ga ne podate, bo funkcija seštevala vrednosti, podane z argumentom Range . Za kaj je. Na primer, morate dobiti vsoto samo tistih števil, ki so večje od nič. V stolpcu A zneska. Nato bo funkcija izgledala takole:
= Poletje (A1: A20000; "> 0")

Kaj je treba upoštevati: obseg_summing in obseg mora biti enak številu vrstic. V nasprotnem primeru lahko dobite napačen rezultat. Optimalno, če bo izgledal v formulah, ki sem jih dal: obseg in obseg seštevanja se začnejo iz ene vrstice in imajo enako število linij: A1: A20000; B1: B20000

Povzetek dveh ali več meril
Toda kaj storiti, ko merila za seštevanje 2 in več? Recimo, da morate povzeti samo tiste zneske, ki pripadajo enemu oddelku in samo za določen datum. Srečni lastniki Officeovih različic 2007 in novejših lahko uporabljajo funkcijo SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - obseg_summing. Prvi argument določa obseg celic, ki vsebuje zneske, ki bodo zbrani v eno.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Kriteriji razpona. Določa obseg celic, v katerih želite poiskati ujemanje po kriteriju.
$ I $ 3, $ H8 - merilo. Tukaj, kot v SUMMESLI, so dovoljeni nadomestni znaki * in ? in delujejo na enak način.

Posebnosti določanja argumentov: najprej je določen razpon meril (oštevilčenih), nato je vrednost (kriterij) navedena neposredno v podpičju, ki jo je v tem obsegu treba najti - $ A $ 2: $ A $ 50; $ I $ 3. In nič drugega. Ne smete poskušati najprej določiti vseh razponov in nato merila za njih - funkcija bo dala napako, ali pa ne bo povzela, kaj je potrebno.

Vsi pogoji se primerjajo po načelu I. To pomeni, da če so izpolnjeni vsi navedeni pogoji. Če vsaj en pogoj ni izpolnjen, funkcija preskoči vrstico in ne doda ničesar.
Kot pri SUMMERS, morajo biti razponi seštevanja in meril enaki številu vrstic.

Ker SUMMESLIMN se je pojavil samo v različicah Excela, od leta 2007 naprej, kako so lahko v takšnih primerih nezadovoljni uporabniki prejšnjih različic? Zelo preprosto: uporabite drugo funkcijo - SUMPRODUCT. Ne bom slikal argumentov, ker Obstaja jih veliko in vse so nizi vrednot. Ta funkcija množi polja, ki jih označujejo argumenti. Skušal bom opisati splošno načelo uporabe te funkcije za povzemanje podatkov o več pogojih.
Če želite rešiti problem seštevanja po več merilih, bo funkcija izgledala takole:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - časovno obdobje. $ I $ 3 je datum merila, za katerega je treba zbirati podatke.
$ B $ 2: $ B $ 50 - imena oddelkov. H5 - ime oddelka, podatki o katerih je treba seštevati.
$ C $ 2: $ C $ 50 - razpon z zneski.

Logiko analiziramo, ker Mnogim bo popolnoma nejasno, če pogledamo to funkcijo. Če samo zato, ker v pomoč ni opisana ta aplikacija. Za večjo berljivost zmanjšajte obseg:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Torej je izraz ($ A $ 2: $ A $ 5 = $ I $ 3) in ($ B $ 2: $ B $ 5 = H5) logični in vrnilni nizi logičnega FALSE in TRUE. TRUE, če je celica obsega $ A $ 2: $ A $ 5 enaka vrednosti celice $ I $ 3 in celici območja $ B $ 2: $ B $ 5 je enaka vrednosti celice H5. Tj imamo naslednje:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Kot lahko vidite, sta v prvem nizu dva ujemanja za pogoj in v drugem. Poleg tega se ta dva niza pomnožita (znak za množenje (*) je odgovoren za to). Ko pride do množenja, pride do implicitne pretvorbe nizov FALSE in TRUE v numerične konstante 0 in 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Kot veste, ko pomnožimo z nič, dobimo nič. In rezultat je eno polje:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Nato se matrika {0; 0; 1; 0} pomnoži z nizom števil v območju $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
In kot rezultat, dobimo 30. Kaj smo potrebovali - dobimo le znesek, ki ustreza kriteriju. Če obstaja več kot ena vsota, ki izpolnjuje merilo, se bodo seštevali.

Prednost SUMMYROIZV
Če imajo argumenti znak znak plus in ne znak za množenje:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
potem se bodo pogoji primerjali po načelu OR: tj. skupne vsote se seštejejo, če je izpolnjen vsaj en pogoj: bodisi $ A $ 2: $ A $ 5 je enak vrednosti celice $ I $ 3 ali celici obsega $ B $ 2: $ B $ 5 je enaka vrednosti celice H5.
To je prednost SUMMPRODUCT nad SUMMESLIMN. SUMMESLIMN ne more povzeti vrednosti po načelu OR samo po načelu AND (vsi pogoji morajo biti izpolnjeni).

Slabosti
SUMPRODUCT ne more uporabljati nadomestnih znakov * in ?. Natančneje je mogoče uporabiti, vendar jih ne bomo zaznali kot posebne znake, temveč kot zvezdico in vprašaj. Mislim, da je to pomembna pomanjkljivost. In čeprav je to mogoče zaobiti, uporabljam druge funkcije znotraj SUMPRODUCT - še vedno bi bilo super, če bi funkcija nekako uporabljala nadomestne znake.

V primeru boste našli nekaj primerov funkcij za boljše razumevanje tega, kar je napisano zgoraj.

Prenesite primer

Znesek po več kriterijih (41,5 KiB, 10,477 Prenosi)

Glejte tudi:
Zbiranje celic z barvo polnila
Zbiranje celic glede na barvo pisave
Zbiranje celic po obliki celice
Izračunajte količino celic s polnilno barvo
Izračunajte količino celic glede na barvo pisave
Kako povzeti podatke iz več listov, vključno s pogoji

{"Spodnja vrstica": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " "24", "besedilo učinka": "slide", "texteffecteasing": "easeOutCubic", "texteffectduration": 600, "texteffectslidedirection": "levo", "texteffectslidedistance"; : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "desno", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 "texteffect2": "slide", "texteffectslidedirection2": "desno", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " prikaz: blok; oblazinjenje: 12px; poravnava besedila: levo; "," textbgcss ":" prikaz: blok; položaj: absolutno; vrh: 0px; levo: 0px; širina: 100%; višina: 100% barva ozadja: # 333333; motnost: 0,6; filter: a lfa (motnost = 60); "," titlecss ":" zaslon: blok; položaj: relativna; "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; barva: #fff; "," descriptioncss ":" prikaz: blok; položaj: relativna; "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; barva: #fff; margin-top: 8px; "," gumbcss ":" zaslon: blok; položaj: relativna; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentatic": 40}}

Iskanje po oznakah