\r\n\r\n
Excelの新しいXLOOKUPは、VLOOKUPに代わるもので、Excelの最も人気のある関数の1つに強力な代替手段を提供します。この新機能は、VLOOKUPのいくつかの制限に対応し、さらに機能を追加したものです。ここで知っておいていただきたいことがあります。
新機能XLOOKUPは、VLOOKUPの最大の欠点であったいくつかの点を解決しています。さらに、HLOOKUPに代わるものです。例えば、XLOOKUPは左側を見ることができ、デフォルトは完全一致で、列番号ではなくセル範囲を指定できます。vloopはそれほど使いやすく、汎用的ではありません。この仕組みをご紹介します。
現在、XLOOKUPはInsidersプログラムのユーザーのみが利用可能です。インサイダーズプログラムに参加すれば、誰でも最新のExcel機能をいち早く利用することができます。マイクロソフトは、近日中にすべてのoffice 365ユーザーへの展開を開始する予定です。
次のデータを例にして、XLOOKUPの例で本題に入りましょう。A列の各IDの部署をF列から返したい。
これは完全一致検索の典型的な例です。XLOOKUP関数に必要な情報は3つだけです。
次の図は、6つのパラメータを持つXLOOKUPであるが、完全一致のために必要なのは最初の3つだけである。
この例では、次の式が適用されます: =XLOOKUP(A2, $E$2:$E$8, $F$2:$F$8)
では、VLOOKUPに対するXLOOKUPの優位性をいくつか探ってみましょう。
VLOOKUPの悪名高い第3パラメータは、テーブル配列から返すべき情報を指定する列番号である。XLOOKUPでは、返したい範囲(この例ではF列)を選択することができるので、このような問題はありません。
XLOOKUPは、以下で詳しく説明するVLOOKUPとは異なり、選択したセルの左側のデータを表示できることを忘れないでください。
また、**新しい列が追加されたときに、数式が壊れる問題もなくなりました。スプレッドシートでこれが発生した場合、戻り値の範囲が自動的に調整されます。
VLOOKUPを学ぶとき、なぜ完全一致を指定しなければならないのか、常に混乱が生じます。
幸いなことに、XLOOKUPのデフォルトは完全一致です(ルックアップフォーミュラを使う理由としては、こちらの方が一般的です)。これにより、第5引数に答える必要がなくなり、数式を初めて使う人でも間違いが少なくなります。
つまり、XLOOKUPはVLOOKUPよりも質問数が少なく、親しみやすく、永続的に使えるということです。
ルックアップ範囲を選択できるため、XLOOKUPはVLOOKUPよりも汎用性が高く、XLOOKUPではテーブルの列の順番は関係ありません。
VLOOKUPは、テーブルの一番左の列を検索し、指定された列数から右に戻ることで制約をかけます。
以下の例では、ID(E列)を見つけて、その人の名前(D列)を返す必要があります。
これは、次の式で実現されます:=XLOOKUP(A2, $E$2:$E$8, $D$2:$D$8)
VLOOKUP関数やMatch関数で必要なものが見つからないときに表示される#N/Aエラーメッセージは、検索関数のユーザーならよくご存じでしょう。これには、たいてい論理的な理由がある。
そのため、ユーザーはこのエラーが正しくない、あるいは有用であるとして、すぐにこのエラーを隠す方法を調べます。もちろん、その方法はあります。
XLOOKUPには、このようなエラーを処理するための「if not found」パラメータが組み込まれています。先ほどの例を実際に見てみましょう。ただし、間違ったIDが入力されている状態です。
次の数式を実行すると、エラーメッセージではなく、「IDが正しくありません」というテキストが表示されます: =XLOOKUP(A2, $E$2:$E$8, $D$2:$D$8, "Incorrect ID")
完全一致ほど一般的ではありませんが、ルックアップ式の非常に効果的な使い方は、範囲内の値を検索することです。次のような例を考えてみましょう。使った金額に応じて、割引で還元したい。
今回は特定の値を求めるのではなく、B列の値がE列の範囲に収まっていることを知る必要がある。これにより、得られる割引額が決まる。
XLOOKUPには、マッチモードと呼ばれるオプションの第5パラメータがあります(デフォルトは完全一致と覚えておいてください)。
XLOOKUPはVLOOKUPよりも近似マッチング力が高いことがわかります。
探している値より小さい(-1)か大きい(1)か、最も近い値を探すかどうかを選択することができます。また、ワイルドカード(2)文字を使用するオプションもあります(例:?または * 記号を使用します。デフォルトでは、VLOOKUPと異なる設定になっています。
この例の式は、完全に一致する値が見つからない場合、見つかった値よりも小さい最も近い値を返します: =XLOOKUP(B2, $E$3:$E$7, $F$3:$F$7,, -1)
しかし、セルC7には#N/aエラーが返される('if not found'パラメータが使用されていない)エラーがある。これは、消費額64が割引の基準を満たさないため、0%の割引が適用されるはずです。
XLOOKUP関数のもう一つの利点は、VLOOKUPのように検索範囲を昇順にソートする必要がないことです。
ルックアップテーブルの下部に新しい行を入力し、数式を開きます。コーナーポイントをクリックしてドラッグし、使用する範囲を広げます。
この数式はすぐにエラーを修正します。「0」を範囲の一番下に配置することは問題ありません。
個人的には、今でもルックアップ・カラムでテーブルをソートしています。一番下に「0」があると、気が狂いそうです。でも、配合が崩れないというのは素晴らしいですね。
前述したように、ここでもHLOOKUPの代わりにXLOOKUP関数が使われている。1つの関数が2つの関数を置き換える。
HLOOKUP関数は水平方向のルックアップで、行に沿った検索に使用されます。
兄弟分のVLOOKUPほど有名ではありませんが、次のような例で、ヘッダーがA列、データが4行と5行にある場合に便利です。
XLOOKUPは、列の下方向と行に沿った方向の2方向で見ることができるので、2種類の関数が不要になりました。
この例では、セル A2 の名前に関連する売上高を返す数式を使用しています。4行目に沿って名前を探し、5行目から値を返す: =XLOOKUP(A2, B4:E4, B5:E5)
通常、ある値に対して最初に(通常は一意に)一致するリストを見つける必要があります。xlookerupには検索モードと呼ばれる6番目のパラメータがあります。これにより、検索を下から始めるように切り替え、リストを検索して最後に発生した値を見つけることができます。
以下の例では、A列にある各製品の在庫レベルを求めたいと思います。
ルックアップテーブルは日付順に並べられ、商品ごとに複数の在庫確認ができるようになっています。最終チェック時(商品IDの最終出現時)の在庫レベルを返したい。
XLOOKUP関数の6番目のパラメータには4つのオプションがあり、ここでは「最後から一番目の検索」オプションを使用することに興味があります。
完全な式は以下の通りです: =XLOOKUP(A2, $E$2:$E$9, $F$2:$F$9,,,,-1)
この式では、4番目と5番目の引数は無視されます。これはオプションで、デフォルトは完全一致になることを期待しています。
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の後継として待望されている関数です。
この記事では、さまざまな例を用いて、XLOOKUPの利点を紹介します。その一つは、XLOOKUPがワークシート、ワークブック、テーブルをまたいで使えることです。記事中の例は、私たちが理解しやすいようにシンプルなものにしています。
動的配列がExcelに導入される予定なので、値の範囲を返すことも可能です。
Flukupの時代は終わり、xLOOKUPが登場し、まもなくそれを見つけるためのデファクト式になるでしょう。