Součet (počet) na základě čísla s písmenem v buňce

Úvodem

V buňkách mám hodnoty D8, D5, N8, N8, P6... a potřebuji spočítat součet čísel v buňkách, které obsahují písmeno D (tj. 8 +5) - případně počet (tj. 2), pak s N bude součet (tj 8 + 8) a počet dvě., atd. Jak na to využitím funkcí se podíváme v tomto článku.

Pro přehlednost je rozdělen na kapitoly

• Zdrojová data
• Součet využitím SUM - včetně principu
• Součet využitím SOUČIN.SKALÁRNÍ
• Počet
• Praktické využití
• Ke stažení

Využití například v pracovních výkazech, úkolovnících, atd.

Zdrojová data

Mějme tabulku, kde ve sloupci jsou uvedeny typy akce, například D ... dovolené a počet hodin, N ... nemoc a počet hodin. Bouhužel údaj o čase a typu nepřítomností je uveden v jedné buňce, viz ukázková tabulka:

Datum	Co	Poznámka
01.01.2021	D8	Dovolená počítá 8 hodin
02.01.2021	N5	Nemoc 5 hodin.
03.01.2021	d5	Dovolená jiné písmeno
04.01.2021	D6	Dovolená počítá 6 hodin
05.01.2021	8
06.01.2021	8
07.01.2021	L3	Lékař 3 hodiny
08.01.2021	8
09.01.2021	N5	Nemoc 5 hodin.
10.01.2021	8

Ukázka tabulky v Excel

Pro další práci se budu odkazovat na tabulku kterou vidíte na obrázku:

Součet využitím SUM

Jak tento úkol vyřešit použijeme funkci. Pozor funkce se musí zadávat jako maticový vzorec. Potvrdit stiskem (Ctrl + Shift + Enter). Výhodu mají majitelé nejnovějších verzí Excel, kde se již jako maticový vzorec zadávat nemusí.

{=SUMA(KDYŽ(JE.ČISLO(NAJÍT("D";C6:C11));DOSADIT(C6:C11;"D";"")+0))}

Jak funkce funguje?

Vnitřní funkce zjistí, zda daná buňka obsahuje číslo NAJÍT (FIND) a DOSADIT (SUBSTITUTE) odstraní písmeno D (pozor funkce rozlišuje velká a malá písmena). Tyto části jsou vnitřními argumenty funkce KDYŽ, kterou vysvětlím vzápětí.

NAJÍT("D";C6:C11) >>> {1;#HODNOTA!;#HODNOTA!;1;#HODNOTA!;#HODNOTA!} DOSADIT(C6:C11;"D";"") >>> {"8";"N5";"d5";"6";"8";"8"}

Výsledem bude funkce uvnitř obsahovat hodnoty (případně chybové hodnoty):

=SUMA(KDYŽ(JE.ČISLO({1;#HODNOTA!;#HODNOTA!;1;#HODNOTA!;#HODNOTA!});{"8";"N5";"d5";"6";"8";"8"}+0))

Funkce je číslo převede na logickou hodnotu informaci, zda v buňce je či není číslo a druhá část z textu vytvoří číslo. Jak víme tak výsledkem textových funkcí je text a ten můžeme převést na číslo přičtením nuly, nebo funkci HODNOTA (VALUE). Výsledkem bude:

=SUMA(KDYŽ({PRAVDA;NEPRAVDA;NEPRAVDA;PRAVDA;NEPRAVDA;NEPRAVDA};{8;#HODNOTA!;#HODNOTA!;6;8;8}))

Tyto dvě části jsou argumenty funkce KDYŽ (IF), první část je ona PRAVDA nepravda, druhá část jsou ony čísla, kdy výsledkem funkce KDYŽ bude matice čísel, případně logické hodnoty NEPRAVDA (FALSE):

=SUMA({8;NEPRAVDA;NEPRAVDA;6;NEPRAVDA;NEPRAVDA})

Funkce SUMA sečte čísla, nepravdu bere jako nulu, a máme potřebný výsledek.

Součet využitím SOUČIN.SKALÁRNÍ

Případně elegantněji využít funkci SOUČIN.SKALÁRNÍ (SUMPRODUCT):

=SOUČIN.SKALÁRNÍ((JE.ČISLO(NAJÍT("D";C6:C15)))* IFERROR(HODNOTA(DOSADIT(C6:C15;"D";""));0))

Počet - například dovolených

Zvládnete určitě sami, ale pro jistotu:

=SUMA(KDYŽ(JE.ČISLO(NAJÍT("D";C6:C11));1))

Praktické využití

Samozdřejmě, že písmena nemusíte psát do vzorce, ale můžete se odkázat na buňku. Pak jen rozkopírujete. Jen stále mějme na paměti, že ve starších verzích Excel se musí zadávat jako maticová funkce (Ctrl + Shift + Enter):

=SUMA(KDYŽ(JE.ČISLO(NAJÍT(E18;$C$6:$C$15));DOSADIT($C$6:$C$15;E18;"")+0))

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.

Závěrem

Narazili jste na nějaké problémy, máte tip, můžete se zmínit v komentářích.

Článek byl aktualizován: 16.01.2021 12:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.