ヒガサラblog

サラリーマン向けに、仕事を効率良く進めていくための方法についてご紹介しています。プログラミングから対人スキルまでを幅広く掲載中。

ヒガサラblog

【エクセル】VLOOKUPの検索列指定を文字列に!VBAでの利用まで!

f:id:yshgs_elec:20210214225121j:plain

エクセルのVLOOKUP関数を使っているときに、

「左から数えた列番号指定ではなく、指定した文字と一致する列を探索したい。」

このような状況に出くわすことは多いのではないでしょうか。

 

この記事では、そんな状況を一発で解決できる方法をご紹介していきます。

具体的には通常のVLOOKUP関数にMATCH関数というものを組み合わせることで、列番号指定ではなく、文字列指定で探索列を決定することが可能になります。

 

実務で使える便利なスキルですので、ぜひ使い方をマスターしておきましょう。

 

それでは早速やっていきます!

 

スポンサーリンク

 

紹介するスキルの概要説明

 まずはじめに冒頭で説明した状況をもう一度整理してみましょう。

※VLOOKUPの基本的な使い方は理解されていることを想定して説明していきます。

 

VLOOKUP関数で指定列のデータを探索には、その列が検索範囲の左から何番目の列であるかを教えてあげる必要があります。

(以下の3つめの引数にあたるところですね。)

f:id:yshgs_elec:20210214221009j:plain

 

しかし、実際にVLOOKUPを使っていると以下のような状況に出くわすことはないでしょうか?

「左から何行目かはわからないけど、ラベル(系列名)はわかってる。」

 

こんなとき、わざわざそのラベルが左から何番目かを数えたあとにVLOOKUP関数を使うなんて面倒ですよね。

列がたくさんあるとなおさらです。

 

というわけでこの記事では、列指定を文字列に置き換えてVLOOKUPを実行する方法をご紹介していきます。

今回は以下の画像のように

◆B4セルにVLOOKUPを適用

◆探索する行はB2セルで指定

◆探索する列(文字)はB3セルで指定

◆探索する領域はD1:K12

という状況でVLOOKUPを使ってみましょう。

f:id:yshgs_elec:20210214220921j:plain

 

スポンサーリンク

 

MATCH関数の使い方

先ほど紹介した事例のやり方を紹介する前に、その中で必要なMATCH関数を紹介しておきます。

 

MATCH関数とは、指定した文字がある領域内の何番目に存在しているかを出力してくれる関数です。

 

例を見てみましょう。

以下の画像はM1セルにこのMATCH関数を適用したものです。

この例では、data4という文字列がD1:K1という領域の何番目に配置されているかを探してきています。

f:id:yshgs_elec:20210214222342j:plain

data4という文字列はH列にあるので、指定した端っこであるD列から数えて5番目ですので、MATCH関数の出力結果は5になっています。

(数える際は左から、もしくは上から数えます。)

 

つまり、このMATCH関数を使えば、VLOOKUP関数で探したいラベルがあるのは何列目かをわざわざ数える必要がなくなるというわけです。

 

MATCH関数の使い方がわかったところで、実際にVLOOKUPと組み合わせてみましょう。

スポンサーリンク

 

VLOOKUPの検索列指定を文字列にする方法

さっそくですが、VLOOKUP関数に先ほど紹介したMATCH関数を組み合わせてみましょう。

 

使い方はこんな感じ。

f:id:yshgs_elec:20210214223219j:plain

 f:id:yshgs_elec:20210214221009j:plain

(それぞれの引数が何を表しているかも一緒に掲載しておきます。)

3つ目の引数である、列番号を指定するところにMATCH関数が使われていますね。

 

で、 このMATCH関数の引数で使われているB3セルにはなにが入っていたかというと、以下の画像のようにdata4という文字が入っています。

f:id:yshgs_elec:20210214220921j:plain

 

つまりこの数式では、

①B2セルに記入された数値と一致する行をD列から探し出す

②B4セルに記入された文字と一致する列番号を1行目から探し出す

③指定した領域から①②の情報をもとにデータを取り出す。

 

ということをやっているわけです。

 

結果はしっかりとtime=0.5, ラベルがdata4の場所にある125という数値が出力されていることがわかります。

 

というわけで指定文字列が何番目にあるかを教えてくれるMATCH関数を組み合わせることで、VLOOKUP関数の列探索を文字(ラベル名)に置き換えることができることが確認できましたね。

 

VBAでの実際の使用例

 最後に先ほどの作業をVBA上でやるにはどうすれば良いかをご紹介しておきます。

(題材は全く同じです。)

 

◆VBAでMATCH関数とVLOOKUP関数を組み合わせる方法

Sub vlookup_kansu()
Dim search_time, search_data As Single
Dim search_word As String
search_time = Cells(2, 2)
search_word = Cells(3, 2)
search_data = WorksheetFunction.VLookup(search_time, Range("D1:K12"), WorksheetFunction.Match(search_word, Range("D1:K1")),True)
Cells(4, 2) = search_data
End Sub

 

 基本的にはsearch?data=・・・という部分を見ていただければOKです。

 まぁエクセルシート上でやっていることをゴリゴリ記述しているだけですね。

 

スポンサーリンク

 

おわりに

というわけで今回はVLOOKUPの検索列指定を文字列置き換える方法についてご紹介しました。

 

知っているだけで実務で非常に役立つはずですので、ぜひ覚えておきましょう!

 

このように私のブログではエクセルスキルはもちろん、様々なプログラミングスキルを紹介しています。

・日々の残業時間を減らしたい。

・業務をもっと効率的にこなしたい。

・もっと勉強する時間を作りたい。

こんな思いを持っている人は、ぜひ他の記事も見てみてくださいね。

 

この記事が役に立ったという方は、ぜひ応援よろしくお願いします。

 ↓ 応援ボタン

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

 

それではまた!