Funktionen af ​​SUMMESLE, samt SUMMESLES ved to kriterier

1. Søg efter tags

Forestil dig et bord, hvor navne på afdelinger (eller konti eller noget andet) er angivet i rækker i træk.

Summen cellerne efter kriterium
Det er nødvendigt at beregne det samlede beløb for hver afdeling. Mange gør det med et filter og skriver med penne i cellerne.
Selvom det kan gøres nemt og simpelt med kun en funktion - SUMMESLI .
SUMMESLES (SUMIF) -Samler celler, der opfylder en given tilstand (kun en betingelse kan specificeres). Denne funktion kan også bruges, hvis tabellen er opdelt i kolonner efter perioder (månedlig, i hver måned, tre kolonner - Indkomst | Udgift | Forskel) og du skal kun beregne det samlede beløb for alle perioder kun efter indtægt, omkostning og forskel.

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

• Område (A1: A20000) - angiver rækkevidden med kriterierne. dvs. Kolonnen for at søge efter værdien angivet med kriterium argumentet.
• Kriteriet (A1) er værdien (tekst eller numerisk samt dato), der skal findes i området . Kan indeholde jokertegnene "*" og "?". dvs. specificere kriteriet "* masse *" for at opsummere de værdier, hvor ordet "masse" forekommer. På samme tid kan ordet "masse" enten forekomme overalt i teksten, eller der kan kun være et ord i en celle. Og angiver "masse *" vil alle værdier, der starter med "masse", opsummeres. "?" - erstatter kun ét tegn, dvs. angivelse af "mas? a" kan du opsummere linjerne med værdien "masse" og værdien "maske" osv.
  Hvis kriteriet er skrevet i en celle, og du stadig skal bruge wildcard-tegn, så kan du lave et link til denne celle ved at tilføje den nødvendige. Antag, at du skal opsummere de værdier, der indeholder ordet "total". Ordet "total" er skrevet i celle A1, mens der i kolonne A kan være forskellige stavelsesværdier indeholdende ordet "total": "sum for juni", "sum for juli", "sum for marts". Formlen skal så se sådan ud:
  = SUMMER (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - & sign (ampersand) kombinerer flere værdier i en. dvs. Resultatet er "* resultat *".
  For bedre at forstå princippet om, hvordan formler fungerer, er det bedre at bruge værktøjet Calculate Formula : Sådan får du vist trinene til beregning af formler
  Alle tekstmæssige kriterier og kriterier med logiske og matematiske tegn skal vedlægges i dobbelt citater (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Hvis kriteriet er et tal, er det ikke nødvendigt at citere. Hvis du vil finde et spørgsmålstegn eller en stjerne direkte, skal du lægge en tilde (~) foran den.
  Om tilde og dens funktioner kan findes i denne artikel: Hvordan erstatter / fjern / find asterisk?
• Sum_Range (B1: B20000) (valgfrit argument) - angiver rækkevidden af ​​summer eller numeriske værdier, der skal summeres.

Sådan virker det: Funktionen søger i rækkevidden for den værdi, der er angivet af kriterium- argumentet, og når en match er fundet, summerer de data, der er angivet af Range_Amount-argumentet. dvs. hvis vi har et afdelingsnavn i kolonne A og et beløb i kolonne B, så angiver udviklingsafdelingen som kriterium summen af ​​alle værdierne i kolonne B, modsat som udviklingsafdelingen findes i kolonne A. Faktisk kan SumArrangementet ikke være den samme størrelse som Range-argumentet, og dette vil ikke medføre en fejl i selve funktionen. Når du definerer celler til summering, bruges øverste venstre celle i Range_Amount-argumentet som startcelle til summering, og derefter summeres cellerne der svarer i størrelse og form til Range-argumentet.

Nogle funktioner
Funktionens sidste argument (Sum_And_Band: B1: B20000) er valgfrit. Det betyder, at det ikke kan specificeres. Hvis du ikke angiver det, vil funktionen tilføje de værdier, der er angivet af rækkevidden . Hvad er det for. For eksempel skal du få summen af ​​kun de tal, der er større end nul. I kolonne A af mængden. Så vil funktionen se sådan ud:
= SUMMER (A1: A20000; "> 0")

Hvad skal overvejes: rækkevidde og rækkevidde skal være ens i antallet af linjer. Ellers kan du få det forkerte resultat. Optimalt, hvis det vil ligne i formlerne, jeg har givet: rækkevidden og rækkevidden af summeringer starter fra en linje og har samme antal linjer: A1: A20000; B1: B20000

Summation over to eller flere kriterier
Men hvad skal man gøre, når kriterierne for opsummering 2 og mere? Antag, at du kun skal opsummere de beløb, der tilhører en afdeling og kun for en bestemt dato. Glade ejere af kontorversioner 2007 og derover kan bruge SUMMESLIMN-funktionen:
= 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 argument angiver rækkevidden af ​​celler, der indeholder de mængder, der vil blive opsamlet til en.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Angiver rækkevidden af ​​celler, hvor du vil søge efter en kamp efter kriterium.
$ I $ 3, $ H8 - kriterium. Her, som i SUMMESLI, er jokertegnene * og ? Tilladt . og de arbejder på samme måde.

Specifikationer for at angive argumenter: For det første er kriteriumområdet angivet (de er nummererede), så angives værdien (kriterium) direkte i semikolon, som i dette interval skal findes - $ A $ 2: $ A $ 50; $ I $ 3. Og intet andet. Du bør ikke forsøge at angive alle områderne, og derefter kriterierne for dem - funktionen vil enten give en fejl, eller det vil ikke opsummere, hvad der er nødvendigt.

Alle forhold sammenlignes i overensstemmelse med princippet I. Dette betyder, at hvis alle de angivne betingelser er opfyldt. Hvis mindst en betingelse ikke er opfyldt, går funktionen over linjen og tilføjer ikke noget.
Hvad angår summerne, skal summations- og kriterierne være ens i antallet af rækker.

fordi SUMMESLIMN viste sig kun i versioner af Excel, startende fra 2007, så hvordan kan ulykkelige brugere af tidligere versioner være i sådanne tilfælde? Meget enkel: brug en anden funktion - SUMPRODUCT. Jeg vil ikke male argumenterne, fordi Der er mange af dem, og de er alle værdisætninger. Denne funktion multiplicerer de arrayer, der er angivet af argumenterne. Jeg vil forsøge at beskrive det generelle princip om at bruge denne funktion til at opsummere data om flere forhold.
For at løse summationsproblemet med flere kriterier, vil funktionen se sådan ud:
= SUMPRODUCT ($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - datointerval. $ I $ 3 er datoen for kriteriet, som det er nødvendigt at opsamle dataene.
$ B $ 2: $ B $ 50 - navnene på afdelingerne. H5 - Afdelingens navn, de data der skal summeres.
$ C $ 2: $ C $ 50 - rækkevidde med beløb.

Vi analyserer logikken, fordi for mange vil det være helt uklart blot ved at se på denne funktion. Hvis kun fordi i hjælpen er denne applikation ikke beskrevet. For større læsbarhed, reducer størrelsen af ​​intervallerne:
= SUMPRODUCT ($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Så er udtrykket ($ A $ 2: $ A $ 5 = $ I $ 3) og ($ B $ 2: $ B $ 5 = H5) logiske og returnerede arrays af logisk FALSE og TRUE. TRUE, hvis cellen i intervallet $ A $ 2: $ A $ 5 er lig med værdien af ​​cellen $ I $ 3, og cellen i intervallet $ B $ 2: $ B $ 5 er lig med værdien af ​​celle H5. dvs. vi har følgende:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Som du kan se, er der i første række to kampe for tilstanden og i den anden. Yderligere multipliceres disse to arrays (multiplikationstegnet (*) er ansvarlig for dette). Når multiplikation forekommer, forekommer den implicitte konvertering af arrayer FALSE og TRUE til numeriske konstanter 0 og 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Som du ved, når vi multipliceres med nul, får vi nul. Og resultatet er et enkelt array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Så multipliceres arrayet {0; 0; 1; 0} med en række tal i intervallet $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Og som følge heraf får vi 30. Hvad vi havde brug for - vi får kun det beløb, der opfylder kriteriet. Hvis der er mere end en sum, der opfylder kriteriet, bliver de opsummeret.

Fordel ved SUMMYROIZV
Hvis argumenterne har plustegnet i stedet for multiplikationsskiltet:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
så vil forholdene blive sammenlignet i henhold til OR princippet: dvs. Summen summeres, hvis mindst en betingelse er opfyldt: enten $ A $ 2: $ A $ 5 er lig med celleværdi $ I $ 3 eller celle i interval $ B $ 2: $ B $ 5 er lig med celleværdi H5.
Dette er fordelene ved SUMMPRODUCT over SUMMESLIMN. SUMMESLIMN kan ikke opsummere værdier i henhold til OR princippet, kun i henhold til AND princippet (alle betingelser skal være opfyldt).

mangler
SUMPRODUCT kan ikke bruge jokertegn * og ?. Det er muligt at bruge mere præcist, men de opfattes ikke som specialtegn, men som en asterisk og et spørgsmålstegn. Jeg synes det er en væsentlig ulempe. Og selv om dette kan omgåes, bruger jeg andre funktioner inden for SUMPRODUCT - det ville stadig være fantastisk, hvis funktionen på en eller anden måde kunne bruge jokertegn.

I eksemplet finder du et par eksempler på funktioner til en bedre forståelse af, hvad der er skrevet ovenfor.

Download et eksempel

Beløb efter flere kriterier (41,5 KiB, 10,477 Downloads)

Se også:
Summere celler ved at fylde farve
Summation af celler efter skrifttype farve
Summere celler efter celleformat
Beregn mængden af ​​celler ved at fylde farve
Beregn mængden af ​​celler efter skrifttype farve
Sådan opsummeres data fra flere ark, inklusiv betingelse

{"Tekstlinje": "Tekstposition": "Statisk", "Tekstpositionsstatisk": "Bund", "Textautohide": True, "TextpositionMarginstatic": 0, "Textpositiondynamic": "bottomleft", "Textposition Marginleft": 24, " tekstpositionsmarginright ": 24," textpositionmarginbottom ": 24," textteffect ":" slide "," textteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left " : 30, "texteffectseparate": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "højre", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectelay1": 1000, "textteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2" textbss ":" display: blok; position: absolut; top: 0px; venstre: 0px; bredde: 100%; højde: 100% ; baggrundsfarve: # 333333; opacitet: 0,6; filter: a lpha (opacitet = 60); "," titlecss ":" display: block; stilling: relativ; skrifttype: fed 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; farve: #fff; "," descriptioncss ":" display: block; stilling: relativ; skrifttype: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; farve: #fff; margin-top: 8px; "," buttoncss ":" display: block; stilling: relativ; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" fontstørrelse: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive " "", "addgooglefonts": falsk, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Søg efter tags