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:
Ako vysvetlenie vám poslúži nasledujúca úloha:
Máte k dispozícii nasledujúci dataset :
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:
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:
Tieto data potom prekopírujeme do dataset-u (samozrejme bez názvov stĺpcov) a urobíme si kontrolnú kontingenčnú tabuľku:
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.
