Ladda ner exempelarbetsboken
Denna handledning visar hur man hittar summan av textvärden där en unik kod tilldelas varje sådant textvärde i Excel och Google Sheets.
SUM -nummer lagras som text
Först ska vi titta på hur man summerar nummer lagrade eller formaterade som text.
I följande exempel lagras kolumnen Lön som text. Om du försöker summera värdena kommer Excel att visa en nolla.
| 1 | = SUMMA (C3: C7) |
För att i stället utföra SUM -operationen på nummer som lagras som text kan du använda en matrisformel med funktionerna SUMMA och VÄRDE så här:
| 1 | = SUMMA (VÄRDE (C3: C7)) |
VALUE -funktionen konverterar en text som representerar ett tal till ett tal. SUM -funktionen summerar dessa nummer.
I Excel 365 och version av Excel nyare än 2022 kan du helt enkelt ange formeln som normalt. När du använder Excel 2022 och tidigare måste du dock ange matrisformeln genom att trycka på CTRL + SKIFT + ENTER (istället för STIGA PÅ), berättar för Excel att formeln är en matrisformel. Du vet att det är en matrisformel av de lockiga parenteserna som visas runt formeln (se översta bilden). I senare versioner av Excel och Excel 365 kan du helt enkelt trycka på STIGA PÅ istället.
Låt oss titta på följande förklaring för att få en bättre förståelse av formeln.
VÄRDE Funktion
VALUE -funktionen används som en matrisformel och konverterar hela antalet nummer som lagras som text till en rad nummer och returnerar det som en ingång för SUM -funktionen.
| 1 | = SUMMA ({10000; 6500; 7500; 15000; 8000}) |
För att se vad VÄRDE -funktionen returnerar, välj önskad funktion och tryck på F9.
SUMMA av textvärden
För att SUMMA ett intervall med textvärden där en unik kod tilldelas varje sådant textvärde kan en matrisformel användas.
Följande tabell registrerar vad människor väljer för tre givna frågor. Tabellen till höger visar de fyra möjliga svaren som vart och ett har tilldelats ett kodvärde. Vi måste summera koderna för varje person.
| 1 | = SUMMA (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Vi går igenom formeln nedan.
MATCH -funktion
MATCH -funktionen letar efter ett specifikt objekt i ett område och returnerar sin relativa position i det intervallet. Dess syntax är:
Som en matrisformel i det här exemplet, låt oss se vad MATCH -funktionen returnerar.
| 1 | = SUMMA (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
För en given person hittar MATCH -funktionen den relativa positionen för varje svar i intervallet H3: H6. Resultatet är en rad positioner.
Notera: Om du vill se vad en funktion returnerar i en matrisformel väljer du önskad funktion och trycker på F9.
IF & N -funktion
IF och N -funktionen som används tillsammans returnerar följande array som en ingång för INDEX -funktionen.
| 1 | = SUMMA (INDEX (I $ 3: I $ 6, {1,3,2}))) |
Här returnerar de två funktionerna en uppsättning av relativa positioner för svar i intervallet H3: H6. Syftet med att använda IF- och N -funktionerna är att utföra en process som kallas dereferencing. Enkelt uttryckt tvingar de två funktionerna INDEX -funktionen att vidarebefordra hela uppsättningen kodvärden till SUM -funktionen.
Vi förklarar detta i nästa avsnitt.
INDEX -funktion
INDEX -funktionen returnerar värdet placerat vid skärningspunkten mellan en angiven rad och kolumn i ett område. Dess syntax är:
Låt oss se hur det fungerar som en matrisformel:
| 1 | = SUMMA ({10,5,8}) |
INDEX -funktionen hittar kodvärdena i intervallet I3: I6 enligt de angivna positionsnumren. Den returnerar sedan en rad värden, dvs respektive kod för varje svar, till SUM -funktionen för att utföra operationer.
Se till att antalet rader och kolumner i båda Svar och den Koda kolumnen är densamma.
SUM -funktion
SUMMEFunktionen summerar kodvärdena som returneras av INDEX -funktionen.
| 1 | = SUMMA ({10,5,8}) |
Allt detta tillsammans ger vår initiala formel:
| 1 | {= SUMMA (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
SUMMA av textvärden - utan IF & N -funktioner
Det här avsnittet förklarar hur Excel svarar om vi inte använder IF och N -funktionen i ovanstående formel.
Samma exempel används med samma koder och svar.
| 1 | {= SUMMA (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Som du kan se skickar INDEX -funktionen endast koden för det första svaret till SUM -funktionen. Om du granskar INDEX -funktionen genom att trycka på F9 du får följande:
Värdet! Fel returneras eftersom INDEX -funktionen inte kan läsa arrayen med radnummer som en array. Därför gör användningen av IF & N -funktionen tricket.
Notera: I Excel 365 kan du helt och hållet hoppa över IF och N -funktionerna.
Sum Text- Google Sheets
Dessa formler fungerar på samma sätt i Google Kalkylark som i Excel.
