新しいサービス【AwesomeQR】

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

4 min 2,136 views

はじめに

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

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

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

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

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

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

サンプル事例

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

従業員管理(顧客管理)

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

従業員DBサンプル
従業員DB
事業所DB

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

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

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

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

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

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

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

  1. STEP

    従業員情報の取り込み

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

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

  2. STEP

    事業所情報の取り込み

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

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

  3. STEP

    従業員情報と事業所情報の結合

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

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

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

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

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

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

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

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

商品・取引管理

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

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

商品DB
取引DB

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

  1. STEP

    商品情報の取り込み

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

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

  2. STEP

    取引情報の取り込み

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

    すると、下図のようになります。

  3. STEP

    商品情報と取引情報の結合

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

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

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

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

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

    すると、下図のように、商品が展開されました。

  4. STEP

    取引額の計算

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

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

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

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

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

  5. STEP

    おまけ

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

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

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

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

さいごに

いかがでしょうか。もう、関数にたよる必要はないんですよね。

Power Queryの使い方さえ覚えれば、もう複雑な関数を覚える必要もないですし、どこで何をやっているかもすぐわかりますし、列の入れ替えをしても計算が崩れることもないですし、メリットがたくさんあります。

今後も、Power Queryでの事例をいくつか紹介していきたいと思います。

関連記事