Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, kuinka löytää tekstiarvojen summa, jossa kullekin tällaiselle tekstiarvolle on määritetty yksilöllinen koodi Excelissä ja Google Sheetsissä.
SUM -numerot, jotka on tallennettu tekstinä
Ensin tarkastelemme kuinka lasketaan yhteen tekstiksi tallennetut tai muotoillut numerot.
Seuraavassa esimerkissä sarake Palkka tallennetaan tekstinä. Jos yrität laskea arvot yhteen, Excel näyttää nollaa.
| 1 | = SUMMA (C3: C7) |
Sen sijaan voit suorittaa SUM -operaation tekstinä tallennetuille numeroille käyttämällä taulukkokaavaa, jossa on SUM- ja VALUE -funktiot seuraavasti:
| 1 | = SUMMA (ARVO (C3: C7)) |
VALUE -funktio muuntaa numeroa edustavan tekstin numeroksi. SUM -funktio summaa nämä luvut.
Excel 365: ssä ja Excelin uudemmassa kuin 2022 -versiossa voit kirjoittaa kaavan tavalliseen tapaan. Kuitenkin, kun käytät Excel 2022 ja sitä vanhempia versioita, sinun on annettava taulukon kaava painamalla CTRL + VAIHTO + ENTER (sijasta TULLA SISÄÄN), kertoen Excelille, että kaava on matriisikaava. Tiedät sen olevan matriisikaava kaavan ympärillä olevista kiharoista suluista (katso yläkuva). Excelin ja Excel 365: n uudemmissa versioissa voit yksinkertaisesti painaa TULLA SISÄÄN sen sijaan.
Katsokaamme seuraavaa selitystä saadaksemme paremman käsityksen kaavasta.
VALUE -toiminto
Matriisikaavana käytettäessä VALUE -funktio muuntaa koko tekstiksi tallennetun numeroalueen numerosarjaksi ja palauttaa sen SUM -funktion tulona.
| 1 | = SUMMA ({10000; 6500; 7500; 15000; 8000}) |
Jos haluat nähdä, mitä VALUE -funktio palauttaa, valitse haluamasi toiminto ja paina F9.
SUMMA tekstiarvoista
Jos haluat laskea yhteen teksti -arvojen alueen, jossa kullekin tällaiselle tekstiarvolle on määritetty yksilöllinen koodi, voidaan käyttää taulukkokaavaa.
Seuraavassa taulukossa kerrotaan, mitä ihmiset valitsevat kolmesta kysymyksestä. Oikealla olevassa taulukossa on lueteltu neljä mahdollista vastausta, joista jokaiselle on annettu koodiarvo. Meidän on laskettava yhteen kunkin henkilön koodit.
| 1 | = SUMMA (INDEKSI (I $ 3: I $ 6, N (JOS (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
Käymme alla olevan kaavan läpi.
MATCH -toiminto
MATCH -toiminto etsii määriteltyä kohdetta alueelta ja palauttaa sen suhteellisen sijainnin kyseisellä alueella. Sen syntaksi on:
Koska tässä esimerkissä on taulukkokaava, katsotaan, mitä MATCH -funktio palauttaa.
| 1 | = SUMMA (INDEKSI (I $ 3: I $ 6, N (JOS (1, {1,3,2}))))) |
Tietylle henkilölle MATCH -toiminto löytää kunkin vastauksen suhteellisen sijainnin alueella H3: H6. Tuloksena on joukko kantoja.
Huomautus: Jos haluat nähdä, mitä funktio palauttaa, valitse taulukkokaavassa haluamasi toiminto ja paina F9.
IF & N -toiminto
Yhdessä käytetyt IF- ja N -funktiot palauttavat seuraavan taulukon INDEX -funktion tulona.
| 1 | = SUMMA (INDEKSI (I $ 3: I $ 6, {1,3,2})) |
Tässä kaksi funktiota palauttaa joukon vastausten suhteellisen sijainnin alueella H3: H6. IF- ja N -toimintojen käytön tarkoitus on suorittaa dereferencing -prosessi. Yksinkertaisesti sanottuna nämä kaksi toimintoa pakottavat INDEX -funktion välittämään koko koodiarvoryhmän SUM -funktiolle.
Selitämme tämän seuraavassa osassa.
INDEX -toiminto
INDEX -funktio palauttaa alueen tietyn rivin ja sarakkeen leikkauspisteeseen sijoitetun arvon. Sen syntaksi on:
Katsotaanpa, miten se toimii taulukkokaavana:
| 1 | = SUMMA ({10,5,8}) |
INDEX -toiminto etsii alueen koodiarvot I3: I6 annettujen sijaintinumeroiden mukaan. Sitten se palauttaa SUM -funktiolle toimintojen suorittamiseksi arvoryhmän, ts. Kunkin vastauksen koodin.
Varmista, että rivien ja sarakkeiden määrä on molemmissa Vastaus ja Koodi sarake on sama.
SUM -toiminto
SUM -funktio summaa INDEX -funktion palauttamat koodiarvot.
| 1 | = SUMMA ({10,5,8}) |
Kaikki tämä yhdessä tuottaa alkuperäisen kaavamme:
| 1 | {= SUMMA (INDEKSI (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))}) |
SUMMA tekstiarvoista - ilman IF & N -toimintoja
Tässä osassa selitetään, miten Excel reagoi, jos emme käytä IF- ja N -funktiota yllä mainitussa kaavassa.
Samaa esimerkkiä käytetään samoilla koodeilla ja vastauksilla.
| 1 | {= SUMMA (INDEKSI (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))) |
Kuten näette, INDEX -toiminto välittää vain SUM -funktion ensimmäisen vastauksen koodin. Jos tarkastelet INDEX -toimintoa painamalla F9 saat seuraavan:
Arvo! Virhe palautetaan, koska INDEX -funktio ei voi lukea rivinumeroiden matriisia taulukkona. Näin ollen IF & N -toiminnon käyttö tekee tempun.
Huomautus: Excel 365: ssä voit ohittaa IF- ja N -funktioiden käytön kokonaan.
Sumuteksti- Google Sheets
Nämä kaavat toimivat samalla tavalla Google Sheetsissä kuin Excelissä.
