Ako si vypočítať hypotéku – Excel

 

Táto téma je v posledných rokoch často skloňovaná. Pri istom množstve kalkulačiek apod. síce máte k dispozícii výpočet anuity. Avšak niekedy je zaujímavé si porovnať, koľko percent z anuity predstavuje platba úrokov v banke a aký je jej vývoj.

Parametre nástroja:

1) Úroková sadzba sa za celé obdobie nemení (každá kalkulačka používa danú podmienku)

2) Doba fixácie je 5 rokov

3) obdobie sa pohybuje v rozmedí od 10 do 30 rokov s 5 ročnými intervalmi

4) Úroková báza je vždy 12 mesiacov

Vzorčeky:

Na použitie výpočtu anuity slúži nasledujúci vzorček:

=PMT(Úroková sadzba/12;Obdobie*12;Cena nehnuteľnosti - Vlastné prostriedky)

Kde vám vyjde záporná hodnota, preto odporúčam to ešte prenásobiť –1-čkou. V prípade, že chceme vidieť splatnú čiastku z každej anuity individuálne, použijeme nasledujúci vzorček:

=PPMT(Úroková sadzba/12;Číslo splátky;Počet splátok celkom;Cena nehnuteľnosti -Vlastné prostriedky)

Zvyšok je už len kompilácia jednoduchých funkcií a logiky tak, aby ste pri zadávaní parametrtov dbali iba na nasledujúce:

1) nadefinovali cenu nehnuteľnosti

2) Vlastné zdroje

3) Sadzbu (p.a.)

4) Koľko rokov chcete splácať

 

Výsledok je farebne označený, tak, aby ste videli predpokladanú mesačnú splátku a o koľko nehnuteľnosť preplatíte danou hypotékou:

Súbor s individuálnou fixáciou alebo zmenou sadzby počas refixácie si môžete vyžiadať prostredníctvom správy na facebooku, stačí kliknúť na odkaz:

Excel ERP systém – úvod

 

Za posledný rok som sa vrámci Excel vývoja stretol s rôznymi požiadavkami od klientov, ktoré boli v rozmedzí priam nereálnych až po vskutku rozumné.

Čo však mali všetky tieto požiadavky spoločné je business mapping. Dovolím si tvrdiť, že 90% ľudí považuje Excel ako ”iba” editor tabuliek a grafov. Niektorí by boli najradšej za jeho neexistenciu, ale či sa nám to páči alebo nie:

- Excel je editor ktorý je užitočné ovládať

- Excel používame na správu a návrh obsahu, ktorý nám pomáha rozhodovať sa – decision making support

- Excel vylepšujeme tak, aby nám údržba obsahu zaberala čo najmenej času

V Exceli, ak je človek šikovný tak dokáže urobiť defakto malý ERP systém bez toho, aby míňal státisíce za softwareové riešenie “na mieru”.  Nastáva moment pre rozlúštenie kľúčovej otázky:

Ako zistím, že je čas pre hľadanie analytika alebo outsourcing?

Nasledujúci obrázok prezentuje stav, kedy zainteresovaná osoba investuje neúmerné množstvo času, zatiaľ čo kvalita výstupov je pri najlepšom rovnaká. Je to stav, keď firemná komplexita naberá na obrátkach a reporty už nemapujú to, čo je potrebné.

Na druhú stranu firma nie je tak veľká aby potrebovala nového analytika za tisíce € mesačne alebo software za desiatky tisíc €.

Túto otázku Vám pomôžem rozlusknúť prípadovou štúdiou jednej menšej firmy spolu s úspešne implementovaným  riešením.

Predstavte si malý podnik, založený na zákazkach, ktoré sú realizované vo forme produktov (Spotreba materiálu a ľudskej práce). Štruktúra firmy nie je komplexná. Pozostáva z 2 vlastníkov, účtovníka a riaditeľa financií v jednej osobe (ďalej len finančník) a približne 18 pracovníkov, z ktorých traja sú hlavní pracovnící, zodpovední za evidenciu dochádzky a spotreby materiálu.

Ich proces fungovania a objekty, dôležité pre mapping popisujú nasledujúce obrázky:

Pre priblíženie komplexity nasledujúca schéma zobrazuje niektoré vzťahy, potrebné k mapovaniu:

Excel audit:

Firma pôvodne disponovala niekoľkými excelmi, ktoré sa pravidelne snažili udržiavať. Vstupy do exelov zaznamenávali traja hlavní pracovníci. Analytickú časť používa “finančník” a vlastníci.

Z hľadiska excelovskej znalosti su vedúci pracovníci na začiatočníckej úrovni. Zvládajú súbor otvoriť, zadať data a uložiť súbor (Nutná podmienka je prísť s tzv. “entry and save” riešením).    Finančník má upper-intermediate level znalosti. Ovláda financie a funckie s nimi späté, dokáže si pripraviť kontingenčnú tabuľku a pokúša sa používať nahrávané makrá.

Zákazník dodal k analýze samotné súbory:

Ako sa očakávalo, attendance s veľkosťou 322 kB obsahoval všetky vstupné data. Súbor Bridge následne urobil potrebné prepočty podľa vštkých dimenzií. Súbor contracts používa dataset zo súboru bridge viackrát pre následnú analýzu.

Body, k vylepšeniu:

- Vstupy sa neukládajú do databázovej podoby. Táto vlastnosť je dôležitá, lebo vedie k efektívnejšej práci vrámci excelu a zároveň tieto data sú potom modularizované (dajú sa použiť v inej aplikácii). Náklady na čistenie dát pri implementácii podnikového systému sú potom drahšie ako samotné kopírovanie zo správnej tabuľky

- Žiadna validácia. Prierezom cez všetky excely užívateľ nemá problém s vložením a analýzou nezmyselných hodnôt. Všeobecne neexistuje horší scenár ako mať zle zmapovaný biznis.

- Používanie nahraných makier. Nahrané makrá sú veľmi senzitívne na takmer akúkoľvek úpravu v reportoch. Stačí pridať alebo odstrániť riadok a procedúry vám v lepšom prípade vrátia chybu, v horšom rozbijú celý excel. Pre efektívne používanie makier je potreba nahrané procesy rozšíriť o používanie:

Deklarácia premenných

Validačne pravidlá

Operátory: IF / ELSE / END IF, FOR / NEXT, AND / OR apod.

- Komplexita. Pribúdaním nových zákazok, zákazníkov a striedaním zamestnancov sa stáva existujúce riešenie do budúcna prakticky neovládateľné.

Takzvané “high level” riešenie by malo vyzerať nasledujúco:

Architektúra je založená na vstupných formulároch s validačnými pravidlami, do ktorých užívateľ zadá hodnoty a uloží. Následne v pozadí beží proces výpočtov a makier, ktorý hodnoty pripraví na uloženie do databázových listov. Data budú tým pádom pripravené na analýzu a prehľady. Na základe setu reportov si koncový užívateľ vyberie povolené atribúty a po kliknutí sa separátne fungujúci proces rozhodne, ktoré data z tabuliek použiť bez toho, aby sa datové tabuľky menili.

V princípe sa jedná o oddelenie datovej časi od analytickej tak, aby data boli použiteľné do budúcna alebo individuálne.

V budúcom článku sa budem podrobne venovať riešeniu z “vlastnej dielne”.

Ako získať komentár a uložiť ho ako hodnotu do bunky

 

Myslím si, že tento titulok nepotebuje dodatočnú interpretáciu. Potrebujeme:

 

VBA riešenie:

Function Komentar(Bunka As Range)

'Funkcia, ktorá vráti komentár z bunky
If Not Bunka.Comment Is Nothing Then
    Komentar = Bunka.Comment.Text
End If

End Function

Pomocný súbor si môžete stiahnuť kliknutím na obrázok

 

Ako zistiť presné súradnice farby v reporte

 

Ako to už býva zvykom, okrem zložitých procesov – namávavých na mozgové bunky človek niekedy potrebuje riešiť triviálnosti ako sú napríklad korporátne farby v reporte. Inak povedané – nie je modrá ako modrá, na čo niektoré firmy dávajú striktný dôraz.

Pre ľahšiu manipuláciu je dôležité poznať tie správne indexy farieb. Určite každý sa v praxi stretol napríklad s pojmom RGB index, kde R – red, G – Green a B – Blue odtiene.

Nasledujúce VBA skripty definujú farebné indexy:

Function Color(RNG As Range, Optional formatType As Integer = 0) As Variant
    Dim colorVal As Variant
    colorVal = Cells(RNG.Row, RNG.Column).Interior.Color
    Select Case formatType
        Case 1
            Color = Hex(colorVal)
        Case 2
            Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case 3
            Color = Cells(RNG.Row, RNG.Column).Interior.ColorIndex
        Case Else
            Color = colorVal
    End Select
End Function

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

Function hexColor(RNG As Range)

    colorVal = RNG.Interior.Color
    hexColor = Hex(RGB((colorVal \ 65536), ((colorVal \ 256) Mod 256), (colorVal Mod 256)))

End Function

Funkcionalitu si môžete skúsiť v priloženom súbore, kliknutím na obrázok. Tip – pokúste sa kombinovať výstupy a hľadať dané farby na internete.

Poznámka: V súčasnosti existuje množstvo tzv. “Color Pickerov”, ako napríklad paint.net ktoré bez akýchkoľvek dodatočných aktivít nám zistia color index a editujú obrázky (ikonky). Avšak ak potrebujete aby sa v reporte menili farby na základe istých podmienok, je VBA funkcionalita *.Interior.Color vskutku užitočný nástroj.

            

Moderný reporting – prvý report (2. časť) + Inštalácia Jaspersoft Studio

 

Dostávame sa do situácie, keď máme všetko potrebné pre prípravu reportu. Nakoľko sa táto fáza zdá byť kozmetická záležitosť, opak je pravdou a čo je ešte viac smutné, 99 % firiem neinvestuje do vývoja obsahovej zložky ani potrebné minimum.

Ako to funguje:

80 % analytikov v novej práci zdedí už hotové reporty. Ich úlohou je pochopenie a pravidelná aktualizácia, prípadne tvorba dodatočných čísiel v závislosti od požiadaviek managementu. Čo musí analytik / controller zvládnuť je pochopiť biznis tak, aby naň mal relevantný pohľad a vedel, “na ktoré čísla dávať zvýšenú pozornosť”. Z technického hľadiska je výhodné automatizovať samotnú prácu ako napríklad čistenie dát, tvorba summary apod..

V prípade, že zvládnete už spomenuté požiadavky, nastáva čas pre “show time”. Vzniká  priestor, v ktorom sa začnete zamýšľať a inovovať existujúce reporty, resp. metriky. Inými slovami konzultovať a prezentovať nápady vedeniu spoločnosti.

V prípade, že sa táto snaha vrámci spoločnosti ocení – máte ideálne podmienky na “varenie si  polievočky seniornej resp. managerskej pozície”.

Rozhodol som sa preto zdieľať niekoľko rád, odpovedí na otázku: Ako vytvoriť kvalitný report?

1) Definujte si, čo je cieľom reportu – aké info má poskytovať? V našom prípade to bude odpoveď na otázku: Koľko jednotlivý predajca predal áut v danom regióne, pričom platí, že predajcovia kolujú medzi predajňami.

2) Stanovte si osnovu reportu. Obrázok ilustruje nasledujúcu logiku:

Zvolím si zamestnanca, obdobie, metriku (vysvetlím neskôr) a ostatné parametre. Prvá vizualizácia je celkový predaj v období, kde okrem absolútnych hodnôt zobrazujem porovnanie s minulým obdobím (ako si polepšil / pohoršil oproti minulému mesiacu alebo kvartálu), s benchmarkom (istá očakávaná hodnota, pod ktorú by nemal spadnúť).

Tento celkový počet chcem rozdeliť podľa modelov – vzhľadom k množstvu verzií automobilov Škoda a skutočnosti, že sa nejedná o časovú súslednosť medzi hodnotami (mám jedno číslo, rozdelené do skupín – modelov aut, ktoré sa predali vo vybranom čase) považujem za prehľadnejší spôsob tabuľku v porovnaní s grafom.

Nakoniec ma zaujíma, kde sa predajcovi darilo najviac a kde najmenej (aby som v budúcnosti vedel optimalizovať alokáciu dealerov). Túto problematiku nám zobrazí HeatMap s hodnotami.

Čo môžem zistiť? – Skutočnosť či zamestnanec nadhodnocuje alebo podhodnocuje plán, ak áno je to sezónna záležitosť (tým pádom by sa nemohol odlišovať od benchmarku) alebo individuálna (rozdiel oproti predchádzajúcemu obdobiu a súčasne benchmarku)? Taktiež, chcem vidieť ktoré autá dobre predával a ktoré nie (zistím, či daný dealer ľahšie predáva rodinám (Yeti, Combi verzie Škodovky), alebo je B2B orientovaný (obnovuje vozový park managerskými Octaviami alebo Superbami). Nakoniec chcem vidieť región, v ktorom mal najlepší a najhorší predaj. Tým pádom ak vezmem v úvahu čas v ktorom regióne ako dlho predával, dokážem s ním efektívne konzultovať výkonnosť a doladiť podmienky tak, aby predával čo najlepšie!

3) Stanovím si metriky – Inak povedané, je rozdiel konštruovať benchmark priemerom, váženým priemerom, mediánom apod. Používajte aspoň vážený priemer, ja osobne preferujem medián, čo je 50% kvantil – v praxi to znamená, že 50% dealerov predalo nad mediánovou hodnotou. Otatní sa budú musieť snažiť viac . Detail o  danej problematike nájdete kliknutím tu.

4) Nepoužívam divoké grafy. V našom prípade použijeme:

Prvý bude kombináciou Line a Area Chartov. Druhý je kombináciou mapy   (Bing / Google) a pre mnohých veľmi obľúbeného, avšak irelevantne používaného Pie chartu. O problematike používania grafov budem písať v samostatnom článku.

Teraz si ukážeme ako si nainštalovať Jaspersoft Studio. Pripomeniem, že pomocou Studia budeme navrhovať daný report.

Kliknite na odkaz: https://community.jaspersoft.com/download Kliknite na

A vyberte si nasledujúci inštalátor podľa toho,koľko bitový máte OS:

 

alebo stiahnite si Studio pre Win 64bit kliknutím tu. Potom kliknite na uložený súbor:

A spusťte inštaláciu (I agree –> niekoľkokrát Next –> Finish a je to ):

Po inštalácii sa Vám progam spusí sám. Ak nie kliknite na ikonku:

Pri otvorení nadefinujte miesto, kde sa majú ukladať reporty, adaptéry a rôzne iné nastavenia vrámci Japsersoft:

Potom by sa Vám malo zobraziť Welcome okno:

Ak ho zavriete alebo prejdete na: Window –> New Window dostanete sa na Jaspersoft Workbench:

Gratulujem, nachádzate sa v prostredí, z ktorého budeme tvoriť webové reporty.

Podrobný popis Studia a prvé začiatky si ukážeme v nasledujúcom článku. V prípade dotazov alebo iných informácii / služieb neváhajte kliknúť na Facebook, alebo si prejsť môj web.

Moderný reporting–prvý report (1. časť)

 

Dostávame sa k prvej biznisovej otázke:

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

Na nasledujúcom obrázku máme vyznačné tabuľky, vrámci ktorých budeme pripravovať query:

Ak použijeme jednoduchú query na počet záznamov (predajov) dostaneme nasledujúci výsledok:

select count(*) as 'Počet' from sales; výsledok: 17472

Čo je na Škoda auto celkom málo, avšak relevantné data ešte len prídu. Ak si rozšírime query podľa rokov a mesiacov, dostaneme nasledujúci výsledok:

select Mesiac, Rok, count(*) as 'Počet' from sales group by Mesiac, Rok;

Teraz nasleduje pripojenie Predajcov, takže použijeme Join klauzulu. Všimnite si logiku pochodu, kde celkový počet rozdeľujem do detailov, pričom Grand Total mi slúži ako kontrola selektu (Predchádzajúcu tabuľku som pripravil v Exceli samozrejme):

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID group by sales.Mesiac, sales.Rok, Dealer.Predajca;

Výsledok, vzhľadom k tomu, že sa už v tomto prípade jedná o celkom veľkú Pivotku je obmedzený na rok 2014 vo filtri, kde si môžeme prepínaním overiť predajnosť dealerov vzhľadom k totálu (728 vozidiel pre všetky mesiace, 672 ročný predaj každého dílera):

Vezmime si teraz napríklad Romana, ktorého celkový predaj za Január v roku 2014 má byť 56 vozidiel. Rozšírime tento skript o hodnoty z tabuľky City:

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', City.Mesto as 'Mesto', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID  left join City on sales.Mesto = City.ID group by sales.Mesiac, sales.Rok, Dealer.Predajca, City.Mesto;

Celkovo si všimnite, že sa nám mesačná predajnosť zhoduje (56 áut) spolu s celkovou predajnosťou dílera za rok 2014 (672 áut).

Ako posledný skript budeme uvažovať doplnenie o typ auta, t.z. pridanie tabuľky Car:

select sales.Mesiac as 'Mesiac', sales.Rok as 'Rok', City.Mesto as 'Mesto', Car.Auto as 'Auto', Dealer.Predajca as 'Díler', count(*) as 'Počet' from sales left join Dealer on sales.Predajca = Dealer.ID  left join City on sales.Mesto = City.ID left join Car on sales.Auto = car.ID  group by sales.Mesiac, sales.Rok, Dealer.Predajca, City.Mesto, Car.Auto;

Platí, že pre každý mesiac predá presne po jednom kuse špecifického modelu.

Ako už iste tušíte, tieto data nepatria medzi najvhodnejšie vrámci budovania reportov. No vzhľadom k tomu, že chcem aby sa Vám SQL-ko dostalo čo najskôr pod kožu, zvolil som túto predbežnú cvičnú databázku (17472 = 728 x 12 x 2 – Tabuľka 1 alebo 56 x 12 x 13 x [2 – Filter] – Tabuľka2 alebo 8 x 7 x 12 x [13 x 2 – Filter] alebo 7 x 1 x 12 x [13 x 2 x 8 – Filter] – Tabuľka3).

Nabudúce si povieme niečo k obsahu reportu, resp. ako má report vyzerať a budeme kresliť

Moderný reporting pracujeme s MySQL (spájanie pomocou JOIN)

 

Väčšina z PC friendly čitateľov určite vrámci databáz počula pojem SELECT-FROM-WHERE-JOIN – GROUP BY spojenie. Vzhľadom k predchádzajúcim článkom si v podstate všetko okrem klauzuly JOIN a GROUP BY dokážete dať do súvislostí.

Tak teda čo znamená pojem JOIN v SQL jazyku:

Podľa Wiki je to klauzula, založená na vymedzení referenčných polí, ktorá kombinuje záznamy medzi tabuľkami. Ľudskou rečou povedané, ak mám napríklad dve tabuľky tak pomocou JOIN spolu s vymedzaním relevantných stĺpcov (Foerign key stĺpec v primárnej tabuľke =  Primary key stĺpec v referenčnej)  program vie, ako má dané záznamy spojiť.

Ak si spomeniete na predchádzajúci článok, kde som spájal dve tabuľky takto:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tak v novom príkaze vypustíme klauzulu WHERE a pridáme JOIN (typ spojenia). Tu sa dostávame pre mnohých do komplikovaného rozhodnutia typu: Aký typ JOIN klauzuly použiť?

V tomto bode si dovolím inšpirovať sa knihou Mistrovství v MySQL od M. Koflera, kde na str. 237 má prehľad typov JOIN klauzúl:

Nepodmienené typy vrátia všetky možné kombinácie vrámci záznamov, odlišný je však STRAIGHT_JOIN, ktorý neoptimalizuje poradie výberu dat. Zameriame sa preto na použitie podmienených typov:

V podmienených typoch záleží na poradí tabuliek. Pre pochopenie som vytvoril nasledujúce tabuľky:

Tabuľka Dealer:

Tabuľka Dovolenka:

Všimnite si, že Tabuľky Dovolenka sa odkazuje na zoznam pracovníkov z tabuľky Dealer a zároveň fakt, že Helena zatiaľ nečerpala žiadnu dovolenku a v Dovolenke máme dva neplatné záznamy s ID zamestnanca 5 a 7.

Ak vezmeme v úvahu LEFT JOIN (a zároveň NATURAL JOIN) vrámci tabuliek dealer a dovolenka, nasledujúci skript:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti všetky záznamy z tabuľky Dovolenka, ku ktorým doplní relevantné mená z tabuľky Dealer. V prípade, ak by sme v evidencii Dovolenka mali ID > 4, znamenalo by to, že tam máme zle vloženú evidenciu alebo ID zamestnanca, ktorý ešte nie je evidovaný v systéme (tabuľke Dealer).

To znamená, ak by kontent tabuľky Dovolenka vyzeral takto:

Predchádzajúci skript by vyhodil nasledujúci prehľad:

To znamená, že vrátil všetky dovolenkové záznamy, vrátane tých, ku ktorým v tabuľke Dealer nenašiel Meno (súčet hodín s prívlastkom NULL).

Čo by sa stalo, ak by sme zamenili LEFT za RIGHT JOIN:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka right join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Tento skript by zamenil tabuľky. To znamená, ku každému Menu v tabuľke Dealer by priradil súčty hodín vrámci dovolenky:

Všimnite si, že nám vrátil naviac prehľad o Helene, ktorá ešte dovolenku nečerpala a zároveň sa zbavil neidentifikovaných 13 hodín voľna. Čo sa stane ak použijem pôvodný select typ z predcházajúceho článku alebo INNER JOIN? INNER JOIN v podstate definuje niečo ako skalárny súčin údajov, medzi dvoma tabuľkami.

Znamená to, že ak niektorá z tabuliek obsahuje vrámci referencií hodnoty NULL (Helena v tabuľke Dealer a záznam 4 a 5 z tabuľky Dovolenka), tak tieto hodnoty budú ignorované pri výstupe:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka inner join  dealer on dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

alebo:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka, dealer where dovolenka.Zamestnanec = dealer.ID group by dealer.predajca;

Vráti nasledujúci prehľad:

Ak by sme tam chceli zanechať tých 13 hodín, museli by sme im priradiť správne ID alebo definotať ich ako nových zamestnancov. Helena sa tam objaví v prípade, že niekedy načerpá dovolenku.

Všimnite si časť GROUP BY: Tá je potrebná, aby aplikácia vedela, podľa ktorých parametrov má urobiť prehľad. Inak povedané, predchádzajúce kódy vypočítali súčet hodín z dovoleniek a rozdelili ich podľa hodnôt zo stĺpca predajca v tabuľke dealer.

Použijeme prvý skript ale bez group by:

select dealer.Predajca as 'Zamestnanec', sum(dovolenka.Hodiny) as 'Dovolenka Celkom' from dovolenka left join  dealer on dovolenka.Zamestnanec = dealer.ID;

Sami vidíme, že výsledok čo sa týka počtu hodín je rovnaký. Akurát problém je v tom, že tu aplikácia zobrala prvé ID = 1, ktoré má hodnotu Peter a k nemu priradila celkový súčet hodín. Príkaz GROUP BY vytvorí prehľad všetkých Mien a k nim nasledne dohodí súčty, ktoré celkovo dajú hodnotu 82 hodin.

Poučenie: snažte sa čo najlepšie poznať svoju databázu a taktiež si vždy presne vymedzte, čo je cieľom selektu.

Tým pádom môžem konštatovať, že sme pripravení si v ďalších článkoch vytvárať selekty podľa daných požiadaviek. Taktiež sa začneme hrať (kresliť si) so schémami dashboardov.

V prípade dotazov sa neváhajte pýtať – alebo kliknite na FB odkaz dole.

Funkcia–OverStlpec (Zistí, či sú v stĺpci rovnaké hodnoty)

 

Dnešok môžem označiť za “excelovský” a to z dôvodu, že som sa po dlhej dobe celý deň hral s datami v Exceli. Ako to už býva u mňa zvykom – priniesol som celkom jednoduché, pre mňa dnes užitočné riešenie na otázku:

Sú v danom stĺpci rovnaké hodnoty?

Samozrejme, spôsoby ako si poradiť sú rôzne. Avšak ak máte povedzme 100 stĺpcov, sú filtre alebo pivotky časovo náročná záležitosť.  Čo by ste povedali na nasledujúce riešenie:

Postup je naozaj jednoduchý: Alt + F11 – otvoríte VBA editor. Kliknutím na Insert – New Module vložíte nový modul, do ktorého stačí skopírovať nasledujúci kód:

Function OverStlpec(Oblast As Range)
 
Dim i As Integer
 
UB = Oblast.Rows.Count
 
 
For i = 2 To UB
 
    If Oblast(i) <> Oblast(i - 1) Then
        OverStlpec = "Nezhoda"
        Exit Function
    End If
Next i
  
OverStlpec = "Zhoda"
 
End Function

Na záver môžete túto funkciu hravo využívať, pričom jej tvorba zabrala maximálne 20 sekúnd. Celý súbor vrátane príkladu si môžete stiahnuť kliknutím na Download obrázok. Jednoduché, prístupné aj pre VBA laika – tak snáď Vám to niekedy pomôže.

             

Moderný reporting pracujeme s MySQL (Prvé selekty)

 

Vzhľadom k tomu, že som postup prípravy databázy spolu s jej monžosťou downloadu popísal v predchádzajúcich článkoch, nemôžem nezdieľať svoj enthusiasmus, čo sa týka manipulácie s datami.

Túto časť považujem za najviac zaujímavú z hľadiska možnej špekulácie. Inak povedané, v tomto štádiu môžete využiť svoju kreativitu a tzv. otázky na papieri premeniť v skutočné dotazy s ešte zaujímavejšími faktami. Spomeňme si na niekoľko bodov, z úvodného sylabu:

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

Na základe nich budeme formovať dotazy tak, aby sme dostali relevantné podklady. Predtým si však musíme osvojiť niektoré základné funckie.

SELECT niečo FROM niečo WHERE niečo;

Tento stýl uvažovania sa používa prakticky neustále. Ako ste si už všimli, SELECT * FROM názov tabuľky vráti vždy všetky záznamy z konkrétnej tabuľky. Napríklady SELECT * FROM cost vráti hodnoty v nasledujúcom obrázku:

Čo jesíce pekné, ale nepoznáme kto sa skrýva pod číslom 1 v stĺpci Predajca. Dnes si preto ukážeme základný postup spájania tabuliek (aby ste pochopili logiku, prepojíme tabuľku cost s tabuľkou dealer tak, aby sme pri selekte miesto čísel mali Mená) – viď obrázok:

SELECT dealer.Predajca, cost.* FROM cost, dealer WHERE Cost.Predajca=Dealer.ID;

Tento skript pracuje s označením názov tabuľky.názov stĺpca – dealer.Predajca, v ktorom sa nachádzajú skutočné mená, cost.* znamená vráť všetko z tabuľky cost. FROM cost, dealer – zoznam tabuliek. Za príkazom WHERE spájame tabuľky tak, že definujeme, ktoré stĺpce medzi tabuľkami su relevantné. Teda  čísla v Cost.Predajca sú zo zoznamu čísel v Dealer.ID.

Po exekúcii skriptu si môžeme overiť, že prepojenie vyšlo tak ako malo:

V uvedenom obrázku platí, že pod číslom 1 je zakódovaný predajca Peter. Pod číslom 2 je už Martin. Takto pripravený skript nám už síce dáva pridanú hodnotu, avšak aby sme z neho dostali zrozumiteľný prehľad, chcelo by to veľa práce (napr. v Exceli). Povezme, že chceme prehľad ročných miezd jednotlivých Predajcov.

SELECT dealer.Predajca, cost.Rok, sum(cost.Mzda) as 'Mzdy' FROM cost, dealer WHERE Cost.Predajca=Dealer.ID GROUP BY dealer.Predajca, cost.Rok;

V ňom sme poupravili názov sumácie ako Mzdy a pridali parameter GROUP BY, v ktorom definujeme stĺpce, podľa ktorých chceme vytvoriť prehľad vo forme sumácie – inak povedané, chceme súčet mzdových nákladov podľa dealera a konkrétneho roku.

Výsledok vyzerá nasledovne:

Nabudúce si rozšírime problematiku spájania tabuliek pomocou JOIN klauzuly, ktorá sa používa omnoho viac frekventovane, než spôsob ktorý som dnes opísal.