はじめに
VBAって、どんな職場でも簡単に導入できるのが魅力的ですよね。
私が以前在籍していた職場は、DX化？何それ？って感じでしたが、Excel作業はたくさんありました。当時は仕事終わりにVBAでExcel業務の自動化を進めたりしていたのですが、転職して一切触っていませんでした。久々にVBA使ってみたいなと思い、ちょうどアンケートをとりたかったので母集団に対しての必要サンプルサイズを計算してみました。
対象読者
・vbaに興味のある方
・アンケートの必要サンプルサイズを計算したい方
サンプルサイズとは
アンケートを取るにあたってサンプル数とサンプルサイズの違いで引っかかったので、調べた結果をアンケートに例えて次の表に記します。
| サンプルサイズ | 1回の標本抽出での調査人数。（一度のアンケートで調査した人数） |
|---|---|
| サンプル数 | 何回標本抽出を行ったか。(何回アンケートを行ったか。３日間ならサンプル数:3) |
計算式
まず、必要なサンプルサイズは次の式で計算できます。
$$ n=\frac{\frac{z^2 \times p(1-p)}{e^2}}{1+\frac{z^2 \times p(1-p)}{e^2N}}$$
| 項目名 | 説明 | |
|---|---|---|
| n | 必要なサンプルサイズ(人) | |
| N | 母集団の人数(人) | |
| z | Zスコア | 抽出サンプル（入手したアンケート）が許容誤差内になる確率 |
| p | 回答比率(%) | 通常のアンケートの場合分からないため、一般的な0.5で固定 |
| e | 許容誤差(%) | アンケート結果に±〇%外れる可能性があるという誤差の範囲 |
通常、許容誤差は1～10％、信頼レベルは90～99％。
計算式に当てはめる場合、n,Nの単位は(人)。
zスコアは次の通り、90%なら1.65を当てはめます。
許容誤差は±5%とするなら0.05のように小数点で表記します。
VBAのコード
先ほどの計算式を含めてVBAのコードを以下のように作成しました。
Sub sampleSize()
'アンケート調査時に必要なサンプルサイズ（回答数）の計算
Dim sampleSize As Double '必要サンプルサイズ
Dim N As Double '母集団
Dim z As Double '信頼レベル
Dim p As Double '回答比率
Dim e As Double '許容誤差
Dim i As Integer
Dim Denominator As Double '分母
Dim Numerator As Double '分子
For i = 4 To 13
N = Cells(i, 2).Value
z = Cells(i, 3).Value
p = Cells(i, 4).Value
e = Cells(i, 5).Value
If N <> 0 And z <> 0 And p <> 0 And e <> 0 Then
Numerator = ((z ^ 2) * (p * (1 - p))) / (e ^ 2)
Denominator = 1 + (((z ^ 2) * (p * (1 - p))) / ((e ^ 2) * N))
sampleSize = Numerator / Denominator
Cells(i, 6).NumberFormat = "0.00" '小数点以下2桁で表示
Cells(i, 6) = sampleSize
End If
Next i
End Sub
以下のようなシートを作成し、母集団、Zスコア、回答比率、許容誤差を入力して実行すると必要サンプルサイズの項目が自動で算出されるようになっています。1～10行目まで、自動で実行されます。
コードとしてはシンプルで、Dimで必要な変数を定義した後、For文で10回(10行分)繰り返しています。
繰り返しを行う際は、以下のようにN,z,p,eが空(0)の場合は計算せずスキップしています。ただ、今回は簡単に作成しただけのため、計算不能な値が入っていた場合にはじかれずエラーが出ます。
If N <> 0 And z <> 0 And p <> 0 And e <> 0 Then
実際の計算部分は以下で、見やすいように分子・分母に分けて計算しました。()が多いですが、適切に()をつけないと計算順序が変わってしまい計算結果が変わってしまいます。私も少しハマってしまいました。
Numerator = ((z ^ 2) * (p * (1 - p))) / (e ^ 2)
Denominator = 1 + (((z ^ 2) * (p * (1 - p))) / ((e ^ 2) * N))
sampleSize = Numerator / Denominator
最後に、必要サンプルサイズのセルを小数点以下二桁表示にして計算結果を表示しています。
Cells(i, 6).NumberFormat = "0.00" '小数点以下2桁で表示
Cells(i, 6) = sampleSize
あとがき
どれだけ母集団の数が大きくても、サンプルサイズは400に収束すると言われています。ですが、400人にアンケートを取るのは難しい状況だったため、対象の母集団やZスコア・許容誤差を調整して比較できたらなと思い作成してみました。vbaを思い出すには良い機会だったかなと思います。