Excelではじめるデータサイエンス - 重回帰分析でタイタニック号の生存予測

![Excelだけで.png](https://qiita-image-store.s3.amazonaws.com/0/120341/a31a32ac-c362-0d64-629d-c913030a829b.png)

# はじめに

- **データサイエンスに興味がある**
- **Excel**なら使える
    - ピボットテーブルなら聞いたことあるぞ！

という方々向けに、**Excelの基本的な機能と関数のみ**を使って、データ分析（重回帰分析）を行う流れを説明していきたいと思います。

みなさまが本格的なデータサイエンスの勉強を始める前の足掛かりとなれば幸いです:bow:

# 分析

## 0. 目的

1912年の北大西洋にて、当時最大の旅客船である「タイタニック号」が沈没した事件はみなさまご存知でしょう。実は、**乗客全員が死亡したわけではなく、何とか生存することができた人たちもいました。**

それでは、どのような人たちが生き残ったのでしょうか？

- 階級の高い乗客が優先的に助けられたのでは？
- 体力がありそうなので若い男性が生き残ったのでは？
- ...

などなど、いろいろ考えられますよね。

今回は、このような仮説が本当か？ということをデータ分析で検証しながら、最終的に**乗客の情報をインプットすると、その乗客が生存したか否かをアウトプットしてくれる関数**を作成することを目指していきましょう。

## 1. 準備

以下の手順に従って**「タイタニック号の乗客データ」**をダウンロードしましょう。

1. Kaggleに登録
    - https://www.kaggle.com/
    - 注：Kaggleとはデータサイエンティストが腕を競い合うSNSのようなものです
2. タイタニック号の乗客データ（`train.csv`と`test.csv`）をダウンロード
    - https://www.kaggle.com/c/titanic/data

エクセルで`train.csv`を開くと、以下のようなデータが見れるはずです。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/ebd4c944-5ba2-7e88-12fd-d4c8cb7a3153.png)


## 2. データを概観する

### 2-1. 変数の意味を調べる

まずは、それぞれの列（＝変数）が何を意味しているのかを確認しましょう。
Kaggleに記載されているデータセットの情報を見ると、以下の説明があります。

- PassengerID
    - 乗客の番号
- Survived
    - 生存結果 
        - 1 = 生存
        - 0 = 死亡
- Pclass
    - 乗客の階級
        - 1 = 1st
        - 2 = 2nd
        - 3 = 3rd
- Name
    - 乗客の名前
- Sex
    - 性別
- Age
    - 年齢
- SibSp
    - 兄弟、配偶者の数
- Parch
    - 両親、子供の数
- Ticket
    - チケット番号
- Fare
    - 乗船料金
- Cabin
    - 部屋番号
- Embarked
    - 乗船した港
        - C = シェルブール（フランス）
        - Q = クイーンズタウン（ニュージーランド）
        - S = サウサンプトン（イギリス）

年齢や性別だけでなく、兄弟の数や、乗船した港もわかるようですね。
果たしてこれらの情報は生存したかどうかに関係があるのでしょうか？

### 2-2. ピボットテーブルをつくる

それぞれの変数が生存したかどうかに関係があるかを確認するためには、**ピボットテーブル**を使います。以下のいずれかの方法でピボットテーブルを作ってみてください。できない場合は[こちら](https://support.office.com/ja-jp/article/%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%97%E3%81%A6%E3%83%AF%E3%83%BC%E3%82%AF%E3%82%B7%E3%83%BC%E3%83%88-%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%88%86%E6%9E%90%E3%81%99%E3%82%8B-a9a84538-bfe9-40a9-a8e9-f99134456576
)


:hammer:**ピボットテーブルの作り方**

- [挿入]から[ピボットテーブル]をクリックして、データの範囲を選択
    - データの範囲全体を選択して、`Alt` -> `N` -> `V`でもOK

#### 2-2-1. 軸を決めて比較する
ピボットテーブルをつくると、特定の軸を決めて比較することができます。
たとえば、性別ごとや乗客の階級ごとの生存率を見ることができます。

ここは全くデタラメにやっても構いませんが、0. 目的でも言及したように「階級の高い乗客が助かったのでは？:thinking_face:」「体力がありそうなので男性が生き残ったのでは？:thinking_face:」などと、仮説を持ちながら検証していくと質の高い分析になるでしょう。

まずは、乗客の階級ごとの生存率を見てみましょう。
果たして階級が高い乗客の方が生存率が高いのでしょうか？

:hammer:**ピボットテーブルで乗客の階級ごとの生存率を見る**

1. ROWS（行）に`Pclass`を入れる
2. VALUES（値）に`Sum of Survived`（＝生存者数）と`Count of PassengerId`（＝乗客数）を入れる
3. 生存率` = 生存者数/乗客数`を計算する

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/3ddcf94a-39fe-e8ed-cab3-80a8f3bdea2b.png)

一等級の生存率は63％ですが、二等級は47％、三等級は24％と下がっていっています。
これより**階級の高い乗客は生存率が高い**ことがわかり、仮説は立証されました。

同様にして、性別ごとの生存率を見てみましょう。
果たして男性のほうが生存率が高いのでしょうか？

ROWS（行）に`Pclass`ではなく`Sex`を入れるだけですね。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/ae87d954-2f0f-463c-1f51-c2920bb31403.png)

男性の生存率が19％で、女性の生存率が74％。
以下のようにグラフにしてみると差が一目瞭然です。

:hammer:**ピボットテーブルからグラフをつくる**

- ピボットテーブルの適当な場所をクリックした状態で、[挿入]から[おすすめのグラフ]をクリック
    - または`Alt` -> `N` -> `R`でもOK

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/8bd12fd7-d22d-9f87-10c4-8cad636fcf51.png)


つまり、**女性の生存率が非常に高い**ことが分かり、仮説が棄却されました。
`Age`や`SibSp`（兄弟、配偶者の数）、`Parch`（両親、子供の数）など他の変数とSurvivedの関係もぜひ手作業で確認してみてください◎

---

:mag:コラム：なぜ女性の生存率が高いのか？
実は、当時日本人としてただ一人の乗客であった細野正文氏による[タイタニック号事故生き残りの手記](http://business.nikkeibp.co.jp/article/NBD/20120509/231811/)に、以下のような記述があります。

> ... 船客はさすがに一人として叫ぶ者はなく、皆落ち着いていたことは感心すべきことだと思った。ボートには婦人たちを優先的に乗せた。その数が多かったため、右舷のボート4隻は婦人だけで満員になった。その間、男子も乗ろうと焦る者も多数いたが、船員は拒んで短銃を向けた。この時船は45度に傾きつつあった。

乗客はパニック的状況の中でも焦らず、徹底して女性を優先的に救助していたようですね。

---

#### 2-2-2. 欠損値をチェック

次に、「空欄になっていてデータが入っていないセル」に注目しましょう。
データ分析用語では、**欠損値**と呼びます。

パッと見ると、AgeやCabinのデータが歯抜けになっていますが、欠損値がどれくらいの割合を占めているかピボットテーブルを使って確認しておきましょう。

ピボットテーブルのROWS（行）に`Age`を入れてみてください。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/9db6a5ae-51ef-d7b6-b3ce-10422eb0f35f.png)

すると、空欄になっているデータが177個あることがわかりました。
全体のデータが891個なので、2割ほどのデータが欠損しているようです。

### 2-3. 相関係数を計算する
「2つの変数がどれくらい関係があるか」を-1から1で表した、**相関係数**と呼ばれる指標があります。

この指標が1に近いほど、「片方の変数が大きいとき、もう片方も大きくなる」傾向にあり、これを**正の相関**と呼びます。
逆に、-1に近いほど、「片方の変数が大きいとき、もう片方は小さくなる」傾向にあり、**負の相関**と呼びます。

たとえば、2-2-1. 軸を決めて比較する で検証したように、「乗客の階級が1に近い（小さい）とき、生存率が高い（大きい）」ことがわかっていますので、**乗客の階級と生存率は負の相関**になっているはずです。確認してみましょう。

:hammer:**相関係数を計算する**
1. `=CORREL(変数の範囲, 変数の範囲)`で、Survivedのデータの範囲とPclassのデータの範囲を指定すればOK

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/47325003-0754-7add-39cd-30649b2063a2.png)

## 3. データを整形する
ここからは本格的にモデルを組むため、データをモデルが読み込みやすい形に整形していきましょう。

### 3-1. 使わない変数を削除する
まずは、必要のない変数を削除します。

ここでは、分析のやりにくそうな`Ticket`と`Name`を分析に使わないことにして、削除してしまいます。
逆に、2. データを概観する で検証したように`Sex`や`Pclass`は生存率に大きくかかわる変数ですので、必ず残しましょう。

### 3-2. ダミー変数をつくる
さて、分析を行うために、`Sex`や`Pclass`など、数字ではないデータを**ダミー変数**に変換する必要があります。

ダミー変数とは、数字ではないデータを「0」か「1」で表すことです。
以下の要領で実際に変換してみましょう。

:hammer:**Sexをダミー変数に変換する**
1. 男性であること1か0で表す変数 `Male` の列を追加する
2. `=IF(参照セル="male", 1, 0)`
3. すべての行が埋まるように縦にコピーする

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/ffec70fe-ff7f-233b-6e68-a18ba5aef24d.png)

このようにすると、変数`Male`が1のとき男性、0のときは女性を意味します。

:hammer:**Embarked（乗船した港）をダミー変数に変換する**
1. 乗船した港がサウサンプトンであること1か0で表す変数 `S` の列を追加する
2. `=IF(参照セル="S", 1, 0)`
3. すべての行が埋まるように縦にコピーする
4. 同様に、乗船した港がシェルブールであることを1か0で表す変数 `C` の列を追加する
5. `=IF(参照セル="C", 1, 0)`
6. すべての行が埋まるように縦にコピー

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/0dc76845-da73-6c01-862c-af787299481a.png)

---
:question: コラム：乗船した港がクイーンズランドであることを示す変数は要らないの？

もちろん作ることができます。`=IF(参照セル="Q", 1, 0)`とすればいいですね。

しかしながら、**SとCがともに0のとき、それはQ**になりますよね？

`Sex`の変数に対して、変数`Male`しか作らなかったのと同様にして、あえて作っていません。

---

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/5b873990-79e3-e132-e064-c1d8478f5d91.png)

次に、`Cabin`（部屋番号）のデータもダミー変数にしていきます。
部屋番号の種類は膨大な数があるため、各部屋番号に対してひとつひとつダミー変数を作っていくと埒があきません・・・。

"Titanic Cabin"でGoogle 画像検索をかけてみると以下のような画像が出てきます。

<img src="https://qiita-image-store.s3.amazonaws.com/0/120341/7f4b870d-1335-5b9a-7f67-08d2ad7602e0.png" width="480px" />

客室の階数によってAからGまでラベルが降られているようですね！
Aに近ければ近いほど、救命ボートに近いため生存確率が上がりそうだな、などということを考えつつ、**部屋番号の頭に付いている英字**がCのとき、Eのとき、Gのとき、とダミー変数を作っていくことにします。

:hammer:**Cabinをダミー変数に変換する**
1. Cabinの頭の英字がCであること1か0で表す変数 `CabinC` の列を追加する
2. `=COUNTIF(参照セル, "C"&"*")`
3. 同様の作業をA～Fで行う

- `*`（アスタリスク）はワイルドカードと呼ばれ、「どんな文字でもいいですよ」という意味
- ふつうの`=IF()`ではワイルドカードは使えないので`=COUNTIF()`を使う

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/a63f85c9-0724-22dc-a111-c61fbb1848b7.png)


### 3-3. データを補完する

最後に、2-2-2.で見た欠損値を何とかして補完していきます。
以下のような方法が考えられます。

#### 統計量を使って補完
- `Age`の欠損値を「平均値」や「中央値」で代替

#### 他の変数のデータを使って補完
- たとえば、`Cabin`と`Pclass`の相関をチェック
    - もし相関があれば、Pclassが1に近い人はCabinもAに近いようにする

今回は簡単のため、`Age`の欠損値を平均値`=AVERAGE()`で埋めて、Cabinの欠損値はそのままにしておきましょう。

## 4. モデルをつくる

### 4-1. 重回帰分析を行う

重回帰分析とは、乗客にかんする情報が、乗客の生存可能性にどれくらい寄与しているかを分析する手法のひとつです。このとき、乗客にかんする情報のことを**説明変数**と呼び、乗客の可能性のことを**目的変数**と呼びます。

式に表すと、以下のような形になります。（回帰式）

$y = a_1x_1 + a_2x_2 + ... + a_nx_n + b$

**たとえば... アイスクリームの売り上げ数 = 30 * 気温 + -5 * 値段 + 100**

もし無事にデータ整形が完了していれば、データは以下のように数字だけになっているはずですので、これに対して重回帰分析を行っていきましょう。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/a5c4193b-9881-222a-5f89-5f9e4f8ff451.png)


:hammer:**重回帰分析を行う**
1. [ファイル] タブをクリックし、[オプション] をクリックして、[アドイン] カテゴリをクリック
2. [管理] ボックスの一覧の [Excel アドイン] をクリックし、[設定] をクリックします
3. [アドイン] ボックスで、[分析ツール] チェック ボックスをオンにし、[OK] をクリック
4. シートに戻り、[データ]→[データ分析]→[重回帰分析]とクリック

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/bacddf7c-d183-c9e2-f873-33bef3c96dd8.png)

5. [入力Y範囲]にSurvivedの列の範囲を選択（目的変数）
6. [入力X範囲]にそれ以外の変数の範囲を選択（説明変数）
7. [ラベル]にチェックを入れておく

うまくいけば、以下のように重回帰分析の結果が得られるはずです。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/ed78e282-a34f-dcc5-f338-00dbe10c231a.png)

Coefficients（係数）の部分に書かれている数字が、
以下の式でいうところの$a_1, a_2, ... , a_n, b$に相当しています。

$y = a_1x_1 + a_2x_2 + ... + a_nx_n + b$

すなわち、回帰式の係数が分かったので、**乗客の情報（つまり$x_1, x_2, ..., x_n$）を入れれば生存するかどうかがわかるモデル**が完成しました！

### 4-2. 推定する
さて、得られたモデルを使って、**乗客の情報から生存予測**をしてみましょう。
まずは、`test.csv`からテストデータを開き、分析に使ったデータと同じ形式に変換してください。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/856f386e-ceb2-b40e-4bc3-38a99a64823c.png)

Regression, Survivedという2つの列を追加したのち、以下の手順で推定を行っていきます。

:hammer:**作ったモデルを使って生存予測をする**
1. 回帰式の係数をコピーし、適当な場所に`Ctrl + Alt + V`で縦横を転置して貼り付け
2. `SUMPRODUCT(ベクトル, ベクトル)`を使って回帰式を計算
3. 閾値を設定して`=IF(得られた値 > 閾値, 1, 0)`を使って1か0に変換する

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/a6c4acf2-fee9-da72-7f87-5834d430d21a.png)


---

:question: コラム：閾値はどういう意味？

重回帰分析は、アイスクリームの売り上げなどのようにあくまでも「実数」を返すモデルです。
しかし、今回は生存したかしていないかを「生存」か「死亡」で表現しなければいけないため、

- モデルがはじき出した数字が1に近ければ「生存」つまり1
- 0に近ければ「死亡」つまり0

という形で変換を施しています。

---

### 4-3. 検証する
最後に、モデルで得られた生存予測を検証してみます。
まずは、テストデータと予測結果いを**PassengerID, Survivedだけ残してcsvで出力**してください。

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/ebdc44df-973d-c914-0989-7d9a609758c9.png)

それを[Submit Form](https://www.kaggle.com/c/titanic/submit)から**Kaggleに提出**すればOKです

![image](https://qiita-image-store.s3.amazonaws.com/0/120341/cbda661d-5dfe-59ef-b2b5-44821f221751.png)

結果を見ると、Excelで作ったモデルは**正答率76％**を記録しました:clap:

## あとがき
いかがでしたでしょうか？

Excelの関数・機能のみを使ってデータ分析を行うイメージが付きましたでしょうか:thinking_face:

Excelは、

- そんなに重くないデータを概観するにはとてもよい
    - ピボットテーブルやグラフなども使いやすい
- 重回帰分析もワンクリックでできる
- 実はすべての変数の相関係数を一気に見ることもできる
    - データ分析の機能をオンにして、「相関」を使えばOK

などなど、データサイエンスをはじめるに当たって基礎的な機能はしっかり持っていますので、
特に初めたてのころは、Excelだけを使ってある程度の分析を行っていけるかと思います:point_up:

---

なお、これ以上の分析をやりたい方々は、ぜひ**Python**や**R**を使っていきましょう。

- Excelの重回帰分析には16変数までという制限がある
- PythonやRを使えばダミー変数化も一発だし、さまざまなモデルがさまざまなライブラリから提供されている

など、Excelだけでは手の届かない分析に簡単に手を伸ばすことができます。
インターフェイスが変わるので取っ付き辛いですが、上記で学んだ基本的な考え方を応用しながら、ぜひトライしてみてください:ok_hand:

https://qiita.com/hik0107/items/19dd2f6a4ab61ec21905
https://qiita.com/hik0107/items/ef5e044d2f47940ba712
https://qiita.com/hik0107/items/3dc541158fceb3156ee0