Námety na časť projektu v MS EXCELI (autor: ing. Miroslav Baláž, CSc.) Veľmi dôležitou vlastnosťou Excelu, je aj spracovanie údajov. Manažéri túto vlastnosť často využívajú pre operatívne úlohy. Informačný systém podniku je zvyčajne zdrojom väčšiny potrebných informácií, ale pre určité úlohy je potrebné údaje z IS, ešte viac prispôsobiť potrebám konkrétnej úlohy. Na ten účel je MS Excel ako stvorený. Aby sa dáta z iných zdrojov do Excelu dostali, je potrebné využiť jeho schopnosti: a. Copy / Paste (asi užívateľsky najjednoduchšia intuitívna možnosť) b. Export / import (Získať externé údaje) na karte „Údaje“ v paneli nástrojov c. Uložiť ako, alebo priamo daný súbor „cudzieho“ typu otvoriť d. Ručné vloženie dát je úplne krajnou možnosťou, ktorá má však iba samé nevýhody a snažíme sa jej preto všemožne vyhnúť (okrem mrhania drahocenného času je to možnosť neobmedzeného množstva vzniku chýb a ďalšie následné nepríjemnosti). Do digitálnej formy sa údaje vkladajú „ručne“ zo zásady maximálne len jeden jediný raz. Ešte lepší je však napr. snímač čiarového kódu a podobné techniky. Hlavné problémy pri preberaní a odovzdávaní dát do dokumentov iných formátov : a) Chyby vyplývajúce z internacionálnych nastavení : dátumy (tvar zápisu), reálne čísla (desatinná čiarka/bodka) b) Chyby vyplývajúce z typov polí tabuľky (MS Excel podporuje v podstate iba textové reťazce a reálne čísla. Čísla sú v MS Exceli následne interpretované ako číslo, dátum, mena, pravda/nepravda). Preto ak sa stratí pri prenose informácia, že sa jedná o dátum, tak sa miesto dátumov objavia, reálne čísla, ktoré majú v súčasnom období hodnotu okolo 40 000. c) Chyby vyplývajúce z transformácie kódových stránok (ASCII, Windows 1xxx, UTF8, Unicode). Aj keď všetko prebehne dobre a znaky sú na prvý pohľad v poriadku, slovenčina vyzerá prirodzene a nie ako posiata čínštinou, tak sa často stane, že medzera (znak s poradovým číslom 32), je nahradený iným znakom, ktorý síce vyzerá ako medzera, ale má iný kód. Preto ak medzera je potenciálnym problémom, prvým krokom záchrany, je zameniť v celom dokumente/tabuľke inkriminovaný znak, ozajstnou medzerou (CTRL+H) d) Chyby z ručného prepisovania. K tým ani netreba komentár, okrem toho, že je prejavom neprofesionality a mrhaním vzácneho času, veľmi záleží aj od ľudského faktora, ktorý môže byť aj s fatálnymi dôsledkami Pre spracovanie údajov má Excel v paneli nástrojov, vyčlenenú jednu samostatnú kartu s názvom “Údaje”, resp. v anglickej verzii “Data”. Ďalšia podstatná vlastnosť Excelu z hľadiska spracovania údajov je schopnosť tvorby Krížových (Pivot) tabuliek, tie sú dostupné z karty “Vložiť”, “Insert”. Karta Údaje zahŕňa tieto hlavné položky : a. Export / Import b. Pripojenia (táto časť sa bude skôr preberať v predmete databázy, alebo iných databázovo orientovaných predmetoch, preto nebude v ďalšom texte vysvetľovaná) c. Zoradiť a Filtrovať (Sort & Filter)
d. Nástroje pre údaje (Data Tools)
Hlavné položky pre spracovanie údajov: 1. Export / Import Je veľmi významnou súčasťou, ktorá dovolí prevziať (odovzdať) údaje z rôznych systémov. Táto časť sa do značnej miery prekrýva aj s funkcionalitou Excel/Súbor „Uložiť ako“, kde je tiež možné výber v akom tvare sa dáta uložia (najmä text, csv, ods, htm, či mht). Podobne je možné súbory „cudzieho“ formátu naopak aj otvoriť. Jedná sa najmä o : a. Spoluprácu s databázou: formáty Access, dBase a ODBC databázy. Najčastejšie problémy, ktoré si vyžadujú pozornosť a môžu prípadne obojsmerne komplikovať prevod údajov za cenu zvýšenej prácnosti: I. Prevod dátumov (zobrazia sa ako čísla, či text a nie ako dátumy) II. Prevod desatinných čísel (desatinná čiarka/bodka – v anglických údajoch) III. Maximálna dĺžka reťazca : Excel v. 2007+ podporuje dĺžku reťazca až 16000 znakov, zatiaľ čo napríklad Access iba 255, resp. potom je to už typ Memo, ktorý zase nepodporuje zoraďovanie a vyhľadávanie IV. Excel podporuje len reálne čísla. Tým pádom sa z MS Excelu do MS Accessu prenesú úplne všetky čísla ako reálne čísla, aj vtedy ak sa jedná s istotou o celé čísla. Celé čísla sa dajú využiť na spájanie tabuliek, reálne už nie, čo je dosť zásadný problém b. Otvorenie html tabuliek, alebo získanie tabuliek z webu (priamo z URL adresy) I. Ak je treba otvoriť dáta priamo na internetovej stránke a importovať tabuľku, treba vybrať možnosť „z webu“ v karte „Údaje“ v záložke „Získať externé údaje“ II. V opačnom prípade je možné priamo otvoriť externé údaje. Najčastejšie problémy, ktoré si vyžadujú pozornosť: I. Prevod dátumov (zobrazia sa ako čísla, či text a nie ako dátumy) II. Prevod desatinných čísel (desatinná čiarka/bodka – v anglických údajoch) III. Medzera v číslach (oddelenie tisícov od stoviek), nie je medzera, ale znak, ktorý síce tak vyzerá, ale nie je to Char(32), čo Excel akceptuje ako oddeľovač. Pokiaľ sa takejto „medzery“ užívateľ nezbaví, čísla sa budú prezentovať stále ako text. Najjednoduchšie je prevziať tento do schránky a použiť zámenu znakov „Ctrl+H“.
Údaje : Zoradiť a Filtrovať Údaje môžeme zoradiť podľa viacerých kritérií naraz, ak jedno nestačí. Typicky zhodné priezviská v zozname. Ďalšie vlastnosti filtrovania: a) Pre text sa v prirodzene používa zoradiť od a po z, čo znamená vzostupne. Opačné poradie znamená zostupne a je to od z po a. Problém : Ak je text kombinovaný s číslami, alebo čísle sú chápané ako text, tak sa zoradia z pohľadu čísel neprirodzene. Napríklad číslo 1 je pred číslom 2. Preto je dôležité, aby stĺpce tabuľky boli v správnom tvare. Ak ide o čísla, tak hodnoty stĺpca nemajú mať predpísaný formát typu text. b) Pre čísla sa hodnoty prirodzene zoraďujú od najmenšieho čísla po najvyššie (vzostupne) c) Pre čísla sa hodnoty prirodzene zoraďujú od najstaršieho dátumu po najnovší (vzostupne). Dátum je reprezentovaný reálnym číslom. Čím novší dátum, tým vyššie číslo. Prevod formátov (hodnoty stĺpca tabuľky), najčastejšie techniky: Ak chceme vnútiť údajom formát textu, zapíšeme na začiatok ako prvé písmeno, znak „’“, teda apostrof. Ak chceme vnútiť údajom formát čísla, vynásobíme danú bunku číslom 1. Ak tieto jednoduché zásahy nepomôžu, musíme pristúpiť k ďalším krokom úprav, ktoré sú popísané na inom mieste tohto dokumentu.
Filtrovanie dát: Jednoduché filtrovanie – ani nebudem písať, každý si vyskúša bez problémov sám. Rozšírené filtrovanie dovolí napríklad jednoducho skopírovať len dáta jedného odberateľa z mnohých do samostatného hárku. U rozšíreného filtrovania je vhodné, aby tabuľka mala názvy stĺpcov a aby bol vytvorený pomocný hárok, na ktorý sa filter odvoláva. V podstate na danom „filtrovacom“ pomocnom hárku stačia dva údaje: A) Názov poľa B) Hodnota kritéria Tie vyššie dve hodnoty sa označia ako hodnota kritéria. Po zapnutí filtra je možné prevziať filtrované dáta do schránky a ľubovoľne ich kopírovať kam treba. Text do stĺpcov : Tento úkon nám dovolí napríklad rozdeliť meno a priezvisko oddelené vzájomne medzerou z jednej bunky, do dvoch samostatných buniek. V tom prípade bol oddeľovačom znak medzera. Je možné samozrejme použiť aj iné znaky. Pozor výber znaku oddeľovača tejto funkcie je spoločný pre oddeľovač polí pri export / import externých txt a csv údajov. Odstrániť duplicity: Veľmi dobre využiteľný príkaz pri tvorbe číselníkov, kde z tabuľky opakujúcich sa záznamov vyberieme len jedinečné záznamy. Napríklad tabuľka hlavičiek faktúr s 5000 riadkami sa dá týmto spôsobom zredukovať na 200 jedinečných odberateľov, ak správne zadáme kritériá. Takáto úloha, bez nástrojov by „ručne“ trvala dlhé minúty. Pivot tabuľky: Pivot tabuľky dokážu z hlavičiek faktúr vyrobiť napríklad prehľadnú tabuľku výkonov po mesiacoch roka. Treba však vedieť ako vhodne upraviť dátumy do tvaru tak, aby sa príslušné mesiace združovali vždy do tej správnej hodnoty. Upravíme to podľa pomyselného vzorca : Dátum = Rok_Mesiac V skutočnosti to budú viaceré vzorce uplatnené asi takto: =Year( Datum ) & “_” & TEXT( Datum; “mm” ) Miroslav Baláž, 10. 02. 2012
