【Excel】エクセルの分析ツールの使い方（データ分析でできること・相関・ヒストグラム・回帰分析）

エクセルで売上、顧客数、作業時間などのデータを扱っていると、平均値を確認するだけでは見えない傾向を調べたくなることがあります。

そのような場面で役立つ機能が、アドインとして用意されている分析ツールです。

分析ツールを有効にすると、相関、ヒストグラム、回帰分析、基本統計量、移動平均などを、関数を組み合わせずに実行できます。

ただし、入力範囲、見出しの有無、出力先の選択を誤ると、計算結果が意図した内容にならない場合もあります。

ここでは、サンプルデータを使いながら、分析ツールの追加方法から各メニューの使い方、結果の読み取り方まで順番に解説していきます。

 

エクセルの分析ツールを有効にする手順

それではまず、データ分析を実行できる状態にするための設定について解説していきます。

項目	内容
使用する機能	分析ツール
表示場所	データタブの分析グループ
初回の作業	Excelアドインの有効化

分析ツールは、最初からデータタブに表示されているとは限りません。

データタブを開いてもデータ分析ボタンが見当たらないときは、Excelアドインの設定を一度だけ変更します。

 

ファイルタブからオプションを開く操作

分析ツールの追加は、Excelのファイルタブにあるオプション画面から行います。

ブックを開いた状態で、画面左上のファイルを選び、左側の一覧の下部にあるオプションをクリックしてください。

Excelのオプション画面では、数式、保存、言語、アドインなど、Excel全体に関する設定を変更できます。

分析ツールを探す場所は、リボンのユーザー設定ではなくアドインです。

別の設定画面を開いてしまった場合でも、左側の項目からアドインを選択すれば問題ありません。

 

Excelアドインで分析ツールを追加する操作

続いては、分析ツールをExcelへ追加する操作を確認していきます。

オプション画面の左側でアドインを選ぶと、画面下部に管理という項目が表示されます。

管理の一覧でExcelアドインを選択し、設定ボタンをクリックします。

アドインの一覧が開いたら、分析ツールにチェックを入れ、OKを選択してください。

一覧には分析ツール VBAも表示されることがありますが、通常の統計分析を行うだけなら分析ツールを選べば十分です。

分析ツール VBAは、VBAから統計分析の機能を呼び出す場合に利用する項目です。

チェックを入れた直後に必要なファイルのインストールを求められた場合は、画面の案内に従って追加します。

 

データ分析ボタンを確認する方法

続いては、追加後にボタンが正常に表示されたかを確認していきます。

設定画面を閉じてワークシートへ戻り、リボンのデータタブを選択してください。

右側にある分析グループ内へ、データ分析というボタンが表示されていれば準備は完了です。

このボタンを押すと、ヒストグラム、相関、回帰、t検定、分散分析などの分析方法を選ぶ画面が開きます。

 

データ分析でできることとデータ準備

続いては、分析ツールで扱える内容と、分析前に整えるデータの形を確認していきます。

月	広告費	売上	来店者数
4月	120	840	310
5月	150	910	335
6月	180	1040	380
7月	130	870	320

分析ツールでは、数値がどのように散らばっているか、複数の項目がどう結び付くかを表として出力できます。

表の1行目に項目名を置き、2行目以降に同じ種類の数値を入力する形が、もっとも扱いやすい構成です。

 

1行目のヘッダーと数値形式の整え方

分析を始める前に、データの1行目へヘッダーを入力します。

今回の例では、A1セルに月、B1セルに広告費、C1セルに売上、D1セルに来店者数を入力する構成です。

分析ツールで先頭行をラベルとして扱う設定を選ぶと、結果の表にも売上や広告費といった項目名が表示されます。

ヘッダーがない状態でラベルの設定を有効にすると、最初の数値が項目名として除外されるため注意が必要です。

金額、件数、時間などの表示形式が異なっていても、セルの中身が数値なら分析対象にできます。

一方で、1,200円のように文字列として入力されている数値は、計算対象から外れることがあります。

 

空白セルとエラー値を確認する方法

続いては、集計結果のずれを防ぐために空白セルとエラー値を確認していきます。

分析範囲の途中に空白セルがあると、処理内容によっては結果が欠けたり、エラーになったりすることがあります。

数式を使っている表では、#N/Aや#DIV/0!などのエラーが残っていないかも確認しましょう。

たとえば売上がC列、来店者数がD列にある場合、売上単価をE2セルへ求める数式は次のようになります。

この式では、C2の売上をD2の来店者数で割り、1人あたりの売上を計算します。

先頭データが2行目にあるため、E2セルへ数式を入力した後、フィルハンドルを下へドラッグしてオートフィルします。

分母となる来店者数が0の行があるとエラーになるため、分析前に値を確認することが大切です。

 

出力先を分けて結果を残す方法

続いては、元データを保護しながら分析結果を出力する方法を確認していきます。

分析ツールは、実行時に出力先を指定できます。

入力データの右側に空いているセルを指定することもできますが、分析結果が広がって表を見づらくする場合があります。

新規ワークシートへ出力する設定を使うと、元データと結果を分けて管理できます。

 

相関分析による項目間の関係

続いては、広告費と売上のような複数項目の関係を調べる相関分析について解説していきます。

	広告費	売上	来店者数
広告費	1.000	0.921	0.886
売上	0.921	1.000	0.954
来店者数	0.886	0.954	1.000

相関分析では、2つの数値が同じ方向へ動く傾向を相関係数で表します。

相関係数はマイナス1からプラス1までの値になり、プラス1に近いほど正の相関、マイナス1に近いほど負の相関が強い状態です。

 

相関メニューで入力範囲を指定する操作

それでは、データ分析から相関を選ぶ操作について解説していきます。

データタブのデータ分析をクリックし、一覧から相関を選択してOKをクリックします。

入力範囲には、比較したい数値列をまとめて指定します。

今回の例では、B1からD5までを指定すると、広告費、売上、来店者数の相関行列を作成できます。

先頭行に項目名を含めているため、先頭行をラベルとして使用する設定にもチェックを入れます。

月のような文字列の列は相関係数の計算対象に含めず、数値の列だけを選択します。

 

相関係数の数値を読み取る考え方

続いては、出力された相関係数の読み方を確認していきます。

表の対角線上に並ぶ1.000は、同じ項目を比較した結果です。

実務では、広告費と売上、売上と来店者数など、異なる項目が交わるセルを見ます。

たとえば広告費と売上の相関係数が0.921なら、今回のデータでは広告費が多い月ほど売上も高い傾向があると読み取れます。

一般に絶対値が0.7以上なら強い傾向、0.4前後なら中程度の傾向と考えることがありますが、件数や業務内容によって慎重に判断しましょう。

 

相関と散布図を組み合わせる確認方法

続いては、相関係数だけでは見えにくい外れ値を確認する方法について解説していきます。

相関の結果を確認した後は、広告費と売上の列を選択し、挿入タブから散布図を作成すると理解しやすくなります。

散布図では、点が右上へまとまるほど正の相関、右下へ並ぶほど負の相関を視覚的に把握できます。

一部の月だけ大きく離れた点がある場合は、特別なセール、欠品、休業などの事情を確認するきっかけになります。

 

ヒストグラムによるデータ分布

続いては、数値がどの範囲に集中しているかを調べるヒストグラムについて解説していきます。

階級	度数
0以上 500未満	3
500以上 1000未満	8
1000以上 1500未満	5
1500以上	2

ヒストグラムは、データを一定の区間に分け、それぞれの区間にいくつの値が含まれるかを確認する分析です。

売上金額、対応時間、テスト得点、商品の注文数などのばらつきを把握したいときに役立ちます。

 

ビン範囲を作成する方法

それではまず、ヒストグラムの区切りとなるビン範囲について解説していきます。

元データとは別の列に、区間の上限値を小さい順に入力します。

たとえば売上データを分析するなら、F1セルへ階級、F2セルへ500、F3セルへ1000、F4セルへ1500のように入力します。

ビンの幅を細かくしすぎると棒が増えすぎ、粗すぎると分布の特徴が隠れます。

データの件数と値の広がりを見ながら、業務上意味のある区切りを設定することが重要です。

たとえば配送時間なら30分ごと、商品単価なら500円ごとというように、判断に使える単位で区切ると結果を活用しやすくなります。

 

データ分析からヒストグラムを実行する操作

続いては、ヒストグラムの設定画面で入力範囲を指定する操作を確認していきます。

データタブのデータ分析を開き、一覧からヒストグラムを選択してOKをクリックします。

入力範囲には分析したい数値列を指定し、ビン範囲には先ほど作成した階級のセル範囲を指定します。

データの先頭行に売上の見出しを含めたときは、ラベルの設定を有効にします。

出力オプションで新規ワークシートを選び、グラフ出力にチェックを入れてOKをクリックしましょう。

グラフ出力へチェックを入れると、度数表だけでなく棒グラフも同時に作成されます。

 

度数表とグラフを読み取る視点

続いては、作成された度数表とヒストグラムの読み方を確認していきます。

度数がもっとも多い階級は、データが集中している代表的な範囲です。

右側に長く棒が続く形なら、一部に非常に大きな値がある右裾の長い分布かもしれません。

左右が比較的対称なら、平均値が全体の中心を表しやすいケースがあります。

一方で極端な値がある場合は、平均値だけでなく中央値も確認すると、実態をつかみやすくなります。

 

回帰分析による売上予測

続いては、広告費や来店者数が売上へどの程度関係しているかを調べる回帰分析について解説していきます。

広告費	来店者数	売上
120	310	840
150	335	910
180	380	1040
130	320	870

回帰分析は、結果として見たい数値を目的変数、影響を調べたい数値を説明変数として設定する分析です。

売上を目的変数にし、広告費と来店者数を説明変数にすると、売上の変化をどの程度説明できるかを確認できます。

 

Y入力範囲とX入力範囲の指定

それではまず、回帰分析の設定にあるY入力範囲とX入力範囲について解説していきます。

データ分析の一覧で回帰を選ぶと、Y入力範囲とX入力範囲を入力する画面が表示されます。

Y入力範囲には、最終的に説明したい結果の列を指定します。

売上を分析するなら、ヘッダーを含めてC1からC5のように指定します。

X入力範囲には、広告費と来店者数などの要因となる列を指定します。

列が隣接している場合は、B1からD5のうち目的変数の列を除く形で、連続範囲を選択できます。

離れた列を使う場合は、分析しやすいように別の場所へ必要列を並べた表を作る方法も便利です。

 

回帰統計と決定係数の見方

続いては、回帰分析の出力結果で確認したい数値を解説していきます。

回帰統計のR Squareは決定係数と呼ばれ、説明変数が目的変数の変動をどの程度説明できているかの目安です。

たとえばR Squareが0.80なら、売上の変動のうち約80パーセントを、設定した説明変数で説明できていると考えます。

決定係数が高いほど当てはまりは良くなりますが、将来の予測が必ず正確になることを意味するわけではありません。

観測数が少ない場合や、たまたま似た動きをした場合は、数値が高くても判断を誤る可能性があります。

係数の表では、各説明変数が増えたときに売上がどの方向へ変わる推定になっているかを確認できます。

 

分析結果を業務判断へつなげる考え方

続いては、回帰分析の数値を実務で活用するための考え方を確認していきます。

広告費の係数が正なら、他の条件が同じと仮定したとき、広告費の増加と売上増加が結び付く推定です。

ただし、広告費を増やす判断では、利益率、在庫、キャンペーンの内容、季節要因も合わせて考える必要があります。

予測値と実際の売上を月ごとに比較し、差が大きい月の理由を記録すると、次回の分析精度を高められます。

 

分析結果を活用するための注意点

続いては、分析ツールの結果を誤解なく利用するために押さえたい注意点について解説していきます。

確認項目	確認内容
データ件数	少なすぎないか
期間	季節変動を含むか
外れ値	特別な事情がないか
単位	円、件、時間が混在していないか

分析ツールは計算を自動化してくれますが、入力するデータの妥当性までは判断してくれません。

結果を会議資料や報告書へ使う前に、対象期間、単位、欠損値、外れ値を見直すことが重要です。

 

データ件数が少ない場合の考え方

それではまず、データ件数と分析結果の信頼性について解説していきます。

数件だけのデータでは、偶然の変動によって相関係数や回帰係数が大きく変わることがあります。

月次データなら数か月分だけで判断せず、可能であれば1年分以上のデータを集めると季節性を確認しやすくなります。

件数を増やすことは、計算式を複雑にすることよりも、分析の安定性に役立つ場合があります。

 

外れ値を削除する前の確認

続いては、極端な数値を扱うときの注意点を確認していきます。

他の値とかけ離れた数値を見つけても、すぐに削除するのは適切ではありません。

入力ミスであれば修正が必要ですが、大型案件の受注や臨時休業など、実際に起きた事実なら重要な情報です。

外れ値がある場合は、含めた分析結果と除いた分析結果を比較し、差が生じる理由を説明できる状態にしましょう。

 

分析結果を共有するときの記載方法

続いては、分析結果を他の人へ伝えるときの整理方法について解説していきます。

結果だけを貼り付けるのではなく、対象期間、データ件数、分析目的、使用した項目を添えると、読み手が判断しやすくなります。

相関分析なら、相関が見られたという表現にとどめ、因果関係が確定したような書き方は避けましょう。

回帰分析なら、予測値には誤差があることも併記すると、数値の受け取り方が適切になります。

 

まとめ エクセルの分析ツールの使い方

分析方法	主な確認内容
基本統計量	平均値やばらつき
相関	項目同士の関係
ヒストグラム	数値の分布
回帰	要因と予測の目安

エクセルの分析ツールは、アドインで分析ツールを有効にすると、データタブのデータ分析から利用できます。

相関分析では項目間の動きの近さを確認し、ヒストグラムではデータの集中やばらつきを把握できます。

回帰分析では、売上のような目的変数に対して、広告費や来店者数などの説明変数がどのように関係するかを調べられます。

分析を正しく行うためには、1行目へヘッダーを置き、空白、エラー値、文字列化した数値がないかを確認することが基本です。

また、相関係数や決定係数が高くても、それだけで原因を断定することはできません。

元データ、グラフ、業務上の出来事を見比べながら、分析結果を判断材料として活用していきましょう。