Banner

pondelok 19. augusta 2013

Automatizujeme reporty: Ako dostať z textu PSČ a číslo domu

 

Najprv chcem poďakovať za námet, myslím si že je to často diskutovaný prípad v rámci reportov. Nebudem chodiť okolo horúcej kaše, vrhneme sa na prípad (Súbor s funkciami na stiahnutie dole):image

Zadanie je jednoduché: z textu potrebujeme vrátiť PS4, Ulicu a Číslo domu.

Problém však nastáva vo forme toho textu. V realite nám možnosť Text To Columns nemusí postačovať, obzvlášť tak dostávame “ručne” naplnené tabuľky.

Začnem PSČ. Ak si pozrieme štruktúru PSČ, s istotou môžeme tvrdiť, že ak nájdeme v texte tri po sebe idúce znaky ako čísla, tak skoro s istotou sa nachádzame na PSČ. Tým pádom vytvoríme FOR cyklus, ktorý nájde takúto možnosť. potom stačí použiť hodnotu iteračného čísla z for cyklu ako začiatočné písmeno pri funkcii MID alebo ČÁST a máme PSČ (viď kód):

Function NajdiPSC(text As String)


Dim i As Integer

Const Cisla = "0123456789"

'Nájdem trojčíslie v texte
For i = 1 To Len(text)

    If InStr(Cisla, Mid(text, i, 1)) <> 0 And InStr(Cisla, Mid(text, i + 1, 1)) <> 0 And InStr(Cisla, Mid(text, i + 2, 1)) <> 0 Then
        'Vytrhnem z textu oblasť, začínajúca trojčíslím
        NajdiPSC = Mid(text, i, Len(text))
        Exit Function
    End If
       
Next i

End Function

Tak a máme PSČ, viď obrázok:

image

Teraz potrebujeme dostať číslo domu, čo bude trošku ťažší oriešok, no stručná metodika: nájdeme trojčíslie, ktoré bude prvá obmedzujúca podmienka pre FOR cyklus, teda i=1 až pozícia trojčíslia. V tomto obmedzení nájde prvé číslo, čím vznikne nové obmedzenie, teda i= poloha prvého čísla až po polohu trojčíslia. V tomto obmedzení budeme hľadať polohu prvého písmena. Následne použijeme funkciu MID alebo ČÁST, kde z textu vrátime časť od polohy prvého čísla až po polohu prvého písmenka, viď kód:

Function NajdiCisloDomu(text As String)


Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer

Const Cisla = "0123456789"

'Nájdem trojčíslie v texte
For i = 1 To Len(text)

    If InStr(Cisla, Mid(text, i, 1)) <> 0 And InStr(Cisla, Mid(text, i + 1, 1)) <> 0 And InStr(Cisla, Mid(text, i + 2, 1)) <> 0 Then
        j = i
        GoTo dalej1
    End If
       
Next i

'Nájdem prvé číslo v texte
dalej1:
For i = 1 To j - 1

    If InStr(Cisla, Mid(text, i, 1)) <> 0 Then
        k = i
        GoTo dalej2
    End If
Next i

'Nájdem prvé písmenko v rámci obmedzenia prvé číslo - trojčíslie
dalej2:
For i = k + 1 To j - 1
    If InStr(Cisla, Mid(text, i, 1)) = 0 Then
    l = i
    GoTo dalej3
    End If
Next i

dalej3:

'Ošetrím ak popisné číslo je jednociferné
If l - k = 0 Then
    l = 1
Else
    l = l - k
End If

'Ošetrím ak nenájdem číslo domu
If k = 0 Then
    NajdiCisloDomu = ""
Else
    NajdiCisloDomu = Mid(text, k, l)
End If

End Function

Výsledok vyzerá nasledovne (ulicu alebo povedzme doplnkový text už nie je problém vyselektovať):

image

Súbor s funkciami stiahnete kliknutím na obrázok. Nezabudnite na Facebook, stačí kliknúť na logo a v prípade dotazov a podobných problémov ako je tento s radosťou odpoviem Žmurk

downloads_normalEK logo FB

pondelok 5. augusta 2013

Automatizujeme reporty: automatické aktualizácie kurzu €


Určite ste sa už niekedy stretli s reportom, ktorý v sebe obsahoval Kč-y a €-á. Takýto report v sebe pravdepodobne obsahuje sheet s tabuľkou, obsahujúcou kurzy podľa dátumu, kedy bol report robený a dáte mi za pravdu ,že buď trávite niekoľko minút copy-paste-ním aktuálnych sadzieb z centrálnej banky alebo máte komplikovanú procedúru, ktorá stiahne celý .CSV súbor a vytiahne z neho hodnotu, ktorú momentálne potrebujete.
Čo tak zvoliť elegantnejší spôsob? image
Ja som sa v rámci svojich reportov rozhodol obísť tieto procedúry a vytvoril som si funkciu s názvom =eukurz(“dátum”). Jej výhoda spočíva v jednoduchosti, stačí ak ju odkážete na bunku s dátumom a ona Vám vráti požadovaný kurz.
VBA riešenie vyzerá nasledovne:
Function EuKurz(datum As Date)
Dim Mena As String
Dim CNBfix
Dim SDatum As String
Dim CNBobjekt
Dim URL

Mena = "EUR"
CNBfix = TimeSerial(14, 30, 0)
Set CNBobjekt = CreateObject("MSXML2.XMLHTTP")
  
'Ošetríme možnosť, že dátum je z budúcna
If datum > Format(Now, "Short Date") Or datum = "0:00:00" Then
    EuKurz = " "
    Exit Function
End If
  
'ČNB aktualizuje kurzy po 14.30 - t.z. pred 14:30 vezmeme kurz z predch. dňa
If datum = Format(Now, "Short Date") Then
    If Time < CNBfix Then
        datum = datum - 1
    End If
End If
   
'Ak je dátum rovný Sobote alebo Nedeli, posunieme sa a použijeme kurz z najbližšieho piatku
If (Weekday(datum, vbMonday) > 5) Then
    datum = datum - Weekday(datum, vbMonday) + 5
End If

'SDatum pre odkazSDatum = Format(datum, "dd.mm.yyyy")
 
'adresa odkazu (ČNB)
URL = "
http://www.cnb.cz/miranda2/m2/cs/financni_trhy/devizovy_trh/kurzy_devizoveho_trhu/vybrane.txt?mena=" & Mena & "&od=" & SDatum & "&do=" & SDatum
CNBobjekt.Open "GET", URL, False
CNBobjekt.send

If (InStr(1, CNBobjekt.responseText, SDatum)) = 0 Then
    EuKurz = "-"
Else
    EuKurz = Mid(CNBobjekt.responseText, InStr(1, CNBobjekt.responseText, SDatum) + Len(SDatum) + 1, Len(CNBobjekt.responseText))
End If

'On error resume next v prípade, že používate iný než anglický Excel (aby výsledok nebol #HODNOTA!)On Error Resume NextEuKurz = CDbl(Replace(EuKurz, ",", "."))
EuKurz = CDbl(EuKurz)
Set CNBobjekt = Nothing

End Function


Nakoľko sa daný kód môže zdať trošku komplikovaný, je to spôsobené tzv. ošetrujúcimi podmienkami. Každopádne si hotovú funkciu môžete stiahnuť kliknutím na obrázok Download a nezabudnite na Facebook skupinu Žmurk
downloads_normalEK logo FB

piatok 2. augusta 2013

Ako vymazať alebo obnoviť niektoré pomenované oblasti

 

V realite sa stáva, že človek dostane pod ruku report, ktorý prešiel mnohými firmami – oddeleniami. Takéto reporty sa zvyknú správať tak trochu netradične. Značné problémy môžu spôsobovať nefungujúce definované oblasti (Defined Names). Pokiaľ sa pokúšate vytvoriť alebo obnoviť niektoré výpočtové operácie a chcete ich zjednodušiť pomocou definovaných oblastí, takýto pohľad do Name Manager-a nebude príjemný:

image

Pre zbavenie a možné nadefinovanie nových pomenovaných oblastí som vytvoril nasledujúce makro:

Sub CleanDefNames()
    Dim DefName As Name
    Dim UB
   

'For-cyklus vymaže všetky definované oblasti + On Error preskočí nejasnosti Excelu
    On Error Resume Next
    For Each DefName In ThisWorkbook.Names
        DefName.Delete
    Next DefName
   
On Error GoTo 0
   
   'Definujeme počet riadkov novej definovanej oblasti
    UB = Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
   
    'Ak je posledný riadok rovný 1, potom definovanú oblasť nemá zmysel robiť - je prázdna
    If UB = 1 Then
        MsgBox "Hodnoty pre pomenovanú oblasť neexistujú", vbCritical
       
Exit Sub
    End If
   
    'Vytvorí sa nová definovaná oblasť s názvom POKUS
    ActiveWorkbook.Names.Add Name:="POKUS", RefersToR1C1:="=DATA!R2C1:R" & UB & "C1"
   
    End Sub

Ak spustíte danú procedúru, v prípade, že v liste DATA v prvom stĺpci nemáte žiadnu hodnotu, objaví sa Vám nasledujúce okno:

image

Toto okno len upozorňuje, že sa pomenovaná oblasť POKUS nevytvorila, lebo stĺpec v liste DATA bol prázdny. V konečnom dôsledku týmto makrom si môžete neustále (po vlastnej úprave) aktualizovať pomenované oblasti a tým pádom budete mať po každom prečistení “čerstvé” výpočty.

Mimochodom, pomocou danej procedúry schudol môj report o viac než 0.5 MB, čo nie je na škodu veci. Celý tento proces si môžete pozrieť na nasledujúcom videu:

 

Refresh–Defined Names

Nabudúce si pripravíme niečo “SPECIAL” …. máte sa načo tešiť a nezabúdajte na Facebook fun-page! EK logo FB

pondelok 29. júla 2013

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í).

image

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.

downloads_normalEK logo FB

utorok 23. júla 2013

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:
image
Niekto by samozrejme mohol argumentovať, že to poradie mesiacov si urobí manuálne, ale buďme profesionáli a pohrajme sa s funkciami ŽmurkNa 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).
image
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) ]
image
- 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) ] 
image
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) ]
image
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.
downloads_normal EK logo FB

štvrtok 18. júla 2013

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äčší.image

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:

image

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

image

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.

image

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ý ÚsmevAk 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í Žmurk

 

EK logo FBdownloads_normal

 

sobota 13. júla 2013

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:

 

downloads_normal

 

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

EK logo FB

štvrtok 30. mája 2013

Ako sa zbaviť diakritiky v slove – funkcia =CistyText()

 

Ako ste si už všimli, sága vlastných funkcií a makier pokračuje. Dnes som sa zamyslel nad otázkou, ako nám môžu slová s diakritikou skomplikovať život.

Určite ste aspoň raz museli ručne kontrolovať názvy firiem, ktoré kedysi zadali účtovníčky ručne do systému a zrazu, keď finalizujete report, tak sa vám v kontingenčnej tabuľke zbytočne zobrazí napríklad: Peter Tabiš s.r.o. a Peter Tabis s.r.o.

Tým pádom vám “totály” nesedia a máte v tom bordel. To všetko len preto, lebo náš Slovenský spolu s Českým jazykom obsahujú písmenka typu é, í, á atď.

Ja som si pripravil zoznam znakov, ktoré sa najviac vyskytujú:

image

Tieto znaky som spojil a použil v nasledujúcom skripte:

Function CistyText(SpinavyText As String)
'funkcia, ktorá vyčistí text od diakritiky

Dim Stare As String
Dim Nove As String
Dim i As Integer

'nadefinované konštanty, v ktorých sú spojené znaky s diakritikou a bez nej
Const SpinavePismenka = "áćĆĎéÉíĹĺńŃóÓŕśŔŚťúÚýźŹÁčČďěĚÍĽľňŇôÔřšŘŠŤůŮÝžŽ"
Const CistePismenka = "acCDeEiLlnNoOrsRStuUyzZAcCdeEiLlnNoOrsRSTuUYzZ"

'pre každý znak z SpinavePismenka nájde alternatívny z CistePismenka a ak sa nachádza v SpinavyText tak ho nahradí
For i = 1 To Len(SpinavePismenka)
Stare = Mid(SpinavePismenka, i, 1)
Nove = Mid(CistePismenka, i, 1)
SpinavyText = Replace(SpinavyText, Stare, Nove)
Next

'Vráti očistený text
CistyText = SpinavyText

End Function

Výsledok v praxi vyzerá nasledovne:

image

Takto vyčistený text si môžete upraviť buď na všetky malé alebo veľké písmená. Tým pádom by sa mala eliminovať akákoľvek manuálne vytvorená chyba a reporty by mali fungovať ako hodinky Úsmev

Sledujte Ďalšie VBA vychytávky a nezabudnite na FB skupinu Úsmev

pondelok 27. mája 2013

Funkcia - Nájdi poslednú neprázdnu bunku v riadku (LROW)


Určite sa Vám stalo, že ste potrebovali napísař sumu alebo nadefinovať oblasť pre kontingenčnú tabuľku tak, aby ste tam po manipulácii mali kompletné data, avšak bez zbytočných prázdnych riadkov.

Takáto povedzme suma neesixtuje. Avšak čo by si človek pomocou VBA nevymyslel :). 

Dnes vám ukážem ako si jednoducho nadefinovať funkciu, ktorá nájde hodnotu posledného neprázdneho riadku v stĺpci. Kód vyzerá nasledovne:

Function LROW(Column As Integer) As Integer

'Vstupná premenná Column je typu integer, t.z. musíte zadať číslo stĺpca, napr. A ako 1, B ako 2 atď.
LROW = Cells(Rows.Count, Column).End(xlUp).Row
'Výstupná premenná LROW je typu integer, takže dostaneme číslo riadku.

End Function

Výsledok si môžete skúsiť naplnením zopár buniek v prvom stĺpci A hodnotami (ja som vyplnil 44). Potom zadajte do funkcie hodnotu 1 a vráti vám počet neprázdných buniek (viď obrázok).


Takúto funkciu môžeme aplikovať na sumu takto:


Takto vytvorená suma berie vždy v úvahu nové, dodatočne vyplnené (vymazané) bunky bez toho, aby ste odkaz museli ručne upravovať.



štvrtok 2. mája 2013

Ako nevhodný formát dokáže znepríjemniť život (inšpirované dotazom čitateľa)

 

Zažili ste niekedy ten pocit beznádeje, keď sa snažíte použiť vzorec (match, vlookup), no skončíte bez výsledku?

Jedným z možných problémov môže byť formát hodnôt v stĺpcoch. Stiahnite si template a môžeme začať.

Ako vidíte, máme k dispozícii dva stĺpce. Skúsme vyhľadať pozíciu hodnoty zo stĺpca A v stĺpci B, viď nasledujúci obrázok. 

image

Ak skopírujete vzorec pre všetky hodnoty v prvom stĺpci, dostanete nasledujúci výsledok:

image

V praxi sa takéto niečo stáva pomerne často (pri výstupoch z databázových systémov sa stane, že hodnoty Excel nemusí zobraziť ako čísla).

Skopírujte a vložte hodnoty do toho istého stĺpca. Objaví sa Vám výstražný kosoštvorec, viď obrázok.

image

Po rozkliknutí zvoľte možnosť Convert to Number a po niekoľkých sekundách (záleží od toho, koľko buniek musí Excel opraviť) Vám funkcia vráti správne hodnoty (nasledujúci obrázok).

image

sobota 20. apríla 2013

What-if Analysis – budujeme scenáre, hľadáme optimálne hodnoty a modelujeme viacnásobné výstupy

 

Dneska si predstavíme čo všetko dokáže táto ikonka:

image

imageAk by som mal v skratke definovať význam, tak by som What-If Analysis nazval ako zjednodušený Solver. Po rozkliknutí sa nám zjavia tri možnosti.

Scenario Manager… nám umožňuje vytvoriť rôzne scenáre vstupných premenných do vzorca a pozorovať výstupy.

Príklad: predstavme si elementárnu funkciu typu množstvo x marža = profit.image

Pre takto vytvorenú funkciu vytvoríme scenáre predaja: Pesimistický, Realistický a Optimistický takto:

Klikneme na What-If Analysis, potom vyberieme Scenario Manager… a klikneme na Add…

image

V Scenario name: vyplníme názov scenára. V položke Changing cells: definujeme bunku, v ktorej sa bude meniť hodnota podľa scenára – bude sa meniť predané množstvo. Klikneme na OK a v ďalšom okne zadáme hodnotu pre scenár. 

Takto si vytvoríme naše tri scenáre. Potom po rozkliknutí na jednotlivý scenár sa aplikujú jeho podmienky a zmení sa výstup.  POZRI VIDEO

Kliknite na Summary… a v Result cells… zvoľte bunku E2 (bunka pod “Total”). Objaví sa Vám nasledujúci prehľad scenárov a ich výsledkov v novom liste.  image

Možnosť Goal Seek…

Vráťme sa k nášmu jednoduchému príkladu a predstavmeimage si, že máme daný cieľový profit a taktiež maržu. Chceme zistiť, koľko množstva musíme nakúpiť.

Klikneme na What-If Analysis, potom vyberieme Goal Seek… V možnosti Set cell: zvolíme cieľovú bunku – E2 (profit) To value: napíšeme cieľovú hodnotu a v By changing cell: definujeme bunku, ktorá sa má zmeniť - C2 (množstvo) a klikneme na OK. Výsledok by mal byť 20.6667. POZRI VIDEO

Možnosť Data Table…

Predstavte si nasledujúci príklad: máte vytvoriť zľavu 10% na niektoré výrobky podľa cien a množstva: Číslo 144000 je výsledkom obecného vzorca – Cena x Množstvo x (1-Diskont). Tento vzorec máme aplikovať pre rôzne množstvá a zároveň ceny:

image

Označte si tabuľku tak ako na obrázku a kliknite na What-If Analysis, potom Data Table… v možnosti Row input cell: zvolíme bunku zo vzorca, ktorej hodnota sa bude meniť podľa stĺpcov, teda D4 bunka (Cena). Do Column input cell: zvolíme bunku, ktorej hodnoty vo vzorci sa menia podľa riadkov – E4 (Množstvo). Klikneme na OK a výsledok by mal vyzerať nasledovne:

image

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

 

What-If Analýza

Aby som nezabudol, Excel pre každého nájdete aj na Facebook-u, stačí kliknúť na logo: