Excel

 

 / 最終更新日: 2021.01.25

【Excel】XLOOKUP関数(近似値検索編):VLOOKUP関数の進化系(Excel 2019・365)


こんにちは。
@OfficeTAKUです。

ご存知のことと思いますが、Excelのバージョンが上がる度にExcelの関数も増えています。
今回は Excel 365 、Excel 2019 から追加された関数、XLOOKUP関数についてです。

XLOOKUP関数かなり進化しています。
まずは、従来のVLOOKUP関数の代わりとして使う方法。
今回は近似値検索の場合です。

【Excel環境】
サブスクリプション:Microsoft 365 for business

  • Microsoft Excel for Mac バージョン16.46
  • Microsoft Excel for Microsoft 365 MSO 16.0

XLOOKUP関数

【XLOOKUP関数】
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
(大かっこ[]の引数はオプション・省略可)

引数 説明
検索値 従来と同じ検索したい値を設定
検索範囲 検索する範囲or配列
戻り範囲 結果として返したい範囲or配列
[見つからない場合] 検索値に一致するものが見つからない場合に表示したい文字列を入力
[一致モード] 0 – 完全一致:これがデフォルト(既定値)。
-1 – 近似値:見つからない場合は、検索値未満の最大値が返されます(VLOOKUP関数で近似値検索の時に指定したTRUEに該当)。
1 – 近似値:見つからない場合は、検索値より大きい最小値が返されます。
2 – *、?、および 〜 が特別な意味を持つワイルドカードの一致。
1 – 先頭の項目から検索を実行。これがデフォルト(既定値)。
-1 – 末尾の項目から逆方向に検索を実行。
2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。
-2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行。並べ替えられていない場合、無効な結果が返されます。

XLOOKUP関数でコードを検索し商品名・単価を表示させる

下図のようなBMI(Body Mass Index)判定表を作成してみましょう。

D列のBMIの値を、右の「肥満度分類(日本肥満学会)」表(セル範囲 G2:H7)から検索し判定を、E列の判定欄に表示します。

【数式:セルE2】
=XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,””,-1)

関数の引数ダイアログボックス(イメージ):Windows

関数の引数ダイアログボックスの入力例
注意:画像合成してあります。実際はダイアログボックス内でスクロールして下さい。

数式パレット作業ウインドウ(イメージ):Mac

数式パレットの入力例
注意:画像合成してあります。実際は数式パレット内でスクロールして下さい。

ポイントとしては、

  • 検索範囲
    肥満度分類表のBMI セル範囲G2:G5(コピーのために絶対参照)
  • 戻り範囲
    肥満度分類表の判定 セル範囲H2:H7
  • [見つからない場合]
    コードが未入力の時には空白を表示したいので “” (ダブルコーテーション)を2つ入れています。
    場合に応じて、「該当なし」とか「見つかりません」などという風にメッセージを入力しても構いません。
    VLOOKUP関数であれば、IF関数を利用したりしていましたが、それが必要なくなりました。便利です。
    省略するとエラー[#N/A]が表示されてしまいますので注意が必要です。
  • [一致モード]
    [-1]を指定します。従来のVLOOKUP関数での[TRUE]と同様の働き
  • [検索モード]
    省略、あるいは[1]を指定。

完成例

XLOOKUP関数とVLOOKUP関数での数式比較

XLOOKUP関数で作成した数式と従来のVLOOKUP関数を利用した数式を比較してみましょう。

BMIが空白の場合など結果がエラーとなる場合は空白を返す、という設定で、VLOOKUP関数の場合は、IF関数とISERROR関数を利用しています。

【数式:セルC4】

=XLOOKUP(D2,$G$2:$G$5,$H$2:$H$5,””,-1)

=IF(ISERROR(=VLOOKUP(D2,$G$2:$H$7,2,TRUE)),””,VLOOKUP(D2,$G$2:$H$7,2,TRUE))

まとめ

Excel 365,2019で追加された XLOOKUP 関数

従来のVLOOKUP関数の代わりに使うことができます。
もちろんVLOOKUP関数がなくなったわけではありませんので、今回の場合はあえて使うことはありません。

しかし、より幅広い使い方のできる関数ですので、慣れるためにも利用していくとよいと思います。

この関数も下位バージョンでは利用できません。
会社や学校で、Excel2016以前が使用されている状況、ファイルをやり取りする必要がある環境では使わない方がいいですね。

created by Rinker
富士通エフ・オー・エム
¥2,310 (2021/11/28 21:14:47時点 Amazon調べ-詳細)

created by Rinker
翔泳社
¥2,310 (2021/11/28 11:31:27時点 Amazon調べ-詳細)

【Excel】XLOOKUP関数(完全一致検索編):VLOOKUP関数の進化系(Excel 2019・365)前のページ

【レビュー】武田英志『伝わるデザインの授業 一生使える8つの力が身につく』次のページ武田英志『伝わるデザインの授業 一生使える8つの力が身につく』

ピックアップ記事

  1. 【初心者・シニアのためのVOD入門】お家で映画三昧!休みは動画配信サイトのお試し…
  2. [レビュー] リンダ・リウカス『ルビイのぼうけん こんにちは!プログラミング』:…
  3. 以前のTwitter画面に戻せる拡張機能「GoodTwitter」が便利「Twi…
  4. Apple Music や Amazon Musice でクラシック音楽の楽曲・…
  5. [Amazon]【30日間無料体験】絶対おすすめ!アマゾン プライム会員。買い物…

関連記事

  1. Photo by Erda Estremera on Unsplash

    Excel

    [Excel] エクセル[名前ボックス]のいろいろな使い方

    こんにちは。@OfficeTAKUです。Excel…

  2. Excel

    【Excel】テンキーだけで時刻(「:」コロン)を入力する方法

    こんにちは。@OfficeTAKUです。最近はノートパソコ…

  3. Excel

    [XL] Excelフィル-1:オートフィルで連番を作成する

    Excel の フィル機能、利用すればデータ入力がとても楽になります。…

LINE公式アカウント:OfficeTAKU

OfficeTAKU Line公式アカウントバナー

最近の記事

人気の記事

  1. 日商PC検定
  2. ロジクール MX KEYS(MAC用)
  1. Canva

    WebService

    これはおすすめ! Canva:ブログやSNSのヘッダ画像など、かんたんに、かっこ…
  2. 日商PC検定

    日商PC検定

    日商PC検定・簿記初級など日本商工会議所検定試験会場になりました!
  3. Web

    2020年プログラミング教育必修化が不安!!というお母さんお父さんに伝えたいこと…
  4. WebService

    Office TAKU、LINE@ アカウント 開設しました!
  5. Surface Mobile Mouse

    Hard・Acc

    【レビュー】Microsoft Surface Mobile Mouse 薄くて…
PAGE TOP