Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de SUMIFS-functie en jokertekens kunt gebruiken om gegevens op te tellen die overeenkomen met cellen die specifieke tekst bevatten in Excel en Google Spreadsheets.
Som als tekst bevat
Eerst zullen we laten zien hoe gegevens met betrekking tot cellen met specifieke tekst kunnen worden opgeteld met behulp van de SUMIFS-functie.
De SOMMEN-functie somt gegevensrijen op die aan bepaalde criteria voldoen. De syntaxis is:
Dit voorbeeld somt alles op Scores met een Staat naam die "Dakota" bevat met behulp van de SUMIFS-functie en het * jokerteken.
| 1 | =SOMMEN.(C3:C9,B3:B9,"*Dakota*") |
Het *-teken zorgt ervoor dat een willekeurig aantal (inclusief nul) van andere tekens zijn plaats kan innemen.
In dit voorbeeld wordt het gebruikt om alle cellen te vinden die de tekst "Dakota" bevatten. Deze zoekopdracht is niet hoofdlettergevoelig, dus "dakota" wordt als hetzelfde beschouwd als "Dakota" of "DAKOTA". Noord-Dakota en zuid Dakota bevatten “Dakota” en zijn daarom inbegrepen in de som.
Som als tekst begint met
Het *-teken kan ook worden gebruikt om te zoeken naar cellen die beginnen met de opgegeven tekst:
| 1 | =SOMMEN.(C3:C9,B3:B9,"Nieuw*") |
New York, New Jersey, en New Mexico beginnen met “Nieuw” en zijn dus inbegrepen in de som. Merk op dat bij de zoekterm "Nieuw*" de tekstcel moet beginnen met "Nieuw"; alleen die karakters bevatten is niet genoeg.
Som als tekst eindigt met
Op dezelfde manier kunnen we alles optellen Scores voor Staten eindigend op "o" met:
| 1 | =SOMMEN.(C3:C9,B3:B9,"*o") |
New Mexico en Ohio eindigen op een "o" en zijn daarom opgenomen in de som.
De … gebruiken ? Jokerteken
De ? karakter kan worden gebruikt om elk afzonderlijk teken in een tekstreeks weer te geven.
Dit voorbeeld vindt alles Staatsnamen beginnend met "Nieuw", gevolgd door precies 7 tekens (inclusief spaties).
| 1 | =SOMMEN.ALS(C3:C9,B3:B9,"Nieuw???????") |
New Jersey en New Mexico aan deze criteria voldoen, maar New York niet, aangezien er slechts 5 tekens na "Nieuw" in New York.
Merk op dat * en ? indien nodig kunnen jokertekens worden gecombineerd om zeer specifieke zoekopdrachten te maken. Dit volgende voorbeeld vindt Staatsnamen die beginnen met "N" en een "o" bevatten voor het laatste teken van de tekenreeks. Dit is exclusief New Mexico; het begint met "N" maar heeft geen "o" voor het laatste teken.
| 1 | =SOMMEN.ALS(C3:C9,B3:B9,"N*o?*") |
Deze functionaliteit is vooral handig bij het zoeken in productcodes, postcodes of serienummers waarbij de positie van elk teken een specifieke betekenis heeft.
Het ~ (Tilde)-teken gebruiken
Het speciale teken ~ (bekend als a tilde) stelt ons in staat om de * of ? tekens alsof het eenvoudige tekstwaarden zijn en zich niet gedragen als jokertekens.
In het onderstaande voorbeeld moeten we de optellen Voorraadniveau wanneer de productnaam komt specifiek overeen met de tekst “Product ?”:
| 1 | =SOMMEN.(C3:C8,B3:B8,"Product ~?") |
De ~ vlak voor een * of een ? karakter verandert het weer in een tekstwaarde, dus de zoekterm "Product ~?" wordt gebruikt om een exacte tekstovereenkomst met "Product?" te vinden.
SUMIFS-jokertekens combineren met celverwijzingen
Gewoonlijk is het geen goede gewoonte om waarden hard in formules te coderen. In plaats daarvan is het flexibeler om afzonderlijke cellen te gebruiken om waarden voor onze zoektermen op te geven.
Om te zoeken of de cellen de tekst in cel E3 bevatten, kunnen we de SOMMEN-functie gebruiken met een celverwijzing en * wildcards:
| 1 | =SOMMEN.(C3:C9,B3:B9,"*"&E3&"*") |
Merk op dat de tekst "Dakota" is vervangen door de celverwijzing &E3& en dat de *-tekens tussen aanhalingstekens zijn geplaatst (" ").
Meerdere celverwijzingen en jokertekens kunnen ook met elkaar worden gecombineerd. Vinden Staatsnamen die beginnen met de tekst in cel E3 en de tekst in cel F3 bevatten gevolgd door minstens 1 extra teken, kan de volgende formule worden gebruikt:
| 1 | =SOMMEN.(C3:C9,B3:B9,E3&"*"&F3&"?*") |
Celverwijzingen vergrendelen
Om onze formules leesbaarder te maken, hebben we de formules weergegeven zonder vergrendelde celverwijzingen:
| 1 | =SOMMEN.(C3:C9,B3:B9,"*"&E3&"*") |
Maar deze formules werken niet goed wanneer ze ergens anders in uw bestand worden gekopieerd en geplakt. Gebruik in plaats daarvan vergrendelde celverwijzingen zoals deze:
| 1 | =SOMMEN($C$3:$C$9,$B$3:$B$9,"*"&E3&"*") |
Lees ons artikel over het vergrendelen van celreferenties voor meer informatie.
Som als cel specifieke tekst bevat met jokertekens in Google Spreadsheets
Deze formules werken in Google Spreadsheets precies hetzelfde als in Excel.
