本ページには広告が含まれています。

Excel VBA テーブルのデータを編集・取得する方法

Excel VBA テーブルのデータを編集・取得する方法
2021.03.07

テーブル操作第9回目です。テーブルのレコード（行データ）のセルに対して何かをしたい場合どうしたらよいのでしょうか。

くるみこ
くるみこ

テーブルからデータを取り出したり、データを編集したりするのはどうすればよいのでしょうか。その方法について勉強していきましょう(^^)/

前回、追加した列に計算式を代入した際に、先頭のセルに代入した方法が参考になる気がします(^^;

【この記事でわかることは】
・テーブルのデータを編集する方法がわかります
・VLOOKUP関数でテーブルからデータを取得する方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA テーブルに追加した列に数式を代入する方法
Excel VBA テーブルの列に数式を代入する方法の解説です。一般の表「セル範囲」を参照する計算式の記述と別テーブルを参照する計算式の記述方法をVLOOKUP関数を使用した例文でそれぞれを解説しています。サンプルファイルも用意しています。
www.kurumico.com
くるみこ
くるみこ

前回記事は、テーブルの列に計算式を代入する方法の解説記事でした。覗いてみてね(^^)/

スポンサーリンク

テーブルの行データを編集する

テーブルの行データ全体を操作する場合、行を追加したり削除したりする方法は、テーブル操作第7回目の「VBAでテーブルのデータ行を追加したり、削除したりする方法について」で解説しました。

これを応用すれば色々なことができそうです。

特定のデータ行を編集する

せっかく過去記事で使ったサンプルがあるのですから、それを編集してみましょう。

行削除のサンプルコードを編集して変えてみます

・前回の削除「.Delete」の部分をフォントの色変更にしただけのコードがこちらです。

'フィルタで抽出した行のフォントを赤くする
Sub EditRowFilterTbl()
    With ActiveSheet.ListObjects(1).DataBodyRange 'データ領域
        .AutoFilter Field:=1, Criteria1:="テスト入力" '「引数名:=」は省略可能
        .EntireRow.Font.ColorIndex = 3　'抽出された行のフォントを赤にする
        .AutoFilter 1
    End With
End Sub

・書き換えたのは、5行目だけです。
・行全体のフォントを赤に変更できました。

特定セルだけ編集するには

・第4回目記事の「Excel VBA テーブル内の範囲や特定位置を指定する方法」が参考になります。
・セルの指定は ListColumns(n) を使います。（nは位置番号）
・テーブルの位置をあえて1列ずらしてからテストしたコードがこちらです。

'フィルタで抽出したセルのフォントを赤くする
Sub EditCellFilterTbl()
    With ActiveSheet.ListObjects(1) 'データ領域
        .DataBodyRange.AutoFilter Field:=1, Criteria1:="テスト入力"
        '抽出された行の特定セルだけフォントを赤にする
        .ListColumns(1).DataBodyRange.Font.ColorIndex = 3
        .ListColumns(3).DataBodyRange.Font.ColorIndex = 3
        .DataBodyRange.AutoFilter 1
    End With
End Sub

・ステップ実行している動画です。

・フィルタで複数行選択となるようにしてテストしました。
・先ほどのサンプルとの違いは、Withブロックの指定で DataBodyRange を外しています。
・それはセルの特定に「ListColumns」を使ってテーブルの列を指定する必要があるためです。
・6行目で、.ListColumns(1).DataBodyRange のテーブル列番1で列を指定しています。
・7行目は、.ListColumns(3).DataBodyRange 列番3を指定しています
・DataBodyRange を先に取得している場合は「Columns」で列を指定することも可能ですが、この場合はテーブルの列ではなく、シートの列を返しています。これを使う場合は、テーブルの位置を加味して調整（プラス）する必要があるので面倒です。「ListColumns」ならテーブルの列ですからそのまま使えるということです。

スポンサーリンク

テーブルからデータを取得する

・前回記事でテーブルのセルにVLOOKUP関数で別の表を参照して代入する方法を紹介しました。

VLOOKUP関数でテーブルからデータを取得する

・ここでは逆に、テーブルのデータを参照してデータを取得する方法を解説します。

Sheet3 テーブルではない一般の表「セル範囲」
Sheet2 テーブル名「Query」

・設定は前回使ったものを再活用して利用します。
・右側の「Sheet2」のテーブルはA列に空欄列を追加し、あえてずらして使うようにします。
・左側の「Sheet3」のB列は空欄にしておきます。

VLOOKUP関数でテーブルを構造化参照する

・画像はセルに入力た数式です。

セルに入力した数式

・テーブルを参照したVLOOKUP関数です。第3引数の列番号も数値指定ではなく、構造化参照にしています。こうしておけば、テーブルが変化した場合でもデータを正しく取得できます。

【第3引数にCOLUMN関数を使った場合の解説】=VLOOKUP(A2,Query,COLUMN(Query[Page])-COLUMN(Query)+1,FALSE)
・第2引数「Query」はテーブル名です。テーブル全体を範囲指定しています。
　列指定にする場合は、Query[[Query]:[Page]] のように記述します。
・第3引数「COLUMN(Query[Page])–COLUMN(Query)+1」は、
　「「目的の列見出し位置」–「テーブルの開始列位置」+1」を表しています。
　テーブル開始位置がA列から始まっていない場合でも対応できるようにしています。

【第3引数にMATCH関数を使う場合】
=VLOOKUP(A2,Query,MATCH(“Page”,Query[#見出し],0),FALSE)
テーブル内の「見出し」を文字列で検索してその位置を返しています
こちらの場合は位置調整の必要はありません！

【MATCH関数を使う場合】の方が短くて良さそうなんですが、VBAで使用する場合 “Page” の処理がどうやってもうまくいきませんでした。ハイフンの処理に Chr(34) を使ってもダメでしたので 【COLUMN関数】を使用してVBAで計算式を書き込んでみます。

VBAで計算式を書き込むコード

'VLOOKUP関数でテーブルからデータを取得する
Sub GetTableVlookup01()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Worksheets("Sheet3")
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row '最終列までループ
        '計算式代入
        ws.Cells(i, 2) = _
            "=VLOOKUP(A" & i & _
            ",Query[[Query]:[Page]],COLUMN(Query[Page])-COLUMN(Query)+1,FALSE)"
    Next
End Sub

・次のコードは、貼り付けた計算式を値に置き換えるようにしたものです。
・追加したのは最後の1行だけです。

'VLOOKUPでテーブルからデータ取得後値にする
Sub GetTableVlookup02()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Worksheets("Sheet3")
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row '最終列までループ
        '計算式代入
        ws.Cells(i, 2) = _
            "=VLOOKUP(A" & i & _
            ",Query[[Query]:[Page]],COLUMN(Query[Page])-COLUMN(Query)+1,FALSE)"
    Next
    '計算式を値にして消す
    ws.Range(Cells(2, 2), Cells(i, 2)).Value = _
            ws.Range(Cells(2, 2), Cells(i, 2)).Value
End Sub

・VLOOKUP関数の例を紹介しましたが、他の関数でも基本的にこのような方法でテーブルからデータを取得できますので参考にしてください。

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

テーブルのデータを編集する方法とデータを取得する方法の解説でした。テーブルの場合は1か所に代入すれば列全部に適用されましたが、今回はループ処理で書き込みました。

構造化参照の使い方は少し練習して覚えるようにしたいと思います。構造化参照で計算式を設定すればテーブルが変更されてもそのままで反映できるからいいですね。(^^)

くるみこ
くるみこ

次はテーブル操作10回目です。
テーブルの集計行ついて解説したいと思います(^^)

【今回分かったことは】
・テーブルの特定のデータ行やセルを編集する方法がわかりました
・VLOOKUP関数でテーブルからデータを取得する方法がわかりました
・構造化参照の方法がわかりました

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようこのリンク先に登録しています！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com