テーブル操作第9回目です。テーブルのレコード（行データ）のセルに対して何かをしたい場合どうしたらよいのでしょうか。
テーブルからデータを取り出したり、データを編集したりするのはどうすればよいのでしょうか。その方法について勉強していきましょう(^^)/
前回、追加した列に計算式を代入した際に、先頭のセルに代入した方法が参考になる気がします(^^;
【この記事でわかることは】
・テーブルのデータを編集する方法がわかります
・VLOOKUP関数でテーブルからデータを取得する方法がわかります
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
前回記事は、テーブルの列に計算式を代入する方法の解説記事でした。覗いてみてね(^^)/
テーブルの行データを編集する
テーブルの行データ全体を操作する場合、行を追加したり削除したりする方法は、テーブル操作第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関数でテーブルからデータを取得する
・ここでは逆に、テーブルのデータを参照してデータを取得する方法を解説します。
・設定は前回使ったものを再活用して利用します。
・右側の「Sheet2」のテーブルはA列に空欄列を追加し、あえてずらして使うようにします。
・左側の「Sheet3」のB列は空欄にしておきます。
VLOOKUP関数でテーブルを構造化参照する
・画像はセルに入力た数式です。
・テーブルを参照したVLOOKUP関数です。第3引数の列番号も数値指定ではなく、構造化参照にしています。こうしておけば、テーブルが変化した場合でもデータを正しく取得できます。
【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回目です。
テーブルの集計行ついて解説したいと思います(^^)
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルをダウンロードできるようこのリンク先に登録しています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回分かったことは】
・テーブルの特定のデータ行やセルを編集する方法がわかりました
・VLOOKUP関数でテーブルからデータを取得する方法がわかりました
・構造化参照の方法がわかりました