Textbook

DS基礎教科書

統計検定データサイエンス基礎(DS基礎)は、Excel を使った実データ操作を中心に問う検定です。本教科書では、Excel の関数・ピボットテーブル・グラフ・回帰機能 ─ DS基礎で求められる主要なスキルを、操作手順を意識しながら整理していきます。Excel をある程度触ったことのある方を想定します。

第 1 章 · Excel とデータ操作の基礎
セル・関数・テーブル機能・ピボットテーブルなど、データ分析の土台となる Excel 操作を整理します。
第 2 章 · 可視化と分析
Excel のグラフ機能と回帰分析機能を使い、データを「見て」「予測する」段階に進みます。

Chapter 1

第 1 章 · Excel とデータ操作の基礎

§1.1

Excel の基本操作とテーブル機能

DS基礎の出題は、ほとんどが「Excel でデータを操作して答えを出す」スタイル。本節ではまず、効率的に操作するための基本 ─ セル参照のコツとテーブル機能を整理します。

絶対参照と相対参照

覚えどころ

Excel でセルを参照する 3 つの形式:

- A1(相対参照): コピーすると参照先が動く - $A$ 1(絶対参照): コピーしても固定 - $A 1/ A$ 1(複合参照): 行だけ・列だけ固定

F4 キーを押すと参照形式が切り替わる。

「ある列の値を、固定の値で割って割合を計算する」のような操作では絶対参照が必須。手で式を入力していると間違えやすいので、$ マークを押すクセをつけましょう。

テーブル機能

データ範囲を選んで「Ctrl + T」(または「挿入」→「テーブル」)で、データ範囲を テーブル化 できます。テーブル化のメリットは大きいです。

データを追加すると数式が自動で拡張: 新しい行を足しても、SUM や AVERAGE が自動で範囲を広げる
列名で参照可能: A1:A100 ではなく `テーブル1[売上]` のような構造化参照が使える
フィルタとソートが組み込み: 1 クリックで絞り込み
行の縞模様が自動: 視認性が上がる

頻出関数の一覧

DS基礎で必ず使う関数

集計系 - `SUM(範囲)` 合計 - `AVERAGE(範囲)` 平均 - `COUNT(範囲)` 数値の個数 - `COUNTA(範囲)` 空でない個数 - `MAX / MIN(範囲)` 最大・最小

条件付き集計 - `SUMIF(条件範囲, 条件, 合計範囲)` 条件付き合計 - `COUNTIF(範囲, 条件)` 条件付きカウント - `AVERAGEIF(条件範囲, 条件, 平均範囲)` 条件付き平均

統計 - `STDEV.S(範囲)` 標本標準偏差(不偏) - `STDEV.P(範囲)` 母集団標準偏差 - `VAR.S / VAR.P` 不偏分散・標本分散 - `MEDIAN(範囲)` 中央値 - `QUARTILE.INC(範囲, 番号)` 四分位数 - `CORREL(X, Y)` 相関係数

検索・参照 - `VLOOKUP / XLOOKUP` 表からの検索 - `IF / IFS` 条件分岐

DS基礎の出題ではこれらの関数の 使い分け が問われます。「不偏分散と標本分散」「STDEV.S と STDEV.P」のような違いを意識して使えるよう、繰り返し触っておきましょう。

実務での使い方:VLOOKUP / XLOOKUP でデータ結合

実務でいちばん使われる関数の一つが VLOOKUP / XLOOKUP(SQL の JOIN に相当)。例: 「顧客 ID から顧客名を引いてくる」「商品コードから単価マスタを引いてくる」など。`=VLOOKUP(A2, 顧客マスタ!A:C, 3, FALSE)` で、A2 の値を顧客マスタの 1 列目で検索 → 3 列目の値を返す、という使い方が定型。新しい Excel(2021/365)では、左にも検索できる XLOOKUP が VLOOKUP の上位互換として推奨されています。

§1.2

関数を使った集計

本節では、データセットに対して 集計操作 を関数で行う方法を扱います。手で書ける式は手で、難しいものはピボットテーブル(次節)へ。

条件付き集計 ─ 「〇〇な行だけの合計を出したい」

SUMIF / COUNTIF / AVERAGEIF

条件にマッチする行だけを対象に、合計・カウント・平均を計算する。

例: 売上テーブルで「東京の売上の合計」を求めたいとき

`=SUMIF(B:B, "東京", C:C)`

(B 列の支店名、C 列の売上)

例題 1.2

売上テーブル(支店、売上額)で、「東京」支店の売上の合計と平均、件数を計算したい。それぞれ使う関数は?

解 : - 合計: `=SUMIF(支店列, "東京", 売上列)` - 平均: `=AVERAGEIF(支店列, "東京", 売上列)` - 件数: `=COUNTIF(支店列, "東京")`

複数条件 ─ SUMIFS / COUNTIFS / AVERAGEIFS

条件が複数になったら、語尾に S を付けた `SUMIFS` 等を使います。引数の順序が SUMIF とは逆なので注意。

SUMIFS の構文

`=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)`

例: 「東京 × 4 月」の売上合計

`=SUMIFS(C:C, B:B, "東京", A:A, "4月")`

比率や割合の計算

「東京支店の売上が全体に占める割合」を計算したいとき:

割合 = \frac{東京の売上合計}{全体の売上合計}

Excel 式では `=SUMIF(B:B,"東京",C:C) / SUM(C:C)`。表示形式を「パーセンテージ」にすれば自動で % 表示になります。

ばらつきの集計

STDEV.S と STDEV.P の使い分け は DS基礎で頻出。

STDEV.S(範囲): 標本標準偏差(不偏)。分母 $n - 1$ 。「データが母集団からの標本」のとき。
STDEV.P(範囲): 母集団標準偏差。分母 $n$ 。「データそのものが母集団全体」のとき。

実務では「データは母集団からの標本」と考える場面が多いので、原則 STDEV.S を使います。「.P」を選ぶと結果が少しだけ小さくなります(分母が大きいぶん)。

§1.3

ピボットテーブルでクロス集計

「商品別 × 月別の売上」「都道府県別 × 性別の人数」 ─ 2 つ以上の軸でデータを集計するクロス集計は、DS基礎の主役機能のひとつです。Excel ではピボットテーブルで一発。

ピボットテーブル =『データキューブの薄切り』

1 行 1 レコードのフラットなデータは、概念的には『多次元のデータキューブ』を平らに展開したもの。ピボットテーブルは、そのキューブをどの軸で切るかを GUI で指定し、別の薄切り(集計)を作る道具です。BI ツール(Tableau・Looker)も内部的には同じ操作を高速にやっているだけで、根っこは Excel と同じ。

実務での使い方:売上分析の定石

毎月の売上データを取り込んだら、まず 行=支店 / 列=商品カテゴリ / 値=売上合計 のピボットを作るのが定石。次に値を『合計』から『前月比』『前年比』に切り替え、列に時系列を加えて推移を見る ─ この 2 ステップだけで、社内会議で共有できる売上分析レポートの 7 割は完成します。

ピボットテーブルの基本手順

データを準備: 1 行 1 レコード形式の表。列名(ヘッダ)が必須。
範囲を選択: データのどこかをクリック → 「挿入」→「ピボットテーブル」
配置場所を選択: 新しいシート(推奨)または既存のシート
フィールドを配置: 「行」「列」「値」のボックスに列名をドラッグ
集計方法を選択: 値ボックスのドロップダウンで合計・平均・カウントなどを切替

「行・列・値・フィルタ」の配置

例 ─ 売上の月別×支店別クロス集計

行: 月列: 支店値: 売上(合計)

これで「行 = 月、列 = 支店、セルにその月・その支店の売上合計」 ─ という典型的なクロス集計表ができる。

値フィールドの集計方法

値ボックスに置いたフィールドをクリックすると、集計方法を切り替えられます。

合計: SUMIFS と同等
個数: COUNT。「何件あるか」
平均 / 最大 / 最小 / 標準偏差 など
合計に対する割合: クロス集計表を「割合」表示に。「行の合計に対する割合」「列の合計に対する割合」「総計に対する割合」が選べる

「割合 % 表示」の使い分け

DS基礎では、ピボットテーブルの値表示を「○○の割合」にする操作がよく問われます。

行集計に対する割合: 各行の中での横方向の構成比。例: 「ある月の中で、各支店の売上が何 % か」
列集計に対する割合: 各列の中での縦方向の構成比。例: 「ある支店の中で、各月の売上が何 % か」
総計に対する割合: 全体に対する各セルの割合

フィルタとスライサー

フィルタ ボックスにフィールドを置くと、その列の特定の値だけに絞った表が作れます。スライサー(挿入 → スライサー)を使うと、ボタンクリックで絞り込みができ、よりインタラクティブな分析が可能になります。

ピボットテーブルは触れば触るほど分かる機能。サンプルデータをダウンロードして、実際に手で動かしてみるのがいちばんの学習法です。

Chapter 2

第 2 章 · 可視化と分析

§2.1

グラフの作り方と選び方

DS基礎では「目的に合ったグラフを選べるか」がよく問われます。本節では、Excel で作れる主要グラフと、それぞれの 使い分けの原則 を整理します。

Excel でのグラフ作成手順

データ範囲を選択(ヘッダ行を含めると凡例が自動生成される)
「挿入」タブ → グラフの種類を選択(おすすめグラフから選んでも OK)
グラフの要素を整える: タイトル・軸ラベル・凡例・データラベル
書式を整える: 色・線の太さ・フォント

目的別グラフ選びの早見表

目的とグラフの対応

| 見たい情報 | おすすめグラフ | |---|---| | カテゴリの大きさを比較 | 棒グラフ | | 時間に沿った変化 | 折れ線グラフ | | 全体に占める割合 | 円グラフ(項目少なめ)/ 帯グラフ | | 1 変数の分布形 | ヒストグラム | | 2 変数の関係 | 散布図 | | 群間のばらつき比較 | 箱ひげ図 | | 2 次元のクロス集計 | ヒートマップ |

ヒストグラムの作り方(2016 以降)

ヒストグラムは Excel 2016 以降、ネイティブ機能として用意されています(それ以前は階級分けを手で計算する必要があった)。

データ列を選択
「挿入」→「グラフ」→「ヒストグラム」
横軸の階級幅(ビン幅)を必要に応じて調整(軸の書式設定)

散布図に近似曲線を入れる

DS基礎で頻出: 散布図に 近似曲線(線形近似) を入れて回帰直線を視覚的に確認する方法。

散布図を作成
データ点を右クリック → 近似曲線の追加
「線形近似」を選択、「グラフに数式を表示」「 $R^{2}$ 値をグラフに表示」をチェック

これで「 $y = a x + b$ 」と $R^{2}$ がグラフに表示されます。回帰係数と決定係数を一目で確認できる便利機能。

やってはいけないグラフ

3D 効果: 立体グラフは見た目より値が読み取りにくい。原則使わない
0 から始まらない縦軸: 棒グラフでは特に。差を誇張するトリック
項目数の多い円グラフ: 5 項目を超えると比較困難。棒グラフに切り替え
色だけに頼る凡例: モノクロ印刷や色覚多様性を配慮し、形やパターンも併用

グラフは『1 枚に 1 メッセージ』

良いグラフの共通点は『何を伝えたいか』が一目でわかること。1 枚のグラフに『売上の月別推移 + 商品別構成 + 目標達成率』を全部詰め込むと、結局何も伝わりません。実務でも『1 グラフ 1 メッセージ』を守ると、レポートの説得力が一気に上がります。タイトルを『○○が△△だった』のような 結論の文 にすると、見る人が一瞬で要点を掴めます。

§2.2

回帰分析機能を使う

Excel には 「データ分析」アドイン があり、単回帰・重回帰を GUI で実行 できます。本節では、その有効化から結果の読み方までをまとめます。

「データ分析」アドインの有効化

「ファイル」→「オプション」→「アドイン」
下部「設定」→「分析ツール」にチェック → OK
「データ」タブの右端に「データ分析」が表示される

単回帰の実行手順

「データ」→「データ分析」→「回帰分析」を選択
「入力 Y 範囲」: 目的変数の列(ラベル含む)
「入力 X 範囲」: 説明変数の列(ラベル含む)
「ラベル」にチェック(ヘッダ行を含めた場合)
「一覧の出力先」を指定して OK

出力結果の読み方

回帰分析の出力は 3 つのブロック に分かれます。

出力ブロック 1 ─ 回帰統計

重相関 R: 相関係数の絶対値 重決定 R²: 決定係数 ─ モデルの説明力(0〜1) 補正 R²: 自由度調整済み決定係数 標準誤差: 残差のばらつき 観測数: サンプルサイズ $n$

出力ブロック 2 ─ 分散分析(ANOVA)

有意 F: F 検定の p 値。これが $0.05$ 未満なら「モデル全体は統計的に有意」と判定。

出力ブロック 3 ─ 係数

各係数について: 係数値・標準誤差・t 値・p 値・信頼区間 が出力される。

- p 値 < 0.05: その説明変数は有意 - 係数値: $x$ が 1 単位増えたときの $y$ の変化量

Excel 回帰出力を読む 3 ステップ

1. 有意 F を見る(全体として意味のあるモデルか) → 2. 補正 R² を見る(どれだけ説明できているか) → 3. 各係数の p 値を見る(どの変数が効いているか)。この順で見れば、初学者でも回帰結果を素早く解釈できます。注意: $R^{2}$ が高くても有意 F や個別 p 値が高ければ『偶然のフィット』の可能性。 $R^{2}$ だけで判断しないのが鉄則です。

重回帰の場合

重回帰も手順は同じ。違いは「入力 X 範囲」を 複数列 にする(隣接した列であること)。各説明変数ごとに「係数・p 値」が出力されるので、どの変数が有意かを確認できます。

実務での注意点

説明変数の単位: スケールが大きく違うと係数の解釈がしにくい。標準化してから回帰を考える
多重共線性: 説明変数同士の相関が高いと、係数が不安定になる。VIF を別途計算するか、相関係数を確認
残差の確認: グラフで残差プロット([データ分析]オプション)を出力 → ランダムに散らばっているか確認

§2.3

Excel での統計的推測

Excel には統計的推測(信頼区間・仮説検定)のための関数が揃っています。本節では DS 基礎で頻出のものを整理します。

実務での使い方:キャンペーン効果測定

「先月のメールキャンペーンで売上が上がったか?」を判断するとき、キャンペーン前と後の各店舗売上を 2 列に並べて `=T.TEST(B2:B30, C2:C30, 2, 1)` を実行 → 返された p 値が 0.05 未満なら「有意な差あり」と報告書に書けます。Excel だけでこの判定ができるのが、DS 基礎が実務に直結するポイント。

信頼区間

関数 ─ 信頼区間の幅

`CONFIDENCE.NORM(α, σ, n)`: 母分散 σ² 既知の場合の信頼区間の幅(片側)。例: `=CONFIDENCE.NORM(0.05, 10, 100)` で 95% 信頼区間の半幅。

`CONFIDENCE.T(α, s, n)`: 母分散未知(不偏分散 s² を使う)の場合の信頼区間の幅(片側、t 分布版)。

信頼区間は「標本平均 ± 戻り値」で得られる。

1 標本 t 検定

「ある母集団の平均が値 $μ_{0}$ と等しいか」を検定したいとき、Excel には専用関数がないので、以下のいずれかの方法で実行します。

手動計算: 検定統計量 $T = (\overset{ˉ}{X} - μ_{0}) / (\overset{s}{^} / n)$ 、p 値は `=T.DIST.2T(ABS(T), n-1)` で両側
データ分析アドイン: 「t 検定: 一対の標本による平均の検定」(2 標本対応形式)

2 標本 t 検定(2 群の平均比較)

関数 ─ T.TEST

`T.TEST(範囲1, 範囲2, 尾, 種類)`

- 尾: 1=片側、2=両側 - 種類: 1=対応あり、2=等分散の独立 2 標本、3=等分散でない独立 2 標本(Welch 検定)

例: `=T.TEST(A1:A10, B1:B10, 2, 3)` → 等分散仮定なしの両側 t 検定の p 値が返る

カイ二乗検定

関数 ─ CHISQ.TEST

`CHISQ.TEST(観測度数の範囲, 期待度数の範囲)`

適合度検定または独立性検定の p 値を返す。両者は同じ関数で実行可能 ─ 期待度数の出し方が変わるだけ。

分割表の独立性検定: 1. 観測度数表(クロス集計)を作る 2. 行計・列計から期待度数表を計算: $E_{ij} = (行 i 計) \times (列 j 計) / 総計$ 3. `CHISQ.TEST(観測, 期待)` で p 値

p 値の判定基準

p 値 < 0.05: 有意水準 5% で帰無仮説を棄却
p 値 < 0.01: 有意水準 1% で棄却(より強い証拠)
p 値 ≥ 0.05: 棄却しない(=「H₀ が真」とは言えないが、棄却の証拠不十分)

実務での Excel 統計の限界

Excel は基本的な検定までは便利ですが、本格的な統計分析(ロジスティック回帰・時系列分析・ベイズ推論など)では Python(statsmodels, scikit-learn) や R に切り替えるのが標準的です。DS基礎では「Excel で何ができるか」を押さえ、それ以上は他ツールへ、という棲み分けが現実的。

目次