縦から横か、それとも横から縦か(テーブルの値取得)。
縦から横か、それとも横から縦か。
先日の社内勉強会で、テーブル(表)内の特定セルの値を取り出す操作があった。その時のお話から抜粋。例えば、このようなテーブルがあるとする。
この中で、みかんの単価が知りたいとき、どうすればよいか。このとき、縦から横か、それとも横から縦かという話になった。
まず、縦から横、つまり列方向でみかんを探してから、当該行の単価を取得する場合を考える。
- 品名列を「みかん」で検索。
- 該当セルの単価を取得。
コードにすると、こんな感じだ。今回は行番号が欲しいので、
FindではなくMatchを用いている。
Sub 縦から横() Dim Tb As ListObject Set Tb = ActiveSheet.ListObjects(1) Dim MatchResult As Long MatchResult = WorksheetFunction.Match("みかん", Tb.ListColumns("品名").DataBodyRange) Dim UnitPrice As Double UnitPrice = Tb.ListRows(MatchResult).Range.Cells(Tb.ListColumns("単価").Index) Debug.Print UnitPrice End Sub
一方で、横から縦、つまりまず単価の列を指定して、その中でみかんの行を指定する場合を考える。
- 単価の列をデータ範囲として取得。
- その中の、みかんの行の値を取得。
コードにすると、こんな感じだ。先ほどより、ちょっとだけ短い。
Sub 横から縦() Dim Tb As ListObject Set Tb = ActiveSheet.ListObjects(1) Dim MatchResult As Long MatchResult = WorksheetFunction.Match("みかん", Tb.ListColumns("品名").DataBodyRange) ' 先ほどと違うところ。 Dim UnitPrice As Double UnitPrice = Tb.ListColumns("単価").DataBodyRange(MatchResult) Debug.Print UnitPrice End Sub
幸い今回は検索文字が表の一番左にあるため、Vlookupを使うという方法もある。
Sub VLookupで() Dim Tb As ListObject Set Tb = ActiveSheet.ListObjects(1) Dim UnitPrice As Double UnitPrice = WorksheetFunction.VLookup("みかん", Tb.DataBodyRange, Tb.ListColumns("単価").Index, False) Debug.Print UnitPrice End Sub
辞書を使う方法も、あるだろう。ひととおりデータを取得してから、該当するものを取り出す方法だ。今回は豪勢に、Enumまで使ってしまおう。
Enum 列名 品名 = 1 数量 単価 合計 End Enum Sub 辞書() Dim Tb As ListObject Set Tb = ActiveSheet.ListObjects(1) Dim Dict As Object Set Dict = CreateObject("Scripting.Dictionary") Dim ListRow As Excel.ListRow For Each ListRow In Tb.ListRows With ListRow.Range Dict(.Cells(列名.品名).Value) = .Cells(列名.単価).Value End With Next Debug.Print Dict("みかん") End Sub
上記はいずれの場合も、誰かが「単価」を「@価格」などの編集した瞬間、全て無効になってしまうのが悲しいところ。
ということで、どの方式で取得するかは皆さんの、時と場合とお好みで。
参考まで。