Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » prakticke-priklady » soucet-pocet-cislo-pismeno-jedna-bunka
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak počítat hodnoty dle podmínek, když v buňce je číslo a písmeno, dle kterého se tvoří podmínka. Možnost rozdělit do dvou sloupců a pak využít SUMIFS je super, ale pracná.

Jak využít vnořené maticové funkce.

Ú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

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:

Excel - zdrojová data

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.

Excel - 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)) Excel - výsledek

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))
Microsoft Excel VBA - stahuj logo

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |