Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako nájsť súčet textových hodnôt tam, kde je každej takejto textovej hodnote priradený jedinečný kód v tabuľkách Excel a Google.
SUM čísla sú uložené ako text
Najprv sa pozrieme na to, ako sčítať čísla uložené alebo naformátované ako text.
V nasledujúcom príklade je stĺpec Plat uložený ako text. Ak sa pokúsite sčítať hodnoty, Excel zobrazí nulu.
| 1 | = SUM (C3: C7) |
Namiesto toho na vykonanie operácie SUM na číslach uložených ako text môžete použiť vzorec poľa s funkciami SUMA a HODNOTA, ako je táto:
| 1 | = SUM (HODNOTA (C3: C7)) |
Funkcia VALUE prevádza text predstavujúci číslo na číslo. Funkcia SUMA tieto čísla sčíta.
V Exceli 365 a verzii Excelu novšej ako 2022 môžete vzorec jednoducho zadať ako obvykle. Pri použití Excelu 2022 a starších verzií musíte však vzorec poľa zadať stlačením CTRL + SHIFT + ENTER (namiesto VSTÚPIŤ), pričom programu Excel poviete, že vzorec je vzorec poľa. Budete vedieť, že je to vzorec poľa podľa zložených zátvoriek, ktoré sa objavujú okolo vzorca (pozri horný obrázok). V novších verziách Excelu a Excelu 365 môžete jednoducho stlačiť VSTÚPIŤ namiesto.
Pozrime sa na nasledujúce vysvetlenie, aby sme lepšie pochopili vzorec.
Funkcia VALUE
Funkcia VALUE, použitá ako vzorec poľa, prevádza celý rozsah čísel uložených ako text na pole čísel a vracia ho ako vstup pre funkciu SUM.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Ak chcete zistiť, čo funkcia VALUE vracia, vyberte požadovanú funkciu a stlačte F9.
SÚHRN textových hodnôt
Na SÚČET rozsahu textových hodnôt, kde je každej takej textovej hodnote priradený jedinečný kód, je možné použiť vzorec poľa.
Nasledujúca tabuľka zaznamenáva, čo ľudia vyberú pre tri dané otázky. V tabuľke vpravo sú uvedené štyri možné odpovede, ku ktorým bola priradená hodnota kódu. Musíme sčítať kódy pre každú osobu.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Prejdeme sa nižšie uvedeným vzorcom.
Funkcia MATCH
Funkcia MATCH hľadá zadanú položku v rozsahu a vracia jej relatívnu polohu v tomto rozsahu. Jeho syntax je:
Keďže ide o maticový vzorec v tomto prípade, pozrime sa, čo funkcia MATCH vracia.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
U danej osoby funkcia MATCH nájde relatívnu polohu každej odpovede v rozsahu H3: H6. Výsledkom je rad pozícií.
Poznámka: Ak chcete vo vzorci poľa zobraziť, čo funkcia vracia, vyberte požadovanú funkciu a stlačte F9.
Funkcia IF & N.
Použitá funkcia IF a N spoločne vracia nasledujúce pole ako vstup pre funkciu INDEX.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Tu dve funkcie vrátia pole relatívnej polohy odpovedí v rozsahu H3: H6. Účelom použitia funkcií IF a N je vykonať proces nazývaný dereferencovanie. Jednoducho povedané, tieto dve funkcie nútia funkciu INDEX, aby odovzdala celé pole hodnôt kódu funkcii SUM.
Vysvetlíme to v nasledujúcej časti.
Funkcia INDEX
Funkcia INDEX vráti hodnotu umiestnenú na priesečníku zadaného riadka a stĺpca v rozsahu. Jeho syntax je:
Pozrime sa, ako to funguje ako vzorec poľa:
| 1 | = SUM ({10,5,8}) |
Funkcia INDEX nájde hodnoty kódu v rozsahu I3: I6 podľa daných čísel pozícií. Potom vráti funkcii SUMA pole hodnôt, t.j. príslušný kód pre každú odpoveď, na vykonanie operácií.
Zaistite počet riadkov a stĺpcov v oboch Odpoveď a Kód stĺpec je rovnaký.
Funkcia SUM
Funkcia SUM bude sčítať hodnoty kódu vrátené funkciou INDEX.
| 1 | = SUM ({10,5,8}) |
Toto všetko dohromady dáva náš pôvodný vzorec:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
SÚHRN textových hodnôt - bez funkcií IF & N.
Táto časť vysvetľuje, ako Excel reaguje, ak vo vyššie uvedenom vzorci nepoužívame funkciu IF a N.
Ten istý príklad sa používa s rovnakými kódmi a odpoveďami.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Ako vidíte, funkcia INDEX odovzdáva funkcii SUM iba kód pre prvú odpoveď. Ak si prezriete funkciu INDEX stlačením F9 získate nasledujúce:
Hodnota! Je vrátená chyba, pretože funkcia INDEX nemôže čítať pole čísel riadkov ako pole. Preto použitie funkcie IF & N robí trik.
Poznámka: V programe Excel 365 môžete úplne preskočiť pomocou funkcií IF a N.
Súhrnný text- Tabuľky Google
Tieto vzorce fungujú v Tabuľkách Google rovnako ako v programe Excel.
