Excelの主要関数 · XLOOKUPとVLOOKUP
Excel Lookup Functions (XLOOKUP, VLOOKUP, INDEX/MATCH)
エクセル主要関数とは?
- 検索関数は、二つの表を基準値一つでつなぐ道具
- XLOOKUPは便利だが、バージョンによっては使えないことがある
- エラーの大半は関数ではなく、基準値の状態にあります
検索関数を身につけるということは、文法を暗記することではありません。人事・教育担当者のエクセル作業のほとんどは二つの表を一つの基準値でつなぐ作業です。人事名簿の社員番号で教育受講履歴を紐づけ、受講者名簿に部署と役職を埋め、年俸契約対象者の表に評価等級を持ってくる。この作業を目でコピー&ペーストすると、300名規模では必ず数行がずれます。検索関数は速さより正確さのためのツールです。
VLOOKUPは =VLOOKUP(検索値, 表の範囲, 列番号, 0) の形で、表の一番左の列で探し、そこから右へ何番目の列の値を取り出します。最後の引数を0(FALSE)にしないと、近い値を返す近似一致で動作し、表の途中に列を挿入すると列番号がずれて的外れな値を返します。INDEX/MATCHは =INDEX(取り出す列, MATCH(検索値, 検索する列, 0)) の形で、検索する列と取り出す列を別々に指定するため、左方向の検索ができ、列の挿入にも強いです。
XLOOKUPは =XLOOKUP(探す値, 探す範囲, 取り出す範囲, 見つからない場合に表示する値) の形です。既定値は完全一致で、検索方向の制限がなく、見つからなかった時の文言を関数の中で指定します。ただし Microsoft 365と比較的新しいバージョンでしか動かないため、ファイルを受け取る相手のエクセルのバージョンを先に確認する必要があります。古いバージョンで開くと数式がエラーに変わります。複数の会社とファイルをやり取りするなら、INDEX/MATCHが安全な共通の選択肢です。
現場で最もよくつまずくのは関数ではなく基準値の状態です。社員番号が片方は数値、もう片方は文字で保存されていたり、名前の後ろにスペースが付いていたりすると、どの関数でも「なし」を返します。名前を基準値にすると、同姓同名のうち最初の人の値だけを取ってきても、エラー表示は出ません。基準値は社員番号のように重複のない値にし、結果のエラー件数と全件数を元データと照合する習慣が、関数の選択よりも重要です。
| 区分 | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| 基本の形 | =VLOOKUP(検索値, 表の範囲, 列番号, 0) | =INDEX(取り出す列, MATCH(探す値, 探す列, 0)) | =XLOOKUP(探す値, 探す範囲, 取り出す範囲) |
| 検索方向 | 探す列の右側のみ | 制限なし | 制限なし |
| 一致方式の既定値 | 近似一致(0を入れると完全一致) | MATCHの0で完全一致を指定 | 完全一致 |
| 列を挿入したとき | 列番号がずれて結果が狂うことがある | 影響は小さい | 影響は小さい |
| 見つからなかったとき | エラー表示(IFERRORで囲む必要あり) | エラー表示(IFERRORで囲む必要あり) | 4番目の引数で表示文言を指定 |
| 使えるバージョン | ほぼすべてのバージョン | ほぼすべてのバージョン | Microsoft 365など最近のバージョン |
VLOOKUPは表計算ソフトの初期から使われてきた検索関数で、その限界を避けるための実務上の慣行としてINDEXとMATCHの組み合わせが広まりました。XLOOKUPは、この二つの不便さを減らすためにマイクロソフトが後から追加した関数なので、Excelのバージョンによっては使えない場合があります。
仮想の事例で見てみましょう。社員680名の食品メーカーA社の教育チームは、年末の教育実績報告を前に、人事名簿とLMS受講履歴2,300件をつなぎ合わせる必要がありました。昨年は代理一人が二日かけてコピー&ペーストし、報告後に部署別の受講率3か所が誤っていたことが判明しました。
- 教育チームの担当者が修了履歴の氏名で部署を検索したところ、同姓同名の11人全員が最初の人の部署で埋まってしまいました。チーム長が"名前ではなく社員番号で改めて合わせ直しましょう"と言いました。
- 社員番号で検索し直すと、今度は140件が'該当なし'と表示されました。LMSからダウンロードした社員番号が文字列形式で保存されていたため、数値に変換したところエラーは6件に減りました。
- 残りの6件は期中退職者でした。代理はXLOOKUPの4番目の引数に「退職者確認」を入れ、空欄の代わりに理由が見えるように変えました。
- 関連会社の人事チームにファイルを送ったところ、「数式がすべてエラーで表示されます」という返信が来ました。相手のエクセルが旧バージョンだったため、INDEX/MATCHで数式を組み直して送り直しました。
- チーム長は、検索結果の件数と原本の件数を照合する検算欄をシートの一番上に設けさせ、2,300件が両方で一致することを確認してから報告書に回しました。
- 集計時間は2日から3時間に短縮され、報告後の訂正依頼は3件から0件になりました。同じファイルは次の四半期の実績集計にもそのまま使われました。
ク・ジャリョン
金鍾赫