Excel pre každého: Kontingenčná tabuľka

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: