Azureデータ分析入門 #2 【ツール比較  Excel編】

## はじめに
前回紹介した内容に沿った簡易集計と可視化を、**Excel**で行います。

#### 連載目次
[Azureデータ分析入門 #1 【はじめに】](https://qiita.com/Catetin0310/items/a7ab83069fec5352b535)
[Azureデータ分析入門 #2 【ツール比較 Excel編】](https://qiita.com/Catetin0310/items/d32c53305ab579d47950) → 本記事はこちら
[Azureデータ分析入門 #3 【ツール比較 Azure Notebook編】](https://qiita.com/Catetin0310/items/ae3c5f3dca5b3315896d)
[Azureデータ分析入門 #4 【ツール比較 Databricks編】](https://qiita.com/Catetin0310/items/b6030d3ad0fde16e57ab)
[Azureデータ分析入門 #5 【Databricks → Power BI Desktop】](https://qiita.com/Catetin0310/items/eb8431f38a51641b0db1)
[Azureデータ分析入門 #6 【CSVデータ → Power BI サービス】](https://qiita.com/Catetin0310/items/4af00df1f0af5feda1bb)
Azureデータ分析入門 #7 【AutoML でタイタニック号の生存者予測】

## 流れ
3つのCSVデータを組み合わせて、中間テーブルを作成します。

![06.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/bfd8f415-f56b-d5d0-415f-aee74ad94e5a.jpeg)

青色は、 各々のレコードに対して ```feight / (price + freight)```で算出した、<font color="Blue">単価と送料の合計に占める送料の割合</font>。オレンジは、キーとなる値を紐づけて取得した<font color="OrangeRed">英語のカテゴリ名</font>。
この中間テーブルを参照元にして、**pivotテーブル**と**pivotグラフ**を作成します。
![excel_csv_to_pivot.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/bd19206b-f2b7-0a33-3ace-11449082f31f.jpeg)


## 手順
テーブルを跨いでしまうと後続の処理が遅くなってしまうので、まずは一つのファイルにCSVデータをまとめます。左から売上テーブル、製品テーブル、翻訳テーブルを配置。
![excel_01.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/56c73efe-ae11-13d8-0102-61734c522797.jpeg)


注文テーブルの最右列に、送料の占める割合を計算する数式を入れます。一つのセルに数式を入れ、行末までコピペします。
![excel_1.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/8250d75a-0802-852f-7bf4-6c04df4aabac.gif)


同じく注文テーブルで、product_idをキーにして、製品カテゴリ(ポルトガル語)データを引っ張ってきます。皆さんおなじみvlookupです。
![excel_2.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/31413d5a-ae00-c8ea-78ec-db2ea1cb3bb9.gif)


次は製品カテゴリ(ポルトガル語)をキーにして、製品カテゴリ(英語)を引っ張ってきます。
![excel_3.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/2ea97bde-b3f4-a61d-4dfc-9726130db255.gif)


これでひとまず使おうと思っている情報は一つのテーブルに集約できました。売上ボリュームの大きいカテゴリを出すために、注文テーブルのセル範囲を選択して、pivotグラフ、pivotテーブルを作成します。
![excel_4.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/62f6a302-fc3a-faa2-9d4c-b57593fe457e.gif)


グラフの要素を選びます。フィールドリストの値に**単価**、行に**カテゴリ**を持っていき、テーブルを**単価の合計が大きい順にソート**します。
![excel_5.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/83e59a89-2235-d857-aaf1-a2803d73a521.gif)


この作業で得られた売り上げのトップ10カテゴリがこちら。
![result_target_categories.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/667ccdf6-c733-7ad1-77ab-562262b739d6.png)

次に、トップ10の中から要件に合う3つのカテゴリを選定するために、別のPivotを作成します。Pivotの参照セルは先ほどと同じ。フィールドリストの値に**単価**と**送料に占める割合**を入れ、行に**カテゴリ**を入れ、集計データの**平均値**を算出するように設定を変更します。
![excel_6.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/4f70c4b5-7ffe-5a19-c6ca-c1a43d3a3bf4.gif)


先ほど出した売上トップ10のカテゴリが表示されるよう、行ラベルをフィルタリングします。(gifファイルは例として1カテゴリだけフィルタリング)
![excel_7.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/5f11dec4-7e18-1ea3-3303-f368df7675ec.gif)


単価を棒グラフで、送料に占める割合を折れ線グラフに変更し、行ラベルなどのグラフ要素を配置します。
![excel_8.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/66d1210f-3c6c-e98c-5098-d74123c5e3ed.gif)

その結果。
![result_target_categories (graph).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/363781/92486fd9-dc4d-fe92-56e0-d5cfd4c27dcc.png)

このグラフから、

- ある程度売上ボリュームがある (トップ10以内)
- 比較的単価が高い
- 配送料の割合が低い

の要件を満たすカテゴリを3つ選定するとすれば、

- watched_gifts
- cool_stuff (DVDなどらしいです。なぜにcool_stuffなのか...)
- health_beauty

あたりでしょうか？なんとなくそれっぽい結果が出ました。
それではこの類の簡易分析・可視化をする場合のExcelの長所短所をまとめてみます。

## pros
- **習得コストが低い**
- 参照できるドキュメントが多い
- データ量が少ない場合にはさくっと可視化できる

## cons
- **運用コストが高い**
    - 元データに対する変更 (列の挿入や行の追加) に弱い
    - 再現性を高めるために必要なコストが高い
    - <font color="Red">慣れてる人ほどこれに気が付かない</font>

- **ビジネスロジックが埋もれやすい**
    - 数式が入れ子になっていて、結局この列で何を計算してるのか、不明瞭になりやすい
    - <font color="Red">他人が見たら意味不明なシートが出来上がることがある</font>

- **not スケーラブル**
    - 処理の可否がローカルマシンのスペックに依存する
    - 回避する方法はあるものの、それはそれで結構習得コストが高い
    - 最大で 約100万行 x 1.6万列 をサポートしているが、今回のような用途ではまず使用不可

## まとめ
Excelはお手軽で良いのですが、複数テーブルを結合したりといった分析には不向きですね。分析結果を共有したりするのも苦手です。次回は**Azure Notebook**でやってみます。お楽しみに！

## 参考サイト
[Kaggle.com](https://www.kaggle.com/)
[Brazilian E-Commerce Public Dataset by Olist](https://www.kaggle.com/olistbr/brazilian-ecommerce)