Day 4

Excel Advent Calendar 2025

Day 5

中部大学新谷研究室 Advent Calendar 2025：サルコメア動態（HSOs/S4C）と Chaordic Homeodynamics Advent Calendar 2025

@Seine_A_Shintani(新谷正嶺)Team Coffeein

中部大学新谷研究室 / Shintani Lab, Chubu University

Excel関数でつくる「軽量データサイエンス環境」教育・研究の現場で本気で使うための設計メモ

Last updated at 2025-12-03Posted at 2025-12-03

「Excel Advent Calendar 2025」の記事です。
前回は .bas モジュールを使った VBA の話を書きましたが、今回は 「Excel関数だけでどこまで行けるか」 にフォーカスします。

対象はこんな方を想定しています。

学部・大学院の学生に「とりあえずデータ分析」を教えたい教員
研究室でアンケートや実験データを Excel で回している方
R / Python に本格的に乗り換える前に、Excel をもう一段活用したい方

TL;DR

Excel関数を「文法」として意識すると、一気に使える幅が広がる
基本関数 → 条件付き集計 → 検索・参照 → 動的配列 → LET / LAMBDA という流れで学ぶとスムーズ
教育・研究の現場だと、特に
- 成績管理・GPA計算
- アンケート集計
- 小規模実験データの前処理と可視化
  あたりで「Excelだけで完結する」範囲は意外と広い
動的配列関数 + LET / LAMBDA を組み合わせると、
「軽量なデータサイエンス DSL」としての Excel が見えてくる

1. Excel関数は「表形式DSL（ドメイン特化言語）」と考える

Excel関数をただの「便利な計算機」として見ると、

SUM、AVERAGE、IF、VLOOKUP あたりで止まりがち

ですが、視点を変えて

行と列に特化したドメイン特化言語（DSL）

と捉えると、設計の仕方が変わります。

行列の範囲を「データフレーム」のように扱う
関数を組み合わせてパイプラインを作る
最終的にグラフやピボットに渡す

という構造を意識すると、R や Python を見据えた教育にもつなげやすくなります。

この記事は、

基本集計
条件付き集計
検索・参照
動的配列
LET / LAMBDA での抽象化

という流れで、1つのデータセットを段階的に料理していくイメージで書いています。

2. 基本関数で「データの輪郭」をつかむ

例データ：授業アンケート + 成績

こんなテーブルがあるとします（A1:D11）。

学籍番号	テスト得点	レポート得点	満足度(1-5)
A001	80	70	4
A002	92	88	5
...	...	...	...

2.1 合計・平均・分散・標準偏差

代表的な基本関数：

=SUM(B2:B11)          ' 合計得点
=AVERAGE(B2:B11)      ' 平均
=VAR.S(B2:B11)        ' 標本分散
=STDEV.S(B2:B11)      ' 標本標準偏差
=MIN(B2:B11)          ' 最小値
=MAX(B2:B11)          ' 最大値

ここで 「母集団」か「標本」か も合わせて話しておくと、
統計教育としてちょうど良い題材になります。

Excelでは .P が母集団、.S が標本を意味します
VAR.P, STDEV.P など

2.2 GPA的な加重平均

レポートとテストを 4:6 で評価したい、という場面もよくあります。

= (B2*0.6 + C2*0.4)   ' 各学生の総合点（列Eなど）
=AVERAGE(E2:E11)      ' クラス平均

もう少し汎用的に書くなら、範囲 + 重み の形で：

=SUMPRODUCT(B2:C2, {0.6,0.4})

後で紹介する LET と組み合わせれば、
「重みを名前付き変数として定義しておく」ということもできます。

3. 条件付き集計で「セグメント」を見る

教育・研究現場では、「属性別に見る」が頻出です。

学部 / 学科 / 学年別
受講回数別（初回・再履修）
研究グループ別

など。

3.1 IF + AND/OR でシンプルな条件

学生を「合格/不合格」に分類する例：

=IF(E2>=60, "合格", "不合格")

テストもレポートも 60 以上なら「S」評価とする場合：

=IF(AND(B2>=60, C2>=60), "S", "A〜D")

Excelの IF は ラベル付け（カテゴリ変数の生成） として非常に強力です。
R / Python の mutate(case_when(...)) の感覚の入門にちょうどいい教材になります。

3.2 AVERAGEIF / COUNTIF で属性別集計

例えば列 F に「学科」が入っているとします。

学籍番号	テスト得点	レポート得点	満足度	学科
A001	80	70	4	物理
A002	92	88	5	情報
...	...	...	...	...

「物理学科の平均満足度」を求めるには：

=AVERAGEIF(F2:F101, "物理", D2:D101)

「満足度が 4 以上の人数」は：

=COUNTIF(D2:D101, ">=4")

複数条件があるときは *IFS 系を使います。

=AVERAGEIFS(D2:D101, F2:F101, "物理", B2:B101, ">=60")

これはまさに 条件付き集計（group-by の前段階） であり、
ピボットテーブルに渡す前のフィルタにも使えます。

4. 検索・参照関数で「テーブル同士を結合する」

データサイエンスの文脈でいう「結合 (join)」は、Excelだと
検索・参照関数 によって実現されます。

4.1 XLOOKUP で「行方向の結合」

最近の Excel であれば、VLOOKUP より XLOOKUP を推したいです。

例えば、別シートに「学生マスタ」がある場合：

学籍番号	学年	学科
A001	3	物理
A002	4	情報
...	...	...

アンケートデータ側の A2 に学籍番号が入っているとして、
学年を拾いたければ：

=XLOOKUP(A2, 学生マスタ!$A$2:$A$101, 学生マスタ!$B$2:$B$101)

第一引数：検索したい値（学籍番号）
第二引数：検索キーの列
第三引数：返したい列

列の位置ではなく「範囲」を直接指定するので、
列追加に対して壊れにくいのが大きな利点です。

4.2 INDEX + MATCH で「古い環境への配慮」

XLOOKUP がない環境でも、INDEX + MATCH で同様のことができます。

=INDEX(学生マスタ!$B$2:$B$101, MATCH(A2, 学生マスタ!$A$2:$A$101, 0))

MATCH：学籍番号が何行目かを返す
INDEX：指定した行の値を返す

これは R / Python でいう merge / join の感覚を
Excelで説明するときの良い導入になります。

5. 動的配列関数で「データフレームっぽく」扱う

ここから一気に現代的な Excel になります。
（Microsoft 365 や最近のバージョンが対象です）

5.1 FILTER で「where句」

特定学科のデータだけ抜き出したいとき：

=FILTER(A2:E101, F2:F101="物理")

第一引数：元のテーブル
第二引数：条件（TRUE/FALSEの配列）

結果は スピル（spill） と呼ばれる形で、
複数セルに一度に展開されます。

SQL でいうところの

SELECT * FROM table WHERE 学科 = '物理'

に相当します。

5.2 UNIQUE + SORT で「グループ一覧」

学科の種別だけ一覧でほしいとき：

=SORT(UNIQUE(F2:F101))

これだけで、例えば

物理
情報
化学
...

という一覧が縦に展開されます。
この一覧をもとに、AVERAGEIF と組み合わせると簡易 group-by もできます。

5.3 「学科別平均満足度」の1行完結版

動的配列関数を組み合わせると、
「学科一覧」と「学科別平均満足度」をまとめて出力できます。

列 H に学科一覧
列 I に平均満足度

を出したい場合、H2 に次の式を書くと：

=LET(
  groups, UNIQUE(F2:F101),
  scores, D2:D101,
  HStack(
    groups,
    BYROW(groups, LAMBDA(g,
      AVERAGEIF(F2:F101, g, scores)
    ))
  )
)

※ HSTACK, BYROW は最新の Excel の関数です

groups に学科一覧を保持
BYROW + LAMBDA で、各学科ごとに AVERAGEIF を呼び出す
HSTACK で「学科」と「平均満足度」を横にくっつける

1つの式が、小さな group-by 集計テーブルを返す イメージです。
ここまで来ると「ほぼデータフレーム操作」になってきます。

6. LET / LAMBDA で「Excelの関数型プログラミング入門」

動的配列 + LET + LAMBDA を使うと、
セルの中に「小さな関数定義」が書けるようになります。

6.1 LET で数式を読みやすくする

例えば、成績の z スコアを Excel 関数だけで計算するなら：

=(B2 - AVERAGE($B$2:$B$101)) / STDEV.S($B$2:$B$101)

と書けますが、平均と標準偏差を何度も書くのは冗長で、読みづらいです。
LET を使うと：

=LET(
  scores, $B$2:$B$101,
  x, B2,
  mu, AVERAGE(scores),
  sigma, STDEV.S(scores),
  (x - mu) / sigma
)

途中変数 scores, mu, sigma を導入
最後の行が「返す値」

R や Python のコードに近い見通しで説明できます。
統計教育で「標準化」の話をするときに、
数式との対応関係が見やすくなるのが地味にうれしいポイントです。

6.2 LAMBDA で「関数化」する

LET で書いた式を、そのままユーザー定義関数化できます。

=LAMBDA(x, scores,
  LET(
    mu, AVERAGE(scores),
    sigma, STDEV.S(scores),
    (x - mu) / sigma
  )
)

この式を「名前の管理」で ZScore という名前で登録しておくと、
セルから

=ZScore(B2, $B$2:$B$101)

と呼べるようになります。
VBA を使わずとも簡単な UDF を作れるので、

VBA はセキュリティ上使えない環境
学部生にはまだ VBA を教えていない授業

などで重宝します。

7. ちょっとしたデータサイエンス：相関・回帰・予測

Excel関数だけでも、簡単な統計モデリング くらいなら触れられます。

7.1 相関係数

テスト得点（列B）と満足度（列D）の相関係数：

=CORREL(B2:B101, D2:D101)

値が 1 に近い → 正の相関
値が -1 に近い → 負の相関
0 付近 → 相関なし

として、散布図と合わせて見せると
「Excelで相関の直感」を掴ませるのにちょうどよいです。

7.2 単回帰の係数と予測

テスト得点から満足度を予測する単回帰を組むなら、
LINEST（配列関数）を使います。

=LINEST(D2:D101, B2:B101, TRUE, TRUE)

これを スピル させると、

傾き（回帰係数）
切片
標準誤差
決定係数など

が表として得られます。

簡易に予測だけしたければ：

=FORECAST.LINEAR(新しい得点, 既存満足度範囲, 既存得点範囲)

=FORECAST.LINEAR(85, D2:D101, B2:B101)

のように「得点 85 のときの満足度の期待値」を出せます。

※ もちろん真面目な回帰分析は R / Python に譲るとして、
概念説明にはこれでも十分な場面が多いです。

8. データクレンジング：TRIM / TEXTSPLIT / SUBSTITUTE

研究室や授業で集まるデータは、きれいとは限りません。

余計なスペース
区切り文字付きの1列データ
全角・半角混在

などを Excel関数である程度まで整えられます。

=TRIM(A2)                 ' 前後と連続スペースを整理
=CLEAN(A2)                ' 制御文字を削除
=SUBSTITUTE(A2, "　", " ") ' 全角スペースを半角に
=TEXTSPLIT(A2, ",")       ' カンマ区切りを列に分解

TEXTSPLIT で分解 → FILTER / UNIQUE で整理 → TEXTJOIN で再結合
のようなフローを作れば、簡単な文字列前処理パイプラインが組めます。

9. 「授業1コマで触れられる最小セット」を考える

教育の視点から見ると、「1コマでどこまでやるか」の設計が重要です。

9.1 初回導入で触れたい最小セット

集計：SUM, AVERAGE, MIN, MAX
条件：IF, AVERAGEIF, COUNTIF
検索：XLOOKUP または INDEX + MATCH
動的配列（可能なら）：FILTER, UNIQUE
基本統計：STDEV.S, CORREL

これだけでも、

「実験データの基本統計」
「アンケートの基礎集計」
「属性別集計」

までは到達できます。

9.2 2回目以降での発展

LET で数式を整える
LAMBDA で簡単な UDF を作る
LINEST / FORECAST.LINEAR で回帰の入り口
TEXTSPLIT / SUBSTITUTE で文字列前処理

を少しずつ差し込んでいくと、
R / Python のコードにスムーズにつなげやすくなります。

10. まとめ：Excel関数は「最初のデータサイエンス言語」になりうる

この記事では、Excel関数を

集計
条件付き集計
検索・参照
動的配列
LET / LAMBDA
簡単な統計モデリング

という流れで眺め直してみました。

ポイントを改めて整理すると：

Excel関数は、単なる「計算のショートカット」ではなく
表形式データに特化したプログラミング言語 として扱える
動的配列 + LET + LAMBDA を使うと、
小ぶりながらも実用的な「データ処理パイプライン」が組める
教育・研究の現場では、
- 成績管理・アンケート
- 小規模実験データの解析
  など、かなりの範囲を「Excelだけ」で完結させることができる

そして、Excelでこうした処理に慣れておくと、

「行列を意識してデータを考える」
「関数をパイプラインとしてつなぐ」

といった素地ができ、
R / Python / SQL などへのスムーズな橋渡しにもなります。

Excel Advent Calendar 2025 では、他の方の記事でも
FILTER / LET / LAMBDA / Python連携 / VBA など、
さまざまな角度から Excel の可能性が掘られていると思います。

本記事が、教育・研究で Excel関数をもう一歩だけ踏み込んで使ってみよう、
と思っていただくきっかけになれば幸いです。

（「こういう関数組み合わせの例も見たい」「この授業での使い方を考えたい」などあれば、コメントでぜひ教えてください）

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Excel関数でつくる「軽量データサイエンス環境」 教育・研究の現場で本気で使うための設計メモ