各シートから値を抽出する

各シートから値を抽出する

　Excelで業務を行っていると、各所に散らばったデータをまとめて抽出したい事があります。
　ここでは、各シートから特定の部分を抜き出して、「抽出」というシートにまとめる例を示していきます。
　
　データの抽出作業では、抽出するための条件設定も大事ですが、データ抽出しないための設定も重要です。
　この事に注目してコードを見てみましょう。

サンプルデータを用意

　今回はプログラム用に、抽出元となるサンプルのデータシートを用意します。
　ここではわかりやすいように、「シート番号 行番号 : 列番号」をそれぞれのセルに書き込んで、きちんと抽出されたか確認できるようにします。

　シートをいくつか作成して、各シートをアクティブにした状態で、以下のコードを実行してください。

Sub mark()
    Dim i As Long, j As Long
    
    For i = 1 To 50
        For j = 1 To 50
            Cells(i, j) = ActiveSheet.Index & " " & i & ":" & j
        Next j
    Next i
    
End Sub

　実行すると以下のようにセルにデータが配置されます。

　更に、データの無いシートと「抽出」という名前の抽出用シートも用意しておきましょう。

決まった行と列で抽出していく

　規則性のある、特定の行、列にあるデータを抽出したい時の例です。
　
　ここでは、3行目から9行おきに並ぶ B,D,L列をそれぞれ、「抽出」シートのA,B,C列にコピーしています。
　
　行に関しては、 trg 変数がコピー元。cnt 変数がコピー先を指定しています。
　trg 変数は最初にオフセットとして3行目で初期化し、その後は9行づつ追加していきます。
　コピー先はcnt 変数が、1行目から逐次追加されていきます。

Sub sample1()
    Dim i As Long
    Dim cnt As Long, trg As Long
    
    Dim sh As Worksheet
    Set sh = Worksheets("抽出")
    
    cnt = 1
    For i = 1 To Worksheets.Count
        
        If Worksheets(i).Name = "抽出" Then GoTo LABEL
        
        If Worksheets(i).Range("A1") = "" Then GoTo LABEL
        
        With Worksheets(i)
        trg = 3
        Do While .Cells(trg, "A") <> ""
            sh.Cells(cnt, "A") = .Cells(trg, "B")
            sh.Cells(cnt, "B") = .Cells(trg, "D")
            sh.Cells(cnt, "C") = .Cells(trg, "L")
            trg = trg + 11
            cnt = cnt + 1
        Loop
       
        End With
LABEL:
    Next i
End Sub

　全体として 変数 i がシートの数だけループしていきます。
　その際、「抽出」シートは除外しています。
　また、シートのA1に値が無い場合は、データシートではないと見なしてこちらも除外しています。
　どちらも　GoTo LABEL　で処理を飛ばしています。
　
　さらに、以下のように Do While でＡ列に値がなくなるまで繰り返しています。

　Do While .Cells(trg, “A”) <> “”

行単位の抽出

　行を抜き出す場合は、転記するのではなく copy コマンドを使用する事になります。
　書き方は何通りかありますが、今回は、Rows(行番号)　を使った例を示します。
　ここでは 9行目、12行目、20行目、23行目、31行目、34行目、、、
　という順でコピーしています。
　
　除外の例は、上記のサンプルと同じなので省略します。

Sub sample2()
    Dim i As Long
    Dim cnt As Long, trg As Long
    
    Dim sh As Worksheet
    Set sh = Worksheets("抽出")
    
    cnt = 1
    For i = 4 To Worksheets.Count
        
        If Worksheets(i).Name = "抽出" Then GoTo LABEL
        
        If Worksheets(i).Range("A1") = "" Then GoTo LABEL
        
        With Worksheets(i)
        trg = 9
        Do While .Cells(trg, "A") <> ""
            .Rows(trg).Copy sh.Rows(cnt)
            cnt = cnt + 1
            .Rows(trg + 3).Copy sh.Rows(cnt)
            cnt = cnt + 1
            
            trg = trg + 11
        Loop
        End With
LABEL:
    Next i
End Sub

条件を定義して抽出

　今度はIf文で、セルの値で条件分岐して抽出する方法を見ていきましょう。
　今回は、A列に　”3:1″　という文字列が含まれている条件で行を抽出します。
　ある文字列が含まれている条件は、InStr関数を使うのが定番です。
　InStr関数は、第一引数の文字列の中に、第二引数の文字列が含まれている場合、その位置を数値で返してくれます。
　もし、含まれていない場合は、０が返ってくるので、これを利用します。
　
　結果的に、3行目、13行目、23行目、、、という感じでヒットします。

Sub sample3()
    Dim i As Long
    Dim cnt As Long, trg As Long
    
    Dim sh As Worksheet
    Set sh = Worksheets("抽出")
    
    cnt = 1
    For i = 4 To Worksheets.Count
        
        If Worksheets(i).Name = "抽出" Then GoTo LABEL
        
        If Worksheets(i).Range("A1") = "" Then GoTo LABEL
        
        With Worksheets(i)
        
        trg = 1
        Do While .Cells(trg, "A") <> ""
            If InStr(.Cells(trg, "A"), "3:1") > 0 Then　　’条件分岐
                .Rows(trg).Copy sh.Rows(cnt)
                cnt = cnt + 1
            End If
            trg = trg + 1
        Loop
   
        End With
LABEL:
    Next i
End Sub

　条件分岐に関しては、色々なバリエーションが考えられるので、アイデア次第で用途が広がっていくと思います。

まとめ

　データ抽出の例を3つ見てきました。
　データの並びや特徴を見つけて、それに合わせて条件分岐していきます。
　最初の例では、行が規則的で、列が決まっている場合。
　次の例では、行が規則的で、その行を全て。
　最後の例では、セルの値を見て条件と照らし合わせる場合。
　これらは、Do While の内部で条件を設定すればOKでした。
　
　その他に、「抽出」シートやデータが存在しないシートを除外する処理を加えておくことも大事です。（GoTo LABEL の部分ですね。）

　データを抽出するというシチュエーションは、Excelで業務を行っているとよく出てきます。
　複雑そうに見えますが、基本を押さえていれば初心者でも出来ますので、是非挑戦してみてください。