VBA100本ノック 45本目：テーブルに列追加
テーブルに列を追加した横合計の計算式を入れる問題です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。
出題
出題ツイートへのリンク
#VBA100本ノック 45本目
シートにB2から始まる5列（列1,列2,列3,列4,列5）のテーブルあります。
・列3の後ろに列挿入して列1から列3の合計列を作成、見出しは"合計列1"
・テーブルの右端に列4から列5の合計列を作成、見出しは"合計列2
出来上がりは画像を参照してください。
※シートは任意
サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_45.xlsm
https://excel-ubara.com/vba100sample/VBA100_45.zip
シートにB2から始まる5列（列1,列2,列3,列4,列5）のテーブルあります。
・列3の後ろに列挿入して列1から列3の合計列を作成、見出しは"合計列1"
・テーブルの右端に列4から列5の合計列を作成、見出しは"合計列2
出来上がりは画像を参照してください。
※シートは任意
https://excel-ubara.com/vba100sample/VBA100_45.xlsm
https://excel-ubara.com/vba100sample/VBA100_45.zip
VBA作成タイム
この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。
他の人の回答および解説を見て、書いたVBAを見直してみましょう。
頂いた回答
解説
テーブルの要素にアクセスするオブジェクトの辿り方は複数あります。
ListColumns
ListColumn
DataBodyRange
これらの組み合わせ方でVBAの書き方はいろいろ変わってきます。
ListColumns.Add
これはListColumnを返します。
ListColumn.DataBodyRangeこれは当該列のデータ部全行になります。
ListObject.DataBodyRange
これはデータ部全体になります。
同じ数式なので全行に入れても良いし、先頭セルにだけ入れても結果は同じになります。
記事にはVBAコードと若干の説明を追加しました。
ListColumns
ListColumn
DataBodyRange
これらの組み合わせ方でVBAの書き方はいろいろ変わってきます。
ListColumns.Add
これはListColumnを返します。
ListColumn.DataBodyRangeこれは当該列のデータ部全行になります。
Sub VBA100_45_01()
Dim ws As Worksheet: Set ws = ActiveSheet
With ws.Range("B2").ListObject
With .ListColumns.Add(.ListColumns("列3").Index + 1)
.Name = "合計列1"
.DataBodyRange.Value = "=SUM([@[列1]:[列3]])"
End With
With .ListColumns.Add
.Name = "合計列2"
.DataBodyRange.Formula = "=SUM([@[列4]:[列5]])"
End With
End With
End Sub
Sub VBA100_45_02()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim col As Long
With ws.Range("B2").ListObject
col = .ListColumns("列3").Index + 1
.ListColumns.Add Position:=col
.ListColumns(col).Name = "合計列1"
.DataBodyRange.Columns(col).Value = "=SUM([@[列1]:[列3]])"
.ListColumns.Add
col = .ListColumns.Count
.ListColumns(col).Name = "合計列2"
.DataBodyRange.Cells(1, col).Value = "=SUM([@[列4]:[列5]])"
End With
End Sub
これはデータ部全体になります。
同じ数式なので全行に入れても良いし、先頭セルにだけ入れても結果は同じになります。
記事にはVBAコードと若干の説明を追加しました。
補足
[列4]このような構造化参照せずに、直接セル参照やR1C1形式での指定も可能ですが、
せっかくテーブルにしているので、構造化参照を使う方が良いでしょう。
テーブルの要素にアクセスするオブジェクトの辿り方は複数あります。
HeaderRowRange
DataBodyRange
ListColumns
ListColumn
ListRows
ListRow
Range
Cells
これらが入り組んでいますので、適宜確認しつつVBAを書くことになります。
以下は、いろいろな書き方での参照するセル範囲がどのようになるかの確認用のVBAです。
コメントに記載した出力結果は、問題文のテーブルの場合の結果です。
各オブジェクトの参照する範囲を把握すれば、VBAはいろいろな書き方が可能になります。
せっかくテーブルにしているので、構造化参照を使う方が良いでしょう。
HeaderRowRange
DataBodyRange
ListColumns
ListColumn
ListRows
ListRow
Range
Cells
これらが入り組んでいますので、適宜確認しつつVBAを書くことになります。
以下は、いろいろな書き方での参照するセル範囲がどのようになるかの確認用のVBAです。
Sub VBA100_45_sample()
Dim ws As Worksheet: Set ws = ActiveSheet
With ws.Range("B2").ListObject
Debug.Print .Range.Address '$B$2:$F$12
Debug.Print .HeaderRowRange.Address '$B$2:$F$2
Debug.Print .DataBodyRange.Address '$B$3:$F$12
Debug.Print .DataBodyRange.Columns(1).Address '$B$3:$B$12
Debug.Print .ListColumns(1).Range.Address '$B$2:$B$12
Debug.Print .ListColumns(1).DataBodyRange.Address '$B$3:$B$12
Debug.Print .ListRows(1).Range.Address '$B$3:$F$3
Debug.Print .DataBodyRange(1, 2).Address '$C$3
Debug.Print .DataBodyRange.Cells(1, 2).Address '$C$3
Debug.Print .DataBodyRange.Columns(2).Cells(1).Address '$C$3
Debug.Print .ListColumns(2).Range(2).Address '$C$3
Debug.Print .ListRows(1).Range(2).Address '$C$3
End With
End Sub
コメントに記載した出力結果は、問題文のテーブルの場合の結果です。
各オブジェクトの参照する範囲を把握すれば、VBAはいろいろな書き方が可能になります。
サイト内関連ページ
第130回．テーブル操作の概要（ListObject）
・ListObjects コレクション ・ListObject オブジェクト ・テーブル操作のVBAコード
第131回．テーブル操作のVBAコード（ListObject,DataBodyRange）・テーブル操作のVBAサンプル使用例 ・テーブルに設定 ・テーブルスタイル一覧 ・テーブルの存在確認 ・テーブルを範囲に変換 ・テーブルの範囲を再設定 ・テーブルのセルに値を入れる ・テーブルのセルの数式変更 ・テーブルの行・列のクリア ・テーブルの列の数式設定 ・テーブルの行挿入・削除 ・テーブルの列挿入・削除 ・テーブルのオートフィルター ・テーブルの並べ替え（ソート） ・テーブルの集計行挿入・削除・非表示 ・テーブルの右端に集計列追加 ・テーブルに新しい行列を含めない ・テーブルの使い方の基本 ・サイト内のテーブルに関するページ
第142回．テーブル全件処理とデータ最終行（ListObject,DataBodyRange）・テーブル全件処理 ・テーブルの特定列のデータ最終行まで ・テーブルが拡張されないパターン ・サイト内のテーブルに関する他のページ
同じテーマ「VBA100本ノック」の記事
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
43本目：CSV出力・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
44本目：全テーブル一覧作成・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
45本目：テーブルに列追加46本目：名前定義に使える文字
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
47本目：Window操作・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
48本目：配列と数値型・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
49本目：条件付き書式の判定・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
50本目：トリボナッチ数列・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
51本目：シート一覧と印刷ページ数・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
52本目：複数シートの一括印刷・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 45本目：テーブルに列追加
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。