Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak najít součet textových hodnot tam, kde je každé takové textové hodnotě v Excelu a Tabulkách Google přiřazen jedinečný kód.
SUMOVÁ čísla uložená jako text
Nejprve se podíváme na to, jak sečíst čísla uložená nebo formátovaná jako text.
V následujícím příkladu je sloupec Plat uložen jako text. Pokud se pokusíte sečíst hodnoty, Excel zobrazí nulu.
| 1 | = SUM (C3: C7) |
Místo toho k provedení operace SUM na číslech uložených jako text můžete použít maticový vzorec s funkcemi SUMA a HODNOTA takto:
| 1 | = SUM (HODNOTA (C3: C7)) |
Funkce VALUE převádí text představující číslo na číslo. Funkce SUMA tato čísla sečte.
V Excelu 365 a verzi Excelu novější než 2022 můžete vzorec jednoduše zadat jako obvykle. Při použití Excelu 2022 a starších verzí však musíte zadat vzorec pole stisknutím CTRL + SHIFT + ENTER (namísto VSTUP), což říká Excelu, že vzorec je maticový vzorec. Poznáte to podle maticového vzorce podle složených závorek, které se objevují kolem vzorce (viz horní obrázek). V novějších verzích Excelu a Excelu 365 můžete jednoduše stisknout VSTUP namísto.
Podívejme se na následující vysvětlení, abychom lépe porozuměli vzorci.
Funkce VALUE
Funkce VALUE, používaná jako maticový vzorec, převádí celý rozsah čísel uložených jako text na pole čísel a vrací jej jako vstup pro funkci SUMA.
| 1 | = SUMA ({10000; 6500; 7500; 15000; 8000}) |
Chcete -li zjistit, co funkce VALUE vrací, vyberte požadovanou funkci a stiskněte F9.
SUMA textových hodnot
K SUMA rozsahu textových hodnot, kde je každé takové textové hodnotě přiřazen jedinečný kód, lze použít vzorec pole.
Následující tabulka zaznamenává, co lidé vyberou pro tři dané otázky. V tabulce vpravo jsou uvedeny čtyři možné odpovědi, z nichž každé byla přiřazena hodnota kódu. Musíme sečíst kódy pro každou osobu.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Projdeme níže uvedený vzorec.
Funkce MATCH
Funkce MATCH hledá zadanou položku v rozsahu a vrací její relativní polohu v tomto rozsahu. Jeho syntaxe je:
Jako vzorec pole v tomto příkladu se podívejme, co vrací funkce MATCH.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Pro danou osobu najde funkce MATCH relativní polohu každé odpovědi v rozsahu H3: H6. Výsledkem je řada pozic.
Poznámka: Chcete -li ve vzorci pole zobrazit, co funkce vrací, vyberte požadovanou funkci a stiskněte F9.
Funkce IF & N.
Použitá funkce IF a N společně vrací následující pole jako vstup pro funkci INDEX.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Zde dvě funkce vrátí pole relativní polohy odpovědí v rozsahu H3: H6. Účelem použití IF a N funkcí je provedení procesu zvaného dereferencing. Jednoduše řečeno, tyto dvě funkce přinutí funkci INDEX předat celé pole hodnot kódu funkci SUM.
Vysvětlujeme to v další části.
Funkce INDEX
Funkce INDEX vrací hodnotu umístěnou na průsečíku zadaného řádku a sloupce v rozsahu. Jeho syntaxe je:
Podívejme se, jak to funguje jako maticový vzorec:
| 1 | = SUM ({10,5,8}) |
Funkce INDEX najde hodnoty kódu v rozsahu I3: I6 podle daných čísel pozic. Poté vrátí funkci SUM k provedení operací pole hodnot, tj. Příslušný kód pro každou odpověď.
Zajistěte počet řádků a sloupců v obou Odpovědět a Kód sloupec je stejný.
Funkce SUM
Funkce SUMA sečte hodnoty kódu vrácené funkcí INDEX.
| 1 | = SUM ({10,5,8}) |
To vše dohromady dává náš počáteční vzorec:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
SOUČET hodnot textu - bez funkcí IF & N.
Tato část vysvětluje, jak aplikace Excel reaguje, pokud ve výše uvedeném vzorci nepoužíváme funkci IF a N.
Stejný příklad se používá se stejnými kódy a odpověďmi.
| 1 | {= SUMA (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Jak vidíte, funkce INDEX předává funkci SUM pouze kód pro první odpověď. Pokud podrobně prozkoumáte funkci INDEX stisknutím F9 získáte následující:
Hodnota! Je vrácena chyba, protože funkce INDEX nemůže číst pole čísel řádků jako pole. Proto použití funkce IF & N dělá trik.
Poznámka: V aplikaci Excel 365 můžete přeskočit pomocí funkcí IF a N.
Souhrnný text- Tabulky Google
Tyto vzorce fungují v Tabulkách Google stejně jako v Excelu.
