TOP 15 vzorců v Excelu

Excel je rozhodně jedním z nejdůležitějších programů. Usnadnil život mnoha uživatelům. Excel umožňuje automatizovat i ty nejsložitější výpočty a to je hlavní výhoda tohoto programu.

Standardní uživatel zpravidla používá pouze omezenou sadu funkcí, zatímco existuje mnoho vzorců, které umožňují implementovat stejné úkoly, ale mnohem rychleji.

To může být užitečné, pokud musíte neustále provádět mnoho akcí stejného typu, které vyžadují velký počet operací.

Stal se zajímavým? Pak vítejte u přehledu 15 nejužitečnějších vzorců Excelu.

Nějaká terminologie

Než začnete přímo kontrolovat funkce, musíte pochopit, co to je. Tento koncept znamená vzorec stanovený vývojáři, podle kterého se provádějí výpočty a na výstupu se získá určitý výsledek. 

Každá funkce má dvě hlavní části: název a argument. Vzorec se může skládat z jedné nebo několika funkcí. Chcete-li jej začít psát, musíte dvakrát kliknout na požadovanou buňku a napsat rovnítko.

Další částí funkce je název. Ve skutečnosti je to název vzorce, který pomůže Excelu pochopit, co uživatel chce. Za ním následují argumenty v závorkách. Toto jsou parametry funkcí, které se berou v úvahu při provádění určitých operací. Existuje několik typů argumentů: numerické, textové, logické. Místo nich se také často používají odkazy na buňky nebo konkrétní rozsah. Každý argument je od druhého oddělen středníkem.

Syntaxe je jedním z hlavních pojmů, které charakterizují funkci. Tento termín označuje šablonu pro vkládání určitých hodnot, aby funkce fungovala.

A nyní si to vše ověříme v praxi.

Formule 1: VLOOKUP

Tato funkce umožňuje vyhledat požadované informace v tabulce a zobrazit vrácený výsledek v konkrétní buňce. Zkratka názvu funkce znamená „Vertikální pohled“.

Syntax

Jedná se o poměrně složitý vzorec, který má 4 argumenty a jeho použití má mnoho funkcí.

Syntaxe je:

=VLOOKUP(vyhledávací_hodnota, tabulka, číslo_sloupce, [vyhledání_rozsahu])

Podívejme se blíže na všechny argumenty:

1. Hodnota, kterou je třeba vyhledat.
2. Stůl. Je nutné, aby v prvním sloupci byla vyhledávací hodnota a také hodnota, která je vrácena. Ten druhý se nachází kdekoli. Uživatel se může nezávisle rozhodnout, kam vloží výsledek vzorce. 
3. Číslo sloupce.
4. Intervalové prohlížení. Pokud to není nutné, můžete hodnotu tohoto argumentu vynechat. Je to booleovský výraz udávající stupeň přesnosti shody, kterou by funkce měla najít. Pokud je zadán parametr „True“, bude Excel hledat nejbližší hodnotu k hodnotě zadané jako hledaná hodnota. Pokud je zadán parametr „False“, bude funkce hledat pouze ty hodnoty, které jsou v prvním sloupci.

Na tomto snímku obrazovky se pomocí vzorce snažíme zjistit, kolik zobrazení bylo vygenerováno pro dotaz „koupit tablet“.

Formule 2: Pokud

Tato funkce je nezbytná, pokud chce uživatel nastavit určitou podmínku, za které se má konkrétní hodnota vypočítat nebo vygenerovat. Může mít dvě možnosti: true a false.

Syntax

Vzorec pro tuto funkci má tři hlavní argumenty a vypadá takto:

=IF(logický_výraz, „hodnota_pokud_pravda“, „hodnota_pokud_nepravda“).

Logický výraz zde znamená vzorec, který přímo popisuje kritérium. S jeho pomocí budou data zkontrolována, zda splňují určitou podmínku. V souladu s tím je argument „hodnota, je-li nepravda“ určen pro stejný úkol, pouze s tím rozdílem, že jde o zrcadlo opačného významu. Jednoduše řečeno, pokud není podmínka potvrzena, program provede určité akce.

Existuje další způsob použití funkce IF – vnořené funkce. Podmínek zde může být mnohem více, až 64. Příklad zdůvodnění odpovídající vzorci uvedenému na snímku obrazovky je následující. Pokud se buňka A2 rovná dvěma, musíte zobrazit hodnotu „Ano“. Pokud má jinou hodnotu, musíte zkontrolovat, zda se buňka D2 rovná dvěma. Pokud ano, musíte vrátit hodnotu „ne“, pokud se zde podmínka ukáže jako nepravdivá, vzorec by měl vrátit hodnotu „možná“.

Nedoporučuje se používat vnořené funkce příliš často, protože jejich použití je poměrně obtížné, jsou možné chyby. A jejich oprava bude trvat dlouho. 

funkce IF lze také použít k určení, zda je konkrétní buňka prázdná. K dosažení tohoto cíle je třeba použít ještě jednu funkci − ISBLANK.

Zde je syntaxe:

=IF(ISBLANK(číslo buňky),”Prázdné”,”Není prázdné”).

Navíc je možné použít místo funkce ISBLANK použijte standardní vzorec, ale uveďte, že za předpokladu, že v buňce nejsou žádné hodnoty.

POKUD – toto je jedna z nejběžnějších funkcí, která se velmi snadno používá a umožňuje vám pochopit, jak pravdivé jsou určité hodnoty, získat výsledky pro různá kritéria a také určit, zda je určitá buňka prázdná.

Tato funkce je základem pro některé další vzorce. Některé z nich nyní rozebereme podrobněji.

Formule 3: SUMIF

funkce SUMMESLI umožňuje shrnout údaje za předpokladu, že splňují určitá kritéria.

Syntax

Tato funkce, stejně jako předchozí, má tři argumenty. Chcete-li jej použít, musíte takový vzorec napsat a nahradit potřebné hodnoty na příslušných místech.

=SUMIF(rozsah; podmínka; [rozsah_součtu])

Podívejme se podrobněji na to, co je každý z argumentů:

1. Stav. Tento argument umožňuje předat funkci buňky, které dále podléhají součtu.
2. Rozsah součtu. Tento argument je volitelný a umožňuje zadat buňky, které se mají sečíst, pokud je podmínka nepravdivá.

V této situaci tedy Excel shrnul data o těch dotazech, kde počet přechodů přesahuje 100000 XNUMX.

Formule 4: SUMMESLIMN

Pokud existuje několik podmínek, použije se související funkce SUMMESLIMN.

Syntax

Vzorec pro tuto funkci vypadá takto:

=SUMIFS(rozsah_součtu, rozsah_stavy1, podmínka1, [rozsah_podmínek2, podmínka2], …)

Druhý a třetí argument jsou povinné, konkrétně „Rozsah podmínky 1“ a „Rozsah podmínky 1“.

Formule 5: COUNTIF a COUNTIFS

Tato funkce se pokouší určit počet neprázdných buněk, které odpovídají daným podmínkám v rozsahu zadaném uživatelem.

Syntax

Chcete-li zadat tuto funkci, musíte zadat následující vzorec:

= COUNTIF (rozsah, kritéria)

Co znamenají uvedené argumenty?

1. Rozsah je sada buněk, mezi kterými se má provést počítání.
2. Kritéria – podmínka zohledňovaná při výběru buněk.

Například v tomto příkladu program spočítal počet klíčových dotazů, kde počet prokliků ve vyhledávačích přesahuje sto tisíc. Výsledkem je, že vzorec vrátil číslo 3, což znamená, že existují tři taková klíčová slova.

Když už mluvíme o související funkci COUNTIFS, pak, podobně jako v předchozím příkladu, poskytuje možnost použít několik kritérií najednou. Jeho vzorec je následující:

=COUNTIFS(rozsah_podmínek1, podmínka1, [rozsah_podmínek2, podmínka2],…)

A podobně jako v předchozím případě jsou „Rozsah podmínky 1“ a „Podmínka 1“ povinné argumenty, zatímco ostatní lze vynechat, pokud to není potřeba. Maximální funkce poskytuje možnost použít až 127 rozsahů spolu s podmínkami.

Formule 6: IFERROR

Tato funkce vrátí uživatelem zadanou hodnotu, pokud při vyhodnocování vzorce dojde k chybě. Pokud je výsledná hodnota správná, nechá ji.

Syntax

Tato funkce má dva argumenty. Syntaxe je následující:

=IFERROR(hodnota;hodnota_pokud_chyba)

Popis argumentů:

1. Hodnotou je samotný vzorec, zkontrolovaný na chyby.
2. Hodnota if error je výsledek, který se objeví po zjištění chyby.

Pokud mluvíme o příkladech, pak tento vzorec zobrazí text „Chyba ve výpočtu“, pokud rozdělení není možné.

Formule 7: VLEVO

Tato funkce umožňuje vybrat požadovaný počet znaků zleva od řetězce.

Jeho syntaxe je následující:

=LEFT(text;[počet_znaků])

Možné argumenty:

1. Text – řetězec, ze kterého chcete získat konkrétní fragment.
2. Počet znaků je přímo počet znaků, které mají být extrahovány.

V tomto příkladu tedy můžete vidět, jak se tato funkce používá, abyste viděli, jak budou vypadat názvy stránek webu. Tedy zda se řetězec vejde do určitého počtu znaků nebo ne.

Formule 8: PSTR

Tato funkce umožňuje získat požadovaný počet znaků z textu, počínaje určitým znakem v účtu.

Jeho syntaxe je následující:

=MID(text,počáteční_pozice,počet_znaků).

Rozšíření argumentu:

1. Text je řetězec, který obsahuje požadovaná data.
2. Počáteční pozice je přímo pozice daného znaku, která slouží jako začátek pro extrakci textu.
3. Počet znaků – počet znaků, které má vzorec z textu extrahovat.

V praxi lze tuto funkci využít například pro zjednodušení názvů titulů odstraněním slov, která jsou na jejich začátku.

Formule 9: PROPISN

Tato funkce převádí všechna písmena obsažená v určitém řetězci na velká. Jeho syntaxe je následující:

=VYŽADOVÁNO(text)

Existuje pouze jeden argument – ​​samotný text, který bude zpracován. Můžete použít odkaz na buňku.

Formule 10: NIŽŠÍ

V podstatě inverzní funkce, která snižuje každé písmeno daného textu nebo buňky malými písmeny.

Jeho syntaxe je podobná, existuje pouze jeden argument obsahující text nebo adresu buňky.

Formule 11: HLEDAT

Tato funkce umožňuje najít požadovaný prvek mezi řadou buněk a zadat jeho polohu.

Šablona pro tento vzorec je:

=MATCH(hledaná_hodnota, vyhledávací_pole, typ_shody)

První dva argumenty jsou povinné, poslední je volitelný.

Existují tři způsoby shody:

1. Menší nebo rovno 1.
2. Přesně - 0.
3. Nejmenší hodnota, rovna nebo větší než -1.

V tomto příkladu se snažíme zjistit, po kterém z klíčových slov následuje až 900 kliknutí včetně.

Formule 12: DLSTR

Tato funkce umožňuje určit délku daného řetězce.

Jeho syntaxe je podobná předchozí:

=DLSTR(text)

Lze jej tedy použít k určení délky popisu článku při SEO propagaci webu.

Také je dobré to kombinovat s funkcí IF.

Formule 13: CONNECT

Tato funkce umožňuje vytvořit několik řádků z jednoho. Kromě toho je přípustné v argumentech specifikovat jak adresy buněk, tak samotnou hodnotu. Vzorec umožňuje zapsat až 255 prvků o celkové délce nejvýše 8192 znaků, což pro procvičení stačí.

Syntaxe je:

=CONCATENATE(text1,text2,text3);

Formule 14: PROPNACH

Tato funkce zaměňuje velká a malá písmena.

Syntaxe je velmi jednoduchá:

=PROPLAN(text)

Formule 15: TISK

Tento vzorec umožňuje z článku odstranit všechny neviditelné znaky (například zalomení řádků).

Jeho syntaxe je následující:

=PRINT(text)

Jako argument můžete zadat adresu buňky.

Závěry

To samozřejmě nejsou všechny funkce, které se v Excelu používají. Chtěli jsme přinést takové, o kterých běžný uživatel tabulek neslyšel nebo je používá jen zřídka. Statisticky se nejčastěji používají funkce pro výpočet a odvození průměrné hodnoty. Excel je ale víc než jen tabulkový procesor. V něm můžete automatizovat naprosto jakoukoli funkci. 

Opravdu doufám, že se to povedlo a naučili jste se spoustu užitečných věcí pro sebe.