<commit_before>---
layout: post
title: "excel"
date: 2021-08-11
excerpt: "excelチートシート"
config: true
tag: ["excel"]
comments: false
---

# excelチートシート

## 先頭行の固定
 1. `[表示]`
 2. `[ウィンドウ枠の固定]`
 3. `[先頭行の固定]`

## RAND関数

**0~1**  

```
= RAND()
```
 - ランダムサンプルで用いることができる

**0~100で整数**  

```
= INT(RAND() * 100)
```

## IF関数

```
= IF(条件式, 1, 0)
```
 - 質的変数を数値変換するときなど

## ピボットテーブル
 - pandasのgroupbyみたいなもの

**テーブルの作成**  
 1. ヘッダー付きのデータでaggしたいものをすべて選択
 2. `挿入`
 3. `ピボットテーブル`

**操作**  
 1. キーとなる要素を`行`に設定する
 2. 集計対象の値を`値`に設定する
 - 量的変数は`合計`, `最大`, `最小`, `平均`, `分散`等
 - 質的変数は`個数`等

## テーブルの結合
 - pandasのmerge, sqlのjoinのようなことができる

**VLOOKUP**  

```
 = VLOOKUP(検索クエリ, 検索対象, 検索対象の何列目か)
```
 - 検索クエリは範囲にしないことが多い
 - 検索対象は範囲であることが多い
 - 検索対象で何列目を取り出すか

## ロングフォーマット, ワイドフォーマット
 - テーブルの横持ち縦持ちを切り替える
 1. 変換したいデータをコピー
 2. データを`形式を選択して貼り付け`の`形式を選択して貼り付け`のネストしているボタンにアクセス
 3. `行/列の入れ替え`を選択

## カイ二乗検定
 - テーブルから縦横の合計を出して、期待値のテーブルを作成する
 - `fx`をクリックして`すべて表示`から`CHISQ.TEST`を選択
 - 実測値と期待値を比較して実行

```
 = CHISQ.TEST(観測範囲, 期待範囲)
```
 - [参考](https://support.microsoft.com/ja-jp/office/chisq-test-%E9%96%A2%E6%95%B0-2e8a7861-b14a-4985-aa93-fb88de3f260f)

## CORREL関数
 - 相関係数を出す
 - `fx`からcorrel関数を選択して実行

## 分析のセットアップ
 - `ファイル`
 - `オプション`
 - `アドイン`
 - `設定` -> `分析ツール`

## 単回帰分析・重回帰分析
 - `データ` -> `データ分析`
 - `回帰分析`
 - データを設定して実行

## 移動平均
 - `データ` -> `データ分析`
 - `移動平均`
 - `区間` -> windowのサイズ
 - `出力先` -> 出力先

## 確率(や値)を返す関数

### 二項分布

```
 = BINOM.DIST(成功数, 試行回数, 確率, FALSE)
```

### 正規分布

**累積**

```
 = NORM.DIST(観測値, 平均値, 標準偏差, TRUE)
```

**確率質量**

```
 = NORM.DIST(観測値, 平均値, 標準偏差, FALSE)
```

### 正規分布(逆関数)

```
 = NORM.INV(確率, 平均, 標準偏差)
```

### カイ二乗分布

```
 = CHISQ.DIST(x, 自由度, 関数形式)
```
 - [参考](https://support.microsoft.com/ja-jp/office/chisq-dist-%E9%96%A2%E6%95%B0-8486b05e-5c05-4942-a9ea-f6b341518732)

## 検定

### Z検定
母集団の平均と標準偏差が判明しているときに一致するかどうか

```
 = Z.TEST(観測範囲, 母集団の平均, 母集団の標準偏差)
```
 - [参考](https://www.ipentec.com/document/office-excel-using-z-test)

### T検定
２つのサンプル群に平均の有意差があるか

```
 = T.TEST(範囲1, 範囲2, 尾部, 検定の種類)
```
 - [参考](https://dekiru.net/article/4597/)

### 正規分布から信頼区間を求める
母集団の標準偏差がわかっており、標本数がいくつかある時

```
 = CONFIDENCE.NORM(有意水準, 母集団の標準偏差, 標本数) 
```
 - [参考](https://support.microsoft.com/ja-jp/office/confidence-norm-%E9%96%A2%E6%95%B0-7cec58a6-85bb-488d-91c3-63828d4fbfd4)

### T分布から信頼区間を求める
母集団の標準偏差がわかっており、標本数がいくつかある時

```
 = CONFIDENCE.T(有意水準, 母集団の標準偏差, 標本数) 
```
 - [参考](https://support.microsoft.com/ja-jp/office/confidence-t-%E9%96%A2%E6%95%B0-e8eca395-6c3a-4ba9-9003-79ccc61d3c53)
<commit_msg>update "excel" doc<commit_after>---
layout: post
title: "excel"
date: 2021-08-11
excerpt: "excelチートシート"
config: true
tag: ["excel"]
comments: false
---

# excelチートシート

## 先頭行の固定
 1. `[表示]`
 2. `[ウィンドウ枠の固定]`
 3. `[先頭行の固定]`

## AVERAGE関数
 - 平均
 - rolling meanは一部限定して選択することで達成する(あとは全部に適応)

## RAND関数
 - **0~1を返す関数**  

```
= RAND()
```
 - ランダムサンプルで用いることができる

 - **0~100で整数にしたいとき**  

```
= INT(RAND() * 100)
```

## IF関数

```
= IF(条件式, 1, 0)
```
 - 質的変数を数値変換するときなど
 - [具体例](https://udemy.benesse.co.jp/business/office/excel-if.html)

## ピボットテーブル
 - pandasのgroupbyみたいなもの

**テーブルの作成**  
 1. ヘッダー付きのデータでaggしたいものをすべて選択
 2. `挿入`
 3. `ピボットテーブル`

**操作**  
 1. キーとなる要素を`行`に設定する
 2. 集計対象の値を`値`に設定する
 - 量的変数は`合計`, `最大`, `最小`, `平均`, `分散`等
 - 質的変数は`個数`等

## テーブルの結合
 - pandasのmerge, sqlのjoinのようなことができる

**VLOOKUP**  

```
 = VLOOKUP(検索クエリ, 検索対象, 検索対象の何列目か)
```
 - 検索クエリは範囲にしないことが多い
 - 検索対象は範囲であることが多い
 - 検索対象で何列目を取り出すか
 = [具体例](https://dekiru.net/article/21009/)

## ロングフォーマット, ワイドフォーマット
 - テーブルの横持ち縦持ちを切り替える
 1. 変換したいデータをコピー
 2. データを`形式を選択して貼り付け`の`形式を選択して貼り付け`のネストしているボタンにアクセス
 3. `行/列の入れ替え`を選択


## CORREL関数
 - 相関係数を出す
 - `fx`からcorrel関数を選択して実行

## 分析のセットアップ
 - `ファイル`
 - `オプション`
 - `アドイン`
 - `設定` -> `分析ツール`

## 単回帰分析・重回帰分析
 - `データ` -> `データ分析`
 - `回帰分析`
 - データを設定して実行

## 移動平均
 - `データ` -> `データ分析`
 - `移動平均`
 - `区間` -> windowのサイズ
 - `出力先` -> 出力先

## 確率(や値)を返す関数

### 標準偏差

**引数を標本と見なし、母集団の標準偏差の推定値を返す**  

```
 = STDEV(...)
```

**引数を母集団全体と見なし、標準偏差を返す**  

```
 = STDEVP(...)
```

### 二項分布

```
 = BINOM.DIST(成功数, 試行回数, 確率, FALSE)
```

### 正規分布

**観測値の確率を返す**  

```
 = NORM.DIST(観測値, 平均値, 標準偏差, TRUE)
```

**観測値の累積確率を返す**  

```
 = NORM.DIST(観測値, 平均値, 標準偏差, FALSE)
```
 - 例えばこれが0.025以下であれば異常値のようにみなせる

### 正規分布(逆関数)

**確率から値を返す**  

```
 = NORM.INV(確率, 平均, 標準偏差)
```

### カイ二乗分布

```
 = CHISQ.DIST(x, 自由度, 関数形式)
```
 - 自由度は`m*n`のとき`(m-1)*(n-1)`
 - 関数形式が`True`であれば確率を返し、`False`であれば累積確率を返す
 - [参考](https://support.microsoft.com/ja-jp/office/chisq-dist-%E9%96%A2%E6%95%B0-8486b05e-5c05-4942-a9ea-f6b341518732)


## 検定

### Z検定
 - **母集団の平均と標準偏差が判明しているときに一致するかどうか**
 - 片側確率が帰ってくる
 - **ユースケース**
   - 理想のデータがあり、すでに母集団の平均と分散がわかっているとき、ある範囲を切り取ったサンプル空間がこの平均と分散に従うか

```
 = Z.TEST(観測範囲, 母集団の平均, 母集団の標準偏差)
```
 - [参考](https://www.ipentec.com/document/office-excel-using-z-test)

### T検定
 - **２つのサンプル群に平均の有意差があるかの検定**
 - **ユースケース**
   - 10人にダイエット薬を与えたときに、与える前と与えた後の差があるかどうか

```
 = T.TEST(範囲1, 範囲2, 尾部, 検定の種類)
```
 - `尾部` -> `1: 片側確率`, `2: 両側確率`
 - `検定の種類` -> `1: 対になっているデータのt検定`, `2: ２つの母集団の分散が等しいときのt検定`, `3: ２つの母集団の分散が等しくないときのt検定`
 - [参考](https://dekiru.net/article/4597/)
 - [具体例](https://office-hack.com/excel/t-test/)

### 正規分布から信頼区間を求める
 - **母集団の標準偏差がわかっているとき**

```
 = CONFIDENCE.NORM(有意水準, 母集団の標準偏差, 標本数) 
 = CONFIDENCE.NORM(0.05, 2.5, 50) # 有意水準 0.05で2.5の標準偏差でsampleが50のとき
```
 - [参考](https://support.microsoft.com/ja-jp/office/confidence-norm-%E9%96%A2%E6%95%B0-7cec58a6-85bb-488d-91c3-63828d4fbfd4)
 - [具体例](https://kenkou888.com/category13/%E6%BC%94%E7%BF%92%E5%95%8F%E9%A1%8C_%E4%BF%A1%E9%A0%BC%E5%8C%BA%E9%96%93%E3%81%AE%E6%8E%A8%E5%AE%9A.html)

### T分布から信頼区間を求める
 - **母集団の標準偏差を推定しているとき**

```
 = CONFIDENCE.T(有意水準, 母集団の標準偏差の推定値, 標本数) 
```
 - [参考](https://support.microsoft.com/ja-jp/office/confidence-t-%E9%96%A2%E6%95%B0-e8eca395-6c3a-4ba9-9003-79ccc61d3c53)
 - [具体例](https://kenkou888.com/category13/%E6%BC%94%E7%BF%92%E5%95%8F%E9%A1%8C_%E4%BF%A1%E9%A0%BC%E5%8C%BA%E9%96%93%E3%81%AE%E6%8E%A8%E5%AE%9A.html)

### カイ二乗検定
 - **やり方**
   - テーブルから縦横の合計を出して、期待値のテーブルを作成する
   - `fx`をクリックして`すべて表示`から`CHISQ.TEST`を選択
   - 実測値と期待値を比較して実行

```
 = CHISQ.TEST(観測範囲, 期待範囲)
```
 - [参考1](https://support.microsoft.com/ja-jp/office/chisq-test-%E9%96%A2%E6%95%B0-2e8a7861-b14a-4985-aa93-fb88de3f260f)
 - [参考2](https://econoshift.com/ja/chi-square-test-basics/)

{'fraction_non_alphanumeric': 0.2223648323439756, 'fraction_numerical': 0.06609979143269694, 'mean_word_length': 4.00722891566265, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 15, 'https://': 18, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4991575', 'n_tokens_mistral': 4916, 'n_tokens_neox': 4897, 'n_words': 354}