同じフォーマットのシートが複数ある場合のデータ確認
Excelで１つのブックの中に表形式の同じフォーマットのシートが複数あることがあります。
よくあるのは個数や金額のフォーマットや、データベースなどの項目定義のフォーマットなどが挙げられます。他にもいろんな業種で表形式のフォーマットは利用されています。
これらのフォーマットを複数のシートで利用する場合に、全シートで何が設定されているのかを確認しなければならないことがあります。
よくあるのが、「入力内容がそもそも合ってるのか間違ってるのか、何が記入されているのかもよく分からない」という場合です。
こういう場合は片っ端から確認していくしかないのですが、量が多い場合や目視での確認に不安がある場合や、修正後の再確認が発生したりするとまた同じことのやり直しになってしまいます。
そこで以下では、全シートの指定列の内容を集約してイミディエイトウィンドウに出力するマクロを紹介します。
事前準備
以下のコードではDictionaryオブジェクトを利用しています。
VBA画面のツールメニュー→参照設定を選び、参照設定ダイアログで「Microsoft Scripting Runtime」にチェックを付けます。これでDictionaryが使えるようになります。
CreateObject関数を使う方法もありますが、詳細は「VBAのDictionaryの使い方（全メソッドとプロパティ網羅）」をご参照ください。
全シートの指定列の全行の値を取得して集約して出力する
以下のマクロでは、全シートの指定された列の全行の内容を取得して、同じ値であれば重複を除去してイミディエイトウィンドウに出力します。
例えば「北海道」から「沖縄」までの全都道府県の47シートがあるブックの場合、「福岡」シートのC2セルを選択している状態で以下のマクロを実行すると、47シートそれぞれのC列の全行の値を取得して重複を除去して出力します。
「福岡」シートのC2セルは「どの列を取得対象の列にするか？」という「C列」が欲しいだけで、「福岡」シートでなくても構いません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
Sub 全シートの指定列の値を集約して取得() Dim sht As Worksheet '// シート（全シートループ用） Dim col As Integer '// 基準となる列 Dim dic As New Dictionary '// 同一値のみを取得するための辞書 Dim iMaxRow As Long '// 各シートの最終行 Dim i As Long '// 行ループ用の行位置指定用ループカウンタ Dim s As String '// セルの文字列 Dim key As Variant '// 辞書のキー '// 現在のアクティブシートのアクティブセルの列を基準列として取得する col = ActiveCell.Column '// 全シートループ For Each sht In Worksheets '// このシートの最終行を取得 iMaxRow = sht.UsedRange.Rows.Count '// 行ループカウンタを先頭行位置の1に初期化 i = 1 '// シートの全行ループ Do '// 最終行に達した場合 If i > iMaxRow Then '// 全行ループを抜ける Exit Do End If '// 現在行のセルの値を取得。列は先に取得した位置。 s = sht.Cells(i, col).Value '// セルの値が未取得の場合 If dic.Exists(s) = False Then '// 辞書に追加する Call dic.Add(s, s) End If '// 次行に設定 i = i + 1 Loop Next '// 辞書の全キーをループ For Each key In dic.keys '// イミディエイトウィンドウに出力 Debug.Print key Next End Sub |
コード説明
コードの内容はコメントに大体書いてますので、何をやろうとしているのかを説明します。
変数colはColumnの略で、どの列の全行を取りたいのか、という基準となる列を設定しています。マクロを実行した際のアクティブシートのアクティブセルを対象としているため、A1セルを選択してればA列、D100セルを選択していればD列を基準としています。
ActiveCell.Columnは実際には英字のAなどではなく列位置を示す数値が変数Colには格納されます。
あとは、For Eachで全シートを表すWorksheesオブジェクトのループで１シートずつ回して、その各シートの最終行をUsedRange.Rows.Countで取得し、１行目から最終行までの基準列のセルを１つずつ取得して、辞書に格納されていなければ格納しています
。
UsedRangeを使った最終行の取得の詳細については「VBAで編集セル範囲の選択と最終行と最終列の取得」をご参照ください。
Dictionaryを使っているのは重複を除去するのにExistsメソッドでの判定がラクなためです。
Dictionaryの詳細については「VBAのDictionaryの使い方（全メソッドとプロパティ網羅）」をご参照ください。
実行例
以下のように都道府県のシートがあるとします。ここでは北海道と沖縄の一部の市町村を書いてます。
＜北海道＞
＜沖縄＞
ここで北海道シートのB2セルを選択して、上のマクロを実行すると、各シートのB列の内容をイミディエイトウィンドウに出力します。
