Som als cellen specifieke tekst in een andere kolom bevatten
Deze zelfstudie laat u zien hoe u waarden optelt als cellen specifieke of gedeeltelijke tekst in een andere kolom bevatten. Neem het onderstaande gegevensbereik als voorbeeld, om de totale hoeveelheden van de producten met de tekst "T-Shirt" te krijgen, kunnen zowel de SUMIF-functie als de SOMPRODUCT-functie deze taak in Excel uitvoeren.
- Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SUMIF-functie
- Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SOMPRODUCT-functie
Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SUMIF-functie
Als u waarden wilt optellen als een cel specifieke tekst in een andere kolom bevat, kunt u de SOM.ALS-functie gebruiken met een jokerteken (*). De generieke syntaxis zijn:
Generieke formule met hardgecodeerde tekst:
- range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;
- *text*: De criteria waarop u waarden wilt optellen op basis van. Hier wordt het * jokerteken gebruikt om een willekeurig aantal tekens te vinden, om alle items die een specifieke tekst bevatten te matchen, plaats de tekst tussen de twee * tekens. (Merk op dat u de tekst en het jokerteken tussen dubbele aanhalingstekens moet plaatsen.)
- sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen.
Generieke formule met celverwijzing:
- range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;
- "*"&cell&"*": de criteria waarop u waarden wilt optellen;
- *: Het jokerteken waarmee een willekeurig aantal tekens wordt gevonden.
- cel: de cel bevat de specifieke tekst waarnaar moet worden gezocht.
- &: Deze aaneenschakelingsoperator (&) wordt gebruikt om een celverwijzing samen te voegen met de sterretjes.
- sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen.
Nadat u de basiswerking van de functie kent, gebruikt u een van de volgende formules die u nodig heeft en drukt u vervolgens op Enter sleutel om het resultaat te krijgen:
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12) (Use a cell reference)
Note: Deze SUMIF-functie is niet hoofdlettergevoelig.
Somwaarden als cel specifieke of gedeeltelijke tekst bevat met SOMPRODUCT-functie
De SOMPRODUCT-functie kan u ook helpen om deze taak te voltooien zonder een jokerteken te gebruiken. Hier moet u de ISNUMBER- en SEARCH-functies in een SOMPRODUCT-functie plaatsen, de generieke syntaxis is:
- sum_range: het cellenbereik met overeenkomende numerieke waarden die u wilt optellen;
- criteria: De criteria waarop u waarden wilt optellen op basis van. Het kan een celverwijzing zijn of specifieke tekst die u hebt gedefinieerd;
- range: het gegevensbereik dat u wilt evalueren aan de hand van de criteria;
Gebruik een van de onderstaande formules in een lege cel en druk op Enter toets om het resultaat te retourneren:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12)))) (Use a cell reference)
Verklaring van deze formule:
=SOMPRODUCT($B$2:$B$12*(ISNUMMER(ZOEK("T-Shirt",$A$2:$A$12))))
- ZOEKEN("T-Shirt",$A$2:$A$12): De SEARCH-functie retourneert de locatie van de specifieke tekst "T-Shirt" uit het gegevensbereik A2:A12, dus u krijgt een array als deze: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#WAARDE!;#WAARDE!;#WAARDE!;#WAARDE!;7}.
- ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Deze functie ISNUMBER wordt gebruikt om te testen op de numerieke waarden en retourneert een nieuwe array: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
- $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Om deze twee arrays te vermenigvuldigen, dwingt de wiskundige bewerking de TRUE en FALSE-waarden tot 1s en 0s. De vermenigvuldiging van de twee arrays is dus als volgt: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1; 0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):Ten slotte telt de functie SOMPRODUCT alle waarden in de array op om het resultaat:1605 te krijgen.
Gebruikte relatieve functie:
- SUMIF:
- De SUMIF-functie kan helpen om cellen op te tellen op basis van één criterium.
- SUMPRODUCT:
- De SOMPRODUCT-functie kan worden gebruikt om twee of meer kolommen of arrays met elkaar te vermenigvuldigen en vervolgens de som van producten te krijgen.
- ISNUMMER:
- De Excel ISNUMBER-functie retourneert WAAR wanneer een cel een getal bevat, en ONWAAR als dat niet het geval is.
- ZOEKEN:
- De SEARCH-functie kan u helpen om de positie van een specifiek teken of substring uit de opgegeven tekstreeks te vinden
Meer artikelen:
- Som als begint met of eindigt met specifieke tekst of tekens
- Als u waarden wilt optellen als corresponderende cellen beginnen met of eindigen met een specifieke waarde, kunt u de SUMIF-functie toepassen met een jokerteken (*) om deze functie uit te voeren. In dit artikel wordt uitgelegd hoe u de formule in detail kunt gebruiken.
- Som kleinste of laagste N-waarden
- In Excel kunnen we gemakkelijk een celbereik optellen met behulp van de SOM-functie. Soms moet u mogelijk de kleinste of onderste 3, 5 of n getallen in een gegevensbereik optellen, zoals hieronder wordt weergegeven. In dit geval kan de SOMPRODUCT samen met de SMALL-functie u helpen dit probleem in Excel op te lossen.
- Som kleinste of laagste N-waarden op basis van criteria
- In de vorige zelfstudie hebben we besproken hoe u de kleinste n-waarden in een gegevensbereik kunt optellen. In dit artikel zullen we een verdere geavanceerde bewerking uitvoeren - om de laagste n-waarden op te tellen op basis van een of meer criteria in Excel.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
