• home
  • about
  • 全ての投稿
  • ソフトウェア・ハードウェアの設定のまとめ
  • 分析関連のまとめ
  • ヘルスケア関連のまとめ
  • 生涯学習関連のまとめ

excel

date: 2021-08-11 excerpt: Excelの使い方とチートシート

tag: microsoftexcel

Excelの使い方とチートシート

オンライン版Excel

多くの分析関数は無料のオンライン版エクセルで動く

  • 分析関数も動くが、アドインの分析ツールが使えない(重回帰分析でP値を見ることなど)
  • excel webで作成したファイル

ライセンス認証しないExcel

  • 基本的に開いて見るだけであるが、osxでpagesを使って開くより、素早く開けるなどの特徴があり、ライセンスを持っていなくてもインストールしておく価値がある

それぞれの関数と機能

先頭行の固定

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

AVERAGE関数

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

RAND関数

  • 0~1を返す関数
= RAND()

  • ランダムサンプルで用いることができる

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

= INT(RAND() * 100)

IF関数

= IF(条件式, 1, 0)
  • 質的変数を数値変換するときなど
  • 具体例

ピボットテーブル

  • pandasのgroupbyみたいなもの

テーブルの作成

  1. ヘッダー付きのデータでaggしたいものをすべて選択(indexも含んだほうが良い)
  2. 挿入
  3. ピボットテーブル

操作

  1. キーとなる要素を行に設定する
  2. 状態を知りたい要素を列に設定する
  3. 集計対象の値を値に設定する(個数を知りたいときはindexを用いる)
    • 量的変数は合計, 最大, 最小, 平均, 分散等
    • 質的変数は個数等

テーブルの結合

  • pandasのmerge, sqlのjoinのようなことができる

VLOOKUP

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

ロングフォーマット, ワイドフォーマット

  • テーブルの横持ち縦持ちを切り替える
    1. 変換したいデータをコピー
    2. データを形式を選択して貼り付けの形式を選択して貼り付けのネストしているボタンにアクセス
    3. 行/列の入れ替えを選択

CORREL関数

  • 相関係数を出す
  • fxからcorrel関数を選択して実行
 = CORREL(列1, 列2)
\[CORREL(X,Y) = \frac{\sum(x-\bar{x})(y-\bar{x})}{\sqrt{\sum(x-\bar{x})^2\sum(y-\bar{y})^2}}\]

分析のセットアップ

  • ファイル
  • オプション
  • アドイン
  • 設定 -> 分析ツール

単回帰分析・重回帰分析

  • データ -> データ分析
  • 回帰分析
  • データを設定して実行

移動平均

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

確率(や値)を返す関数

標準偏差や分散

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

 = STDEV.S(...)

不偏分散を返す

 = VAR.S(...)

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

 = STDEV.P(...)
  • 偏差値や変動係数の計算で用いられる

二項分布

 = BINOM.DIST(成功数, 試行回数, 確率, FALSE)
 = BINOM.DIST(6, 10, 0.5, FALSE) # -> 0.2050781, 10回のコイン投げで6回表が出る確率

正規分布

観測値の確率を返す

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

観測値の累積確率を返す

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

正規分布(逆関数)

確率から値を返す

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

カイ二乗分布

 = CHISQ.DIST(x, 自由度, 関数形式)
  • 自由度はm*nのとき(m-1)*(n-1)
  • 関数形式がTrueであれば確率を返し、Falseであれば累積確率を返す
  • 参考

検定

Z検定

  • 母集団の平均と標準偏差が判明しているときに一致するかどうか
  • 片側確率が帰ってくる
  • ユースケース
    • 理想のデータがあり、すでに母集団の平均と分散がわかっているとき、ある範囲を切り取ったサンプル空間がこの平均と分散に従うか
 = Z.TEST(観測範囲, 母集団の平均, 母集団の標準偏差)
  • 参考

T検定

  • ２つのサンプル群に平均の有意差があるかの検定
  • ユースケース
    • 10人にダイエット薬を与えたときに、与える前と与えた後の差があるかどうか
 = T.TEST(範囲1, 範囲2, 尾部, 検定の種類)
 = T.TEST($A:$A, $B:$B, 2, 1) # 具体例
  • 尾部 -> 1: 片側確率, 2: 両側確率
  • 検定の種類 -> 1: 対になっているデータのt検定, 2: ２つの母集団の分散が等しいときのt検定, 3: ２つの母集団の分散が等しくないときのt検定
  • 参考
  • 具体例

数式的定義
\(s^2 = \frac{(n_a-1)s_a^2 + (n_b-1)s_b^2}{n_a + n_b - 2}\)

\[T = \frac{\bar{X_a} - \bar{X_b}}{s \sqrt{\frac{1}{n_a} + \frac{1}{n_b}}}\]

自由度は(na + nb - 2)

正規分布から信頼区間を求める

  • 母集団の標準偏差がわかっているとき
 = CONFIDENCE.NORM(有意水準, 母集団の標準偏差, 標本数) 
 = CONFIDENCE.NORM(0.05, 2.5, 50) # 有意水準 0.05で2.5の標準偏差でsampleが50のとき
  • 標準偏差にはSTDEV.Pを用いる
  • 参考
  • 具体例

T分布から信頼区間を求める

  • 母集団の標準偏差を推定しているとき
 = CONFIDENCE.T(有意水準, 母集団の標準偏差の推定値, 標本数)
  • 標準偏差にはSTDEV.Sを用いる
  • 参考
  • 具体例

カイ二乗検定

  • やり方
    • テーブルから縦横の合計を出して、期待値のテーブルを作成する
    • fxをクリックしてすべて表示からCHISQ.TESTを選択
    • 実測値と期待値(期待度数)を比較して実行
 = CHISQ.TEST(観測範囲, 期待範囲)
  • 参考1
  • 参考2

microsoftexcel Share Tweet