Download eksemplet på projektmappe
Denne vejledning vil demonstrere, hvordan man finder summen af tekstværdier, hvor en unik kode er tildelt hver sådan tekstværdi i Excel og Google Sheets.
SUM -numre gemt som tekst
Først vil vi se på, hvordan man summer summer gemt eller formateret som tekst.
I det følgende eksempel gemmes kolonnen Løn som tekst. Hvis du forsøger at opsummere værdierne, viser Excel et nul.
| 1 | = SUM (C3: C7) |
I stedet for at udføre SUM -operationen på numre, der er gemt som tekst, kan du bruge en matrixformel med funktionerne SUM og VÆRDI som denne:
| 1 | = SUM (VÆRDI (C3: C7)) |
VALUE -funktionen konverterer en tekst, der repræsenterer et tal, til et tal. SUM -funktionen summerer disse tal.
I Excel 365 og version af Excel nyere end 2022 kan du blot indtaste formlen som normalt. Når du bruger Excel 2022 og tidligere, skal du dog indtaste matrixformlen ved at trykke på CTRL + SKIFT + ENTER (i stedet for GÅ IND) og fortæller Excel, at formlen er en matrixformel. Du ved, at det er en matrixformel af de krøllede parenteser, der vises omkring formlen (se øverste billede). I senere versioner af Excel og Excel 365 kan du blot trykke på GÅ IND i stedet.
Lad os se på følgende forklaring for at få en bedre forståelse af formlen.
VÆRDI Funktion
VALUE -funktionen bruges som en matrixformel og konverterer hele rækken af tal, der er gemt som tekst, til en matrix med tal og returnerer det som input til SUM -funktionen.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
For at se, hvad VALUE -funktionen returnerer, skal du vælge den nødvendige funktion og trykke på F9.
SUM af tekstværdier
For at SUMME en række tekstværdier, hvor der tildeles en unik kode til hver sådan tekstværdi, kan en matrixformel bruges.
Følgende tabel registrerer, hvad folk vælger til tre givne spørgsmål. Tabellen til højre viser de fire mulige svar, som hver har fået tildelt en kodeværdi. Vi er nødt til at opsummere koderne for hver person.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Vi går gennem formlen herunder.
MATCH -funktion
MATCH -funktionen leder efter et bestemt element i et område og returnerer sin relative position i dette område. Dens syntaks er:
Som en matrixformel i dette eksempel, lad os se, hvad MATCH -funktionen returnerer.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
For en given person finder MATCH -funktionen den relative position for hvert svar i intervallet H3: H6. Resultatet er en række positioner.
Bemærk: Hvis du vil se, hvad en funktion returnerer i en matrixformel, skal du vælge den nødvendige funktion og trykke på F9.
IF & N -funktion
IF og N -funktionen, der bruges sammen, returnerer følgende array som input til INDEX -funktionen.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Her returnerer de to funktioner en matrix med relativ position af svar i området H3: H6. Formålet med at bruge IF og N -funktionerne er at udføre en proces kaldet dereferencing. Enkelt sagt tvinger de to funktioner INDEX -funktionen til at videregive hele rækken af kodeværdier til SUM -funktionen.
Vi forklarer dette i det næste afsnit.
INDEX -funktion
INDEX -funktionen returnerer værdien, der er placeret i skæringspunktet mellem en bestemt række og kolonne i et område. Dens syntaks er:
Lad os se, hvordan det fungerer som en matrixformel:
| 1 | = SUM ({10,5,8}) |
INDEX -funktionen finder kodeværdierne i intervallet I3: I6 ifølge de givne positionsnumre. Det returnerer derefter en række værdier, dvs. den respektive kode for hvert svar, til SUM -funktionen for at udføre operationer.
Sørg for, at antallet af rækker og kolonner i begge Svar og Kode kolonne er den samme.
SUM -funktion
SUM -funktionen summerer kodeværdierne, der returneres af INDEX -funktionen.
| 1 | = SUM ({10,5,8}) |
Alt dette tilsammen giver vores oprindelige formel:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
SUM af tekstværdier - Uden IF & N -funktioner
Dette afsnit forklarer, hvordan Excel reagerer, hvis vi ikke bruger IF og N -funktionen i ovennævnte formel.
Det samme eksempel bruges med de samme koder og svar.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Som du kan se, sender INDEX -funktionen kun koden til det første svar til SUM -funktionen. Hvis du undersøger INDEX -funktionen ved at trykke på F9 får du følgende:
Værdien! Fejl returneres, fordi INDEX -funktionen ikke kan læse rækken af rækkenumre som en matrix. Derfor gør brugen af IF & N -funktionen tricket.
Bemærk: I Excel 365 kan du helt springe over IF og N -funktionerne.
Sum tekst- Google Sheets
Disse formler fungerer på samme måde i Google Sheets som i Excel.
