Domov › Internet › Příklady použití textových funkcí v excelu. Textové funkce Excelu. Oddělovač tisíců skupin

Příklady použití textových funkcí v excelu. Textové funkce Excelu. Oddělovač tisíců skupin

Excel nabízí svým uživatelům hned 3 funkce pro práci s velkými a malými písmeny v textu (velká a malá písmena). Tyto textové funkce dělají písmena velká a malá nebo mění pouze první písmeno ve slově na velké.

Vzorce s textovými funkcemi aplikace Excel

Nejprve se podívejme na 3 textové funkce aplikace Excel jako příklad:

KAPITÁLNÍ – tato textová funkce změní všechna písmena ve slově na velká písmena.
LOWER – Tato funkce převede všechny textové znaky na malá písmena.
PROPNACH - funkce změní pouze první písmeno v každém slově na velké písmeno.

Jak můžete vidět na příkladu na obrázku, tyto funkce nevyžadují ve svých argumentech nic jiného než původní textová data, která by měla být převedena v souladu s požadavky uživatele.

I přes tak široký výběr funkcí Excel stále potřebuje funkci, která dokáže nahradit první písmeno velkým pouze u prvního slova ve větě, a ne v každém slově. Chcete-li však tento problém vyřešit, můžete si vytvořit svůj vlastní vzorec pomocí stejných a dalších textových funkcí aplikace Excel:

Chcete-li vyřešit tento oblíbený problém, musíte ve vzorci použít další textové funkce aplikace Excel: LEFT, RIGHT a LENGTH.

Princip fungování vzorce pro nahrazení prvního písmene ve větě

Pokud se pozorně podíváte na syntaxi výše uvedeného vzorce, snadno zjistíte, že se skládá ze dvou částí, které jsou navzájem spojeny operátorem &.

Levá strana vzorce používá další funkci LEFT:

Účelem této části vzorce je změnit první písmeno na velké písmeno v původním textovém řetězci buňky A1. Díky funkci LEFT můžete získat určitý počet znaků počínaje levou stranou textu. Funkce vyžaduje vyplnění 2 argumentů:

Text – odkaz na buňku se zdrojovým textem.
Number_characters – počet znaků vrácených z levé strany (od začátku) zdrojového textu.

V tomto příkladu potřebujete pouze získat první 1 znak z původního textového řetězce v buňce A1. Následně se výsledný znak převede na velké velké písmeno.

Pravá strana vzorce za operátorem & je v principu velmi podobná levé straně, jen řeší jiný problém. Jeho úkolem je převést všechny textové znaky na malá písmena. Ale to musí být provedeno tak, aby se nezměnilo první velké písmeno, za které je zodpovědná levá strana vzorce. Místo funkce LEFT na pravé straně vzorce se používá funkce RIGHT:

Textová funkce VPRAVO funguje inverzně s funkcí VLEVO. Vyžaduje také oprášení dvou argumentů: zdrojového textu a počtu znaků. Ale vrátí určitý počet písmen získaných z pravé strany zdrojového textu. V tomto případě však nemůžeme zadat pevnou hodnotu jako druhý argument. Ostatně počet znaků ve zdrojovém textu předem neznáme. Kromě toho se může lišit délka různých řetězců zdrojového textu. Musíme tedy nejprve vypočítat délku textového řádku a od výsledné číselné hodnoty odečíst -1, abychom nezměnili první velké písmeno v řádku. Koneckonců, první písmeno je zpracováno levou stranou vzorce a již bylo transformováno tak, aby vyhovovalo požadavkům uživatele. Proto by neměl být ovlivněn žádnou funkcí z pravé strany vzorce.

Pro automatický výpočet délky zdrojového textu použijte textovou funkci Excelu - DLST (zkratka pro délku řádku). Tato funkce vyžaduje vyplnění pouze jednoho argumentu – odkazu na zdrojový text. V důsledku výpočtu vrací číselnou hodnotu, takže za funkcí =DÉLKA(A1) odečteme -1. Což nám dává možnost nedotknout se prvního velkého písmene na pravé straně vzorce. Výsledkem je, že funkce RIGHT vrátí textový řetězec bez prvního znaku do funkce LOWER, která převede všechny znaky textu na malá malá písmena.

V důsledku spojení obou částí vzorce operátorem & dostaneme krásnou textovou větu, která podle pravidel začíná prvním velkým písmenem. A všechna ostatní písmena jsou malá až do konce věty. Bez ohledu na délku textu pomocí stejného vzorce dostaneme správný výsledek.

Celé jméno, čísla bankovních karet, adresy klientů či zaměstnanců, komentáře a mnoho dalšího – to vše jsou řetězce, se kterými se mnozí při práci s aplikací Excel setkávají. Proto je užitečné umět tento typ informací zpracovávat. Tento článek se podívá na textové funkce v Excelu, ale ne všechny, ale ty, které jsou podle názoru webu nejužitečnější a nejzajímavější:

Seznam všech textových funkcí najdete na záložce „Vzorce“ => rozevírací seznam „Text“:

Funkce LEFT

Vrátí podřetězec z textu v pořadí zleva doprava v rámci daného počtu znaků.

Syntax:=LEFT(text, [počet znaků])

Definice argumentů:

text – řetězec nebo odkaz na buňku obsahující text, ze kterého chcete vrátit podřetězec;
počet_znaků je volitelný argument. Celé číslo udávající, kolik znaků se má z textu vrátit. Výchozí hodnota je 1.

Příklad použití:

Vzorec: =LEFT("Vlastní text";8) – vrácená hodnota "Vlastní".

Funkce RIGHT

Tato funkce je podobná funkci LEFT s tím rozdílem, že znaky jsou vráceny z konce řetězce.

Příklad použití:

Vzorec: =RIGHT("volný text";5) – vrácená hodnota je "text".

Funkce LENGTH

Slouží k určení délky řetězce. Výsledkem je celé číslo udávající počet znaků textu.

Syntax:=DÉLKA(text)

Příklad použití:

Funkce FIND

Vrátí číslo, které je výskytem prvního znaku podřetězce hledaného textu. Pokud text není nalezen, vrátí se chyba „#VALUE!“.

Syntax:=FIND(hledaný_text; hledaný_text; [počáteční_pozice])

Definice argumentů:

search_text – řetězec, který má být nalezen;
search_text – text, ve kterém se hledá první argument;
start_position je volitelný prvek. Přijímá celé číslo, které určuje, kterým znakem text_to_search má vyhledávání začít. Výchozí hodnota je 1.

Příklad použití:

Z úryvku básně velkého básníka S.A. Yesenina najdeme výskyt prvního znaku řádku „pták“. Vyhledávání se provádí od začátku řádku. Pokud by ve výše uvedeném příkladu bylo vyhledávání provedeno od znaku 40, pak by funkce vrátila chybu, protože nebyla nalezena žádná vstupní pozice.

Funkce REPLACE

Tato funkce nahradí část řetězce v zadaném počtu znaků, počínaje zadaným znakem, novým textem.

Syntax: REPLACE(starý_text, počáteční_pozice, počet znaků, nový_text)

Definice argumentů:

starý_text – řetězec nebo odkaz na buňku obsahující text;
start_position – pořadové číslo znaku zleva doprava, od kterého se má provést nahrazení;
počet_znaků – počet znaků počínaje počáteční_pozicí včetně, které je třeba nahradit novým textem;
nový_text – řetězec, který nahrazuje část starého textu zadaného argumenty počáteční_pozice a počet znaků.

Příklad použití:

Zde je v řádku obsaženém v buňce A1 slovo „starý“, které začíná na 19. znaku a má 6 znaků, nahrazeno slovem „nový“.

V případě zpracování malého počtu buněk je tato metoda vcelku přijatelná. Pokud však potřebujete zpracovat několik tisíc různých řádků, bude proces vytváření vzorců náročný na práci. Proto zopakujeme uvažovaný příklad s využitím znalostí funkcí, které již byly popsány na začátku článku, konkrétně:

Argument „start_position“ bude nahrazen funkcí „FIND“;
Místo argumentu „počet znaků“ vložíme funkci „DÉLKA“.

Výsledkem je vzorec: =REPLACE(A1; NAJÍT("staré";A1);DÉLKA("staré");"Nový")

Podívejte se na tiskovou obrazovku okna funkcí. Vrácené výsledky argumentů se nezměnily, ale nyní je lze určit dynamicky jednorázovým složením vzorce.

Funkce SUBSTITUTE

Tato funkce nahrazuje výskyty zadaného podřetězce v textu novým textem, což je obdoba funkce „NAHRADIT“, ale je mezi nimi zásadní rozdíl. Pokud funkce "NAHRADIT" změní text zadaný znak po znaku ručně, pak funkce "NAHRADIT" automaticky vyhledá výskyty zadaného řetězce a změní je.

Syntax: SUBSTITUTE(text; starý_text; nový_text; [číslo_výskytu])

Definice argumentů:

old_text – podřetězec z prvního argumentu, který je třeba nahradit;
nový_text – řetězec, který nahradí starý text;
číslo_výskytu je volitelný argument. Přijímá celé číslo udávající pořadové číslo výskytu old_text, který má být nahrazen, všechny ostatní výskyty nebudou ovlivněny. Pokud ponecháte argument prázdný, budou nahrazeny všechny výskyty.

Příklad použití:

Řádek v buňce A1 obsahuje text, který má 2 podřetězce „staré“. Musíme nahradit řetězec „new“ místo prvního výskytu. V důsledku toho je část textu „...starý-starý...“ nahrazena „...nový-starý...“.

Pokud by byl poslední argument vynechán, výsledkem by byl řetězec "řetězec obsahující nový-nový text".

Funkce PSTR

PSTR vrátí ze zadaného řetězce část textu v zadaném počtu znaků, počínaje zadaným znakem.

Syntax: PSTR(text; počáteční_pozice; počet_znaků)

Definice argumentů:

text – řetězec nebo odkaz na buňku obsahující text;
start_position – pořadové číslo znaku, ze kterého má být řetězec vrácen;
number_characters – přirozené celé číslo udávající počet znaků, které mají být vráceny, počínaje pozicí počáteční_pozice.

Příklad použití:

Z textu umístěného v buňce A1 je potřeba vrátit poslední 2 slova, která mají celkovou délku 12 znaků. První znak vrácené fráze má pořadové číslo 12.

Argument počet_znaků může překročit povolenou délku vrácených znaků. Tito. pokud v uvažovaném příkladu byla místo čísla_znaků = 12 zadána hodnota 15, pak se výsledek nezměnil a funkce také vrátila řetězec „PSTR function“.

Pro usnadnění použití této funkce lze její argumenty nahradit funkcemi „FIND“ a „LENGTH“, jak bylo provedeno v příkladu s funkcí „REPLACE“.

Funkce SPACE

Tato funkce odstraní všechny nadbytečné mezery: mezery kolem okrajů a dvojité mezery mezi slovy. Poté, co funkce zpracuje řetězce, zůstanou mezi slovy pouze jednotlivé mezery.

Syntax:=SPACE(text)

Příklad použití:

PROSTOR( "Text s mezerami navíc mezi slovy a kolem okrajů" )

Výsledkem provedení funkce bude řádek: "Text s mezerami navíc mezi slovy a kolem okrajů" .

Funkce CONNECT

Pomocí funkce CONCATENATE můžete spojit několik řádků dohromady. Maximální počet řádků ke sloučení je 255.

Syntax:=CONCATENATE(text1; [text2]; ...)

Funkce musí obsahovat alespoň jeden argument

Pro usnadnění práce s textem má Excel textové funkce. Usnadňují zpracování stovek řádků najednou. Podívejme se na některé z nich s příklady.

Příklady funkce TEXT v Excelu

Převádí čísla na text. Syntaxe: hodnota (číselný odkaz nebo odkaz na buňku se vzorcem, jehož výsledkem je číslo); formát (pro zobrazení čísla jako textu).

Nejužitečnější funkcí funkce TEXT je formátování číselných dat, která mají být kombinována s textovými daty. Bez použití funkce Excel „nerozumí“ zobrazení čísel a převede je do základního formátu.

Ukažme si to na příkladu. Řekněme, že potřebujete zkombinovat text v řetězcích a číselných hodnotách:

Použití ampersandu bez funkce TEXT dává „neadekvátní“ výsledek:

Excel vrátil sériové číslo pro datum a obecný formát namísto formátu měny. Aby se tomuto výsledku zabránilo, použije se funkce TEXT. Formátuje hodnoty podle zadání uživatele.

Vzorec data nyní vypadá takto:

Druhým argumentem funkce je formát. Kde získám formátovací řetězec? Klepněte pravým tlačítkem myši na buňku s hodnotou. Klikněte na "Formátovat buňky". V okně, které se otevře, vyberte „všechny formáty“. Zkopírujte ten, který potřebujete, do řádku „Typ“. Vložte zkopírovanou hodnotu do vzorce.

Uveďme si další příklad, kde může být tato funkce užitečná. Na začátek čísla přidáme nuly. Pokud je zadáte ručně, Excel je smaže. Proto zavádíme vzorec:

Pokud potřebujete vrátit předchozí číselné hodnoty (bez nul), použijte operátor „--“:

Všimněte si, že hodnoty se nyní zobrazují v číselném formátu.

Funkce dělení textu v Excelu

Jednotlivé textové funkce a jejich kombinace umožňují rozdělit slova z jedné buňky do samostatných buněk:

LEFT (text; počet znaků) – zobrazí zadaný počet znaků od začátku buňky;
RIGHT (text; počet znaků) – vrací zadaný počet znaků od konce buňky;
SEARCH (hledaný text; rozsah vyhledávání; počáteční pozice) – zobrazuje pozici prvního výskytu hledaného znaku nebo řetězce při pohledu zleva doprava

Při dělení textu na řádek se bere v úvahu poloha každého znaku. Mezery označují začátek nebo konec názvu vyhledávání.

Pomocí funkcí rozložíme jméno, příjmení a patronymii do různých sloupců.

První řádek obsahuje pouze jméno a příjmení oddělené mezerou. Vzorec pro extrakci názvu: =LEFT(A2,SEARCH(" ",A2,1)). K určení druhého argumentu funkce LEFT - počtu znaků - se používá funkce HLEDAT. Najde mezeru v buňce A2, počínaje zleva.

Vzorec pro extrakci příjmení:

Excel používá funkci HLEDAT k určení počtu znaků pro funkci RIGHT. Funkce LENGTH „počítá“ celkovou délku textu. Poté se odečte počet znaků až do první mezery (zjištěný pomocí SEARCH).

Druhý řádek obsahuje křestní jméno, druhé jméno a příjmení. Pro název použijeme stejný vzorec:

Vzorec pro extrakci příjmení je mírně odlišný: Je to pět znaků vpravo. Vnořené funkce SEARCH hledají druhou a třetí mezeru v řetězci. SEARCH(" ";A3;1) najde první mezeru vlevo (před prostředním jménem). K nalezenému výsledku přidejte jednu (+1). Dostaneme pozici, ze které budeme hledat druhý prostor.

Část vzorce je SEARCH(" ";A3;SEARCH(" ";A3;1)+1) - najde druhou mezeru. Toto bude konečná pozice druhého jména.

Vzorec „pro patronymii“ je založen na stejných principech:

Funkce sloučení textu v Excelu

Chcete-li sloučit hodnoty z více buněk do jednoho řádku, použijte operátor ampersand (&) nebo funkci CONCATENATE.

Například hodnoty jsou umístěny v různých sloupcích (buňkách):

Umístěte kurzor do buňky, kde budou umístěny tři kombinované hodnoty. Zadejte rovno. Vyberte první buňku s textem a stiskněte & na klávesnici. Potom – znak mezery uzavřený v uvozovkách („ “). Znovu - &. A tak postupně spojujeme buňky textem a mezerami.

Dostaneme kombinované hodnoty v jedné buňce:

Použití funkce CONCATENATE:

Použitím uvozovek ve vzorci můžete do konečného výrazu přidat libovolný znak nebo text.

Funkce VYHLEDÁVÁNÍ textu v Excelu

Funkce SEARCH vrací počáteční pozici hledaného textu (nerozlišuje se malá a velká písmena). Například:

Funkce SEARCH vrátila pozici 10, protože slovo „Zakhar“ začíná desátým znakem v řádku. Kde to může být užitečné?

Funkce SEARCH určuje pozici znaku v textovém řetězci. A funkce PSTR vrací textové hodnoty (viz příklad výše). Nebo můžete nalezený text nahradit funkcí REPLACE.

Syntaxe funkce SEARCH:

„hledaný text“ - co je třeba najít;
„zobrazený text“ - kde hledat;
„počáteční pozice“ – od které pozice začít vyhledávání (výchozí – 1).

Pokud je třeba vzít v úvahu případ, použije se funkce FIND.

Vzorce v Excelu jsou jednou z nejdůležitějších výhod tohoto editoru. Díky nim se vaše schopnosti při práci s tabulkami několikanásobně zvyšují a jsou omezeny pouze vašimi dosavadními znalostmi. Můžeš dělat cokoliv. Excel vám přitom pomůže na každém kroku – speciální tipy jsou téměř v každém okně.

Chcete-li vytvořit jednoduchý vzorec, postupujte podle následujících pokynů:

Aktivujte libovolnou buňku. Klikněte na řádek pro zadání vzorce. Dejte rovnítko.

Zadejte libovolný výraz. Lze použít jako čísla

V tomto případě jsou postižené buňky vždy zvýrazněny. To se děje proto, abyste se svým výběrem neudělali chybu. Chybu je snazší vidět vizuálně než v textové podobě.

Z čeho se vzorec skládá?

Vezměme si jako příklad následující výraz.

Skládá se z:

symbol „=“ – začíná jím jakýkoli vzorec;
funkce "SUM";
argument funkce "A1:C1" (v tomto případě se jedná o pole buněk od "A1" do "C1");
operátor „+“ (dodatek);
odkazy na buňku "C1";
operátor „^“ (umocňování);
konstanta "2".

Pomocí operátorů

Operátory v editoru Excel označují, které operace je třeba provést se zadanými prvky vzorce. Výpočet probíhá vždy ve stejném pořadí:

závorky;
vystavovatelé;
násobení a dělení (v závislosti na posloupnosti);
sčítání a odčítání (také v závislosti na pořadí).

Aritmetický

Tyto zahrnují:

sčítání – „+“ (plus);

=2+2

negace nebo odčítání – „-“ (mínus);

=2-2 =-2

Pokud před číslo vložíte „mínus“, bude mít zápornou hodnotu, ale v absolutní hodnotě zůstane úplně stejné.

násobení - "*";

=2*2

dělení "/";

=2/2

procent "%";

=20%

umocnění – „^“.

=2^2

Porovnávací operátory

Tyto operátory se používají k porovnání hodnot. Operace vrátí hodnotu TRUE nebo FALSE. Tyto zahrnují:

znak „rovná se“ – „=“;

=C1=D1

znak „větší než“ – „>“;

=C1>D1

znak "menší než" - "<»;

=C1

znak „větší než nebo rovno“ – „>=“;

=C1>=D1

znak „menší nebo rovno“ – „<=»;

=C1<=D1

znak „nerovná se“ – „<>».

=C1<>D1

Operátor zřetězení textu

Pro tento účel se používá speciální znak „&“ (ampersand). Pomocí něj můžete spojovat různé fragmenty do jednoho celku - stejný princip jako u funkce „CONNECT“. Zde jsou nějaké příklady:

Pokud chcete sloučit text v buňkách, musíte použít následující kód.

=A1&A2&A3

Abyste mezi ně vložili jakýkoli symbol nebo písmeno, musíte použít následující konstrukci.

=A1&","&A2&","&A3

Sloučit můžete nejen buňky, ale i běžné symboly.

"Auto"&"mobil"

Jakýkoli jiný text než odkazy musí být citován. Jinak vzorec vygeneruje chybu.

Upozorňujeme, že použité uvozovky jsou přesně stejné jako na snímku obrazovky.

K definování odkazů lze použít následující operátory:

pro vytvoření jednoduchého odkazu na požadovaný rozsah buněk stačí označit první a poslední buňku této oblasti a mezi nimi symbol „:“;
pro spojení odkazů se používá znak „;“;
pokud je nutné určit buňky, které jsou v průsečíku několika rozsahů, pak se mezi odkazy umístí „mezera“. V tomto případě se zobrazí hodnota buňky „C7“.

Protože pouze to spadá pod definici „průniku množin“. Toto je název přidělený tomuto operátoru (mezera).

Pomocí odkazů

Při práci v editoru Excel můžete používat různé typy odkazů. Většina začínajících uživatelů však ví, jak používat pouze ty nejjednodušší z nich. Naučíme vás, jak správně zadávat odkazy všech formátů.

Jednoduché odkazy A1

Tento typ se zpravidla používá nejčastěji, protože je mnohem pohodlnější skládat než ostatní.

sloupce – od A do XFD (ne více než 16384);
linky – od 1 do 1048576.

Zde jsou nějaké příklady:

buňka na průsečíku řádku 5 a sloupce B je „B5“;
rozsah buněk ve sloupci B od řádku 5 do řádku 25 je „B5:B25“;
rozsah buněk v řádku 5 počínaje sloupcem B až F je „B5:F5“;
všechny buňky v řádku 10 jsou „10:10“;
všechny buňky v řádcích 10 až 15 jsou „10:15“;
všechny buňky ve sloupci B jsou „B:B“;
všechny buňky ve sloupcích B až K jsou „B:K“;
Rozsah buněk B2 až F5 je „B2-F5“.

Někdy vzorce využívají informace z jiných listů. Funguje následovně.

=SUM(List2!A5:C5)

Druhý list obsahuje následující informace.

Pokud je v názvu listu mezera, musí být ve vzorci uvedena v jednoduchých uvozovkách (apostrofech).

=SUM("List číslo 2"!A5:C5)

Absolutní a relativní vazby

Excel editor pracuje se třemi typy odkazů:

absolutní;
relativní;
smíšený.

Pojďme se na ně podívat blíže.

Všechny výše uvedené příklady odkazují na relativní adresy buněk. Tento typ je nejoblíbenější. Hlavní praktickou výhodou je, že editor během migrace změní reference na jinou hodnotu. V souladu s tím, kam přesně jste tento vzorec zkopírovali. Pro výpočet bude vzat v úvahu počet buněk mezi starou a novou pozicí.

Představte si, že potřebujete roztáhnout tento vzorec přes celý sloupec nebo řádek. Písmena a čísla v adresách buněk nebudete ručně měnit. Funguje následovně.

Zadáme vzorec pro výpočet součtu prvního sloupce.

=SUM(B4:B9)

Stiskněte klávesové zkratky Ctrl + C. Chcete-li přenést vzorec do sousední buňky, musíte tam jít a stisknout Ctrl + V.

Pokud je tabulka velmi velká, je lepší kliknout na pravý dolní roh a bez uvolnění prstu přetáhnout ukazatel na konec. Pokud je dat málo, pak je kopírování pomocí horkých kláves mnohem rychlejší.

Nyní se podívejte na nové vzorce. Index sloupce se automaticky změnil.

Pokud chcete, aby při přenosu vzorců zůstaly zachovány všechny vazby (tedy aby se automaticky neměnily), musíte použít absolutní adresy. Jsou označeny jako "$B$2".

=SUM($B$4:$B$9)

V důsledku toho vidíme, že nedošlo k žádným změnám. Všechny sloupce zobrazují stejné číslo.

Tento typ adresy se používá, když je potřeba opravit pouze sloupec nebo řádek a ne všechny najednou. Lze použít následující konstrukce:

$D1, $F5, $G3 – pro upevnění sloupků;
D$1, F$5, G$3 – pro upevnění řad.

S takovými vzorci pracujte pouze v případě potřeby. Pokud například potřebujete pracovat s jedním konstantním řádkem dat, ale měnit pouze sloupce. A co je nejdůležitější, pokud se chystáte vypočítat výsledek v různých buňkách, které nejsou umístěny na stejné lince.

Faktem je, že když vzorec zkopírujete na jiný řádek, čísla v odkazech se automaticky změní na počet buněk z původní hodnoty. Pokud použijete smíšené adresy, vše zůstane na svém místě. To se provádí následovně.

Použijme následující výraz jako příklad.

= B$4

Přesuňme tento vzorec do jiné buňky. Nejlépe ne na dalším nebo jiném řádku. Nyní vidíte, že nový výraz obsahuje stejný řádek (4), ale jiné písmeno, protože to bylo jediné, které bylo relativní.

3D odkazy

Pojem „trojrozměrný“ zahrnuje ty adresy, ve kterých je uveden rozsah listů. Příklad vzorce vypadá takto.

=SUM(List1:List4!A5)

V tomto případě bude výsledek odpovídat součtu všech buněk „A5“ na všech listech, počínaje 1 až 4. Při sestavování takových výrazů musíte dodržet následující podmínky:

takové odkazy nelze použít v polích;
je zakázáno používat trojrozměrné výrazy tam, kde dochází k průniku buněk (například operátor „mezera“);
Při vytváření vzorců s 3D adresami můžete použít následující funkce: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNTA, COUNT, MIN, MAX, MINA, MAX, VARVE, PRODUCT, VARIANCE, VAR a DISPA.

Pokud tato pravidla porušíte, uvidíte nějakou chybu.

Odkazy ve formátu R1C1

Tento typ odkazu se liší od „A1“ tím, že číslo není přiřazeno pouze řádkům, ale také sloupcům. Vývojáři se rozhodli nahradit běžné zobrazení touto možností pro pohodlí v makrech, ale lze je použít kdekoli. Zde je několik příkladů takových adres:

R10C10 – absolutní odkaz na buňku, která se nachází na desátém řádku desátého sloupce;
R – absolutní vazba na aktuální (ve kterém je uveden vzorec) vazba;
R[-2] – relativní vazba k přímce, která se nachází o dvě pozice nad touto;
R[-3]C je relativní odkaz na buňku, která je umístěna o tři pozice výše v aktuálním sloupci (kam jste se rozhodli napsat vzorec);
RC je relativní odkaz na buňku, která se nachází pět buněk vpravo a pět řádků pod aktuální buňkou.

Použití jmen

Excel umožňuje vytvářet vlastní jedinečné názvy pro pojmenování rozsahů buněk, jednotlivých buněk, tabulek (regulárních a kontingenčních), konstant a výrazů. Zároveň pro editora není žádný rozdíl při práci se vzorci - rozumí všemu.

Můžete použít názvy pro násobení, dělení, sčítání, odčítání, výpočet úroků, koeficienty, odchylka, zaokrouhlování, DPH, hypotéka, úvěr, odhad, časové rozvrhy, různé formuláře, slevy, platy, délka služby, výplata anuity, práce s VPR vzorce , „VSD“, „MEDZIPRODUKT. VÝSLEDKY“ a tak dále. To znamená, že si můžete dělat, co chcete.

Existuje pouze jedna hlavní podmínka - tento název musíte definovat předem. Jinak se o tom Excel nic nedozví. To se provádí následovně.

Vyberte sloupec.
Vyvolejte kontextovou nabídku.
Vyberte "Přiřadit jméno".

Zadejte požadovaný název pro tento objekt. V tomto případě musíte dodržovat následující pravidla.

Pro uložení klikněte na tlačítko „OK“.

Stejným způsobem můžete přiřadit název buňce, textu nebo číslu.

Informace v tabulce můžete použít jak pomocí názvů, tak pomocí běžných odkazů. Takto vypadá standardní verze.

A pokud se pokusíte vložit naše jméno místo adresy „D4:D9“, uvidíte nápovědu. Stačí napsat pár znaků a uvidíte, co sedne (z databáze jmen) nejvíce.

V našem případě je vše jednoduché - „sloupec_3“. Představte si, že takových jmen budete mít velké množství. Nebudete si moci zapamatovat vše zpaměti.

Používání funkcí

Existuje několik způsobů, jak vložit funkci do Excelu:

ručně;
pomocí panelu nástrojů;
pomocí okna Vložit funkci.

Pojďme se na jednotlivé metody podívat blíže.

V tomto případě je vše jednoduché - pomocí rukou, vlastních znalostí a dovedností zadáváte vzorce do speciálního řádku nebo přímo do buňky.

Pokud v této oblasti nemáte pracovní zkušenosti, je lepší použít zpočátku jednodušší metody.

V tomto případě je nutné:

Přejděte na kartu "Vzorce".
Klikněte na libovolnou knihovnu.
Vyberte požadovanou funkci.

Ihned poté se objeví okno Argumenty a funkce s již vybranou funkcí. Jediné, co musíte udělat, je zadat argumenty a uložit vzorec pomocí tlačítka „OK“.

Průvodce nahrazením

Můžete jej aplikovat následovně:

Aktivujte libovolnou buňku.
Klikněte na ikonu „Fx“ nebo použijte klávesovou zkratku SHIFT + F3.

Ihned poté se otevře okno „Vložit funkci“.
Zde uvidíte velký seznam různých funkcí seřazených podle kategorií. Kromě toho můžete použít vyhledávání, pokud nemůžete najít položku, kterou potřebujete.

Jediné, co musíte udělat, je zadat nějaké slovo, které může popsat, co chcete dělat, a editor se pokusí zobrazit všechny vhodné možnosti.

Vyberte funkci z poskytnutého seznamu.
Chcete-li pokračovat, musíte kliknout na tlačítko „OK“.

Poté budete požádáni, abyste specifikovali "Argumenty a funkce". Můžete to udělat ručně nebo jednoduše vybrat požadovaný rozsah buněk.
Chcete-li použít všechna nastavení, musíte kliknout na tlačítko „OK“.

V důsledku toho uvidíme číslo 6, i když to již bylo jasné, protože předběžný výsledek je zobrazen v okně „Argumenty a funkce“. Data se okamžitě přepočítají, když se změní některý z argumentů.

Použití vnořených funkcí

Jako příklad použijeme vzorce s logickými podmínkami. K tomu budeme muset přidat nějaký druh tabulky.

Poté postupujte podle následujících pokynů:

Klikněte na první buňku. Vyvolejte okno „Vložit funkci“. Vyberte funkci "Pokud". Pro vložení klikněte na „OK“.

Pak budete muset vytvořit nějaký logický výraz. Musí být zapsán do prvního pole. Můžete například přidat hodnoty tří buněk do jednoho řádku a zkontrolovat, zda je součet větší než 10. Pokud je „pravda“, uveďte text „Větší než 10“. Pro falešný výsledek – „méně než 10“. Poté klikněte na „OK“ pro návrat do pracovního prostoru.

V důsledku toho vidíme následující - editor ukázal, že součet buněk ve třetím řádku je menší než 10. A to je správně. To znamená, že náš kód funguje.

=IF(SUM(B3:D3)>10,"Více než 10","Méně než 10")

Nyní musíte nakonfigurovat následující buňky. V tomto případě náš vzorec jednoduše sahá dále. Chcete-li to provést, musíte nejprve najet kurzorem na pravý dolní roh buňky. Po změně kurzoru musíte kliknout levým tlačítkem a zkopírovat jej úplně dolů.

V důsledku toho editor přepočítá náš výraz pro každý řádek.

Jak můžete vidět, kopírování bylo docela úspěšné, protože jsme použili relativní odkazy, o kterých jsme hovořili dříve. Pokud potřebujete přiřadit adresy k argumentům funkce, použijte absolutní hodnoty.

Můžete to udělat několika způsoby: pomocí řádku vzorců nebo speciálního průvodce. V prvním případě je vše jednoduché - klikněte do speciálního pole a ručně zadejte potřebné změny. Ale psát tam není úplně pohodlné.

Jediné, co můžete udělat, je zvětšit vstupní pole. Chcete-li to provést, stačí kliknout na uvedenou ikonu nebo stisknout kombinaci kláves Ctrl + Shift + U.

Stojí za zmínku, že toto je jediný způsob, pokud ve svém vzorci nepoužíváte funkce.

Používáte-li funkce, vše je mnohem jednodušší. Chcete-li upravit, musíte postupovat podle následujících pokynů:

Aktivujte buňku se vzorcem. Klikněte na ikonu "Fx".

Poté se objeví okno, ve kterém můžete velmi pohodlným způsobem změnit argumenty funkce, které potřebujete. Navíc zde můžete přesně zjistit, jaký bude výsledek přepočtu nového výrazu.

Pro uložení provedených změn použijte tlačítko „OK“.

Chcete-li odstranit výraz, proveďte následující:

Klikněte na libovolnou buňku.

Klikněte na tlačítko Smazat nebo Backspace. V důsledku toho bude buňka prázdná.

Přesně stejného výsledku dosáhnete pomocí nástroje „Vymazat vše“.

Možné chyby při vytváření vzorců v editoru Excel

Níže jsou uvedeny nejčastější chyby uživatelů:

Výraz používá obrovské množství hnízdění. Nemělo by jich být více než 64;
vzorce označují cesty k externím knihám bez úplné cesty;
Otevírací a zavírací konzoly jsou umístěny nesprávně. To je důvod, proč jsou v editoru na řádku vzorců všechny závorky zvýrazněny jinou barvou;

názvy knih a listů se neuvádějí v uvozovkách;
čísla jsou použita ve špatném formátu. Pokud například potřebujete zadat 2 000 $, musíte jednoduše zadat 2 000 a vybrat příslušný formát buňky, protože symbol $ program používá pro absolutní odkazy;

Požadované argumenty funkce nejsou zadány. Všimněte si, že volitelné argumenty jsou uzavřeny v hranatých závorkách. Vše bez nich je nutné, aby vzorec správně fungoval;

Rozsahy buněk jsou zadány nesprávně. K tomu musíte použít operátor „:“ (dvojtečka).

Chybové kódy při práci se vzorci

Při práci se vzorcem se mohou zobrazit následující možnosti chyb:

#HODNOTA! – tato chyba označuje, že používáte nesprávný datový typ. Pokoušíte se například použít text místo číselné hodnoty. Excel samozřejmě nebude schopen vypočítat součet mezi dvěma frázemi;
#NÁZEV? – taková chyba znamená, že jste udělali překlep v pravopisu názvu funkce. Nebo se snažíte zadat něco, co neexistuje. To nemůžeš. Kromě toho může být problém i v něčem jiném. Pokud jste si jisti názvem funkce, zkuste se na vzorec podívat blíže. Možná jste zapomněli závorku. Kromě toho musíte vzít v úvahu, že fragmenty textu jsou uvedeny v uvozovkách. Pokud vše ostatní selže, zkuste sestavit výraz znovu;
#ČÍSLO! – zobrazení takové zprávy znamená, že máte problém s argumenty nebo výsledkem vzorce. Číslo se například ukázalo jako příliš obrovské nebo naopak malé;
#DIV/0 – tato chyba znamená, že se pokoušíte napsat výraz, ve kterém dojde k dělení nulou! Excel nemůže přepsat pravidla matematiky. Proto je zde i takové jednání zakázáno;
#N/A! – editor může zobrazit tuto zprávu, pokud některá hodnota není k dispozici. Pokud například použijete funkce SEARCH, SEARCH, MATCH a Excel nenajde hledaný fragment. Nebo neexistují vůbec žádná data a vzorec nemá s čím pracovat;
Pokud se pokoušíte něco vypočítat a Excel zapíše slovo #REF!, pak argument funkce používá nesprávný rozsah buněk;
#PRÁZDNÝ! – tato chyba se objeví, pokud máte nekonzistentní vzorec s překrývajícími se rozsahy. Přesněji, pokud ve skutečnosti žádné takové buňky (které jsou náhodou na průsečíku dvou rozsahů) neexistují. Tato chyba se často vyskytuje náhodou. V argumentu stačí nechat jednu mezeru a editor to bude vnímat jako speciální operátor (o tom jsme mluvili dříve).

Když upravíte vzorec (buňky jsou zvýrazněné), uvidíte, že se ve skutečnosti neprotínají.

Někdy můžete vidět spoustu # znaků, které zcela vyplňují šířku buňky. Ve skutečnosti zde není žádná chyba. To znamená, že pracujete s čísly, která se do dané buňky nevejdou.

Chcete-li zobrazit hodnotu tam obsaženou, stačí změnit velikost sloupce.

Kromě toho můžete použít formátování buněk. Chcete-li to provést, musíte provést několik jednoduchých kroků:

Vyvolejte kontextovou nabídku. Vyberte Formát buněk.

Zadejte typ jako "Obecné". Chcete-li pokračovat, použijte tlačítko „OK“.

Díky tomu bude Excel editor schopen toto číslo převést do jiného formátu, který se do tohoto sloupce vejde.

Příklady použití vzorců

Editor Microsoft Excel vám umožňuje zpracovávat informace jakýmkoli způsobem, který vám vyhovuje. Jsou k tomu všechny potřebné podmínky a příležitosti. Podívejme se na několik příkladů vzorců podle kategorií. To vám usnadní pochopení.

Chcete-li vyhodnotit matematické schopnosti aplikace Excel, musíte provést následující kroky.

Vytvořte tabulku s některými podmíněnými daty.

Pro výpočet částky zadejte následující vzorec. Pokud chcete přidat pouze jednu hodnotu, můžete použít operátor sčítání („+“).

=SUM(B3:C3)

Kupodivu v editoru Excelu nemůžete používat funkce. Pro odečítání se používá obvyklý operátor „-“. V tomto případě bude kód následující.

=B3-C3

Abyste mohli určit, kolik procent je první číslo od druhého, musíte použít tuto jednoduchou konstrukci. Pokud chcete odečíst několik hodnot, budete muset pro každou buňku zadat „mínus“.

=B3/C3%

Všimněte si, že symbol procent je umístěn na konci, nikoli na začátku. Navíc při práci s procenty není potřeba dodatečně násobit 100. To se děje automaticky.

Excel může přidávat na základě několika podmínek najednou. Můžete vypočítat součet buněk v prvním sloupci, jejichž hodnota je větší než 2 a menší než 6. A stejný vzorec lze nastavit i pro druhý sloupec.

=SUMIFS(B3:B9,B3:B9,">2"B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2"C3:C9,"<6")
Můžete také spočítat počet prvků, které splňují nějakou podmínku. Nechte například Excel spočítat, kolik čísel máme větších než 3.

=COUNTIF(B3:B9;">3") =COUNTIF(C3:C9,">3")
Výsledek všech vzorců bude následující.

Matematické funkce a grafy

Pomocí Excelu můžete vypočítat různé funkce a na jejich základě vytvořit grafy a poté provést grafickou analýzu. Takové techniky se zpravidla používají v prezentacích.

Jako příklad si zkusme sestavit grafy pro exponent a nějakou rovnici. Pokyny budou následující:

Vytvoříme tabulku. V prvním sloupci budeme mít počáteční číslo "X", ve druhém - funkci "EXP", ve třetím - zadaný poměr. Bylo by možné udělat kvadratický výraz, ale pak by výsledná hodnota prakticky zmizela na pozadí exponenciály na grafu.

Jak jsme řekli dříve, růst exponentu nastává mnohem rychleji než u obyčejné kubické rovnice.

Tímto způsobem lze graficky znázornit jakoukoli funkci nebo matematický výraz.

Vše popsané výše je vhodné pro moderní programy z let 2007, 2010, 2013 a 2016. Starý editor Excel je výrazně horší, pokud jde o možnosti, množství funkcí a nástrojů. Pokud otevřete oficiální nápovědu od společnosti Microsoft, uvidíte, že navíc označují, ve které verzi programu se tato funkce objevila.

Ve všech ostatních ohledech vše vypadá téměř úplně stejně. Jako příklad si spočítejme součet několika buněk. K tomu potřebujete:

Uveďte některá data pro výpočet. Klikněte na libovolnou buňku. Klikněte na ikonu "Fx".

Vyberte kategorii „Matematická“. Najděte funkci „SUM“ a klikněte na „OK“.

Můžete zkusit přepočítat v jakémkoli jiném editoru. Proces bude probíhat úplně stejně.

Závěr

V tomto tutoriálu jsme mluvili o všem, co souvisí se vzorci v editoru Excel, od těch nejjednodušších až po velmi složité. Každá část byla doplněna podrobnými příklady a vysvětleními. To se provádí s cílem zajistit, aby informace byly přístupné i úplným figurínám.

Pokud se vám něco nedaří, znamená to, že někde děláte chybu. Možná máte chybně napsané výrazy nebo nesprávné odkazy na buňky. Hlavní věc je pochopit, že vše je třeba zavést velmi opatrně a opatrně. Navíc všechny funkce nejsou v angličtině, ale v ruštině.

Kromě toho je důležité si uvědomit, že vzorce musí začínat symbolem „=“ (rovná se). Mnoho začínajících uživatelů na to zapomíná.

Soubor příkladů

Abychom vám usnadnili pochopení dříve popsaných vzorců, připravili jsme speciální demo soubor, ve kterém byly zkompilovány všechny výše uvedené příklady. Můžete to udělat z našich webových stránek zcela zdarma. Pokud při tréninku použijete hotovou tabulku se vzorci na základě vyplněných dat, dosáhnete výsledků mnohem rychleji.

Video návod

Pokud vám náš popis nepomohl, zkuste se podívat na níže přiložené video, které podrobněji vysvětluje hlavní body. Možná děláte všechno správně, ale něco vám chybí. S pomocí tohoto videa byste měli porozumět všem problémům. Doufáme, že vám podobné lekce pomohly. Sledujte nás častěji.

Funkce TEXT převádí číselnou hodnotu na text a umožňuje určit formát zobrazení pomocí speciálních formátovacích řetězců.

Popis funkce TEXT

Funkce TEXT převádí číselnou hodnotu na text a umožňuje určit formát zobrazení pomocí speciálních formátovacích řetězců. Tato funkce je užitečná, chcete-li čísla zobrazit v pohodlnějším formátu nebo pokud chcete čísla kombinovat s textem nebo symboly.

Chcete-li například naformátovat číslo 23,5 jako částku v rublech, můžete použít následující vzorec:

TEXT(A1"0,00r."")

Výsledek: 23,50 rub.

Čísla lze také formátovat pomocí příkazů ve skupině Číslo, která se nachází na kartě Domů na pásu karet. Tyto příkazy však fungují pouze v případě, že buňka obsahuje pouze číslo. Pokud chcete číslo naformátovat a zkombinovat s jiným textem, je nejlepší použít funkci TEXT. Do předchozího vzorce můžete přidat například následující text:

TEXT(A1;„0,00r.“)& „za hodinu“

Výsledek: 23,50 rub. v jednu hodinu .

Syntax

=TEXT(hodnota, formát)

Argumenty

formát hodnoty

Povinný argument.Číselná hodnota, vzorec, který se vyhodnotí jako číselná hodnota, nebo odkaz na buňku obsahující číselnou hodnotu.

Povinný argument.Číselný formát jako textový řetězec v uvozovkách (například "D.M.YYYY" nebo "# ##0.00". Pravidla formátování jsou uvedena níže.

Formáty čísel

Agregát	Popis
0 (nula)	Zobrazí koncové nuly, pokud má číslo méně číslic, než je počet nul ve formátovacím řetězci. Například formát #.00 umožňuje zobrazení hodnoty 8.9 jako 8.90.
#	Funguje stejně jako 0 (nula). V tomto případě však funkce Text nevrací nuly navíc, pokud má číslo vlevo nebo vpravo od oddělovače desetinných míst méně číslic, než je počet # znaků ve formátovacím řetězci. Například číslo 8.9 se zobrazí jako 8.9 při použití formátu #,##.
?	Řídí se stejnými pravidly jako 0 (nula). Místo úvodních nul se však před a za desetinnou čárkou objevují mezery, aby bylo zajištěno, že desetinné čárky jsou ve sloupci zarovnány. Například formát 0.0? poskytuje zarovnání čárkami pro čísla 8,9 a 88,99. (Pro čistotu experimentu byste si však měli vybrat)
, (čárka)	Zobrazí znak oddělovače desetinných míst. Chování tohoto znaku závisí na jazyku vlastního formátu.

Pokud má zlomková část čísla ve formátu více číslic než zástupných symbolů, je číslo zaokrouhleno na počet číslic rovný počtu zástupných symbolů. Pokud je v celé části čísla více číslic, než je zástupných symbolů ve formátu, zobrazí se číslice navíc. Pokud formát obsahuje pouze číselné znaky (#) nalevo od desetinné čárky, pak čísla menší než 1 budou začínat čárkou, např. ,47 .

Oddělovač tisíců skupin

Chcete-li zobrazit mezeru jako oddělovač tisíců nebo reprezentovat číslo v násobcích 1000, vložte do formátu čísla následující oddělovač.

Formáty data a času. Dny, měsíce a roky. Chcete-li zobrazit čísla ve formátu data (s dnem, měsícem a rokem), argument formátu používá níže uvedené kódy.

Formát	Popis
M	Zobrazte měsíc jako číslo bez úvodní nuly.
MM	V případě potřeby zobrazte měsíc jako číslo s nulou na začátku.
MMM	Zobrazuje zkrácený název měsíce (leden–prosinec).
MMMM	Zobrazuje celý název měsíce (leden–prosinec).
MMMMM	Zobrazuje měsíc jako jedno písmeno (Z–D).
D	Zobrazuje den jako číslo bez úvodní nuly.
DD	V případě potřeby zobrazte den jako číslo s nulou na začátku.
DDD	Zobrazuje zkrácený název dne v týdnu (Po-Ne).
DDDD	Zobrazuje celý název dne v týdnu (pondělí–neděle).
GG	Zobrazuje rok jako dvoumístné číslo.
YYYY	Zobrazuje rok jako čtyřmístné číslo.

Hodiny, minuty a sekundy

Chcete-li zobrazit čísla ve formátu času (s hodinami, minutami a sekundami), argument format používá níže uvedené kódy.

Formát	Popis
h	Zobrazte hodinu jako číslo bez úvodní nuly.
[h]	Zobrazuje uplynulý čas v hodinách. Pokud vzorec vrací časovou hodnotu, ve které je počet hodin větší než 24, použijte číselný formát, jako je tento: [h]:mm:ss.
hh	V případě potřeby zobrazte hodinu jako číslo s nulou na začátku. Pokud je formát označen AM nebo PM, hodiny se zobrazují ve 12hodinovém cyklu. Jinak se používá 24hodinový cyklus.
m	Zobrazuje minuty jako číslo bez úvodní nuly. *Poznámka.*
[m]	Zobrazuje uplynulý čas v minutách. Pokud vzorec vrací časovou hodnotu, ve které je počet minut větší než 60, použijte číselný formát, jako je tento: [mm]:ss.
mm	V případě potřeby zobrazte minuty jako číslo s nulou na začátku. *Poznámka.* Kód m nebo mm musí být uveden bezprostředně za kódem h nebo hh nebo bezprostředně před kódem ss, jinak se místo minut zobrazí měsíc.
S	Zobrazí sekundy jako číslo bez úvodní nuly.
[S]	Zobrazuje uplynulý čas v sekundách. Pokud váš vzorec vrací časovou hodnotu, ve které je počet sekund větší než 60, použijte formát čísla, jako je tento: [ss].
ss	V případě potřeby zobrazte sekundy jako číslo s nulou na začátku. Chcete-li zobrazit zlomky sekundy, použijte formát čísla, jako je tento: h:mm:ss,00.
AM/PM, a/pm, A/P, a/p	Zobrazení hodin na základě 12hodinového cyklu. Kódy AM, am, A a a znamenají denní dobu od půlnoci do poledne a PM, pm, P a p znamenají dobu od poledne do půlnoci.

Formáty měny, úroku a vědecké notace

Symboly měn. Chcete-li číslu předcházet znak dolaru ($), zadejte jej na začátek argumentu formátu (například "$# ##0.00"). Chcete-li do číselného formátu zahrnout jeden z následujících symbolů měny, stiskněte klávesu NUM LOCK a pomocí numerické klávesnice zadejte kód ANSI pro odpovídající symbol.

Poznámka . Musíte použít numerickou klávesnici. Při použití klávesy ALT s čísly v horní řadě kláves se kódy ANSI negenerují.
Procentuální hodnoty. Chcete-li zobrazit čísla jako procenta – například 0,08 jako 8 % nebo 2,8 jako 280 % – vložte do argumentu formátu znak procenta (%).
Exponenciální zápis. Chcete-li zobrazit čísla v exponenciálním formátu, vložte do argumentu formátu jeden z následujících kódů.

Včetně textu a přidávání mezer

Pokud do argumentu formátu zahrnete některý z následujících znaků, zobrazí se tak, jak byly zadány.

Poznámky

Argument formátu nemůže obsahovat hvězdičku (*).
Funkce TEXT převede číslo na formátovaný text a výsledek již nelze použít jako číslo ve výpočtech. Chcete-li naformátovat číslo a přesto jej zachovat jako číslo, klikněte pravým tlačítkem na buňku, klikněte na Formát buněk a v dialogovém okně Formát buněk na kartě Číslo upravte požadované možnosti formátování. Další informace o použití dialogového okna Formát buněk získáte klepnutím na tlačítko nápovědy (?) v pravém horním rohu okna.

Oblíbené v kategorii: