Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako najti vsoto besedilnih vrednosti, kjer je vsaki tovrstni besedilni vrednosti v Excelu in Google Preglednicah dodeljena edinstvena koda.
Številke SUM, shranjene kot besedilo
Najprej bomo pogledali, kako sešteti številke, shranjene ali oblikovane kot besedilo.
V naslednjem primeru je stolpec Plača shranjen kot besedilo. Če poskusite sešteti vrednosti, bo Excel prikazal ničlo.
| 1 | = SUM (C3: C7) |
Namesto tega lahko za izvajanje operacije SUM nad številkami, shranjenimi kot besedilo, uporabite formulo matrike s funkcijami SUM in VALUE, kot je ta:
| 1 | = SUM (VREDNOST (C3: C7)) |
Funkcija VALUE pretvori besedilo, ki predstavlja številko, v številko. Funkcija SUM sešteje te številke.
V Excelu 365 in različici Excela, novejši od leta 2022, lahko preprosto vnesete formulo kot običajno. Ko pa uporabljate Excel 2022 in starejše, morate formulo matrike vnesti s pritiskom na CTRL + SHIFT + ENTER (namesto ENTER), ki Excelu pove, da je formula formula matrike. Za kodrno oklepaje, ki se pojavijo okoli formule, boste vedeli, da gre za matrično formulo (glejte zgornjo sliko). V novejših različicah Excela in Excela 365 lahko preprosto pritisnete ENTER namesto tega.
Poglejmo naslednjo razlago, da bolje razumemo formulo.
Funkcija VALUE
Funkcija VALUE, ki se uporablja kot matrična formula, pretvori celotno paleto številk, shranjenih kot besedilo, v niz števil in jih vrne kot vhod za funkcijo SUM.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Če si želite ogledati, kaj vrne funkcija VALUE, izberite želeno funkcijo in pritisnite F9.
SUM besedilnih vrednosti
Za seštevanje obsega besedilnih vrednosti, kjer je vsaki taki besedilni vrednosti dodeljena edinstvena koda, se lahko uporabi matrična formula.
V naslednji tabeli je zapisano, kaj ljudje izberejo za tri vprašanja. V tabeli na desni so navedeni štirje možni odgovori, od katerih je vsakemu dodeljena vrednost kode. Za vsako osebo moramo sešteti kode.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Sprehodili se bomo po spodnji formuli.
Funkcija MATCH
Funkcija MATCH išče določeno postavko v obsegu in vrne njen relativni položaj v tem območju. Njegova sintaksa je:
Kot formula matrike v tem primeru poglejmo, kaj funkcija MATCH vrne.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Za določeno osebo funkcija MATCH najde relativni položaj vsakega odgovora v obsegu H3: H6. Rezultat je vrsta položajev.
Opomba: Če želite v formuli matrike videti, kaj funkcija vrne, izberite želeno funkcijo in pritisnite F9.
IF & N funkcija
Skupaj uporabljeni funkciji IF in N vrneta naslednji niz kot vhod za funkcijo INDEX.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Tu dve funkciji vrneta niz relativnih položajev odgovorov v obsegu H3: H6. Namen uporabe funkcij IF in N je izvajanje postopka, imenovanega dereferenciranje. Preprosto povedano, dve funkciji prisilita funkcijo INDEX, da posreduje celotno paleto kodnih vrednosti funkciji SUM.
To razlagamo v naslednjem razdelku.
INDEX funkcija
Funkcija INDEX vrne vrednost, ki je nameščena na presečišču določene vrstice in stolpca v obsegu. Njegova sintaksa je:
Poglejmo, kako deluje kot matrična formula:
| 1 | = SUM ({10,5,8}) |
Funkcija INDEX najde vrednosti kode v obsegu I3: I6 glede na podane številke položajev. Nato vrne polje vrednosti, to je ustrezno kodo za vsak odgovor, funkciji SUM za izvajanje operacij.
Prepričajte se, da je število vrstic in stolpcev v obeh Odgovor in Koda stolpec je isti.
Funkcija SUM
Funkcija SUM bo seštela vrednosti kod, ki jih vrne funkcija INDEX.
| 1 | = SUM ({10,5,8}) |
Vse to skupaj daje našo začetno formulo:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))}} |
SUM besedilnih vrednosti - brez funkcij IF & N
Ta razdelek pojasnjuje, kako se Excel odziva, če v zgoraj omenjeni formuli ne uporabljamo funkcije IF in N.
Isti primer se uporablja z istimi kodami in odgovori.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Kot lahko vidite, funkcija INDEX posreduje samo kodo za prvi odgovor funkciji SUM. Če natančno pregledujete funkcijo INDEX s pritiskom na F9 dobili boste naslednje:
Vrednost! Napaka se vrne, ker funkcija INDEX ne more prebrati matrike številk vrstic kot matrike. Zato uporaba funkcije IF & N pomaga.
Opomba: V Excelu 365 lahko v celoti preskočite uporabo funkcij IF in N.
Skupno besedilo- Google Preglednice
Te formule delujejo enako v Google Preglednicah kot v Excelu.
