SUMMESLE: s funktion samt SUMMESLES med två kriterier

  1. Sök efter taggar
stealth »11 juni 2011 Dmitry 243582 visningar

Föreställ dig ett bord där namnen på avdelningar (eller konton eller något annat) anges i rader i rad.

Summa cellerna efter kriterium
Det är nödvändigt att beräkna totalbeloppet för varje avdelning. Många gör det med ett filter och skriver med pennor i cellerna.
Även om det enkelt och enkelt kan utföras med bara en funktion - SUMMESLI .
SUMMESLES (SUMIF) -Samlar upp celler som uppfyller ett givet tillstånd (endast ett villkor kan anges). Den här funktionen kan också användas om tabellen delas in i kolumner efter perioder (månadsvis, i varje månad, tre kolumner - Inkomster | Expense | Skillnad) och du måste beräkna totalbeloppet för alla perioder endast enligt Inkomst, Utgift och Skillnad.

Det finns totalt tre argument för SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Område (A1: A20000) - anger intervallet med kriterierna. dvs Kolumnen för att söka efter det värde som anges av kriteriumargumentet .
  • Kriteriet (A1) är värdet (text eller numeriskt samt datum) som måste hittas i området . Kan innehålla jokertecken "*" och "?". dvs specificera kriteriet "* massa *" för att sammanfatta de värden där ordet "massa" inträffar. Samtidigt kan ordet "massa" antingen inträffa någonstans i texten, eller det kan bara vara ett ord i en cell. Och specificerar "mass *" kommer alla värden som börjar med "massa" att summeras. "?" - ersätter endast ett tecken, d.v.s. specificerar "mas? a" kan du sammanfatta linjerna med värdet "massa" och värdet "mask" etc.
    Om kriteriet är skrivet i en cell och du fortfarande behöver använda jokertecken, kan du länka till den här cellen genom att lägga till den nödvändiga. Antag att du måste summera de värden som innehåller ordet "total". Ordet "totalt" är skrivet i cell A1, medan det i kolumn A finns olika stavningsvärden som innehåller ordet "totalt": "Totals for June", "Totals for July", "Totals for March". Formeln ska då se ut så här:
    = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - & Sign (Ampersand) kombinerar flera värden i en. dvs resultatet blir "* resultat *".
    För att bättre förstå principen om hur formler fungerar, är det bättre att använda verktyget Calculate Formula : Så här visar du stegen för att beräkna formler
    Alla textkriterier och kriterier med logiska och matematiska tecken måste bifogas dubbla citat (= SUMMESLI (A1: A20000; "totalt"; B1: B20000)). Om kriteriet är ett nummer krävs inte citat. Om du vill hitta ett frågetecken eller en asterisk direkt måste du lägga en tilde (~) framför den.
    Om tilde och dess funktioner finns i den här artikeln: Hur ersätter / tar bort / hitta asterisk?
  • Sum_Range (B1: B20000) (valfritt argument) - anger summan av summer eller numeriska värden som ska summeras.

Hur det fungerar: funktionen söker i området för det värde som anges av kriterionsargumentet och när en matchning hittas summeras data som anges av Range_Amount-argumentet. dvs om vi har ett avdelningsnamn i kolumn A och ett belopp i kolumn B, då specificerar utvecklingsavdelningen som kriterium kommer att resultera i summan av samtliga värden i kolumn B, mittemot vilken utvecklingsavdelning finns i kolumn A. Faktum är att SumArrangementet kanske inte är lika stor som Range-argumentet och det här kommer inte att orsaka ett fel i själva funktionen. När du definierar celler för summering används dock den övre vänstra cellen i Range_Amount-argumentet som startcell för summering, och sedan summeras cellerna som motsvarar storlek och form till Range-argumentet.

Vissa funktioner
Funktionens sista argument (Sum_And_Band: B1: B20000) är valfritt. Det betyder att det inte kan specificeras. Om du inte anger det, lägger funktionen upp de värden som anges av räckvidden . Vad är det för. Till exempel behöver du summan av endast de siffrorna som är större än noll. I kolumn A av mängden. Då ser funktionen ut så här:
= SUMMERS (A1: A20000; "> 0")

Vad bör beaktas: range_summing och intervallet ska vara lika med antalet linjer. Annars kan du få fel resultat. Optimalt, om det kommer att se ut i formlerna jag har gett: intervallets och intervallet av summeringar startar från en rad och har samma antal rader: A1: A20000; B1: B20000

Summa över två eller flera kriterier
Men vad ska man göra när kriterierna för summering 2 och mer? Antag att du bara behöver sammanfatta de belopp som tillhör en avdelning och endast för ett visst datum. Lyckliga ägare av kontorsversioner 2007 och senare kan använda 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örsta argumentet anger hur många celler som innehåller de mängder som kommer att samlas in i en.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Anger hur många celler du vill söka efter en match efter kriterium.
$ I $ 3, $ H8 - kriterium. Här, som i SUMMESLI, är wildcard-tecknen * och ? Tillåtet. och de fungerar på samma sätt.

Specificerar att ange argument: För det första anges kriteriumintervallet (de är numrerade), då anges värdet (kriterium) direkt i semikolon, vilket i detta område måste hittas - $ A $ 2: $ A $ 50; $ I $ 3. Och inget annat. Du bör inte försöka först ange alla intervaller, och då kriterierna för dem - funktionen kommer antingen att ge ett fel eller det kommer inte att sammanfatta vad som är nödvändigt.

Alla förhållanden jämförs enligt princip I. Detta innebär att om alla de angivna villkoren är uppfyllda. Om minst ett villkor inte är uppfyllt, hoppar funktionen över linjen och lägger inte till något.
När det gäller SUMMERS bör summations- och kriterierna vara lika med antalet rader.

eftersom SUMMESLIMN visas bara i versioner av Excel, från och med 2007, hur kan då olyckliga användare av tidigare versioner vara i sådana fall? Mycket enkelt: använd en annan funktion - SUMPRODUCT. Jag kommer inte att måla argumenten, för Det finns många av dem och de är alla värderingar. Denna funktion multiplicerar de arrayer som anges av argumenten. Jag kommer att försöka beskriva den allmänna principen att använda denna funktion för att sammanfatta data på flera villkor.
För att lösa summeringsproblemet med flera kriterier kommer funktionen att se ut så här:
= SUMPRODUCT ($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - datumintervall. $ I $ 3 är datumet för kriteriet för vilket det är nödvändigt att summera data.
$ B $ 2: $ B $ 50 - namnen på avdelningarna. H5 - Institutionens namn, de uppgifter som ska summeras.
$ C $ 2: $ C $ 50 - intervall med belopp.

Vi analyserar logiken, för för många kommer det att vara helt oklart bara genom att titta på den här funktionen. Om bara för att i hjälpen beskrivs inte denna applikation. För större läsbarhet, minska storleken på intervallet:
= SUMPRODUKT ($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Så, uttrycket ($ A $ 2: $ A $ 5 = $ I $ 3) och ($ B $ 2: $ B $ 5 = H5) är logiska och returrader med logisk FALSE och TRUE. SANT om cellen i intervallet $ A $ 2: $ A $ 5 är lika med värdet på cellen $ I $ 3 och cellen i intervallet $ B $ 2: $ B $ 5 är lika med värdet av cellen H5. dvs Vi har följande:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Som du kan se finns det i matchen två matcher för villkoret och i den andra. Vidare multipliceras dessa två arrays (multiplikationsskylten (*) är ansvarig för detta). När multiplikation uppträder sker den implicita omvandlingen av arrayer FALSE och TRUE till numeriska konstanter 0 respektive 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). Som ni vet, när vi multipliceras med noll, blir vi noll. Och resultatet är en enda grupp:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Sedan multipliceras arrayen {0; 0; 1; 0} med en rad siffror i intervallet $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Och som ett resultat får vi 30. Vad vi behövde - vi får bara det belopp som uppfyller kriteriet. Om det finns mer än en summa som uppfyller kriteriet, kommer de att summeras.

Fördelen med SUMMYROIZV
Om argumenten har plustecknet istället för multiplikationsskylten:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
då kommer villkoren att jämföras enligt OR principen: d.v.s. Summa summor summeras om minst ett villkor är uppfyllt: antingen $ A $ 2: $ A $ 5 är lika med cellvärdet $ I $ 3 eller cellintervall $ B $ 2: $ B $ 5 är lika med cellvärdet H5.
Detta är fördelen med SUMMPRODUCT över SUMMESLIMN. SUMMESLIMN kan inte summera värden enligt OR principen, endast enligt AND principen (alla villkor måste vara uppfyllda).

brister
SUMPRODUCT kan inte använda jokertecken * och ?. Det är möjligt att använda mer exakt, men de kommer att uppfattas inte som specialtecken, utan som en asterisk och ett frågetecken. Jag tycker att detta är en stor nackdel. Och även om detta kan kringgå, använder jag andra funktioner inom SUMPRODUCT - det skulle fortfarande vara bra om funktionen på något sätt kan använda jokertecken.

I exemplet hittar du ett par exempel på funktioner för en bättre förståelse av vad som står ovan.

Ladda ner ett exempel

Belopp enligt flera kriterier (41,5 KiB, 10,477 Nedladdningar)

Se även:
Summa celler genom att fylla färg
Summation av celler efter typsnitt färg
Summa celler med cellformat
Beräkna mängden celler genom att fylla färg
Beräkna mängden celler efter typsnitt färg
Så här summerar du data från flera ark, inklusive med villkor

Artikel hjälpte? Dela länken med dina vänner! Video tutorials

{"Textstyle": "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, "textpositionmarginatic": "textposition": "static" textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," textteffect ":" slide "," textteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left " : 30, "texteffectseparate": 500, "texteffectseparate": falskt, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 "texteffectelay1": 1000, "textteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, textcss ":" display: block; padding: 12px; text-align: left; ";" textbgcss " ; bakgrundsfärg: # 333333; opacitet: 0,6; filter: a lpha (opacitet = 60); "," titlecss ":" display: block; position: relativ; font: fet 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; färg: #fff; "," descriptioncss ":" display: block; position: relativ; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; färg: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relativ; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" fontstorlek: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive " "", "addgooglefonts": falskt, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Sök efter taggar

tillgång äpple klocka multex Outlook Power Query och Power BI VBA arbetar i redaktören VBA kodhantering Gratis tillägg Datum och tid Diagram och grafer papper Dataskydd Internet Bilder och objekt Ark och böcker Makroer och VBA Add-ons justering trycket Sök data Sekretesspolicy post program Arbeta med applikationer Arbeta med filer Applikationsutveckling Sammanfattande tabeller listor Träningar och webinärer finansiell formatering Formler och funktioner Excel-funktioner VBA-funktioner Celler och intervall Multex aktier dataanalys buggar och glitches i Excel referenser Kan innehålla jokertecken "*" och "?
Quot;?
Specificerar "mas?
Eftersom SUMMESLIMN visas bara i versioner av Excel, från och med 2007, hur kan då olyckliga användare av tidigare versioner vara i sådana fall?