De functie van de SUMMESLE, evenals de SAMENVATTING door twee criteria

  1. Zoeken op tags
heimelijkheid »11 juni 2011 Dmitry 243582 keer bekeken

Stel u een tabel voor waarin de namen van afdelingen (of accounts of iets anders) in rijen op rij worden weergegeven.

Tel de cellen op op basis van criterium
Het is noodzakelijk het totale bedrag voor elke afdeling te berekenen. Velen doen het met een filter en schrijven met pennen in de cellen.
Hoewel het eenvoudig en eenvoudig kan worden gedaan met slechts één functie: SUMMESLI .
SUMMESLES (SUMIF) -Geeft cellen op die aan een gegeven voorwaarde voldoen (er kan slechts één voorwaarde worden opgegeven). Deze functie kan ook worden gebruikt als de tabel is verdeeld in kolommen per periode (maandelijks, in elke maand, drie kolommen - Inkomen | Uitgave | Verschil) en u het totale bedrag voor alle perioden alleen door Inkomen, Onkosten en Verschil moet berekenen.

Er zijn in totaal drie argumenten voor de SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SOMM (A1: A20000, A1, B1: B20000)

  • Bereik (A1: A20000) - geeft het bereik met de criteria aan. ie De kolom waarin moet worden gezocht naar de waarde die wordt aangegeven door het argument Criterium .
  • Het criterium (A1) is de waarde (tekst of numeriek, evenals de datum) die in het bereik moet worden gevonden. Kan de jokertekens "*" en "?" Bevatten. ie het specificeren van de criterium "* massa *" om de waarden samen te vatten waarin het woord "massa" voorkomt. Tegelijkertijd kan het woord "massa" overal in de tekst voorkomen, of er kan slechts één woord in een cel voorkomen. En als u "massa *" opgeeft, worden alle waarden die beginnen met "massa" samengevat. "?" - vervangt slechts één teken, d.w.z. met "mas? a" kunt u de regels samenvatten met de waarde "massa" en de waarde "masker", enz.
    Als het criterium in een cel is geschreven en u nog steeds jokertekens moet gebruiken, kunt u een koppeling naar deze cel maken door de vereiste toe te voegen. Stel dat u de waarden moet samenvatten die het woord 'totaal' bevatten. Het woord "totaal" is geschreven in cel A1, terwijl in kolom A verschillende spellingswaarden kunnen voorkomen die het woord "totaal" bevatten: "totalen voor juni", "totalen voor juli", "totalen voor maart". De formule zou er dan als volgt uit moeten zien:
    = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - het & -teken (ampersand) combineert verschillende waarden tot één. ie het resultaat is "* resultaat *".
    Voor een beter begrip van het principe van hoe formules werken, is het beter om de Calculate Formula- tool te gebruiken: Hoe bekijk je de stappen om formules te berekenen
    Alle tekstuele criteria en criteria met logische en wiskundige tekens moeten tussen dubbele aanhalingstekens worden geplaatst (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Als het criterium een ​​getal is, zijn aanhalingstekens niet vereist. Als u direct een vraagteken of een sterretje wilt vinden, moet u daarvoor een tilde (~) plaatsen.
    Over de tilde en de functies ervan vindt u in dit artikel: Hoe te vervangen / verwijderen / vinden asterisk?
  • Sum_Range (B1: B20000) (optioneel argument) - specificeert het bereik van bedragen of numerieke waarden die moeten worden gesommeerd.

Hoe het werkt: de functie doorzoekt het bereik op de waarde die is opgegeven in het criteriumcriterium . Wanneer een overeenkomst wordt gevonden, worden de gegevens opgeteld die worden aangegeven door het argument Range_Amount. ie als we een afdelingsnaam hebben in kolom A en een bedrag in kolom B, dan geeft het specificeren van de Ontwikkelingsafdeling als criterium de som van alle waarden van kolom B, waar tegenover de Ontwikkelingsafdeling wordt gevonden in kolom A. De SumArrangement is mogelijk niet dezelfde grootte als het Range-argument en dit veroorzaakt geen fout van de functie zelf. Bij het definiëren van cellen voor sommatie, zal de cel linksboven van het Range_Amount-argument echter worden gebruikt als de startcel voor sommatie, waarna de cellen die in grootte en vorm overeenkomen met het bereikargument worden gesommeerd.

Sommige functies
Het laatste argument van de functie (Sum_And_Band: B1: B20000) is optioneel. Dit betekent dat dit niet kan worden gespecificeerd. Als u het niet opgeeft, telt de functie de waarden op die zijn opgegeven in het argument Range . Waar is het voor? U moet bijvoorbeeld de som krijgen van alleen die getallen die groter zijn dan nul. In kolom A van het bedrag. Dan ziet de functie er als volgt uit:
= SUMMERS (A1: A20000; "> 0")

Waar moet rekening mee gehouden worden: de range_summing en het bereik moeten gelijk zijn in het aantal regels. Anders kunt u het verkeerde resultaat krijgen. Optimaal, als het eruit zal zien in de formules die ik heb gegeven: het bereik en bereik van sommaties starten vanaf één regel en hebben hetzelfde aantal regels: A1: A20000; B1: B20000

Optelling door twee of meer criteria
Maar wat te doen bij de criteria voor sommatie 2 en meer? Stel dat u alleen die bedragen hoeft op te tellen die bij een afdeling horen en alleen voor een bepaalde datum. Blije eigenaars van Office-versies 2007 en hoger kunnen de SUMMESLIMN-functie gebruiken:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Het eerste argument specificeert het cellenbereik dat de bedragen bevat die in één worden verzameld.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Specificeert het celbereik waarin u op basis van criterium naar een overeenkomst wilt zoeken.
$ I $ 3, $ H8 - criterium. Hier, net als in SUMMESLI, zijn de jokertekens * en ? Toegestaan . en ze werken op dezelfde manier.

Specifiek voor het specificeren van argumenten: eerst wordt het criteriumbereik gespecificeerd (ze zijn genummerd), vervolgens wordt de waarde (criterium) direct in puntkomma aangegeven, die in dit bereik moet worden gevonden - $ A $ 2: $ A $ 50; $ I $ 3. En niets anders. Probeer niet eerst eerst alle bereiken te specificeren, en dan de criteria daarvoor: de functie geeft een fout weer of geeft niet meer aan wat nodig is.

Alle voorwaarden worden vergeleken volgens het principe I. Dit betekent dat aan alle vermelde voorwaarden is voldaan. Als aan ten minste één voorwaarde niet wordt voldaan, slaat de functie de regel over en voegt niets toe.
Wat de SUMMERS betreft, moeten de sommatie- en criteriabereiken gelijk zijn in het aantal rijen.

omdat SUMMESLIMN verscheen pas in 2007 in versies van Excel, maar hoe kunnen ongelukkige gebruikers van eerdere versies zich in dergelijke gevallen bevinden? Heel eenvoudig: gebruik een andere functie - SUMPRODUCT. Ik zal de argumenten niet schilderen, omdat Er zijn er veel en ze zijn allemaal arrays van waarden. Met deze functie worden de arrays vermenigvuldigd die worden aangegeven door de argumenten. Ik zal proberen het algemene principe van het gebruik van deze functie te beschrijven om gegevens over verschillende omstandigheden samen te vatten.
Om het sommatieprobleem op te lossen aan de hand van verschillende criteria, ziet de functie er als volgt uit:
= SOMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - datumbereik. $ I $ 3 is de datum van het criterium waarvoor het noodzakelijk is om de gegevens samen te vatten.
$ B $ 2: $ B $ 50 - de namen van de afdelingen. H5 - de naam van de afdeling, waarvan de gegevens moeten worden opgeteld.
$ C $ 2: $ C $ 50 - bereik met bedragen.

We analyseren de logica, omdat voor velen zal het volledig onduidelijk zijn alleen door naar deze functie te kijken. Al was het alleen maar omdat in de Help deze applicatie niet wordt beschreven. Voor grotere leesbaarheid, verkleint u de reeksen:
= SOMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Dus, de uitdrukking ($ A $ 2: $ A $ 5 = $ I $ 3) en ($ B $ 2: $ B $ 5 = H5) zijn logische en return-arrays van logisch FALSE en TRUE. TRUE als de cel van het bereik $ A $ 2: $ A $ 5 gelijk is aan de waarde van de cel $ I $ 3 en de cel van het bereik $ B $ 2: $ B $ 5 is gelijk aan de waarde van cel H5. ie we hebben het volgende:
= SOMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Zoals u kunt zien, zijn er in de eerste array twee overeenkomsten voor de voorwaarde en in de tweede reeks. Verder zijn deze twee matrices vermenigvuldigd (het vermenigvuldigingsteken (*) is hiervoor verantwoordelijk). Wanneer vermenigvuldiging optreedt, vindt de impliciete conversie van arrays FALSE en TRUE naar numerieke constanten 0 en 1 ({0; 1; 1; 0} * {0; 0; 1; 0}) plaats. Zoals je weet, krijgen we, vermenigvuldigd met nul, nul. En het resultaat is een enkele array:
= SOMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Vervolgens wordt de array {0; 0; 1; 0} vermenigvuldigd met een reeks getallen in het bereik $ C $ 2: $ C $ 50:
= SOMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
En als gevolg daarvan krijgen we 30. Wat we nodig hadden - we krijgen alleen het bedrag dat aan het criterium voldoet. Als er meer dan één som is die aan het criterium voldoet, worden ze samengevat.

Voordeel van SUMMYROIZV
Als de argumenten het plusteken hebben in plaats van het vermenigvuldigingsteken:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
dan worden de condities vergeleken volgens het OR-principe: d.w.z. totale sommen worden samengevat als aan ten minste één voorwaarde is voldaan: $ A $ 2: $ A $ 5 is gelijk aan celwaarde $ I $ 3 of cel van bereik $ B $ 2: $ B $ 5 is gelijk aan celwaarde H5.
Dit is het voordeel van SUMMPRODUCT via SUMMESLIMN. SUMMESLIMN kan waarden volgens het OF-principe niet samenvatten, alleen volgens het EN-principe (aan alle voorwaarden moet zijn voldaan).

tekortkomingen
SUMPRODUCT kan geen jokertekens * en? Gebruiken. Het is mogelijk om nauwkeuriger te gebruiken, maar ze worden niet als speciale tekens, maar als een asterisk en een vraagteken gezien. Ik denk dat dit een belangrijk nadeel is. En hoewel dit kan worden omzeild, gebruik ik andere functies binnen SOMPRODUCT - het zou nog steeds geweldig zijn als de functie op de een of andere manier jokertekens zou kunnen gebruiken.

In het voorbeeld vindt u een aantal voorbeelden van functies voor een beter begrip van wat hierboven is geschreven.

Download een voorbeeld

Bedrag aan meerdere criteria (41.5 KiB, 10.477 downloads)

Zie ook:
Cellen samenvoegen op opvulkleur
Optellen van cellen op kleur van het lettertype
Cellen optellen volgens celformaat
Bereken de hoeveelheid cellen op vulkleur
Bereken de hoeveelheid cellen op letterkleur
Hoe u gegevens uit meerdere bladen samenvat, inclusief per voorwaarde

Artikel geholpen? Deel de link met je vrienden! Video-tutorials

{"Onderste balk": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" weergave: block; opvulling: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; achtergrondkleur: # 333333; dekking: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; positie: relatief; lettertype: vet 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", schreefloos, Arial; kleur: #fff; "," descriptioncss ":" weergave: blok; positie: relatief; lettertype: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", schreefloos, Arial; kleur: #fff; margin-top: 8px; "," buttoncss ":" display: block; positie: relatief; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Zoeken op tags

toegang appel horloge Multex vooruitzicht Power Query en Power BI VBA werkt in de editor VBA-codebeheer Gratis invoegtoepassingen Datum en tijd Diagrammen en grafieken papieren Gegevensbescherming Het internet Afbeeldingen en objecten Bladen en boeken Macro's en VBA Add-ons afstelling afdruk Zoek gegevens Privacybeleid mail programma's Werk met applicaties Werk met bestanden Applicatie ontwikkeling Overzichtstabellen lijsten Trainingen en webinars financieel opmaak Formules en functies Excel-functies VBA-functies Cellen en bereiken MulTEx aandelen data-analyse bugs en glitches in Excel referenties Kan de jokertekens "*" en "?
Quot;?
Met "mas?
Waar is het voor?
Omdat SUMMESLIMN verscheen pas in 2007 in versies van Excel, maar hoe kunnen ongelukkige gebruikers van eerdere versies zich in dergelijke gevallen bevinden?