WorkoutWednesday 2019/W39の課題を提出したのですが、正直なところ試行錯誤で何となく出来てしまった感が強いので、自分のためにもまとめを書きます。
全て説明すると長くなってしまうので、チャレンジの要所のみ解説します。
チャレンジページは以下です。
今回の記事に使用したワークブックは以下からダウンロードできます。
要件について
基本的なところはチャレンジページを見て頂くとして、要点だけ以下の図に書きました。
要は、サブカテゴリなりManufacturerなりでSalesまたはOrder数のGrowthとMarket Shareを計算し、それをもとにしてBCG Matrixという分類にかけましょう、というチャレンジです。
そのためには大まかに言って、以下の数値を計算する必要があります。
最新年（このデータでは2018年）の数値
過去〇〇年の平均数値または総数値
過去〇〇年の全体数値
実際にはもう少し細かい計算がありますが、今回のチャレンジの要旨は上記の通りです。
ちなみに今回のチャレンジ、表計算の使用が禁止されていました。
表計算の方が実装が簡単であろう箇所はありますが、ご了承下さい。
それでは早速はじめていきましょう。
SalesについてBCG MATRIXを作る
チャレンジではSalesとOrderそれぞれに計算を作りますが、まずはSalesから取り組みます。
日付フィルターを作成する
年を指定した計算を多用しますので、先にこのあたりを作っておきます。
パラメータ：Years to Compare
（チャレンジの要件で、計算につかう年数を指定できるようにとあることから）
Year Filter
YEAR([Order Date]) >= {MAX(YEAR([Order Date]))} -[Years to Compare]+1
Latest Year filter
YEAR([Order Date]) = {MAX(YEAR([Order Date]))}
Previous Years Filter
YEAR([Order Date]) >= {MAX(YEAR([Order Date]))} -[Years to Compare]+1
AND
YEAR([Order Date]) <= {MAX(YEAR([Order Date]))} -1
このあたりは算数ですので詳細割愛します。
{}についてだけ言及すれば、こちら粒度を指定しないFIXEDです。
最終的に、それぞれの計算式がどの年をTRUEと返すかを以下に図示します。
Growthに関する計算（Sales）
GrowthとMarket Shareを計算していきますが、まずはGrowthに関連したものから取り扱っていきます。
Latest Sales
SUM(IF [Latest Year Filter] THEN [Sales] END)
Average Sales in n-1 years
SUM(IF [Previous Years Filter]THEN [Sales] END)
/
COUNTD(YEAR(IF [Previous Years Filter] THEN [Order Date] END))
Average Salesについて補足すると、以下２ステップで年平均を出しています。
Previous Years Filterに該当する年の総Salesを計算
含まれている年数で割り、年平均を算出
ちなみに、別解としては以下です。
Average Sales in n-1 years (別解)
AVG({ INCLUDE YEAR([Order Date]):
SUM(IF [Previous Years Filter]THEN [Sales] END)
})
さて、最新年のSalesと年平均Salesが計算できたので、Growthを計算します。
Sales Growth
([Latest Sales] - ZN([Average Sales in n-1 years]))
/
IIF(ZN([Average Sales in n-1 years])=0,1,[Average Sales in n-1 years])
分母のIIF()は単に0で割る場合を除外するためです。
もう一つ、Growthの最大値を算出する必要がありますが、こちらは後程。
Market Shareに関する計算（Sales）
続いて、Market Shareの計算をしていきます。
Total Sales
{SUM([Sales])}
Sales Market Share
SUM([Sales]) / MIN([Total Sales])
Market Shareの計算について、上述のYear Filter等を計算式に含めていないのは、ワークシート上でコンテキストフィルターに設定することを想定しているからです。
明示的に計算に使用する期間を指定したい場合は、適宜計算式に入れれば良いかと思います。
さて、ここまで出来たので、一旦は散布図を作ってみましょう。
BCG Matrixを作る
とりあえず下記のように配置してみましょう。
ところで、BCG Matrixの４分類のための基準値は、Market Shareが7%、Growthが最大Growthの半分でした。このあたりの数値を作っていきましょう。
Mid Market Share（パラメータ）
Mid Sales Growth
MIN({
MAX({ FIXED [Sub-Category]: [Sales Growth]})/2
})
Mid Sales Growthについて少し補足すると、最初の
MAX({ FIXED [Sub-Category]: [Sales Growth]})/2
の計算で、目的の「サブカテゴリ間の最大Growthの半分」を取得しています。
これを粒度指定なしのFIXEDにより、全サブカテゴリ（厳密に言えば全レコード）に同一の数値をアサインしている形になります。
ちなみに後ほどManufacturerについてのGrowthも使い、ManufacturerをBCG Matrixの分類に当てはめるわけですが、その際の基準Growthは上記のサブカテゴリ基準のものを使用するようです（再現対象Vizとの比較検算で確認）。
さて、２つの基準値が作成できたので、先ほどの散布図にリファレンスラインで追加します。
そして、この４象限のどこに含まれるかで分類していきます。
Sales Portfolio Category
IF [Sales Market Share] > [Mid Market Share]
AND [Sales Growth] > [Mid sales Growth]
THEN 'Stars'
ELSEIF [Sales Market Share] > [Mid Market Share]
AND [Sales Growth] <= [Mid sales Growth]
THEN 'Cash Cows'
ELSEIF [Sales Market Share] <= [Mid Market Share]
AND [Sales Growth] > [Mid sales Growth]
THEN 'Question Marks'
ELSEIF [Sales Market Share] <= [Mid Market Share]
AND [Sales Growth] < [Mid sales Growth]
THEN 'Dogs'
END
画像は以下サイトから適当に探しました。
さて、これでSalesに関するBCG Matrixの骨子は出来ました。
ここでは細かいフォーマット設定については割愛します。
ORDERについてBCG Matrixを作る
これからOrderについても作っていくわけですが、基本的には同じ計算を辿ります。
特にGrowthについては同一の計算ロジックのため、以下に計算式のみ記載しておきます。
Latest Orders
COUNTD(IF [Latest Year Filter] THEN [Order ID] END)
Average Orders in n-1 years
COUNTD(IF [Previous Years Filter]THEN [Order ID] END)
/
COUNTD(YEAR(IF [Previous Years Filter] THEN [Order Date] END))
Orders Growth
([Latest Orders] - ZN([Average Orders in n-1 years]))
/
IIF(ZN([Average Orders in n-1 years])=0,1,[Average Orders in n-1 years])
Mid Orders Growth
MIN({
MAX({ FIXED [Sub-Category]: [Orders Growth]})/2
})
OrderのMarket Shareについて
さて、ここからがちょっとだけ厄介です。
上述の通り、Order数にはCOUNTD([Order ID])を使用しますが、ちょっと以下の図を見てください。
当たり前と言えば当たり前なのですが、各Order IDに対して、複数サブカテゴリが含まれている場合があります。
（要は１注文で複数製品を注文した場合です）
この場合、Salesの場合のように{COUNTD([Order ID])}という計算で、Market Share算出のための全体Order数を算出することはできません。
したがって、Orderについては以下のように、サブカテゴリ単位でのOrder数を算出してから、その和を取る形で全体Order数を定義します。
（そうしないとMarket Shareの和が100%を超えます）
Total Orders
{SUM(
{FIXED [Sub-Category]: COUNTD([Order ID])}
)}
Orders Market Share
COUNTD([Order ID])
/
MIN([Total Orders])
ということで、めでたく同様にOrderのBCG Matrixを作成することが出来るようになりました。
Orders Portfolio Category
IF [Orders Market Share] > [Mid Market Share]
AND [Orders Growth] > [Mid Orders Growth]
THEN 'Stars'
ELSEIF [Orders Market Share] > [Mid Market Share]
AND [Orders Growth] <= [Mid Orders Growth]
THEN 'Cash Cows'
ELSEIF [Orders Market Share] <= [Mid Market Share]
AND [Orders Growth] > [Mid Orders Growth]
THEN 'Question Marks'
ELSEIF [Orders Market Share] <= [Mid Market Share]
AND [Orders Growth] < [Mid Orders Growth]
THEN 'Dogs'
END
最後に
今回の記事はここまでで留めておきますが、チャレンジ完成までの80％はここまででカバーされています。
興味ある方は、ぜひ残りの部分にも取り組んでみて下さい。
ちなみにWorkoutWednesday参加方法については以下にまとめました。ご参考になれば幸いです。