Banner

sobota 19. januára 2013

Kontingenčná tabuľka–ako ju zmeniť na dataset

 

Už je to dávno, čo som sa zaoberal tvorbou a manipuláciou s kontingenčných tabuliek. No ako čas plynie, človek pracuje a špekuluje nad všetkým – dospel som k problému, s ktorým sa stretol skoro každý analytik/controller.

Ako Vyplniť bunky v referenčných stĺpcoch tak, aby sme výstup mohli vložiť do zdrojových dat?

Myslím ,že obrázok by ako ilustrácia padol vhod:

image

Ako vysvetlenie vám poslúži nasledujúca úloha:

Máte k dispozícii nasledujúci dataset :image

Vašou úlohou je porovnať útratu na jednotlivé kategórie projektov tzn. cez ID(EU) Kód  a podľa jednotlivých miest. Jeden ID(EU) Kód  reprezentuje typ projektu a takýto projekt môže byť realizovaný vo  viacerých mestách Slovenska.

V tomto prípade porovnať útratu znamená urobiť diferencie medzi útratami v rokoch 2012 a 2011. Výsledky je nutné uložiť vo forme D_”ID(EU) Kód_referenčný rok”.

Napríklad projekt typu 77E2011A3 bude mať diferenčný kód D_77E2011A3_2012. Tento referenčný kód spolu s mestami a diferenciou vložíme do dataset-u.

Riešenie:

Urobíme si na nový list kontingenčnú tabuľku, ktorá bude vyzerať takto:image

Takto vytvorenú tabuľku skopírujeme a uložíme ako hodnoty na iný list alebo vedľa kontingenčnej tabuľky. Ako priradiť jednotlivý ID(EU) Kód k mestu tak, aby sa neporušila štruktúra tabuľky?

Proces Copy-Paste sa zdá byť možným riešením, ale ak máme napríklad 600  až 60 000 riadkov tak to by sme vypĺňali pol dňa ak nie celý deň.

Riešenie bude makro:

Budeme potrebovať:

4 premenné:

UB – Horný riadok, LB – Dolný riadok, j – číslo stĺpca, i – iteračná premenná (bude meniť jednotlivé riadky)

3 InputBox-y (Dialógové okná, do ktorých vložíme hodnoty pre naše premenné)

1 “For procedúru

3 “IF procedúry

2 funkcie (Cint – konvertuje text na číslo, IsEmpty – overí či je bunka prázdna)

Nezabudnime na zamrznutie obrazovky: screenupdating

Makro vyzerá nasledovne:

Sub VyplnStlpec()

Application.ScreenUpdating = False

'===========================================================
'========definujeme premenné===================================
'===========================================================

Dim i
Dim j
Dim LB
Dim UB

'===========================================================
'========naplníme premenné====================================
'===========================================================

LB = InputBox("Vložte hodnotu prvého riadku kontingenčnej tabuľky", _
"Prvý zaplnený riadok")
If LB = "" Then Exit Sub


UB = InputBox("Vložte hodnotu posledného riadku kontingenčnej tabuľky", _
"Posledný zaplnený riadok")
If UB = "" Then Exit Sub

j = InputBox("Vložte hodnotu stĺpca ktorých chcete vyplniť", _
"Stĺpec tabuľky")
If j = "" Then Exit Sub

LB = CInt(LB)
UB = CInt(UB)
j = CInt(j)

'===========================================================
'========Vyplníme prázdne bunky v stĺpci===========================
'===========================================================

For i = LB To UB
    If IsEmpty(Cells(i, j)) =
True Then
        Cells(i, j).Value = Cells(i - 1, j)
    Else
   
End If
Next i

Application.ScreenUpdating = True

End Sub

Po jeho spustení a vyplnení požadovaných údajov dostanete vyplnený stĺpec kódov. Potom stačí už len urobiť diferenciu (môžete aj relatívnu), skopírovať a vložiť do dataset-u s tým, že pre diferencie uložíte do jedného zo stĺpcov útraty

Výsledok by mal vyzerať takto:

image

Tieto data potom prekopírujeme do dataset-u (samozrejme bez názvov stĺpcov) a urobíme si kontrolnú kontingenčnú tabuľku:

image

Všimnite si, že Grand Total hodnoty sa zhodujú, tzn. že ak sčítame žlté bunky (diferencie) so svetlo-žltými (útrata 2011) dostaneme útratu 2012. 

Celý súbor spolu s makrom si môžete stiahnuť tu. Ak by ste k tomu mali dotazy, alebo chceli sa dozvedieť niečo viac – pýtajte sa kliknutím tu.

Žiadne komentáre:

Zverejnenie komentára

Poznámka: Komentár môže zverejniť iba člen tohto blogu.