データサイエンティストのためのExcelデータ分析

Excel の基本操作、関数、ピボットテーブル、グラフ、線形回帰、ソルバー、VBA までを、実務で使う順番に沿って整理した入門ノートです。SQL で抽出したデータを Excel に渡した後、どう整え、どう集計し、どう説明し、どこまで自動化できるかを 1 本で見直せる構成にしています。

0. 導読

0.1 記事の目的

この記事の目的は、Excel をデータ分析の現場で使う実務ツールとして理解することです。特に次の流れを一通り押さえることを目標にします。

1. Excel が分析実務でどこに位置づくかを理解する。
2. セル参照、数式、基本関数で表を正しく作る。
3. 検索関数、条件分岐、条件集計でデータを整える。
4. グラフとピボットテーブルで結果を読み、伝える。
5. 相関分析と線形回帰で変数の関係を確認する。
6. ソルバーで小規模な最適化を試す。
7. マクロと VBA で繰り返し作業を自動化する。

0.2 最初に優先して覚えたいこと

入門段階で先に押さえるなら、次の項目が優先です。

1. 相対参照と絶対参照
2. VLOOKUP と XLOOKUP
3. IF、AND、OR
4. SUMIFS、COUNTIFS、AVERAGEIFS
5. LEFT、RIGHT、LEN、TRIM、REPLACE
6. ピボットテーブルの行、列、値、フィルター
7. CORREL と線形回帰の基本的な読み方
8. ソルバーの目的セル、変数セル、制約条件
9. マクロ記録と .xlsm 保存

0.3 読み進め方

本稿は、次の順で読むと理解しやすくなります。

1. まず 1 章と 2 章で、Excel の役割と数式の土台をつかむ。
2. 次に 3 章から 5 章で、実務でよく使う関数を具体例と一緒に確認する。
3. その上で 7 章と 8 章を読み、可視化と集計の流れを押さえる。
4. 最後に 9 章から 11 章で、分析、最適化、自動化の入口を見る。

---

1. なぜ Excel を学ぶのか

1.1 Excel の役割

Excel は、分析専用ツールではありません。しかし、データ分析の実務では非常に長く使われ続けています。理由は大きく三つあります。

1. ほとんどの職場にあり、共有しやすい。
2. 関数、グラフ、ピボットテーブル、簡単な自動化まで 1 つの画面で行える。
3. SQL や Python の結果を受け取って、最終的な確認や説明に回しやすい。

1.2 SQL、Python、BI ツールとの分担

Excel は、他ツールと分担して使うと最も力を発揮します。

1. SQL：大きな表から必要な行と列を取り出す。
2. Excel：抽出後のデータを照合し、整え、集計し、説明資料へつなぐ。
3. Python / R：大規模処理や複雑な分析、再現性の高い処理を行う。
4. BI ツール：継続的な可視化やダッシュボード化を行う。

つまり Excel は、「データを使う最後の数メートル」で特に強い道具です。

1.3 Excel が向く場面と向かない場面

1.3.1 Excel が向く場面

1. 数万行規模のデータを確認しながら加工したいとき。
2. 突合、条件付け、ラベル付けを短時間で行いたいとき。
3. ピボットテーブルで切り口を変えながら探索したいとき。
4. 社内向けの報告資料を素早く作りたいとき。

1.3.2 Excel が向かない場面

1. 数百万行を超える大規模データをそのまま処理したいとき。
2. 厳密なバージョン管理や共同開発が必要なとき。
3. 複雑な機械学習や本格的な統計モデリングが主役のとき。

Excel は万能ではありませんが、分析の初動、整形、共有、説明の段階では非常に強力です。

---

2. Excel の基本構造と数式の土台

2.1 Excel の基本構造

2.1.1 ブック、ワークシート、セル

1. ブック：Excel ファイル全体
2. ワークシート：ブックの中の各ページ
3. セル：行と列が交わる 1 マス

たとえば B3 は、B 列と 3 行目の交点にあるセルです。

2.1.2 行数と列数の上限

1. 行数上限：1,048,576 行
2. 列数上限：16,384 列（A から XFD）

この上限を知っておくと、Excel で受けるべきか、SQL 側でさらに集約すべきかを判断しやすくなります。

2.2 数式の基本

2.2.1 数式は = から始まる

=A1
=1+1
=SUM(1,2,3)

2.2.2 よく使う演算子

1. +：加算
2. -：減算
3. *：乗算
4. /：除算
5. ^：累乗
6. ()：優先順位の調整
7. &：文字列連結

=(B2-C2)/C2
=A2&"_"&B2
=2^3

2.3 参照の考え方

2.3.1 相対参照と絶対参照

1. A1：行も列も動く
2. $A$1：行も列も固定する
3. $A1：列だけ固定する
4. A$1：行だけ固定する

2.3.2 絶対参照の具体例

税率が F1 に入っていて、各商品の税込金額を計算する例です。

=E2*(1+$F$1)

この式を下へコピーしても、$F$1 は固定されたままです。共通の定数を参照するときは、絶対参照を使わないとずれやすくなります。

2.4 表を見やすくする基本操作

2.4.1 ウィンドウ枠の固定

表頭やキー列を見失わないために、表示 -> ウィンドウ枠の固定 を使います。日別売上、会員一覧、商品マスタのような長い表では特に重要です。

2.4.2 表示形式の調整

1. 金額には桁区切りを付ける。
2. 比率は % 表示にする。
3. 小数点の桁数をそろえる。

2.4.3 列幅の自動調整

列境界をダブルクリックすると、その列の内容に合う幅へ自動調整できます。共有前に必ず行いたい基本操作です。

---

3. 検索・参照関数

3.1 VLOOKUP の基本

3.1.1 VLOOKUP の役割

VLOOKUP は、キーを使って別表から対応する値を引く関数です。商品コードから商品名や単価を引く、会員 ID から会員ランクを引く、といった場面でよく使います。

3.1.2 基本構文

VLOOKUP(検索値, 範囲, 列番号, 検索方法)

1. 検索値：探したいキー
2. 範囲：検索表全体
3. 列番号：返したい列が範囲の左から何列目か
4. 検索方法：FALSE は完全一致、TRUE は近似一致

3.1.3 完全一致の具体例

注文表の A2 に商品コードがあり、商品マスタが F2:H6 にあるとします。

F列	G列	H列
商品コード	商品名	単価
P001	お茶	120
P002	水	100
P003	コーヒー	150

商品名を引く式は次の通りです。

=VLOOKUP(A2,$F$2:$H$6,2,FALSE)

単価を引くなら、返却列番号を 3 に変えます。

=VLOOKUP(A2,$F$2:$H$6,3,FALSE)

3.1.4 売上金額まで計算する例

数量が B2 に入っているなら、単価を引いた上で売上金額まで一気に出せます。

=VLOOKUP(A2,$F$2:$H$6,3,FALSE)*B2

3.1.5 近似一致の具体例

評価表が J2:K5 にあり、次のように下限点数で管理されているとします。

J列	K列
点数下限	評価
0	D
60	C
80	B
90	A

得点が C2 にあるなら、近似一致で評価を返せます。

=VLOOKUP(C2,$J$2:$K$5,2,TRUE)

この場合、下限値の表は 昇順 に並んでいる必要があります。

3.1.6 VLOOKUP の注意点

1. 検索列は範囲の一番左に必要です。
2. 列番号指定なので、途中で列を挿入すると壊れやすいです。
3. 完全一致で使うなら第 4 引数に FALSE を明示するほうが安全です。

3.2 XLOOKUP の基本

3.2.1 XLOOKUP の役割

XLOOKUP は、VLOOKUP より柔軟な新しい検索関数です。左方向への参照や、未一致時の文言指定がしやすいのが強みです。

3.2.2 基本構文

XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致方法, 検索方法)

3.2.3 完全一致の具体例

=XLOOKUP(A2,$F$2:$F$6,$G$2:$G$6,"未登録",0)

この式は、A2 の商品コードを F2:F6 から探し、対応する G2:G6 の商品名を返します。見つからないときは "未登録" を返します。

3.2.4 左方向参照の例

商品名から商品コードを返したい場合は、次のように書けます。

=XLOOKUP(B2,$G$2:$G$6,$F$2:$F$6,"該当なし",0)

VLOOKUP では面倒な左方向参照も、XLOOKUP なら自然に書けます。

3.2.5 VLOOKUP と XLOOKUP の使い分け

1. 既存ファイルとの互換性を重視するなら VLOOKUP。
2. 新しく作る表で柔軟性を重視するなら XLOOKUP。
3. 未一致時の扱いを明確にしたいなら XLOOKUP。

3.3 INDEX と MATCH

XLOOKUP が使えない環境では、INDEX と MATCH の組み合わせも重要です。

=INDEX($H$2:$H$6,MATCH(A2,$F$2:$F$6,0))

これは、A2 の商品コードが F2:F6 の何番目かを調べ、その位置の H2:H6 を返す式です。

---

4. 条件判断と条件集計

4.1 IF、AND、OR

4.1.1 IF の基本

IF(条件, 真の場合, 偽の場合)

たとえば月間売上が C2 にあり、目標が 100,000 円なら次のように書けます。

=IF(C2>=100000,"達成","未達")

4.1.2 AND の具体例

地域が東日本で、かつ売上が 100,000 円以上の店舗を重点店舗にしたい場合です。

=IF(AND(B2="東日本",C2>=100000),"重点","通常")

4.1.3 OR の具体例

会員である、または購入回数が 3 回以上ならフォロー対象にする場合です。

=IF(OR(D2="会員",E2>=3),"フォロー対象","一般")

4.1.4 IF を入れ子にする例

点数を 3 段階で判定するなら、次のように書けます。

=IF(B2<60,"要再学習",IF(B2<80,"合格","優秀"))

4.2 SUMIF と SUMIFS

4.2.1 単一条件の合計

B列 = 地域、D列 = 売上 の表で、東日本の売上合計を出す例です。

=SUMIF($B$2:$B$100,"東日本",$D$2:$D$100)

4.2.2 複数条件の合計

地域が東日本、かつカテゴリが飲料の売上合計なら次のように書けます。

=SUMIFS($D$2:$D$100,$B$2:$B$100,"東日本",$C$2:$C$100,"飲料")

4.2.3 日付条件を含む例

4 月分だけを合計したいなら、日付条件を 2 つ並べます。

=SUMIFS($D$2:$D$100,$A$2:$A$100,">=2024/4/1",$A$2:$A$100,"<=2024/4/30")

4.3 COUNTIF、COUNTIFS、AVERAGEIF、AVERAGEIFS

4.3.1 COUNTIF の例

=COUNTIF($B$2:$B$100,"東日本")

東日本の注文件数を数える式です。

4.3.2 COUNTIFS の例

=COUNTIFS($B$2:$B$100,"東日本",$F$2:$F$100,"会員")

東日本かつ会員の件数を数える式です。

4.3.3 AVERAGEIF の例

=AVERAGEIF($B$2:$B$100,"東日本",$E$2:$E$100)

東日本の平均客単価を出せます。

4.3.4 AVERAGEIFS の例

=AVERAGEIFS($E$2:$E$100,$B$2:$B$100,"東日本",$F$2:$F$100,"会員")

東日本かつ会員の平均客単価を出す式です。

4.4 SUMPRODUCT

SUMPRODUCT は、配列同士を掛け合わせて合計する関数です。単価と数量から総売上を一気に出すような場面で便利です。

=SUMPRODUCT(B2:B6,C2:C6)

この式は、各行の 単価 × 数量 を計算して合計しています。

---

5. 文字列処理と補助関数

5.1 文字列連結

5.1.1 & と CONCATENATE

店舗名と地域をまとめて表示したい場合です。

=A2&"店（"&B2&"）"

=CONCATENATE(A2,"店（",B2,"）")

入門段階では & のほうが短く、読みやすく感じることが多いです。

5.2 LEFT、RIGHT、LEN

5.2.1 LEFT の例

商品コードの先頭 3 文字を取り出す例です。

=LEFT(A2,3)

5.2.2 RIGHT の例

会員番号の末尾 4 桁を取り出す例です。

=RIGHT(A2,4)

5.2.3 LEN の例

コード長が想定どおりか確認したい場合です。

=LEN(A2)

5.3 TRIM と REPLACE

5.3.1 TRIM の例

CSV を貼り付けた直後の余分な空白を削除するなら次の式です。

=TRIM(B2)

5.3.2 REPLACE の例

電話番号の中央だけを伏せ字にする例です。

=REPLACE(C2,4,4,"****")

5.4 文字列処理の実務例

5.4.1 電話番号マスキング

=LEFT(C2,3)&"****"&RIGHT(C2,4)

5.4.2 連番作成に ROW を使う

=ROW()-1

2 行目から始まる表なら、1、2、3 と連番が作れます。

---

6. 統計の基本と Excel 実装

6.1 代表値の理解

6.1.1 平均値

=AVERAGE(B2:B11)

6.1.2 中央値

=MEDIAN(B2:B11)

外れ値が強いデータでは、平均より中央値のほうが実態に近いことがあります。

6.1.3 最頻値

=MODE.SNGL(B2:B11)

6.2 分散と標準偏差

6.2.1 標準偏差

=STDEV.P(B2:B11)

標準偏差は、値が平均の周辺にどれくらい散らばっているかを見る指標です。

6.2.2 平均から何標準偏差離れているか

=(B2-AVERAGE($B$2:$B$11))/STDEV.P($B$2:$B$11)

このように標準化すると、単位が違う指標でも比較しやすくなります。

6.3 中心極限定理の直感

中心極限定理は、「元の分布が少し歪んでいても、標本平均を何度も取ると、その平均の分布は正規分布に近づきやすい」という考え方です。入門段階では、平均比較や信頼区間の背景にある直感として押さえておけば十分です。

---

7. グラフによる可視化の基本

7.1 グラフ作成の前に整理すること

グラフは、見た目を整える前に「何を見せたいか」を決めることが重要です。先に決めるべき観点は次の四つです。

1. 比較：どの店舗、どの商品が大きいかを比べたいのか。
2. 推移：月次や日次でどう変化したかを見たいのか。
3. 構成比：全体の中で何がどれくらい占めるかを見たいのか。
4. 関係性：二つの変数がどう関係するかを見たいのか。

目的が定まると、使うべきグラフも決まりやすくなります。

7.2 棒グラフ

7.2.1 棒グラフで何が分かるか

棒グラフは、カテゴリ同士の大小比較に最も向いています。たとえば、店舗別売上、商品カテゴリ別注文件数、地域別利益の比較では第一候補になります。

7.2.2 具体例

「5 店舗の月間売上」を比較したいとします。列は次の二つです。

1. A列：店舗名
2. B列：月間売上

7.2.3 作成手順

1. A1:B6 の範囲を選択する。
2. 挿入 -> 縦棒 / 横棒グラフ を選ぶ。
3. グラフタイトルを 店舗別月間売上 に変更する。
4. 必要ならデータラベルを表示する。
5. 売上の大きい順に元表を並べ替えて見やすくする。

7.2.4 得られる効果

棒グラフにすると、どの店舗が上位で、どの店舗が下位かを一目で把握できます。レポートで「比較」を示したいときに非常に強い形式です。

7.3 折れ線グラフ

7.3.1 折れ線グラフで何が分かるか

折れ線グラフは、時系列の変化を追うのに適しています。月次売上、日次アクセス数、在庫推移、広告費の推移などで使います。

7.3.2 具体例

「1 月から 12 月までの売上推移」を見たいとします。

1. A列：月
2. B列：売上

7.3.3 作成手順

1. A1:B13 を選択する。
2. 挿入 -> 折れ線グラフ を選ぶ。
3. 横軸が月、縦軸が売上になっているか確認する。
4. ピーク月を強調したいならデータラベルを追加する。
5. 必要なら目標値の系列を追加して実績比較を行う。

7.3.4 得られる効果

売上が右肩上がりか、季節性があるか、急に落ち込んだ月があるかをすぐ確認できます。推移の説明に最も向いているグラフです。

7.4 円グラフ

7.4.1 円グラフで何が分かるか

円グラフは、全体に対する構成比をざっくり把握するときに使います。ただし、カテゴリ数が多いと読みづらくなるため、3 から 5 項目程度に絞るのが基本です。

7.4.2 具体例

「売上全体に占める商品カテゴリ構成比」を確認する場合です。

1. A列：カテゴリ名
2. B列：売上

7.4.3 作成手順

1. 集計済みのカテゴリ別売上表を用意する。
2. A1:B5 などの範囲を選択する。
3. 挿入 -> 円グラフ を選ぶ。
4. データラベル でパーセンテージを表示する。
5. 小さい項目が多い場合は その他 にまとめる。

7.4.4 得られる効果

「飲料が 45%、食品が 30%」のように、全体に対する比率を直感的に伝えられます。

7.5 散布図

7.5.1 散布図で何が分かるか

散布図は、二つの数値変数の関係を見るためのグラフです。広告費と売上、来店客数と売上、価格と販売数量などで使います。

7.5.2 具体例

広告費と売上の関係を見る例を考えます。

1. A列：月
2. B列：広告費
3. C列：売上

7.5.3 作成手順

1. B列 と C列 を選択する。
2. 挿入 -> 散布図 を選ぶ。
3. 横軸が広告費、縦軸が売上になっているか確認する。
4. 近似曲線の追加 を行い、必要ならグラフ上に数式と決定係数を表示する。
5. 極端に離れた点がないか確認する。

7.5.4 得られる効果

二つの変数が右上がりなのか、右下がりなのか、あるいは関係が薄いのかを視覚的に把握できます。相関分析や線形回帰の前段として非常に重要です。

7.6 レーダーチャート

7.6.1 レーダーチャートで何が分かるか

レーダーチャートは、複数指標のバランスを比較したいときに使います。たとえば、店舗を「売上」「利益率」「客数」「リピート率」「在庫回転率」で比べるような場面です。

7.6.2 具体例

3 店舗について、次の 5 指標を比較する例です。

1. 売上
2. 利益率
3. 客数
4. リピート率
5. 接客評価

7.6.3 作成手順

1. 行に店舗名、列に各指標を置いた表を作る。
2. 指標の単位差が大きい場合は標準化する。
3. 範囲を選択して 挿入 -> レーダーチャート を選ぶ。
4. 店舗ごとの形の違いを見る。

7.6.4 得られる効果

単に総合点を比べるのではなく、「A 店は売上は強いが利益率が弱い」といった特徴の形を比較しやすくなります。

7.7 見やすいグラフにするための仕上げ

7.7.1 仕上げの基本手順

1. タイトルで何を示すグラフか明確にする。
2. 単位を省略しない。
3. 色数を増やしすぎない。
4. 凡例と軸ラベルを読みやすくする。
5. 双軸グラフは本当に必要な場合だけ使う。

7.7.2 実務での注意点

1. 円グラフを項目数の多い表にそのまま使わない。
2. 時系列なのに棒グラフだけで済ませない。
3. 散布図を使う前に、文字列や空欄が混ざっていないか確認する。
4. 色は強調したい 1 系列だけを濃くし、他は抑える。

---

8. ピボットテーブル

8.1 ピボットテーブルとは何か

8.1.1 ピボットテーブルの役割

ピボットテーブルは、元データを壊さずに切り口を変えながら集計できる Excel の代表的な分析機能です。SQL の GROUP BY を画面操作で柔軟に試せるイメージに近いです。

8.1.2 ピボットテーブルで何が分かるか

ピボットテーブルを使うと、たとえば次の問いに短時間で答えられます。

1. どの地域で売上が高いか。
2. どの時間帯に販売数量が多いか。
3. 商品カテゴリ別の平均単価はいくらか。
4. 会員と非会員で客単価はどう違うか。

8.2 作成前のデータ準備

8.2.1 元データの条件

1. 1 行目に見出しがある。
2. 見出しに空白セルがない。
3. 見出し名が重複していない。
4. 途中に結合セルがない。
5. 小計行や合計行を混ぜない。
6. 日付列と数値列の型がそろっている。

8.2.2 テーブル化しておくと便利

元データ範囲を Ctrl + T でテーブル化しておくと、データ追加後にピボットテーブルを更新しやすくなります。範囲が固定されにくくなるため、運用上かなり便利です。

8.3 基本的な作成手順

8.3.1 ピボットテーブルを挿入する

1. 元データ範囲の中のセルを 1 つ選ぶ。
2. 挿入 -> ピボットテーブル を選ぶ。
3. 元データ範囲が正しく認識されているか確認する。
4. 新しいワークシート か 既存のワークシート を選ぶ。
5. OK を押す。

8.3.2 フィールド一覧を理解する

ピボットテーブルの右側には、通常フィールド一覧が表示されます。主な配置先は次の四つです。

1. 行：縦方向に並ぶ分類軸
2. 列：横方向に並ぶ分類軸
3. 値：合計、平均、件数などの集計対象
4. フィルター：全体を切り替える条件

8.3.3 各エリアに置くと何が起こるか

1. 地域 を行に置くと、地域ごとの集計が縦に並ぶ。
2. 時間帯 を列に置くと、時間帯ごとの比較が横に並ぶ。
3. 売上金額 を値に置くと、地域別・時間帯別の売上合計になる。
4. 商品カテゴリ をフィルターに置くと、カテゴリを切り替えながら見られる。

8.4 値の設定を変える

8.4.1 値フィールドの設定

右クリックから 値フィールドの設定 を開くと、次のような集計方法に切り替えられます。

1. 合計
2. 個数
3. 平均
4. 最大値
5. 最小値

たとえば 売上金額 は合計で見ることが多いですが、客単価 は平均で見たほうが意味があります。

8.4.2 値の表示方法

値の表示方法 を使うと、単純合計以外の見せ方ができます。

1. 総計に対する比率
2. 行集計に対する比率
3. 列集計に対する比率
4. 前月差
5. 累計

たとえば 列集計に対する比率 を使うと、各地域の中でどの時間帯の比率が高いかを比較しやすくなります。

8.5 ピボットテーブルを作る詳細手順

8.5.1 例：地域別・時間帯別の販売数量

元データに次の列があるとします。

1. 取引日
2. 地域
3. 時間帯
4. 商品カテゴリ
5. 商品名
6. 販売数量
7. 売上金額

8.5.2 フィールドの配置

1. 行：地域
2. 列：時間帯
3. 値：販売数量
4. フィルター：商品カテゴリ

8.5.3 作業手順

1. 元データからピボットテーブルを作成する。
2. フィールド一覧から 地域 を 行 にドラッグする。
3. 時間帯 を 列 にドラッグする。
4. 販売数量 を 値 にドラッグする。
5. 商品カテゴリ を フィルター にドラッグする。
6. 値が 販売数量の合計 になっているか確認する。

8.5.4 得られる効果

この設定にすると、「どの地域で、どの時間帯に、どのくらい売れているか」が一目で分かります。さらに商品カテゴリを切り替えれば、飲料だけが朝に強いのか、食品が夕方に強いのかも見えます。

8.6 グループ化、並べ替え、絞り込み

8.6.1 日付のグループ化

日付フィールドを行や列に置いた状態で右クリックすると、月別、四半期別、年別にグループ化できます。時系列の要約では非常に便利です。

8.6.2 並べ替え

売上が大きい順、小さい順に並べ替えることで、上位店舗や下位店舗をすぐに把握できます。

8.6.3 絞り込み

フィルターだけでなく、ラベルフィルターや値フィルターを使えば、「売上 100 万円以上だけ」のような条件でも絞れます。

8.7 スライサー、タイムライン、ピボットグラフ

8.7.1 スライサー

1. ピボットテーブルを選択する。
2. ピボットテーブル分析 -> スライサーの挿入 を選ぶ。
3. 地域やカテゴリなど、切り替えたい項目を選ぶ。

スライサーを使うと、会議中でも直感的に条件を切り替えられます。

8.7.2 タイムライン

日付フィールドがある場合は タイムラインの挿入 を使うと、月単位や四半期単位で期間を絞りやすくなります。

8.7.3 ピボットグラフ

ピボットテーブルを選んだ状態で ピボットグラフ を挿入すると、集計結果と連動したグラフが作れます。元の集計を変えれば、グラフも自動で追随します。

8.8 ピボットテーブルでよくある失敗

1. 数値が文字列になっていて合計できない。
2. 日付が文字列でグループ化できない。
3. 見出し名が重複していてフィールド名が不自然になる。
4. データ更新後に 更新 を押していない。
5. 元データ範囲が追加分を含んでいない。

---

9. 相関分析と線形回帰

9.1 相関の基本

9.1.1 相関とは何か

相関は、二つの変数がどの方向に一緒に動くかを見る考え方です。

1. 正の相関：片方が増えると、もう片方も増えやすい。
2. 負の相関：片方が増えると、もう片方は減りやすい。
3. 相関が弱い：一緒に動く傾向があまり見えない。

9.1.2 Excel で相関係数を出す

広告費が B2:B13、売上が C2:C13 にあるなら次の式です。

=CORREL(B2:B13,C2:C13)

9.1.3 相関と因果は別

相関が高くても、必ずしも原因と結果を意味するわけではありません。季節要因や第三の変数で一緒に動いて見えることもあります。

9.2 散布図で先に形を見る

相関係数を出す前に、散布図で外れ値や曲線関係がないかを見ると、解釈のミスを減らせます。右上がりか、右下がりか、外れ値がないかをまず視覚的に確認することが大切です。

9.3 線形回帰の基本

9.3.1 単回帰分析

一つの説明変数 x から目的変数 y を予測する基本形です。

$$
y = a + bx
$$

1. a：切片
2. b：係数

たとえば広告費から売上を予測する場合、広告費が x、売上が y です。

9.3.2 重回帰分析

説明変数が複数ある場合は次の形になります。

$$
y = a + b_1x_1 + b_2x_2 + b_3x_3 + \cdots
$$

広告費だけでなく、値引き率や来店客数も一緒に使う場合がこれに当たります。

9.3.3 線形回帰で分かること

線形回帰を使うと、次のようなことが分かります。

1. どの変数が売上に強く関係しそうか。
2. 変数が 1 単位増えたとき、目的変数がどれくらい変わるか。
3. モデル全体がどれくらいデータを説明できているか。

9.4 Excel で線形回帰を実行する手順

9.4.1 分析ツールを有効化する

1. ファイル -> オプション -> アドイン を開く。
2. 管理で Excel アドイン を選び、設定 を押す。
3. 分析ツール にチェックを入れる。

9.4.2 回帰分析を実行する

1. データ -> データ分析 -> 回帰分析 を選ぶ。
2. Y 入力範囲に目的変数を指定する。
3. X 入力範囲に説明変数を指定する。
4. 見出しを含む場合は ラベル にチェックを入れる。
5. 出力先を選んで実行する。

9.4.3 実行前に確認すること

1. 欠損や空欄が混ざっていないか。
2. 文字列列を誤って指定していないか。
3. 目的変数と説明変数の行数が一致しているか。
4. 外れ値が極端でないか散布図で確認したか。

9.5 結果の読み方

9.5.1 R Square

R Square は、モデルがどれくらいデータを説明できているかを見る指標です。1 に近いほど説明力が高いと解釈しやすくなります。

9.5.2 Adjusted R Square

説明変数の数を考慮した決定係数です。重回帰分析では R Square だけでなく、こちらも確認したほうが無理な説明変数追加を見抜きやすくなります。

9.5.3 P-value

一般には p < 0.05 なら有意と見ることが多いです。ただし、統計的に有意でも、業務的な意味が大きいとは限りません。

9.5.4 Coefficients

係数が 3.5 で、広告費の単位が万円なら、「広告費が 1 万円増えると、売上は平均して 3.5 万円増える」と読めます。

9.5.5 切片

切片は説明変数が 0 のときの理論上の値です。業務上はそのまま使うより、モデル全体の式を作るための部品として読むほうが自然な場合もあります。

9.6 予測の具体例

回帰結果が次のように出たとします。

$$
売上 = 120 + 3.5 \times 広告費
$$

広告費が 20 万円なら、予測売上は次の通りです。

$$
120 + 3.5 \times 20 = 190
$$

つまり、予測売上は 190 万円です。Excel 上では、切片セルと係数セルを参照して、各月の予測値一覧を作ることもできます。

9.7 実務での読み方をもう一段深くする

9.7.1 単に係数を見るだけで終わらせない

係数が正でも、外れ値の影響で見かけ上そう見えることがあります。散布図と合わせて読むことが重要です。

9.7.2 残差を見る意識を持つ

予測値と実績値の差を残差と呼びます。特定の月だけ残差が大きいなら、その月に特別な販促や欠品があった可能性があります。

9.7.3 説明変数同士の重なりに注意する

広告費とクーポン配布数のように、似た動きをする変数を同時に入れると、係数の解釈が不安定になることがあります。重回帰ではこの点も意識する必要があります。

9.8 線形回帰で注意すること

1. 相関が高くても因果とは限らない。
2. 外れ値 1 点で係数が大きく変わることがある。
3. 過去の範囲を大きく外れた予測は危険。
4. 説明変数が少なすぎると、見かけの関係を誤解しやすい。
5. 数字が出ても、業務上の納得感を必ず確認する。

---

10. 最適化とソルバー

10.1 ソルバーとは何か

ソルバーは、Excel 上で最適化問題を解くためのアドインです。条件を満たしながら、コスト最小化、利益最大化、在庫配分最適化などを試すことができます。

10.2 最適化の考え方

10.2.1 三つの基本要素

ソルバーでは、次の三つを分けて考えることが重要です。

1. 目的セル：何を最小化または最大化したいか
2. 変数セル：何を調整できるか
3. 制約条件：何を守らなければならないか

10.2.2 実務での典型例

1. シフト人数を最小コストで決める。
2. 広告予算を限られた範囲で配分する。
3. 複数商品への在庫配分を調整する。
4. 売場面積を利益が最大になるように割り当てる。

10.3 コンビニのシフト配置を例にする

10.3.1 例の設定

日本で身近な例として、コンビニの土曜日シフトを考えます。必要人数は次の通りです。

1. 7:00-11:00：3 人以上
2. 11:00-15:00：4 人以上
3. 15:00-20:00：3 人以上

使えるシフトは次の四つとします。

1. 早番：7:00-15:00、1 人 9,000 円
2. 中番：11:00-20:00、1 人 8,500 円
3. 遅番：15:00-20:00、1 人 4,500 円
4. 店長シフト：7:00-20:00、1 人 13,000 円

さらに、店長シフトは必ず 1 人以上必要だとします。

10.3.2 変数、目的、制約

1. B2：早番人数
2. B3：中番人数
3. B4：遅番人数
4. B5：店長シフト人数

総人件費は次のように表せます。

=9000*B2+8500*B3+4500*B4+13000*B5

制約は次の通りです。

1. 朝：B2+B5>=3
2. 昼：B2+B3+B5>=4
3. 夕：B3+B4+B5>=3
4. 店長：B5>=1
5. すべて 0 以上の整数

10.4 シート設計をどう作るか

10.4.1 おすすめのレイアウト

1. A2:A5 にシフト名を置く。
2. B2:B5 に人数を置く。
3. C2:C5 に 1 人あたりコストを置く。
4. D2:D5 に各シフトのコスト式を置く。
5. D6 に総コストの合計式を置く。
6. 別表で朝、昼、夕の必要人数と充足人数を計算する。

10.4.2 なぜ分けて置くのか

制約条件をセルの数式として見える形にしておくと、ソルバー設定ミスに気付きやすくなります。あとから他人が見ても、何を最適化しているのか追いやすくなります。

10.5 ソルバーの操作手順

10.5.1 ソルバーを有効化する

1. ファイル -> オプション -> アドイン を開く。
2. Excel アドイン -> 設定 を押す。
3. ソルバー アドイン にチェックを入れる。

10.5.2 ソルバーに設定する内容

1. データ -> ソルバー を開く。
2. 目的セル に総人件費セルを指定する。
3. 最小値 を選ぶ。
4. 変数セル に B2:B5 を指定する。
5. 追加 ボタンで制約を登録する。
6. B2:B5 = 整数 を追加する。
7. B2:B5 >= 0 を追加する。
8. 線形問題なら シンプレックス LP を選ぶ。
9. 解決 を押す。

10.5.3 解を採用する前に確認すること

1. すべての制約を本当に満たしているか。
2. 人数が現実的か。
3. 欠勤や繁忙への余裕がまったくない解になっていないか。
4. 目的セルの式が想定通りか。

10.6 ソルバーで得られる効果

ソルバーを使うと、勘だけで決めていた配分を、条件付きで比較的合理的に決めやすくなります。特に「何を守るか」「何を最小化するか」を分けて考える訓練として有効です。

10.7 実務でありがちな失敗

1. 目的セルが数式ではなく固定値になっている。
2. 整数制約を入れ忘れて 2.6 人のような解が出る。
3. 実務上必要な制約を入れ忘れている。
4. 解法を適切に選んでいない。
5. 数学的な最適解を、そのまま現場運用に当てはめてしまう。

10.8 ソルバーを使うときの姿勢

ソルバーは便利ですが、入力したモデル以上のことはしてくれません。式や制約の設計が間違っていれば、計算上は正しくても業務上は意味のない答えになります。したがって、解の良し悪しは必ず現場感覚と合わせて確認する必要があります。

---

11. VBA とマクロ

11.1 マクロと VBA の違い

1. マクロ：Excel 上の操作を自動実行する仕組み
2. VBA：そのマクロを記述、編集するための言語

つまり、マクロは仕組み、VBA は中身を書く言語です。

11.2 事前準備

11.2.1 開発タブを表示する

1. ファイル -> オプション -> リボンのユーザー設定 を開く。
2. 開発 にチェックを入れる。
3. リボンに 開発 タブが出ることを確認する。

11.2.2 保存形式を理解する

マクロを含むブックは、通常の .xlsx ではなく、.xlsm で保存する必要があります。.xlsx のままだとマクロは保持されません。

11.2.3 セキュリティの基本

マクロ付きファイルを開くと、コンテンツの有効化 が必要になることがあります。出所不明のマクロは安易に有効化しない、という基本姿勢が重要です。

11.3 マクロ記録の基本手順

11.3.1 例：週次レポートを整形する

毎週、次の操作を行うとします。

1. 1 行目を太字にする。
2. 1 行目を固定する。
3. 金額列をカンマ区切りにする。
4. 列幅を自動調整する。

11.3.2 記録手順

1. 開発 -> マクロの記録 を押す。
2. マクロ名を FormatWeeklyReport などにする。
3. 保存先を このブック にする。
4. 上の 4 操作を実際に行う。
5. 記録終了 を押す。

11.3.3 実行手順

1. 開発 -> マクロ を開く。
2. FormatWeeklyReport を選ぶ。
3. 実行 を押す。

11.4 記録されたコードを見る

マクロ記録後に Visual Basic を開くと、Excel の操作が VBA コードになっています。たとえば次のような形になります。

Sub FormatWeeklyReport()
    Rows("1:1").Font.Bold = True
    ActiveWindow.SplitRow = 1
    ActiveWindow.FreezePanes = True
    Columns("D:F").NumberFormatLocal = "#,##0"
    Cells.EntireColumn.AutoFit
End Sub

11.4.1 どこを見るべきか

入門段階では、まず次の点を見れば十分です。

1. Rows や Columns がどの範囲を指しているか。
2. Font.Bold や NumberFormatLocal が何を変更しているか。
3. ActiveWindow や ActiveSheet に依存していないか。

11.5 手書き VBA の最初の例

11.5.1 メッセージボックスを出す

Sub ShowFinishMessage()
    MsgBox "月次レポートの更新が完了しました。", vbInformation, "Excel自動化"
End Sub

11.5.2 ピボットテーブル更新後に知らせる例

Sub RefreshReport()
    ThisWorkbook.RefreshAll
    MsgBox "データ更新とピボットテーブルの再計算が完了しました。"
End Sub

11.5.3 条件付きでセル色を変える例

Sub HighlightLowSales()
    Dim cell As Range

    For Each cell In Worksheets("売上レポート").Range("E2:E100")
        If cell.Value < 100000 Then
            cell.Interior.Color = RGB(255, 230, 230)
        End If
    Next cell
End Sub

この例では、売上が 10 万円未満のセルだけを薄い赤で塗ります。For Each と If の基本形をまとめて確認できます。

11.6 実行方法

1. 開発 -> Visual Basic を開く。
2. 挿入 -> 標準モジュール を選ぶ。
3. コードを貼り付ける。
4. F5 で実行する。
5. 問題があれば F8 で 1 行ずつ実行して確認する。

11.7 よく使う VBA の考え方

11.7.1 Range と Cells

1. Range("A1") は、セル番地で指定する書き方です。
2. Cells(1,1) は、行番号と列番号で指定する書き方です。

11.7.2 Worksheet を明示する

ActiveSheet に頼りすぎると、別シートが開いていたときに誤動作しやすくなります。入門でも Worksheets("売上レポート") のように対象を明示する癖を持つと安全です。

11.7.3 繰り返し処理と条件分岐

VBA を使うと、次のようなことを自動化できます。

1. 全シートに同じ書式を当てる。
2. 条件を満たす行だけ色を変える。
3. 複数ファイルを順に開いて集計する。

11.8 実務での注意点

1. テストは必ずコピーしたファイルで行う。
2. ActiveSheet や Selection に依存しすぎない。
3. ブック名やシート名が変わると壊れやすいことを意識する。
4. 自動処理前にバックアップを取る。
5. 記録マクロはそのままでは冗長なことが多い。

11.9 VBA を学ぶ近道

最も実務的な学び方は、次の繰り返しです。

1. 操作を一つ決める。
2. その操作だけを記録する。
3. 生成コードを見る。
4. 一部を書き換えて再実行する。

この進め方なら、文法だけで止まらず、実務と結びついた形で VBA を覚えられます。

---

12. まとめ

12.1 入門段階で最低限できるようにしたいこと

1. セル参照と絶対参照を正しく使える。
2. VLOOKUP と XLOOKUP でマスタ参照ができる。
3. IF、SUMIFS、COUNTIFS で条件付き集計ができる。
4. 基本的な文字列処理関数を使える。
5. グラフで比較、推移、構成比、関係性を表現できる。
6. ピボットテーブルで探索と報告ができる。
7. CORREL と線形回帰の結果を最低限読める。
8. ソルバーで小規模な最適化を試せる。
9. マクロ記録と .xlsm 保存ができる。

12.2 この後に進むなら何を学ぶか

1. INDEX + MATCH や動的配列関数
2. Power Query による前処理自動化
3. Power Pivot や DAX
4. VBA のループ、条件分岐、複数ブック操作
5. SQL、Python、BI ツールとの連携

Excel は、単なる表計算ソフトではなく、分析、可視化、最適化、自動化まで広くつながる入口です。入門では「式を暗記すること」より、「どの場面でどの機能を使うか」を理解することが重要です。そこが固まると、関数もピボットテーブルもソルバーも VBA も、単なる操作ではなく実務の武器として使いやすくなります。