Excel 2007
Sponsored Links
This presentation is the property of its rightful owner.
1 / 91

Excel 2007 PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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

Příklad: Máme takovouto tabulku (viz soubor prevraceni.xls)

  • A chceme z ní udělat následující tabulku:


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


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č.


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č.


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.


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


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


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.


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é.


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“


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.


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“.


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.


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“


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:


Funkce Souhrn

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


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.


Funkce Souhrn

Výsledek je zde:


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“


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)


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:


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:


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.


Funkce OPAKOVAT

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


Funkce OPAKOVAT

Pak už jen roztáhnu na další řádky.

Videotutoriál – zde

Výsledek:


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.


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


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".


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)


Concatenate aneb spojování obsahu buněk

Pokud nechci funkci natvrdo psát, ale jen vyplňovat formulář, vyplním ho takto:


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ý.


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“


Použití funkce KDYŽ

Do buňky D2 napíši =KDYŽ(B2>C2;"Ztráta";"Zisk")


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:


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


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.


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


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.


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.


Zaokrouhlení na jedno desetinné číslo

Vzoreček: =ZAOKROUHLIT(A1;1)

Výsledek: 1234,6


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.


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.


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.


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.


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“.


Zamknutí části tabulky


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.


Zamknutí části tabulky

Jděte na kartu „Revize“ a klikněte na „Zamknout list“.


Zamknutí části tabulky

Otevře se toto okno:

Má-li být přístup chráněn heslem, zadejte ho.Klikněte na OK.

A to je vše, do modrých buněk teď jde zapisovat, do ostatních ne.


Zamknutí souboru

Příklad: Potřebuji zamknout soubor tak, aby ho nikdo nemohl otevřít, nebo aby ho bylo možné otevřít pouze pro čtení bez možnosti úprav.

Klikněte na „Uložit jako“

Klikněte na „Nástroje“


Zamknutí souboru

Vyberte „Obecné možnosti“.

Zadejte hesla pro otevření, pro úpravy nebo obě. Následně je zadejte ještě jednou pro kontrolu.

Uložte soubor.


Zamčení sešitu

Příklad: Potřebuji zamknout Excelovský sešit. Tedy zařídit, aby nikdo nemohl přidat nebo odstranit listy.

Klikněte na „Revize“ a dále na „Zamknout sešit“.


Zamčení sešitu

V okně, které se otevře, nastavte heslo. Uživatel souboru, který heslo nezná, nebude moci přidat, ubrat ani přemístit žádný list.


Funkce MAX a MIN

Příklad: Funkce MAX a MIN jsou jedny z nejčastěji používaných v Excelu. V následující tabulce mám seznam lidí a jejich věk. Do buněk B7 a B8 potřebuji doplnit hodnoty nejvyššího a nejnižšího věku.

Viz soubor „maxmin.xls“


Funkce MAX a MIN

V buňce B7 použiji funkci MAX. Její zápis vypadá takto:=MAX(A2:A5)Jednoduše napíšu "=", dále název funkce - v tomto případě "MAX" a do závorky nadefinuji oblast, ze které se má nejvyšší hodnota vybrat.


Funkce MAX a MIN

Obdobně pracuji i s funkcí MIN - u ní vypadá zápis takto:=MIN(A2:A5)


Funkce SVYHLEDAT

A co kdybych chtěl najít jméno člověka, který je nejstarší nebo nejmladší? Pak použiji funkci SVYHLEDATV tomto případě napíšu do buňky B10 funkci tímto zápisem: 

= SVYHLEDAT(B7;A2:B5;2), resp. do buňky B11 zápisem 

= SVYHLEDAT(B8;A2:B5;2).


Funkce SVYHLEDAT

  • SVYHLEDAT(B7;A2:B5;2) – popis parametrů

  • B7 – v této buňce je věk nejstaršího pro vyhledávání

  • A2:B5 – tabulka určující rozsah hledání

  • 2 - údaj, z kterého sloupce mám hodnotu doplnit


Funkce SVYHLEDAT

Zápis:

  • Výsledek:


Použití funkce SVYHLEDAT

Příklad:

Potřebuji z jedné tabulky (v jednom listu) doplňovat hodnoty do druhé.

Viz soubor „vyhledani.xls“


Použití funkce SVYHLEDAT

Ve vzorové tabulce potřebuji doplnit do sloupečku "Cena za kus" ceny z malé tabulky vpravo (v tomto příkladu mě nebude zajímat sloupeček "Cena celkem" a budu se tvářit, jako by zde nebyl).

Např. u čokolády chci doplnit do buňky E5 cenu 12 Kč, která odpovídá čokoládě, u oplatky 8 Kč atd. Mohl bych to udělat ručně, ale u velkých tabulek by to bylo nepřiměřeně zdlouhavé.


Použití funkce SVYHLEDAT

Do buňky E5 vpíši: =SVYHLEDAT(B5;H7:I11;2)

"=" začíná každou funkci

"SVYHLEDAT" je název funkce

"B5" proto, že v této buňce je slovo "čokoláda" - slovo, podle kterého má excel v pravé tabulce najít příslušnou cenu.

"H7:I11" proto, že v této oblasti je umístěna tabulka, ze které se vybírá.

"2" proto, že z malé tabulky, ze které se vybírá, se má doplnit hodnota, která je ve druhém sloupečku, což je sloupeček "Cena/ks".


Použití funkce SVYHLEDAT

Pokud chci funkci zadat přes formulář (Vložit - Funkce...), vypadá formulář takto:


Použití funkce SVYHLEDAT

Než funkci, zadanou do buňky F5, roztáhnu na další řádky, musím doplnit pevné (absolutní) odkazy a funkce pak vypadá takto:

=SVYHLEDAT(B5;$H$7:$I$11;2)

Zafixoval jsem druhý parametr - oblast tabulky, ze které se vybírá. Dělám to proto, že tato tabulka zůstane úplně stejná pro všechny řádky a při roztahování se nemá měnit.


Použití funkce SVYHLEDAT

Výsledek je (po jednoduchém doplnění "Tržby celkem"), tento:


Použití funkce VVYHLEDAT

Kdyby tabulka, ze které se vybírá, byla "obrácená" (stejně jako tabulka vpravo dole u výsledné tabulky), použil bych místo funkce SVYHLEDAT funkci VVYHLEDAT

Ve třetím parametru by bylo číslo řádku místo čísla sloupce (v tomto případě je to tak jako tak dvojka), jinak se obě funkce používají obdobně.


Funkce SVYHLEDAT/VVYHLEDAT

Jak si pamatovat názvy funkcí? "S" na začátku názvu funkce SVYHLEDAT je odvozené od slova "Svisle", "V" na začátku názvu funkce VVYHLEDAT" je odvozeno od slova "Vodorovně".

Nefunguje-li funkce správně, srovnejte položky v malé tabulce podle velikosti od nejmenší po největší (vzestupně).


Kontingenční tabulky

Příklad:

Mám neuspořádaná data a chci z nich získat důležité informace.

V tomto případě se chci (s pomocí kontingenční tabulky) dozvědět, kolik je v seznamu (nabídka autobazaru) aut značky Ford a kolik dohromady stojí.

Viz soubor „auta.xls“

70


Kontingenční tabulky

Budeme pracovat s touto tabulkou:


Kontingenční tabulky

Začnu tak, že kliknu kamkoliv do tabulky - nemusím nic označovat.

Dále kliknu nahoře na „Vložení“ a „Kontingenční tabulka“.


Kontingenční tabulky

Následující dialog mohu nechat jak je a jen ho potvrdit "OK".


Kontingenční tabulky

Tím vznikne nový list s kontingenční tabulkou.


Kontingenční tabulky

Všimněte si pravého sloupečku s nabídkou - nahoře jsou v řádcích vypsané názvy sloupečků.

Tím, jak je budu přesouvat do tabulky, budu upravovat kontingenční tabulku.

Mým úkolem bylo zjistit, kolik je v seznamu Fordů a kolik dohromady stojí.

Udělám to tak, že v tabulce nechám vypsat součty cen za všechny značky - tedy i za Ford.


Kontingenční tabulky

„Ford“ je jedna ze značek aut v seznamu. Proto přetáhnu „Značka“ z horního obdélníku vpravo do obdélníku „Sem přetáhněte řádková pole“ v tabulce.


Kontingenční tabulky

Tím se Vám v obdélníku, kam jste přetahovali, vypíší všechny značky aut v seznamu. 


Kontingenční tabulky

Teď ještě zjistit, kolik tyto značky dohromady stojí. Přetáhnu „Cena“ do „Sem přetáhněte datové položky“. 


Kontingenční tabulky

Teď již u každé značky vidím, kolik dohromady stojí. 


Kontingenční tabulky

Teď mě ještě zajímá, kolik aut té které značky v seznamu je.

Dvojkliknu tedy na „Součet z Cena“ a v nabídce změním „Součet“ na „Počet“.


Kontingenční tabulky

A to je všechno – výsledek:

Videotutoriál - zde


Formuláře v Excelu

Vytvořte ze zadaných tabulek „preklad.xls“ miniprogram, který podle ceníku a zadaného počtu stran vypočítá cenu za překlad.

Uživatel pouze v nabídce zvolí jazyk a pomocí přepínacích šipek nastaví počet stránek. Program sám doplní cenu a vypočítá celkovou cenu


Formuláře v Excelu

Nejprve aktivujeme kartu „Vývojář“

Tlačítko Office – Možnosti aplikace Excel - Oblíbené – Zobrazit na pásu kartu Vývojář


Formuláře v Excelu

Vybereme: Vývojář – Vložit – Ovládací pole se seznamem

Nakreslíme pole do buňky B3


Formuláře v Excelu

Pravým tlačítkem nastavíme formát ovládacího prvku

Vstupní oblast bude E2:F6

Propojení s buňkou F8


Formuláře v Excelu

Počet stránek k přeložení vyrobíme pomocí tlačítka Číselník (taktéž na kartě Vývojář – Vložit)

Nakreslíme pole do pravé části buňky B5


Formuláře v Excelu

Pravým tlačítkem nastavíme formát ovládacího prvku

Aktuální hodnota 1, nejnižší hodnota 1, propojení s buňkou B5


Formuláře v Excelu

Cenu za 1 stránku zjistíme pomocí SVYHLEDAT

Do buňky B4 vložíme tuto funkci s tímto nastavením:


Formuláře v Excelu

Výslednou cenu v buňce B6 zjistíme pomocí vzorce =B4*B5

Výsledek:


Úkol k procvičení

V souboru excel2007c.pdf je zadaní i řešení úkolu Pokladní doklad

Pro tento úkol je potřeba soubor zasoby.xls

Jedná se o komplexní procvičení znalostí programu Excel 2007


Informační zdroje

Soubory cvičných úloh v programu MS Excel 2007:

http://www.excelentnitriky.com/

Soubory ke stažení: http://skoleni.holesov.cz


  • Login