Excelによるアンケートデータ集計・可視化のコツ 「ピボットグラフを使う」

カテゴリ：

hr.icon

1. Webアンケート設計のコツ　設問・回答選択肢をナンバリングしておく

Webアンケート設計時に、設問と回答選択肢にナンバリングしておくと、アンケートデータを即時に集計できるのと、並び替えが楽になります。

例：満足度の場合 (ポジティブな回答の数字を大きく設計しておけば、段階尺度をスコア化して、平均の比較ができる)

1. とても不満　　2. 不満　　3. どちらでもない　　4. 満足　　5. とても満足

https://gyazo.com/21cf65d7b13480a2917e212add2f7d30

2. 集計はピボットグラフを使う

【何故、ピボットグラフか？】

Excelのピボットテーブル、ピボットグラフであれば、ほぼすべての人が利用できる。

市販の参考書籍も多い。

フィルタを使い、絞り込みが容易　特にスライサー機能は便利

クロス集計は、「条件付き書式」を使えば、ヒートマップにすることができる 【横持ちデータのみ】→このテクニックは、学内統計等で広範囲に活用できる

ピボットグラフを組み合わせた、「ダッシュボード」も作成できる

回答選択肢をスコア化(得点化)して、移動平均の折れ線グラフや、レーダーチャートの作成もできる。

3. Excelのピボットで作成できるグラフ1　棒グラフ

【完成イメージ】授業時間外学習時間の棒グラフ

「学部」間比較

スライサーを使った、「学年」と「課題の多さ」のフィルタ

棒グラフには数を入れ、色を分かりやすくする

https://gyazo.com/2ed6631c4bc00142d6140092a2165526

【作成手順1】

アンケート結果から、ピボットテーブルを作成、列に「授業時間外学習時間」、行に「所属学部」、値に「学生番号【ダミー】」

https://gyazo.com/7bc49814129e0d327827a36963f8f134

【作成手順2】

挿入メニューから、縦棒グラフを選択

【作成手順3】

グラフに各個数を入れる。

グラフを選択し、「ピボットグラフツール」から、「デザイン」を選択する。「グラフ要素の追加」から、「データラベル」で、「外側」を指定。

https://gyazo.com/100cf3f813d8e4e141f0c30875edf8cc

【作成手順4】スライサーの挿入

ピボットテーブルをクリックし、ピボットテーブルツールの「分析」から、「スライサーの挿入」を選び、「学年」を指定。(他の設問も、フィルタとして使える)

https://gyazo.com/01ad7d4fcb25b03fd8e3e6e2054dc8c0

【作成手順5】グラフの色を変える

色がカラフルだと見づらいだけなので、「ピボットグラフツール」のデザインから、色の変更を行う。数値が高いほうを濃くすると見やすい。

https://gyazo.com/316956d5e7de97589cec9ccfcbe27cc8

4. Excelのピボットで作成できるグラフ2　クロス集計＆ヒートマップ

クロス集計を、ヒートマップ化することにより、各段に分かりやすくなる。

スライサーの組み合わせも可能。ピボットのレイアウトに注意。

【完成イメージ】

「授業の理解度」と「授業の満足度」のクロス集計

条件付き書式のカラースケールを使い、ヒートマップ化

https://gyazo.com/f89550fbcb00bc9e01d3add5a09c7155

【作成手順1】

下のピボットは、「授業の理解度」と「授業の満足度」をかけわせたクロス集計（値の「学生番号【ダミー】」はカウント)。

これはこれで使えるが、もう少し分かりやすくしたい。

https://gyazo.com/7908b6c1718a68396068d84c2c0786e1

細かいが、上の例の「授業の理解度」は、ポジティブな回答を上に持ってきた方が、見やすい(と思う)　

ピボットテーブルで、「降順」に並び替える。

https://gyazo.com/8b83e8f6f837126adbd6e18087bb4732

【作成手順2】

ピボットテーブルで、「総計」や「小計」を出したくないので、ピボットテーブルツールの「デザイン」から、「総計」や「小計」の集計を行わないように設定をする。

https://gyazo.com/3f7b563bec91ae1b94385d30fcb7740d

【作成手順3】

ヒートマップを作成するには、「表形式」のほうが見やすい(と思う)。

ピボットテーブルツールの「デザイン」、「レポートのレイアウト」で設定。

https://gyazo.com/cee15011a912ab24a0f0bbb141fadf7f

【作成手順4】

ホームタブにある、「条件付き書式」から「カラースケール」を指定。

※Excelの教科書だと、よくデータバーの使用例があるが、数字が見にくいので、「カラースケール」を推奨。

https://gyazo.com/c43b10d6c2d0789e52fe754c39e91cc7

5. Excelのピボットで作成できるグラフ3　ダッシュボード

【完成イメージ】

作成した、ピボットグラフを組み合わせる。

スライサーを使い、二つのグラフに、同時にフィルタをかけることができる。

https://gyazo.com/692323bd9e6a006802cb04a99b157101

【作成手順1】

(今回のケースでは)作成した棒グラフ、スライサーをコピーし、ヒートマップの下に貼る。

【作成手順2】

スライサーを右クリックし、「レポートの接続」を選択し、適用対象のグラフを選択する。

https://gyazo.com/5835956c4057a13e3823a359730e0976

【参考書籍】

Excelピボットテーブル データ集計・分析の「引き出し」が増える本 (木村 幸子 著、翔泳社)

週刊東洋経済 2021/4/3号【特集】Excelデータ分析術　※Kindle版も買えます

※当コラムの文責及び著作権は、すべて投稿者に帰属します。