DataSaber：Ord9
個人的に一番難しかったOrd9を最終試験へ向けて復習しておこうと思う。
【Q1】
地域、カテゴリ、オーダー年ごとの売上実績と予算を対比して
家電カテゴリにおいて全ての年で売上目標を達成していない地域を教えて下さい。またその未達成度合いはどれくらいでしょうか。
50%にも満たない年があるようであれば教えて下さい。
なお予算シートは作成担当者がうっかりしており家電は昨年までの名称「テクノロジー」のままです。
また目標年月を更新せず目標金額だけ更新したため予算のExcelの年月は+2年して比較してください。
●前準備
予算ファイルを取り込みデータをユニオン
予算項目をピポット
予算月の型を日付型に変更
別名設定：製品カテゴリの「テクノロジー」→「家電」に変更
計算式作成
データブレンド
列：年（オーダー日）不連続 売上（合計）
行：カテゴリ 地域
詳細：予算額（合計） ※設定登録はせず直接詳細へ入れる
リファレンスライン設定
色：SUM([予算+ (サンプル - スーパーストア - 予算)].[予算額])<=SUM([売上])
【Q2】
各都道府県がサブカテゴリ「椅子」の利益に及ぼした貢献度を知りたいです。都道府県を利益合計を多い順に並べ、積み上がっていく状況と総計を表して下さい。
Q2-1：累計利益金額が最大となるのはどの都道府県ですか？
列：都道府県
行：利益（合計）→昇順に並び替え
フィルター：サブカテゴリ=椅子
利益（合計）→簡易表計算（累計）
色：WINDOW_MAX(RUNNING_SUM(SUM([利益])))=RUNNING_SUM(SUM([利益]))
Q2-2:Q2-1の時の利益はいくらでしょうか？
ラベル表示：最大値
Q2-3:利益に対して貢献できなかったと都道県に地域制はありますか？
色とラベルを外す
色：地域→属性：
グラフ：ガントチャート
サイズ：利益（合計）→-Sum(利益）
都道府県：並べ替え→フィールド（昇順）
【Q3】
四半期ごとにサブカテゴリの売上ランキングの推移をみたとき
Q3-1：最も長い間、継続して1位を獲得していたのはどのサブカテゴリですか？
列：四半期（オーダ日）連続
行：売上（合計）→簡易表計算ランク（次を使用して計算：サブカテゴリ）
→列の軸の編集：反転
列：四半期（オーダ日）連続→不連続
行：売上（合計）→不連続
グラフ→棒グラフ 列：MIN(1) 行の軸の編集→範囲：固定0、1
→画面表示を画面に合わせる
軸の非表示 サイズ：大きくする 色：自動→白に変更
Q3-2:そのサブカテゴリがもっともランクを下げたのは何位か？
Q3-3:そのサブカテゴリを含め同じカテゴリに所属しているサブカテゴリは全期間の内何回1位を獲得したか？
【Q4】
利益Top10、Worst10の顧客の売上を比較し、利益でTop1の金額を出している顧客の売上とそれ以外の顧客それぞれの売上の差を見せて下さい。
Q4-1：利益Top1の顧客より高い売上を出している顧客はいますか？
列：利益（合計）
行：顧客名
利益Top10とWorst10のセットを作成→Top10とWorst10のセットの結合作成 フィルター：セット結合 行：利益Top10
Q4-2:利益Worst10の顧客の中で売上金額の差が利益Top1と比較し
て最も小さい顧客との金額の差はいくらですか？
列：売上（合計）→複製
売上（合計）→簡易表計算（差）基準：最初
利益の差がが最も低い人のグラフを右クリック→マークラベル：常に表示
Q4-3:Q$-2の顧客は利益Worst何位ですか？
列：利益（合計）→複製
簡易表計算：ランク
不連続→行の顧客の前に移動
表計算：ペイン（下） 昇順
【Q5】
年四半期の売上が最高記録を更新したのは何回ありますか？売上が最初に発生した四半期は回数から除いて下さい
なお、最高記録を更新したタイミングがわかるよう、その箇所は色を変えて下さい
列：四半期（オーダー日）連続
行：売上（合計）→簡易表計算：累計（最大値）
行：売上（合計）複製→グラフ：円（サイズ変更）
色： INDEX()>1 AND RUNNING_MAX((SUM([売上])))=(SUM([売上]))
※1回目の売上を除外するためINDEX>1
【Q6】
利益率ワースト3のサブカテゴリを除外すると利益率は何％になるか
列：利益率
行：サブカテゴリ
フィルター：サブカテゴリ（除外にチェック）
ダッシュボードのフィルタを設定
【Q7】
サブカテゴリごとの売上と売上の前年比成長率の相関を年ごとに確認したいです。2015年と2016年で前年比割れしてしまったサブカテゴリの傾向を教えて下さい。
役員がモバイルのため横に並べて各年を確認できないたえフィルターで切替るようにしてください
列：年（オーダー日）不連続 売上（合計）
列：売上（合計）→簡易表計算（前年比成長率）
サブカテゴリ→ラベル＆詳細
計算フィールド作成：LOOKUP(MIN(DATEPART('year', [オーダー日])),0)
→0行前のオーダー年の最小値を返す
【Q8】
出荷にかかる平均日数を醜態の推移で見せてください
社の方針として上方管理限界線（UCL：UpperControlLimit）を3σとして管理している
Q8-1：この限界を突破して配送が遅延した週は何回ですか
列：週（オーダー日）連続
行：平均（出荷日数）
アナリティクスペイン：分布（標準偏差）
Q8-2：その週の配送先の市区町村を確認し、問題が起こった原因について調査せよ
列：平均（出荷日数）
行：都道府県 市区町村 色：地域
ダッシュボードのフィルター設定
【Q9】
2016年の四半期単位の売上推移をメーカーごとに比較してください。メーカー名が「s」で終わるメーカーのはどのようなトレンドがありますか？
列：四半期（オーダー日）連続
行：メーカー 売上（合計）
フィルター：オーダー日=2016年
メーカー（ワイルドカード：後方一致（sで終わる）
売上の軸の編集：独立した範囲 ゼロ含めるのチェックを外す
ヘッダー非表示
ラベル：売上（合計）→終点 最終行
【Q10】
地域別売上ランキングの推移を年四半期で見たとき
売上開始当初（2013/1Q）と最終四半期（2016/4Q）でランキングに変動があった地域はどこですか？
列：四半期（オーダ日）連続
行：売上（合計）→簡易表計算ランク（次を使用して計算：地域）
最初と最初のQ以外を全て選択し除外
売上（合計）を複製し、グラフ=円（サイズ変更）
二重軸：軸の同期、軸の書式設定（反転）
地域をラベル表示する
【Q11】
カテゴリ「家電」における週数別の売上をみたとき￥500,000を連続で超えたのは？
Q11/1：最大何週間ですか？
列：週（オーダ日）連続
行：売上（合計）
パラメータ作成：閾値 （￥500000スタート）
計算式作成
閾値判定：SUM([売上])>=[閾値]
週数超過累計：IF [閾値判定] THEN PREVIOUS_VALUE(0)+1 ELSE 0 END
もし売上合計が閾値(今の値は500,000)を超えていたら
前の行の計算に合計売上から閾値(500,000)を引いた数字を足す
週数超過累計をラベルに入れ、最大値/最小値を選択し最大値のみチェック
Q11-2：連続で超えている間￥500,000の超過分を累計したとき最も高い累計金額はいくらですか？
計算式作成
超過週数売上累計：IF [閾値判定] THEN PREVIOUS_VALUE(0)+(SUM([売上])-[閾値]) ELSE 0 END
超過週数売上累計をラベルに入れる
この記事が気に入ったらサポートをしてみませんか？