Výuka a školení Excelu Výuka a školení Excelu Výuka a školení Excelu
Výuka a školení Excelu Výuka a školení Excelu

neděle 18. listopadu 2012

Jak se zbavit duplicit

Příklad

Mám tabulku, ve které jsou některé řádky dvakrát. Vadí mě to, a chci je buď označit nebo odstranit. Je několik řešení, jak toho dosáhnout - a každé se hodí v jiné situaci.

Označení duplicit

Pokud nechci duplicity odstranit, ale jen zvýraznit, použiji funkci KDYŽ/IF (předtím ovšem musím data seřadit).
Zápis funkce:
Výsledek:

Odebrat stejné

Excel má zvláštní funkci, která odebere duplicitní hodnoty - její nevýhoda však je, že je smaže natrvalo - už se k nim pak nelze vrátit.









Kontingenční tabulka

Je také možné použít kontingenční tabulku. Výhodou je, že zůstane původní i nový seznam - tedy s duplicitami i bez duplicit.

pondělí 12. listopadu 2012

Použití kontingenční tabulky pro dynamické řazení

Příklad

Potřebuji seřadit data. A potřebuji to udělat tak, aby se veškeré změny, které provedu v původních datech, projevily i v seřazených datech. Jinými slovy budu mít jedna data neseřazená a druhá data seřazená, dynamicky upravovaná podle měnících se hodnot prvních dat.
Např. v této tabulce jsou časy závodníků po jednotlivých kolech. Potřebuji průběžně sledovat jejich pořadí - současně s tím, jak přibývají časy za jednotlivá kola v tabulce.








Návod

  • Přidám součtový sloupec (pokud je třeba)
  • Přidám kontingenční tabulku (v tomto příkladu v řádkových polích budou jména závodníků a v polích hodnot pak součet výsledného času - sečtený však pouze z jedné hodnoty)
  • Seřadím tabulku podle součtového času
  • Pak už jen aktualizuji kontingenční tabulku po každé změně vstupních dat
Řešení spolu se zadáním je tady:
https://www.dropbox.com/s/qr1p39p4in5bfa9/vysledkova_listina_kontingencni_tabulka.xlsx

pondělí 5. listopadu 2012

Archiv Sweb

Ruším staré stránky na sweb.cz, a protože je ještě občas někdo navštěvuje, přesunuji je sem. Nicméně pokud se Vám bude zdát obsah zastaralý, neuspořádaný a s chybami, tak to není náhoda :)
  • Rozcvička
    • Úvodní tabulka

      • Zadání:
      • Vytvořte tuto jednoduchou tabulku. Zařiďte, aby se hodnoty ve sloupci "Rozdíl" počítaly automaticky a stejně tak hodnoty v řádku "Celkem".
      • Postup
      • Řešení
    • Absolutní odkazy
      • Vytvořte vzorovou tabulku. Údaje v šedých buňkách se počítají automaticky.
      • Zadání:
      • Postup
      • Řešení
    • Funkce když, podmíněné formátování, výpočty, formáty
      • Vytvořte tabulku. Údaje ve sloupci "Výsledek hospodaření" a v řádku "Celkem" se počítají automaticky. Ve sloupci "Zisk nebo ztráta?" je uvedeno "Zisk", pokud je výsledek hospodaření větší než nula a "Ztráta" v případě opačném. Záporné údaje ve sloupci "Výsledek hospodaření" jsou červeně napsané a orámované, je-li hodnota menší než nula. Toto vše se dělá automaticky. Vytvořte graf a do jeho pozadí vložte libovolný obrázek.
      • Zadani:
      • Řešení
    • Filtry, řazení
      • V této tabulce nejprve použijte automatický filtr a vyfiltrujte všechny Alfy Romeo. Pak zase zobrazte všechny hodnoty tabulky a vyfiltrujte pouze stříbrné Fiaty Coupe s levnější než 100 000. (Měly by být dva). Filtr zrušte. 
      • V dalším kroku seřaďte auta podle značky auta podle abecedy vzestupně. V případě, že budou dvě auta stejné značky, bude se řadit vzestupně podle konkrétního modelu. A kdyby existovala dvě auta stejné značky i modelu, budou srovnaná podle počtu najetých kilometrů od toho, s největším počtem km po to s nejmenším počtem.
      • tabulka
      • postup
    • Import, "rozsekávání" dat do sloupců
      • Cvičný soubor
      • Máte k dispozici textový soubor. Uložte si ho do svého počítače a jeho obsah převeďte do Excelu. Zajistěte, aby v jednom sloupci bylo Jméno, ve druhém Linka a ve třetím Pracoviště. Neprovádějte to ručně. (Pokud byste měli problém se stažením, klidněte na slovo "tabulka" pravým tlačítkem a vyberte možnost "Uložit odkaz" nebo něco obdobného.
      • postup
  • Pokročilejší příkládky

    • Vyplňovací funkce
      • V podkladové tabulce máte seznam prodejů. Zajistěte, aby z vedlejší malé tabulky (nejprve horní) byly do hlavní tabulky doplněny ceny za kus a následně spočítejte ceny celkem. Pak vše smažte a totéž proveďte znovu s využitím druhé malé tabulky.
      • Podkladová tabulka
    • Práce se spojnicí grafu
      • Podkladová tabulka
      • V tomto příkladě jsou pro určitou firmu vypsané výdaje za reklamu v minulosti a příslušné prodeje v kusech. Vaším úkolem je odhadnout, kolik by se přibližně prodalo při výdajích 600 000 Kč. Nejprve to odhadněte pouze vizuálně prostřednictvím grafu a následně podle spojnice grafu. Pak zobrazte příslušnou rovnici spojnice trendu, zobrazte rovnici závislosti a přesně spočtěte předpokládanou hodnotu prodejů. Úloha má více řešení podle toho, jakou spojnici trendu si vyberete.
      • Řešení
    • Komplexní příklad - faktura
    •  Vytvořte stejnou fakturu, jaká je uvedena. Zařiďte, aby ten, kdo ji bude vyplňovat, vyplňoval pouze část, která je žlutě, zbytek se dopočte sám. Zařiďte, aby nemohl nic jiného vyplnit nebo přepsat, ani kdyby chtěl. Zařiďte, aby do sloupce Počet kusů a Cena bez DPH nemohl vyplnit nic jiného než číslo. Vytvořte tlačítko určené k mazání žluté oblasti.
      • Kontingenční tabulka
        • tabulka
        • Stáhněte tabulku. Pomocí kontingenčí tabulky zjistěte, kolik je celkový dovoz z jednotlivých kontinentů a z jednotlivých zemí.Pak vytvořte kontingenční graf, ve kterém budou zobrazeny celkové hodnoty za celé kontinenty.
      • Řešitel
        • Vypočtěte tento příklad:
          Firma vyrábí hračky – autíčka a vláčky.
          K výrobě potřebuje pouze dřevo a plastová kolečka. Na jedno autíčko spotřebuje 4 kolečka a 0,8 metru dřeva
          Na jeden vláček spotřebuje 6 koleček a 0,4 metru dřeva.
          Na jednom vláčku vydělá 180 Kč a na jednom autíčku 120 Kč.
          Pro příští týden má k dispozici 1000 koleček a 200 metrů dřevěného materiálu.
          Co má firma vyrábět, aby maximalizovala zisk?
        • tabulka
      • Funkce SUBTOTAL
        • tabulka
        • Vytvořte sami tuto tabulku. Při použití automatického filtru se do buňky F1 zobrazuje pouze součet cen aktuálně vyfiltrovaných aut.
      • Funkce SUMIF
        • tabulka
        • Vytvořte uvedenou tabulku. V horní části jsou různé položky tržeb za pobočky určité firmy. V dolní části se pak mají sčítat sumy za všechny pobočky.
      • Textové a časové funkce
        • tabulka
        • Zadání i řešení těchto funkcí je obsaženo v souboru.
      • Import dat z externí databáze
        • databáze
        • Otevřete nový dokument Excelu. Pak do něj z přiložené databáze importujte seznam zaměstnanců i s názvem jejich oddělení. V tabulce musí být uvedeno jméno zaměstnance, příjmení zaměstnance a slovy vypsaný název oddělení.
      • Automatický souhrn
        • tabulka
        • Pro následující tabulku vytvořte automatický souhrn pro jednotlivé mezisoučty.
      • Funkce KDYŽ
        • tabulka
        • Vytvořte takovouto tabulku. Údaje ve sloupcích A, B, C opište, sloupec D se bude z těchto sloupců odvozovat - bude v něm napsáno buďto Ztráta nebo Zisk a v případě ztráty bude buňka žlutá.
      • Hledání řešení
        • tabulka
        • Prostudujte tuto tabulku. Zjistěte, kolik návštěvníků musí přijít, abychom utržili 500 000 Kč. (Mělo by vyjít 4347.826, tedy 4348 diváků)
      • Správce scénářů
        • tabulka
        • Jste pořadatelem koncertu pod širým nebem. Víte, že akce se bude vyvíjet podle toho, jaké bude počasí. Máte připravené tři situace:
          Bude-li hezké počasí, přijde při vstupném 300 Kč 6000 lidí a náklady na pořádání budou 1 000 000 Kč.
          Bude-li horší počasí, přijde při stejném vstupném pouze 3000 lidí, náklady na pořádání budou 700 000 Kč.
          Bude-li opravdu ošklivé počasí, přijde jen 1200 lidí, náklady na pořádání budou 120 000, ale budeme muset snížit vstupné na 200 Kč.
          Zachyťte tyto situace jako scénáře.
          Zde je výsledek.
      • Slučování dat z více sešitů
        • Stáhněte tyto čtyři soubory. V těch, která se nazývají po městech, jsou data, která Vám byla zaslána z poboček. Vy pak máte do posledního z nich dostat součty z těchto tabulek.
          Karviná
          Opava
          Ostrava
          Prodeje dohromady

    sobota 3. listopadu 2012

    Podmíněné formátování podle hodnot jiné buňky

    Příklad

    Potřebuji použít podmíněné formátování. Ale ne tak, abych formátoval buňku podle její hodnoty - potřebuji ji formátovat podle hodnoty jiné buňky. Např. buňku B1 chci formátovat podle hodnoty buňky A1.
    Tímto příspěvkem reaguji na několik dotazů v diskusích. Také se tímto omlouvám všem, kterým jsem někdy tvrdil, že tohle nejde dělat - naučil jsem se to až nedávno :)

    Návod

    1. Označím buňku, která se má podmíněně formátovat.
    2. Kliknu na Podmíněné formátování a vyberu "Nové pravidlo...".
    3. Vyberu "Určit buňky k formátování pomocí vzorce".
    4. V následujícím dialogu nastavím buňku, podle které se řídím, a pravidlo podle kterého se formát aplikuje. Následně nastavím požadovaný formát.
    Například tady říkám, že buňka se bude zabarvovat modře v případě, že bude mít buňka A1 hodnotu větší než 5.













    V některých případech toto ale potřebujete nastavit pro celý sloupec. Jak to tedy udělat, abychom nemuseli řádek po řádku formátování nastavovat?
    1. Vytvořím pravidlo jak je to popsáno výše - jen odstraním absolutní odkazy. Tedy z "=$A$1většítko5" udělám "=A1většítko5" (protože v editačním systému Blogger nejde napsat znamenko vetsi nebo mensi, obchazim to slovem vetsitko - doufam, ze to neubere na srozumitelnosti...)
    2. Jdu na "Podmíněné formátování" a "Správa pravidel...".
    3. Nastavím vpravo oblast, pro kterou to platí.










    Poznámka - pokud chci například formátovat celou řádku podle jednoho sloupce, odeberu znaménko pro absolutní odkaz pouze u písmenka - bude to tedy např. A$1.


    Podmíněné formátování podle hodnot jiné buňky

    Příklad

    Potřebuji použít podmíněné formátování. Ale ne tak, abych formátoval buňku podle její hodnoty - potřebuji ji formátovat podle hodnoty jiné buňky. Např. buňku B1 chci formátovat podle hodnoty buňky A1.
    Tímto příspěvkem reaguji na několik dotazů v diskusích. Také se tímto omlouvám všem, kterým jsem někdy tvrdil, že tohle nejde dělat - naučil jsem se to až nedávno :)

    Návod

    1. Označím buňku, která se má podmíněně formátovat.
    2. Kliknu na Podmíněné formátování a vyberu "Nové pravidlo...".
    3. Vyberu "Určit buňky k formátování pomocí vzorce".
    4. V následujícím dialogu nastavím buňku, podle které se řídím, a pravidlo podle kterého se formát aplikuje. Následně nastavím požadovaný formát.
    Například tady říkám, že buňka se bude zabarvovat modře v případě, že bude mít buňka A1 hodnotu větší než 5.













    V některých případech toto ale potřebujete nastavit pro celý sloupec. Jak to tedy udělat, abychom nemuseli řádek po řádku formátování nastavovat?
    1. Vytvořím pravidlo jak je to popsáno výše - jen odstraním absolutní odkazy. Tedy z "=$A$1většítko5" udělám "=A1většítko5" (protože v editačním systému Blogger nejde napsat znamenko vetsi nebo mensi, obchazim to slovem vetsitko - doufam, ze to neubere na srozumitelnosti...)
    2. Jdu na "Podmíněné formátování" a "Správa pravidel...".
    3. Nastavím vpravo oblast, pro kterou to platí.







    Poznámka - pokud chci například formátovat celou řádku podle jednoho sloupce, odeberu znaménko pro absolutní odkaz pouze u písmenka - bude to tedy např. A$1.


    Funkce NEBO/OR

    Příklad

    Potřebuji vyhodnotit, jestli je splněna alespoň některá z více podmínek. Např. chci ověřit, jestli je alespoň jedno z čísel větší než 10 - přičemž čísla jsou v buňkách A1, A2, A3, A4 a A5.

    Návod

    Vzorec vypadá takto
    =NEBO(A1>5;A2>5;A3>5;A2>5;A2>5)
    (Podmínky samozřejmě mohou být i každá jiná.)
    Poznámky

    • Funkce NEBO/OR se často spojuje s funkcí KDYŽ/IF
    • Funkce NEOB/OR je logickým opakem funkce A/AND


    Prezentace v PowerPointu - na co nezapomenout

    Existuje milion článků o tom, jak vytvořit a odprezentovat správnou prezentaci. V tomto příspěvku popíši body, na které byste neměli zapomenout - přičemž přiznávám, že na většinu z nich jsem já někdy zapomněl.
    V tomto článku se nevěnuji tomu, co a jak máte říkat - věnuji se jen technické přípravě na prezentaci a PowerPointu.

    Příprava na prezentaci

    • Přijďte včas

    Je pravděpodobné, že budete muset řešit technické problémy, že budete muset hledat místnost nebo lidi se kterými máte schůzku, že se zdržíte na recepci. Přijít na prezentaci pozdě Vás znevýhodní ještě než otevřete pusu.

    • Nezapomeňte na doklad totožnosti

    Když prezentujete v nějaké firmě, obvykle potřebujete na recepci ukázat občanku

    • Mějte prezentaci na více místech

    Nejlépe ve vlastním notebooku, na flash paměti nebo telefonu a ještě na internetu ke stažení. Je dost pravděpodobné, že nepůjde připojit notebook k projektoru, že nepůjde připojit flash paměť kvůli bezpečnostním opatřením, že nepůjde internet a že se toho stane i více najednou.

    • Kabely - nenápadní zabijáci

    Odstraňte nebo přilepte páskou k zemi kabely, které leží v místech, kde chcete stát při prezentaci. Je sice pravda, že Vaše efektní zakopnutí, stržení počítače a zápěstí naražené o zem přitáhne dostatek pozornosti a diváky probudí i pobaví, není to ale moc příjemné.

    • Pití

    Během prezentace popíjejte nějaké pití, budete mluvit srozumitelněji a vydržíte déle. Nepijte chlazené nápoje, ty Vám spíše uškodí

    PowerPoint

    • Nemějte na slidu více než 5 odrážek

    A pokud možno ani více než 3 odrážky. Vím, že v prezentacích chceme sdělit co nejvíce informací a všichni máme tendenci je přeplňovat, ale přecpaným slidem nesdělíte nic, uspíte účastníky a vypadáte jako suchaři. Pamatujte si, že mít hodně slidů se stručným textem je mnohem lepší než mít málo slidů s dlouhým textem.

    • Nevykecávejte se v textech

    Vykecávejte se raději mluvením při prezentaci samotné, když už. Opusťte představu, že všechno co chcete prezentací sdělit, musíte mít napsané na slidu.

    • Hlídejte kontrast barev

    Barvy na stránkách mějte dostatečně kontrastní, aby bylo všechno dobře vidět. Počítejte s tím, že na projektoru všechno vypadá jinak a že to, co je na Vašem monitoru dobře vidět, může být při promítání málo kontrastní.
    Pozor na kontrast červené a černé - je často špatně rozpoznatelný.

    • Pište velká písmena

    Nepoužívejte menší písmo než velikost 32. Riskujete, že lidé sedící dál nebo s horším zrakem text neuvidí.

    • Používejte vtipy

    Je-li to alespoň trochu možné, použijte přinejmenším na začátku a na konci nějaké odlehčení - kreslený vtip, obrázek, citát. Posluchači Vám budou velmi vděční.
    Mimochodem, tenhle je fakt dobrej :)
    http://dilbert.com/dyn/str_strip/000000000/00000000/0000000/000000/00000/6000/800/6845/6845.strip.gif

    • Opakujte základní fakta, sumarizujte

    Počítejte s tím, že velká část posluchačů poslouchá velkou část Vaší prezentace nesoustředěně a složitější věci prostě nepochopí. Proto důležité věci opakujte.