mamma_dsです。エクセル月報をTableau化したいお話をよく聞きます。エクセルは自由すぎますね。実際に自由すぎるエクセルをTableau化した事例（多少変えてますが）をご紹介していきたいと考えております。
この記事でお伝えする内容
今月のTableau月報 -一般経費 予実管理 データ処理 Tableau Prep編-
2024年8月号は、一般経費の予実管理です。今月はデータ処理Tableau Prep(以下Prep）編となります。来月はダッシュボード作成編を行います。予実管理はどの会社でも実施されていると思います。予算、実績のデータはエクセル管理や基幹システム管理といろいろなパターンがあると思います。今回は、予算はエクセル管理、実績は基幹システムからエクセルダウンロードしている想定で予実管理のTableau化を行います。
サンプルデータはChatGPTにて生成しております。ちょっと違和感があるデータになっていますが、気にしないようにお願いします。
まだ、Prepを活用されたことが無い方はRintaro Sugimuraさんの「はじめてのTableau Prep」をご覧になってみてください。
今月のTableau月報のポイント
・予算管理エクセルをPrepに読み込ませる
・予実管理のデータフォーマット（横持ちと縦持ち）
サンプルデータ、サンプルフローのダウンロード
サンプルデータ ダウンロード
＊ダウンロードできない時：右クリック→名前を付けてリンク先保存
サンプルフローを開くとアラートが発生します。これは、保存先のパスが無いためです。TableauやPrepは絶対パス（Cドライブから指定）のためです。出力先のパスを修正してください。読み込みも同様ですが、tlfxファイル（パッケージドフロー：データが含まれている）のため読み込みはアラートが発生しません。
サンプルデータの解説
予算データ
・予算2023年.xlsxと予算2024年.xlsxの２ファイルがあります。
・部署（セル結合）、科目と年月のマトリックスになっていす。
・小計と総計がエクセル上で計算されています。
実績データ
・2023年、2024年の実績が１つのシートにあります。基幹システムからエクセルファイルをダウンロードしたイメージです。
・カラムとして年月日（予算は年月）、部署、費目（予算にはなし）、科目、氏名（予算には無し）、実績額
予実管理データ処理（Prep）
今回のサンプルデータでは、実績は素直に読み込むことができると思います。問題は予算です。余分な情報（合計）を放置しておくとTableauでの集計がおかしくなります。余計なデータは削除する必要があります。もちろんTableauでフィルターをかけることもできますが、データソースを共有することもあるとおもいますので事前にキレイにしておきましょう。
接続-テーブルで複数ファイルを一括で読み込む
Prepの読み込みはファイル、シートのフィルターを設定することによって複数のファイル、シートを読み込むことができます。「*」はワイルドワードと言って、任意の文字となります。
「予算*.xlsx」と指定すると「予算2023.xlsx」、「予算2024.xlsx」を一括で読み込んでくれます。Prepの超便利機能ですね。
エクセル帳票をPrepに読み込む
案１）Prepで頑張ってデータ整形
まずは、「部署」のセル結合です。このままPrepで読み込むと、「部署」にNULLが大量に発生します。これは、「営業部」は5行ありますが、1行だけ「営業部」になり、残りの4行はNULLになるためです。
「データーインタープリター」のチェックを入れるとセル結合を分解して、一行一行に「部署」が入るようになります。「データインタープリター」すごく便利ですが万能ではありません。「データーインタープリター」でも読めないときは、別の方法を考える必要があります。まだ部署にNULLが残っています。続いて、「合計」や「総計」を除外することでNULLを削除してみます。
エクセルの不要な行を水色で色付けしました。「合計」や「総計」が不要な行となります。Tableauで集計時に合計や総計まで含めて集計（合計や平均）がされてしまいます。もちろん、Tableauでフィルターしても良いのですが、データはなるべく事前にきれいにした方が間違いが無くて良いと思います。
どこを削除（除外）するかはエクセルを良く確認しながら行います。「科目」の「合計」と「総計」を除外するとキレイに余計なデータを削除することができます。
また、P列の「合計」も余分なので削除しておきます。
「科目」の「総計」で右クリックするとメニューがでてきて「除外」することができます。
ご参考
・【Prepスキルアップ】クリーニングステップ Tableau Prepユーザー会
・Tableau Prep Builder 使い方(4) 〜データのクリーニング データ分析＆クラウド関連ブログ
案２）エクセルで頑張ってデータ整形
エクセルでも余分な合計や総計を省くことができます。また、セル結合している「部署」も1行1行に「部署」を記載できます。コピーではなく、別のシートから参照することによって数値や文字が変更されても大丈夫です。
エクセルの参照がわからない方は「[Excel] 他のシートのセルを参照する方法を教えてください。」をご覧ください。
請求書のようなフォーマットをPrepで読み込ませたことがあります。Prepでは修正が大変でした。しかも、余分なデータを削除できたのかちょっとわかりにくいです。
エクセルの別シートで参照による一覧表を作成して簡単にPrepに読み込ませることができました。
エクセルで頑張るか、Prepで頑張るか考えてみてください。
もっとも重要な事！ それはエクセル帳票のフォーマットを統一すること
エクセルは自由にフォーマットを変えることができます。また、人は多少フォーマットが変わっても読み解くことができますが、TableauやPrepは柔軟にフォーマット変更に対応するのが難しいです。当たり前の話ですが、エクセルのフォーマットを統一することが重要になります。注意書きをするときはセルではなく図形を挿入して図形に注意書きを書いておきましょう。図形はTableau/Prepは読み取らないので影響がありません。
しかし、統一されてないエクセル帳票を大量に読み込みたいことがあります。エクセルでもPrepでも大変手間がかかります。そんな場合、私はPythonでデータをPrepで読み込める程度まで整形します。
データの縦持ちと横持ち
データの縦持ち、横持ちというキーワードを聞いたことがありますでしょうか？データのフォーマットを示す重要な考え方になります。
データ構造（縦持ちと横持ち）とTableauのパフォーマンスの関係
今回のサンプルデータでは
予算：横持ち
実績：縦持ち
となります。単純なデータだと横持ち、縦持ちははっきりします。しかし、メジャーが複数あるデータだといろいろな持ち方ができます。そんな時は縦に長くなると「縦持ち」くらいと考えていただければ良いと思います。
Tableauで扱うときは何かと縦持ちにした方が扱いやすいので、まずは縦持ちにすることを考えて可視化しやすいようにデータを調整していきます。
今回は予算を縦持ちにピボット（変換）後、「金額」、「予算額」、「実績額」の３つのカラムを作成しました。「金額」は「予算額」、「実績額」と重複しています。「予算/実績」でどちらの金額なのかわかるようになっています。
「金額」があれば、データとしては十分なのですが、可視化するときに「予算額」と「実績額」が分かれていた方が都合がいい場合もあります。来月の可視化で確認したいと思います。では、実際にPrepでデータ処理を行ってみましょう。
予算のデータを縦持ちに変換（ピボット）して実績とユニオン
ピボット変換で横持ちから縦持ちに変換します。予算のように毎年追加されていくデータは「ワイルドカード検索を使用してピボットする」を活用しましょう。
「20」をキーワードに前方一致すると2023/04~2025/03のカラムが選択できます。
このように縦持ちにピボットできました。あとは、年月を日付型に変更しておきましょう。
「予算/実績」のフィールドを追加して、’予算’を記述しておきます。これは、金額が予算なのか実績なのか判別するためになります。実績にも同じように「予算/実績」のフィールドを追加して’実績’を記述しておきます。
「金額」をコピーして「予算額」にフィールド名を変更しておきます。先ほども書きましたが、「予算額」と「実績額」は分けておいた方が今回は可視化しやすいと思います。Tableau側で工夫すれば、「金額」１つでも大丈夫です。
実績も同様に処理をします。
予算、実績、それぞれのデータが整ったらユニオンします。フィールド名はそろえておく必要があります。
今回は以下のフィールド名は片方のみ存在します。
予算のみ：予算額
実績のみ：年月日、費目、氏名、実績額
最後にピボットするとNull行が発生することがありますので、不要な行は除外しておきましょう。
出力する前に「Tableau Desktopでプレビュー」で確認してみましょう。右クリックでメニューが出てきます。
エクセルの総計値とぴったり一致しました。キレイにデータ処理できているようです。Prepでいろいろデータを処理した時は思わぬミスをしている場合もありますので、検算を心掛けた方がよいと思います。Tableauで可視化した年度が「2024年度」、「2025年度」となっています。日本と米国だと1年ずれるようですね。日本は開始年、米国は終了年のようです。
横持ちの結果を確認してみる
予算と実績を結合するとどうなるか検証をしてみます。予算と実績の共通である「科目」と「年月」で結合するとよさそうですが、これがとんでもない数値になってしまいます。
横持ちと縦持ちの予算額を集計するとまったく違う値になりました。これは横持ちの結合でよくやってしまうミスです。結合はキーのすべての組み合わせを作りますので、同じキーの値がお互いに重複していると行数が増えてしまい合計値が何倍にもなってしまいます。この結合を「多対多」や「ｎ対ｎ」と言います。予算と実績を結合するときは、事前に集計をして「1対多」や「1対ｎ」になるように調整します。しかし、その調整によってデータが荒くなってしまうので、ユニオンをして縦持ちにした方が良いと思います。
月報あるある 自動でデータ更新したい
エクセル月報は多少の手作業があることが多いので1回/月の更新かと思います。Prepは自動実行させることができます。自動化すると毎日でもデータ更新が可能となります。Tableau Server管理者に怒られそうですが10分毎にPrepでデータ更新していたこともあります。
Tableau Prep Conductor(Tableau Server / Cloud): Tableau Data Managementをお持ちだとスケジュール実行可能
Tableau Prep Builder:tableau-prep-cli.bat とタスクスケジューラ―で自動実行可能
今回はTableau Prep Builderの自動実行についてです。macはよくわからずwindowsのみです。最近の記事がなく、ちょっと古い記事のリンクです。現在では、Tableau Server用の認証用jsonフォーマットもありますので公式ヘルプもご覧ください。
Tableau Prepのワークフロー実行を自動化する
Tableau公式ヘルプ コマンドラインからフロー出力ファイルを更新
コマンドラインからPrepを実行できるようになると複数のフローを順番に実行できるのもメリットの一つです。その場合バッチファイルのフロー実行コマンドの前にcall をつけておきます。
call "C:\Program Files\Tableau\Tableau Prep Builder 2020.1\scripts\tableau-prep-cli.bat" -c "C:\PrepTest\testprep.json" -t "C:\PrepTest\testprep.tfl"また、Prepにパラメータを渡すこともできます。公式ヘルプを確認してみてください。
参考資料一覧
はじめてのTableau Prep Rintaro Sugimura
【Prepスキルアップ】クリーニングステップ Tableau Prepユーザー会
Tableau Prep Builder 使い方(4) 〜データのクリーニング データ分析＆クラウド関連ブログ
データ構造（縦持ちと横持ち）とTableauのパフォーマンスの関係 Masakazu Fukae
【SQL】テーブル結合で行を増殖させないポイント okinawa
Tableau Prepのワークフロー実行を自動化する KCME Tech Blog
Tableau公式ヘルプ コマンドラインからフロー出力ファイルを更新
ご参考：ChatGPTでのサンプルデータ作成
今回のサンプルデータはChatGPTで作成しました。かなり便利です。ただし、今回はエクセルでデータ加工・修正もかなり行いました。なかなか言葉でデータを定義するのも大変ですね。とはいえ、サンプルデータを作成したいときはおすすめです。
サンプルCSVデータ作成
実際はサンプルデータを出力するpythonを生成して実行しているようです。pythonコードも出力できるので、コードを手直ししても良いかもしれません。
