【Excel】Vとか、Hとか、Xとか、ルックアップ関数はもう覚える必要すらない！それもう関数使わないでできるよ！

はじめに

Excelは仕事において非常に重宝している神ツールで、少し前まではあらゆる関数を駆使していろいろなデータ整形をしておりました。

少しググればいろいろなケースでいろんな関数を紹介しているサイトがあって、困ることがほとんどなく、基本的に実現したいことはたいていできる素晴らしいツールだと思っております。

関数の中でも特に、VLOOKUPやHLOOKUP関数はデータ整形する中で頻繁に扱う関数で、さらには最近では、XLOOKUP関数というVLOOKUPやHLOOKUPの上位互換というべき素晴らしい関数が登場しました。

参考

XLOOKUP 関数 – Microsoft サポート

support.microsoft.com

ただ、このLOOKUP関数は、使い方を理解するのに少し厄介であると感じております。

私も初めてこの関数に出会ったときは、理解するのに時間がかかりました。

ですが、このLOOKUP関数を覚えなくても同じことが実現できるので、今回はそちらを紹介していきたいと思います。

サンプル事例

LOOKUP関数をよく見かけるケースで考えていきます。

従業員管理（顧客管理）

例えば下図のような従業員DBがあって、その従業員がいくつかある事業所DBと紐づいているケースを考えてみます。（従業員を顧客、事業所を取引先名などと置き換えれば顧客管理にも使えます。）

こちらは架空のデータです。メールや電話を掛けるのはお控えください。

この2つのデータを合わせたいと思ったとします。

例えばある従業員になにかを送る場合、従業員DBには住所情報がないので、事業所DBから住所を持ってくる必要があります。

そんな場合、下図のようにVLOOKUP関数を使って対応することがあると思います。

ただ、この関数にも以下のような弱点があると考えております。

セルの中身を見ないと何をしているかわからない
行が増えたり、列が増えたりすると追従しないことがある
列の順番を変えたりするとうまくいかないことがある

ということで、これらのデメリットを解消するやり方を紹介します。

STEP
従業員情報の取り込み

まずは、従業員情報を取り込むため、下図のように範囲を選択して、データ＞データの取得と変換＞テーブルまたは範囲からをクリックします。

すると、Power Queryエディタが立ち上がりますので、いったん、ホーム＞閉じる＞閉じて読み込むをクリックします。
STEP
事業所情報の取り込み

続けて、事業所情報を取り込むため、従業員情報同様に、下図のように、データ＞データの取得と変換＞テーブルまたは範囲からをクリックします。

すると、従業員情報同様に、Power Queryエディタが立ち上がり、事業所情報のデータも準備できました。
STEP
従業員情報と事業所情報の結合

最後に、従業員情報と事業所情報を合わせていきます。

下図のようにホーム＞結合＞クエリのマージの右の▼ボタンをクリックして、新規としてクエリをマージをクリックします。

すると、マージというウィンドウが立ち上がりますので、続けて、下図のように上のドロップダウンから、従業員情報のテーブルを選択し、下のドロップダウンから事業所情報のテーブルを選択します。

一致させたい列（ここでは事業所ID）を選択してOKをクリックします。

すると、下図のように、従業員一人一人にテーブルという列の情報が追加されます。

最後に下図の通り、テーブル列の右にある展開ボタンをクリックして、事業所情報の必要な列だけ選択してOKをクリックします。

すると、下図のように、事業所名、郵便番号、住所が一人一人に割り当てられました。

最後に、閉じて読み込むと、下図のようなテーブルが完成します。

商品・取引管理

次に、商品と取引に関して考えてみます。

下図のように、商品情報と、取引情報があって、その日の売上がいくらだったかなどを知りたいとします。

こちらも従業員管理と同様にマージしていきます。

STEP
商品情報の取り込み

画像は割愛しますが、商品情報同様に、範囲を選択して、データ＞データの取得と変換＞テーブルまたは範囲からをクリックします。

すると、Power Queryエディタが立ち上がりますので、いったん、ホーム＞閉じる＞閉じて読み込むをクリックします。
STEP
取引情報の取り込み

STEP1同様に、範囲を選択して、データ＞データの取得と変換＞テーブルまたは範囲からをクリックします。

すると、下図のようになります。
STEP
商品情報と取引情報の結合

最後にこれらを結合します。

従業員管理同様に、ホーム＞結合＞クエリのマージの右の▼ボタンをクリックして、新規としてクエリをマージをクリックします。

下図のように、マージのウィンドウが立ち上がりますので、上のドロップダウンに取引情報のデータを選択し、下のドロップダウンに商品情報を選択して、商品ID列をそれぞれ選択してOKをクリックします。

すると、従業員管理同様に下図の通り、新しくテーブル列が追加され、各取引レコードに商品情報が付与されます。

続けて、従業員管理同様にテーブルを下図のように、展開します。

すると、下図のように、商品が展開されました。
STEP
取引額の計算

せっかくなので、このまま各取引の取引額を計算します。

下図のように、列の追加＞全般＞カスタム列をクリックします。

続いて、下図のように、新しい列名に例えば「取引額」にして、カスタム列の式のところに、右の使用できる列から単価と個数を選んで、それらをかけ合わせます。

すると、下図のように、各取引の取引額が計算されます。

このとき、追加されたカスタムというステップができますので、ここの名前を取引額の計算などと名前をつけておくと、どこでなんの計算をしているかもすぐにわかります。
STEP
おまけ

さらにここから、商品ごとのの取引額の合計を計算してみましょう。

下図のように、商品名の列を選択した状態で、ホーム＞変換＞グループ化をクリックします。

続けて、グループ化というウィンドウが立ち上がりますので、下図のように設定します。

すると、下図のように商品ごとの取引金額の合計が計算できます。