|
|
||||||||||||
|
MS Office – video návody |
||||||||||||
|
|
||||||||||||
|
Manuál pre školenia aplikácií balíka MS Office
(MS Excel, MS Word, MS Outlook ...), |
||||||||||||
|
|
||||||||||||
|
|
||||||||||||
|
Školiace videá sú vytvorené a uložené vo formáte *.swf. Bezproblémové prehrávanie flashu v prehliadači (Internet Explorer, FireFox či iný) zabezpečuje Plugin Adobe Flash Player. Podrobnejšie informácie o plugine a download najnovšej verzie (anglicky) je na stránke: http://get.adobe.com/flashplayer/ ). Podrobný popis riešenia prípadných problémov s inštaláciou je na stránke (anglicky): http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_19166. Návod na inštaláciu v slovenčine: http://www.gardentown.sk/download/index.html
Pre prevádzku cvičných príkladov a úloh je nutné mať nainštalované aplikácie balíka MS Office 2003 resp. 2007, minimálne MS Word a MS Excel.
Samotné video sa spustí kliknutím na hyperlinku príslušného videa. V závislosti od rýchlosti pripojenia (pri bežnom DSL pripojení cca 15 – 30 sekúnd) sa video najskôr načíta a následne automaticky spustí. Video je možné kedykoľvek zastaviť, spustiť od začiatku alebo presunúť sa na konkrétny obrázok videa.
|
|
TIP1: Ak vidíte v prehliadači iba časť videa, stlačte F11, okno prehliadača sa rozšíri na celú obrazovku (vrátenie do pôvodného stavu, tiež F11).
TIP2: Spustenie videa z Wordu: stlačiť a držať klávesu CTRL a kliknúť na linku videa.
· Štruktúra konkrétnej úlohy a jej riešenia
Všetky tu uvedené materiály (zadania, riešenia, prílohy, videá ) tvoria podkladové a pomocné materiály pre školenie danej témy (resp. okruhu tém). To znamená, že komplexná informácia je školenému poskytnutá pri použití týchto pomôcok zároveň s odborným výkladom.
Po ukončení školenia zostávajú materiály účastníkom školenia, aby si sami mohli celý proces kedykoľvek zopakovať a získané vedomosti utvrdiť.
Každá téma má rovnakú štruktúru:
1) Zadanie (resp. Otázka) – krátky popis (požiadavka) riešeného problému,
2) Riešenie (resp. Odpoveď) – pár bodová postupnosť krokov, ktorými je možné zadanie vyriešiť (zodpovedať otázku),
3) Prílohy – externé súbory (zväčša XLS, DOC, MDB) použité pri riešení zadaní, resp. súbory s finálnym riešením
4) Videá – „krok za krokom“ odchytené obrazovky, ktoré chronologicky zachytávajú celý proces riešenia zadania
Excel je často používaný ako nástroj spracovania a vyhodnotenia veľkej množiny údajov (databáz), zvyčajne získaných z externých zdrojov alebo informačných systémov (účtovníctva, sklady, predaje ...).
Údaje býva nutné spracovávať priebežne, napr. mesačné uzávierky, prehľady a reporty. Nad zdrojovými údajmi je zvyčajne treba vykonať (jednorázovo alebo pri každom spracovaní) množinu rôznych operácií, ako vyhľadávanie, filtrovanie, triedenie (sortovanie), výpočtové operácie, atď.
Nižšie popisovaný okruh úloh rieši uzavretý cyklus od načítania údajov z externej databázy, cez jej spracovanie a úpravu údajov, až po generovanie finálnych prehľadov a reportov (kontingečná tabuľka).
TIP 1 ( ...NIE KAŽDÝ VIE ...): kombinácia
stlačených kláves pre jednotlivé operácie závisí od nastavenia primárneho
jazyka v regionálnych nastaveniach (Regional and
Language Options).
PRÍKLAD – Zobrazenie dialógu formátu bunky: Keyboard EN: CTRL + 1, Keyboard Slovak: CTRL + SHIFT + 1
POZOR !!!: Platí i keď si klávesnicu v aplikácii prepnete
Pre túto príručku predpokladáme ako primárnu slovenskú klávesnicu.
TIP 2: Klávesové skratky pre Keyboard US vidíte pri voľbe príslušného príkazu z hlavnej ponuky:
· Prílohy pre celú skupinu úloh:
Northwind.mdb – MS Access vzorová databáza s ekonomickou agendou
Book1.xls – XLS zošit – finálny stav po vyriešení všetkých zadaní (pre porovnanie)
· A) Prispôsobenie pracovného prostredia Excelu pri prvom spustení
- ZADANIE:
a) len XL 2003 – panel nástrojov Standard a Format (Štandard a Formát), do dvoch riadkov, zobrazovať vždy plné ponuky,
b) len XL 2003 – pridať na
panel nástrojov tlačidlo „Zmraziť hlavičky“
(Freeze Panes),
t.j.
ukotvenie
záhlaví riadkov (hore) a stĺpcov (vľavo) od aktuálnej bunky.
- RIEŠENIE (XL 03 – EN):
1.
a) Hlavná
ponuka: Tools (Nástroje) / Customize (Prispôsobiť))
resp. Klik pravým tl. myši
na ľubovoľný panel nástrojov a z lokálnej ponuky vybrať voľbu
Customize (Prispôsobiť))
2. b) Klik pravým tl. myši na ľubovoľný panel nástrojov a vybrať Customize (Prispôsobiť))
3. b) vybrať strednú záložku Commands (Príkazy),
4. b) v ľavej časti dialógu zvoliť kategóriu Windows and Help (Okná a pomocník),
5. b) v pravej časti dialógu kliknúť ľavým (a držať stlačené) tl. myši na ikonku Freeze Panes (Ukotviť priečky) a ťahať ikonku tesne za poslednú ikonku niektorého z panelov nástrojov,
6. b) pri stále otvorenom dialógu klik pravým tl. myši na novo pridanú ikonku, zo zobrazenej lokálnej ponuky vybrať voľbu Image and Text (Obrázok a text) ...
7.
b) ...
a v poli Names (Názov) prepísať text na „&Zmraziť hlavičky“
- VIDEÁ:
o a) 01_TLB_1st.htm
o b) 02_TLB_AddButton.htm
TIP: Súbor, v ktorom sa ukladá vlastné
nastavenie je ExcelXX.xlb (XX – verzia
Excelu). Ak máte prostredie Excelu už nastavené a chcete si ho zachovať,
ale zároveň vyskúšať aj všetky príklady, premenujte súbor napr. na Excel11.xlbOLD,
a po ukončení učenia naspäť .
(Cesta k súboru.: Excel 2003 c:\Documents and Settings\UserName\Application Data\Microsoft\Excel\Excel11.xlb).
· 1) Import údajov z externej databázy (MS Access MDB)
- ZADANIE:
Importujte údaje z externej databázy (MDB), tak aby ich bolo možné priebežne zo zdroja aktualizovať.
Zdroj: ../db1/Northwind.mdb, tabuľka (Invoice) // faktúry
Spresnenie: od 4-tého riadku // !!! dôležité pre nasledujúce úlohy
- RIEŠENIE (XL 03 – EN):
1. Hlavná ponuka: Data (Údaje) / Import External Data (Importovať externé údaje) / Import Data (Importovať údaje)
2.
Zadať
zdroj údajov:
- manuálne: C:/../db1/Northwind.mdb,
- preklikaním cez dialóg: My Computer –> disk
C: –> ../db1/Northwind.mdb
3. Vybrať tabuľku Invoice (faktúry)
4. Vložiť na pozíciu B4 aktuálneho listu
- VIDEO:
Excel 2003 (EN) – db_01_ImportMdbData.htm
· 2) Zmeniť vlastnosti importu externých údajov
- ZADANIE:
zmeniť názov oblasti externých údajov,
prepísať údaje pri aktualizácii databázy,
zachovať vzorce (pomocné vzorce z importovaných údajov, ktoré budú vytvorené neskôr)
- RIEŠENIE (XL 03 – EN):
1. Vybrať ľubovoľnú bunku v priestore importovaných údajov
2. Na paneli nástrojov „External
Data
(Externé údaje)“
kliknúť na ikonku 
3. V dialógovom okne urobiť
nasledovné úpravy:
- pole Name (Názov)
– prepísať na Northwind_Faktury
- zaškrtnúť (ak už nie je): Preserve
cell formating, Include Field Names, Adjust Column width
- zvoliť voľbu Overwrite existing
cells ...
- zaškrtnúť: Fill down
formulas ...
4. Stlačiť OK
- VIDEO:
Excel 2003 (EN) – db_02_ImportedDataProperties.htm
POZOR: Po aktualizácii údajov z externého zdroja budú zachované IBA vzorce vytvorené BEZPROSTREDNE VPRAVO od importovaných údajov.
· 3) Zmeniť font – „vidieť naraz viac“, upraviť formáty buniek
viditeľnosť viac údajov ne jednej obrazovke // font Arial Narrow
zmraziť hlavičky nadpisov // Window - Freeze Panes
formátovať nadpisový riadok // písmo Bold (Tučné), iná farba pozadia
nastaviť formát numerických polí – stĺpec W až AA // Standard
(2 des. miesta, oddeľovač tisícov,
záporné čísla červenou)
nastaviť formát – stĺpec Discount // percento
1.
označiť všetky
bunky
- klávesnica - CTRL + A
- myš: klik na obdĺžnik nad záhlavím riadkov, vľavo od záhlavia stĺpcov
2. V paneli nástrojov Formatting (Formátovanie) vybrať font Arial Narrow (úzky font),
3. Postaviť sa na bunku C5 a zmraziť záhlavia, pridaná ikonka alebo Windows (Okno) / Freeze Panes (Ukotviť priečky),
4.
Označiť bunky B4:AA4 (TIP:
postaviť sa na B4 a stlačiť CTRL + SHIFT + šípka vpravo),
naformátovať záhlavia (CTRL + B) a zvoliť iné pozadie (cez panel nástrojov),
5. Označiť stĺpce W:X, zobraziť dialóg formátovania – hlavná ponuka Format (Formátovanie), Cells (Bunky) (otvoriť dialóg: CTRL + SHIFT + 1),
6. Záložka Number (Číslo), kategória Number (Číslo), formátovať čísla na 2 des. miesta, oddeľovač tisícov, mínusové čísla červenou,
7. Označiť stĺpec Y a formátovať čísla ako percento (analogicky ako bod 5 a 6), alebo cez ikonu percenta na paneli nástrojov Formatting (Formátovanie),
8.
Označiť stĺpce
Z:AA a formátovať čísla
Standard
(analogicky ako bod 5 a 6),
alebo (kl. skratka: CTRL + SHIFT + !)
- VIDEO:
Excel 2003 (EN) – db_03_FormatDbCells.htm
- ZADANIE:
vytvoriť jednotlivé vzorce a nakopírovať do celej tabuľky:
Vzorec - do nového stĺpca "BezZlavy": J_cena * Mnozstvo
- kopírovať vzorec do ostatných buniek // ako: ťahanie, dblClick, ^+end,
Vzorec - do nového stĺpca "S dph": BezZlavy * %dph
- percento dph – demonštrovať absolútny / relatívny odkaz
- pomenovaná oblasť: jedna bunka, rozsah (% DPH - PercDph, CelkomBezZlavy)
- F3 – použiť pomenovanú oblasť do vzorca
- RIEŠENIE (XL 03 – EN):
1. Vytvorte názvy stĺpcov v bunkách AB4 až AC4 (Bez_Zlavy, Celkom_s_Dph, Rok_Mesiac)
2. Percento DPH – do bunky AD2 zadajte hodnotu 0,19 a naformátuje ako percento,
3. Bez_Zlavy – do bunky AB5
zadajte vzorec =W5*X5,
(TIP: adresy buniek nie nutné písať manuálne,
pri tvorbe vzorca stačí na zdrojovú bunku kliknúť myšou, t.j. napísať =, kliknúť na W5,
napísať *, kliknúť na X5)
4. Celkom_s_Dph – do bunky AC5
zadajte vzorec = =AB5*(1+$AD$2),
(VŠIMNITE SI: Adresa bunky pre %DPH je písaná so
znakmi $ – absolútny odkaz, AB5 –
bez „dolára“ relatívny odkaz, t.j. pri kopírovaní vzorca na ľubovoľnú
pozíciu bude vzorec odkazovať vždy na bunku AD2, prvá časť vzorca sa
zmení relatívne, podľa novej pozície vzorca)
(TIP: znak $ nie nutné písať manuálne, pri tvorbe vzorca stačí
stlačiť kláves F4)
5. Skopírovať vzorce v bunkách
AB5
a AC5 do celej tabuľky: označiť bunky AB5:AC5, ťahať čierny úchyt pod bunkou AC5 až po bunky AB2148:AC2148
ALTERNATÍVA 1: dvojklik na čiernom
úchyte, !!! musí byť kontinuálna oblasť
ALTERNATÍVA 2 - kopírovaním: CRTL + C, CRTL + SHIFT + END, CTRL + V,
6. Skopírovať formát stĺpcov W:X, do stĺpcov AB, AC:
označiť stĺpce W:X, kliknúť na ikonu
kópie formátu 
na paneli nástrojov Standard (Štandard), označiť stĺpce AB:AC,
- VIDEO:
Excel 2003 (EN) – db_04_Formulas.htm
· 5) Jednoduchá funkcia, obnova zdrojových údajov
- ZADANIE:
vytvoriť jednotlivé vzorce a nakopírovať do celej tabuľky
otestovať automatické vygenerovanie dodatočných vzorcov pri obnove zdrojových údajov
Vzorec RokMesiac / stĺpec AD, posledný stĺpec
- textové funkcie =TEXT(...)
- !!! POZOR !!! – Ak sa použije v stĺpci A – vzorec sa automaticky neskopíruje pri obnove údajov
Mazať všetko od riadku 10
- CTRL+SHIFT+ šípka dole, SHIFT+ medzerník, CRT+ mínus na num.kláv
„ReFresh“ zdrojových údajov
!!! POZOR !!! ak sú vo vzorcoch použité pomenované oblasti, pri obnove údajov sa nevygenerujú korektne
- RIEŠENIE (XL 03 – EN):
1. Rok_Mesiac – postaviť sa do bunky AD5,
2. Kliknutím na ikonku 
spustiť
dialóg Insert Function (Vložiť funkciu),
3. Vybrať kategóriu Text (Text), funkciu TEXT, stlačiť OK,
4. V dialógu Function
Arguments (Argumenty funkcie),
do poľa Value (Hodnota) S5 (ShippedDate),
do poľa Format_text - "YYYY.MM" (!!! Vrátane úvodzoviek),
5. Skopírovať vzorec do celého stĺpca
(viď bod 4.5)
- VIDEO:
Excel 2003 (EN) – db_05_FunctionText.htm
- ZADANIE:
len XL 2003 – pridať na panel
nástrojov tlačidlá pre filtrovanie údajov / Data-AutoFilter, z hlavnej ponuky Data / Filter / Autofilter,
- RIEŠENIE (XL 03 – EN):
1.
Hlavná ponuka:
Tools (Nástroje) / Customize (Prispôsobiť))
resp. Klik pravým tl. myši
na ľubovoľný panel nástrojov a z lokálnej ponuky vybrať voľbu
Customize (Prispôsobiť))
2. Klik pravým tl. myši na ľubovoľný panel nástrojov a vybrať Customize (Prispôsobiť))
3. vybrať strednú záložku Commands (Príkazy),
4. v ľavej časti dialógu zvoliť kategóriu Data (Údaje),
5. v pravej časti dialógu kliknúť ľavým (a držať stlačené) tl. myši na ikonku AutoFilter a ťahať ikonku tesne za poslednú ikonku niektorého z panelov nástrojov,
6.
pri stále otvorenom dialógu
vybrať z hlavnej ponuky vybrať
voľbu Data / Filter / Autofilter, (Údaje / Filter /
Autofilter), stlačiť
a držať CTRL,
kliknúť ľavým tl. myši (a držať stlačené)
na ikonku AutoFilter a ťahať ikonku na panel nástrojov
- VIDEÁ:
Excel 2003 (EN) – db_06_AutoFilter.htm
· 7) Porovnanie funkcií SUM verzus SUBTOTAL pri použití filtra
- ZADANIE:
použitie funkcií sumáru (SUM a SUBTOTAL) pri zapnutom a vypnutom filtri údajov
- RIEŠENIE (XL 03 – EN):
1.
Postaviť sa na
bunku AB2 a kliknúť na
ikonku sumarizácie 
, výsledok: =SUM(AB5:AB2148)
(TIP: po zobrazení funkcie =SUM(), klik na AB5, CTRL + SHIFT + šípka dole), ENTER),
2.
Zapnúť AutoFilter
a vyfiltrovať hodnotu „1997.05“
Postaviť sa AD5, kliknúť na ikonku 
,
3.
Postaviť sa na
bunku AC2 a kliknúť na
ikonku sumarizácie , výsledok: =SUBTOTAL(9;AC5:AC9989)
(TIP: po zobrazení funkcie =SUBTOTAL(9;), klik na AC5, CTRL + SHIFT + šípka dole), ENTER),
4.
Vypnúť AutoFilter
Postaviť sa AD5,
kliknúť na ikonku 
,
POZNÁMKA: Všimnite si, že po vypnutí
autofiltra sa suma v bunke AB2 nezmení,
ale v bunke AC2 sa zmení.
Funkcia SUBTOTAL spracúva
len vyfiltrované údaje.
POZOR: Pozor na rozsah sumarizovaných údajov po aktualizácii zdrojových údajov.
- VIDEO:
Excel 2003 (EN) – db_07_SUM_vs_SUBTOTAL.htm
· 8) Zoskupiť / Oddeliť detaily (Group / Ungroup)
- ZADANIE:
pridať a použiť na panel nástrojov ikonky pre zoskupovanie údajov (Data / Group – Ungroup)
- RIEŠENIE (XL 03 – EN):
1.
Hlavná ponuka:
Tools (Nástroje) / Customize (Prispôsobiť))
resp. Klik pravým tl. myši
na ľubovoľný panel nástrojov a z lokálnej ponuky vybrať voľbu
Customize (Prispôsobiť))
2. Klik pravým tl. myši na ľubovoľný panel nástrojov a vybrať Customize (Prispôsobiť))
3. vybrať strednú záložku Commands (Príkazy),
4. v ľavej časti dialógu zvoliť kategóriu Data (Údaje),
5. v pravej časti dialógu kliknúť ľavým (a držať stlačené) tl. myši na ikonku Group (Zoskupiť) a ťahať ikonku tesne za poslednú ikonku niektorého z panelov nástrojov,
6. zopakovať bod 5 – ikonka Ungroup (Oddeliť),
7. pri stále otvorenom dialógu klik pravým tl. myši na novo pridanú ikonku, zo zobrazenej lokálnej ponuky vybrať voľbu Image and Text (Obrázok a text) ...
8. označte stĺpce E:J a kliknite na novo pridanú ikonku Group,
9. zopakovať bod 8 so stĺpcami L:P a T:V,
10.
kliknutím na
ikonku 
nad zoskupenými stĺpcami sa
definovaná oblasť skryje, kliknutím na 
sa znovu zobrazí,
11.
kliknutím na
ikonku 
sa zoskupia všetky
skupiny naraz, kliknutím na 
sa všetky skupiny naraz
zobrazia.
POZNÁMKA: definované zoskupenia nemožno použiť pri zamknutom zošite.
- VIDEO:
Excel 2003 (EN) – db_08_GRP_unGRP.htm
·
9) Validácia v bunke – výber zo zoznamu
(medzikrok – Pivot table „City_List“)
- ZADANIE:
V nasledujúcej úlohe budeme vytvárať vzorce, kde sa budú súčtovať numerické polia, v závislosti od určitej hodnoty. V tejto úlohy si pre to pripravíme nástroje.
Pre validáciu v bunke (výber zo zoznamu disponibilných miest):
- vytvoriť kontingenčnú tabuľku zo stĺpca City Mesto
- vytvorený zoznam pomenovať City_List,
- použiť pomenovanú oblasť ako zdroj údajov pre výber údajov pre bunku K2: "City_List"
- RIEŠENIE (XL 03 – EN):
1. postaviť sa na bunku K4,
2. hlavná ponuka: Data (Údaje) / PivotTable and PivotChart Report... (Kontingenčná tabuľka ...),
3. v prvom kroku v zobrazenom dialógu zvoliť voľby: zdroj údajov Microsoft Excel List..., typ reportu (...kind of report...) Pivot Table (Kontingenčná tabuľka) a stlačiť tlačidlo Next (Ďalej),
4.
v druhom
kroku sa definuje oblasť, ktorú použiť: Sheet1!$K$4:$K$2148
(TIP: klik na K4, CTRL + SHIFT + šípka dole), ENTER
a stlačiť tlačidlo Next (Ďalej),
5. v treťom kroku stlačiť tlačidlo Layout (Rozmiestenie),
6. ťahať ikonku „City“ do oblasti „ROW (RIADOK)“ a znovu i do oblasti „DATA (ÚDAJE)“ a stlačiť OK,
7. dokončiť krok 3: voľba „new worksheet (nový list)“ a stlačiť tlačidlo Finish (Ukončiť),
8. vybrať strednú záložku Commands (Príkazy),
9. v ľavej časti dialógu zvoliť kategóriu Data (Údaje),
10. v pravej časti dialógu kliknúť ľavým (a držať stlačené) tl. myši na ikonku Group (Zoskupiť) a ťahať ikonku tesne za poslednú ikonku niektorého z panelov nástrojov,
11. zopakovať bod 5 – ikonka Ungroup (Oddeliť),
12. pri stále otvorenom dialógu klik pravým tl. myši na novo pridanú ikonku, zo zobrazenej lokálnej ponuky vybrať voľbu Image and Text (Obrázok a text) ...
13. označte stĺpce E:J a kliknite na novo pridanú ikonku Group,
14. zopakovať bod 8 so stĺpcami L:P a T:V,
15.
kliknutím na
ikonku
nad zoskupenými stĺpcami sa
definovaná oblasť skryje, kliknutím na
sa znovu zobrazí,
16.
kliknutím na
ikonku
sa zoskupia všetky
skupiny naraz, kliknutím na
sa všetky skupiny naraz
zobrazia.
POZNÁMKA: definované zoskupenia nemožno použiť pri zamknutom zošite.
- VIDEO:
Excel 2003 (EN) – db_09_Pivot_CityList.htm
· 10) Validácia dát – „SumIf“ function
- F3: "list_City" (zdroj z KTG_City)
- funkcia SUMIF
- Video: db_10_SumIf_Function.htm
· 11) Odchytenie makra - Rozšírený filter
- spusti záznam makra / A_RunAdvancedFilter, B_ShowAll
- run makro - ALT+F8
- Video: db_11_Macro_Catch.htm
· 12) Priradenie makra tlačidlu
- toolbar Forms – priradenie makra A_RunAdvancedFilter na tlačidlo
- Video: db_12_AddMacro_ToButton.htm
· 13) Podmienené formátovanie (napr. „po splatnosti“)
- buka R1 "Dátum", S1 "Dni"
- Video: db_13_ConditionalFormating.htm
- sort podľa ShipCountry, CustomerID
Subtotály:
- Edit / podľa ShipCountry, CustomerID, OrderDate
- Group (úroveň 2), ofarbiť SubTotaly
- ikonka na toolbar / Edit - Select Visible Cells
- Video: db_14_Sort_and_Subtotal.htm
· 15) Kontingečná tabuľka – „Pivot table“
KTG – data
- rozsah + aj počítané stlpce
- ShipCity, RokMesiac, Sum:BezZlavy, Celkom_s_Dph
- Ofarbenie, Sortovanie, Výber skupiny, Data - pod seba, vedla seba, Do zahlavia, Check vybrané
KTG - graf
- rozsah + aj počítané stĺpce
- ShipCity, RokMesiac, Sum:BezZlavy, Celkom_s_Dph
- Ofarbenie, Sortovanie, Výber skupiny, Data - pod seba, vedla seba, Do zahlavia, Check vybrané
- Video: db_15_Pivot_Data.htm