Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să găsiți suma valorilor textului în care un cod unic este atribuit fiecărei astfel de valori text în Excel și Foi de calcul Google.
SUM Numere stocate ca text
Mai întâi ne vom uita la cum să sumăm numerele stocate sau formatate ca text.
În exemplul următor, coloana Salariu este stocată ca text. Dacă încercați să însumați valorile, Excel va afișa un zero.
| 1 | = SUMĂ (C3: C7) |
În schimb, pentru a efectua operația SUM pe numerele stocate ca text, puteți utiliza o formulă matrice cu funcțiile SUM și VALUE astfel:
| 1 | = SUMĂ (VALOARE (C3: C7)) |
Funcția VALUE convertește un text care reprezintă un număr într-un număr. Funcția SUM rezumă aceste numere.
În Excel 365 și versiunea de Excel mai recentă decât 2022, puteți introduce pur și simplu formula ca în mod normal. Cu toate acestea, atunci când utilizați Excel 2022 și versiunile anterioare, trebuie să introduceți formula matrice apăsând CTRL + SHIFT + ENTER (in loc de INTRODUCE), spunând Excel că formula este o formulă matrice. Veți ști că este o formulă matrice după parantezele crețate care apar în jurul formulei (a se vedea imaginea de sus). În versiunile ulterioare ale Excel și Excel 365, puteți apăsa pur și simplu INTRODUCE in schimb.
Să analizăm următoarea explicație pentru a înțelege mai bine formula.
Funcția VALUE
Utilizată ca formulă matrice, funcția VALUE convertește întreaga gamă de numere stocate ca text într-o matrice de numere și o returnează ca intrare pentru funcția SUM.
| 1 | = SUMĂ ({10000; 6500; 7500; 15000; 8000}) |
Pentru a vedea ce returnează funcția VALUE, selectați funcția dorită și apăsați F9.
SUMA valorilor textului
Pentru a SUMA o gamă de valori de text în care un cod unic este atribuit fiecărei astfel de valori de text, poate fi utilizată o formulă matrice.
Următorul tabel înregistrează ce selectează oamenii pentru trei întrebări date. Tabelul din dreapta listează cele patru răspunsuri posibile cărora li s-a atribuit o valoare de cod. Trebuie să însumăm codurile pentru fiecare persoană.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Vom parcurge formula de mai jos.
Funcția MATCH
Funcția MATCH caută un articol specificat într-un interval și își returnează poziția relativă în acel interval. Sintaxa sa este:
Fiind o formulă matrice în acest exemplu, să vedem ce returnează funcția MATCH.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Pentru o anumită persoană, funcția MATCH găsește poziția relativă a fiecărui răspuns din interval H3: H6. Rezultatul este o serie de poziții.
Notă: Într-o formulă matrice, pentru a vedea ce returnează o funcție, selectați funcția dorită și apăsați F9.
Funcția IF & N
Funcția IF și N utilizate împreună returnează următoarea matrice ca intrare pentru funcția INDEX.
| 1 | = SUMĂ (INDEX (I $ 3: I $ 6, {1,3,2})) |
Aici, cele două funcții returnează o serie de poziții relative ale răspunsurilor în interval H3: H6. Scopul utilizării funcțiilor IF și N este de a efectua un proces numit dereferențiere. În termeni simpli, cele două funcții forțează funcția INDEX să transmită întreaga matrice de valori de cod funcției SUM.
Vă explicăm acest lucru în secțiunea următoare.
Funcția INDEX
Funcția INDEX returnează valoarea poziționată la intersecția unui rând și a unei coloane specificate într-un interval. Sintaxa sa este:
Să vedem cum funcționează ca o formulă matrice:
| 1 | = SUMĂ ({10,5,8}) |
Funcția INDEX găsește valorile codului în interval I3: I6 conform numerelor de poziție date. Apoi returnează o serie de valori, adică codul respectiv pentru fiecare răspuns, la funcția SUM pentru a efectua operațiuni.
Asigurați-vă că numărul de rânduri și coloane din ambele Răspuns si Cod coloana este aceeași.
Funcția SUM
Funcția SUM va însuma valorile codului returnate de funcția INDEX.
| 1 | = SUMĂ ({10,5,8}) |
Toate acestea puse împreună produc formula noastră inițială:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
SUMA valorilor textului - fără funcții IF & N
Această secțiune explică modul în care Excel răspunde dacă nu folosim funcția IF și N în formula menționată mai sus.
Același exemplu este utilizat cu aceleași coduri și răspunsuri.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
După cum puteți vedea, funcția INDEX transmite doar codul pentru primul răspuns la funcția SUM. Dacă scrutați funcția INDEX apăsând F9 veți primi următoarele:
Valoarea! Eroarea este returnată deoarece funcția INDEX nu poate citi matricea numerelor de rând ca matrice. Prin urmare, utilizarea funcției IF și N face truc.
Notă: În Excel 365, puteți sări peste utilizarea funcțiilor IF și N în totalitate.
Suma text - Foi de calcul Google
Aceste formule funcționează la fel în Foi de calcul Google ca în Excel.
