最終試験に向けて復習してみた -Ord9HandsON Advanced Ⅱ-
DATA Saber Boot Camp Week7 "HandsOn - Advanced II" - YouTube
Q1：サブカテゴリ「テーブル」の売上は、オーダー年の2013年と比べて2016年は何%増加していますか？
※動画の時間 8:00-12:20
・合計(売上)を見る⇒サブカテゴリをテーブルでフィルタ⇒合計(売上)を「前年比の差」で簡易計算⇒ 表計算の編集で基準を最初の2013年にする
Q2:各都道府県がサブカテゴリ「椅子」の利益に及ぼした貢献度を知りたいです。都道府県を利益合計を多い順に並べ、積みあがっていく状況と総計を表してください。
Q2-1:累計利益金額が最大となるのはどの都道府県ですか？
Q2-2:Q2-1の時の利益はいくらでしょうか？
Q2-3:利益に対して貢献できなかった都道府県に地域特性はありますか？
動画の時間：12:20-22:00
・合計(利益)を見る⇒昇順にして最大利益を確認⇒合計(利益)を簡易表計算で累計にする※累計だと貢献が少ない（利益が小さい県）が利益を多く出して見えてしまうため、ウォーターフォールチャートで表現する⇒ガントチャートにして、累計の頂点のみにする⇒マークに ｰ[利益] を作成し、サイズに入れると、ウォーターフォールチャートになる。⇒最後にアナリティクスで合計値を入れると、累計の最終合計が表の左に表示される⇒地域性をみたいので、地域をマークの色に入れると、表計算がぶれるため、「属性」にすることで、計算の邪魔をしないようにする
メモ：属性ってよくわからないけど、下記みたいなこと
属性は〇〇に対して■■(属性)がいくつあるか
・値が1つ→その値
・値が1つ以上→＊(アスタリスク)
・NULLは無視される
属性になるとディメンションでなくなるため、”区切らず”に”ラベル”になる。
例えば、カテゴリ、サブカテゴリごとの売上を出して、サブカテゴリを属性にすると、サブカテゴリが＊になる。
これは、カテゴリに対してサブカテゴリが複数あるから。
同じように、カテゴリを属性にしてみると、変わらない。ってこと？だと思う。
あと、値の一致を確認したり、グラフを繋げたりする時に使う。 ←個人的にこれがいまだに腹落ちしていないけど…
Q3:年四半期ごとサブカテゴリの売上ランキングの推移をみたとき、
Q3-1：最も長い間、継続して1位を獲得していたのはどのサブカテゴリでしょうか？
Q3-2：そのサブカテゴリがもっともランキングを下げたときは何位でしたか？
Q3-3：そのサブカテゴリ含め、同じカテゴリに所属しているサブカテゴリは全期間の内何回位を獲得しましたか？
動画の時間：22:00-33:00
・合計(売上)と四半期(オーダー日)をみる
⇒サブカテゴリをマークの色にいれる
⇒合計(売上)を簡易表計算でランクにする※表計算が年四半期の表(横)で計算されているため、特定のディメンションで、サブカテゴリごとの表計算とする
⇒軸を反転させて、ランク順位の降順にする
⇒合計(売上)と四半期(オーダー日)を不連続にし、棒グラフにする※棒グラフにするが連続的なビジュアルじゃないため、MIN(1)を列に入れる※MIN関数は( )内の最小値を返す関数
⇒軸の編集「範囲」を「固定」にし、最小値0、最大値1とすると対称になる※これで各サブカテゴリで連続1位をとっているのがどれかわかる
⇒また、カテゴリについてみたいので、カテゴリを属性にしてマークの色にいれる
Q4: 年利益TOP10、WORST10の顧客の売上を比較し、利益でTOP1の金額を出している顧客の売上とそれ以外の顧客それぞれの売上の差を見せてください。
Q4-1：利益TOP1の顧客より高い売上を出している顧客はいますか？
Q4-2：利益WORST10の顧客の中で売上金額の差が利益TOP1と比較してもっとも小さい顧客との金差はいくらですか？
Q4-3：Q4-2の顧客は利益WORST何位でしたか？
動画の時間：33:00ｰ48:15
・合計(利益)と顧客名を行列にいれる
⇒フィルターに顧客名を入れて、条件で利益TOP10にする
⇒セットの作成で、「顧客名_利益TOP10」を作成
⇒同様に、「顧客名_利益WORST10」を作成
※フィルターはディメンションでANDであるため、TOPとWORSTの両方をフィルタに入れることはできないため、結合セットの作成で「顧客名_TOP&WORST10」を作成
⇒フィルタにいれると、顧客のTOPとWORST10がでる
⇒グラフを見やすくするために、顧客名_利益TOP10（WORST10でも可）を行にいれヘッダ表示にし、IN/OUTをそれぞれ利益TOP10/WORST10と標記
⇒合計(売上)を表計算で差にし、計算の編集で表下で、計算基準を「最初」にすることで、TOP1利益者の売上との差をみることができる
Q5:年四半期の売上が最高記録を更新したのは何回ありますか？売上が最初に発生した四半期は回数から除いて下さい。なお、最高記録を更新したタイミングがわかるよう、その箇所は色を変えて表現してください。
動画時間：48:15-54:45
・合計(売上)と四半期(オーダー日)を行列にいれる
⇒合計(売上)を簡易表計算で累計にし、計算を表(横)で「最大」を選択し、累計で過去と比べて最大売上を更新するタイミングがわかるようになる
⇒表(横)の最大で表計算した累計の合計(売上)を行にもう1つ追加し、「〇」とし、更新したタイミングに色をつける※RUNNING_MAX(SUM([売上]))=SUM([売上])
⇒最初の四半期(2013Q1)も色がつくため、
INDEX()>1 AND RUNNING_MAX(RUNNING_MAX(SUM([売上])))=SUM([売上])とし、行数が1以上（つまりは一番はじめの値以外）で表内の売上合計の累積最大値が売上合計と一致したら真あとは偽とする
⇒二重軸・軸同期し、片方の軸のヘッダ表示をOFFにする
Q6: 利益率ワースト3のサブカテゴリを除外すると、利益率は何%になりますか？
動画時間：54:45ｰ1:04:30-/1:12:30
①利益率:SUM([利益])/SUM([売上])とサブカテゴリを行列に入れる
⇒利益率を降順にする
②利益率(全体)：{SUM([利益])/SUM([利益])}として、利益率と利益率(全体)の複合グラフにする
⇒メジャーネームが行にできるので、マークの色に入れ、またサイズに入れると利益率と利益率(全体)で色とサイズ違いができる
⇒ラベルを表示する
⇒ツールバーの分析からスタックマークをOFFにすると、2つのグラフが積みあがらずに0から並ぶ
・ダッシュボードで、①と②をシートにいれる
⇒アクションで、①のグラフの選択したサブカテゴリでフィルターができるようにする
⇒②のシートで、フィルターでアクションができているので、編集で除去にする
③サブカテゴリ選択：サブカテゴリをフィルタに入れ、適当なサブカテゴリを選択し、セットの作成をする
⇒利益率と利益率(全体)を入れる
⇒サブカテゴリ選択のセットを色に入れると二色IN/OUTになる
⇒必要なものは除外したもの（=選択していないもの）になるので、OUTのみ残すために、INは非表示にする
⇒メジャーネームをサイズにもってくる・ダッシュボードに入れる⇒アクションでセットアクションを選択し、「選択」したときに、「選択サブカテゴリ」のセットが、除外されるようにする※②のフィルタに③も勝手に入るため、除外しなおす。
⇒メジャーネームをサイズに入れる
Q7:サブカテゴリごとの売上と売上の前年比成長率の相関を年ごとに確認したいです。2015年と2016年で前年比割れしてしまったサブカテゴリの傾向を教えてください。役員がモバイルで確認するため横に並べて各年を比較することができず、フィルターで切り換えて確認できるように依頼されています。
動画時間：1:12:30-1:22:40
・行列に合計(売上)と合計(売上)の簡易表計算で前年度比成長率を選択
⇒年(オーダー日)を列にいれ、2013年は前年比成長率がNULLとなるため非表示とする
⇒合計を色に入れ、ステップ2で色分けする。
⇒サブカテゴリをテキストにいれる
⇒年の選択：LOOKUP(MIN(DATEPART('year',[オーダー日]),0))を作成し、フィルタに入れる
※0行前のオーダー年最小値をかえす（つまりその年を数字で返す）
※普通に年(オーダー日)でフィルタしようとすると、前年度比成長率が計算できなく、NULLになるため
・ダッシュボードでスマホレイアウトにする
Q8: 出荷にかかる平均日数を週単位の推移で見せてください。社の方針として上方管理限界線(UCL:Upper Control Limit)を3σとして管理しているのですが、
Q8-1:この限界を突破して配送が遅延した週は何回ありましたか？
Q8-2:その週の配送先の市区町村を確認し、問題が起こった原因について調査してください。
動画時間：1:22:40-1:33:00
・出荷にかかる日数：DATEDIFF([オーダー日],[出荷日])
⇒平均(出荷にかかる日数)と平均の週(オーダー日)を行列にいれる
⇒〇円と棒グラフに二重軸・軸の同期をする
⇒アナリティクスで平均線を入れる
⇒リファレンスラインの分布を選択し、値は標準偏差「-3,3」を設定する
⇒標準偏差の3σを超えているところに色をつけるために、
AVG([出荷にかかる日数])>
WINDOW_AVG(AVG([出荷にかかる日数]))+WINDOW_STDEV(AVG([出荷にかかる日数]))*3をする
※「平均の出荷にかかる日数」が、「平均の出荷にかかる日数±3σ」より大きいとき
Q9: 2016年の四半期単位の売上推移をメーカーごとに比較してください。メーカー名が「s」で終わるメーカーにはどのようなトレンドがありますか？ メーカーの情報はExcelに入っていないので、「サンプル-スーパーストア-メーカー情報.tdsx」を使用してください。
動画時間：1:33:00-1:43:14
・メーカーを顧客 Idでブレンディングする
・メーカーをフィルタするために、ワイルドカードで後方の一致で｢s｣とする
・軸を行列によらないようにし、売上のヘッダも非表示にする
・行に表計算の IF LAST()=0 THEN SUM([売上]) END として、最後から0番目つまり、最後ののときは合計売上を返してという計算式をいれ、二重軸
・軸の同期をすることで、最後の値だけを表示する※テキストで最後だけ表示できるのでそれでもOK
Q10: 地域別売上ランキングの推移を年四半期で見たとき、売上開始当初(2013/1Q)と最終四半期(2016/4Q)でランキングの変動が合った地域はどこですか？
動画時間:1:43:10-1:47:15
・合計(売上) 簡易表計算でランクと四半期(オーダー日)を行列にいれる
⇒地域をマークの色にいれる
⇒計算フィールドで「最初と最後」：FIRST()=0 or LAST()=0として、フィルターで真のみにする
※2013/Q1と2016/Q4の内側を選択し、除去でも可能
Q11: カテゴリ「家電」における週数別の売上を見たとき、500,000円を連続で超えたのは
Q11-1:最大何週間ですか？
※閾値500,000はシミュレーションで変更される可能性があるので可変にしてください。閾値を超えた週を異なる色で橋梁した上で「閾値超え連続週数」を教えてください。閾値超えが途切れたら、ゼロリセットし、閾値超え連続週数の値を連続した週の最後の週にのみ表示してください。
Q11-2: 連続で超えている間、500,000円の超過分を累計したとき、最も高い累計金額はいくらですか？
※閾値を超過している分の売上高を累計し、最後の週に表示してください。
動画時間：1:47:15-
・カテゴリをフィルターに入れて、家電に絞る
⇒週ごとの売上推移を棒グラフで出す
※基準となる値(今回は問題の500,000)を超えたら色をつけたいが、可変にするためパラメーターを使う。
⇒SUM([売上])>=[閾値]
※売上合計が閾値(今の値は500,000)を超えていたら真、それ以外は偽
⇒IF [閾値判定] THEN PREVIOUS_VALUE(0)+1 ELSE 0 END
※もし売上合計が閾値(今の値は500,000)を超えていたら、前の行の計算(つまり0)に1を足して返す
PREVIOUS_VALUE計算は最初の行は普通に計算される（つまり最初の値は0になる）。
⇒上の式をテキストに入れると、閾値(500,000)を超える週数を数える
⇒IF [閾値判定] THEN PREVIOUS_VALUE(0)+(SUM([売上])-[閾値])
ELSE 0 END
※もし売上合計が閾値(今の値は500,000)を超えていたら、前の行の計算に合計売上から閾値(500,000)を引いた数字を足す
※PREVIOUS_VALUE計算は最初の行は普通に計算される。つまり最初の値は、売上から閾値(500,000)を引いた値が返される。
⇒上の式をテキストに入れると回答を確認することができる。
この記事が気に入ったらサポートをしてみませんか？