いまさら聞けないExcelの使い方講座

【Excel】エクセルのXLOOKUP関数とテーブル機能を組み合わせると作業効率がアップ!

XLOOKUP関数とテーブルを組み合わせて使う

データが増えるたびに数式を変更するのが面倒!

 普段の業務で、大量のデータの中から目的のデータを探したり、検索したデータをセルに入力したりする際に、関数を使ってサッとできたら効率的ですよね。

 VLOOKUP関数の改良版として登場したXLOOKUP関数は、大量のデータの中から検索条件を満たすデータを取り出して表示してくれる、たいへん便利な関数です(Microsoft 365版のExcelでのみ使用可能)。

 前回の記事では、次のような[請求書]シート(①)と[商品カタログ]シート(②)が保存された[請求書]ブックの例を使い、[請求書]シートのA列に入力した「商品番号」(③)をもとに、[商品カタログ]シートの「商品カタログ」(④)から必要な情報を抜き出して、B列に表示する(⑤)方法を解説しました。

前回の記事では、[請求書]シートのA列に入力した「商品番号」(③)をもとに、[商品カタログ]シートの「商品カタログ」(④)から必要な情報を抜き出して、B列に表示する(⑤)方法を解説しました

 このような例で、「商品カタログ」に新しいデータが追加された場合、どうすればいいでしょうか。例として、[商品カタログ]シートの13行目(⑥)に新しいデータが追加された場合を考えてみます。

 現在、[請求書]シートのセルB9には「=XLOOKUP(A9,商品カタログ!$C$4:$C$12,商品カタログ!$A$4:$A$12)」(⑦)という数式が入力されていますが、セル範囲「$C$4:$C$12」やセル範囲「$A$4:$A$12」を参照している箇所を、それぞれセル「C13」やセル「A13」まで参照するように書き直さなければなりません。

参照している箇所を、それぞれセル「C13」やセル「A13」まで参照するように書き直さなければなりません

 このようなとき、XLOOKUP関数が参照する「商品カタログ」の表をテーブルに変換しておくと便利です。テーブルに変換しておけば、[商品カタログ]シートの13行目に新しいデータが追加されても、[請求書]シートのB列に入力した数式を変更しなくてもよくなります。

 今回は、テーブルの機能とXLOOKUP関数を組み合わせて便利に活用する方法を解説します。なお、XLOOKUP関数だけでなく、VLOOKUP関数でも利用できるテクニックです。VLOOKUP関数を使っている人も、ぜひ読んでくださいね。

数式で参照する表をテーブルに変換してみよう

 先ほどの例で、[請求書]シートのB列に入力された数式が参照している範囲([商品カタログ]シートのセル範囲「A3:C12」)をテーブルに変換してみましょう。

 表内のいずれかのセルを選択(①)した状態で、[挿入]タブ(②)→[テーブル](③)をクリックします。

いずれかのセルを選択(①)した状態で、[挿入]タブ(②)→[テーブル](③)をクリック

 すると、[テーブルの作成]ダイアログボックスが表示されます。テーブルに変換するデータ範囲が自動的に認識されるので、正しく入力されていること確認します(④)。また、[先頭行をテーブルの見出しとして使用する]のチェックマーク(⑤)がONになっていることを確認して、[OK](⑥)をクリックします。

[先頭行をテーブルの見出しとして使用する]のチェックマーク(⑤)がONになっていることを確認して、[OK](⑥)をクリック

 これで、数式で参照する表「商品カタログ」をテーブルに変換できました(⑦)。

数式で参照する表「商品カタログ」をテーブルに変換できました

 では、この「商品カタログ」表の最後に新しいデータを追加してみます。13行目にデータを入力して[Enter]キーを押すと、テーブルが拡張され、自動的に他のセルと同じ書式が設定されます(⑧)。

「商品カタログ」表の最後の行に新しいデータを追加してみます

 [請求書]シートに戻りましょう。セルA9の値を、先ほど新しく追加したデータの商品番号「C102」(⑨)に書き替えてみます。値を入力できたら[Enter]キーを押します。

セルA9の値を、先ほど新しく追加したデータの商品番号「C102」(⑨)に書き替えてみます

 すると、セルB9には「C102」に紐づいた商品名「ファイルボックス B5 10個セット」(⑩)が表示されましたね。数式を見てみると「=XLOOKUP(A9,商品カタログ!$C$4:$C$13,商品カタログ!$A$4:$A$13)」(⑪)となっています。参照する範囲が自動的に拡張されていることがわかりますね。

セルB9には「C102」に紐づいた商品名「ファイルボックス B5 10個セット」(⑩)が表示されました

 これなら、「商品カタログ」のデータが増えるたびに、XLOOKUP関数の数式を変更する必要はありませんね。

テーブルに変換しておけばデータが増えても安心!

 今回は、テーブルの機能とXLOOKUP関数を組み合わせて便利に活用する方法を解説しました。XLOOKUP関数で参照する範囲をテーブルにすると良い理由をわかってもらえたのではないでしょうか。このように、表をテーブルに変換すると、仕事の効率が格段に上がることが多いです。ぜひ試してみてくださいね。