テーブル操作第8回目です。第6回目の「Excel VBA テーブルの列を追加／削除する方法について」で
追加した列が空っぽなのでなんだか寂しいですね(^^;
というコメントがありました。今回はVBAでテーブルに追加した「列」に代入する処理について解説します。
「列」を追加・挿入するのは、その「列」に何かを代入するために行います。その方法について勉強していきましょう(^^)/
【この記事でわかることは】
・テーブル内に追加した「列」にデータを代入する方法がわかります
・「一般の表」と「別テーブル」の参照の違いがわかります
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
前回記事は、テーブルのデータ行に「行」を追加（挿入）または削除する方法についての解説記事でした。覗いてみてね(^^)/
テーブルに追加した列にデータを代入する
追加した列には、どんなデータを代入するでしょうか。考えてみましょう。
・一般的には、数式を代入することが多いのではないかと思います。
・データの場合は、手入力するよりVBAマクロなどでデータを書き入れることが多いのではないでしょうか。
追加した列に数式を代入する方法
テーブル機能の素晴らしさは、列内の1つのセルに数式を代入すると、同じ数式が全部のセルへ自動的に代入されることです。数式を変更する場合も同様で、一か所の数式を変更するとほかの数式もすべて同じように変更されます。
前回記事のサンプルコードに追加してみます
VBAマクロで数式を代入するときも、1つのセルに入れれば列内すべてに同じ数式が適用されることになります。
・前回記事のコードはこちらです。
'テーブルの2列目に列を追加挿入してタイトルを設定
Sub AddColumnNameInsertTbl()
ActiveSheet.ListObjects(1).ListColumns.Add(2).Range(1) = "クリック順位"
End Sub
・この追加された列「クリック順位」に計算式を代入します。
・計算式は、「順位」ですので順位付けの関数「RANK.EQ」を使います。
・計算式 “=RANK.EQ([@クリック数],[クリック数])“
・@の付いた[@クリック数] は同レコードのセルです。[クリック数] は、データ列です。
'テーブル2列目に列を追加してタイトルと数式を代入
Sub AddColTblSample()
With ActiveSheet.ListObjects(1)
.ListColumns.Add(2).Range(1) = "クリック順位"
.ListColumns(2).Range(2) = "=RANK.EQ([@クリック数],[クリック数])"
End With
End Sub・ステップ実行している動画です。
・仮に「クリック率」とした場合、計算式は “=[@クリック数]/[@表示回数]” となります。
VLOOKUP関数で別の表からデータを代入する
・データを代入する方法としては、VLOOKUP関数を使って別の表を参照して代入することが多いと思います。
・用意した表は次の二つです。「Page」列のデータを取得します。
一般の表「セル範囲」から代入する場合
'テーブル2列目に列を追加してタイトルと数式VLOOKUPを代入
Sub AddColTblVlookup01()
With ActiveSheet.ListObjects(1)
.ListColumns.Add(2).Range(1) = "Page_URL"
'参照先が一般の「表」セル範囲の場合の数式例
.ListColumns(2).Range(2) = _
"=VLOOKUP([@検索キーワード],Sheet3!$A$1:$B$19,2,0)"
End With
End Sub・ステップ実行動画がこちらです。
・追加挿入した列名を「Page_URL」としています。
・代入する計算式は、”=VLOOKUP([@検索キーワード],Sheet3!$A$1:$B$19,2,0)“
・テーブルのセル参照は、[@列見出し名]
・一般の表「セル範囲」の参照は、「シート名!対象セル範囲の番地」のように書きます。
別のテーブルから代入する場合
'テーブル2列目に列を追加してタイトルと数式VLOOKUPを代入
Sub AddColTblVlookup02()
With ActiveSheet.ListObjects(1)
.ListColumns.Add(2).Range(1) = "Page_URL"
'参照先が別テーブルの場合の数式例
.ListColumns(2).Range(2) = _
"=VLOOKUP([@検索キーワード],Query[[Query]:[Page]],2,0)"
End With
End Sub・代入する計算式は、”=VLOOKUP([@検索キーワード],Query[[Query]:[Page]],2,0)“
・別のテーブルの参照は、テーブル名[[範囲先頭の列名]:[範囲最後の列名]] のように書きます。
まとめ（おわりに）
・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
テーブルの列に計算式を代入する方法の解説でした。テーブルの場合は1か所に代入すれば列全部に適用されるからすごく便利ですよね。
変更した場合も全部変更されるんですからテーブルってすごいですね。扱い方をしっかり覚えて活用できるようにしたいと思いま～す(^^)/
次はテーブル操作9回目です。テーブルの行データを編集する方法について解説したいと思います(^^)
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルをダウンロードできるようこのリンク先に登録しています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回分かったことは】
・テーブル内の列に計算式を代入する方法
「外部の一般の表（セル範囲）を参照する計算式を代入する方法」
「別のテーブルのデータを参照する計算式を代入する方法」