狠狠撸

全部Excelだけで実現しようとして後悔するデータ分析
2nd Edition
じょんすみす

2お前誰よ？
じょんすみす
クラスメソッド
データアナリティクス事業本部インテグレーション部
ML推進チームリーダー
札幌の人
普段のお仕事
? 機械学習, SageMakerおじさん
? Hadoop, EMRおじさん
? Alteryx ACEの人
? クソExcelにモンク言うおじさん

4お題目
? なぜExcelは万能で人々に愛されるのか？
? データ分析のためのExcel準備
? データ分析のための贰虫肠别濒活用

5お題目

6なぜExcelは万能で人々に愛されているのか？
世界で最も多く使われているソフトウェアの1つ、それがExcelと言っても
過言ではないでしょう。
? 誰でも簡単に「とりあえず使ってみる」が可能
? 自由すぎるセルと圧倒的多機能により割と何でも実現
? 自由に使えすぎるので他の人のExcelは何してるかわからん
? 「正しいExcelの使い方」なんて概念すらも...
? 誰でもフリーダムに使えるのでエンジニアにとって忌み嫌われる存在
? 自動化や効率化の妨げ的存在として扱われる
? もっと適切なツールがあるでしょ...と言われる

7実践！嫌われる贰虫肠别濒の使い方

8ちょっと待て
その使い方は本当に悪なのか？

9データ分析の流れで考える必要な要素
データの取得分析レポーティング
テーブルデータ
以下の内容を明確化
? 取得元
? フォーマット
? 含まれる情報
定期的な取得?更新の検討
分析内容に合わせた加工
分析処理の実施
専門知識が必要
意思決定で活用可能な形式
帳票やダッシュボード作成
この部分でレイアウトや
印刷形式を整える

10お題目

11データを整備しよう
何処にあるのかわからないデータは存在しないのと同じ
また、データとして利用可能な形式になっていない場合も同様

理想的なデータの集め方と現実
理想
現実

まずはスモールスタートで始めていく
今あるデータや今回使いたいデータをまずは準備
より詳細な分析を行ったり精度を高めたりするには様々なデータが
必要となるがいきなり全部は扱いきれない
まずは共有フォルダなど低コストで実現可能な方法でデータを集約
最初は手動でもいいのでデータカタログを作成しておく
共有されているデータを加工して分析を行う
すぐに始められる手ごろなツールを使ってDWHなどの導入は
共通処理などが見えてきてから行う
→ Excelでデータソースのリストを作成する
→ Power 蚕耻别谤测を使ったデータの取得?加工

ある程度分析が定着してきたら
データ収集の自動化も検討
データからカタログを自動生成可能なツールの導入を検討
整形されたデータを共通の基盤で利用可能とするためのDWHを導入
データレイクからのETLを自動化
様々な視点で分析を行ためのBIツールや分析ツールを導入
データ分析の民主化

16今あるデータをカタログ化する
データの情報をリスト化する

データの情報をリスト化し、HYPERLINK関数を使って紐づける。
HYPERLINK(“https://dev.classmethod.jp/”, “Developers.IO”)
HYPERLINK(“Z:analysisdata”, “データフォルダ”)
HYPERLINK(“[iris.xlsx]iris!A1”, “颈谤颈蝉データセット”)

データの情報をリスト化し、HYPERLINK関数を使って紐づける。

19データの取得にはPower Queryを使う
Power Query? 何それおいしいの?

なんかいろいろ対応してる！！

Excelの場合、シートを選択(複数も可)

フォルダからの場合、同一スキーマの複数ファイルをまとめて取得可能

ODBCではSQLやテーブル選択で取得可能

WebページからではHTMLのtableタグやデータセットを取得可能
※ 画像はデモ用のものになります。Wikipediaなどクロールを禁止してるサイトもあるのでご注意ください。

(再掲)そのほかもいろいろ対応

26データはキレイな形式に！！
取り込むデータは分析しやすいように加工しよう。
データの基本はテーブル形式

先頭行はヘッダ
各列の名前を入れる

データは1行につき1件

同じ列には同じ種類の
データ

ダメな例 : 1行に複数データ
購入商品列に複数データが
入ってる
一行1データに変換する

ダメな例 : 複数列に1つのデータ(データにあわせて列も増加する)
同一の指標が複数列に
またがってる
同じ指標は常に同じ列

32いいデータってどんなデータ？
Tidy Data(整然データ)という概念が存在する。
R界隈で有名なHadley Wickham氏が提唱
? 個々の変数が1つの列をなす。
? 個々の観測が1つの行をなす。
? 個々の観測の構成単位の類型が1つの表をなす。
TidyじゃないデータはMessy Data(雑然データ)と呼ばれる
Tidy Dataの詳細は以下を参照
? Wickham, Hadley (20 February 2013). "Tidy Data". Journal of Statistical Software.
? 【翻訳】整然データ｜Colorless Green Ideas

33ここまでやると何が嬉しいか？
分析をする際にこの構造になってることで様々なメリットが生まれる。
? 元データが未集計の状態なので分析の切り口を変更しやすい
? Power Queryはデータに更新があった際に「データ更新」のみで対応可能
? データをテーブルにしておくことで構造化参照が利用可能
? Tidy Dataは分析での利用やそのための変換をしやすくするためのデータ構造
? ピボットテーブルを利用する際に扱いやすい構造
? などなど

34ところで...
構造化参照 is 何？
セルの範囲ではなく列名を使ってデータを取り出す参照方法
↑こんな感じのデータがあるとする

35ところで...
テーブル名[列名]で特定の列全体を取り出す
=SUM(iris[Sepal.Length])

36ところで...
テーブル名[[列名1]:[列名2]]で連続する複数列を取り出す
=SUMPRODUCT(iris[[Sepal.Length]:[Sepal.Width]])
テーブル名のみでデータ部分全体を取り出す
=SUMPRODUCT(iris)

37ところで...
テーブル名[#見出し]で列全体を取り出す
=COUNTA(iris[#見出し])
テーブル名[[#見出し], [列名]]で特定の列を取り出す
=iris[[#見出し], [Sepal.Length]]

38データをキレイにしろ、と言いたいことはわかった
俺のデータはお前の言う形式になってない
だから使えないじゃないか！

39データをキレイにしろ、と言いたいことはわかった
俺のデータはお前の言う形式になってない
だから使えないじゃないか！
安心してください
Power Queryの真価はここからです！

40Power Queryでデータの加工?整形をせよ！
JSONデータの取り込みで学ぶPower Query
俺、こんな感じのデータがあるので取り込んでみようと思うんだ...

こんな感じの画面が出てくる
機能リスト
データのプレビュー
やったこと
リスト

今どんなデータ？
JSONの構造、こんな感じですよー

JSONはテーブル形式のデータってわけじゃないのでまずは変換
“オブジェクトの名前とその中に含まれるデータ”の構造に展開された

リストの中身を展開しよう
Value列がカンマ区切りで展開された。

1データ1列にしよう
1データ1列になった

Name列を列名、それ以外を値とするために転置しよう
1行1データの形に近づいた！！

先頭列をヘッダにして、完成！！
Tidyな感じのデータになった！！

こんな感じでデータ整形できるわけですが、そのプロセスはすべて記録されてる。
? 何の処理をしたかを明確化
? 各ステップの実行前後の結果比較が可能
? ステップの削除や追加が可能
? もちろん編集も可能
? ステップに名前を付けて明確化することも可能

他にもできることは様々！
? このほかにも様々な処理があるので大体なんとかなる
? 複数のデータソースを扱うことも可能
? 独自言語(M言語)ではあるが、ソースコード記述も可能

JSON取り込みのソースコードはこんな感じ

51データモデルの話
データモデルとキューブと...
Power Queryで読み込んだデータはどこに保持してるのか？
? Power Queryでの読み込み先設定
? テーブルとして読み込む以外にも様々
? データモデルに追加することも可能
キューブはこんな感じ→
軸1 : 月ごとの
軸2 : 商品ごとの
売上

52データモデルの話
データモデルを作成してPower Pivotで扱う。
データモデルに対するリレーションリレーションはPivot 罢补产濒别でそのまま利用可能

53お題目

54分析をはじめていこう
さあ、準備は整った！いよいよここからデータ分析が始まるゾ！！

さあ、準備は整った！いよいよここからデータ分析が始まるゾ！！
の前にExcelの基礎知識

56Excelの基本的な関数
関数名用途?利用方法備考
SUM関数対象範囲の足し算を行う
=SUM(A1:A10)
TRANSPOSE ベクトルや行列の転置を行う
{=TRANSPOSE(A1:C10)}
MMULT 行列積を計算する最も基本的な関数と言える
{= MMULT(TRANSPOSE(A1:C4), A1:C4)} さらばSUMPRODUCT
よく使う基本的な関数を確認していこう

57Excelの基本的な機能
配列数式を理解しておこう
入力または出力が配列になるような計算式
全行を合計
範囲内で行ごとに計算した結果に対してSUM関数を実行
Ctrl+Shift+Enter

配列数式とTRANSPOSE, MMULT関数の組合せでベクトルの内積計算
→ SUMPRODUCTより汎用性が高いので以降の計算はこっちを利用

複数の値を結果として返すことも可能
出力と同じサイズの範囲を選択して配列数式を実行

NxM行列とMxK行列の積をMMULTで計算
NxKの範囲で結果を出力

おまけ:配列を作る
「{}」内に値を入れる
「,」で次の列、「;」で次の行

今度こそ本当にデータ分析が始まるゾ！！

63データを用意したらまず最初にやること
それは、基本統計量の確認。
データ > データ分析を選択(ない場合はアドオンで追加)

データ > データ分析を選択(ない場合はアドオンで追加) > 基本統計量

データ分析ツールは甘えなので計算する

フィル

ヒストグラムは1列1列心を込めて丁寧に作成
対象の列を選択し、グラフの種類にあるヒストグラム(棒グラフではないよ！)で作成

コピーしてデータソースだけを書き換えると比較的楽

そんなことはやってられないので、VBAで全列のヒストグラム作成
Sub test()
Dim col As Variant
For Each col In Range("boston").ListObject.HeaderRowRange
Range("boston").ListObject.ListColumns(col.Value).Range.Select
ActiveSheet.Shapes.AddChart2(366, xlHistogram).Select
ActiveChart.ChartTitle.Select
Selection.Caption = col
Next col
End Sub
※ このままだと全部同じ位置に出力されるので適当に調整する

70カーネル密度推定もしてみよう
カーネル関数にぶっこんである値周辺の確率値を求める
? ? =
1
??
?=1
?
?
? ? ??
?
? ? =
1
2?
??? ?
?2
2
x についての確率密度ガウシアンカーネルを使用
n : データ件数
xi : 観測された各データ
h : パラメータ

取りうるxの範囲をデータから決める
? 最大値 : MAX(boston[medv])
? 最小値 : MIN(boston[medv])
編集 > フィル > 連続データの作成
でmin/maxを参照にxの値を生成する
? 今回は0～60までを0.1刻みで生成

??
?
? {=SUM(
1 / SQRT(2 * PI()) *
EXP(
-POWER(($D3 - boston[medv]) / E$2, 2) / 2
)
) / COUNT(boston[medv]) * E$2}
? ? =
1
??
?=1
?
1
2?
??? ?
? ? ?? ? 2
2
計算する値 :

あとは下までオートフィル

この値で折れ線グラフを作ることでカーネル密度関数の完成！
いい感じになるようにhを調整しよう

75異常値を見つけてみる
z値を計算する
? = (? ? ?) ?
=STANDARDIZE(
boston[@nox],
AVERAGE(boston[nox]),
STDEV.S(boston[nox]))
計算する値 :
? テーブルになってれば行は勝手に計算される
? 列方向はオートフィルで

76異常値を見つけてみる
異常値となるものに対して条件付き書式を割り当てて確認する
z値の絶対値が3以上のものを異常値として設定。
この結果を元にフィルタの対象にするなど、どのように対処するかを決める。

77相関係数を求めてみる
どのデータとどのデータに関連がありそうなのか俯瞰する
? =
1
? ?=1
?
(?? ? ?)(?? ? ?)
1
? ?=1
?
(?? ? ?)2 1
? ?=1
?
(?? ? ?)2
? 相関係数は2つの列データにの関連性を数値で表す
? 値が1に近いほど正の相関(片方が高くなればもう片方も高くなる)がある
? 値が-1に近いほど負の相関(片方が高くなればもう片方は低くなる)がある
? 値が0に近ければ相関なし
? 列の組み合わせ分だけ相関係数が計算できるので行列で表せる
相関係数の求め方

どのデータとどのデータに関連がありそうなのか俯瞰する
データ > データ分析 > 相関で利用可能

別解 : 頑張って計算する
各データのz値
{=MMULT(TRANSPOSE(boston_z), boston_z) / (COUNT(boston_z[crim])-1)}
行列積を計算してデータ件数-1で割る

別解 : 頑張って計算する
任意の2の列x,yに対して、i番目のデータのz値をzxi, zyiとする。
元データDに対してDTDを計算結果の、x行y列目の値は
? ?
? ? ? =
?
? ?? ? ?? ? ?? =
?? ? ? ?
? ?
? ?? =
?? ? ? ?
? ?
=
?
?? ? ? ? ?? ? ? ?
? ? ? ?
? ? =
1
? ? 1
?
?? ? ? ?
2 ? ? =
1
? ? 1
?
?? ? ? ?
2
=
? ? 1
?? ? ? ?
2 ?? ? ? ?
2 ?? ? ? ? ?? ? ? ?
,
,
n-1で割ることで、相関係数と一致する。

81散布図を見てみる
相関係数と同じように2つのデータの関連を見るためのもの
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1
0 5 10 15 20 25 30
nox
indus
散布図

82主成分分析(PCA)をしてみる
PCA is 何？は割愛
列数と同じ数の適当な値を入れる
適当な値
=SUMSQ(I2:I5)
ベクトルの長さ
{=MMULT(
iris4[@[Sepal.Length]:[Petal.Width]],
$J$2:$J$5)}
主成分の値を計算する
分散を求める
=VAR.S(iris4[第1軸])

ソルバーを起動する
目的セル : 分散を計算している部分
→ PCAの定義からこれを最大化できればOK
変更可能 : 最初に適当な値を入れた4つのセル
制約条件 :
? 各値が-1から1の範囲内
? ベクトルの長さが1になっている

第1主成分(分散を最大化する軸)が求まった！！
第2主成分も同じ手順で求める
=SUMPRODUCT(I2:I5, J2:J5)
第1主成分と第2主成分の内積
PCAの各主成分は直交するので
→ソルバでこの値が0を制約条件に追加する

第2主成分も求まった！！
主成分の軸でプロットすると
こんな感じ
※ 第3, 4主成分も同様に求められる

結果を確認する
共分散行列を作成(相関行列と同じノリで作れます)
共分散行列第一主成分(固有ベクトル)
分散(固有値) 第一主成分(固有ベクトル)
ソルバーによる近似解
なので多少のずれ
はあるがほぼ一致してる

87回帰分析をしよう
我々は未知の数値を予測する
データ分析ツールにある回帰分析

0
2
4
6
8
10
12
切片 crim zn indus chas nox rm age dis rad tax ptratio black lstat
変数重要度
y = 0.7406x + 5.844
R? = 0.7406
-10
0
10
20
30
40
50
0 10 20 30 40 50 60
予測値
medv

とはいえ、回帰は関数が用意されている
{=LINEST(boston[medv], boston[[crim]:[lstat]], TRUE, TRUE)}
※ 係数の順番が元データと逆になるので注意

関数の戻り値以外は計算する

説明変数と係数をかけ合わせて切片を足すだけ！！
予測値を求めてみようよ！！

94ロジスティック回帰もしよう
我々は分類もする
Excelの機能にロジスティック回帰は無いので実装する
切片の項を追加
wをランダムな値で初期化

損失関数を計算
? ? =
1
1 + exp ? ? ?
? = ?
?=1
?
?? ??? ?? + 1 ? ? ?? 1 ? ? ??
{=-SUM(
iris_orig[num_species] *
LN(1 / (1 + EXP(-MMULT(iris_orig[[Sepal.Length]:[w_0]], TRANSPOSE(A4:E4))))) +
(1-iris_orig[num_species]) *
LN((1 - 1 / (1+EXP(-MMULT(iris_orig[[Sepal.Length]:[w_0]], TRANSPOSE(A4:E4))))))
)

損失関数の微分とハイパーパラメータα
の値を使って各wの値を更新
?? = ?? + ?
??
???
??
?? ?
= ? ? ? ?
?
??
=$A4:$E4 + $B$1 *
MMULT(
TRANSPOSE(iris_orig[num_species] –
(1/(1+EXP(
-MMULT(iris_orig[[Sepal.Length]:[w_0]],
TRANSPOSE($A4:$E4)))))),
iris_orig[[Sepal.Length]:[w_0]]
)/COUNT(iris_orig[Sepal.Length])
損失関数の減り具合を確認

収束するまでオートフィル

実装できたロジスティック回帰で推論する
=IF([@predict] > 0.5, 1, 0)
{=1 / (1 + EXP(-MMULT(iris[@[Sepal.Length]:[w_0]], TRANSPOSE(Sheet1!$D$300:$H$300))))}

実装できたロジスティック回帰で推論する
そして無事それっぽい結果に

100時系列予測をしよう
Excelなら時系列データだって扱えちゃうんです！
今度はExcelの機能を使えるよ！
時系列の売上データデータタブにある「予測シート」

=FORECAST.ETS(A35, $B$2:$B$34,$A$2:$A$34)
予測値(C列)
予測対象推論に使うデータ時系列情報
上振れ、下振れの95%信頼区間(D, E列)
=C35-FORECAST.ETS.CONFINT(<同じ範囲>)
=C35+FORECAST.ETS.CONFINT(<同じ範囲>)

A~E列全体で折れ線グラフを作成する
予測値となる部分を境に別な系列に
なっているので色や形を調整する

105ダッシュボートを作ろう
色々データが出そろったのでまとめようと思うんだ...

甘えなかったものはそのまま更新可能
甘えた場合再度生成する必要がある
元データは簡単に
更新可能

111まとめ(?)的な何か
そんなことはない！！Excel大っっっっっっ嫌い！！！！！

112参考資料
? Excelパワーピボット
? 最速で収益につなげる完全自動のデータ分析
? Excelで学ぶデータ分析本格入門
? プロコンサルタントの最強エクセル術
? Excelで操る! ここまでできる科学技術計算
? 2017 Planning Guide for Data and Analytics
? HYPERLINK関数でハイパーリンクを作成する
? 主成分分析をExcelで理解する

狠狠撸

全部Excelだけで実現しようとして後悔するデータ分析 2nd Edition

More Related Content

全部Excelだけで実現しようとして後悔するデータ分析 2nd Edition

Editor's Notes