Shimesaba-ba blog

大手日系企業で経営管理や経理業務を行い、英語や海外とは無縁な状態から海外で業務を行い、グローバル企業へ転職。英語、経営管理、ビジネスについての経験や学んだことをアウトプットします。

VLOOKUP関数の実務的な使い方2、文字列の一部だけを抽出した検索の仕方

前回はVLOOKUP関数の

実務的な使い方の基本と

よくあるエラーの原因と対策について

書きました。

www.shimesaba-ba.com

 

今回は色んな検索の仕方に

ついて書きたいと思います。

 

というのもVLOOKUP関数で検索する時に、

データベースと検索するキーの

粒度が違うことないでしょうか? 

 

そうするとそのままVLOOKP関数を使っても

検索ができないことが

実務でよくあると思います。

 

今回はこういった検索する

キーとデータベースの粒度の違いを

乗り越えて検索をする方法をお伝えします。

 

f:id:Shimesaba-ba:20200920145312p:plain

過去の私がそうでしたが、

このような実務で発生しやすい問題と

その対処法がわからないから

有名な関数とはいえ実務でVLOOKUPを

なかなか使えない人が多いのだと思います。

 

エクセル学習のオススメは解説を読み込むより

実際のファイルを見て自分で再現できるか

やってみることだと考えていますので、

そのファイルを無料で置いておきます。

drive.google.com


 

 

左から2文字だけ抽出したい

 既存のデータベースのデータに

余計な情報が含まれており、

あなたが検索したい検索キーが見つからないことないでしょうか?

 

下記の例では

発注オーダー番号に事業部コードと

勘定コードの

組み合わせでできているデータベースです。

 

それぞれの事業部の発注金額を

調べているとします。

素直にVLOOKUPを使っても

検索エラーになってしまいます。

f:id:Shimesaba-ba:20201004164159p:plain

そこでデータベースの

発注オーダー番号の中から

事業部コード(頭2文字)だけを

抜き出します。

そこで使われるのがLEFT関数です。

f:id:Shimesaba-ba:20201004164856p:plain

LEFT関数

とてもシンプルです。

LEFT(H4,2)というのは、

セルH4から左2文字を取ってきて

ということだけです。

f:id:Shimesaba-ba:20201004164655p:plain


これで事業部コードが取り出せたのであとは

いつも通りにVLOOKUP関数を作れば

検索結果が返ってきました。

f:id:Shimesaba-ba:20201004165104p:plain

 

 

同じ発想で例えば右から〇文字を取って

それで検索するといったこともRIGHT関数を

使って同様にできます。

 

特定の文字の後から4文字を抽出したい

例えば発注オーダー番号の中身を見ると

事業部コードと勘定コードの組み合わせで

できています。

 

その時に勘定コードを検索キーにして検索

する場合どうすればよいでしょうか?

データベースから勘定コードを

抜き出したいですよね?

 

f:id:Shimesaba-ba:20201004170034p:plain

この対処法として

MID関数とFIND関数の組み合わせでできます。

少し難しい式があるので解説します。

f:id:Shimesaba-ba:20201004170355p:plain

 

MID関数とFIND関数の組合わせ

それぞれの関数について触れてから、

最後に組み合わせをしていきます。

 

MID関数

あるセルについて

左から何番目から何文字取るかを

指定する関数です。

f:id:Shimesaba-ba:20201004172822p:plain

上記の通り目視でやればMID関数だけで

勘定コードは抜き出せます。

 

しかし下記のように

常に4番目から取るとは限らないことは

実務でよくあります。

頭にあるコード数がマチマチといったことありませんか?

f:id:Shimesaba-ba:20201004173226p:plain


だからこの例では

ハイフンの次の文字から取り始める

工夫をしないといけません。

そこで使われるのがFIND関数です。

 

FIND関数

特定の文字列があるセルの中で

左から何番目かを教えてくれます。

この例ですとハイフンは左から

それぞれ3番目と4番目とわかります。

尚、文字列を関数に入れるときは必ず""で囲みましょう。

f:id:Shimesaba-ba:20201004173551p:plain

 

ここで最後の工夫が必要です。

それぞれ1を加えてあげて下さい。

なぜかわかるでしょうか?

 

今やりたいことは

MID関数が左から何文字目から

取り始めるかを可変にすることです。

  

このままだとハイフンから文字を

取り始めてしまいます。

だから最後に1を加えて下さい。

 

MID関数とFIND関数の組合わせ

こちらMID関数とFIND関数

のまとめになります。

f:id:Shimesaba-ba:20201004171728p:plain

あとは通常通りにVLOOKUP関数を使えば

発注金額を無事に拾うことができるようになりました。

f:id:Shimesaba-ba:20201004180134p:plain

まとめ

VLOOKUP関数は初めから

素直に使える場面は限られています。

 

データベースと検索したい

キーの粒度が違うといった

実務的な問題が発生します。

 

そういった時、

例えばご紹介したLEFT関数、

FIND関数やMID関数といった

文字列操作関数を使って

粒度をあわせることができます。

 

VLOOKUP関数が使えるには、

式の作り方を覚えるだけでなく、

こういった実務でよく起こる問題と

その解決法をセットで知っておくことで

知っている知識から使える知識になります。