Excel2016 XLOOKUP新しい関数の紹介 VLOOKUP苦手な人に朗報です 

ECRSってなに?改善の4原則 ビジネスの生産性向上や整理のためのフレームワーク Excel
こんな方におススメ
  • 新しい関数を使ってみたい
  • VLOOKUPの説明を受けたがうまく行かない、ニガテ

最低限知っておくべきことのみ、まとめました。結論的にはVLOOKUP関数で届かなかったかゆいところに手が届いたような関数なので、便利だと思います。

Excel2016 XLOOKUP新しい関数の紹介 VLOOKUPがわからない、できない人も見てみてください。

Excel2016 XLOOKUP新しい関数の紹介 VLOOKUPがわからない、できない人も見てみてください。

今回、VLOOKUPについてまずは触れずに説明します。最後に違いについて簡単に説明しますが、一度VLOOKUPで挫折した人も試してみてください。

大きな違いについても説明しますので、違いだけを知りたい人も是非ご覧ください。

XLOOKUP活用法 使い方① できることのイメージはこんな形です。 

XLOOKUP活用法 使い方① VLOOKUPより簡単。 新機能:戻り範囲の選択は1列でいい

まずA1~C11列の並びを見てください。

  • A商品番号 1~10番
  • B商品名 (りんご・バナナ…と続く 果物 の名前)
  • C価格  100円など

が書いてあります。

数式は以下になっています。

=XLOOKUP(この中に必要なセルを選んでいきます。

=XLOOKUP(E2の商品番号と同じものを,A2~A11セルの中で探してね、探したらその商品のB2~B11セルの中身つまり果物の名前を教えてね。,見つからなかったら「0」と答えてね)

という指示になります。

今の日本語をすべて省くと、

=XLOOKUP(E2,A2 :11,B2:11,0)になります。

次にG2セルの中身を見てみます。

A2:A11がA2~A11という意味になり、日本語の間は「、」もしくは「,」で区切ればいいです。
A2:A11はドラッグアンドドロップでも選択できます。数式の打ち方は以下の動画をご覧ください。

同じ内容が離れたセルでも適用できます。

=XLOOKUP(E2の商品番号と同じものを,A2~A11セルの中で探してね、探したらその商品のC2~C11セルの中身つまり果物の名前を教えてね。,見つからなかったら「0」と答えてね)

=XLOOKUP(E2,A2 :11,C2:11,0)になるわけです。基本はコレだけ。

「F2セル」を選択した状態で、「数式タブ」「関数の挿入」からでも入力できます。

「関数の検索」欄にXLOOKUPと入力、「検索開始」ボタンをクリック、関数名に表示される「XLOOKUPが青くなるようクリック」、「OK」をクリック

XLOOKUP活用法 使い方②  新機能:マイナス方向への検索が可能

=XLOOKUP(E7の商品名と同じものを,B2~B11セルの中で探してね、探したらその商品のA2~A11セルの中身つまり商品番号を教えてね。,見つからなかったら「0」と答えてね)

=XLOOKUP(E7,B2:B11,C2:C11,0) 場所は違いますが考え方は一緒です。

この数式を入れた状態でE7セルに商品名を正確に打ち込むと、商品番号が出てきます。

基本的にはXLOOKUP関数の説明はこれで8割です。

商品番号(A1列に貼り付け)商品名価格
1りんご100
2バナナ200
3キウイ300
4なし400
5もも500
6ブドウ150
7みかん250
8スイカ350
9メロン450
10さくらんぼ550
こちらを使って試してみてください。

XLOOKUP活用法 使い方③ 横にも検索可能 新機能:縦横共用=HLOOKUPは不要

=XLOOKUP(A7の商品名と同じものを,B1~K1セルの中で探してね、探したらその商品のB2~K2セルの中身つまり商品番号を教えてね。,見つからなかったら「0」と答えてね)

=XLOOKUP(A7,B1:K1,B2:K2,0)

商品番号(A1に貼り付け)12345678910
商品名りんごバナナキウイなしももブドウみかんスイカメロンさくらんぼ
価格100200300400500150250350450550

XLOOKUP活用法 使い方④ 応用編 複数の選択範囲とスピル

ちょっとすごい機能をもう一つ紹介します。「戻り範囲」を2列以上で指定すると・・・

F2に「メロン」が入力され、本来数式のないG2には薄い文字でF2と同じ数式が入ります。これはスピル(あふれる の意味)と言って、数式がないところにも式の結果が流れ込んでいると思ってください。数式を入れているのは1つのセルなのに、メロンの隣の数字(価格)まで数式の結果がスピル(あふれる)します。

XLOOKUP活用法 使い方⑤ 詳細編 見つからない場合・一致モード・検索モードについて

XLOOKUP活用法 使い方⑤ 詳細編 見つからない場合・一致モード・検索モードについて

見つからない場合の検索方法なども複数あります。

見つからない場合

  • 0  →0を返す 

一致モード(任意)

  • 0   →完全一致
  • 1   →完全一致か次に大きな近似値
  • -1 →完全一致か次に小さい近似値
  • 2  →ワイルドカード

検索モード

  • 1  →先頭から末尾まで順に検索
  • -1  →末尾から先頭まで順に検索

XLOOKUP活用法 本来の使い方(絶対参照)は別記事で紹介しようと思います

一応今回はゼロベースでXLOOKUP関数の仕組みをお伝えしました。すでにVLOOKUP関数を使える方からは「絶対参照はどうした?」という叱咤を頂きそうな内容でありますが、今回XLOOKUPは色々な人に直感的に使いやす機能になったのでまずは、その点をお伝えしたかったです。

絶対参照の考え方はもちろんVLOOKUP、HLOOKUP使用時と一緒なのでそのまま使っていただいて大丈夫です。

Excel生産性向上
スポンサーリンク
Tをフォローする
みちログ

コメント