Funksjonen til SUMMESLE, samt SUMMESLES etter to kriterier

1. Søk etter koder

Tenk deg et bord der navnene på avdelinger (eller kontoer eller noe annet) er oppført i rader på rad.

Summene cellene etter kriterium
Det er nødvendig å beregne totalbeløpet for hver avdeling. Mange gjør det med et filter og skriver med penner i cellene.
Selv om det kan gjøres enkelt og enkelt med bare en funksjon - SUMMESLI .
SUMMESLES (SUMIF) - Sommer opp celler som tilfredsstiller en gitt tilstand (bare en betingelse kan spesifiseres). Denne funksjonen kan også brukes hvis tabellen er delt inn i kolonner etter perioder (månedlig, i hver måned, tre kolonner - Inntekt | Kostnad | Differanse) og du må beregne totalbeløpet for alle perioder bare etter inntekt, kostnad og forskjell.

Det er totalt tre argumenter for SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Område (A1: A20000) - angir rekkevidden med kriteriene. dvs. Kolonnen der du skal søke etter verdien angitt av kriterium- argumentet.
• Kriteriet (A1) er verdien (tekst eller tall, så vel som datoen) som må finnes i området . Kan inneholde jokertegnene "*" og "?". dvs. Spesifiserer kriteriet "* masse *" for å oppsummere verdiene der ordet "masse" oppstår. På samme tid kan ordet "masse" enten forekomme hvor som helst i teksten, eller det kan bare være ett ord i en celle. Og angi "masse *" vil alle verdier som starter med "masse" bli oppsummert. "?" - erstatter bare ett tegn, dvs. Spesifiserer "mas? a" du kan oppsummere linjene med verdien "masse" og verdien "maske", etc.
  Hvis kriteriet er skrevet i en celle, og du fortsatt trenger å bruke jokertegn, kan du lage en lenke til denne cellen ved å legge til den nødvendige. Anta at du må oppsummere verdiene som inneholder ordet "totalt". Ordet "totalt" er skrevet i celle A1, mens det i kolonne A kan være forskjellige staveverdier som inneholder ordet "totalt": "totals for juni", "totals for juli", "totals for mars". Formelen bør da se slik ut:
  = SUMMERE (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - & sign (ampersand) kombinerer flere verdier i en. dvs. Resultatet blir "* resultat *".
  For bedre å forstå prinsippet om hvordan formler fungerer, er det bedre å bruke Calculate Formula- verktøyet: Slik viser du trinnene for å beregne formler
  Alle tekstkriterier og kriterier med logiske og matematiske tegn skal være vedlagt i to sitater (= SUMMESLI (A1: A20000; "totalt"; B1: B20000)). Hvis kriteriet er et tall, er ikke anførselstegn kreves. Hvis du vil finne et spørsmålstegn eller en stjerne direkte, må du sette en tilde (~) foran den.
  Om tilde og dens funksjoner finnes i denne artikkelen: Hvordan erstatte / fjerne / finne stjerne?
• Sum_Range (B1: B20000) (valgfritt argument) - angir rekkevidden av summer eller numeriske verdier som skal summeres.

Slik fungerer det: Funksjonen søker i Range for verdien som er spesifisert av Criterion- argumentet, og når en kamp er funnet, summerer dataene angitt av Range_Amount-argumentet. dvs. hvis vi har et avdelingsnavn i kolonne A og et beløp i kolonne B, så angir utviklingsavdelingen som kriterium summen av alle verdiene i kolonne B, motsatt som utviklingsavdelingen finnes i kolonne A. Faktisk kan SumArrangementet ikke være av samme størrelse som Range-argumentet, og dette vil ikke forårsake en feil i selve funksjonen. Når du definerer celler for summering, brukes imidlertid øverste venstre celle i Range_Amount-argumentet som startcell for summering, og deretter summeres cellene som svarer til størrelse og form til Range-argumentet.

Noen funksjoner
Det siste argumentet for funksjonen (Sum_And_Band: B1: B20000) er valgfritt. Dette betyr at det ikke kan spesifiseres. Hvis du ikke spesifiserer det, vil funksjonen legge opp verdiene som er angitt av Range argumentet. Hva er det for. For eksempel må du få summen av bare de tallene som er større enn null. I kolonne A av beløpet. Da vil funksjonen se slik ut:
= SUMMER (A1: A20000; "> 0")

Hva bør vurderes: Range_summing og rekkevidde skal være like i antall linjer. Ellers kan du få feil resultat. Optimalt, hvis det vil se ut i formlene jeg har gitt: rekkevidde og rekkevidde av summeringer starter fra en linje og har samme antall linjer: A1: A20000; B1: B20000

Sammendrag over to eller flere kriterier
Men hva skal jeg gjøre når kriteriene for summering 2 og mer? Anta at du må oppsummere bare de beløpene som tilhører en avdeling og bare for en bestemt dato. Glade eiere av kontorversjoner 2007 og over kan bruke SUMMESLIMN-funksjonen:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Det første argumentet angir rekkevidden av celler som inneholder mengdene som vil bli samlet inn i en.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Angir rekkevidden av celler der du vil søke etter en kamp etter kriterium.
$ I $ 3, $ H8 - kriterium. Her, som i SUMMESLI, er jokertegnene * og ? Tillatt . og de jobber på samme måte.

Spesifikasjoner for å angi argumenter: Først er kriteriumområdet angitt (de er nummerert), deretter er verdien (kriterium) angitt direkte i semikolon, som i dette området må finnes - $ A $ 2: $ A $ 50; $ I $ 3. Og ingenting annet. Du bør ikke prøve å først angi alle områder, og deretter kriteriene for dem - funksjonen vil enten gi en feil, eller det vil ikke oppsummere det som er nødvendig.

Alle forholdene blir sammenlignet i henhold til prinsippet I. Dette betyr at hvis alle oppførte betingelser er oppfylt. Hvis minst en tilstand ikke er oppfylt, hopper funksjonen over linjen og legger ikke til noe.
Når det gjelder SUMMERS, skal summasjons- og kriteriene være like i antall rader.

fordi SUMMESLIMN dukket opp bare i versjoner av Excel, fra 2007, da kan det da være ulykkelige brukere av tidligere versjoner i slike tilfeller? Veldig enkelt: bruk en annen funksjon - SUMPRODUCT. Jeg vil ikke male argumentene, fordi Det er mange av dem, og de er alle arrays of values. Denne funksjonen multipliserer de arrayene som er angitt av argumentene. Jeg vil forsøke å beskrive det generelle prinsippet om bruk av denne funksjonen for å oppsummere data om flere forhold.
For å løse summasjonsproblemet med flere kriterier, vil funksjonen se slik ut:
= SUMPRODUCT ($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - datoperiode. $ I $ 3 er datoen for kriteriet som det er nødvendig å summere dataene.
$ B $ 2: $ B $ 50 - navnene på avdelingene. H5 - navnet på avdelingen, dataene som skal oppsummeres.
$ C $ 2: $ C $ 50 - rekkevidde med beløp.

Vi analyserer logikken, fordi For mange vil det være helt uklart bare ved å se på denne funksjonen. Hvis bare fordi i hjelpen er dette programmet ikke beskrevet. For større lesbarhet, reduser størrelsen på områdene:
= SUMPRODUCT ($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Så uttrykket ($ A $ 2: $ A $ 5 = $ I $ 3) og ($ B $ 2: $ B $ 5 = H5) er logiske og returnerte arrays av logisk FALSE og TRUE. SANT hvis cellen i området $ A $ 2: $ A $ 5 er lik verdien av cellen $ I $ 3, og cellen i området $ B $ 2: $ B $ 5 er lik verdien av cellen H5. dvs. Vi har følgende:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Som du ser, er det i første rekkevidde to kamper for tilstanden, og i den andre. Videre blir disse to arrays multiplisert (multiplikasjonstegnet (*) er ansvarlig for dette). Når multiplikasjon oppstår, forekommer den implisitte konverteringen av arrays FALSE og TRUE til numeriske konstanter 0 og 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Som du vet, når vi multipliseres med null, får vi null. Og resultatet er et enkelt utvalg:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Da multipliseringen {0; 0; 1; 0} multipliseres med en rekke tall i området $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Og som et resultat får vi 30. Det vi trengte - vi får kun beløpet som oppfyller kriteriet. Hvis det er mer enn en sum som tilfredsstiller kriteriet, blir de oppsummert.

Fordelene med SUMMYROIZV
Hvis argumentene har plustegnet i stedet for multiplikasjonstegnet:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
da vil forholdene bli sammenlignet i henhold til OR prinsippet: dvs. Sum summene vil bli oppsummert dersom minst en betingelse er oppfylt: enten $ A $ 2: $ A $ 5 er lik celleverdi $ I $ 3 eller rekkevidde $ B $ 2: $ B $ 5 er lik celleverdi H5.
Dette er fordelen med SUMMPRODUCT over SUMMESLIMN. SUMMESLIMN kan ikke oppsummere verdier i henhold til OR prinsippet, bare i henhold til AND prinsippet (alle betingelser må oppfylles).

mangler
SUMPRODUCT kan ikke bruke jokertegn * og ?. Det er mulig å bruke mer presist, men de vil bli oppfattet ikke som spesialtegn, men som en stjerne og et spørsmålstegn. Jeg tror dette er en betydelig ulempe. Og selv om dette kan bli omgått, bruker jeg andre funksjoner i SUMPRODUCT - det vil fortsatt være bra hvis funksjonen på en eller annen måte kan bruke jokertegn.

I eksemplet finner du et par eksempler på funksjoner for bedre forståelse av hva som er skrevet over.

Last ned et eksempel

Beløp etter flere kriterier (41,5 KiB, 10,477 Nedlastinger)

Se også:
Summere celler ved å fylle farge
Summering av celler etter skrifttype farge
Summere celler etter celleformat
Beregn mengden celler ved å fylle farge
Beregn mengden celler etter skrifttype farge
Slik oppsummerer du data fra flere ark, også etter betingelse

{"Textstyle": "tekstposisjon": "statisk", "tekstposisjonstatisk": "nederst", "teksttautisk": sant, "tekstposisjonmarginstatisk": 0, "tekstposisjondynamisk": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left " : "Texteffectslide1": 120, "texteffectease1": "textteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right" "texteffectdelay1": 1000 textcss ":" display: blokk; polstring: 12px; tekstjustering: venstre; ";" textbgcss ":" display: blokk; posisjon: absolutt; topp: 0px; venstre: 0px; bredde: 100%; høyde: 100% Bakgrunnsfarge: # 333333; Opacity: 0.6; Filter: a lpha (opacity = 60); "," titlecss ":" display: block; posisjon: relativ; font: fet 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; farge: #fff; "," descriptioncss ":" display: block; posisjon: relativ; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; farge: #fff; margin-topp: 8px; "," buttoncss ":" display: block; posisjon: relativ; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" fontstørrelse: 12px; ";" descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive " "", "addgooglefonts": falskt, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Søk etter koder