こんにちは！ 健史(たけふみ)です。
この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。
最初は、真似ることです。
少しづつ何回も Excel-VBAの起動、実行に慣れて、プログラムにも目を通して理解を深め、自然に手が動くようになるまで頭の回路に焼き付けて頂ければと思います。
そして、活用できるレベルにいって頂ければと嬉しく思います。
Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。
プログラムの作成と実行
今回のサンプルプログラムは
です。
プログラムの概要は以下の通りです。
・同じフォーマットで一部の値が異なる２つのシートがあり、シート３に「値が異なるセルの内容」を出力する
・基のシートの異なる部分には、マーキングする
データ量が多い場合、目視で探すのは不可能に近く、見間違えもあり非効率です。
そんな時に対応するプログラムです。
ファイルの更新系プログラム(追加・削除を伴わない)のテスト確認で、更新前後のファイルを比較するときなどにも使えます。
実行までの手順
1.Excelを起動
2.データ入力
Excel2007以降は、Sheet1のみであるためシートを２つ追加し、全部で３つ作成しておきます。
Book1のSheet1とSheet2のセル[A1]～[E5]に、分かりやすいようにセルの番号を入力します。
そして、Sheet1とSheet2の何ヵ所かの値を変えます。
3.Excel-VBAを起動
Altを押した状態でF8キーを押します。
4.[マクロ名(M):]に'test'と入力(''は不要、''内のtestを入力)
5.[作成(C)]をクリック
私はマクロ名を入力したら、そのままEnterを押します。
6.以下のプログラムをドラッグ＆コピー
Dim i As Long, j As Long, i3 As Long
Worksheets(1).Cells.ClearFormats
Worksheets(2).Cells.ClearFormats
Worksheets(3).Cells.Clear
Worksheets(3).Cells(1, 1) = "セル"
Worksheets(3).Cells(1, 2) = "シート１"
Worksheets(3).Cells(1, 3) = "シート２"
i3 = 1
For i = 1 To 5
For j = 1 To 5
If Worksheets(1).Cells(i, j) <> Worksheets(2).Cells(i, j) Then
i3 = i3 + 1
Worksheets(3).Cells(i3, 1) = Worksheets(1).Cells(i, j).Address
Worksheets(3).Cells(i3, 1) = Replace(Worksheets(3).Cells(i3, 1), "$", "")
Worksheets(3).Cells(i3, 2) = Worksheets(1).Cells(i, j)
Worksheets(3).Cells(i3, 3) = Worksheets(2).Cells(i, j)
Worksheets(1).Cells(i, j).Interior.ColorIndex = 3
Worksheets(2).Cells(i, j).Interior.ColorIndex = 6
End If
Next
Next
6.以下に貼り付け(ペースト)
Sub test()
ここに貼り付ける
End Sub
7.F5キーで実行
8.実行結果の確認
Book1に切り替えて下さい。
Altを押した状態でTabキーを押して、Book1に位置づいたらTabキーを離すことで切り替わります。
シート１、シート２でマーキングされているセルが異なる部分です。
シート３には、異なる内容が出力されています。
フローチャート
ループ処理である[For i = 1 To 5 ・・・ Next]部分フローチャートです。
iで固定した状態でjを１～5まで変えながら処理を行います。
具体的には、
①.i=1に固定した状態で、jを１～5まで変えながら処理を行う
②.i=2に固定した状態で、jを１～5まで変えながら処理を行う
③.i=3に固定した状態で、jを１～5まで変えながら処理を行う
④.i=4に固定した状態で、jを１～5まで変えながら処理を行う
⑤.i=5に固定した状態で、jを１～5まで変えながら処理を行う
です。
プログラムの詳細説明
Dim i As Long, j As Long, i3 As Long
i,j,i3 という名前のデータをプログラム内に定義
Worksheets(1).Cells.ClearFormats
Worksheets(1)であるSheet1の書式設定だけをクリアする
最初は何も設定されていないのですが、複数回流す場合などを考慮してクリアします
Worksheets(2).Cells.ClearFormats
Worksheets(2)であるSheet2の書式設定だけをクリアする
Worksheets(3).Cells.Clear
Worksheets(3)であるSheet3の内容、書式設定などすべてをクリアする
Worksheets(3).Cells(1, 1) = "セル"
Worksheets(3).Cells(1, 2) = "シート１"
Worksheets(3).Cells(1, 3) = "シート２"
Worksheets(3)であるSheet3に見出しを付ける
i3 = 1
Sheet3に内容を出力するが、1番目には見出しが入っていて2番目から出力するので1をセットして
おく、i3は「何番目までデータが入っている」ということを示す
最初に2をセットしておいて、セットしたら1カウントアップする方法もある
For i = 1 To 5
iに１をセットし1づつカウントアップして[i > 5]になるまで一番下のNextの範囲までの
処理を繰り返す
For j = 1 To 5
jに１をセットし1づつカウントアップして[j > 5]になるまで下から２番目のNextの
範囲までの処理を繰り返す
If Worksheets(1).Cells(i, j) <> Worksheets(2).Cells(i, j) Then
「Sheet1とSheet2の同じi行目、j列目を比較して、等しくない時は」という意味
等しい場合には、<>を=に変える
i3 = i3 + 1
対象データがあったので、Sheet3に内容を出力するためi3を1カウントアップする
Worksheets(3).Cells(i3, 1) = Worksheets(1).Cells(i, j).Address
[Address]はセル番号を出力する指定であるが、[A1]の場合には[$A$1]と出力される
Worksheets(3).Cells(i3, 1) = Replace(Worksheets(3).Cells(i3, 1), "$", "")
[$A$1]と出力されてしまうので、Replace命令で"$"を空白に置き換える
Worksheets(3).Cells(i3, 2) = Worksheets(1).Cells(i, j)
Sheet1の内容をSheet3に出力する
Worksheets(3).Cells(i3, 3) = Worksheets(2).Cells(i, j)
Sheet2の内容をSheet3に出力する
Worksheets(1).Cells(i, j).Interior.ColorIndex = 3
Sheet1のセルの色を変更する、3はレッド
Worksheets(2).Cells(i, j).Interior.ColorIndex = 6
Sheet1のセルの色を変更する、6はイエロー
End If
Next
Next
補足です。
・変数について
今回は、３つの変数[i,j,i3]を使います。
i3は[i:縦の行を示し]+[3:３番目のシートで使うもの]なので、'i3'としました。
・Cells.ClearFormats、Cells.Clear について
都度、シートを別ファイルから持ってきたり、sheet3は手でクリアしたり、削除して再度作成するなどの対応であれば不要です。
・[i3 = 1]と[i3 = i + 1]について
[i3 = 1]を[i3 = 2]にした場合、[i3 = i + 1]の位置は[Worksheets(3).Cells(i3, 3) = Worksheets(2).Cells(i, j)]の後にする必要があります。
この場合のi3は「次に入る行の番号」を示しています。
好みの問題ですが、私はその変数が「今何番目まで入っている」という処理、すなわち[i3 = 1]にしておいてセットする直前で[i3 = i + 1]を行う処理としています。
・.Address について
セル番号を出力する方法は他にもありまが、[.Address]がスッキリしているので採用しました。
ですが、[$A$1]のように'$'が付いてきます。そのため次の命令[Replace]で、'$'を無くす処理を入れました。
・Interior.ColorIndex について
数字と色の関係は'Interior.ColorIndex'で検索できます。
最後に
このプログラムは、シート１とシート２について、セル[A1][A2][A3][A4][A5][B1][B2]・・・[C3][C4][C5]と一つひとつ比較し、異なる部分をシート３に出力するプログラムでした。
最初のシートフォーマットやシートをクリアする処理、セルにマーキングする処理が不要だったり、逆にシート３に出力する処理が不要だったり、ケースは多々あると思います。
使うシーンに応じて対応して頂ければと思います。
コメント