見出し画像

Tableau Prepの作業を変えるTips集　８　ーExcelのvlookup関数をPrep結合で、Lookup関数ー

Tableau Prepユーザー会

Tableau Prepユーザー会のNakajima2です。
Japan Preppin Data FamメンバーのPrep Tips集をご紹介します。
今回は第8回目、複数テーブルや上下のレコードでデータを参照するための　2つのTipsです。
ちょっと長くなっていますが、お付き合いを。。


Prep Tips （１５）　：　ExcelからPrepへ　ーExcelのvlookup関数をPrep結合でー

＜初心者〜中級者＞　

「全てのExcelユーザーがTableauを利用するように」
Tableau社の目標として上記趣旨が掲げられていた記事を以前見た記憶があります。
ExcelとPrep、それぞれに良さ、作業の限界があります。業務利用として、上手い使い分けが必要と感じています。
筆者は現場担当　ユーザーサイドの視点で、ExcelをRDB的に利用できるvlookup関数が、Prepの結合処理に置き換えることで、業務時間の短縮、引き継ぎなどのメンテナンス作業の効率アップが大幅に計れた経験を何度かしています。
Prep利用メリットの一例だと思いますが、Tableau社の目標　その意味を少し感じた点。

今回は、このExcelのvlookup関数をPrep結合で利用する事例について具体例を用いて説明します。

Excelのvlookup関数

シンプルなデータ利用の例として、果物の商品マスタと販売データを用いて売上の実績を計算するテーブル操作を考えます。

作業事例　：　「商品マスタ」を参照し、「販売データ」から「売上実績」を計算させる

上の例では、Excelワークシート上に利用するテーブルを並べて表記していますが、実際のデータではそれぞれのデーブルを別のワークシート（下記参照）で管理しているものとします。

「商品マスタ」　：　商品の基本情報データ
「販売データ」　：　日々の販売情報が更新されるトランザクションデータ
「売上実績」　：　トランザクションデータを元に集計されたテーブル
vlookupなどの関数が利用されます

「売上実績」テーブルで一覧表を作成する際に「商品マスタ」からデータをvlookup関数で引用します。
[商品コード]をキーとして、[商品名]、[単価]のデータを引用する形で「売上実績」のテーブルにそれぞれのデータを表示しています。Excel上のvlookup関数の表記は次の例などになります。

　[商品名]　：　=VLOOKUP($B2,商品マスタ[#すべて],2)
　[単価]　　：　=VLOOKUP($B2,商品マスタ[#すべて],3)

スプレッドシートであるExcelは、基本的に計算、集計はセル単位で作業を行います。
vlookup関数の場合は、(　) 内の引数は、引き出すテーブルのキー項目（この例では[商品コード]）からのデータを列の左右位置関係を基準に、関数中の引数で位置を指定しデータを抽出してくる仕組みです。
　＊[商品名]はキー項目（B２）から２列目を抽出
　　[単価]はキー項目（B２）から３列目を抽出　　をそれぞれ指示している

Prepの結合で集計テーブルを作る

この果物の売上データ集計でExcelのvlookup関数を利用していた操作を、Prepで対応する例をご紹介します。
それぞれのデーブルを別のワークシートで保管したExcelファイルをデータソースとして利用します。
作成したPrepフローの例は、次の通りです。

結合を利用して作成したPrepフロー例

ここでのポイントは、Excelのvlookup関数をPrepでは結合を使用した点です。
「販売データ」テーブルを、「商品マスタ」のテーブルにある[商品コード]をキーとして結合させて「売上実績」のテーブルを作成しています。
　＊結合方法の詳細は、別のTipsでご紹介します

結合処理の詳細

結合処理により、「売上実績」のテーブル相当するデータが作成されます。
Excelで2回vlookup関数を利用して作成した[商品名]と[単価]のフィールドは、結合の処理で各列の横に並んだ形になっています。
これに「売上実績」の集計に必要な計算フィールド[金額]を追加し、必要なデータが揃いました。

[金額]は、[数量]*[単価]　の計算フィールドで作成
作成された「売上実績」全データ

作成されたデータは、出力して利用できます。
今回は、「販売実績」のExcelファイルとして出力する設定としています。

「販売実績」のExcelファイルを出力

Prepを利用するメリット

多くのPrepユーザーが挙げられている今回例のPrepを利用するメリットは次の通りです。

  1. 結合の利用で、vlookup関数で抽出したいデータが一度に複数のフィールドで実行出来る。フィールド数に応じたvlookup関数の作成や、vlookup関数の引数（データを列の左右位置関係）を考えることなく楽になった。

  2. vlookup関数で参照するテーブルが、共有ファイルサーバーにあるファイルを見に行く場合、関数式が冗長になり後追い確認が大変になる点が無くなった。

  3. 「販売データ」や「商品マスタ」にデータの更新があった際、vlookup関数の書き換え手間が無くなった。参照データがバッチ処理で日々更新されるcsvデータを利用し、Excelワークシートにコピペで毎回張り付けていた作業が無くなり、作業効率が大幅に向上した。

  4. ３項については、Prepの接続するデータソースの更新を利用すること、簡単な作業でデータ更新が図れる様になった（データソースによっては自動更新も可能）。

  5. Tableau Serverや外部クラウドサーバーのデータベースへの接続利用を併用することで、システム的なデータ自動取得（更新）などが行える。

  6. データ数の多い（レコード数が万単位以上など）場合は、Excelでの処理が大幅に遅くなるケースがあり、Prepでの処理に業務負荷低減のメリットを感じる。

まとめ

今回の様なデータ量が少ない事例では、使い慣れたExcelで作業した方が早く作業が完了する方も多いと思います。
Excelでは、データ量が多くなるとパソコンの性能に依存して処理速度が極端に遅くなったり、引継ぎ作業で前任者のExcelファイル中身を確認しても計算式が冗長でない様理解に時間を要する　という話しを良く耳にします。
Prepを使用出来る環境にある方は、この様なケースで一度Prepの利用を検討してみてはいかがでしょうか。
作業時間が減り、夕方早く退社出来る　なんて経験が出来るかもしれませんよ。

Prep Tips （１５）　：　Lookup関数　Tableau prepでのLookup使い方

＜初心者〜中級者＞　

Lookup関数の利用方法をExcelとPrepで比較すると、概念的に違いがあります。
Excelの利用に慣れた方、特に前述のvlookup関数の恩恵を享受されている方ほど、Prep（Tableau）のLookup関数が理解しづらいかもしれません。
その違いを、少しまとめてみました。

Excelの Lookup関数

Excelではキーとなる項目からデータの値を検索・参照する際にLookup関数を利用します。ベクトル形式と配列形式で値を検索する方法がありますが、一般的には配列方式での利用が多いと思われます。
詳しくは、Microsoftのサポートページをご参照ください。

配列形式には、次の2種があります。

  1. vlookup　：　テーブルの左端列または配列内の特定の値を検索し、テーブルまたは配列内の指定した列から同じ行の値を返す

  2. hlookup　：　テーブルの上端行または配列内の特定の値を検索し、テーブルまたは配列内の指定した行から同じ列の値を返す

  3. xlookup　：　範囲または配列を検索し、最初に見つかった一致に対応する項目を返す。近年、ExcelのSpill機能の実装により大きな範囲で一気に検索されたデータを表示できる様になった

Excelでの利用は、主にvlookup関数

多くの方は、vlookup関数を利用されているのでは？　と思います。
詳細は、上記のサポートページをご覧頂くとして、vlookup関数の利用概念は下記の様になります。

　[商品名]　を「商品マスタ」（上のテーブル）から参照する例

前述のPrep Tips （１５）で利用した例になりますが、次の関数で[商品名]のフィールドをvlookup関数で検索しています。

　　　　　　　　　=VLOOKUP($B2,商品マスタ[#すべて],2)

[商品コード]をキー項目として利用し、「商品マスタ」のテーブルにある[商品コード]から２つ目にある列の値を参照してね　という関数になります。
この作業で、「売上実績」のテーブルにある[商品名]のフィールドに果物の名前が表示される様になりました。横方向（列）に対する処理ですね。


Prep のLookup関数

Prepのlookup関数は、Excelのhlookup関数の利用方法に近いものになります。
縦方向（行）に対する処理になりなます。上下の位置関係から値を参照しますが、Prepの方が位置関係に柔軟さがあります。

TableauでLookup関数は表計算関数で扱われる

Prepにおいては、Lookup関数は表計算関数に区分される関数です。ORDERBYなど並べ替えの関数と合わせて利用されます（こちらの記事もご参照ください）。

Prep（Tableau）は、SQLを用いたデータベース処理で動作しているため、基本的に同一行（レコード）にあるデータを、横に並んでいる列（フィールド）のデータ通しで計算などの比較しています。
今回の事例のように、フィールド内の縦位置にあるデータとの比較を行う際は、lookup関数などを用いて横に参照した新たな列（フィールド）を作成し、同一レコードにある左右のフィールドの比較処理が行えます。

具体的なlookup関数の記述は、次の通りです。
Preppin Data 2024W18のソースデータ(San Diego Climate)から、最高気温の前月との差を算出させる例を用います。

前月の気温を当月の右側列に並べた例

　{ ORDERBY [Source Row Number] ASC :  LOOKUP([最高気温 °C] , -1)}

[Source Row Number]で昇順に並び替え（これで縦方向の参照順を固定させる）、[最高気温 °C]のフィールドからひとつ上の行にある値を参照してね　という関数になります。
lookup関数のオフセット値となる -１ の部分は、数値を変えることが出来ます。+3 であれば下の3行目にある値を参照する様になります。

これで、求めたい最高気温の前月との差を、次の計算フィールドで表示出来ました。
　　　　[前月の最高気温]-[最高気温 °C]

同一行（レコード）にあるデータで計算

UIを利用し、簡単に[温度差]を求める

lookup関数の基本動作を説明するために、上記では[前月の最高気温]のフィールドを作成し、その差を計算フィールドで作成する流れで説明しました。
Prepさん、実は優れたUIも持っており、以下の操作で簡単に [温度差] を簡単に算出してくれます。

データ加工を行いたいフィールド（今回は [最高気温 °C] ）の３点リーダーから
計算フィールドの作成　ー　次との差　を選択する
表示されたUI画面に「並び替え」と「次の方法で計算」、
「次との差」の必要項目を入力
入力後の算出結果　前述のものと同じ結果です

UI画面を見てお気付きの方も多いと思いますが、設定する各項は次の関数指定に相当しています

  • グループ化　　　：　PARTITION

  • 並び替え　　　　：　ORDERBY

  • 次の方法で計算　：　参照したいフィールド（今回は[最高気温 °C] ）

  • 次との差　　　　：　オフセット値（参照する上の行、下の行を示す数値）

このUIでの計算式は、自動で下記の内容が記載されていました。

　　{ ORDERBY [Source Row Number] ASC :  LOOKUP([最高気温 °C] , 0)}  -  
　　{ ORDERBY [Source Row Number] ASC :  LOOKUP([最高気温 °C] , -1)}

まとめ

lookup関数は、Version 2023.2 で実装された関数です。
TableauDesktopでは、簡易表計算などを用い「差」を利用して上記事例のような値を求めることが出来ますが、Prepにはそれまで同種の関数がなく多くのユーザーに実装が期待されていたものだったそうです。
縦方向上下のデータ比較がPrepのデータ前処理でも柔軟に行えることとなり、またDesktopの簡易表計算に似たUIメニューからの処理もできる様になりました。

便利な関数として、利用機会があった際は是非動作の仕組みも思い出しつつ使ってみてください。


この記事が気に入ったらサポートをしてみませんか？