PAGE TOP

取り組み

印刷する

VBAで自動化を図る

さぼ郎
【前の話
下記の表の若干の説明。

1列目のデータは、ある内容を持つコードで、内容の出現数が2列めの数値です。
3列目は、同じ種類で異なる内容を持つコードになります。その出現数が4列の値です。
1列目のコードと3列目のコードを並べることで、それぞれが持つ内容データの件数を比較することができます。

Excel
gawkの前の記事にリンク ↑

1列目のデータが「85,889件」あって、その出現数をカウントした結果として「1,770件」になりました。
3列目のデータは「36,448件」あって、その出現数をカウントした結果として「1,607件」です。

1列目にあるコードが2列めには無いデータが「163件」になり、それを見つけて行を揃えるのは、けっこう大変です。

そこでVBAの登場になります。

gawkで加工したテキストデータをExcelに貼ります。

Excel

行を揃えるためのVBAは、このようになります。

Sub gyouzoroe()
    Dim dtMax As Long
    dtMax = Range("B10000").End(xlUp).Row
    
    Dim i As Long:    i = 1
    Dim j As Long:    j = 1
    
    Do While Range("D" & j).Value <> ""
        'Range("D" & j).Activate
        If Range("B" & i).Value <> Range("D" & j).Value Then
            Do While Range("D" & j).Value <> Range("B" & i).Value
            'Debug.Print "B:" & Range("B" & i).Value
                i = i + 1
            Loop
            Range("D" & j & ":E" & i - 1).Select
            Selection.Insert Shift:=xlDown,
 CopyOrigin:=xlFormatFromLeftOrAbove
            j = i
        End If
        
        i = i + 1
        j = i
        If i > dtMax Then
            MsgBox "最後まで来た"
            Exit Do
        End If
    
    Loop

End Sub

VBAの説明

Sub gyouzoroe()
戻り値がある場合は、「function」となります。
単に処理をするだけなら「sub」です。

Dim dtMax As Long
変数の宣言をします。
「Long」とは倍精度の整数のことです。
昔、「int」が16ビットの頃の「long」は32ビットでした。
調べてみたら今でも4バイトだそうです。
-2,147,483,648 ~ 2,147,483,647

dtMax = Range("B10000").End(xlUp).Row
「B10000」というのは、10,000行まではデータがないことが分かっているので、10,000行の下から見て、最初にデータが出現した行番号を「dtMax」に代入しています。
実際には「1,770」が入ります。

前のExcelは16ビットの整数である「65,536」行でした。
今のExcelは「1,048,576」行になりました。
16×16×16×16×16
のようです。
列方向は、前は256でしたが、「16,384」列になりました。
16×16×16×4
のようですが、64ビットのOfficeでも同じです。

ここで取得する数値は、ループの際の離脱条件に使用します。

Dim i As Long:    i = 1
Dim j As Long:    j = 1
カウンターに使用する変数の宣言と初期化です。

Do While Range("D" & j).Value <> ""
D列のカウンターをインクリメントして空白セルになったらループが終了です。
ちなみに、D列(1,607件)はB列(1,770件)よりもデータ数が少ないことが分かっているので、そのD列が最終行まで行ったら、比較はできなくなるわけです。

'Range("D" & j).Activate
デバッグ用にアクティブセルを表示させていますが、実際には邪魔なのでコメントアウトします。

If Range("B" & i).Value <> Range("D" & j).Value Then
この行でB列とD列の値を比較しています。
もし、違いがあるようなら、この「If文」以下に入ります。

Do While Range("D" & j).Value <> Range("B" & i).Value
更にループでB列とD列が同じになるまでループを回します。
'Debug.Print "B:" & Range("B" & i).Value
デバッグ用に出力しています。
i = i + 1
B列のカウンターをインクリメントしています。

B列とD列の値が同じになれば、ループから離脱します。

Loop

Range("D" & j & ":E" & i - 1).Select
D列の位置と、B列で進めたカウンターの1行前までを選択します。

Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove

ここの呪文は、マクロを記録して取りました。
ようは、空行を挿入してD列とE列を同時に下方に下げています。

j = i
下げたのだから、B列と同じ位置にあわせます。

End If

i = i + 1
j = i
B列とD列の位置を1行下げます。

If i > dtMax Then
MsgBox "最後まで来た"
Exit Do
カウンターが最終行に来たら、そこで終わりになります。

End If

Loop
ループの終わり

End Sub
プロシジャーの終わり

まず、日本語で書いてみるのがいいように思います。

1.B列とD列を比較する
2.同じなら1行下げる
3.違ったら、D列をB列と同じになるまで下げる
4.下げただけのカウント分、空白行を挿入する
5.B列とD列のカウントをそろえてから1行下げる
6.ループが離脱条件になったら終了する



これだけのことなんですが、慣れないと1、2時間はかかってしまいます。しかし、自動化しなくても1、2時間はかかりますし、どちらが楽かということと正確かということから考えて、出来得る限り、大量のデータ加工や、繰り返し性のあるデータ処理は自動化することを検討するべきです。

キーワード