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

Jste zde: Úvodní stránka » excel » funkce » funkce-textove-prakticke-priklady-excel

Funkce textové - praktické příklady - Excel

Videokurzy Excel

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

Jak prakticky aplikovat textové funkce v Excelu, aneb jak vyřešit problém.

20.1.2016: Doplněno o nový příklad - Obsahuje buňka slovo / text

Logo tipy triky Microsoft Excel

Co se v článku dozvíte

Seznam řešených příkladů:


Úvodem do textových funkcí

Odpovědi na dotazy týkající se praktických dotazů, které lze vyřešit za pomocí textových funkcí, které jsou součásti Microsoft Excel.

Teoretický popis funkcí, jejich syntaxe a praktický příklad je k dispozici v článku: Textové funkce - abecedně řazený popis se syntaxi a příklady. V totmo článku již tyto vědomosti aplikujeme do praktických příkladů, které jsou v praxi potřeba.

Na řešení většinou nejde použít jen jedna funkce, ale musí se jich zkombinovat několik. čímž se teprve dostanete k požadovanému výsledku.

Spočítat výskyt zadaného znaku

Jak zjistit počet nějakého znaku v buňce?

Například v buňce je zadáno slovo: "nejnekulaťoulinkatější" a potřebujete zjistit kolikrát se v tomto slově objevuje písmeno "e"!

Řešení je jednoduché.

  • Zjistíte délku řetězce - DÉLKA
  • Ze zkoumaného řetězce odstraníte hledané znaky - tj. nahradíte je prázdným znakem funkce DOSADIT
  • Zjistíte délku zkráceného řetězce opět funkce DÉLKA
  • Tyto dvě délky odečtete

Ukázkové řešení za předpokladu, že zkoumaný text je v buňce A1 a funkce (vzorec) bude v buňce B1

=DÉLKA(A1)-DÉLKA(DOSADIT(A1;"e";""))

Počet znaku v oblasti

Jak zjistit počet vybraného znaku v zadané oblasti?

Zjištění počtu výskytu daného znaků (například e) v zadané oblasti (např: A1:A5.). V přechdozího příkladů je popsán teoretický postup řešení (takže se nebudu opakovat). Jen sledovanou oblast místo buňky rozšiříme na porhledávanou oblast.

=SUMA(DÉLKA(A1:A5)-DÉLKA(DOSADIT(A1:A5;"e";"")))

Poznámka: Nutno zadávat jako maticový vzorec Shift + Ctrl + Enter, ale to jen na doplnění.

Spočítat výskyt výrazu v oblasti

Jak spočítat kolikrát se výraz vyskytuje v dané oblasti. V buňkách A1 až A5 máte zadána různá slova (například klíčová slova z vyhledávače) a potřebujete zjistit, kolikrát se dané slovo v této oblastí nalézá. Podobně jako v předchozím případě, jen budete počítat s celou oblastí.

  • Zjistíte délku řetězce v dané oblastí
  • Ze zkoumané oblasti odstraníte hledané slovo - nahradíte je prázdným znakem
  • Zjistíte délku bez hledaného slova
  • Tyto dvě délky odečtete - jako v předchozím příkladu
  • Vydělíte délkou daného slova

Tabulka do Vašeho příkladu:

  • A1: letenky
  • A2: levné letenky
  • A3: letenky zdarma
  • A4: letenky chorvatsko
  • A5: letenky balonem

To buňky zadáte vzorec, která spočte počet výskytu slova letenky v oblasti A1:A5

=SUMA(DÉLKA(A1:A5)-DÉLKA(DOSADIT(A1:A5;"letenky";"")))/DÉLKA("letenky")

Poznámka: Samozdřejmě jde vzorec upravit tak aby se daná slova volily dynamicky, včetně dynamické volby oblastí, atd.

Poznámka: Nutno zadávat jako maticový vzorec Shift + Ctrl + Enter, ale to jen na doplnění.

Odstranit dvě a více mezer

Mám text, kde někdo mezi slovy nechal dvě a více mezer, jak mohu tyto mezery odstranit? Doporučuji použít funkci PROČISTIT. Třeba je dotyčný placen za úhozy (a mezerník je také úhoz).

PROČISTIT(" Někdo kdo má velice rád mezerník a fláka mezery všude. ")

Výsledkem bude normální čitelný text: Někdo kdo má velice rád mezerník a fláka mezery všude.

Vložit znak na určené místo

Jak do rodnného čísla vložit lomítko?

Máme rodné číslo např 7711225544 a potřebujeme na 7 místo vložit lomítko. Nejideálnější se jeví použít funkci NAHRADIT. Kdy počtu nahrazovaných znaků přiřadíte nulu. Předpoklad v buňce A1 je rodné číslo.

Rodné číslo bude vypadat 771122/5544.

=NAHRADIT(A1;7;0;"/")

Finta pokud budete chtít dále doplnit mezery za rok a měsíc.

=NAHRADIT(NAHRADIT(NAHRADIT(C11;5;0;"-");3;0;"-");9;0;"/")

Rodné číslo bude vypadat 77-11-22/5544.

Vložit +420 do telefoního čísla

potřebujete vložit +420 do telefoního čísla

=CONCATENATE("+420";A36)

ošetříme pokud by buňka pro telefoní číslo byla prázdná tak nebude vypsáno +420 ale buňka bude prázdná.

=KDYŽ(A36="";"";CONCATENATE("+420";A36))

A doplníme o kontrolu pokud buňka už obsahuje číslo s předvolbou ať není výsledek+420+420777888999 ...

=KDYŽ(A44="";"";KDYŽ(DÉLKA(A44)=9;CONCATENATE("+420";A44);A44))

Dále jde kontrolovat zda není zadaváno 777 222 333 atd...

Jak sloučit text do jedné buňky a rozdělit do více řádku

Potřebujete sloučit text v buňkách a přidat do buňky "Alt+Enter". Tak aby byl text v jedné buňce ve více řádcích

aaa
bbb
ccc
aaa
bbb
ccc

Alt+Enter provede funkce ZNAK(10) v anglickém excelu CHAR(10). Sloučení provedete do buňky A4, v buňkách A1, A2, A3 budete mít slučovaný text.

=A1 & ZNAK(10) & A2 & ZNAK(10) & A3

Nebo

=CONCATENATE(A1;ZNAK(10);A2;ZNAK(10);A3)

Poznámka: Ve formátu buňky musí být povoleno zalomit text.

Rozdělení jména

Potřebujeme rozdělit jméno, které se nachází v jedné buňce. Jeméno rozdělíme na Jméno a příjmení.

pro jméno

=ZLEVA(A48;(NAJÍT(" ";A48;1))-1)

pro příjmení

=ČÁST(A48;(NAJÍT(" ";A48;1))+1;DÉLKA(A48))

nebo:

=ZPRAVA(A52;(DÉLKA(A52)-NAJÍT(" ";A52;1)))

Poznámka: Předpokládám, že v buňce je jen jméno a přijmení. Neobsahuje tituly, další jména atd.

Z textu vytvořit hodnotu

Potřebujete-li po úpravách pomocí textových funkcí dále s tímto výsledkem počítat můžete využít funkce HODNOTA převést na hodnotu s kterou lze dále počítat:

=HODNOTA(ZLEVA(A60;3))

Nebo fintu a pouze násobit jedničkou :)

=ZLEVA(A58;3)*1

Doplnit text nulami

V buňce mám údaj a potřebuji jej doplnit nulami, tak aby počet číslic byl 13.

Pro 1 to bude 1000000000000

Pro 12345 to bude 1234500000000

Možné řešení je:

=CONCATENATE(A1;OPAKOVAT("0"; 13-DÉLKA(A1)))

Odřádkování místo čárky

Odřádkování místo čárky

Z databáze je v jedné buňce adresa, která jsou položky odděleny čárkami, pro tisk štítku je potřeba text odřádkovat. Místo čárky vložit Enter. Jak to provést? Využitím funkce DOSADIT.


=DOSADIT(B6;", ";ZNAK(10))

Uvozovky ve funkci CONCATENATE

Je potřeba aby funkce CONTACENATE zobrazila uvozovky.

Doporučuji využít funkce ZNAK(34). Tato funkce zobrazí uvozovky a funkce CONCATENATE si s těmato uvozovkama bez problému poradí.

" =ZNAK(34) "test" =CONCATENATE(ZNAK(34);"test";ZNAK(34)) =CONCATENATE("test") =CONCATENATE("=CONCATENATE(";ZNAK(34);"test";ZNAK(34);")") Uvozovky ve funkci CONCATENATE

Obsahuje buňka slovo

Obsahuje buňka hledané slovo? Využití HLEDAT a KDYŽ.

=KDYŽ(HLEDAT("koruna";B5)>0;"obsahuje";"neobsahuje") Využití HLEDAT a KDYŽ

Další využití textových funkcí

Textové funkce lze využít v mnoha praktických aplikacích. Obrovské využití mají při statistických analýzách textů. Při analýzách pro SEO reporty. Chceteli se dozvědět více sepsal jsem tyto články, které využívají textové funkce:

Ke stažení ukázkové příklady

Soubor Ukázky praktických příkladů na textové funkce soubor ve formátu *.xlsx - Microsoft Excel 2010, ke stažení zdarma.

Závěrem

Pro případné dotazy a odpovědi jsou k dispozici komentáře. Zajímavý dotaz dolním do článku.

Článek byl aktualizován: 20.01.2016 18:15

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, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 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 také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek 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


Martina

Přidáno: 06.12.13 11:22

Dobry den,dakujem pekne za pomoc pri textovych funkciach v exceli.Mata

Pavel Lasák

Přidáno: 07.12.13 01:47

To Martina: Rádo se stalo.

Dana z Prahy

Přidáno: 09.12.14 11:36

Dobrý den, prosím potřebuji poradit. Musím z 5 listů součty z různých tabulek sečíst na první list vždy po řádcích. Vzorce jsem měla hotové pro 4 listy,ale potřebovala jsem přidat další list a i když postupuji stejným způsobem, jako můj předchůdce, součet i s 5.listem mi nechce zobrazit správně. Nejprve se zobrazovala chyba a teď mi nesmyslně přičítá 210 ks. Jsem zmatená.

Jojo

Přidáno: 11.12.14 07:49

To Dana z Prahy: -- =List1!A1+List2!A1+List3!A1+List4!A1+List4!A1+List5!A1 -- ALEBO ak sú listy za sebou a sčítava sa z každého listu napr. "A1", tak: =SUMA(List1:List5!A1)

Jojo

Přidáno: 12.12.14 07:21

To Dana z Prahy: -- Správne má byť: =List1!A1+List2!A1+List3!A1+List4!A1+List5!A1

Martin

Přidáno: 07.01.15 09:07

Dobrý den, potřeboval bych od Vás poradit, potřebuji si v excelu vytvořit xml, dělám to celkem běžně, teď jsem ale narazil na problém. Toto je xml výstup z erp systemu ano potřebuji pomocí funkce =CONCATENATE napsat vzorec tak, aby mi ze sloupce A měnil ID (ID="A2") a pote i ANO nebo ne UserData (to funguje) problem je s temi uvozovkami a rovná se v zapisu ObjectName ="Firma", vzorec to bere jako opetator, jak mu reknu, ze to je pouze znak, ktery nemá nic provádet. Moc dekuji za pomoc.

Martin

Přidáno: 07.01.15 09:18

Nakopíroval jsem xml, ale po uložení zmizelo, tak nevím jak uvidíte zdrojový kod :-)

Pavel Lasák

Přidáno: 11.01.15 08:36

To Martin: U funkce CONTACENATE se musí použít místo uvozovek funkce ZNAK(34), který představuje uvozovky. Pro AHOJ v uvozovkách =CONCATENATE(ZNAK(34);"AHOJ";ZNAK(34))

Martin

Přidáno: 12.01.15 11:09

To Pavel Lasák: Super, moc děkuju, to by mě nenapadlo. Hezký den. ;-)

Roman

Přidáno: 28.04.15 17:50

Jaký je rozdíl mezi "concatenate" a "&" ? Výstup je teoreticky stejný jestli použiji =CONCATENATE(ZNAK(34);"AHOJ";ZNAK(34)) Nebo kratší zápis: =ZNAK(34)&"Ahoj"&ZNAK(34) PS: zatím jsem s "&" pochodil vždy, tak by mě zajímalo, kdy musím použít concatenate, které jsem používal předtím...

stana

Přidáno: 22.09.15 14:06

Dobrý den Jako každý tady bych byl rád za radu. Při tvorbě rozpisu služeb bych potřeboval zkontrolovat zda sedí počet pracovníkú jak na "D" denní tak noční "N" "VR" apodobně tudíž potřebuji zjistit zda sedí počet a druh textu v buňkách.Kdysi jsem to uměl bohužel čas je svinstvo a zapoměl sem to:( Předem děkuji za jakoukoliv odpověď.

Jenik

Přidáno: 09.12.15 08:35

Dobrý den, mám na Listu "Roky" v buňce B2 datum 1.12.2015 a potřeboval bych do buňky C2 téhož lisu stáhnout hodnotu z listu L-2015 z buňky A2. Zadám do buňky C2 vzorec ="L-"&HODNOTA.NA.TEXT(ROK(B2);"0000")&"!A2". Zobrazí se výsledek odkaz na buňku L-2015!A2, ale nenapíše se to číslo z té buňky A2 z listu L-2015. Nevíte proč? Děkuji.

Alcina

Přidáno: 25.05.16 10:18

Dobré dopoledne, prosím Vás o radu, nevím jak zapisovat dlouhý text, když mám sloučených mnoho řádků i sloupců. Text se mi tam píše i když dám zalamovat, tak je to ok, ale když chci jako pokračovat ve psaní jako další odstavec, tak nevím jak? Děkuji

Matej

Přidáno: 30.05.16 12:13

Dobrý deň pán Lasak, Mal by som dotaz ohaldne rozdelenia textu z jednej bunky do 3 buniek. Potrebujem, rozmer dosky napr.100x100x10 bol rozdeleny pomocou funkcie na 100;100;10, ale potom ak by bol rozmer 1000x1000x100, zasa na 1000;1000;100. Prosim ak poradite vzorec kde bude hladat text po prve x-ko, po druhe x-ko a vyhlada text po 3-tom x-ku resp. zprava najde prve x-ko. Dakujem.

Jojo

Přidáno: 01.06.16 08:13

To Matej: -- rozdelenie textu do 3 buniek(oddelovač="x")(=LEFT(A1;SEARCH("x";A1)-1) =MID(A1;SEARCH("x";A1)+1;(SEARCH("x";A1;SEARCH("x";A1)+1)-SEARCH("x";A1)-1)) =RIGHT(A1;LEN(A1)-SEARCH("x";A1;SEARCH("x";A1)+1))

Jojo

Přidáno: 01.06.16 09:15

To Alcina: -- Ak otázke rozumiem dobre, tak pre ďalší odstavec v bunke treba stlačiť: Alt+Enter

Petr

Přidáno: 10.06.16 16:27

Rád bych zjistil, jak sloučit vzorcem text z několika buněk a oddělit je nějakým znakem (například středník nebo pomlčka). priklad: bunka A1 az A200 obsahuje jmena, potrebuji dostat jako vysledek text vsech jmen oddeleny stredniky. dekuji

Jojo

Přidáno: 13.06.16 10:51

To Petr: -- Zreťazenie obsahu buniek v stĺpci:For rw = 1 To 200 out = out & Cells(rw, "A") & ";" ' zreťazenie Next rw Range("B1") = out ' výstup

Petr

Přidáno: 13.06.16 15:33

To Jojo: díky, vysypalo to to, co jsem potřeboval. zkoušel jsem maticové vzorce, ale neúspěšně...nezvládám je na takové úrovni, nebo to zkrátka přes ně nejde :(

Eva

Přidáno: 02.09.16 13:02

Dobrý den, potřebuji, aby se mi z jedné buňky načetl zápis z jiné buňky bez lomítka a písmen. Příklad buňka 1: 2016/12/PB buňka 2: 201612 Děkuji předem za pomoc.

Jojo

Přidáno: 05.09.16 08:29

To Eva: -- Ak bude zachovaný formát uvedený v príklade (4číslice + 1znak na vymazanie + 2číslice + zvyšok na vymazanie), tak je možné použiť: =LEFT(REPLACE(A1;5;1;"");6)

Jojo

Přidáno: 05.09.16 08:42

To Eva: -- ...alebo použiť: =LEFT(A1;4)&MID(A1;6;2)

Eva

Přidáno: 05.09.16 09:26

Pro Jojo: Díky za radu, ale nějak to nefunguje, potřebuji při vystavení faktury (vytvořena v excelu) napsat číslo faktury a aby se mi do buňky pod číslem načetlo to číslo bez lomítka jakožto variabilní symbol. FAKTURA - DAŇOVÝ DOKLAD Dodavatel: Faktura číslo: 16/10 IČO: Variabilní symbol: Konstantní symbol: O308 DIČ: Forma úhrady: převodem Nevím jestli se mi vzor načte správně. Jsem úplný amatér na vzorečky.

Eva

Přidáno: 05.09.16 09:42

Pro Jojo: omlouvám se, načetlo se mi to jako text.

Jojo

Přidáno: 05.09.16 14:46

To Eva: -- Tak je to OK, alebo treba ešte niečo doriešiť?

Mirec

Přidáno: 04.11.16 22:22

Dobry den,ja by som potreboval poradit ako automaticky odstranim "/" z rodnych cisiel, aby som miesto 123456/7890 mal len 1234567890. Dakujem

Jojo

Přidáno: 07.11.16 07:54

To Mirec: -- =SUBSTITUTE(A1;"/";"";1)






Excel


Sdílejte

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

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

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 - 2017 | 69796

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.