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

最終更新日：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

頂いた回答

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

解説

テーブル(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入門

テーブルのVBAでの操作で使用するオブジェクトの概要の説明です、テーブルは、セルの範囲を表（テーブル）に変換することで、関連するデータの管理と分析を容易にできるエクセルの機能で、以前はリストと呼ばれていました。テーブルを作成して書式設定することで、データを視覚的にグループ化して分析しやすくできます。

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

テーブル操作の具体的なVBAコードをパターン別に掲載します、テーブルの全体的な構成は・テーブル全体・見出し行・データ範囲・集計列・集計行 VBAでは、これらのオブジェクトを使い各要素にアクセスします。テーブル全体のオブジェクトがListObjectで、シート内に複数テーブルが作成可能なので、シート内のListObjectのコレクションがListObj…

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

テーブルの全件処理と実際にデータが入っている最終行の取得について具体的なVBAを掲載します。注意点として、最新の365でテーブルが拡張されないパターンについても説明しておきます。テーブルの全体的な構成は・テーブル全体・見出し行・データ範囲・集計列・集計行 VBAでは、これらのオブジェクトを使い各要素にアクセスします。

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

2つの日付の間の日数、月数、または年数を計算します。この関数は、Lotus1-2-3との互換性を提供するために用意されています。この関数は、関数ウイザード（数式パレット）が用意されていませんので、全て手入力する必要があります。

DateDiff関数｜VBA関数

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

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

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.Excelショートカットキー一覧｜Excelリファレンス
6.繰り返し処理（For Next)｜VBA入門
7.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
8.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
9.セルに文字を入れるとは（Range,Value）｜VBA入門
10.とにかく書いてみよう（Sub,End Sub）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
53本目：テーブルの扱いと年齢計算

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

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

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