Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip rasti teksto reikšmių sumą, kur kiekvienai tokiai teksto vertei „Excel“ ir „Google“ skaičiuoklėse priskiriamas unikalus kodas.
SUM numeriai, išsaugoti kaip tekstas
Pirmiausia apžvelgsime, kaip susumuoti skaičius, išsaugotus arba suformatuotus kaip tekstas.
Šiame pavyzdyje stulpelis „Atlyginimas“ saugomas kaip tekstas. Jei bandysite susumuoti reikšmes, „Excel“ parodys nulį.
| 1 | = SUMA (C3: C7) |
Vietoj to, norėdami atlikti SUM operaciją skaičiais, saugomais kaip tekstas, galite naudoti masyvo formulę su funkcijomis SUM ir VALUE, kaip nurodyta toliau:
| 1 | = SUMA (VERTĖ (C3: C7)) |
Funkcija VALUE konvertuoja skaičių vaizduojantį tekstą į skaičių. Funkcija SUM sumuoja šiuos skaičius.
„Excel 365“ ir naujesnėje nei 2022 m. „Excel“ versijoje galite tiesiog įvesti formulę kaip įprasta. Tačiau, kai naudojate „Excel 2022“ ir ankstesnes versijas, turite įvesti masyvo formulę paspausdami CTRL + SHIFT + ENTER (vietoj Įveskite), nurodydami „Excel“, kad formulė yra masyvo formulė. Jūs žinosite, kad tai masyvo formulė pagal garbanotus skliaustus, esančius aplink formulę (žr. Viršutinį paveikslėlį). Vėlesnėse „Excel“ ir „Excel 365“ versijose galite tiesiog paspausti Įveskite vietoj to.
Pažvelkime į šį paaiškinimą, kad geriau suprastume formulę.
Funkcija VALUE
Funkcija VALUE, naudojama kaip masyvo formulė, visą skaičių diapazoną, saugomą kaip tekstą, paverčia skaičių masyvu ir grąžina jį kaip SUM funkcijos įvestį.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Norėdami pamatyti, ką grąžina funkcija VALUE, pasirinkite reikiamą funkciją ir paspauskite F9.
Teksto reikšmių suma
Norėdami sudėti teksto reikšmių diapazoną, kur kiekvienai tokiai teksto vertei priskiriamas unikalus kodas, galima naudoti masyvo formulę.
Šioje lentelėje įrašoma, ką žmonės pasirenka pagal tris pateiktus klausimus. Dešinėje esančioje lentelėje išvardyti keturi galimi atsakymai, kiekvienam iš jų priskirta kodo reikšmė. Turime susumuoti kiekvieno žmogaus kodus.
| 1 | = SUMA (INDEKSAS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))) |
Mes eisime per žemiau pateiktą formulę.
Funkcija MATCH
Funkcija MATCH ieško nurodyto diapazono elemento ir grąžina jo santykinę padėtį tame diapazone. Jo sintaksė yra tokia:
Kaip pavyzdžio masyvo formulė, pažiūrėkime, ką grąžina funkcija MATCH.
| 1 | = SUMA (INDEKSAS (I $ 3: I $ 6, N (IF (1, {1,3,2})))))) |
Tam tikram asmeniui funkcija MATCH nustato santykinę kiekvieno atsakymo padėtį diapazone H3: H6. Rezultatas yra pozicijų masyvas.
Pastaba: Masyvo formulėje, norėdami pamatyti, kokia funkcija grąžina, pasirinkite reikiamą funkciją ir paspauskite F9.
IF & N funkcija
Kartu naudojamos IF ir N funkcijos grąžina šį masyvą kaip INDEX funkcijos įvestį.
| 1 | = SUMA (INDEKSAS (3 USD: 6 USD, {1,3,2})) |
Čia abi funkcijos grąžina masinę atsakymų padėties masyvą diapazone H3: H6. IF ir N funkcijų naudojimo tikslas yra atlikti procesą, vadinamą dereferencingu. Paprasčiau tariant, abi funkcijos verčia INDEX funkciją perduoti visą kodo reikšmių masyvą funkcijai SUM.
Mes tai paaiškiname kitame skyriuje.
INDEX funkcija
Funkcija INDEX grąžina reikšmę, esančią nurodytos diapazono eilutės ir stulpelio sankirtoje. Jo sintaksė yra tokia:
Pažiūrėkime, kaip tai veikia kaip masyvo formulė:
| 1 | = SUMA ({10,5,8}) |
Funkcija INDEX nustato diapazono kodo reikšmes I3: I6 pagal nurodytus pozicijos numerius. Tada ji grąžina reikšmių masyvą, t. Y. Atitinkamą kiekvieno atsakymo kodą, funkcijai SUM atlikti operacijas.
Įsitikinkite, kad abiejose eilutėse ir stulpeliuose yra skaičius Atsakymas ir Kodas stulpelis tas pats.
SUM funkcija
Funkcija SUM susumuoja INDEX funkcijos grąžintas kodo reikšmes.
| 1 | = SUMA ({10,5,8}) |
Visa tai sudaro mūsų pradinę formulę:
| 1 | {= SUMA (INDEKSAS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))))}} |
Teksto verčių suma - be IF ir N funkcijų
Šiame skyriuje paaiškinta, kaip „Excel“ reaguoja, jei aukščiau minėtoje formulėje nenaudojame IF ir N funkcijos.
Tas pats pavyzdys naudojamas su tais pačiais kodais ir atsakymais.
| 1 | {= SUMA (INDEKSAS (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Kaip matote, funkcija INDEX perduoda tik pirmojo atsakymo į SUM funkciją kodą. Jei tikrinate INDEX funkciją paspausdami F9 gausite šiuos dalykus:
Vertė! Klaida grąžinama, nes funkcija INDEX negali perskaityti eilutės numerių masyvo kaip masyvo. Taigi, naudojant funkciją „IF“ ir „N“ tai pavyksta.
Pastaba: „Excel 365“ galite praleisti IF ir N funkcijas.
Sumos tekstas- „Google“ skaičiuoklės
Šios formulės veikia taip pat „Google“ skaičiuoklėse kaip ir „Excel“.
