エクセルマクロによるデータベースの基本操作

最終行データの行番号を自動で検知・検出できること、これがエクセルでデータベース機能をパワーアップさせる第一歩です。先日のページではこんな考え方から最終行の行番号を検出するマクロコードをご紹介しました。

今回はそれを使って、わたしが実際にマクロ化した工程をご紹介します。その工程とは、二つの表に別々にあるデータからそれぞれ目的に必要なデータだけを取り出し、必要なデータだけがあるひとつの表に集約する工程です。

また、その二つの表は別の場所に保存されていて、それをこの工程を実行させようとするエクセルファイルに一旦丸ごと手作業でコピー＆ペーストし、そのあとの工程をマクロによって実行させることにします。

では必要なデータを抽出する工程から順を追っておハナシを進めます。

必要なデータを抽出する工程

今あるデータから必要なデータを抽出するとは、不要なものを削除することでもあります。不要なデータを削除する工程が一番工数が少なく、シンプルな工程になりそうな気がしたので、不要なデータを削除する工程を考えることにします。

次の命令文はその一例です。データの範囲から、各データの列Ｃにあたるセルの値が100以上のデータである場合、その行を丸ごとを削除する命令文をパターン化したものです。

For n = 最終行データの行番号 To 第一行目データの行番号 Step -1
    If Cells(n, 3).Value >= 100 Then
        Rows(n).Delete
    End If
Next n

ここから以降は、データテーブルのフィールドタイトル行が３行目にある場合を想定して、それを前提として各値決めております。その想定に沿えば、その時に当てはめる具体的なコードなり数値なりは、

『最終行データの行番号』には

先日ご紹介したコードRange("B3").End(xlDown).Rowを当てはめて、

第一行目データの行番号には

4

になります。

オンラインヘルプやマクロを解説している市販本などでは、Forで始まる行とNextで始まる行の２行をワンセットにして、「For...Nextステートメント」と呼ばれており、これは、ある条件が満たされる限り、その２行で挟まれている記述内容（ステートメント）を繰り返し実行する機能を持っております。

またこの事例で繰り返し実行すべき記述内容にはIfで始まる行とEnd Ifで始まる行があり、こちらもこの二つがワンセットになります。

それぞれ、使い慣れるととても便利な命令文のようです。それらの命令文の詳しいことは別の機会に触れるとして、ここではおハナシを先に進めます。必要なデータだけを抽出したら、次はここから必要なフィールドを抽出し、それらを必要な順序に並びを変える工程です。

必要なフィールドを抽出しそれらを必要な順序に並びを変える工程

データベースの概念では、個々のテーブルではその目的に沿ってフィールド件数が定まり、その順序も常に一定に保ちます。抽出したいフィールドとその並びが確定できれば、フィールドの抽出とその順序を変えるのは、エクセルの数式を入力することで対処できます。

先ほどの必要なデータだけになったテーブルの構成は、

• フィールドタイトル行は３行目、
• データの行がそれ以降

です。そのテーブルの右横の適当な列の３行目を起点にして、抽出するフィールド名称を並べたい順番で右横方向に並べてタイトル行をまず作ります。その行の上、例えば１行目に元テーブルのフィールドを参照させる数式をあらかじめ入力しておきます。

そうしておけば、あとは一行目に入力した一連の数式を、抽出されたデータの件数だけコピー＆ペーストする操作をマクロ化するだけです。ただひとつだけ、データベース概念からの注意事項があります。

今しようとしているのは、複数あるテーブルから必要なものだけを取り出して、別のテーブルに集約することですが、集約後のテーブルで個々のデータの出所をもとに何らかの集計をすることが必要であるならば、出所を区分するためのフィールドを集約後のテーブルにちゃんと設けておく必要があります。

ですから、それが必要ならば、必要な順序に並びを変える時に、区分するためのフィールドをいっしょに加えてしまえば効率的です。

さて、一連の数式をセル範囲「ＡＡ１：ＡＦ１」に入力してあるとします。そうするとそれをセルＡＡ４から下方向に必要個数コピー＆ペーストする操作をすることになります。

次の命令文によってそれが実行されます。

Range("AA1:AF1").Copy _
    Destination:=Range( _
         Cells(4, 27), _
         Cells(Range("B3").End(xlDown).Row, 27) _
    )

この命令文は
コピー元範囲.Copy Destination:=貼り付け範囲

という構成をしており、

Copyは

「Ctrl」+「C」の操作に相当し、

Destination:=は

「Ctrl」+「V」の操作に相当

します。これによりコピー元内容の全要素が貼り付け先のセルに貼り付きます。

これでレイアウトが統一された二つのデータテーブルが出来上がりました。次はいよいよそれらをひとつのテープるに集約する工程です。

二つのデータをひとつのテーブルに集約する工程

することそのものは、コピーしてそれを場所を指定してそこに値だけを貼り付けることです。最初に貼り付けるデータの位置は常に特定できます。その一方で、その次に貼り付けるデータの位置は特定できず、直前に貼り付けたデータの最終行の次の行になるところがポイントです。つまり、最終行データの行番号を自動で検知・検出して、その値に1を加えることで解決できます。

例えば、コピー元のデータがあるシートがアクティブな状態であるとし、貼り付け先のシート名称がMergeDataであるとすると、次のような命令文になります。

Range( _
    Cells(4, 27), _
    Cells(Range("B3").End(xlDown).Row, 32) _
).Copy
'
Worksheets("MergeData").Cells( _
    Worksheets("MergeData").Range("B3").End(xlDown).Row + 1, 2) _
    .PasteSpecial _
        Paste:=xlPasteValues, _
        Operation:=xlNone
'

PasteSpecialとは

形式を選択して貼り付ける動作の命令

Paste:=xlPasteValuesとは

値を貼り付けること

Operation:=xlNoneとは

貼り付け時に演算をしないこと

を、それぞれ意味しております。

以上で、

• データを抽出する工程
• フィールドを抽出し並びを変える工程
• 別々のテーブルをひとつに結合する工程

がマクロ化出来ました。

ところでこの三つの工程は、データベース操作の一番基本的な内容だそうです。エクセルはデータベースを専門に扱うソフトウェアではないため、これら三つの工程をしようと思えば、原則的には手作業に頼らざるを得ません。一方でデータベースソフトとエクセルの一般性を比べると、やっぱりエクセルの方が一般的であり、あるファイルを複数の人が扱う場合はエクセルを使う選択肢が一番現実的です。マクロを活用することでデータベース操作を自動化出来た事例であると言えそうです。

---