【Power Query】ExcelデータをVBAで前処理する際の基本知識【Excel】

Itoi Yoshihiko

横浜国立大学理工学部建築都市環境系学科卒
一級鉄筋技能士

ExcelシートをVBAで効果的に操作することは、多くのタスクを自動化し、作業効率を向上させるための強力な手段です。

この記事では、操作するシートの決め方や、データの前処理に必要な能力(セルからデータを取得/セルにデータを入力)、Excel関数をVBAで操作する方法について詳しく解説します。

【Power Query】VBAの変数・条件分岐・繰り返し処理を使いこなそう【Excel】

操作するシートを決める

まず最初にVBAを使って操作するシートを指定します。

インデックス(0～)かシート名(“シート名”)で指定する

Sheet 1～6などの数字の部分がインデックスです。

sheet1(メイン)などの(メイン)の部分がシート名です。

Sub excel_test()

    Dim ws As Worksheet
'どちらでも可
'    Set ws = ThisWorkbook.Sheets(1)
    Set ws = ThisWorkbook.Worksheets(1)    

'どちらでも可
    Debug.Print ws.Name
    Debug.Print ThisWorkbook.Worksheets("ピボットテーブル").Name

End Sub

Sub excel_test()

Dim ws As Worksheet

'どちらでも可

' Set ws = ThisWorkbook.Sheets(1)

Set ws = ThisWorkbook.Worksheets(1)

'どちらでも可

Debug.Print ws.Name

Debug.Print ThisWorkbook.Worksheets("ピボットテーブル").Name

End Sub

セルの操作

データの前処理の基本は、セルに値を入力する/セルから値を取得する事です。

このように聞くと、すごく難しそうに感じますが覚えることはとても少ないのでサッと習得してしまいましょう。

CellsとRangeでセルに値を入力

Sub excel_test()

    Dim ws As Worksheet
'    Set ws = ThisWorkbook.Sheets(1)
    Set ws = ThisWorkbook.Worksheets(1)
       
'    セルの操作 セルに入力
'    A1のセルにhelloと入力
     ws.Cells(1, 1).Value = "hello"
'    A1:B19の範囲にhelloと入力
     ws.Range("A1:B19").Value = "hello"

End Sub

Sub excel_test()

Dim ws As Worksheet

' Set ws = ThisWorkbook.Sheets(1)

Set ws = ThisWorkbook.Worksheets(1)

' セルの操作セルに入力

' A1のセルにhelloと入力

ws.Cells(1, 1).Value = "hello"

' A1:B19の範囲にhelloと入力

ws.Range("A1:B19").Value = "hello"

End Sub

シート1のA1:B19の範囲にhelloが入力されたのを確認します。

Debug.Printでセルから値を取得

セルにデータを入力することができたら、今度はセルに入力されているデータを取得してみます。

Sub excel_test()

    Dim ws As Worksheet
'    Set ws = ThisWorkbook.Sheets(1)
    Set ws = ThisWorkbook.Worksheets(1)
       
'    セルの操作
'    A1のセルにhelloと入力
     ws.Cells(1, 1).Value = "hello"
'    A1:B19の範囲にhelloと入力
     ws.Range("A1:B19").Value = "hello"
    
    
     Debug.Print ws.Cells(1, 1).Value

End Sub

Sub excel_test()

Dim ws As Worksheet

' Set ws = ThisWorkbook.Sheets(1)

Set ws = ThisWorkbook.Worksheets(1)

' セルの操作

' A1のセルにhelloと入力

ws.Cells(1, 1).Value = "hello"

' A1:B19の範囲にhelloと入力

ws.Range("A1:B19").Value = "hello"

Debug.Print ws.Cells(1, 1).Value

End Sub

イミディエイトにhelloと表示されるのを確認します。

VBAでExcelの関数を使う

ここまで習得できれば、前処理の基本を習得したことになります。

続いては、Excel関数をVBAで使用してみましょう。

VLOOKUPをVBAで使用してみる

図のような「名前、年齢、testの点数」を例にしてVLOOKUP関数をVBAで動かしてみます。

Sub excel_test()

    Dim ws As Worksheet
'    Set ws = ThisWorkbook.Sheets(1)
    Set ws = ThisWorkbook.Worksheets(1)
    
 
'    3.VLOOKUP関数を使ってみる

    Dim result As Integer
    On Error Resume Next 'エラーが発生した場合の処理をスキップ
    result = WorksheetFunction.VLookup("田中", ws.Range("A1:C11"), 3, False)
    On Error GoTo 0 'エラー処理の再設定   
    
    Debug.Print result 

End Sub

Sub excel_test()

Dim ws As Worksheet

' Set ws = ThisWorkbook.Sheets(1)

Set ws = ThisWorkbook.Worksheets(1)

' 3.VLOOKUP関数を使ってみる

Dim result As Integer

On Error Resume Next 'エラーが発生した場合の処理をスキップ

result = WorksheetFunction.VLookup("田中", ws.Range("A1:C11"), 3, False)

On Error GoTo 0 'エラー処理の再設定

Debug.Print result

End Sub

イミディエイトに田中さんのtestの点数「65」が表示されるのを確認します。

ポイント

“On Error Resume Next” はエラーが出たときのおまじない

VBAコードがエラーを発生させた場合に、エラーメッセージを表示せずに、次の行のコードを実行し続けることができます。

まとめ

この記事では、操作するシートの決め方や、データの前処理に必要な能力(セルからデータを取得/セルにデータを入力)、Excel関数をVBAで操作する方法について解説しました。

食わず嫌いしてVBAを敬遠していましたが、意外にも覚えることは少なくて簡単ですね！

【Power Query】ピボットテーブルで集計処理をマスターしよう【Excel】