Moderný reporting–pracujeme s MySQL (upravujeme tabuľky)

 

Doposiaľ sme sa naučili založiť si databázu a vytvoriť tabuľky. Dnes si ukážeme ako ich upravovať.

Začneme príkazom, ktorým vymažeme celú tabuľku a ktorý je totožný s príkazom, ktorý vymaže celú databázu:

-- SQL kódy pre manipuláciu s vytvorenými tabuľkami

DROP TABLE dealer; -- SQL vymaže celú tabuľku, teda obsah a stavbu

Za zmienku stojí príkaz DELETE, ktorý maže iba obsah z tabuľky:

DELETE FROM dealer; -- SQL vymaže iba obsah tabuľy, teda záznamy

Avšak bežne sa stane, že po vytvorení tabuľky si uvedomíte, že ste niečo nadefinovali zle, poprípade ste niečo zabudli, povedzme niektorú z potrebných premenných. Tým pádom nemusíte mazať a písať celú tabuľku odznova, stačí ak upravíte už existujúcu tabuľku nasledujúcím príkazom:

ALTER TABLE

Nasledujúca tabuľka predstavuje všetky mnou použité výrazy pre realizáciu nášho projektu:

Pokúsim sa vysvetliť funkcionalitu všetkých príkazov okrem constraint a key prípadov, ktoré rozoberiem podrobne v nasledujúcom článku.

Vytvorme si nasledujúcu tabuľku (nezabudnite použiť niektorú databázu, poprípade si vytvorte skúšobnú – príkaz use alebo create database):

CREATE TABLE tab_1 (Meno varchar(30) NOT NULL, Priezvisko varchar(50) NOT NULL);

Táto tabuľka po zobrazení pomocou select from príkazu vyzerá takto:

Potrebujeme pridať ID, aby sme tabuľku mohli napojiť do vzťahu (relácie).Použijeme ALTER TABLE ADD:

ALTER TABLE tab_1 ADD ID int;

Tento príkaz pridal premennú (stĺpec) s názvom ID, ktorý je celočíselná premenná (int). Ak by sme potrebnú premennú potrebovali nadefinovať ako automaticky generovanú a zároveň primary key, urobili by sme to takto:

ALTER TABLE tab_1  MODIFY ID int auto_increment primary key;

Po nasledujúcich kódoch bude tabuľka vyzerať takto:

Ak by sme zistili, že 30 znakov pre Priezvisko je málo, jednoducho zmeníme tento atribút premennej Priezvisko takto:

alter table tab_1 change Priezvisko Priezvisko varchar(50);

Zostáva nám možnosť, ktorou je DROP. Avšak pridajme si ešte jeden stĺpec:

alter table tab_1 add Vek int(20) not null;

Tým pádom máme tabuľku s ID (Vyplní sa samo),menom, priezviskom a vekom. Vložme do nej zopár hodnôt ako napríklad:

insert into tab_1 (Meno, Priezvisko, Vek) values ('Peter','Tabiš',26),('Martin','Sládek',42),('Roman','Kraus',38);

Tabuľka bude potom vyzerať takto <select*from tab_1;>

Ak si zmyslíme, že vek nepotrebujeme tak, zmažeme rovno celý stĺpec:

alter table tab_1 drop Vek;

Po exekúcii zistíme, že sa celý stĺpec s hodnotymi vymazal. V prípade, že chceme vyprázdniť tabuľku, použijeme kód:

delete from tab_1;

Čo je však prúser, je fakt že akonáhne by sme do takejto tabuľky opäť nahrávali hodnoty, tak prvá už nebude mať ID = 1 ale 4. V tomto prípade je potrebné resetovať aj auto_increment kľúč takto:

alter table tab_1 auto_increment=1;

Potom ak by sme opäť vložili tie isté záznamy, tak ich ID bude postupne opäť 1, 2 a 3.

Nabudúce si ukážeme ako naplniť a prepojiť tabuľky (čo sú to KEY a FOREIGN KEY, CONSTRAINT a INSERT INTO skripty).

PF 2015

Popri písaní článkov o automatizovanom reportingu som si už ako každý rok našiel čas a pripravil excelovskú špecialitku vo forme pour féliciter vizualizácie.

 

Týmto príspevkom by som sa zároveň chcel poďakovať čitateľom za tohtoročnú priazeň, zákazníkom za prejavenú dôveru a do nového roku popriať každému veľa síl a trpezlivosti, ktoré predchádzajú pravému úspechu.

 

Pevne verím, že tieto články boli a naďalej zotrvajú pre mnohých dostatočnou motiváciu k proaktivite a sebavzdelávaniu. Zároveň budem robiť všetko pre to, aby v podnikateľskom prostredí aj naďalej vzbudzovali pocit, že analytická práca nie je pre mňa iba prácou, ale spoločenským poslaním.

 

Nezabúdajte, že rok 2015 bude rokom úrodným na články a ako to už býva vo zvyku, každý Nový rok sa obvykle nesie vo forme predsavzatí.

 

Budem preto veľmi rád, ak sa v budúcom roku stanem autorom tak zaujímavých článkov, ktorými spoločne pokoríme hranicu 100 000 návštev.

 

 

S pozdravom

  Peter Tabiš

 

 

‘======================================================================

Tento PF_2015 súbor si môžete stiahnuť kliknutím na download obrázok. V prípade, že by sa procedúra nerealizovala správne, k dispozícii je nasledujúce promočné video:

 

 

 

 

 

Moderný reporting–pracujeme s MySQL (prvé príkazy)

 

V predchádzajúcom článku sme si ukázali, ako nainštalovať MySQL. Tak si otvorme MySQL Workbench. Po kliknutí na Local instance MySQL56 sa nám zobrazí dialógové okno, kde od nás program žiada heslo (to, ktoré sme zadávali pre root užívateľa pri inštalácii).

 

 

Po prihlásení sa nám objaví prostredie, kde môžeme programovať databázy a nasledne z nich vyťahovať data apod. V nasledujúcom obrázku som do listu Query 2 zapísal dva kódy. Prvý z nich vytvorí databázu s názvom pokus a druhý ukáže mená všetkých dosputných databáz.

 

Po kliknutí na blesk (hneď vedľa diskety) nám program exekuuje všetky napísané kódy vrámci listu. Pokiaľ by sme klikli na druhý blesk od diskety , program realizuje len ten skript, na ktorom je aktívny kurzor. Po kliknutí na prvý blesk výstup vyzerá takto:

 

 

Prvý príkaz vytvoril databázu s názvom pokus, a druhý vypísal v okne Result všetky dostupné databázy. V okne Output sú zobrazené informácie o stave, čase a prípadných chybách, ktoré vznikli pri exekúcii kódu.

===============================================================================================Základné SQL príkazy:===================================

Príkaz, ktorým vytvoríme databázu:

create database názov databázy;

Príkaz, ktorým vymažeme databázu:

drop database názov databázy;

Ak chcete vidieť zoznam všetkých databáz, použijete nasledujúcúci príkaz:

show databases;

V prípade, že chcete v MySQL prostredí používať poznámky, t.j. písať si niečo, čo program nebude považovať za kód, použijete dve pomĺčky:

-- toto je časť textu, ktorú MySQL nebude považovať za kód …..

Ak to zosumarizujem, vieme si vypísať názvy databáz, dokážeme si vytvoriť databázu a taktiež ju vymazať + bonus: písať si poznámky k rôznym kódom.

K tomu, aby sme sa mohli hrať s konkrétnou databázkou je nutné povedať programu, nech ju používa. Preto použijeme:

use názov databázy;

Každá databáza musí mať svoju kostru (tabuľky a relácie). O spôsobe tvorby databáz som písal už predtým, no každopádne za zmienku stojí urobiť si plán:

1) – nakresliť si pavúka

2) – Pripraviť si tabuľky podľa pavúka

3) – Vložiť data z Excelu do tabuliek

4) – prepojiť tabuľky relačnými vzťahmi

5) – špekulovať, čo užitčné by nás ako analytikov / managerov / majiteľov zaujímalo

Posledný bod budeme rozoberať už v súvislosti s produktom Jaspersoft. Vrátim sa však k prvým dvom bodom. Prvý bod som v konečnom dôsledku predstavil v predchádzajúcich článkoch, no pre vizualizáciu nasledujúci obrázok predstavuje realizáciu druhého bodu (všetky tabuľky).

Tabuľku si vytvoríme pomocou nasledujúceho príkazu:

create table názov tabuľky  (Názov stĺpca Dátový typ, … );

Tento príkaz sa na prvý pohľad zdá byť elementárny, ale skutočnosť je tak trochu náročnejšia na pochopenie. Ak sa pozrieme na tabuľku dealer, jej vytvorenie vyzerá takto:

CREATE TABLE dealer (ID int(11) NOT NULL AUTO_INCREMENT, Predajca varchar(30) NOT NULL,   PRIMARY KEY (ID));

Každá z tabuliek, stvárnených v predchádzajúcom obrázku pozostáva z ID stĺpca. Tento stĺpec je automaticky generovaný a neprázdny, t.z. že každému riadku musí prináležať konkrétne – špecifické číslo. Z tohto titulu auto-generáciu deklarujeme kódom AUTO_INCREMENT a NOT NULL deklarujeme neprázdny charakter stĺpca (hodnoty stĺpca pre každý riadok nesmú byť prázdne).

Pomocou PRIMARY KEY atribútu určime, ktorý zo stĺpcov v tabuľke bude primárny kľúč (napríklad ak  hodnote 1 priradíme meno Tabiš, bude to znamenať, že ak v akejkoľvek inej tabuľke, ktorá sa nachádza v databáze zapíšeme predajcu číslom 1, budeme vedieť že sa v praxi jedná o Tabiša).

Posledný významný atribút je dátový typ. My okrem názvu stĺpca musíme programu definovať, aké typy hodnôt tam budú. V našej vzorovej databáze sa stretneme s typmi: INT(počet cifier) –celé číslo s definovaným maximálnym počtom cifier, DECIMAL(počet cifier,desatinné miesta) –Počet cifier a desatinných miest, VARCHAR(počet znakov) – textový reťazec s maximálnou dĺžkou, resp. počtom znakov.

Kliknutím na tutorialspoint.com máte k dispozícii podrobný popis ďalších datových typov, poprípade na strane 179 je veľmi podrobne opísaný rozbor dátových typov, spolu s definíciou ich parametrov (kniha Michael Kofler – mistrovství v MySQL 5).

Ak sa vrátim k nášmu pavúkovi, prehľad kódov pre jednotlivé tabuľky bude vyzerať takto:

-- SQL kódy pre tvorbu všetkých tabuliek vrámci SALES databázy

CREATE TABLE dealer (ID int(11) NOT NULL AUTO_INCREMENT, Predajca varchar(30) NOT NULL,   PRIMARY KEY (ID));

CREATE TABLE car (ID int(11) NOT NULL AUTO_INCREMENT, Auto varchar(30) NOT NULL, Cena decimal(10,2) NOT NULL ,PRIMARY KEY (ID));
CREATE TABLE city (ID int(11) NOT NULL AUTO_INCREMENT, Mesto varchar(30) NOT NULL, PRIMARY KEY (ID));

CREATE TABLE cost (ID int(11) NOT NULL AUTO_INCREMENT, Predajca int(11) NOT NULL , Mesiac int(11) NOT NULL , Rok int(11) NOT NULL, Mzda decimal(10,2) NOT NULL , Sprava decimal(10,2) NOT NULL , Bonus decimal(10,2) ,  PRIMARY KEY (ID));

CREATE TABLE discount (ID int(11) NOT NULL AUTO_INCREMENT, Zlava decimal(2,2) NOT NULL , PRIMARY KEY (ID));

CREATE TABLE sales (ID int(11) NOT NULL AUTO_INCREMENT, Naklady int(11) NOT NULL, Predajca int(11) NOT NULL, Mesto int(11) NOT NULL, Auto int(11) NOT NULL, Diskont int(11) NOT NULL, Predaj int(11), Mesiac int(11) NOT NULL, Rok int(11) NOT NULL, PRIMARY KEY (ID));

Nabudúce si ukážeme dodatočné kódy, ktorými budeme manipulovať s tabuľkami. Nezabúdajte na like a prípadné dotazy na FB.

Pre tých, ktorým práca s vytváraním databáz nevyhovuje, resp. ju nezvládajú. Pokúsim sa umožniť stiahnúť kompletne hotovú databázku spolu s dátami, takže pre Vás v budúcnosti budú kľúčové manipulácie s Jaspersoft a MySQL.

Moderný reporting–inštalujeme databázový systém MySQL

 

Začneme základným problémom, ako si nainštalovať MySQL. Ak si do Google-u zadáte “MySQL download” navedie Vás na stránky dev.mysql.com/downloads/. V nej prejdeme na sekciu MySQL Community Server DOWNLOAD.

Nasledujúci obrázok nás naviguje na Download MSI installer (viď obrázok).

V ďalšej stránke máme na výber inštalačné verzie, ja si vyberiem väčšiu – 287.0 M.

Inštalačný proces prebieha na prvý pohľad jednoducho až kým sa nedostanete na nasledujúce dialógové okno:

Toto okno reprezentuje zoznam programov (aplikácií), ktoré inštalátor potrebuje pre dokončenie inštalácie MySQL. Programovací jazyk Python 3.4 sa inštaluje kliknutím na nasledujúci odkaz: python.org/downloads/ kde klikneme na Download Python 3.4.2 (užívatelia 2.7 verzie budú musieť čeliť dileme zmeny verzie):

Po stiahnutí je inštalačný proces intuitívny, nie je potreba nič špeciálne nastavovať (označime I agree, klikame na next a nakoniec finish) .

 

Klikneme na Next > adresár si môžeme podľa potreby zmeniť, no ja ho nechávam pôvodne nastavený.

Ďalšou úlohou bude inštalácia Visual Studia. Kliknite na stránky Visualstudio.com, a nechajte si stiahnuť bezplatnú Express edition vo forme ISO. Potom stačí nainštalovať si Visual Studio (všetky časti v nasledujúcom obrázku):

Takto pokračujte až kým nebudete mať k dispozícii všetky doplnky, vyžadované inštalátorom MySQL. Potom opäť otvorte MySQL inštalátor a pokračujte kliknutím na Execute možnosť.

Spustí sa inštalácia, ktorej priebeh vyzera nasledovne: 

Ďalšie kroky sú viac-menej intuitívne, jediné čo si musíte zvážiť je heslo pre prihlasovanie ako ROOT užívateľ. Pomocou neho sa totiž prihlasujete k danej databázke cez Excel a iné aplikácie (Workbench, v ktorom budeme pracovať).

Po inštaláci sa stačí preklikať ku konfigurácii resp. dodatočným nastaveniam a úspešne to ukončiť kliknutím na Finish. Na ploche alebo štart lište by sa mala objaviť ikonka s delfínom.

po niekoľkých sekundách saotvorí MySQL Workbench, ktorý vyzerá takto:

V budúcom článku si ukážeme ako pracovať v MySQL workbench prostredí a pripravíme si databázu spolu s tabuľkami (začneme používať SQL dotazy). V prípade dotazov som k dispozícii na Facebooku alebo linkedin-e.

Moderný reporting–príprava podkladu (Relačná databáza)

 

Než sa začneme hrať s dashboardmi v Jaspersoft, potrebujeme dátovú základňu. Začnem v niečom, kde sa cítim aspoň trošku “doma”.

Je to MySQL databáza.

V tomto programe si vytvoríme tzv. relačnú databázu. Takže než si nainštalujeme daný program, chcel by som napísať zopár pojmov a užitočných odkazov, ktoré pomôžu. Začnem však veľmi užitočnými článkami od p. Petra Zajíce na LINUXSOFT o MySQL všeobecne.

Pre tých, ktorí si radi po večeroch čítajú knižku odporúčim knihu “mistrovství v MySQL” od Michaela Koflera, ktorá vyzerá takto:

Priznám sa, že čo nedokážem nájsť na nete, tak hľadám v tejto biblii. Vrátim sa však k pojmu relačný databázový systém. Oficiálnu definíciu Vás nechám vygoogliť si, ja prejdem k podstate, ktorú vysvetlím tak trochu “excelovsky”.

Predchádzajúca tabuľka je súbor dát, kde stĺpce reprezentujú rôzne premenné (Kto, Kde, Kedy, Koľko a za Koľko predal tovar) a spoločne definujú jeden záznam, teda jedného človeka. Tým pádom, riadok = záznam.

Pozrime sa na túto tabuľku špekulačne – koľko pamäte môže zabrať? Túto otázku budem demonštrovať počtom znakov v každej bunke (Pre výpočet použijem funkciu LEN(), ktorá vráti počet znakov v bunke).

Vidíme, že táto malá databázka spotrebuje pamäť o veľkosti 467 znakov. Dá sa však toto množstvo redukovať? Odpoveď znie, áno. Prvé dva stĺpce sa totiž opakujú, preto ak napríklad meno Peter zakódujeme číslom 1 a mesto Praha taktiež, vytvorí sa nám zoznam Predajcov a Miest:

Tým pádom, môžeme pôvodnú tabuľku upraviť tak ,že miesto plného názvu mesta a mena predajcu vložíme iba dané tzv. referenčné číslo, alebo kľúč. Tabuľka bude mať v žltých stĺpcoch iba jedno číslo namiesto názvu a celkový počet znakov sa v tabuľke zredukuje na 286.

V prípade, že chceme urobiť pivotku, stačí doplniť pôvodné názvy cez funkciu VLOOKUP() a máme k dispozícii plnohodnotný dataset. Musím podotknúť, že sa pohybujeme v Exceli. MySQL funkciu VLOOKUP nepotrebuje, resp. v tomto prostredí sa nazýva relácia / integrita, ktorá sa medzi tabuľkami využíva pri spájaní.

Ďalšou výhodou je, že ak máme napr. pod číslom 6 Pardubice a zrazu sa niečo stane (Pardubice sa zlúčia s mestom Chrudim a vznikne Pardudim), tak nám stačí pod číslom 6 zmeniť názov a zvyšné data sú vždy aktualizované (počítač alebo my nemusíme ručne prepisovať každý riadok).

Ostanem dúfať, že tento popis stačí na to, aby ste si uvedomili silu a taktiež slabiny takéhoto relačného prepojenia tabuliek.

Existuje niekoľko metód, ako si takto pripraviť záznamy a rozkrájať ich na podtabuľky. Tieto metódy sa nazývajú Normalizačné pravidlá. Tie sú podrobne rozpísané v mnou spomínanej knihe na strane 191. Internetovo založení čitatiela si kliknú na Linuxsoft.

Tieto definície nebudem spomínať, prejdem však k vecnej logike. Pri konštrukcii databáze je veľmi dôležité si uvedomiť, čo s ňou potom budete robiť. Inak povedané, absolutne normalizovaná databáza môže byť nočná mora pre užívateľov, ktorí chcú selektovať data z viacerých tabuliek – vtedy skripty dosahujú veľkosti papiera A4 a ľahko sa pri manipulácii dá pomýliť! Na druhú stranu akákoľvek zmena popisných atribútov je jednoducho aplikovateľná.

Asi najlepší spôsob ako dosiahnúť cit je urobiť si vlastnú databázku a potom sa s ňou hrať, no rozhodne to nie je problematika na zopár článkov na blogu. Ja sa však chcem sústrediť na analytickú prácu, tým pádom náš relačný model mám hotový a bude vyzerať takto:

Tento pavúk sa tiež nazýva ERR diagram a slúži na to, aby sa užívateľ zorientoval vo funkcionalite a mohol komfortne selektovať. Len pre pochopenie vzťahov si predstavte, že jednotlivé prerušované čiary sú VLOOKUP funkcie medzi tabuľkami a to tak, že tabuľka z ktorej vychádza trojuholník je tabuľka, do ktorej sa ťahajú informácie z druhej tabuľky. Takže napríklad tabuľka DEALER dopĺňa data do tabuliek COST a SALES.

V budúcom článku si ukážeme ako nainštalovať MySQL a ako vytvoriť databázu spolu s tabuľkami. Kliknutím na odkaz download si môžete stiahnúť Excel s tabuľkou a počtami znakov. Kliknite tiež na facebook a pýtajte sa ak niečomu nerozumiete – potrebujete pomoc .

            

Moderný reporting – Sylabus

 

Ako som už spomínal v predchádzajúcom článku, je na čase naučiť sa niečo nové. Nasledujúce články pojmem v štýle “learn by repeat” a začnem tým, že nadefinujem jednoduchú case study.

Majme na starosti štatistiky predaja automobiliek Škoda vrámci rôznych regiónov v Českej republike.  Momentálne disponujeme predajnými miestami v nasledujúcich mestách: Brno, Hradec Králové, Liberec, Mladá Boleslav, Olomouc, Pardubice, Plzeň a Praha. Slovíčkom “momentálne” som chcel naznačiť, že tento zoznam sa bude meniť v čase, taktiež ako dealeri, ktorí nám zaisťujú tržby:  Peter, Martin, Roman, Milan, Lukáš, Eva, Martina, Angelika, Natália, Jana, Mária, Alžbeta a Markéta.

Čo nás zaujíma je samozrejme zisk. K nemu okrem tržieb patria náklady, spojené s predajom. Takže potrebujeme priebežne evidovať:

1) Koľko ktorý predajca predal áut v danom regióne, pričom platí, že predajcovia kolujú medzi predajňami.

2) Aké auto predal - každý model má inú cenu/maržu a Škoda auto momentálne disponuje modelmi: Citigo, Fabia, Roomster, Rapid, Yeti, Octavia a ich vlajková loď Superb. Tým pádom je jasné, že sa nám bude lepšie dariť, ak predáme 10 Superb-ov, nadopovaných nie skromnou výbavou oproti 10 Citigo vozíkom, určeným na nákupy a ku kaderníkovi.

3) Náklady spojené s predajom alebo odborne Cost of Sales. Pod nimi si môžeme pre jednoduchosť predstaviť rôzne promo akcie, výstavy v supermarketoch, víkendové dni a iné. Ďalšou súčasťou Cost of Sales sú zľavy na automobil.

4) Mzdové hodnotenie dealerov spolu s bonusovým systémom, aby boli dostatočne motivovaní predávať a predávať.

Pre jednoduchosť nebudeme uvádzať náklady na výrobu automobilu a ďalšie detaily. Taktiež podotknem, že akékoľvek údaje o predajoch a cenách sú generované v MS Excel a nemajú akýkoľvek súvis s realitou v automotive prostredí.

Začneme tým, že si vytvoríme nasledujúcu schému:

Inak povedané, nastavíme si proces tak, že predajcovia budú mať k dispozícii Excel súbory, ktoré budú prebežne vypĺňať a k určitému dňu v mesiaci odosielať požadované informácie do databázy. Ako ste si všimli z obrázku, mám tam logá dvoch softwareových riešení pre tvorbu databázy.

Začneme tým, že si ukážeme, ako pripraviť relačnú databázu v MySQL a ako challenge som si zaumienil pripraviť riešenie v MongoDB, ktorá je NoSQL typu.

Takto pripravené  databázy napojíme na prostredie Jaspersoft a ukážeme si, ako pripraviť automatizovaný reporting. 

V nasledujúcom článku začneme konštrukciou relačnej databázy v MySQL – Step-by-Step. V prípade dotazov neváhajte kliknúť na facebook .

Schopnosti kvalitného analytika v budúcnosti

 

Je rozhodne v poriadku ak sa aj napriek spokojnosti s terajším zamestnaním alebo popri štúdiu zaujímate o pracovný trh. Dovolím si tvrdiť, že vzhľadom k dnešnej dobe je táto aktivita priam nevyhnutná.

Pri pohľade na ponuku analytických prác či už na domácom trhu alebo v zahraničí môžem konštatovať, že nároky sa čím ďalej, tým viac sprísňujú.

Pre ilustráciu: Ak ste pred piatimi rokmi ovládali VLOOKUP a kontingenčné tabuľky, tak ste patrili medzi žiadaných ľudí pre rôzne organizácie. V súčasnosti so znalosťou VBA a skriptovaním v SQL taktiež nemáte núdzu o zamestnávateľov, no tvorba pivotiek už nie je terno.

Čo však bude o 5, 10 alebo 15 rokov? To samozrejme nevie nikto a ja sa taktiež neodvážim predpovedať z krištáľovej gule. Avšak message tohto článku bude odzrkadľovať zopár postrehov, z ktorých si každý môže vytvoriť vlastný úsudok.

Prvý postreh: Rozvoj IT. Je fakt, že pred zopár rokmi bol pojem cloud pre 90% ľudí neznámy, respektíve si pod ním predstavili krásny obláčik na nebíčku. Len zopár zainteresovaných písalo svoje diplomové a dizertačné práce na túto problematiku. V súčasnosti tento pojem nabral úplne iný rozmer a množstvo firiem, ktoré chcú mať všetky kľúčové informácie o svojom biznise k dispozícii či už v tablete alebo smartphone rastie.

Druhý postreh: V súčasnosti sa často skloňuje pojem Dashboard. Jeho dostatočne výstižná definícia znie takto:

“A business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators (KPIs) for an enterprise. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen. They may be tailored for a specific role and display metrics targeted for a single point of view or department.”

Zdroj: http://searchbusinessanalytics.techtarget.com/definition/business-intelligence-dashboard

Niekto by mohol oponovať, že sa jedná o problematiku Business Inteligence prostredia, avšak pri pohľade na vývoj MS Excel od 95 do 2013 Microsoft urobil významný pokrok. Spomeniem nástroj Power View, ktorého tvorbu som popísal v predchádzajúcich článkoch a ktorý vo finále môže vyzerať napríklad takto (Zdroj web):

Na druhú stranu ukážka dashboard-u, vytvoreného v Jaspersoft studio vyzerá takto (zdroj web):

Budeme súhlasiť, že tieto obrázky sú na prvý pohľad rovnaké. Rozdiel je akurát v tom, že druhá ukážka je zobrazená vo webovom prehliadači a nie je probém tento výstup exportovať a stiahnuť na disk vo forme excelu prakticky kedykoľvek. Túto výhodu však môžeme eliminovať schopnosťou Exelu komunikovať so serverom, ale spotrebovalo by to veľa času, námahy a vo finále by to nedosiahlo komfort, porovnateľný s druhým riešením.

Tretí postreh: Množstvo dat rastie spolu so spoločnosťou. Tento fakt ukážkovo ilustruje napríklad spoločnosť Alza (otravný marťan, ktorý sa nám snaží predať čo najviac elektroniky), ktorá vznikla vo forme živnostenského listu v roku 1994 a v súčastnosti je to obor s obratom cez 9 miliárd CZK ročne. Predstava, akým rozvojom museli prejsť skladovacie možnosti, nákup, distribúcia, monitorovanie zákazníkov, vývoj portfólia, personálne zmeny atď. vyvoláva husiu kožu. 

To všetko sa samozrejme muselo priebežne monitorovať a vyhodnocovať, čo je aspoň pre mňa ešte viac zamrazujúce. Tým pádom si dovolím tvrdiť, že podobné spoločnosti riešia konsolidáciu rôznych databázových skladov a za každú cenu sa snažia štandarzdizovať celý “analytics” proces do jedného celku.

Môj úsudok:

S rozvojom hardware a software sa analytické možnosti rozširujú, tým pádom aj požiadavky na analytika. Často sa stáva, že ich v oddelení ubúda, no agenda a zodpovednosť spolu s ich hodnotou rastie.

Čo sa týka pracovnej náplne, analytici budú musieť zčasti prebrať prácu developera nakoľko ich problematikou už nebude len vyriešiť monitoring dodatočných zmien v biznise, ktoré nebol schopný zachytiť software, ale zároveň ich doň efektívne implenetovať.

Moderný analytik by tým pádom v budúcnosti mal mať taktiež osvojenú problematiku fungovania databáz, základy programovania, ovládanie visual studios pre tvorbu a kontrolu dashboardov, programovací jazyk – orientovaný na web alebo iný BI tool.

Spomenuté hard skills sú cenné hlavne ak má cit pre rozumnú interpretáciu dat. Hovorím o nastavení lievika, ktorý filtruje všetky dostupné informácie tak, aby ich prezentácia bola zrozumiteľná cieľovej skupine, čo prirodzene vyžaduje hladké pochopenie chodu biznisu a ekonomikých pojmov.

Záver:

Excel, aj napriek jeho markantnému vývoju v budúcnosti nebude stačiť tržným nárokom a človek okrem nadobúdania citu pre biznis by sa mal neustále zdokonaľovať vo využívaní analytických nástrojov. Takže nám neostáva nič iné iba pracovať, učiť sa a skúšať nové nástroje, objavovať nové trendy.

Z tohto dôvodu som sa rozhodol implementovať do blogu ďalšie nástroje, ktoré súvisia s analytickou prácou a perfektne doplňujú funkcionalitu Excel-u a zároveň moderznizujú kvality súčasných analytikov.

Ako spočítať unikátne názvy v bunkách alebo počet hodnôt bez duplicít

Po niekoľkých minútach hľadania na internete sa dostanete na stránky MS OFFICE, kde majú napísaný základný prehľad vzorcov. Určite mi dáte za pravdu, že nevyzerajú vôbec prívetivo a v prípade, že by sme chceli rozšíriť danú podmienku bez duplicít, tak to nemusí byť záležitosť 5 minút (aspoň pre mňa nebola).

Prejdime k príkladu, kde mám vygenerovaný zoznam 57 mien, z ktorých je presne 12 jedinečných, teda neopakujúcich sa.

V tomto prípade bol použitý nasledujúci vzorec:

{=SUM(IF(FREQUENCY(IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""); IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""))>0;1))}

Špeciálne zátvorky znamenajú. že ide o formu maticového počtu (napísaný vzorec aktivujeme pomocou Ctrl + Shift + Enter). Prejdime však na rozbor tohto vzorca:

IF(LEN($G$3:$G$59)>0;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Prvý stĺpec analyzuje bunky v stĺpci takto: Ak je dĺžka textového reťazca nenulová (t.z., že sa v bunke nachádza text), tak vráti pozíciu textu v stĺpci (Keďže meno Bernard sa nachádza ako 2. v poradí v tabuľke, tak funkcia MATCH() vráti číslo dva v oboch, oranžovo označených prípadoch). Ak by bunka bola prázdna, tak funkcia vráti prázdnu hodnotu “”.

{=FREQUENCY($H$3:$H$59;$H$3:$H$59)}

Ďalším kľúčovým prvkom je funkcia FREQUENCY(), ktorá funguje maticovým spôsobom. V nej uvedieme, že chceme pre každú hodnotu vo vypočítanom stĺpci nájsť, koľkokrát sa  v rámci stĺpca nachádza.

{=IF(I3:I59>0;1;0)}

Posledná podmienka už len vyhodnotí výsledok vzorca FREQUENCY() v zmysle, že ak je frekvencia daného čísla v tabuľke nenulová, tak vráti 1 ako jedinečná, inak 0 ako duplicita.

Posledná sčítacia funkcia už len spočíta nenulové hodnoty v predchádzajúcom stĺpci a tým pádom vráti počet mien bez opakovania.

Pokúsme sa pridať ďalšiu obmedzujúcu podmienku. Chceme vedieť počet špecifických mien, ku ktorým prináleží vek nad 30 rokov.

Ak si vyfiltrujeme všetky mená, ktoré majú vek nad 30 rokov a použijeme predchádzajúcu funkciu, zistíme, že máme už len 11 mien (viď template). Avšak my nechceme zbytočne filtrovať a upravovať rozsahy vo vzorci. Chceme pridať alebo pozmeniť len časť predchádzajúceho vzorca.

Pozmeníme preto hneď prvú časť vzorčeka takto:

         Definujeme si podmienku vek nad 30 rokov - IF($F$3:$F$59>30;1;0)

         Definujeme si podmienku neprázdna bunka - IF(LEN($G$3:$G$59)>0;1;0)

Ak obe podmienky platia, ich súčet musí dať dohromady číslo 2. Takže platí:

=IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Červenou farbou je zvýraznená zmena oproti pôvodnej podmienke. Ostatné vzorčeky sa nemenia, avšak aby sme obišli problém s výsledkom #N/A pri niektorých neplatných hodnotách, prerobíme poslednú sčítaciu funkciu na:

SUMIFS($R$3:$R$59;$R$3:$R$59;">0")

Takto spojené podmienky a funkcie budú vyzerať nasledujúco (pri spájaní do jednej funkcie použijeme len SUM() a nie SUMIFS(), lebo sa jedná o maticový počet):

=SUM(IF(FREQUENCY(IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""); 
IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""))>0;1))

Na základe použitého princípu môžeme pridať oveľa väčší rozsah podmienok. Stačí ak hodnoty, vstupujúce do FREQUENCY() sú založené na báze Ak{ (Platí Podmienka 1 = 1 inak 0) + (Platí Podmienka 2 = 1 inak 0) + (Platí Podmienka 3 = 1 inak 0) + … + (Platí Podmienka n = 1 inak 0) } = n tak nájdi pozíciu textu v rámci tabuľky, inak vráť prázdnu hodnotu.

Jedným z ďalších riešení je použiť VBA a naprogramovať si niečo ako vlastný COUNTIFS() bez duplicít. Čo môže byť obsahom nasledujúceho článku. Každopádne template na stiahnutie spolu s odkazom na web a facebook je k dispozícii kliknutím na nasledujúce obrázky:

Ako Excel upraví Word a uloží do PDF: VBA automatizácia

Máte štandardné wordovské dokumenty ako zmluvy, vyúčtovacie formuláre, prihlášky atď.. V nich potrebujete zmeniť zopár hodnôt ako dátum, meno, priezvisko, vek atď.. Tieto dokumenty sa v prevažnej miere ukladajú ako historické podklady, tlačia a posielajú koncovým adresátom.

Problém je však v tom, že tie hodnoty sa nachádzajú v Excel-i a my ich potrebujeme dostať do Word-u.

Tí z Vás, ktorí tieto veci robia ručne, tak pozor! … Existuje na to makro .

 

Príklad: Pripravme si najprv Word dokument s názvom Dokument.docx, ktorý bude vyzerať takto:

 

 

 

Na obrázku vidíme, že hodnotu, ktorú plánujem meniť podľa zoznamu v Exceli je Zmena dňa:. Tým pádom si vytvorím záložku (Bookmark) Označím oblasť vo Worde – Insert – Bookmark:

 

Túto oblasť pomenujem ako DEN a potvrdím voľbu kliknutím na Add:

 

Tým pádom som si pripravil predlohu vo Worde, ktorú potrebujem obmieňať o špecifický zoznam z Excelu. Ten bude jednoduchý:

Máme pripravený Word a Excel. Teraz nám stačí vytvoriť si VBA procedúru tak, aby po kliknutí na tlačítko sa otvoril Word, vložili doň hodnoty a povedzme aby sa dokument uložil (aj vytlačil, záleží od toho čo potrebujeme) pod špecifickým názvom ako PDF, v našom prípade “konkrétny deň.pdf”.

 

Sub PDF_word()

Dim Meno As String
Dim WAPP As ObjectDim WDOC As Object
Dim EWB As Workbook: Set EWB = ThisWorkbook
Meno = "Dokument.docx"

'=================Aktivujeme Word=======================================
'======================================================================  
Set WAPP = CreateObject("Word.Application")
WAPP.Visible = True
Set WDOC = WAPP.Documents.Open(ThisWorkbook.Path & "\" & Meno)
     
'======================================================================
'======================================================================
For i = 3 To 9
        Dim denRNG: Set denRNG = WDOC.Bookmarks("DEN").Range
            denRNG.text = EWB.Sheets("ZOZNAM").Cells(i, 2).Value
        WDOC.Bookmarks.Add "DEN", denRNG
        WAPP.ActiveDocument.SaveAs2 ThisWorkbook.Path & "\" & Cells(i, 2).Value & ".pdf", 17
Next i

WAPP.Quit savechanges:=wdDoNotSaveChanges

End Sub

Výsledok vyzerá nasledovne:

V referenčnom adresári sú okrem vzorového wordu a excelu s markom uložené kópie dokumentov podľa dňa v týždni.

Cvičný súbor si môžete stiahnuť kliknutím na nasledujúci odkaz. V prípade dotazov, alebo ak by ste mali záujem o takéto riešenie na mieru: