In dit artikel zullen we leren hoe de ALS-functie te gebruiken in plaats van de SOMPRODUCT- en SUMIFS-functie in Excel.
Scenario:
In eenvoudige bewoordingen, wanneer we met een lange verspreide dataset werken, moeten we soms de som van getallen vinden met enkele criteria eroverheen. Bijvoorbeeld het vinden van de som van salarissen in een bepaalde afdeling of het hebben van meerdere criteria over datum, namen, afdeling of zelfs cijfers zoals salarissen onder de waarde of hoeveelheid boven de waarde. Hiervoor gebruik je meestal de SOMPRODUCT of SOMMEN functie. Maar je zou het niet geloven, je voert dezelfde functie uit met Excel-basisfunctie ALS-functie.
Hoe het probleem op te lossen?
U moet zich afvragen hoe dit mogelijk is, om logische bewerkingen uit te voeren over tabelmatrices met behulp van de IF-functie. ALS de functie in Excel erg handig is, zal het je door een aantal moeilijke taken in Excel of een andere codeertalen loodsen. De functie IF test voorwaarden op array die overeenkomen met vereiste waarden en retourneert het resultaat als array die overeenkomt met True-voorwaarden als 1 en False als 0.
Voor dit probleem zullen we de volgende functies gebruiken:
- SOM-functie
- ALS-functie
We zullen deze bovenstaande functies en een basisgevoel voor gegevensverwerking nodig hebben. logische voorwaarden op arrays kunnen worden toegepast met behulp van logische operators. Deze logische operatoren werken zowel op tekst als op getallen. Hieronder staat de generieke formule. { } accolades is het magische hulpmiddel om matrixformules uit te voeren met de ALS-functie.
Generieke formule:
| { = SOM ( IF ( (logische_1) * (logische_2) *… * (logische_n) , sum_array )) } |
Let op: voor accolades ( { } ) Gebruik maken van Ctrl + Shift + Enter bij het werken met arrays of bereiken in Excel. Dit genereert standaard accolades op de formule. Probeer GEEN accolades hard te coderen.
Logisch 1: test voorwaarde 1 op array 1
Logisch 2: test voorwaarde 2 op array 2 enzovoort
sum_array : array, operatiesom wordt uitgevoerd
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren. Hier hebben we gegevens van geleverde producten in verschillende steden, samen met bijbehorende categorievelden en hoeveelheden. Hier hebben we de gegevens en we moeten de hoeveelheid cookies vinden die naar Boston zijn verzonden, waar de hoeveelheid groter is dan 40.
Gegevenstabel en criteriatabel worden weergegeven in de bovenstaande afbeelding. Om het doel te begrijpen, hebben we benoemde bereiken gebruikt voor de gebruikte arrays. Benoemde bereiken worden hieronder vermeld.
Hier :
Plaats gedefinieerd voor array A2:A17.
Categorie gedefinieerd voor array B2:A17.
Hoeveelheid gedefinieerd voor array C2:C17.
Nu bent u klaar om het gewenste resultaat te krijgen met behulp van de onderstaande formule.
Gebruik de formule:
| { = SOM ( IF ( (Stad = "Boston") * (Categorie = "Cookies") * (Aantal>40) , Hoeveelheid)) } |
Uitleg :
- Stad = "Boston" : controleert de waarden in het stadsbereik om overeen te komen met "Boston".
- Category="Cookies" : controleert de waarden in het categoriebereik om overeen te komen met "Cookies".
- Hoeveelheid > 40 : controleert de waarden in Hoeveelheidsbereik tot ma
- Hoeveelheid is een matrix waar som vereist is.
- ALS-functie controleert alle criteria en asterisk char (*) vermenigvuldigt alle arrayresultaten.
= SOM ( ALS ( { 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 0 ; 0 ; 0 } , {33 ; 87 ; 58 ; 38 ; 54 ; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30 }))
- Nu retourneert de ALS-functie alleen de hoeveelheden die overeenkomen met de 1s en de rest wordt genegeerd.
- De functie SOM retourneert de SOM.
Nu telt de hoeveelheid die overeenkomt met enen alleen maar op om het resultaat te krijgen.
Zoals u kunt zien, wordt hoeveelheid 43 geretourneerd, maar er zijn drie cookie-orders geleverd aan "Boston" met hoeveelheid 38, 36 en 43. We hadden een som van de hoeveelheid nodig waarbij de hoeveelheid groter is dan 40. Dus de formule retourneert alleen 43. Gebruik nu andere criteria om de SOM Hoeveelheid voor Stad te krijgen: "Los Angeles" & Categorie: "Bars" & Hoeveelheid kleiner dan 50.
Gebruik de formule
| { = SUM ( IF ( ( Stad = "Los Angeles") * (Categorie = "Bars") * (Hoeveelheid < 50), Hoeveelheid) ) } |
Zoals u kunt zien, retourneert de formule de waarden 86 als resultaat. Dat is de som van 2 bestellingen die voldoen aan de voorwaarden met hoeveelheid 44 & 42. In dit artikel wordt geïllustreerd hoe u een geneste IF-formule vervangt door een enkele IF in een matrixformule. Dit kan worden gebruikt om de complexiteit van complexe formules te verminderen. Dit specifieke probleem kan echter eenvoudig worden opgelost met de SUMIFS- of SOMPRODUCT-functie.
Gebruik van de SOMPRODUCT-functie:
De functie SOMPRODUCT retourneert de som van de overeenkomstige waarden in de array. Dus we zullen ervoor zorgen dat de arrays 1s a de True-statementwaarden en 0s naar de False-statementwaarden retourneren. Dus de laatste som komt overeen met waar alle uitspraken waar staan.
Gebruik de formule:
| = SOMPRODUCT ( -- (Plaats = "Boston") , -- (Categorie = "Cookies") , -- (Aantal > 40) , Hoeveelheid) |
-- : bewerking die wordt gebruikt om alle TRUE's naar 1s en False naar 0 te converteren.
De functie SOMPRODUCT controleert opnieuw de SOM van de hoeveelheid die wordt geretourneerd door de SOM- en ALS-functie die hierboven is uitgelegd.
Evenzo voor het tweede voorbeeld staat het resultaat hetzelfde.
Zoals u kunt zien, kan de SOMPRODUCT-functie dezelfde taak uitvoeren.
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- De sum_array in de formule werkt alleen met getallen.
- Als de formule de fout #WAARDE retourneert, moet u controleren of de accolades aanwezig moeten zijn in de formule, zoals weergegeven in de voorbeelden in het artikel.
- Negatieteken (--) verandert waarden, TRUEs of 1s naar FALSEs of 0s en FALSEs of 0s naar TRUEs of 1s .
- Bewerkingen zoals is gelijk aan ( = ), kleiner dan gelijk aan ( <= ), groter dan ( > ) of niet gelijk aan ( ) kan worden uitgevoerd binnen een toegepaste formule, alleen met getallen.
Ik hoop dat dit artikel over het gebruik van de ALS-functie in plaats van de SOMPRODUCT- en SUMIFS-functie in Excel verklarend is. Vind hier meer artikelen over optelformules. Als je onze blogs leuk vond, deel het dan met je fristarts op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
SOM als datum tussen ligt : Retourneert de SOM van waarden tussen bepaalde datums of periode in Excel.
Som als datum groter is dan opgegeven datum: Retourneert de SOM van waarden na de opgegeven datum of periode in Excel.
2 manieren om per maand op te tellen in Excel: Retourneert de SOM van waarden binnen een bepaalde specifieke maand in Excel.
Meerdere kolommen optellen met voorwaarde: Retourneert de SOM van waarden over meerdere kolommen met voorwaarde in Excel.
Populaire artikels :
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.