SUMIF-functie in Excel en optellen door meerdere voorwaarden

Excel is een ongelooflijk functioneel programma. Zelfs de ingebouwde functieset is voldoende om bijna elke taak te voltooien. En naast de standaard, voor velen bekend, zijn er ook die waarvan maar weinig mensen hebben gehoord. Maar tegelijkertijd houden ze niet op nuttig te zijn. Ze hebben een smallere specialisatie en daar is niet altijd behoefte aan. Maar als je ze kent, kunnen ze op een kritiek moment heel nuttig zijn.

Vandaag zullen we het hebben over een van dergelijke functies − SUMMESLIMN.

Als de gebruiker wordt geconfronteerd met de taak om verschillende waarden op te sommen, gericht op bepaalde criteria, dan is het noodzakelijk om de functie te gebruiken SUMMESLIMN. De formule die deze functie gebruikt, neemt deze voorwaarden als argumenten, telt vervolgens de waarden op die eraan voldoen en vervolgens wordt de gevonden waarde ingevoerd in de cel waarin deze is geschreven. 

SOMMEN Functie Gedetailleerde Beschrijving:

Alvorens de functie te overwegen: SUMMESLIMN, moet u eerst begrijpen wat een eenvoudigere versie ervan is - SUMMESLI, omdat de functie die we overwegen daarop is gebaseerd. Ieder van ons is waarschijnlijk al bekend met twee functies die vaak worden gebruikt: SOM (voert de optelling van waarden uit) en ALS (test een waarde tegen een gespecificeerde voorwaarde).

Als je ze combineert, krijg je een andere functie - SUMMESLI, die de gegevens controleert aan de hand van door de gebruiker gespecificeerde criteria en alleen die getallen optelt die aan die criteria voldoen. Als we het hebben over de Engelse versie van Excel, dan heet deze functie SUMIF. In eenvoudige bewoordingen is de -taalnaam een ​​directe vertaling van de Engelstalige. Deze functie kan voor verschillende doeleinden worden gebruikt. In het bijzonder kan het als alternatief worden gebruikt VPR, dat wil zeggen, opschrijven

Het belangrijkste verschil tussen de functie: SUMMESLIMN  van de gebruikelijke functie SUMMESLI is dat er meerdere criteria worden gebruikt. De syntaxis is op het eerste gezicht vrij complex, maar bij nader onderzoek blijkt de logica van deze functie heel eenvoudig te zijn. Eerst moet u het bereik selecteren waar de gegevens worden gecontroleerd en vervolgens de voorwaarden instellen voor naleving waarvan de analyse zal worden uitgevoerd. En zo'n operatie kan voor een vrij groot aantal omstandigheden worden uitgevoerd.

De syntaxis zelf is:

SOMMEN.ALS(som_bereik, voorwaarde_bereik1, voorwaarde1, [voorwaarde_bereik2, voorwaarde2], …)

Op geschikte plaatsen is het noodzakelijk om arrays van cellen te plaatsen die geschikt zijn voor een bepaald geval. 

Laten we de argumenten in meer detail bekijken:

1. Som_bereik. Dit argument, evenals het bereik van voorwaarde 1 en voorwaarde 1, is vereist. Het is een verzameling cellen die moet worden opgeteld.
2. Voorwaarde_bereik1. Dit is het bereik waar de conditie wordt gecontroleerd. Het is gekoppeld aan het volgende argument - Voorwaarde1. De sommatie van waarden die overeenkomen met het criterium wordt uitgevoerd binnen de cellen die in het vorige argument zijn gespecificeerd.
3. Voorwaarde1. Dit argument specificeert de criteria waaraan moet worden gecontroleerd. Het kan bijvoorbeeld op deze manier worden ingesteld: “> 32”.
4. Conditiebereik 2, Conditie 2… Hier worden de volgende condities op dezelfde manier ingesteld. Als er meer dan een paar voorwaarden moeten worden opgegeven, worden de argumenten Voorwaardebereik 3 en Voorwaarde 3 toegevoegd. De syntaxis is hetzelfde voor de volgende argumenten.

De functie maakt een maximale verwerking van maximaal 127 paren voorwaarden en bereiken mogelijk. 

Je kunt het op meerdere gebieden tegelijk gebruiken (we zullen er maar een paar geven, de lijst is eigenlijk nog langer):

1. Boekhouding. Het is bijvoorbeeld goed om de functie SUMMESLIMN om overzichtsrapporten te maken, bijvoorbeeld per kwartaal voor uitgaven boven een bepaald bedrag. Of maak een rapport over één product uit een bepaalde prijscategorie.
2. Verkoop management. Ook hier kan de functie erg handig zijn. We staan ​​bijvoorbeeld voor de taak om alleen de kosten op te tellen van goederen die op een bepaald moment aan een bepaalde klant zijn verkocht. En in een dergelijke situatie is de functie SUMMESLIMN kan erg nuttig zijn.
3. Opleiding. We zullen vandaag meer praktische voorbeelden uit dit gebied geven. U kunt het met name gebruiken om een ​​overzicht van de cijfers van studenten te krijgen. Je kunt kiezen voor een enkel vak of voor individuele cijfers. Een persoon kan meteen verschillende criteria instellen op basis waarvan de beoordeling wordt geselecteerd, wat erg handig is en veel tijd kan besparen.

Zoals u kunt zien, is het scala aan toepassingen voor deze functie erg breed. Maar dit is niet de enige verdienste. Laten we nog een paar voordelen van deze functie bekijken:

1. Mogelijkheid om meerdere criteria in te stellen. Waarom is dit een voordeel? U kunt de normale functie gebruiken: SUMMESLI! En dat allemaal omdat het handig is. Het is niet nodig om voor elk van de criteria aparte berekeningen uit te voeren. Alle acties kunnen vooraf worden geprogrammeerd, zelfs daarvoor. hoe de gegevenstabel zal worden gevormd. Dit is een geweldige tijdsbesparing.
2. Automatisering. De moderne tijd is de tijd van automatisering. Alleen de persoon die zijn werk goed weet te automatiseren, kan veel verdienen. Dat is de reden waarom de mogelijkheid om Excel en de functie onder de knie te krijgen SUMMESLIMN in het bijzonder is zo belangrijk voor iedereen die een carrière wil opbouwen. Als u één functie kent, kunt u meerdere acties tegelijk uitvoeren, als één. En hier gaan we verder met het volgende voordeel van deze functie.
3. Tijd besparen. Gewoon vanwege het feit dat één functie meerdere taken tegelijk uitvoert.
4. Eenvoud. Ondanks het feit dat de syntaxis op het eerste gezicht vrij zwaar is vanwege de omvang, is de logica van deze functie in feite heel eenvoudig. Eerst wordt een reeks gegevens geselecteerd en vervolgens een reeks waarden, die worden gecontroleerd op naleving van een bepaalde voorwaarde. En natuurlijk moet de voorwaarde zelf ook worden gespecificeerd. En zo meerdere keren. In feite is deze functie gebaseerd op slechts één logische constructie, wat het eenvoudiger maakt dan de bekende VPR ondanks het feit dat het voor dezelfde doeleinden kan worden gebruikt, ook rekening houdend met een groter aantal criteria. 

Kenmerken van het gebruik van de SOMMEN-functie

Er zijn verschillende kenmerken van het gebruik van deze functie waar u op moet letten. Ten eerste negeert deze functie bereiken met tekststrings of nulls, aangezien deze datatypes niet bij elkaar kunnen worden opgeteld in een rekenkundig patroon, maar alleen aaneengeschakeld als strings. Deze functie kan dit niet. Je moet ook letten op de volgende voorwaarden:

1. U kunt dit soort waarden gebruiken als voorwaarden voor het selecteren van cellen om de daarin opgenomen waarden verder toe te voegen: numerieke waarden, booleaanse expressies, celverwijzingen, enzovoort. 
2. Als tekst, logische uitdrukkingen of wiskundige tekens worden gecontroleerd, worden dergelijke criteria gespecificeerd door middel van aanhalingstekens.
3. Kan geen termen gebruiken die langer zijn dan 255 tekens.
4. Het is mogelijk om geschatte criteria te gebruiken voor het selecteren van waarden met behulp van jokertekens. Het vraagteken wordt gebruikt om een ​​enkel teken te vervangen en het vermenigvuldigingsteken (sterretje) is nodig om meerdere tekens te vervangen. 
5. Booleaanse waarden die zich in het sommatiebereik bevinden, worden automatisch geconverteerd naar numerieke waarden op basis van hun type. Dus de waarde "TRUE" verandert in één en "FALSE" in nul. 
6. Als een #WAARDE! fout in een cel verschijnt, betekent dit dat het aantal cellen in de conditie- en sommatiebereiken anders is. U moet ervoor zorgen dat de grootte van deze argumenten hetzelfde is. 

Voorbeelden van het gebruik van de SOMMEN-functie

Functie SUMMESLIMN niet zo ingewikkeld als het op het eerste gezicht lijkt, zo blijkt. Maar laten we voor meer duidelijkheid eens kijken naar enkele praktische voorbeelden van hoe u de functie kunt gebruiken SUMMESLIMN. Dit maakt het veel gemakkelijker om je in het onderwerp te verdiepen.

Voorwaarde sommatie dynamisch bereik

Dus laten we beginnen met het eerste voorbeeld. Laten we zeggen dat we een tabel hebben die informatie bevat over hoe studenten omgaan met het curriculum in een bepaald vak. Er is een reeks cijfers, de prestaties worden beoordeeld op een 10-puntsschaal. De taak is om het cijfer te vinden voor het examen van die studenten wiens achternaam begint met de letter A, en hun minimumscore is 5.

De tafel ziet er zo uit.

Om de totaalscore te berekenen op basis van de hierboven beschreven criteria, moeten we de volgende formule toepassen.

Laten we de argumenten in meer detail beschrijven:

1. C3:C14 is ons sommatiebereik. In ons geval valt het samen met het conditiebereik. Hieruit worden punten geselecteerd die worden gebruikt om het bedrag te berekenen, maar alleen die punten die onder onze criteria vallen.
2. “>5” is onze eerste voorwaarde.
3. B3:B14 is het tweede sommatiebereik dat wordt verwerkt om aan het tweede criterium te voldoen. We zien dat er geen toeval is met het sommatiebereik. Hieruit concluderen we dat het bereik van sommatie en het bereik van de aandoening al dan niet identiek kunnen zijn. 
4. "A*" is het tweede bereik, dat de selectie van cijfers alleen specificeert voor die studenten wiens achternaam begint met A. In ons geval betekent een asterisk een willekeurig aantal tekens. 

Na berekeningen krijgen we de volgende tabel.

Zoals u kunt zien, heeft de formule de waarden samengevat op basis van het dynamische bereik en op basis van de voorwaarden die door de gebruiker zijn opgegeven.

Selectieve sommatie op voorwaarde in Excel

Stel nu dat we informatie willen krijgen over welke goederen in het laatste kwartaal naar welke landen zijn verscheept. Zoek daarna de totale inkomsten uit verzendingen voor juli en augustus.

De tafel zelf ziet er zo uit. 

Om het eindresultaat te bepalen, hebben we zo'n formule nodig.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Als resultaat van de berekeningen die met deze formule zijn uitgevoerd, krijgen we het volgende resultaat.

Opgelet! Deze formule ziet er behoorlijk groot uit, hoewel we slechts twee criteria hebben gebruikt. Als het gegevensbereik hetzelfde is, kunt u de lengte van de formule aanzienlijk verkorten, zoals hieronder wordt weergegeven.

SOMMEN-functie om waarden over meerdere voorwaarden op te tellen

Laten we nu nog een voorbeeld geven om het te illustreren. In dit geval blijft de tabel hetzelfde als in het vorige geval. 

We gebruiken de volgende formule (maar we schrijven het als een matrixformule, dat wil zeggen, we voeren het in via de toetsencombinatie CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Na de functie SUMMESLIMN zal de reeks waarden optellen op basis van de criteria die zijn gespecificeerd in de formule (dat wil zeggen, de landen van China en Georgië), de resulterende reeks wordt opgeteld door de gebruikelijke functie SOM, die is geschreven als een matrixformule.

Als de voorwaarden zijn doorgegeven als een matrixconstante voor meer dan één paar, geeft de formule een onjuist resultaat.

Laten we nu eens kijken naar de tabel met de totalen.

Zoals je ziet is het ons gelukt. Het gaat jou ook zeker lukken. Groot succes op dit gebied. Dit is een heel eenvoudige functie die een persoon die net voet heeft gezet op het pad van het leren van Excel, kan begrijpen. En we weten al dat de functie SUMMESLIMN stelt u in staat effectief te zijn op elk gebied van activiteit, van boekhouding tot zelfs onderwijs. Zelfs als je een carrière opbouwt op een ander gebied dat hierboven niet is beschreven, zal deze functie je nog steeds helpen geld te verdienen. Daarom is ze waardevol.

Het belangrijkste is dat u tijd kunt besparen, wat helaas een beperkte hulpbron is. Het lijkt erop dat er een paar seconden zijn om twee functies toe te passen, maar als je een groot aantal repetitieve bewerkingen moet uitvoeren, tellen deze seconden op tot uren die aan iets anders kunnen worden besteed. We raden u daarom aan om te oefenen met het gebruik van deze functie. Bovendien is het ongelooflijk eenvoudig.