Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako pronaći zbroj tekstualnih vrijednosti gdje je svakoj takvoj tekstualnoj vrijednosti dodijeljen jedinstveni kôd u Excelu i Google tablicama.
SUM brojevi pohranjeni kao tekst
Prvo ćemo pogledati kako zbrojiti brojeve pohranjene ili oblikovane kao tekst.
U sljedećem primjeru stupac Plaća pohranjen je kao tekst. Ako pokušate zbrojiti vrijednosti, Excel će prikazati nulu.
| 1 | = ZBIR (C3: C7) |
Umjesto toga, za izvođenje operacije SUM na brojevima pohranjenim kao tekst, možete koristiti formulu niza s funkcijama SUM i VALUE poput ove:
| 1 | = ZBIR (VRIJEDNOST (C3: C7)) |
Funkcija VALUE pretvara tekst koji predstavlja broj u broj. Funkcija SUM zbraja te brojeve.
U Excelu 365 i verziji Excela novijoj od 2022. formulu možete jednostavno unijeti kao i obično. Međutim, kada koristite Excel 2022 i starije verzije, morate unijeti formulu polja pritiskom na CTRL + SHIFT + ENTER (umjesto UNESI), govoreći Excelu da je formula formula niza. Znat ćete da je to formula niza po uvojljenim zagradama koje se pojavljuju oko formule (pogledajte gornju sliku). U kasnijim verzijama programa Excel i Excel 365 možete jednostavno pritisnuti UNESI umjesto toga.
Pogledajmo sljedeće objašnjenje kako bismo bolje razumjeli formulu.
VRIJEDNOST Funkcija
Koristeći se kao formula niza, funkcija VALUE pretvara cijeli niz brojeva pohranjenih kao tekst u niz brojeva i vraća je kao ulaz za funkciju SUM.
| 1 | = ZBIR ({10000; 6500; 7500; 15000; 8000}) |
Da biste vidjeli što funkcija VALUE vraća, odaberite željenu funkciju i pritisnite F9.
ZBOR tekstualnih vrijednosti
Za ZBIR raspona tekstualnih vrijednosti gdje je svakoj takvoj tekstualnoj vrijednosti dodijeljen jedinstveni kôd, može se koristiti formula niza.
Sljedeća tablica bilježi ono što su ljudi odabrali za tri zadana pitanja. Tablica s desne strane navodi četiri moguća odgovora od kojih je svakom dodijeljena vrijednost koda. Moramo zbrojiti kodove za svaku osobu.
| 1 | = SUM (INDEKS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Proći ćemo kroz donju formulu.
MATCH funkcija
Funkcija MATCH traži određenu stavku u rasponu i vraća njezin relativni položaj u tom rasponu. Njegova sintaksa je:
Budući da je formula niza u ovom primjeru, pogledajmo što funkcija MATCH vraća.
| 1 | = ZBIR (INDEKS (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Za datu osobu funkcija MATCH pronalazi relativni položaj svakog odgovora u rasponu H3: H6. Rezultat je niz pozicija.
Bilješka: U formuli niza, da biste vidjeli što funkcija vraća, odaberite željenu funkciju i pritisnite F9.
IF & N funkcija
IF i N funkcija korištene zajedno vraćaju sljedeći niz kao ulaz za funkciju INDEX.
| 1 | = ZBIR (INDEKS (I $ 3: I $ 6, {1,3,2})) |
Ovdje dvije funkcije vraćaju niz relativnih položaja odgovora u rasponu H3: H6. Svrha korištenja IF i N funkcija je izvođenje procesa koji se naziva dereferenciranje. Jednostavno rečeno, dvije funkcije prisiljavaju funkciju INDEX da proslijedi cijeli niz vrijednosti koda funkciji SUM.
To ćemo objasniti u sljedećem odjeljku.
INDEKS funkcija
Funkcija INDEX vraća vrijednost pozicioniranu na sjecištu navedenog retka i stupca u rasponu. Njegova sintaksa je:
Pogledajmo kako to funkcionira kao formula niza:
| 1 | = ZBIR ({10,5,8}) |
INDEX funkcija pronalazi vrijednosti koda u rasponu I3: I6 prema danim brojevima pozicija. Zatim vraća niz vrijednosti, tj. Odgovarajući kôd za svaki odgovor, funkciji SUM radi izvođenja operacija.
Provjerite broj redaka i stupaca u datoteci Odgovor i Kodirati stupac je isti.
SUM funkcija
Funkcija SUM zbraja vrijednosti koda koje vraća funkcija INDEX.
| 1 | = ZBIR ({10,5,8}) |
Sve to zajedno daje našu početnu formulu:
| 1 | {= SUM (INDEKS (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
ZBOR tekstualnih vrijednosti - bez IF & N funkcija
Ovaj odjeljak objašnjava kako Excel reagira ako ne koristimo IF i funkciju N u gore spomenutoj formuli.
Isti se primjer koristi s istim kodovima i odgovorima.
| 1 | {= SUM (INDEKS (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H 6,0)))} |
Kao što vidite, funkcija INDEX prosljeđuje samo kôd za prvi odgovor funkciji SUM. Ako pomno proučite funkciju INDEX pritiskom na F9 dobit ćete sljedeće:
Vrijednost! Vraćena je pogreška jer funkcija INDEX ne može čitati niz brojeva redaka kao niz. Stoga korištenje IF & N funkcije čini trik.
Bilješka: U Excelu 365 možete potpuno preskočiti korištenje funkcija IF i N.
Zbirni tekst- Google tablice
Ove formule rade isto u Google tablicama kao i u Excelu.
