エクセルでよく使う必須の関数を短時間でマスター！
これだけ覚えておけば良い13個の関数を厳選しているので、すぐに仕事の早い人になれます！
RPA開発歴4年 / MICHIRU RPAエキスパート認定 / 業務改善アドバイザー
RPA導入代行サービスCoreBeeを運営しており、RPA開発に取り組む他、業務改善アドバイザーとしてあらゆる業種の業務改善に携わっている。
社内のエクセルマスターとしての地位を確立しており、今回その知識を最大限生かし執筆しています。
VLOOKUP関数とは？
VLOOKUP関数は、検索条件を指定して指定範囲の中から検索ができる関数です。
一般的なページ内検索と異なり大量のデータがある表から該当のものだけを抽出して検索することができるため、見やすく間違いのない検索ができます。
具体的な使い方として、膨大な社員データが入力されている表の中から【社員ID】【雇用形態】【所属課】などの検索条件に一致する社員を瞬時に見つけるなどができます。
では早速、社員データの中から検索できる機能を例に、VLOOKUP関数の使い方を解説していきます。
VLOOKUP関数の使い方
VLOOKUP関数を利用するには、まず以下のように分割して考えるとわかりやすいでしょう。
- 検索対象となるデータが入った表
- 検索結果を表示するフィールド
では、実際にVLOOKUP関数を利用するための表を作成してみます。
例として、以下のような表があるとします。
この表はある会社の社員一覧を記載した表です。
この会社の人事担当者が、社員からの問い合わせに対してすぐに応答できるように関数を組んでみましょう。
表とは別に、関数を記入して検索結果を表示させるためのフィールドを用意します。
ここに関数を入れていきます。
例として、このケースでは社員から【社員ID】を聴取すれば該当社員のデータがすぐに見つかるように関数を組み立てます。
まず、「社員名」を表示したいB3のセルに上の関数を入力します。
記載する式は【=VLOOKUP(A3,A6:F21, 2, FALSE )】となりました。
この式について細かく解説します。
【=VLOOKUP】のあとにある【A3】は、【検索値】と呼ばれます。
何を検索条件として検索するか、という意味です。
今回は【社員ID】を検索条件とするため、A3に記入される社員IDを検索条件として表を検索するという意味となります。
次に、【A6:F21】の部分は【範囲】です。
表のどの部分から検索をするかという意味になります。
今回は、表全体から検索してくるため【A6:F21】という表全体をカバーできる値で入力しています。
次に【2】という項目ですが、これは表の何列目の値を返すかという意味になります。
この【B2】セルは【社員名】を表示させたいセルであるため、表の左から2列目ということで【2】を入力しています。
最後にある【FALSE】については、【検索方法】と呼ばれる項目です。
基本的にこの検索方法についてはいったん、【FALSE】を使うものと覚えておきましょう。
この式を確定すると、以下のような表示となります。
エラーが出ていますが、これはA3セルに検索条件が何も入っていないためですので問題ありません。
では実際に、【A3】セルに【社員ID】を入力してみます。
社員ID【E1003】を入力すると、B3セルに【白河 涼子】さんの名前が表示されたことがわかります。
これで基本的な関数は完成しました。
では続いて、CからF列の【ふりがな】や【所属】なども同様の手順で埋めていきます。
式はほとんど変える必要はありませんが、式の2つ目のカンマの後にある【2】を【3】に変更します。
これは、【ふりがな】の項目が表の3列目にあるため修正が必要となるのです。
同様の手順で【所属】の場合は【4】、【勤続年数】は【5】…と当てはめて関数を埋めていきます。
すべての関数が入力されたら、A3の社員IDを別のIDで検索してみましょう。
上記のように、正しく関数が設定されていればIDを変更するだけで該当の複数の情報を一気に表示できるようになります。
【反映・表示されない！】#REF! / #N/A! エラーが出た場合の対処
VLOOKUP関数で頻繁に起こるエラーのよくある原因とその解決策について解説します。
計算すべき値が見つからない【#N/A!】
計算すべき対象が見つからないとき、VLOOKUP関数は【#N/A!】というエラーを返します。
先の表で、関数を組んだにも関わらず検索条件である【A3】を空欄にしていたためエラーが出ていたケースを思い出すと理解しやすいでしょう。
なお、このエラーについては、VLOOKUP関数の式をコピーした場合にも頻繁に起こります。
式をコピーして別のセルに貼り付けると、参照先がずれてしまうことがあります。
【#N/A!】のエラーが出た際には、関数が見に行っているセルが正しいかどうかを確認することで修正できます。
検索範囲外を検索させようとしている【#REF!】
【#REF!】もVLOOKUP関数で頻繁に発生するエラーのひとつです。
【#REF!】のエラーは、VLOOKUP関数の式のうち【範囲】と【指数】の値に矛盾があることを意味します。
たとえばこの式の場合【範囲】には【A6からE21】を参照するように定義しているにも関わらず、【指数】で【6列目（F列）】の値を持ってくるように定義しています。
そのため、【参照範囲内に指数がない】というエラーを返してきている状態なのです。
この場合、参照範囲の値を【A6:F21】と正しい範囲に修正することでエラーを解消できます。
検索結果がおかしい【検索キーがユニークでない】
VLOOKUP関数を使って検索を行った際に、意図した検索結果が表示されない場合があったものと想定します。
このようなケースで関数の式に問題がない場合、表自体のデータに問題がある場合があります。
例として、以下の検索結果が表示されたと想定します。
検索した担当者は【A1004】は【山田 太郎】さんのIDとして検索しましたが、結果には【佐藤 一郎】さんが表示されています。
これは6行目を見るとわかるように、【佐藤 一郎】さんと【山田 太郎】さんで社員IDが重複していることに原因があります。
機械的に社員IDが割り振られる場合には重複は考えにくいですが、手作業でIDを発番しているような場合には社員IDがユニーク【※固有のもの】でない場合があります。
検索条件として指定する数値・文字列は、表中で重複することのない固有の情報を用いるように設計しなければなりません。
【応用編】別のシートを参照する
先の事例では、表と検索結果の表示フィールドを同じシートに作りました。
しかし、社員の数が増えてデータの量が多くなると同じシートでは見づらいというケースも出てきます。
そこで検索結果の表示フィールドを別シートに作成し、別シートの表を参照するように関数を組み立ててみましょう。
このような場合も、関数の組み立て方はそれほど難しくありません。
表のあるシートのシート名を【 ‘（シングルクォーテーション）】で囲み、その後に半角の【!】を記入して参照範囲を記入します。
参照先のシートが変わっても、シート名を適切に指定できていれば問題なく表は反映されます。
#N/A! エラーが出た場合
別のシートを参照先としているにも関わらず、同じシートに表がある場合と同じ記載方法にすると、【#N/A!】のエラーが表示されます。
参照するシート名の記載を忘れずに行うこと、そしてシート名・検索範囲の記載方法はルールに従って記載することでエラーを解消することができます。
【応用編】複数条件を設定する
先にエラー例で挙げたように、【社員IDに重複がある場合】などは社員IDだけを検索条件として検索しても期待通りの結果は得られません。
このような場合は【複数条件での検索】が行えるように検索フィールドを設定しましょう。
具体的には、まずA列に【作業列】を作ります。
それぞれの社員の左側に、【社員ID】と【社員名】を結合するための式を入力します。
この式は、該当のセル2つを【&】でつなぎます。
これによって、【社員ID】＋【社員名】を入力すると【ふりがな】以降が自動で反映されるようになります。
A列に【社員ID】と【社員名】が続けざまに入力されているデータが入るようになったら、今度は検索フィールドの式を変更します。
検索条件を【B3&C3】と設定します。
これにより、【社員ID】と【社員名】を合わせて検索してくれるようになります。
なお、A列を追加したためそのあとの検索範囲にA列を加え【A6:G21】と変更することを忘れないようにしましょう。
画像注釈にあるとおり、【社員ID】と【社員名】の両方を入力することではじめて【ふりがな】以降の情報が表示されるようになりました。
A列に作成した作業列は、【社員ID】と【社員名】を結合して検索するために必要な列というわけです。
【応用編】文字列を含む検索と抽出
VLOOKUPを利用して表中を検索する場合に、文字列を検索値として検索したいというケースもあります。
たとえば以下のような表があると仮定します。
この表はある会社の組織図から抜き出したもので、社員が所属課を名乗った際にその上位の所属【△△部】を特定するために作ったシートです。
このような場合、A3の検索値に所属課を入力すると、B3に所属部が表示されれば目的は果たせます。
また、社内のやりとりでよくあるケースとして、【一課の○○】【契約の△△】といったように所属課を必ずしも正確な形で名乗るとは限りません。
そのため、文字列の一部でも検索ができるように関数を組み立ててみます。
表中の注釈にもありますが、【＊】は【ワイルドカード】と呼びます。
このワイルドカードが入ることで、A3に記入された文字列を一部でも含むセルを探すという意味になります。
これを確定したうえで、A3に文字列を記入して検索してみましょう。
画像にあるように、【契約】という文字だけでB3に【技術部】という検索結果が反映されました。
VLOOKUP以外を使う
EXCELには、VLOOKUP関数と似た関数として、【HLOOKUP】と【XLOOKUP】があります。
- HLOOKUP – 検索条件を指定して右方向へ表を検索する
- XLOOKUP – VLOOKUPよりも詳細かつ簡易に条件検索が可能な応用版
VLOOKUPと似た部分もありつつも、それぞれに特色がありVLOOKUPとは異なる部分もあります。
VLOOKUPとXLOOKUPの違いとは？
VLOOKUPでは、【検索値】【検索範囲】【指数】【検索方法】という4つの属性を入力して検索しました。
XLOOKUPではこれらの指定がより直感的で分かりやすく、かつ不要な部分が省略できるようになっています。
XLOOKUPでは【検索値】【検索範囲】【戻り範囲】までを指定すれば検索が可能で、必要であれば【見つからない場合の表示】【一致モード】【検索モード】といった引数の指定が可能です。
おすすめはどっち？
VLOOKUPとHLOOKUPは、それぞれ縦方向と横方向に検索するという違いがありますが基本的な動きは同じです。
一方でXLOOKUPはMicrosoft公式でも使用が強く推奨されており、実際に引数の指定もVLOOKUP・HLOOKUPに比べて簡単になっています。
そのため、XLOOKUPに対応しているバージョンのEXCELを利用できる環境であれば、XLOOKUPがおすすめであるといえます。
ただし、以下のようにXLOOKUP関数が利用できないケースもあります。
※Microsoft Office・EXCELのバージョンによってXLOOKUPに対応していない場合があります。
※EXCEL機能の一部を代替できる【Googleスプレッドシート】では、XLOOKUP関数は利用できません。
私自身も、基本的にXLOOKUPを利用しています。
その理由としてはXLOOKUPのほうが引数の指定も少なく済む場合が多く、また式の構成も直感的にわかりやすいためです。
XLOOKUP関数の使い方も解説していますので、ぜひ使ってみてください。
一方で、以下のような場合にはVLOOKUPを利用するのがおすすめです。
- EXCELのバージョン等によりXLOOKUP関数が利用できない環境である
- LOOKUP系の検索が行える関数の考え方をまず学びたい
VLOOKUP関数の使い方・応用まとめ
VLOOKUP関数を利用するための基本的な使い方を把握し応用できるようになることで、EXCELの表中を非常に効率的に検索することができます。
表や検索条件の設定を作り込めば、簡易的な検索システムすらEXCELのみで実装が可能となるケースもあります。
商品管理や人材管理のほか部署の資産管理など活用できるシーンは多いため、VLOOKUP関数の使い方を身につけることは大きなメリットがあるのです。
エクセルでよく使う必須の関数を短時間でマスターする！
エクセルのよく使う関数まとめ13選
