VBA100本ノック 53本目：テーブルの扱いと年齢計算

公開日：2020-12-23　最終更新日：2021-02-22

VBA100本ノック 53本目：テーブルの扱いと年齢計算

テーブルにある一定条件の人だけ年齢計算する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

出題ツイートへのリンク

#VBA100本ノック 53本目
画像のようにA1から始まるテーブルがあります。
「東京都」の「男」で「35歳以上」の人のみ、備考欄に「対象」と入れてください。
年齢はマクロ実行年（つまり今年）の12月31日で計算。
※シートは任意

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_53.xlsm
https://excel-ubara.com/vba100sample/VBA100_53.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

テーブル(ListObject)の操作方法はいろいろあり悩んでしまいます。
条件で絞り込む処理なのでまずはオートフィルタでやってみます。
Worksheeetのオートフィルタと違う部分もあるので若干注意が必要です。
他の条件で絞り込まれている場合を考慮して一旦解除してから行っています。


  
    
      Sub VBA100_53_01()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　

　　Dim tbl As ListObject

　　Set tbl = ws.Range("A1").ListObject

　　tbl.DataBodyRange.Columns(getCol(tbl, "備考")).ClearContents

　　tbl.ShowAutoFilter = False

　　

　　With tbl.DataBodyRange

　　　　.Columns(getCol(tbl, "備考")).ClearContents

　　　　.AutoFilter Field:=getCol(tbl, "都道府県"), Criteria1:="東京都"

　　　　.AutoFilter Field:=getCol(tbl, "性別"), Criteria1:="男"

      　　　　.AutoFilter Field:=getCol(tbl, "誕生日"), Criteria1:="<"
      & DateSerial(Year(Date) - 35, 12, 31)

　　　　On Error Resume Next

　　　　.Columns(getCol(tbl, "備考")).SpecialCells(xlCellTypeVisible) = "対象"

　　End With

　　

　　ws.ShowAllData

End Sub


Function getCol(ByVal aTbl As ListObject, aColName As String) As Long

　　getCol = aTbl.ListColumns(aColName).Index

End Function

Forループで処理する場合は、
DataBodyRange
ListRows
ListColumns
このあたりの使い方でいろいろと書き分けられます。
記事補足に3通りのVBAを掲載しました。

補足

シートでの年齢計算はDATEDIF関数を使うのが定番だと思いますが、
VBAのDateDiff関数はシートのDATEDIF関数とは全く違うものとなっていますので注意してください。

DateDiff関数

DateDiff関数は、2つの指定した日付の時間間隔を表す値を返します。ワークシート関数のDATEDIF関数とはスペルも引数も違います、さらに、年の計算は違ったものとなっていますので注意してください。DateDiff関数 DateDiff(interval,date1,date2[,firstdayofweek[,

以下は、Forループで処理する場合の、3通りに書き分けたテーブル処理のVBAサンプルになります。

Select Case True
これを使っているのは好みの問題なので、あまり気にしないでください。
Caseの条件は、カンマでOrですが、ショートサーキットになるので、条件を満たした時点で次の条件は判定せずにCaseブロックに入ります。
つまり、<> "東京都"これを満たした場合は、性別以降は判定されません。
したがって、年齢計算は東京都の男性だけしか計算していません。

DataBodyRange.Rows


  
    
      Sub VBA100_53_02()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　

　　Dim tbl As ListObject

　　Set tbl = ws.Range("A1").ListObject

　　tbl.DataBodyRange.Columns(getCol(tbl, "備考")).ClearContents

　　

　　Dim i As Long

　　For i = 1 To tbl.DataBodyRange.Count

　　　　With tbl.DataBodyRange.Rows(i)

      　　　　　　Select Case True

      　　　　　　　　Case .Columns(getCol(tbl, "都道府県")) <> "東京都",
      _

　　　　　　　　　　 .Columns(getCol(tbl, "性別")) <> "男", _

　　　　　　　　　　 Year(Date) - Year(.Columns(getCol(tbl, "誕生日"))) < 35

　　　　　　　　Case Else

　　　　　　　　　　.Columns(getCol(tbl, "備考")) = "対象"

　　　　　　End Select

　　　　End With

　　Next

      End Sub


Function getCol(ByVal aTbl As ListObject, aColName As String) As Long

　　getCol = aTbl.ListColumns(aColName).Index

End Function

ListRows


  
    
      Sub VBA100_53_03()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　

　　Dim tbl As ListObject

　　Set tbl = ws.Range("A1").ListObject

　　tbl.DataBodyRange.Columns(getCol(tbl, "備考")).ClearContents

　　

　　Dim tRow As ListRow

　　For Each tRow In tbl.ListRows

　　　　Select Case True

　　　　　　Case tRow.Range(getCol(tbl, "都道府県")) <> "東京都", _

　　　　　　　　 tRow.Range(getCol(tbl, "性別")) <> "男", _

　　　　　　　　 Year(Date) - Year(tRow.Range(getCol(tbl, "誕生日"))) < 35

　　　　　　Case Else

　　　　　　　　tRow.Range(getCol(tbl, "備考")) = "対象"

　　　　End Select

　　Next

      End Sub


Function getCol(ByVal aTbl As ListObject, aColName As String) As Long

　　getCol = aTbl.ListColumns(aColName).Index

End Function

ListColumns


  
    
      Sub VBA100_53_04()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　

　　Dim tbl As ListObject

　　Set tbl = ws.Range("A1").ListObject

　　Intersect(tbl.DataBodyRange, tbl.ListColumns("備考").Range()).ClearContents

　　

　　Dim i As Long

　　For i = 2 To tbl.Range.Rows.Count

　　　　Select Case True

　　　　　　Case tbl.ListColumns("都道府県").Range(i) <> "東京都", _

　　　　　　　　 tbl.ListColumns("性別").Range(i) <> "男", _

　　　　　　　　 Year(Date) - Year(tbl.ListColumns("誕生日").Range(i)) < 35

　　　　　　Case Else

　　　　　　　　tbl.ListColumns("備考").Range(i) = "対象"

　　　　End Select

　　Next

End Sub

サイト内関連ページ

第130回．テーブル操作の概要（ListObject）｜VBA入門

・ListObjects コレクション・ListObject オブジェクト・テーブル操作のVBAコード

第131回．テーブル操作のVBAコード（ListObject,DataBodyRange）｜VBA入門

・テーブル操作のVBAサンプル使用例・テーブルに設定・テーブルスタイル一覧・テーブルの存在確認・テーブルを範囲に変換・テーブルの範囲を再設定・テーブルのセルに値を入れる・テーブルのセルの数式変更・テーブルの行・列のクリア・テーブルの列の数式設定・テーブルの行挿入・削除・テーブルの列挿入・削除・テーブルのオートフィルター・テーブルの並べ替え（ソート）・テーブルの集計行挿入・削除・非表示・テーブルの右端に集計列追加・テーブルに新しい行列を含めない・テーブルの使い方の基本・サイト内のテーブルに関するページ

第142回．テーブル全件処理とデータ最終行（ListObject,DataBodyRange）｜VBA入門

・テーブル全件処理・テーブルの特定列のデータ最終行まで・テーブルが拡張されないパターン・サイト内のテーブルに関する他のページ

DATEDIF関数（年齢・勤続年数）｜エセル入門

・DATEDIF関数の書式・DATEDIF関数の使用例・DATEDIF関数の注意点

DateDiff関数｜VBA関数

同じテーマ「VBA100本ノック」の記事

50本目：トリボナッチ数列
 51本目：シート一覧と印刷ページ数
 52本目：複数シートの一括印刷
53本目：テーブルの扱いと年齢計算
54本目：シートのChangeイベント
 55本目：他ブックのマクロを起動
 56本目：数式内の自身のシート名を消す
 57本目：ファイルの更新日時
 58本目：番号リストを簡潔にした文字列で返す
 59本目：12ヶ月分のシートを四半期で分割
 60本目：「株式会社」の表記ゆれ置換

アクセスランキング・・・ランキング一覧を見る

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本ノック
53本目：テーブルの扱いと年齢計算

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 53本目：テーブルの扱いと年齢計算