5 NEJUŽITEČNĚJŠÍCH NÁSTROJŮ EXCELU · Funkci KDYŽ nalezneme na kartě Vzorce – Logické...

5 NEJUŽITEČNĚJŠÍCH

NÁSTROJŮ EXCELU

© 2017 Lenka Mikšátková www.lenkamiksatkova.cz

5 NEJUŽITEČNĚJŠÍCH NÁSTROJŮ EXCELU

Prohlášení: Tento materiál je informačním produktem. Jakékoliv šíření nebo poskytování třetím osobám bez souhlasu autorky je zakázáno a je porušením autorského

zákona, které může být stíháno. Děkuji za pochopení a respektování tohoto sdělení.

„Již mnoho let je mou velkou vášní Excel. Naplňuje mě ukazovat ostatním lidem, jak ho zvládnout, aby jim usnadňoval

práci a šetřil jim čas.“

www.lenkamiksatkova.cz

tkova.cz

5 NEJUŽITEČNĚJŠÍCH NÁSTROJŮ EXCELU Lenka Mikšátková

ZAJÍMÁ MĚ ONLINE KURZ

1) NA ÚVOD

Dnes miluji práci v Excelu. Pokaždé, když tvořím nový report, si užívám vymýšlení kombinací různých funkcí a nejraději mám moment, když vše

dodělám a ono to funguje! Když vím, že až příště bude můj klient report potřebovat, jen vyplní vstupní data a zobrazí se přesně ty údaje, které

potřebuje znát.

Než jsem se ale naučila vše, co znám dnes (a to určitě není úplně všechno, co Excel umí, je potřeba stále se učit ), často jsem propadala skepsi.

Pokaždé, když jsem nastoupila do nové práce, cítila jsem se k ničemu. Jako naprostý začátečník, který vůbec neví, která bije. Trávila jsem hodiny

a hodiny tím, abych zjistila, k čemu jaká funkce slouží, jak fungují jednotlivé nástroje, vše mi trvalo dlouho a měla jsem pocit, že za celý den jsem

ani nic pořádně neudělala.

A proto jsem napsala tento e-book. Shrnula jsem do něj 5 nástrojů, které jsou podle mého názoru úplně nejužitečnější. Velmi často se na ně zabrousí

na pracovních pohovorech a svoji běžnou práci si bez nich vůbec neumím představit. Doufám, že vám pomůžou ušetřit něco z vašeho drahocenného

času a začít pracovat efektivněji.

http://lenkamiksatkova.cz/kurz-nejuzitecnejsi-nastroje/







2) KDYŽ

KDYŽ je jedna z mých nejoblíbenějších funkcí. Zjišťuje, jestli je splněná nějaká podmínka. Pokud splněná je, Excel

něco udělá (napíše, spočítá atd.)a pokud není, udělá něco jiného. Pojďme si to vysvětlit na příkladu ze života.

PŘÍKLAD: RODIČE ŘEKNOU DÍTĚTI: „POKUD PŘINESEŠ ZE ŠKOLY JEDNIČKU, PŮJDEME SPOLU DO KINA.

POKUD NE, BUDEME DOMA A BUDEŠ SE UČIT“. CO SE STANE, POKUD DOSTANE DÍTĚ DVOJKU?

Dostal jsi jedničku?

ANO -> půjdeme do

kina NE -> budeš se učit









Napíšeme si tedy do buňky C6 číslo 2, stoupneme si do jiné buňky, např. D6 a zadáme funkci KDYŽ.

Funkci KDYŽ nalezneme na kartě Vzorce – Logické – ze seznamu vybereme KDYŽ. Poté se nám zobrazí

tabulka pro zadání (argumenty funkce).

Pro náš příklad tedy zadáme následovně:

Podmínka – >pokud je v buňce C6 napsaná jednička-> C6=1

Ano –> pokud je v buňce C6 zapsaná jednička chceme, aby Excel napsal, že půjdeme do kina

-> „Půjdeme do kina“

Ne –> pokud v buňce C6 není jednička, chceme, aby Excel napsal, že se budeš učit -> „Budeš

se učit“

Už jen odsouhlasíme zadání stiskem OK a

uvidíme výsledek. My jsme v buňce C6 neměli

zadanou jedničku a proto vyšlo, že nebude žádné

kino, ale budeme se učit

Funkce KDYŽ se dá výborně kombinovat i

s dalšími funkcemi (např. do podmínky můžeme

zadat jakoukoliv další funkci, často se používají

např. funkce A nebo NEBO), do argumentů

„ANO“ a „NE“ můžeme klidně vložit další funkci










KDYŽ (nebo opět jakoukoliv další funkci) a tím rozšířit varianty (skvělé využití např. u intervalů) atd. Více využití této

velmi užitečné funkce najdete v online kurzu Nejužitečnější nástroje Excelu.

Na co dávat pozor u funkce KDYŽ?

Tato funkce přímo vybízí k vnořování dalších funkcí (A, NEBO, HLEDAT, další KDYŽ atd.). Je tedy nutné dávat velký

pozor na správný počet a použití závorek.

3) SVYHLEDAT Funkce SVYHLEDAT (v AJ verzi Excelu VLOOKUP) umí přiřadit údaji v jedné tabulce hodnotu z jiné tabulky. Například

najde telefonní číslo podle zadaného jména, jméno zákazníka podle čísla objednávky nebo cokoliv jiného, co budete

potřebovat.

Funkce SVYHLEDAT dokáže vyhledat buď přesnou hodnotu, kterou hledáme, nebo nejbližší hodnotu. Častější je, že

hledáme přesnou hodnotu, tedy chceme v tabulce najít přesné jméno, číslo apod., proto se budeme věnovat této

možnosti.

PŘÍKLAD: MÁME TABULKU S TRŽBAMI A NÁKLADY PO JEDNOTLIVÝCH MĚSÍCÍCH A JÁ V NÍ POTŘEBUJI VYHLEDAT

TRŽBY ZA ÚNOR.

Do buňky F10 zadáme funkci SVYHLEDAT. Půjdeme na kartu

Vzorce - Vyhl. A Ref.- v seznamu najdeme SVYHLEDAT. Otevře

se nám šedivé okno pro zadání argumentů funkce. Tady zadáme

všechno, co Excel potřebuje vědět, aby nám tržby našel správně.









Do pole „Hledat“ zadáme CO má Excel hledat

v tabulce. Má tam hledat tržby. Buď tedy přímo

napíšeme „tržby“ anebo lepší způsob je, jen

kliknout do buňky D10, kde máme položku již

napsanou. Do pole „Tabulka“ zadáme, KDE, tedy

v jaké tabulce má Excel to tržby hledat.

Vybereme tedy myší celou tabulku (buňky

C6:G8). Do políčka „Sloupec“ napíšeme, kolikátý sloupec z tabulky chceme tržbám přiřadit, tedy

v kolikátém sloupci máme napsané tržby za únor. V našem případě je to třetí sloupec tabulky, proto

napíšeme číslo 3. Do posledního políčka „Typ“ se zadává logická hodnota – buď 0, to znamená, že chceme hledat podle

přesné hodnoty (ano, to chceme, Excel nám má najít přesně tržby) anebo se zde zadá číslo 1, potom by Excel vrátil nejbližší

hodnotu (to pro náš příklad nemá ale žádný význam). Nyní již jen potvrdíme stisknutím OK a už víme, za kolik jsme v tom

únoru prodali .










NA CO SI DÁT U SVYHLEDAT POZOR?

Funkce umí vyhledávat jen směrem doprava a údaj, podle kterého vyhledáváte, MUSÍ vždy být v prvním sloupci tabulky.

Nevýhodou je také, že funkce nám doplní první údaj, který najde. Pokud tedy budete vyhledávat údaj, který je v tabulce

vícekrát, najde vám jen první hodnotu.

4) ŘAZENÍ Pokud máme větší tabulku, je velmi užitečné naučit se data různě řadit, abychom neztráceli přehled. V řazení nabízí Excel opravdu hodně možností, můžeme řadit podle jednoho sloupce, ale také klidně můžeme přidat více úrovní řazení, takže se data seřadí nejdříve podle jednoho sloupce, potom podle druhého, třetího atd. Také si můžeme zvolit řazení podle barvy buňky nebo písma (to mám moc ráda, barvy, to je moje ), anebo např. podle ikony v buňce. Můžeme si dokonce i nastavit, že chceme, aby Excel bral jako rozdíl velké a malé písmeno a řadil stejná slova, která se liší právě jen velikostí prvního písmene, odděleně. A co podle mě většina lidí neví, dá se řadit nejen ve sloupcích odshora dolů, ale také zprava doleva. Anebo když si nevybereme, můžeme si nastavit svůj vlastní seznam toho, jak se naše data mají řadit. Tak si to pojďme ukázat

PŘÍKLAD: MÁME SEZNAM OKRESŮ ČR A JEDNOTLIVÉ OBCE, KTERÉ DO OKRESU SPADAJÍ, V DALŠÍCH SLOUPCÍCH

JE UVEDENÝ CELKOVÝ POČET OBYVATEL V KAŽDÉ OBCI A POTOM POČET MUŽŮ A ŽEN. MY BYCHOM TEĎ CHTĚLI

SEŘADIT TABULKU TAK, ABY OBCE BYLY PODLE ABECEDY.







Vybereme celou naši tabulku a půjdeme na kartu Data – Seřadit. (Pokud je tabulka opravdu obsáhlá, poradím vám šikovnou klávesovou zkratku. Stoupněte si do první buňky tabulky, zmáčkněte Ctrl+Shift+šipku dolů -> tím vyberete celý první sloupec, Ctrl+Shift pořád držte a teď zmáčkněte šipku doprava -> tabulka je celá vybraná ). Otevře se dialogové okno, kde v rolovacím seznamu vybereme, že chceme tabulku seřadit podle Názvu obce, řadit chceme podle hodnot v buňkách a pořadí má být A až Z.

Teď jen potvrdíme svoji volbu

tlačítkem OK a máme seřazeno

Ani to nebolelo, že?












NA CO DÁVAT POZOR U ŘAZENÍ?

Vždy se ujistěte, že jste opravdu vybrali celou tabulku. Pokud je tabulka velmi dlouhá, používejte klávesovou zkratku,

kterou jsem uvedla výše. Pokud si celou tabulku nevyberete a seřadíte jen část dat, můžete si způsobit velké zmatky .

5) Filtrování Data v tabulce už si umíme seřadit, ale co když chceme vidět jen ty buňky, které splňují nějakou podmínku? Od toho máme

filtry V tomto e-booku se budeme věnovat jen automatickému filtru, pokročilejší možnosti si necháme na příště.

AUTOMATICKÝ FILTR

Filtrovat můžeme v jednom sloupci, ale klidně i ve všech sloupcích zároveň. Nebo si můžeme vybrat, že chceme vidět jen

prázdné buňky v nějakém sloupci (třeba, kdo nám ještě nezaplatil ). Můžeme filtrovat podle barvy buňky nebo písma.

Filtrování se dá nastavit také jen podle toho, jestli daná buňka obsahuje část textu, podle její hodnoty, nebo podle toho,

jestli je ta hodnota větší (menší, stejná atd.) než jiná hodnota. Prostě můžeme si s filtry vyhrát, jak chceme. Jak si tedy ten

automatický filtr vložíme?

PŘÍKLAD: BUDEME VYCHÁZET ZE STEJNÉ TABULKY JAKO PŘI ŘAZENÍ. ROZHODNEME SE, ŽE CHCEME VIDĚT JEN

TY OBCE, KDE JE POČET OBYVATEL VĚTŠÍ NEŽ 10 000.







Postavíme se do tabulky a půjdeme na kartu

Data - Filtr, po stisknutí tlačítka se nám u

buněk v hlavičce tabulky zobrazí takové malé

šipečky, které můžeme rozkliknout a v nich si

zvolit, co chceme a co nechceme vidět. My

chceme zobrazit jen obce, které mají počet

obyvatel větší než 10 000, rozklikneme tedy

šipku u sloupce Počet obyvatel – celkem,

vybereme Filtry čísel – Větší než, do okna,

které se nám otevřelo, doplníme číslo 10000 a

stiskneme OK.












A máme to . Kdybyste chtěli, můžete si filtr vložit i do dalšího sloupce, např. jen pro určitý počet mužů apod. Všimněte

si, že po tom, co jsme nastavili filtr, se čísla řádků zbarvily namodro. Podle toho vždy poznáte, že pracujete se

zafiltrovanými daty (a někdy může pořádně

potrápit, když si toho nevšimnete . Když už

filtr nechcete, jen na kartě Data znovu

stiskněte tlačítko Filtr a on zmizí.

NA CO DÁVAT POZOR U FILTROVÁNÍ?

Pokud počítáte s daty, která jsou zafiltrovaná,

tak nemůžete používat běžné funkce, protože

ty neumí rozpoznat, že máte zapnutý nějaký

filtr a počítají pořád se všemi daty. Vždy si tedy

zkontrolujte, jestli máte všechny filtry vypnuté. Pokud potřebujete počítat se zafiltrovanými daty, použijte funkci

SUBTOTAL.








6) KONTINGENČNÍ TABULKA Kontingenční tabulka umí souhrnně zobrazit opakující se údaje. To znamená, že už nikdy více nebudete muset ručně sčítat

např. objednávky od jednotlivých zákazníků. Jen si zvolíte, že v řádku bude zákazník, ve sloupci součet jeho objednávek a

voilá, je to tam Pojďme si to tedy ukázat, bude se vám to určitě líbit

PŘÍKLAD: VRÁTÍME SE TEĎ K TABULCE, NA KTERÉ JSME SE UČILI ŘADIT A FILTROVAT– K TABULCE, KDE JSOU

UVEDENY OKRESY, OBCE A POČTY OBYVATEL. BUDEME OPĚT CHTÍT ZJISTIT POČET OBYVATEL CELKEM I PODLE

POHLAVÍ PODLE OKRESŮ.

Jak budeme postupovat…Vybereme si myší celou oblast tabulky, půjdeme na kartu Vložení a

vybereme Kontingenční tabulka.

Pozor, je důležité, aby všechny sloupce v tabulce byly pojmenované. Pokud nějaký pojmenovaný nebude,

kontingenční tabulka nepůjde vložit!








Objevilo se nám okno pro vytvoření kontingenční tabulky. Nejdříve

zkontrolujeme, jestli je správně vybraná oblast, dále si můžeme vybrat, jestli

kontingenční tabulku chceme vložit na nový list, nebo někam do již existujícího

listu. My se např. rozhodneme, že chceme vložit novou kontingenční tabulku

do listu, který už máme (jmenuje se Kontingenční tabulka) a to do buňky L5,

nyní můžeme potvrdit OK.








Teď se nám zobrazilo okno, které děsí spoustu lidí, ale uvidíte, jak je

jednoduché s ním pracovat. Vlevo se bude ukazovat, jak zrovna

vypadá naše kontingenční tabulka a vpravo si můžeme volit, jaký

sloupec z původní tabulky se má kde zobrazit. Pro náš příklad

chceme, aby Okres byl v řádku a v polích pro Hodnoty aby byly

uvedeny počty obyvatel celkem i podle pohlaví. Jak tam ty jednotlivá

pole dostaneme? Jednoduše levým tlačítkem myši kliknu na nápis

Okres a tahem ho přemístím do bílého pole pod nápisem Řádky.

Úplně stejně přemístím i nápisy Počet obyvatel – celkem, Počet

obyvatel – muži a Počet obyvatel – ženy, ale tentokrát do pole pod

nápisem Hodnoty.

Vidíte? Excel sám rovnou vložil součty

pro jednotlivá pole (sloupce se jmenují

Součet z Počet obyvatel….). Součet se

dá samozřejmě změnit i na jinou

funkci.









Uděláme to tak, že pro každý sloupec si rozklikneme šipku u jeho názvu v bílém poli pod nápisem Hodnoty – zvolíme

Nastavení polí hodnot a po otevření okna vybereme, kterou funkci chceme použít, např. průměr. Rovnou si zde můžeme

nastavit i formát čísla. Odsouhlasíme a máme hotovo.











Tak co říkáte? Není to úžasně rychlé a přehledné? Že ta kontingenční tabulka

není tak hrozná, jak jste si mysleli? ;-)

NA CO SI DÁT U KONTINGENČNÍ TABULKY POZOR?

Pokud vkládáte kontingenční tabulku, ujistěte se, že všechny sloupce tabulky

máte pojmenované. Pokud máte v tabulce např. prázdný sloupec, také musí mít

nějaký název, jinak nepůjde kontingenční tabulku vložit.

A pokud jste změnili vstupní data, nezapomeňte si kontingenční tabulku aktualizovat ;-) Vřele doporučuji původní data

vložit jako excelovou tabulku (Ctrl+T). Potom po vložení dalších dat nemusíte upravovat zdrojová data kontingenční

tabulky.








7) ZÁVĚR Skvěle, naučili jste se všech 5 podle mě nejužitečnějších nástrojů v Excelu. Věřím, že vám jejich znalost pomůže jak u pracovního

pohovoru, tak při běžné práci. Jak Vám to šlo? Bylo Vám všechno jasné? Pokud se potřebujete na něco zeptat, napište mi, udělám,

co bude v mých silách.

Pokud cítíte, že e-book je sice fajn, ale spíš byste potřebovali všechno vidět na vlastní oči, podívejte se na můj online kurz určený

pro mírně a středně pokročilé uživatele. Najdete v něm informace z tohoto e-booku, ale i dalších více než 50 videí s dalšími funkcemi

a skvělými nástroji včetně příkladů ke stažení.

Můžete se ke mně také přidat na Facebooku, kde pravidelně zveřejňuji zajímavé tipy

Moc vám děkuji, že jste si tento můj e-book přečetli a doufám, že vám pomohl získat novou práci nebo ušetřit čas. Budu moc ráda,

pokud mi dáte vědět





http://lenkamiksatkova.cz/kontakt/


https://www.facebook.com/MameRadiExcel/?ref=aymt_homepage_panel


Date post:	27-Dec-2019
Category:	Documents
Upload:	others
View:	3 times
Download:	0 times

5 NEJUŽITEČNĚJŠÍCH NÁSTROJŮ EXCELU · Funkci KDYŽ nalezneme na kartě Vzorce – Logické...

Documents