Ako doplniť víkendy do časovej rady

 

Ako ste si už stihli všimnúť, sága článkov o tom, ako radím niekoľkým ľuďom pokračuje. Dnes tu máme niečo z prostredia banky – tradingu. 

Dotaz znel nasledovne: upraviť časovú radu tak, aby obsahovala víkendy a hodnoty Open, Close, Min, Max, Adj. Close a Volume boli kĺzavým priemerom za 5 predchádzajúcich dní.

Daný problém vyzerá nasledovne (použijeme funkciu =TEXT(adresa bunky, “dddd”), aby sme videli názvy dní).

Všimnite si, že za dňom Friday, nenasleduje Saturday a taktiež Sunday. Preto nasledujúce makro bude hľadať dátum, ktorý je piatkom. Ak nájde takýto dátum, vloží nad neho dva prázdne riadky, doplní dátumy a kĺzavé priemery (=AVERAGE(posledných 5 dní)). Taktiež som ošetril možnosť, ak by ako prvá hodnota bola piatkom (v tom prípade by sme už extrapolovali o dva dni do budúcna hodnoty).

Riešenie:

Sub DoplnData()

Application.ScreenUpdating = False

Dim UB
Dim RB
Dim i
Dim j
Dim APW

'Najde veľkosť tabuľky
UB = Cells(Rows.Count, 1).End(xlUp).Row
RB = Cells(1, Columns.Count).End(xlToLeft).Column

Set APW = Application.WorksheetFunction
For i = 2 To UB
    'Ošetrenie ak by prvá hodnota bola piatková, preskočí proces
    If Application.WorksheetFunction.Text(Cells(i, 1), "dddd") = "Friday" Then
    If i = 2 Then
        GoTo koniec
    End If
        'Tento proces sa opakuje 2x - sobota aj nedeľa
        For j = 1 To 2
            'Vloží riadok a do neho dopočíta kĺzavé priemery
            Cells(i, 1).EntireRow.Insert
            Cells(i, 1).Value = Cells(i + 1, 1) + 1
            Cells(i, 2).Value = APW.Average(Range(Cells(i, 2), Cells(i + 5, 2)))
            Cells(i, 3).Value = APW.Average(Range(Cells(i, 3), Cells(i + 5, 3)))
            Cells(i, 4).Value = APW.Average(Range(Cells(i, 4), Cells(i + 5, 4)))
            Cells(i, 5).Value = APW.Average(Range(Cells(i, 5), Cells(i + 5, 5)))
            Cells(i, 6).Value = APW.Average(Range(Cells(i, 6), Cells(i + 6, 6)))
            Cells(i, 7).Value = APW.Average(Range(Cells(i, 7), Cells(i + 7, 7)))
        Next j
   
    End If
   
koniec:
    i = i + 2
       
Next i

Application.ScreenUpdating = True

End Sub

Celý súbor spolu s cvičným dataset-om si môžete stiahnuť kliknutím na obrázok. Pamätajte, na Facebook-u mi môžete poslať svoj dotaz, rád Vám naň odpoviem – stačí like kliknutím na logo dole.

Dynamicky meniace sa hodnoty v referenčnom stĺpci (podľa dátumu)

V praxi sa stane, že potrebujete urobiť prehľad tak, aby v prvom riadku bol súčet za dnešný mesiac ( obvykle mesiac, pre ktorý robíte daný report) a potom zvyšné riadky budú obsahovať doplnok mesiacov, k tomu nejaký subtotal a celkový súčet za celý rok.

Celá táto paráda by mohla vyzerať takto:

Niekto by samozrejme mohol argumentovať, že to poradie mesiacov si urobí manuálne, ale buďme profesionáli a pohrajme sa s funkciami Na prvom mieste je vhodne si zvoliť niečo ako referenčný titulok listu – “Reporting date:” a dátum. Z neho sa potom do prvého stĺpca ťahá hlavný referenčný mesiac (viď obrázok).

Potom je nutné nadefinovať druhý až dvanásty riadok. Budeme uvažovať nasledujúcu logiku:

- 2. riadok v stĺpci: Ak je prvý riadok rovný 1, druhý nadobudne hodnotu 2, inak nadobudne hodnotu  [  =IF($J$4=1,2,1) ]

- n. riadok v stĺpci: Ak je hodnota predchádzajúceho riadoku +1 rovná hodnote prvého riadku, nadobudne hodnotu predchádzajúceho riadku + 2, inak hodnotu predchádzajúceho riadku + 1      [ =IF((J13+1)=$J$4,J13+2,J13+1) ] 

Potom stačí už len vyplniť tabuľku funkciou SUMIFS a prehľad je hotový. [ =SUMIFS($E$5:$E$245,$D$5:$D$245,$J4,$F$5:$F$245,K$3) ]

Celý tento proces si môžete pozrieť na nasledujúcom videu:

Dynamické hodnoty stĺpca podľa dátumu

Alebo si stiahnite hotový súbor kliknutím na download. Nezabudnite na Facebook – stačí ak klikneš na Logo.

 

Top najlepší alebo najhorší podľa mesiacov

 

Drahí, analýzami nadšení kamaráti. Ako som už avizoval, prinášam opäť niečo z praxe. Predstavte si situáciu, keď máte štruktúrované data, zoradené podľa mesiacov od najmenšieho po najväčší.

Z nich potrebujete určiť prvých 5 najlepších alebo najhorších, v našom prípade povedzme – darcov na charitu. Ešte uvediem do poznámky, že tie data sú vygenerované (aby ma niektorá z firiem nezmazala z povrchu zemského).

Prehľad by mohol vyzerať takto:

Rozumiem, hovoríte si –vyzerá to pekne, už len vyplniť no nebojte sa. Použijeme nasledujúce zostavy:

Začal som jednoduchšou funkciou – vlookup, myslím, že ju nemusím predstavovať, v našom prípade proste príde a vráti hodnote názov firmy.

Táto zostava hľadá maximum v oblasti podľa mesiaca. To znamená, že ak si zvolíme mesiac Marec, tak si nájde oblasť buniek, ktoré spadajú pod Marec a v nich nájde maximum. Ako sa to celé deje:

 

=LARGE(INDIRECT(ADDRESS(IF($K$4=1,1,MATCH($K$4-1,CRIT)),5)&":"&ADDRESS(MATCH($K$4,CRIT),5)),$J7)

Modrá oblasť jednoducho hľadá maximum v poradí, ktoré sa nachádza v bunke J7.

Zelená oblasť vytvorí z čísel riadku a stĺpca adresu bunky: ADDRESS(1,5) = $A$5, spojením dvoch typov ADDRESS dostávame odkaz typu $A$5:$A$500

Červená oblasť ošetruje prípad ak je mesiac Január, a to z dôvodu, že prvý ADDRESS definuje spodnú hranicu intervalu (v MATCH použijem náš mesiac –1) a druhý zase hornú (v MATCH použijem náš mesiac).

Takto pripravené vzorčeky len nakopírujete do príslušných buniek a výstup je hotový Ak ste viac vizuálny typ, v nasledujúcom videu sa to snažím laicky popísať:

TOP 5 schedule

Taktiež si môžete stiahnuť template kliknutím na download a nezabúdajte – byť v skupine na FB sa oplatí

 

 

Ako sčítať hodnoty podľa farby bunky

 

V praxi sa človek stretne s rôznymi problémami, ktoré sú bežne dostupnými excelovskými funkciami a možnosťami neriešiteľné, resp. ich použitím by to trvalo veľmi dlho.

Zručnejší užívateľ poďakuje Bohu za VBA, pomocou ktorého vyrieši skoro všetko. Dnešná téma je námetom od jedného čitateľa - analytika.

Dostal sa mu do rúk report, kde potreboval sčítať jednotlivé bunky podľa farieb. Nakoľko jeho špekulácia s funkciami nedopadla úspešne, rozhodol som sa mu pomôcť a napísal som krátky VBA kód:

Function SumaFarba(FarbaBunka As Range, Oblast As Range)

Dim c
Dim Farba As Integer
Dim Vysledok
 
Farba = FarbaBunka.Interior.ColorIndex 'Pomocou .Interior.ColorIndex načíta kód  farby  a vloží ho do premennej Farba
   For Each c In Oblast
       If c.Interior.ColorIndex = Farba Then 'Ak sa kód farby v bunke rovná kódu v premennej Farba, hodnota sa pričíta do premenne Vysledok
              Vysledok = c.Value + Vysledok
       End If
   Next c
  
SumaFarba = Vysledok

End Function

Celá táto procedúra je k dispozícii vo forme template-u, ktorý si môžete stiahnuť kliknutím na:

 

 

Nezabudnite sa pridať a sledovať novinky a postrehy na Facebook-u – Kliknite na nasledujúce logo.