VBA 複数ファイルとの比較

Question

Aのファイル(ThisWorkbook)のデータと複数のファイルのデータを全て照合するコードを書きたいと思っています。
配列Sheet(1~179)に複数のファイルのパスが入っています。

AファイルにはG列にデータが入っています
######Aファイル
行番号|F |G |H|
|:--|:--:|--:|
1||初期||
2||テスト||
3||アクション||
4||イベント||
5||デバッグ||

配列に入っている複数のファイルのデータはCC列に入っているが、データ数がバラバラ
Bファイル CC1CC6
Cファイル CC1CC8 など

######Bファイル
行番号|CB |CC | CD |
|:--|:--:|--:|
1||イベント||
2||検索||
3||デバッグ|
4||PHP||
5||AWS|
6||初期||

######Cファイル
行番号|CB |CC | CD |
|:--|:--:|--:|
1||C#||
2||テスト||
3||Ruby|
4||Java||
5||C||
6||エラー||
7||Python||
8||module||

実装したいことは、以下の2つです

もしAファイルのG列のデータが「初期」で、配列に入っているどれかのファイルのデータに初期という文字が1つでもあれば、真横のH列に「一致」という文字を入力。無ければ不一致を入力。
(例)AファイルのG1が初期であり、Sheet(1)に入っているBファイルのCC6に初期という文字があれば、AファイルのH2に一致を入力。全ファイルと比較し、無ければ不一致を入力

######Aファイル
行番号|F |G |H|　　　　　　　
|:--|:--:|--:|
1||初期|一致|
2||テスト||
3||アクション||
4||イベント||
5||デバッグ||

AファイルのG列のデータが「初期」以外である場合は、配列に入っているどれかのファイルのデータに、Aファイルと同じデータが1つでもあれば、「一致」という文字を入力。無ければ不一致を入力。
(例)AファイルのG2がテストであり、Sheet(2)に入っているCファイルのCC2にテストという文字があれば、AファイルのH3に一致を入力。全ファイルと比較し、無ければ不一致を入力

######Aファイル
行番号|F|G|H|
|:--|:--:|--:|
1||初期|一致|
2||テスト|一致|
3||アクション|不一致|
4||イベント|不一致|
5||デバッグ|一致|

考え途中の構想が、

a=2
b=2
d=1(配列Sheetの要素番号に使用 要素179まである)
book = Sheet(d) 配列の中には、ファイルのパスが179まで入っている



For i = 1 to ?
  For j = 1 to ?
　　　if ThisWorkbook.WorkSheets("イベント").Cells(a,7).value Like "*初期*" Then
       比較するファイルを開く記述
　　　　 if ThisWorkbook.Worksheets("イベント").Cells(c,7).Value = Workbooks(book).Worksheets("説明").Cells(b,81) Then
         ThisWorkbook.Worksheets("イベント")Cells(c,8).Value = "一致"
　　　　　　　c = c + 1
　　　　　　　Aファイルの行番号であるcの値を更新して、次のデータの処理を行いたい
　　　　　　　Else
             b =  b+ 1
             比較ファイルの行番号であるbの値を更新し、2つめのifに戻り再びAファイルの値と比較したい
　　　　　　End if
       End if
    Next j
  Next i

この時点で色々疑問点が出てきてしまい、手が止まってしまいます。
やりたいことは、

1. 一致するデータがあれば、その時点で処理を終了し、Aファイルの行番号であるcの値を更新し、Aファイルの次のデータの処理に移りたい

2. 無ければ、比較ファイルの行番号であるdの値を更新する。1つの比較ファイルの全データと比較した場合は、次の比較ファイルと比べる　(例)Bファイル（Sheet(1)に格納）は最後のデータが入っているCC14まで比較したが、一致は無かったので、次はCファイル(Sheet(2)に格納)のCC2から比較を始める。Cファイルは、CC11までデータがある。CファイルをCC11まで比較ても一致はなかったので、次はDファイルのCC2から比較を始める

3.全てのファイルと比較してもなかった場合はH列に不一致と入力し、Aファイルの行番号であるcの値を更新し、次のデータの処理に移りたい

4 それぞれ、比較ファイルのデータ数が違うのでForの上限を何にすれば良いのか?

5 配列Sheetはどこで要素番号を更新すれば良いのか

6 Aファイルのデータが「初期」では無い場合の処理も記述したい

if文をかなりネストするのではないかと、思っています。
どのような構想にすればいいのかだけでも良いので、ご教授願います。

Comments

[mako1972]

丁寧にご説明ありがとうございます。
ThisWorkbook.WorkSheets("イベント")とは何ですか。

最近の質問者にうんざりするんですが、ずらずらと条件だけ並べる方が多いのですが、
どのような表があって、どのような結果がほしいか、簡単な表で
お示しいただいたほうがレスがつくと思います。

回答される方もそれに付き合うだけの時間があればいいと思いますが
結果、何をいいたいのかわからない時点でスルーすると思います。

数日して回答がつかない理由は、説明が冗長でよけいな前置きが多いこと。
前置きが回答者にとってどうでもいい内容など意識されたほうがよいと思います。

[yakumo02]

ご指摘ありがとうございます
ThisWorkbook.WorkSheets("イベント")はAファイルのイベントシートです

[mako1972]

削除

[meg_]

> 4 それぞれ、比較ファイルのデータ数が違うのでForの上限を何にすれば良いのか?
上記の意味が分かりません。
データ数はファイルを開けば取得できませんか？　またi,jをループ内で利用していないので何がしたいのか分かりません。

[yakumo02]

仰る通りでした
考え直してみます

Answer 1

ん～。
プログラムに向かう前に予備知識を整理しましょう。

1）読むのはセルの値、書くのもセルの値
→つまり操作するのはセルなので、まずはセル範囲を特定できるようになりましょう。

2）ブックはいくつかのシートで構成されており、シートは多数のセルで構成されている。
→つまり、常にどのブックのどのシートのどのセルの話か常に意識して話（プログラムも）進めましょう。逆にいうと、「このセル」と一回決めたら、「このセル」というものには、
どのブックのどのシートかという情報も含まれるということです。

3）Windowsから見たら、ファイルという存在ですが、それをエクセルで開いたら、
エクセル君は「Workbook」と呼びます。
→つまりファイル形式がなんであっても、エクセルで開けたら、Workbookという存在

4）セル範囲は各セルの集まり(=集合体=コレクション）であり、
コレクションの各要素(セル範囲の場合は集合体を構成する各セル）を巡回するには、
For Each ステートメントを用いる。

5)エクセルには一括で値や数式をセットできる機能がある

6）パソコンは繰り返しの作業は得意なので、
とりあえず、1つできるように考える。

7)各プロシージャは別のプロシージャから呼び出せる

なのでまずは、動的に変化する(開くファイルによって変わる）セル範囲を特定できるように、
なるところから始めてみてはいかがでしょう。

Sub test()
    Dim rngTarget As Range  '操作対象範囲
    Dim rngTop As Range     '一番上のセル
    Dim rngBottom As Range  '一番下のセル
    
    Set rngTop = ThisWorkbook.Worksheets("イベント").Cells(1, "G")
    Set rngBottom = ThisWorkbook.Worksheets("イベント").Cells(ThisWorkbook.Worksheets("イベント").Rows.Count, "G").End(xlUp)
    Set rngTarget = Application.Range(rngTop, rngBottom)
    
    rngTarget.Select
End Sub

参考サイト＞
セル範囲の指定方法:Excel VBA入門

これが出来たら、
比較元も比較先も同じように可変でセル範囲が取得できるようになると思います。
そして、ほぼほぼ同じことを繰り返すので、
シートと列を与えてデータのセル範囲を得られるよう、
関数化し再利用できるようにしましょう。

Sub test2()
    Dim rngData As Range
    Dim rngList As Range
    
    Set rngData = GetRange(ThisWorkbook.Worksheets("イベント"), "G")
    Set rngList = GetRange(Workbooks(Workbooks.Count).Worksheets(1), "CC")
    'Workbooks(Workbooks.Count)は、最後に開いたブックの意になります。
    
    MsgBox "比較元セル範囲： " & rngData.Address(False, False, , ture)
    MsgBox "比較先セル範囲： " & rngList.Address(False, False, , ture)
End Sub

'シートと列番号(アルファベット)を指定して、データ範囲を取得する自作関数
Function GetRange(ByRef ws As Worksheet, ByVal col As String) As Range
    Dim rngTop As Range                          '一番上のセル
    Dim rngBottom As Range                       '一番下のセル
    
    Set rngTop = ws.Cells(1, "G")
    Set rngBottom = ws.Cells(ws.Rows.Count, "G").End(xlUp)
    Set GetRange = Application.Range(rngTop, rngBottom)
End Function

意図するセル範囲が特定できたら、
ワークシートにCountif関数で数えて存在確認をしてみましょう。

Sub test3()
    Dim rngData As Range
    Dim rngList As Range
    
    Set rngData = GetRange(ThisWorkbook.Worksheets("イベント"), "G")
    Set rngList = GetRange(Workbooks(Workbooks.Count).Worksheets(1), "CC")
    '数式の入力
    Set rngData.Offset(, 1).Formula = "=If(CountIf(" & rngList.Address(, , , True) & _
                                    "," & rngData(1).Address(False, False) & ",""一致"",NA())"
End Sub

ここまで出来たら、あとはエラー値のセルの数式をを次のファイルのセル範囲に変えて
繰り返すだけだと思います。
最終的にエラー値が残ったらそれを「不一致」という文字に置き換えてやればいいかと。
まずはここまで、トライしてみてはいかがでしょうか？
※わからない用語は辞書なりネットなりで調べてみてください。
それでもわからなければ聞いてください。

Comments

[yakumo02]

ありがとうございます
しっかりと基礎を固めて行こうと思います

Answer 2

「考え途中の構想」と言うコードが未完成なので(例えば、ループ用のj変数の用途が分かりません）読むこととそれについて述べることは避けますが、

4 それぞれ、比較ファイルのデータ数が違うのでForの上限を何にすれば良いのか?

データの終わりというのは分かりますよね。あるいは、分かるようにデータを工夫することはできますね。比較ファイルと言われているのがExcelのシートなのかテキストファイルなのか分かりませんが、Excelシートであれば行データが無くなった（セルが空）のであればそこが終端のはずです。ループの回数と言うよりは、それを検出することを終了条件として回れば良いです。

6 Aファイルのデータが「初期」では無い場合の処理も記述したい

"初期"かそうでないかと言うのは可変部分でありますが、判定し、処理をする全体のフローはほぼ同じはずです。"初期"の文字列部分をパラメータとして、うまく関数化すれば良いだけの話です。

if文をかなりネストするのではないかと、思っています。

そんなことは無いと思います。ループとして繰り返す部分と、関数として切り出す部分をうまくすれば、少なくともひとつの処理で極端にifでネストするようなことはなくなるはずです。

全部の処理を一度に考え、コードを書こうとすることは止めましょう。そうしようとしているので、考えと手が停まっているのだと思います。

(1) 1つ目のやりたいこと：

もしAファイルのG列のデータが「初期」で、配列に入っているどれかのファイルのデータに初期という文字が1つでもあれば、真横のH列に「一致」という文字を入力。無ければ不一致を入力。

"初期"と言う文字列を一つ目の引数とし、ファイル名を2つ目の引数とする関数を考えます。その関数では指定されたファイルから指定された"初期"の文字列を探し、見つかったか見つからなかったか、だけを返します。その関数を呼び出した上位側の処理では、その結果をもって「一致」か「不一致」を真横のH列とやらにセットすれば良いです。

(1) 2つ目のやりたいこと：

AファイルのG列のデータが「初期」以外である場合は、配列に入っているどれかのファイルのデータに、Aファイルと同じデータが1つでもあれば、「一致」という文字を入力。無ければ不一致を入力。

これも良く考えてみると、"初期"が"初期"ではない、別の文字列に変わっただけではありませんか。関数化した場合、その文字列部分が可変なだけで、基本的な処理フローは同じはずです。

全体の処理を考え、ループで回す部分と共通化できる部分、つまり関数化できる部分をうまく切り分けることを考えましょう。そうすることで見通しも良くなり、それぞれ単独でのデバッグや確認もし易くなるはずです。

---

**追記しました：(2020/08/08 14:12) **

先に示した私の回答をVBAっぽい疑似コードで示すと、以下のようになります。細かい部分は省いているものの、処理のイメージはおおむね伝わるかと思いますがいかがでしょうか。必ずしも同じ処理フローにしなければいけない訳ではなく、あくまで一例です。

'* ※これは、VBAっぽい疑似コードです
'* 指定の文字列が指定のファイルに含まれているを判定する関数
Function IsContained(target, filename)

   Loop filenameで指定されたファイルの全行を処理するまでループ

       If 現在の対象行に対象文字列(target)が存在する Then
           '* 1個でも見つかればそれ以上処理する必要はないので終了
           Return "見つかった"
       End If

   End Loop

   Return "見つからなかった"

End Function

'**************************
'* 全体の処理
Loop AファイルのG列の末端までループ
    target = AファイルのG列の現在行から対象文字列を取得

    Loop 配列シートの件数分ループ

        filename = 配列シートの現在行からファイル名を取得

        '* 対象文字列とファイル名を引数として関数呼び出し
        IsContained(target, filename)
        If 関数の返り値 = 対象文字列が見つかった Then
            Aファイルの現在行のH列に「一致」をセット
        Else
            Aファイルの現在行のH列に「不一致」をセット
        End If

    End Loop

End Loop

Comments

[yakumo02]

回答ありがとうございます
関数の呼び出しはifの中で行うのでしょうか？

[dodox86]

> 関数の呼び出しはifの中で行うのでしょうか？

えーと、それは最終的な造り次第でしょうし、ifの条件にもよるはずなので一概に言えないはずなのですが。条件の判定は関数内で抱えた方が良いようなものもあり得ます。例えばどんな状況でのifの分岐を言われていますか。

[dodox86]

疑似コードとして追記してみましたのでご覧ください。細かい部分で過不足があれば、ご自身で取捨選択してもらえると良いです。

[yakumo02]

追記ありがとうございます。大変参考になります。
質問なのですが、

内側のループである、Loop 配列シートの件数分ループ
で「一致」をセットした場合、直ぐに内側のループを抜け出すことはできますでしょうか？

[dodox86]

> 内側のループである、Loop 配列シートの件数分ループで「一致」をセットした場合、直ぐに内側のループを抜け出すことはできますでしょうか？

それはそのようにコードを書けば当然できます。VBAの文法のお話ですね。DoループならExit Doですし、ForループならExit Forでしょう。VBAのリファレンスを見て構文を確認してください。
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/doloop-statement
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/fornext-statement
更に付け加えれば、ループの条件に「ループ続行/終了」を示すフラグを使えばWhileでもできるでしょうし、ループが関数内で回るのであれば、関数からReturn でも同等の結果を得られるように書けると思います。要望の動きをするようにコードを書いてください。

[yakumo02]

失礼しました！基礎的なことを忘れておりました
ご回答ありがとうございました！

[yakumo02]

先日はお世話になりました。
今日一日実装しまして、また新たな悩みがでてしまいました。
一番新しい質問に、力を貸していただくことはできないでしょうか？