Excel 2007

transcript

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

Soubory ke stažení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í tabulkyPř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í tabulkyPřevrácení tabulky

Smyslem je tedy změnit tabulku tak, aby se ze sloupečků staly řádky a naopak.

Postup: 1. Označíme buňky původní tabulky.

2. Zkopírujeme (např. Ctrl C)

3. Klikneme na místo, kam se má vložit změněná tabulka

4. Domů - Vložit jinak - zaškrtneme Transponovat

Použití absolutních odkazů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ů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č.

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.

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

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

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.

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 Pojmenování skupiny buněkbuně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 Pojmenování skupiny buněkbuně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 Pojmenování skupiny buněkbuně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 Pojmenování skupiny buněkbuně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 SouhrnFunkce 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“

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:

Nyní klikneme myší do jakékoliv buňky tabulky a klikneme na "Data" a "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.

Výsledek je zde:

Skalární součinSkalá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činSkalá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činSkalá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 OPAKOVATFunkce 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 OPAKOVATFunkce 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.

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

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

Generování náhodných čísel Generování náhodných čísel pomocí funkce pomocí funkce RANDBETWEENRANDBETWEEN 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 Generování náhodných čísel pomocí funkce pomocí funkce RANDBETWEENRANDBETWEEN 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í Concatenate aneb spojování obsahu buněkobsahu 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í Concatenate aneb spojování obsahu buněkobsahu 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)

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

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

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

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 dataZobrazení 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ů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.

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

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í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 Zaokrouhlení na jedno desetinné číslodesetinné číslo

Vzoreček: =ZAOKROUHLIT(A1;1)

Výsledek: 1234,6

Zaokrouhlení na desítkyZaokrouhlení 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 nahoruZaokrouhlit nahoru

Vzoreček: =ROUNDUP(A1;1) Výsledek: 1234,6

Př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ůZaokrouhlit dolů

Vzoreček: =ROUNDDOWN(A1;1) Výsledek: 1234,5

Př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 tabulkyZamknutí čá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.

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

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.

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

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í souboruZamknutí 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í souboruZamknutí 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šituZamč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šituZamč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 MINFunkce 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 MINFunkce 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 MINFunkce MAX a MIN

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

Funkce SVYHLEDATFunkce SVYHLEDAT

A co kdybych chtěl najít jméno člověka, který je nejstarší nebo nejmladší? Pak použiji funkci SVYHLEDAT V 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).

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

Zápis: Výsledek:

Použití funkce SVYHLEDATPouž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 SVYHLEDATPouž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é.

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

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

vypadá formulář takto:

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.

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

Použití funkce VVYHLEDATPouž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 Funkce SVYHLEDAT/VVYHLEDATSVYHLEDAT/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í tabulkyKontingenč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“

Budeme pracovat s touto tabulkou:

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

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

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

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

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

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

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

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

A to je všechno – výsledek:

Videotutoriál - zde

Formuláře v ExceluFormulář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 ExceluFormuláře v Excelu

Nejprve aktivujeme kartu „Vývojář“ Tlačítko Office – Možnosti aplikace Excel

- Oblíbené – Zobrazit na pásu kartu Vývojář

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

Nakreslíme pole do buňky B3

Pravým tlačítkem nastavíme formát ovládacího prvku Vstupní oblast bude E2:F6 Propojení s buňkou F8

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

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

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

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

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

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

Výsledek:

Úkol k procvičeníÚ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í zdrojeInformační zdroje

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

http://www.excelentnitriky.com/ Soubory ke stažení:

http://skoleni.holesov.cz

Excel 2007

Documents