1 / 91

Excel 2007

Excel 2007. pro začátečníky (eGON) Lekce č.3. Soubory ke stažení. Stáhněte soubor excel2007c.zip Rozbalením získáte soubory: auta, cisilka, dan, maxmin, když, opakovat, preklad, prevraceni, skalarni, souhrn, vyhledani, zasoby Tyto soubory budou potřeba v této lekci. Převrácení tabulky.

manjit
Télécharger la présentation

Excel 2007

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel 2007 pro začátečníky (eGON) Lekce č.3

  2. Soubory ke stažení Stáhněte soubor excel2007c.zip Rozbalením získáte soubory: auta, cisilka, dan, maxmin, když, opakovat, preklad, prevraceni, skalarni, souhrn, vyhledani, zasoby Tyto soubory budou potřeba v této lekci

  3. Převrácení tabulky Příklad: Máme takovouto tabulku (viz soubor prevraceni.xls) • A chceme z ní udělat následující tabulku:

  4. Převrácení tabulky Smyslem je tedy změnit tabulku tak, aby se ze sloupečků staly řádky a naopak. Postup: Označíme buňky původní tabulky. Zkopírujeme (např. Ctrl C) Klikneme na místo, kam se má vložit změněná tabulka Domů - Vložit jinak - zaškrtneme Transponovat

  5. Použití absolutních odkazů Příklad: Potřebuji v této tabulce vyplnit sloupeček s daní. (viz soubor dan.xls) • Tedy u lízátek vypočítat 20% z 20 Kč u oplatky 20% z 30 Kč a u bonbonů 20% z 15 Kč.

  6. Použití absolutních odkazů Lízátka zvládnu snadno. Do buňky C2 napíši vzorec =B2*C7, v buňce se zobrazí správně spočítaná hodnota 4 Kč.

  7. Použití absolutních odkazů Pak se ale pokusím roztáhnout vzorec i na oplatku a bonbony, a nastane problém - do těchto dvou řádků se zobrazila chyba nebo žádná hodnota. Proč se to tak stalo?Protože Excel při roztažení vzorečku =B2*C7 připočte v každém dalším řádku ke každému číslu jedničku. Takže oplatky mají vzoreček =B3*C8, bonbony =B4*C9.

  8. Použití absolutních odkazů Sazba daně je ale pořád v buňce C7, takže vzoreček pro výpočet daně si "sahá" na špatné, prázdné, buňky. Jak to zařídit, abych se problému vyhnul?Vrátím se k situaci, kdy mám v první buňce správný výpočet s tímto vzorečkem:=B2*C7

  9. Použití absolutních odkazů Potřebuji Excelu říct něco jako:„Tady je jeden správný vzoreček, který za chvilku roztáhnu na další řádky. Potřebuji ale, aby se při roztažení měnilo pouze číslo řádku s cenou bez daně, a neměnilo se číslo řádku se sazbou daně.„ Řeknu mu to tak, že před části vzorečku, které se nemají měnit, zapíšu znak "$". Ten v tomto kontextu neoznačuje peníze, ale právě jen pevnou, absolutní část odkazu. Náš vzoreček tedy bude vypadat takto:=B2*$C$7

  10. Použití absolutních odkazů Symboly mohu buď vypsat, nebo si kliknu ve vzorečku na příslušnou část vzorce, a stisknu klávesu F4. Symboly se mi doplní samy. Pak zmáčknu Enter a tím ukončím zápis do buňky.

  11. Použití absolutních odkazů Pak už jen roztáhnu a tabulka je správně vyplněná. Samozřejmě, že takto malou tabulku je možné správně "zavzorečkovat" i tím, že napíši každý řádek zvlášť. Na větších tabulkách je však použití absolutních odkazů nezbytné.

  12. Pojmenování skupiny buněk Někdy by se mi při práci s Excelem hodilo pojmenovat určitou skupinu buněk (oblast). Pak bych při výpočtech odkazoval na tuto skupinu a nemusel bych vždy znovu vypisovat všechny buňky, které do ní patří. Např. u této tabulky se jedná o skupinu buněk A2 až A6 (v praxi se tato funkcionalita používá spíše u složitějších oblastí). Viz soubor „cisilka.xls“

  13. Pojmenování skupiny buněk Pojmenování buňky mi umožní např. při sčítání buněk A2 až A6 napsat vzorec "=SUMA(cisilka)" místo složitějšího "=SUMA(A2:A6)". Jistě jste správně odvodili, že "cisilka" je název oblasti.

  14. Pojmenování skupiny buněk Označím oblast, o kterou jde, v mém případě A2 až A6. Kliknu na „Vzorce“a dále na „Definovat název“ a ještě jednou na „Definovat název“.

  15. Pojmenování skupiny buněk Nyní tedy kdekoliv budu pracovat s oblastí A2 až A6, mohu použít výraz "cisilka". Např. "=PRŮMĚR(cisilka)" nebo "=MAX(cisilka)" atd. Také si mohu všimnout, že při označení příslušné oblasti se mi v levé horní části obrazovky zobrazí její jméno.

  16. Funkce Souhrn Příklad: V následující tabulce potřebuji posčítat mzdové náklady dle oddělení. Bylo by možné provést to pomocí kontingenční tabulky, v tomto případě ale chceme využít funkcionalitu "Souhrn". Viz soubor „souhrn.xls“

  17. Funkce Souhrn Protože budeme hledat součty mzdových nákladů za jednotlivá oddělení, začneme tím, že dle oddělení seřadíme řádky.Po seřazení vypadá tabulka takto:

  18. Funkce Souhrn Nyní klikneme myší do jakékoliv buňky tabulky a klikneme na "Data" a "Souhrn"

  19. Funkce Souhrn V předchozím okně nemusíme nic měnit. V prvním řádku sdělujeme, že chceme sčítat po odděleních. V druhém řádku sdělujeme, že chceme opravdu sčítat - tedy použít funkci "Součet". A zaškrtnutím "Mzdové náklady" říkáme, že chceme sčítat právě tento sloupeček.

  20. Funkce Souhrn Výsledek je zde:

  21. Skalární součin Příklad: V následující tabulce je seznam položek. U každé z nich je cena za kus a počet kusů. Potřebuji do buňky C15 dostat celkovou cenu za všechny položky. Viz soubor „skalarni.xls“

  22. Skalární součin Mohli bychom do třetího sloupečku vynásobit vždy cenu položky a počet kusů a dole pak výsledky spočítat. My ale využijeme funkci „SOUČIN.SKALÁRNÍ“ Do buňky C15 tedy vepíšeme funkci v této syntaxi: = SOUČIN.SKALÁRNÍ(B2:B13;C2:C13)

  23. Skalární součin Tedy nejprve název funkce a dále středníkem oddělené dva sloupečky - skalární součin tyto sloupečky vzájemně pronásobí a výsledek sečte.Výsledek vypadá takto:

  24. Funkce OPAKOVAT Příklad: Mám tabulku s tržbami za jednotlivé dny. (viz soubor „opakovat.xls“) Potřebuji graficky lépe zdůraznit, ve kterých dnech bylo dosaženo kterých tržeb - tím, že ve vedlejším sloupečku zobrazím odpovídající počet čárek nebo jiných symbolů. Asi takto:

  25. Funkce OPAKOVAT Do buňky C2 ji napíši takto:=OPAKOVAT("i";B2/1000) "i" proto, že právě tento symbol chci opakovat "B2" proto, že v této buňce je uveden počet požadovaného opakování"/1000" proto, že nechci mít řady příliš dlouhé - proto si je zkrátím tisíckrát.

  26. Funkce OPAKOVAT Pokud bych chtěl funkci zapisovat prostřednictvím dialogového formuláře, vyplním jej takto:

  27. Funkce OPAKOVAT Pak už jen roztáhnu na další řádky. Videotutoriál – zde Výsledek:

  28. Generování náhodných čísel pomocí funkce RANDBETWEEN Příklad: Potřebuji vygenerovat řadu náhodných čísel. Např. pro účely testování, školení, ukázky atd. Využiji funkci RANDBETWEEN. Tato funkce generuje náhodná čísla v rozsahu, který si stanovíme v jejích dvou parametrech..Např. při zápisu =RANDBETWEEN(1;5) jsou náhodně generována čísla 1,2,3,4 nebo 5.

  29. Generování náhodných čísel pomocí funkce RANDBETWEEN Jsou tedy generována čísla bez desetinných míst. Pokud desetinná čísla potřebuji, využiji běžné dělení nebo násobení výsledku. Tuto funkci si nebudeme plést s funkcí RAND, která generuje čísla od nuly do jedné. Funkce RANDBETWEEN je dostupná až od verze Excelu 2003 Funkce RANDBETWEEN není přeložena do češtiny

  30. Concatenate aneb spojování obsahu buněk Příklad: Potřebuji spojit obsahy dvou buněk. Např. v buňce A1 je "Prší prší" a v buňce A2 "jen se leje". V buňce A3 chci mít "Prší prší jen se leje".

  31. Concatenate aneb spojování obsahu buněk Do buňky A3 napíšu tuto funkci:=CONCATENATE(A1;A2)a je to. Mohu si však všimnout, že výsledek je "Prší pršíjen se leje" bez mezery mezi "prší" a "jen".Abych to spravil, doplním mezeru ohraničenou uvozovkami a zápis bude vypadat takto:=CONCATENATE(A1;" ";A2)

  32. Concatenate aneb spojování obsahu buněk Pokud nechci funkci natvrdo psát, ale jen vyplňovat formulář, vyplním ho takto:

  33. Concatenate aneb spojování obsahu buněk Místo funkce Concatenate mohu použít také zápis pro spojení textu - pak bych do buňky A3 napsal: =A1&A2respektive =A1&" "&A2Výsledek je stejný.

  34. Použití funkce KDYŽ Příklad: Potřebuji, aby se v této tabulce do posledního sloupečku doplnilo slovo "Zisk" nebo slovo "Ztráta" podle příslušného řádku. Viz soubor „kdyz.xls“

  35. Použití funkce KDYŽ Do buňky D2 napíši =KDYŽ(B2>C2;"Ztráta";"Zisk")

  36. Použití funkce KDYŽ Pokud nechci funkci vypisovat, mohu kliknout myší do buňky D2 a kliknout na Vložit - Funkce. Vyberu funkci Když a následující okno vyplním takto:

  37. Zobrazení dnešního data Příklad: Potřebuji mít v buňce vypsané dnešní datum, který se bude aktualizovat podle toho, kolikátého právě je. Vyřeším s pomocí funkce DNES: =DNES() Pokud bych chtěl zobrazit například zítřejší datum, zapíši funkci takto:=DNES()+1

  38. Propojení dvou listů Příklad: Potřebuji na jednom listu počítat s hodnotami jiného listu. Např. na listu List2 mám v buňce A1 hodnotu 2, v buňce A2 hodnotu 3, na listu List1 je chci sečíst a dostat číslo 5 v buňce A1.

  39. Propojení dvou listů Začnu na listu List1 v buňce A1.Napíšu "=".Kliknu na ouško listu „List2". Zkontrolujte - do řádku vzorců nahoře se vpisuje zápis včetně adresy listu

  40. Propojení dvou listů V druhém listu kliknu na buňku A1, ve které je jedna ze sčítaných hodnot. Nyní stiskněte "+" a klikněte na druhou buňku. Tím je zápis vzorečku hotov. Stiskněte Enter.Tím se vrátíte na první stránku a jste hotovi.

  41. Zaokrouhlování Potřebuji zaokrouhlit číslo.Např. číslo 1234,5678 v buňce A1 potřebuji zaokrouhlit do buňky A2. Do buňky A2 mohu napsat následující vzorce podle toho, jak přesně chci číslo zaokrouhlit.

  42. Zaokrouhlení na jedno desetinné číslo Vzoreček: =ZAOKROUHLIT(A1;1) Výsledek: 1234,6

  43. Zaokrouhlení na desítky Vzoreček:  =ZAOKROUHLIT(A1;-1) Výsledek: 1230 Druhý parametr funkce tedy říká, na kolik čísel napravo od desetinné čárky se bude zaokrouhlovat. V případě, že jde o záporné číslo, zaokrouhluje se "doleva" (na desítky), v případě, že je v parametru kladné číslo, zaokrouhluje se na desetinná místa.

  44. Zaokrouhlit nahoru Vzoreček: =ROUNDUP(A1;1) Výsledek: 1234,6Při takovémto zaokrouhlení se zaokrouhluje nahoru bez ohledu na matematická pravidla zaokrouhlování. Druhý parametr funkce (druhé číslo v závorce, v našem případě "1") říká, stejně jako u předchozích příkladů, na kolik desetinných míst se má zaokrouhlovat.

  45. Zaokrouhlit dolů Vzoreček: =ROUNDDOWN(A1;1) Výsledek: 1234,5Při takovémto zaokrouhlení se zaokrouhluje dolů bez ohledu na matematická pravidla zaokrouhlování. Druhý parametr funkce (druhé číslo v závorce, v našem případě "1") říká, stejně jako u předchozích příkladů, na kolik desetinných míst se má zaokrouhlovat.

  46. Zamknutí části tabulky Příklad: Potřebuji část tabulky zamknout tak, aby ji nemohli uživatelé upravovat.Např. v této tabulce potřebuji zamknout vše kromě modrých buněk.

  47. Zamknutí části tabulky Zamčení tabulky zahájím jejím odemčením.Nejprve totiž stanovím, které buňky zůstanou odemčené, a pak zbytek listu zamknu.Označte buňky, které mají zůstat odemčené a přístupné úpravám. Pak do výběru klikněte pravým tlačítkem, vyberte „Formát buněk“ a přepněte se do karty „Zámek“.

  48. Zamknutí části tabulky

  49. Zamknutí části tabulky Zjistíte, že aniž jste cokoliv podnikli, je zaškrtnuto „Uzamčeno“. Protože chceme, aby vybrané buňky byly přístupné pro úpravy, zrušíme toto zaškrtnutí. Klikněte na OK. Teď tedy zamkneme zbytek listu.

  50. Zamknutí části tabulky Jděte na kartu „Revize“ a klikněte na „Zamknout list“.

More Related