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.