【VBA】超簡単！テーブルから直感的に値を取得する関数（番号を使わない）

概要

テーブルから値を取得する関数を公開します。
横軸方向への検索→列名を指定（列番号特定）
縦軸方向への検索→特定の列の特定の値を指定（行番号特定）
上記で指定された列と行から値を取得する
コピペですぐ使えます。
操作は簡単で直感的です。

こんにちは、hokkyokunです。

テーブルから特定の値を取得する関数を作りました。
めちゃくちゃ簡単に、しかも直感的に値を取得できます。

Contents

デフォルトのテーブルから値取得方法は使いづらい
値を取得するとき、我々はどうしているか？
Pythonは値取得が超優秀
関数
使い方
まとめ

デフォルトのテーブルから値取得方法は使いづらい

「テーブルを使って値を取得する方法」を紹介してるブログはたくさんあります。

例えば下図のような表に対し、
「小西さんの国語の点数を取得したい」とき
以下のようなコードを紹介している人が多いです。

Sub sample()
Dim table As ListObject
Set table = ThisWorkbook.Worksheets("Table2").ListObjects(1)

Debug.Print (table.ListColumns("国語").DataBodyRange(1).Value)
''>>80

Debug.Print (table.ListRows(1).range(2).Value)
''>>80

Dim rng As range
Set rng = range(table.Name & "[国語]")
Debug.Print (rng(1).Value)
''>>80

End Sub

…すみません、辛めのこと言って申し訳ないですが、
これで「よし、テーブル最高！めっちゃ使いやすい！」ってなります？
めちゃくちゃ取得しづらいですよね…

１００歩譲ってましなのは
table.ListColumns(“国語”).DataBodyRange(1).Valueですが

まだ、「国語」って表記しているから、指定がしやすいですが、
何行目かなんて直感的にわからんし、テーブルに値を追加したら位置変わるから
それを気にしてプログラムしなくちゃいけないし…

申し訳ないですが、こんなの使い物にならないです。
なので関数を作りました。

値を取得するとき、我々はどうしているか？

突然ですが、我々人間が値を取得するときってどうしていますか？

おそらく、ほとんどの人は直感的にやっていると思いますが、

横軸方向の検索は、列名でカテゴリー（ここでは国語）を探し、
縦軸方向の検索は、起点となる列（ここでは名前）から特定の行を抽出して、
縦と横がクロスした値を参照していますよね。

コーディングもこの作業をプログラムすると思いますが、
VBAのテーブル操作はなぜか番号指定がデフォルトでこれをなんとか
文字列や値などで検索できるように工夫して使うことを強いられています。

Pythonは値取得が超優秀

偉そうなこと言ってますが、
私も「こんなもんか」って思い、そこまで不満なく使ってました。

が、Pythonを使ってから、値取得の便利さに感動したので紹介します。
細かい文法は分からなくていいので、その直感性を認識してもらえたらと思います。

df = df.set_index("名前", Drop = True)
df.loc ["小西","国語"]

以上です、
テーブルとdfがイコールだと思ってください
超直感的に扱えますね！！

正確に言うと、Pythonを土台としたPandasライブラリが優秀で
上記のような簡単なコードで値が取得できます。

これをVBAで可能な限り簡潔に表現します

関数

関数名
table_Loc( table , index_Col , index_Val , col_Name )
引数
①table : 対象のテーブル（データ型はListObject)
②index_Col : 縦軸方向で検索する特定の列名（データ型はString)
③index_Val : ②の列での検索したい値（データ型はVariant)
④col_Name : 横軸方向で検索する特定の列名（データ型はString）
戻り値
取得した値

Function table_Loc(ByVal table As ListObject, ByVal index_Col As String, ByVal index_Val As Variant, ByVal col_Name As String)
    Dim i As Long
    
    For i = 1 To table.ListColumns(index_Col).DataBodyRange.Count
        If table.ListColumns(index_Col).DataBodyRange(i).Value = index_Val Then
            Exit For
        End If
    Next
    
    table_Loc = table.ListColumns(col_Name).DataBodyRange(i).Value

End Function

使い方

同様の図を使用します

同様に「小西さんの国語の点数」を取得してみたいと思います。

Sub test_loc()

Dim table As ListObject
Dim settings As Variant

Set table = ThisWorkbook.Worksheets("Table2").ListObjects(1)

Debug.Print (table_Loc(table, "名前", "小西", "国語"))
'＞＞80
End Sub

値「80」を取得できます。

まとめ

正直クラスモジュールを作るか迷いましたが、
使用するまでの準備が大変だし、本家のtableと私が作ったクラスモジュールを併用して使うと混乱すると思い、関数としました。

少し引数が多くなってしまい申し訳ございません。

関数名
table_Loc( table , index_Col , index_Val , col_Name )
引数
①table : 対象のテーブル
②index_Col : 縦軸方向で検索する特定の列名
③index_Val : ②の列での検索したい値
④col_Name : 横軸方向で検索する特定の列名
戻り値
取得した値