Preppin Data Practice #02 （24年5月 - 2024: Week 17 Budget vs Actuals Part 2 -）
Tableau Prepユーザー会のNakajima2です。
Japan Preppin Data Fam 第2回目のPreppin Data勉強会、24年5月のYouTube動画公開は、2024W17 の課題にチャレンジです。
24年4月にPreppin Dataで出題された全４題（W14 〜17、下表参照）は、2週ごとに同じデータソースからの違う分析意図のデータ処理を行う課題でした。
今回は、実務でも扱う機会の多い予算と実績の対比、会計的な報告をする際の数値の取り扱い、特に小数点以下の数値の丸め方 などにも注視したW17を取り上げています。
以下、課題の内容、対応のポイント、参加者の解答例をご紹介します。
Preppin Data勉強会の配信動画（YouTube）も、以下リンク先からご覧ください。
公開した動画はこちらです
１）課題の内容
Preppin Dataの課題は、下記を参照ください。
https://preppindata.blogspot.com/2024/04/2024-week-17-budget-vs-actuals-part-2.html
・出題の背景
実務でもよく扱う、予算と実績を対比する課題です。実践的で良い内容です。
スーパーバイツ社の予算と実績を比較する課題。
同社は、毎月の販売実際を追跡する作業を開始した2023年に焦点が当たっています。年間を通じて予算に対して実際の支出実績が増加し、業績に悪い影響を与えた主要な分野を見つけるもの。
同社のCEOは、2023年全体の販売実績を向上させることが出来ず、在庫に投資しすぎたのではないかと疑っています。
疑惑が真実であることが判明した場合、扱っている製品の分野を最適化し、同社の戦略目標に沿って全体的な財務パフォーマンスを向上させるための実用的な洞察を提供したいと考えています。
・データソース、Outputデータ
Inputのデータソースは、Excelファイル。予算データは、「Budget」ワークシートに記載されています。
実績データは、各月 違うワークシートに入っています。月毎に集計された１レコードのみのデータです。
Prep処理後のOutputデータは、５フィールド、12行（ヘッダ除く）となります。
・Requirements（要求事項のポイント）
今回与えられたデータソースは、エクセルのファイルで複数のワークシートで構成されています。次の通りです。
・年単位で作成された予算の値
・月単位で集計された支出の実績データ（月ごとに別のワークシート）
実務で、よくありそうなデータソースの形です。
今回の課題の要件は、次の通り。
・エクセル内の月ごとのワークシートをひとつのテーブルにまとめる。
・年単位で予算に対して支出実績が上回っていた、業績に悪い影響の主要な
分野をのみを残す（支出実績が下回っている分野を除外する）
・残った分野に対して、予算と実績の支出差を月単位で計算すること
ここで、月単位の計算では、予算の値は年単位で提示されているため、月単位の計算には年間を通じて均等に分ける必要があります。
また、集計する値は、最も近い整数に丸めて提示する指示があります。
最終的に、集計した数値を利用し、月毎に予算に対して支出実績が最も上回る分野を見つけて、最終データを出力します。
２）対応のポイント
Prepでの前処理作業における、技術的なポイント（上記 Requirementsに対応）は次の通りです。
Excelワークシートの集約
予算と実績の比較 年単位で計算し支出実績が上回っていた分野を削除
月単位の予算と実績の比較 ランク付けし月ごとで最も支出実績が上回っていた分野を抽出
数値の丸め方、フロー処理上の丸める作業をするタイミング
３）参加者の解答例、Tipsなど
上記 ２）ポイントに対応した参加者の回答をご紹介します。
Excelワークシートの集約（ユニオン）
各月のActual データを集約する。
PCで利用できるアプリと、Tableau Cloudで利用するWEB版でワイルドカード ユニオンの利用可否が異なっている。両者の差異は、次の通りです。
PCアプリ
ワイルドカード ユニオンが利用可能。データ接続の「テーブル」タブから、ワークシートフィルターで名称が一致するワークシート名の部分をし指定してユニオンを行う。下の事例では、「Month*」で部分指定を行っている。
Cloud WEB版
ワイルドカード ユニオンが利用出来ない。下のように、ここのワークシートをユニオンする。
個別のユニオンで作成される[Table Name] を利用して、[Month] のフィールドを作成する。
TRIM( SPLIT( [Table Names], "-", -1 ) )
ユニオンが二段になると、Table Name の名前の長さが段で異なってしまうため、Sprit作業を段ごとに実施して月の名前を求める必要が出てきます。
予算と実績の比較 年単位で計算し支出実績が上回っていた分野を削除
年単位の支出実績差の算出は、今回の課題においては、年単位の差分計算を行わなくても、回答が得られるものとなっています。
参加者の中で、年単位の計算を実施されていない方も多い状況でした。
Preppin Dataで回答Solutionとして提示されていたフローでは、月単位で集計されている[Actual] のデータを[Category] 単位に集計し、年単位の[Actual] のデータを算出した上で、年単位の予算と実績の比較を行っています。
Join3で結合処理を実施後、実績＞予算の計算を行い、業績に悪い影響の主要な分野のみを残す（今回は、４つのCategory）ことになります。
Tips（結合フィールドの不等号利用）
予算と実績の比較は、年単位と月単位をそれぞれ別に実施する方法が一般的ですが、結合処理時に両者を一気に実施する方法があります。
Mr もりた が行った処理方法です。具体的には、次の通りです。
①Inner結合の処理内容 結合句は[Category] ： これは一般的方法と同じ
②年単位データ抽出を[Annual Spending] > [Annual Budget]の結合処理で
③月単位のデータ抽出を[Actual Spending] > [Budget]の結合処理で
複数の結合句での結合処理として実現しています。下図を参照ください。
③月単位のデータ抽出を３つの結合処理で実現
この結合処理にて、その後のクリーニングステップでの抽出（フィルタなどを利用）する処理は実施してなく、フロー全体の処理数も減らすことが実現出来ています。
別途、Prep Tips：結合フィールドの不等号利用 としても記事掲載しています。こちらも、ご参照ください。
月単位の予算と実績の比較 ランク付けし月ごとで最も支出実績が上回っていた分野を抽出
この算出方法は、大きく2つに利用者が分かれることとなりました。
（１）Fixed関数の利用
各月で最も支出実績が上回った分野を算出するため、Fixedで[Month]を指定して、Max([Difference])で最大値を求める方法。
[Work]フィールド（計算用の仮フィールド）を作成
{Fixed [Month] : Max([Difference])}
Fixed関数利用後に、フィルターに計算式を入れて該当分野のみを抽出。
Filterで設定（判定式を利用）
[Difference] = [Work]
（２）Rank関数（PARTITION）の利用
各月で最も支出実績が上回った分野を算出を、Rank関数で最も大きい1位のランク付けから選択する方法です。
{ PARTITION [Month] : { ORDERBY [Difference]DESC : RANK()}}
数値の丸め方、フロー処理上の丸める作業をするタイミング
今回の課題フローにおいては、上記の年単位、および月単位の予算と実績の比較を行う前にRound関数を利用した数値を丸める処理が必要になります。
予算と実績の比較後に数値を丸めると、算出結果で数値が「一の位」がズレる結果になるデータが散見されるようになります。
誤差範囲ではありますが、実務などでは経理処理などに用いるデータではこの手の数値差も合わせ込む必要が出てくるケースがあると思われます。
会社ごとの数値を丸める（処理する）タイミングなどがあり、その方針に合わせることが必要ですが、今回の課題では数値の丸める作業をどのタイミングで行うかを考慮することも重要視されていた点を、Requirementsの要求事項として記載しておきます。
４）その他
文字列の修正
”1008000 SameAsLastYear” などのデータで、数値部分を取り出す文字列の修正が課題としてありました。
Prepの標準機能にある、クリーニング の 文字を削除 を利用して修正することが出来ます。
REGEXP_REPLACE([Budget], '[[:alpha:]]', '')
新しい行
参加者のRiekoさんが、今回の課題で「新しい行」を作成した作業を行っています。Prepで昨年導入された便利な機能です。YouTubeで公開中のアーカイブでRiekoさんからポイントの説明があります、詳細はアーカイブをご覧ください。
１Stepフロー
課題のフロー作成からは少しマニアックな面もありますが、アーカイブの中ではNakajima2からピボットを利用した１Stepフローの説明をしています。
本稿では、詳細の記述は致しませんが、基本的な考え方を用いて作業の流れを考えながらフローを作成する点は、１Stepフローが基本知識の習得としての良い練習になると感じています。
１Stepフローについては、機会があれば基本知識を整理しながらの説明をする場を別途持てればと思っています。
５）おわりに
今回で2回目の勉強会 公開配信（ビデオ解説）になります。
事前の相談なしで録画を行っていますが、参加者のフロー作成結果が完全に一致することなく、いろいろな手法が使われていました。
今回も、他の方の方法を知り、技の引き出しを増やす、自分の方法よりも効率的な良い方法を知ることが出来た有意義な場になったと感じています。
毎回同じ感想になってしまいますが、Practice（練習する）ことの大切さ を、改めて感じられた時間となりました。
Preppin Data勉強会（Japan Preppin Data Fam）では、新規参加者を募集しています。
初心者の方も大歓迎。Tableau Prepが使い慣れた中級以上の方も、目から鱗いっぱいありますので、よろしければご参加を検討ください。
参加希望の方は、下記までメールご連絡ください。
Tableau Prepユーザー会 ： tableauprep.usergroup@gmail.com
この記事が気に入ったらサポートをしてみませんか？