Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u de som van tekstwaarden kunt vinden waarbij een unieke code is toegewezen aan elke dergelijke tekstwaarde in Excel en Google Spreadsheets.
SOM-nummers opgeslagen als tekst
Eerst zullen we kijken hoe we getallen kunnen optellen die zijn opgeslagen of opgemaakt als tekst.
In het volgende voorbeeld wordt de kolom Salaris als tekst opgeslagen. Als u probeert de waarden op te tellen, geeft Excel een nul weer.
| 1 | =SOM(C3:C7) |
Om in plaats daarvan de SOM-bewerking uit te voeren op getallen die als tekst zijn opgeslagen, kunt u een matrixformule gebruiken met de functies SUM en VALUE zoals deze:
| 1 | =SOM(WAARDE(C3:C7)) |
De VALUE-functie converteert een tekst die een getal voorstelt naar een getal. De SOM-functie telt die getallen op.
In Excel 365 en versie van Excel nieuwer dan 2022, kunt u de formule gewoon zoals normaal invoeren. Als u echter Excel 2022 en eerder gebruikt, moet u de matrixformule invoeren door op te drukken CTRL + SHIFT + ENTER (in plaats van BINNENKOMEN), Excel vertellen dat de formule een matrixformule is. U weet dat het een matrixformule is aan de accolades die rond de formule verschijnen (zie bovenste afbeelding). In latere versies van Excel en Excel 365 kunt u gewoon op . drukken BINNENKOMEN in plaats daarvan.
Laten we naar de volgende uitleg kijken om de formule beter te begrijpen.
WAARDE Functie
Gebruikt als een matrixformule, converteert de VALUE-functie het hele bereik van getallen die als tekst zijn opgeslagen naar een matrix van getallen en retourneert deze als invoer voor de SOM-functie.
| 1 | =SOM({10000;6500;7500;15000;8000}) |
Om te zien wat de functie WAARDE oplevert, selecteert u de gewenste functie en drukt u op F9.
SOM van tekstwaarden
Om een reeks tekstwaarden op te tellen waarbij een unieke code is toegewezen aan elke dergelijke tekstwaarde, kan een matrixformule worden gebruikt.
In de volgende tabel staat wat mensen selecteren voor drie gegeven vragen. In de tabel rechts staan de vier mogelijke antwoorden die elk een codewaarde hebben gekregen. We moeten de codes voor elke persoon optellen.
| 1 | =SOM(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0))))) |
We zullen de onderstaande formule doorlopen.
MATCH-functie
De MATCH-functie zoekt naar een opgegeven item in een bereik en retourneert de relatieve positie in dat bereik. De syntaxis is:
Omdat het in dit voorbeeld een matrixformule is, laten we eens kijken wat de MATCH-functie retourneert.
| 1 | =SOM(INDEX(I$3:I$6,N(IF(1,{1,3,2})))) |
Voor een bepaalde persoon vindt de MATCH-functie de relatieve positie van elk antwoord in het bereik H3:H6. Het resultaat is een scala aan posities.
Opmerking: Om in een matrixformule te zien wat een functie teruggeeft, selecteert u de gewenste functie en drukt u op F9.
ALS & N-functie
De IF- en de N-functie die samen worden gebruikt, retourneren de volgende array als invoer voor de INDEX-functie.
| 1 | =SOM(INDEX(I$3:I$6,{1,3,2})) |
Hier retourneren de twee functies een array van relatieve positie van antwoorden in het bereik H3:H6. Het doel van het gebruik van de IF- en de N-functies is om een proces uit te voeren dat dereferentie wordt genoemd. In eenvoudige bewoordingen dwingen de twee functies de INDEX-functie om de hele reeks codewaarden door te geven aan de SOM-functie.
We leggen dit uit in de volgende paragraaf.
INDEX-functie
De functie INDEX retourneert de waarde die is gepositioneerd op het snijpunt van een opgegeven rij en kolom in een bereik. De syntaxis is:
Laten we eens kijken hoe het werkt als een matrixformule:
| 1 | =SOM({10,5,8}) |
De INDEX-functie vindt de codewaarden in het bereik I3:I6 volgens de opgegeven positienummers. Het retourneert vervolgens een reeks waarden, d.w.z. de respectieve code voor elk antwoord, naar de SOM-functie om bewerkingen uit te voeren.
Zorg ervoor dat het aantal rijen en kolommen in zowel de Antwoord geven en de Code kolom is hetzelfde.
SOM-functie
De SOM-functie zal de codewaarden optellen die worden geretourneerd door de INDEX-functie.
| 1 | =SOM({10,5,8}) |
Dit alles bij elkaar levert onze initiële formule op:
| 1 | {=SUM(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0)))))} |
SOM van tekstwaarden - zonder IF & N-functies
In deze sectie wordt uitgelegd hoe Excel reageert als we de IF- en de N-functie niet gebruiken in de bovengenoemde formule.
Hetzelfde voorbeeld wordt gebruikt met dezelfde codes en antwoorden.
| 1 | {=SOM(INDEX(I$3:I$6,MATCH(C4:E4,H$3:H$6,0)))} |
Zoals u kunt zien, geeft de INDEX-functie alleen de code voor het eerste antwoord door aan de SOM-functie. Als u de INDEX-functie nauwkeurig bekijkt door op . te drukken F9 je krijgt het volgende:
De waarde! Er wordt een fout geretourneerd omdat de INDEX-functie de reeks rijnummers niet als een reeks kan lezen. Daarom werkt het gebruik van de IF & the N-functie.
Opmerking: In Excel 365 kunt u het gebruik van de IF- en de N-functies helemaal overslaan.
Som tekst - Google Spreadsheets
Deze formules werken in Google Spreadsheets hetzelfde als in Excel.
