エクセルを使いこなす！　複雑なセルデータから一部の情報のみを指定する方法

検索値に直接セルデータを指定できない場合

おさ氏：どうも、みなさんこんにちは。「おさとエクセル」のおさです。今日はVLOOKUP関数、第6段をやっていきます。すでにかなりの上級レベルまで来ているんですけど。

今日やることは、第1引数の検索値に直接的にIDを反映できない場合どうすればいいのかを見ていきます。具体例で見たほうが早いと思うので、早速ワークシートに移っていきましょう。

ちなみに、以前LEFT関数やMID関数、RIGHT関数といった文字列操作関数を勉強したんですが、今日はそれを使っていきます。まだ勉強していない方は、そちら見てから学習するようにしていきましょう。どうぞよろしくお願いします。

実際のワークシートで学んでいきましょう

こちらのシートをご覧ください。

VLOOKUPの第1引数の検索値にIDの一部だけを引用したいパターンを見ていきます。具体的には今から説明してきます。まず左側の表をご覧ください。

こちらピンクのところ、まったく同じ表が3つ描かれています。それぞれの場合で、LEFT関数、MID関数、RIGHT関数と異なる関数を使うので、3つ同じ物を用意しています。

具体的にやることは、給料、今はここ空欄になっているのですが、このIDを持つ人の給料がいくらになるのかをVLOOKUP関数で、こちらの表（右側の表）から引用していきたいんですね。

「HR、ヒューマンリソースに属する○○くんは、基本給は15万ですよ」ということを、こちら（左表）に反映させたい。

ただ一方で、IDが3つ余計な物が組み合わさったものでできているので、これをシンプルな検索値に変えた後に、右側の青いところから右側の数値を引っ張ってくるようなことをやっていきます。

FIND関数を使っていきましょう

早速、入っていきますね。今回の肝になるのが、今こちらの組み合わさっているなかのハイフンの位置です。

ハイフンの位置をどうやって特定するのかを以前学習してきました。FIND関数というものを1回やりましたね。FIND関数のほかにも、SERACH関数を用いてやってもいいのですが、せっかくFIND関数をやったので、FIND関数を使いながらハイフンの位置を特定していきましょう。

まず、全体像を説明する前に、１回やってみましょう。FIND関数で「ハイフンという検索文字列をこちらから探してきてね」と指定すると、3という値が返ってきましたね。

今、これは通貨表示になっていますが、元々設定でいじっているので、気にしないでください。

3という値です。FIND関数で3という値が返ってきたのですが、今私たちが引用したいのはHRという2文字だけなんですね。

ということは、これを抽出していきます。LEFT関数を用いて。

LEFT関数の指定の方法をご存じでしょうか。覚えていますか？　

文字列、文字数というのですね。

まず、この文字列というところのセル番地を指定して、コンマ。そして文字列を指定するんですけど、先ほど3という値になっていましたね。3という値はダメです。

HRが２文字ですので、そこからマイナス１文字してあげて、括弧で閉じてあげるといい感じで返ってきますね。

HRという2文字が返ってきました。

ここまで大丈夫ですか？　コピーするとこんな感じでいい感じに反映されています。

抽出したデータをVLOOKUPで指定

これで複雑だったIDが単純に２文字だけ抽出してくることができました。ここにVLOOKUP関数で指定してあげることになります。

今VLOOKUP関数の第1引数、検索値はLEFT関数以下のこの部分ですでに説明しきっています。

ですので、範囲指定をしていきましょう。範囲は右側の表のこちらですね。ここで絶対参照にまずすると。

列番号としては、給料を引用したいので、2列目ですよと。

検索方法は、完全一致のFALSE、0ですね。

これを指定して、括弧でエンターを押すと、15万円が返ってきました。大丈夫ですか？　

今VLOOKUP関数の第1引数にLEFT関数でここですね。

中身見てみてると、HRという文字列を指定しているのがわかります。

HRをこちらの範囲、「F7からG9までの中から探してきてね」と指定した後に、給料は2列目、かつ完全一致の方法ですので検索方法としては0を指定して、エンターを押したら、15万円という値が返ってきたというわけです。

こんな感じでやっていきますね。

MID関数の場合

さくさくとMID関数もやっていきましょう。MID関数のもまったく同じ手順を踏んでいきます。「ハイフンをこちらのほうから見つけてきてね」と。

そうすると、3という値が返ってきましたね。これ全部3という値が返ってきます。

MID関数を用いて、真ん中にある1980というのを引っ張ってきますね。MID関数もまったく同じです。

文字列としては、こちらを指定して、開始位置は今FIND関数でハイフンまでの位置、ハイフンまでの文字数をカウントしてくれているので、更にプラス１することで、４文字目と指定することができるんですね。

ですので、ここでプラス1をしてあげましょう。プラス1ですね。

これで4という数字が書かれています。オッケーですね。

開始位置を4というふうに指定できたので、開始位置から何個目まで文字数カウントするんですかというので、今これは、1980、1990、2000と全部で4文字分あるので、ここに4という数字を指定して、エンターを押すと、1980といい感じで返ってきましたね。

これをコピペすると、全部、1990、2000としっかり反映されています。

同様にVLOOKUPで指定しましょう

早速、VLOOKUP関数で、ここもまた同じようにやっていきますね。VLOOKUP関数の第1引数は、すでに検索値、MID関数以下で表現できています。1980という検索値を指定できています。

ですので、コンマを打って第2引数を反映して、こちらの範囲を指定して絶対参照。2列目の0で、先ほどと同様です。

エラーになる理由は？

ここでエンターを押すと、なんででしょう？　エラーが返ってきてしまいました。

これ、我々が指定してきたMID関数の1980という値と右側に描かれている1980という値は、実はエクセルの中では別物と解釈されてしまっています。

具体的にどういうことかと言うと、このMID関数で我々が指定してきた値って、実は、文字列としての1980という値だったんです。

ダブルコーテーションで囲まれているものはすべて、エクセル内部では文字列として扱われます。

一方で、このIDのほうは数値としての1980ですので、文字列と数値は別物と捉えられてしまいます。ですので、こちらの文字列としての1980を数値としての1980に変えたいと思います。

裏技としては、プラス0をしてあげてください。

これでエンターを押してあげると、いい感じで返ってきましたね。

1980のIDの右側は給料20万円ですね。これでしっかり返ってきます。これでコピペしてあげると、19万、18万と返ってきました。

ここはMID関数の注意点ですね。

RIGHT関数の場合

RIGHT関数を最後にやっておしまいにしましょう。RIGHT関数の使い方をシンプルに覚えておきましょう。これ3文字指定なので、こんな感じでサクサクいけると思います。

AAA、BBB、CCCと返ってきました。

ここに対して、VLOOKUPというふうに指定して第1引数を検索してオッケーですので、範囲指定をして、2列目の0と指定すると30万、いい感じで返ってきましたね。

コピペで、25万、20万と返ってきました。いい感じですね。

どうもみなさんお疲れ様でした。

ID、製品型番などを扱う時に便利です

今日は、文字列操作関数のLEFT関数、MID関数、RIGHT関数、あるいはFIND関数を用いて、VLOOKUP関数の第1引数が複雑な時に、どう対応したらいいのかを見ていきました。

IDとか製品型番を複数条件で指定している会社が多いので、この方法を知っておくと、わりとVLOOKUP関数の発展版として対応できるようになってくると思います。ちょっと複雑だったので、もう1回くらい見直してください。どうもありがとうございました。