Excelで簡単データ分析!便利な関数・ツールの活用方法を知ろう
- ツイート
-
- 2024/08/16
「データ分析」と聞くとどんなイメージがわきますか?近年身近にはなってきてはいるものの、未だ専門的で難しそうだと思っている人も多いのではないでしょうか。しかし、身近なツールである Excel(エクセル)を活用して、簡単にデータを分析することができます。
データ分析は難しそうな先入観から倦厭されがちですが、意外と簡単にできるんですよ。今回はそのやり方を伝授します!
目次
関数を用いたデータ分析やデータクレンジング(データ整備)
Excelにはたくさんの関数が存在します。関数を使えば、基礎的な分析をしたり、データ分析前にデータクレンジング(データを整えること)をすることができます。
関数 | |
---|---|
SUM | 指定範囲の値の合計を表示する |
MAX / MEDIAN / MIN / AVERAGE | 指定範囲の値の内の最大値/中央値/最小値/平均値を表示する |
IF / IFS | 条件に合致しているか否かで異なる値を表示する(IFSは複数の条件を組み込める) |
COUNTIF / COUNTIFS | 条件に合致した値の数を表示する(COUNTIFSは複数の条件を組み込める) |
SUMIF / SUMIFS | 条件に合致した値の合計を表示する(SUMIFSは複数の条件を組み込める) |
今回は、とあるお店が4月から6月に販売した商品とその値段のデータに、関数を使ってみます。
IF / IFS関数で商品の値段を自動表示してみよう
まず、値段対応表を参考に、自動で商品の値段を表示させるために、IF関数・IFS関数を使います。
IF関数は「=IF(条件1,指定の値1)」という形で用います。この場合、条件1に合致すれば指定の値1が表示されます。C2のセルに「=IF(A2=E2,F2)」と入力すると、「A2がE2と同じであれば、F2の値を表示する」という指令になり、いちごケーキの値段が表示されました。またF2の代わりに直接"¥600"と入力しても同じ結果が返されます。
IFS関数は「=IF(条件1,指定の値1,条件2,指定の値2,...)」という形で用い、条件は127個まで指定できます。この場合も、IF関数と同じく合致した条件があればそれぞれの指定の値が表示されます。例えばここでC3のセルに「=IFS(A3=E2,F2,A3=E3,F3,A3=E4,F4,A3=E5,F5,A3=E6,F6,A3=E7,F7,A3=E8,F8,TRUE,"エラー")」と入力すると、「A3がE2と同じであれば、F2の値を表示し、A3がE3と同じであれば、F3の値を表示し、...A3がE8と同じであれば、F8の値を表示し、それ以外の全てでは、エラーと表示する」という指令になります。結果、無事にモンブランの値段が表示されました。
SUM関数・MAX / MEDIAN / MIN / AVERAGE関数で月毎の売上やデータの概要を把握しよう
次に、SUM関数を使って月毎の合計売上を計算してみます。
SUM関数は「=SUM(範囲)」という形で用いると、指定した範囲の中の全ての値の合計を表示してくれます。例えば4月の合計売上を調べるためには、「=SUM(C2:C7)」と入力します。結果、3,900円だとわかりました。
指定した範囲内の最大値・中央値・最小値、平均値を求めたい場合には、それぞれMAX / MEDIAN / MIN / AVERAGE関数を利用します。使い方はどれもSUM関数と同じで、関数名の後に括弧で範囲を指定すれば、求める値が表示されます。
COUNTIF / COUNTIFS関数で商品ごとの販売個数を数えてみよう
COUNTIF関数では、条件に合致した値の数をカウントしてくれるため、たくさんの商品名が羅列されている中でどの商品がいくつ売れたのかを一瞬で数えることができます。関数は「=COUNTIF(範囲,"数える値")」または「=COUNTIF(範囲,数える値が入ったセル)」という形で用います。
今回の場合は「=COUNTIF(A2:A13,"いちごケーキ")」と入力し、合計個数は4個という結果が得られました。数える値には"いちごケーキ"のような名詞だけでなく、">100"のように数式を入れることもでき、">100"の場合には指定した範囲内で100より大きい数の個数を表示してくれます。
また、COUNTIFS関数の場合は、「=COUNTIFS(範囲1,"数える値"1,範囲2,"数える値"2...)」という形で用います。
SUMIF / SUMIFS関数で商品ごとの売上を計算しよう
条件に合致した値の合計を計算するには、SUMIF関数を用います。式は「=SUMIF(範囲A,指定の値,範囲B)」で、この場合、「範囲Aに指定の値があれば、それに対応する範囲Bの値を合計する」という指令になります。
例えば、Q2のセルに「=SUMIF(A2:A26,"いちごケーキ",C2:C26)」と入力すれば、A2からA13にある"いちごケーキ"の売上が合算され、表示されます。またモンブランの場合の「=SUMIF(A2:A26,L4,C2:C26)」のように、指定の値が入ったセルを用いることもできます。
Excelの分析ツールを使ってデータ分析をする
Excelにはデータを分析できる専用ツールが用意されており、それを使うことで数クリックで簡単にデータを分析することができます。
分析ツールは元々インストールされている場合とされていない場合があります。インストールされていなかった場合はこちらを参考にツールを自身でインストールしてみてください。
分析ツールを用いると、
- 基本統計量の一括計算
- 共分散の計算
- 回帰分析
- F・t・z検定
- 分散分析
- ヒストグラム作成
などができます。
ここからは、具体的な使い方をいくつかご紹介します。
自動で表示される分析情報
Excelのシートを開き、ホームタブを開いたまま「データの分析」をクリックします。
すると、自動で簡易的なデータ分析を行い、画面右側に結果を表示してくれます。
画像のような今回のデータからは、
- 性別とクラス別の「点数:国語」の平均点を表したピボットテーブル(表)
- 性別とクラス別の「点数:合計」の合計を表した棒グラフ
- 「点数:国語」と「点数:合計」の相関関係を表した散布図と回帰直線
- 性別が男の生徒の「点数:英語」を高得点順に表した棒グラフ
- 「点数:英語」のヒストグラム
生成された表やグラフを使いたい場合は、画像下部にある「⚪︎⚪︎の挿入」というボタンをクリックすれば、新しいシートに挿入されます。
基本統計量
データタブを開き「データの分析」をクリックします。すると分析ツールのメニューのようなものが出てくるので、その中から「基本統計量」を選択し、「OK」をクリックします。
フローティングウィンドウの表示が切り替わるので、詳細を設定していきます。
入力範囲にはデータ分析したいセル範囲をシート上で選択するか、手で入力します。今回は3教科とその合計の点数を分析したいので、D1からG9までの範囲を選択しました。
そして先頭の行が数値のラベルとなっていれば「先頭行をラベルとして使用」をチェックします。今回の場合も先頭の行が「点数:英語」「点数:国語」「点数:算数」「点数:合計」というラベルになっているので、チェックを入れました。
最後に「統計情報」にチェックを入れ、「OK」ボタンを押します。
こうすることで新しいシートにラベル毎の平均、中央値、最頻値、標準偏差、分散、最小値、最大値、合計などが出力されます。
画像では見切れていますが、英語、国語、算数、合計の全てにおいて、それぞれの数値が出力されました。例えばこの英語の結果からは、平均は59点、中央値は63点であることなどがわかりました。
よってこの分析ツールの基本統計量を使えば、SUM関数・MAX / MEDIAN / MIN / AVERAGE関数を代替できるうえ、標準偏差や分散などの他の数値も一発で計算できます。
回帰分析
回帰分析は、複数の変数の関係性を明らかにする分析手法です。
データタブを開き「データの分析」をクリックし、表示された分析ツールのメニューの中から「回帰分析」を選択し、「OK」をクリックします。
フローティングウィンドウの表示が切り替わるので、詳細を設定していきます。
入力Y範囲と入力X範囲にはデータ分析したいセル範囲をシート上で選択するか、手で入力します。この際、ラベルは範囲に含まないようにします。今回は先ほど同様にテスト点数のデータを用いて「点数:英語」が「点数:合計」にどう関係しているかを分析したいので、Yの範囲にG2からG9、Xの範囲にD2からD9を入力しました。
そして「ラベル」「残差」にチェックを入れ、「OK」ボタンを押します。
こうすることで回帰分析が実行され、新しいシートに画像のような結果が表示されます。
相関係数や決定係数、t値やP値などが算出されるため、その値からXとYの関係性を明らかにできます。
今回の結果からは、以下のようなことがわかりました。
- 回帰式はY=2.4471X+32.1414
- XのP値<0.05であるため、有意水準5%で「Xの係数は0」という仮説が棄却され、Xの係数は2.4471となる
- 切片のP値>0.05であるため、有意水準5%で「係数は0」という仮説が採択され、切片は0となる
t検定
t検定には以下の3つの種類があります。
- 対応があるt検定
- スチューデントのt検定
- ウェルチのt検定
「対応があるt検定」というのは、同じ人やもので2回計測するなど、標本(計測対象)が対応していることをいいます。例としては、同じ人たちの補習前と補習後のテスト点数が挙げられます。
「スチューデントのt検定」というのは、別の人やもので1回ずつ計測するなど、標本が異なり、さらにその2つの群の母分散が等しいと仮定できることをいいます。
「ウェルチのt検定」というのは、別の人やもので1回ずつ計測するなど、標本が異なり、さらにその2つの群の母分散が異なると仮定できることをいいます。
図に表すと、以下のような位置付けになります。
Excelの分析ツールではどのt検定も実施できますが、ひとまず今回は対応のあるt検定の方法をご説明します。
先ほども用いた点数のデータに、新しく1ヶ月後のテストの合計点を付け加えて検定を行います。
画像の「点数:合計①」が一度目のテストの合計点、「点数:合計②」が1ヶ月後のテストの合計点です。
まずデータタブを開き「データの分析」をクリックし、表示された分析ツールのメニューの中から「t検定:一対の標本による平均の検定」を選択し、「OK」をクリックします。(ここで「t検定:等分散を仮定した2標本による検定」を選択すればスチューデントのt検定が、「t検定:分散が等しくないと仮定した2標本による検定」を選択すればウェルチの検定が実施できます。)
フローティングウィンドウの表示が切り替わるので、詳細を設定していきます。
入力範囲にはデータ分析したいセル範囲をシート上で選択するか、手で入力します。この際、ラベルは範囲に含まないようにします。今回は変数1に「点数:合計①」のデータ範囲である「B2:B9」を、変数2に「点数:合計②」のデータ範囲である「C2:C9」を入力しました。
また、「仮説平均との差異」には「0」を入力します。これは2つのデータの意味は同じだということを表します。
最後に「OK」を押すと、画像のような結果が新しいシートに表示されます。
結果の「t境界値片側」より棄却域が「|t|>1.8946」とわかり、「t値」は-2.3393であり、「|t|(t値の絶対値)」は2.3393であることから、t値は棄却域に入っていることがわかります。
そして「P (T<=t) 片側」が0.05以下となっているので、「2回のテストの平均値には有意な差がある」ということになり、1ヶ月で生徒たちのテストの点数が向上したと言えます。
おわりに
今回の記事では、Excel(エクセル)を活用して簡単にデータ分析をする方法を、具体例を交えて解説しました。「データ分析」は決して難しく専門家にしかできないことではないので、今回の記事を通して身近で誰でも手が届くのだと感じていただけたら嬉しいです。
インターネット・アカデミーではデータ分析やデータ管理の講座をご用意しております。データについて詳しく学んでみたいという方は、ぜひ無料カウンセリングでお気軽にご相談ください。
業務効率をアップするビジネススキルを習得
Python×Excel自動化講座Pythonの基本的な操作から学び、最終的にPythonでExcelファイルのデータ集計や顧客リストのデータ抽出などが自動でできる方法を学びます。会社で、Excelでの日々の集計作業や顧客データの管理に追われ、業務が進まないと感じている方、RPAを導入したいとお考えの方にお勧めの講座です。
データのビジネス活用ができる実践スキルを習得
データ分析講座デジタルデータの増大は著しく、企業としてもビジネスにデータを利活用したいと考える企業は増えています。本講座では、Pythonのライブラリを使って、データ分析に必要な基礎スキルを学びながら、データの収集から加工、可視化を行うような実践的なノウハウについて学びます。
相談してコースを選びたい方はカウンセラーに無料で相談
※無理な勧誘は一切ありません
無料で相談してみる
専門のプログラミング言語とかで検定や解析をしたり、数学が得意な人にしかできなさそうなイメージがあります...