Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du finner summen av tekstverdier der en unik kode er tilordnet hver slik tekstverdi i Excel og Google Sheets.
SUM -tall lagret som tekst
Først skal vi se på hvordan vi summerer tall som er lagret eller formatert som tekst.
I følgende eksempel er kolonnen Lønn lagret som tekst. Hvis du prøver å summere verdiene, vil Excel vise et null.
| 1 | = SUMMER (C3: C7) |
I stedet for å utføre SUM -operasjonen på tall som er lagret som tekst, kan du bruke en matriseformel med SUM- og VALUE -funksjonene slik:
| 1 | = SUMME (VERDI (C3: C7)) |
VALUE -funksjonen konverterer en tekst som representerer et tall til et tall. SUM -funksjonen summerer disse tallene.
I Excel 365 og versjon av Excel nyere enn 2022 kan du ganske enkelt angi formelen som vanlig. Når du bruker Excel 2022 og tidligere, må du imidlertid angi matriseformelen ved å trykke CTRL + SKIFT + ENTER (i stedet for TAST INN), og forteller Excel at formelen er en matriseformel. Du vet at det er en matriseformel av de krøllete parentesene som vises rundt formelen (se bildet øverst). I senere versjoner av Excel og Excel 365 kan du bare trykke TAST INN i stedet.
La oss se på følgende forklaring for å få en bedre forståelse av formelen.
VERDI Funksjon
Brukt som en matriseformel, konverterer VALUE -funksjonen hele tallområdet som er lagret som tekst til en rekke tall og returnerer det som en inngang for SUM -funksjonen.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
For å se hva VALUE -funksjonen returnerer, velg ønsket funksjon og trykk F9.
SUM av tekstverdier
For å SUMME et område med tekstverdier der en unik kode er tilordnet hver slik tekstverdi, kan en matriseformel brukes.
Tabellen nedenfor registrerer hva folk velger for tre gitte spørsmål. Tabellen til høyre viser de fire mulige svarene som hver har blitt tildelt en kodeverdi. Vi må summere kodene for hver person.
| 1 | = SUMME (INDEKS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Vi går gjennom formelen nedenfor.
MATCH -funksjon
MATCH -funksjonen ser etter et spesifisert element i et område og returnerer sin relative posisjon i det området. Syntaksen er:
Som en matriseformel i dette eksemplet, la oss se hva MATCH -funksjonen returnerer.
| 1 | = SUMME (INDEKS (I $ 3: I $ 6, N (HVIS (1, {1,3,2})))) |
For en gitt person finner MATCH -funksjonen den relative posisjonen til hvert svar i området H3: H6. Resultatet er en rekke posisjoner.
Merk: For å se hva en funksjon returnerer i en matriseformel, velg den nødvendige funksjonen og trykk F9.
IF & N -funksjon
IF og N -funksjonen som brukes sammen returnerer følgende array som en inngang for INDEX -funksjonen.
| 1 | = SUMME (INDEKS (I $ 3: I $ 6, {1,3,2})) |
Her returnerer de to funksjonene en rekke relative posisjoner for svar i området H3: H6. Formålet med å bruke IF og N -funksjonene er å utføre en prosess som kalles dereferencing. Enkelt sagt tvinger de to funksjonene INDEX -funksjonen til å videresende hele koden med verdier til SUM -funksjonen.
Vi forklarer dette i neste avsnitt.
INDEKS Funksjon
INDEX -funksjonen returnerer verdien som er plassert i krysset mellom en spesifisert rad og kolonne i et område. Syntaksen er:
La oss se hvordan det fungerer som en matriseformel:
| 1 | = SUM ({10,5,8}) |
INDEX -funksjonen finner kodeverdiene i området I3: I6 i henhold til oppgitte posisjonsnumre. Den returnerer deretter en rekke verdier, dvs. den respektive koden for hvert svar, til SUM -funksjonen for å utføre operasjoner.
Sørg for at antall rader og kolonner i begge Svar og Kode kolonne er den samme.
SUM -funksjon
SUM -funksjonen summerer kodeverdiene som returneres av INDEX -funksjonen.
| 1 | = SUM ({10,5,8}) |
Alt dette sammen gir vår første formel:
| 1 | {= SUMME (INDEKS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
SUM av tekstverdier - Uten IF & N -funksjoner
Denne delen forklarer hvordan Excel reagerer hvis vi ikke bruker IF og N -funksjonen i formelen ovenfor.
Det samme eksemplet brukes med de samme kodene og svarene.
| 1 | {= SUMME (INDEKS (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Som du kan se, sender INDEX -funksjonen bare koden for det første svaret til SUM -funksjonen. Hvis du undersøker INDEX -funksjonen ved å trykke på F9 får du følgende:
Verdien! Feil returneres fordi INDEX -funksjonen ikke kan lese rekken med radnummer som en matrise. Derfor gjør bruk av IF & N -funksjonen trikset.
Merk: I Excel 365 kan du hoppe over å bruke IF og N -funksjonene helt.
Sum tekst- Google Regneark
Disse formlene fungerer på samme måte i Google Regneark som i Excel.
