はじめに

この動画ではエクセルでアンケート結果を管理して、集計表を作成したり、割合を表すグラフを作成する方法を紹介します。また、年代ごとの集計表を作成する方法についても紹介します。

まずは完成形を確認します。

この表の左側にアンケートの結果を入力していきます。素早く入力できるように数字で入力する方式を取っています。

入力した数字に応じて、右側にはアンケート結果が文字で表示されます。アンケート結果を数字だけでなく文字で表示することで、入力表やこの後作成する集計表で読みやすくすることができます。

入力したアンケート結果に応じて、自動で

• 集計表や
• 割合のグラフ
• 年代ごとの集計表

を作成することができます。

それでは作成していきましょう。

項目名

まずは、アンケート結果を入力する表の項目名の欄を作成していきます。

(2行目から)

右へ情報を入力していく際は、Tabキーを押すと、セルの入力を確定して選択セルを右に移動することができるので、素早く入力していくことができます。

列幅が足りず情報が表示しきれていない場合はその列名の右側の境目をダブルクリックすると、情報量に合わせて自動で、列幅を調整することができます。

(1行目入力)

セルをまたいで文字を表示したい場合はセルを結合します。セルを結合するには、結合したいセルをドラッグで選択して、上のホームタブのセルを結合して中央揃えをクリックすると、セルを結合することができます。

Ctrlキーを押しながらYキーを押すと、以前と同じ操作を行うことができるので、繰り返しの操作がある場合は、Ctrlキーを押しながらYキーを押すことで、作業をスピードアップすることができます。

仮

次に、集計などの各挙動を確認するために、仮で選択肢の数字を入力していきます。

この動画では

• 年代の項目は6択
• きっかけの項目は8択
• 満足度の項目は4択

を想定しています。

先程と同じように、入力を確定する際はTabキーで選択セルを右に移動しながら入力していきます。右までいったらEnterキーで入力を確定すると、選択セルが次の行の1番左の列に移動するので、素早く次のデータを入力していくことができます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

VLOOKUP

次に、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるように設定していきます。

入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるようにするには、まず選択肢の数字と集計用の文字の対応表を作成します。

ここでは新しいシートに対応表を作成します。

年代

左下のシートタブの横のプラスボタンをクリックして、新しいシートを作成します。

シート名をダブルクリックして、シート名をリスト(年代)と書き換えておきます。

ついでに、入力用のシートをアンケート結果と書き換えておきます。

次にリスト(年代)のシートに年代の選択肢の数字と集計用の文字の対応表を作成します。

連続する数字を入力する場合は2つ入力した後、そのセルをドラッグで選択して、右下の小さい四角をドラッグすることで、素早く入力することができます。

集計用の文字は後に集計表で並び替えができるように、集計用の文字の頭には数字を付けておくと便利です。

集計表用の文字も候補が出る場合があるので、そのままEnterを押すと素早く入力することができます。

対応表が完成したら、アンケート結果のシートに戻って

=VLOOKUP(A3,

と入力して、リスト(年代)のシートをクリックして、A列～B列をドラッグします。

‘リスト(年代)’!A:B

そのまま

,2,0)

というように入力して、Enterで確定します。

すると、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されました。

VLOOKUP関数では、

1つ目の引数には、検索する文字列

2つ目の引数には、検索先の対応表

3つ目の引数には、検索先の対応表の左から何列目の情報を抽出するか

4つ目の引数には、検索の方式

を入力することで、他の表から文字列が一致する情報を、抽出することができる関数です。

今回の場合、

• A3セルに入力されている文字を、
• リスト(年代)シートのA列～B列の表から検索し、
• その表の一致する文字の左から2列目を表示する。

という意味になります。

2つ目の引数である、検索先の対応表は、検索する列が一番左にくるように設定します。

また、検索先の表は、このように範囲で指定することもできるのですが、列で指定しておけば、情報が追加された際に、指定し直す必要がなくなるので便利です。

4つ目の引数である、検索の方式は、

• 0またはFALSEと入力すると完全一致検索を行い
• 1またはTRUEと入力すると近似値検索を行います。

近似値検索を使うシチュエーションはあまり無いので、VLOOKUP関数の4つ目の引数には『0を入れる』と覚えておけばOKです。

他の行にも適用するには、入力したセルを選択した状態で、右下の小さい四角をダブルクリックすることで、他の行にも適用することができます。

これで、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるように設定することができました。

(書き換えるか、書き足す)

きっかけ

同じように、きっかけの選択肢の対応表を作成します。

左下のシートタブの横のプラスボタンをクリックして、新しいシートを作成します。

シート名をダブルクリックして、シート名をリスト(きっかけ)と書き換えます。

シートはドラッグで並び替えることができるので、分かりやすいように並び替えておきましょう。

次に対応表を作成していきます。

連続する数字を入力する場合は2つ入力した後、そのセルをドラッグで選択して、右下の小さい四角をドラッグして入力します。

対応表が完成したら、アンケート結果のシートに戻って

=VLOOKUP(B3,

と入力して、リスト(きっかけ)のシートをクリックして、A列～B列をドラッグしたら、

‘リスト(きっかけ)’!A:B

そのまま

,2,0)

というように入力します。

Enterで確定します。

入力したセルを選択して、右下の小さい四角をダブルクリックして、他の行にも適用します。

列の境目をダブルクリックして、列幅を整えます。

これで、きっかけの欄も、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるように設定することができました。

(書き換えるか、書き足す)

満足度

続いて同じように、満足度の選択肢の対応表を作成します。

左下のシートタブの横のプラスボタンをクリックして、新しいシートを作成します。

シート名をダブルクリックして、シート名をリスト(満足度)と書き換えて、ドラッグで並び替えます。

次に対応表を作成していきます。

連続する数字を入力する場合は2つ入力した後、そのセルをドラッグで選択して、右下の小さい四角をドラッグして入力します。

対応表が完成したら、アンケート結果のシートに戻って

=VLOOKUP(C3,

と入力して、リスト(満足度)のシートをクリックして、A列～B列をドラッグして、

‘リスト(満足度)’!A:B

そのまま

,2,0)

というように入力します。

Enterで確定します。

入力したセルを選択して、右下の小さい四角をダブルクリックして、他の行にも適用します。

これで、満足度の欄も、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるように設定することができました。

(書き換えるか、書き足す)

シートの非表示

各、選択肢の対応表が完成したら、これらのシートを編集することは、ほとんどないので、非表示にしていきます。

シートを非表示にするには、非表示にしたいシートを右クリックして、 非表示をクリックします。

再表示したい場合は、既存のシートを右クリックして再表示をクリックして、再表示したいシートを選択した状態でOKをクリックすると、再表示することができます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

テーブル

次に表のデザインをこのように縞々にして、見やすくします。

表を縞々のデザインにするには、その表にテーブルという設定をする必要があります。

テーブルというのは日本語で表という意味で、これを設定することで、表のデザインを簡単に整えたり、データを並び替えたり絞り込んだりできるようになります。

テーブルを設定するには、テーブルを設定したい表のどこかを選択した状態で、Ctrlキーを押しながらTキーを押します。

すると、テーブルの設定メニューが表示されます。

テーブルの設定メニューが表示されたら、まずテーブルを設定する範囲を選択します。通常は自動で選択されるのですが、正しく選択されていない場合は、ドラッグで選択し直す必要があります。

テーブルの見出しは1行しか設定することができないので、今回はこのように選択し直します。

選択した範囲の、先頭行は見出しなので、先頭行をテーブルの見出しとして使用するにチェックが入ってることを確認したら、OKをクリックします。

これで、テーブルを設定することができ、縞々のデザインになりました。

もしデザインを変更したい場合は、テーブルを適用した表を選択した状態で、上のテーブルデザインタブのテーブルスタイルのオプションとテーブルスタイルのグループから変更することができます。

この動画ではそのままのデザインで進めていきます。

罫線

次に入力用の列と集計用の列の見分けが付きやすいように、セルの境目に線を引きます。

エクセルではセルの境目のことを罫線といい、罫線を黒色にすることで、線を引くことができます。

罫線の書式を変えるには、まず書式を変えたい罫線の隣接するセルを選択します。

今回は複数列変更したいので、1つ目の列を通常通りクリックしたあと、2つ目の列をCtrlキーを押しながらクリックすることで、複数列を選択した状態にすることができます。

セルを選択できたら、上のホームタブのフォントグループの罫線メニューで罫線の書式を変更することができます。今回は右側に罫線を引きます。

セルを結合している場合は、罫線がうまく適用されない場合があるので、その場合は、個別で設定する必要があります。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

ピボットテーブル(きっかけ)

次に、入力した表を元に、きっかけの回答数と比率の集計表を作成します。

集計表を作成するには、ピボットテーブルという機能を利用します。ピボットテーブルは簡単に集計表を作ることができる機能です。

ピボットテーブル機能で集計表を作成するには、集計元となる表のどこかのセルを選択した状態で、上の挿入タブから、ピボットテーブルをクリックします。

するとピボットテーブル作成の設定画面が表示されます。

設定画面が表示されたら、まず集計の元となるデータの表を選択します。

集計元の表にテーブルを設定している場合は、自動でテーブル名が入力されます。

表にテーブルを設定すると、そのテーブルには自動でテーブル名が設定されます。テーブル名は変更することもできますが、今回は変更していないので、テーブル1という名前が設定されていて、そのテーブル名が入力されています。

通常は自動で入力されますが、もし適切に入力されていない場合は、ドラッグして入力し直してください。

テーブルの設定範囲はデータが追加されると自動で広がるので、集計元のデータをテーブルで指定することで、データが追加された際に自動で集計する範囲が広がり、新しく追加されたデータも自動で集計されるようになります。

集計の元となるデータの表を選択できたら、次に、ピボットテーブルを作成するシートを選択します。今回は集計元となる表のシートとは分けて集計したいので、新規ワークシートを選択した状態で、OKをクリックします。

すると、新しいシートが開いて、右側にピボットテーブルの設定画面が表示されます。

上のボックスには、集計元の表の項目名が表示されています。

設定画面の使い方を簡単に紹介すると、上のボックスにある項目名を、このように下のボックスにドラッグして使います。

• 行のボックスには左側に表示したい項目名、
• 列のボックスには上側に表示したい項目名、
• 値のボックスには算出したい項目の項目名、
• フィルターのボックスには絞り込みをしたい項目名

を入力します。

今回は、左側にきっかけの項目を表示したいので、きっかけ(集計用)の項目名をドラッグして行のボックスに入れます。すると、左側に、きっかけの項目が表示されました。

次に、きっかけの回答数を集計をしたいので、きっかけ(集計用)をドラッグして値のボックスに入れます。

これで、きっかけの回答数を集計することができました。

次にきっかけの比率を集計します。

もう一度きっかけ(集計用)をドラッグして値のボックスに入れます。するときっかけ(集計用)の集計が2つになるので、2つ目の項目名をダブルクリックします。すると、値フィールドの設定画面が表示されます。

計算の種類タブから計算の種類を総計に対する比率に変更します。これで、比率を表示することができるようになります。

OKをクリックするときっかけの比率を表示することができました。

ここで、小数点以下の表示する桁数を調整する方法を紹介します。表示する桁数を変更したい項目名をダブルクリックして、表示形式をクリックします。表示形式機能では、その名の通り、表示する形式を変更することができます。

パーセンテージを選択します。ここでは、小数点以下は表示しないように設定したいので、小数点以下の表示桁数が0になっていることを確認して、OK、OKをクリックします。

すると、小数点以下の表示がなくなりました。

次に行ラベルの欄をきっかけと変更しておきます。

次に項目名をわかりやすく変更しておきます。

値フィールドの項目名を変更するには、フィールド名をダブルクリックすると設定メニューが表示されるので、名前の指定欄から項目名を変更することができます。

ここで、ピボットテーブルの注意点をお伝えしておきます。

ピボットテーブルで作成した集計表は、元となるデータが更新されても自動で反映されません。

反映させるには、

• ピボットテーブルで作成した集計表を選択して、ピボットテーブル分析から更新をクリックするか
• ファイルを保存して閉じて、ファイルを開き治すことで

反映させることができます。

ピボットグラフ(きっかけ)

次に、この集計表を元にグラフを作ります。表のどこかを選択した状態で、ピボットテーブル分析タブからピボットグラフをクリックします。

するとどのようなグラフを作成するかの選択画面が表示されるので、今回は円グラフの中の、円グラフを選択してOKをクリックします。するとグラフを作成することができました。

グラフのドラッグして移動したり、四隅をドラッグすることでサイズを変えることができます。

タイトルはクリックして選択した状態で、再度クリックすると編集することができるので、きっかけと変更します。変更できたら、Shiftキーを押しながらドラッグすることで、垂直に位置を調整することができます。

次にグラフを選択した状態で、上のデザインタブからグラフ要素を追加をクリックし、データラベルからグラフに補足するデータのレイアウトを設定することができます。ここでは、データ吹き出しを選択します。

次に、右側に表示されている、凡例(はんれい)は不要なので、クリックして選択し、Deleteキーで削除します。

最後にドラッグでグラフの大きさやラベルの吹き出しの位置を調整したらグラフは完成です。

ピボットテーブル(満足度)

次に、満足度の集計表とグラフも作成します。

今、作成した集計表をドラッグで選択して、Ctrlキーを押しながらCキーを押してコピーします。

次に、満足度の集計表を作成したいセルをクリックして選択したら、Ctrlキーを押しながらVキーを押して貼り付けます。

貼り付けができたら、行ラベルの欄を満足度と変更しておきます。

次に、その表を選択した状態で右側に表示されている、ピボットテーブルフィールドを変更していきます。

もし、設定メニューが表示されていない場合は、設定したい表を選択した状態で、上のピボットテーブル分析タブからフィールドリストをクリックすると表示することができます。

次は、左側に満足度の項目を表示したいので、行のボックスのきっかけ(集計用)の項目をクリックしてフィールドの削除で削除します。

削除できたら、満足度(集計用)の項目名をドラッグして行のボックスに入れます。すると、左側に、満足度の項目が表示されました。

次に、満足度の回答数を集計をしたいので、回答数と比率も削除して、満足度(集計用)をドラッグして値のボックスに入れます。

次に満足度も比率を集計したいので、再度、満足度(集計用)をドラッグして値のボックスに入れます。次に、項目名をダブルクリックして、値フィールドの各種設定を行います。

ピボットグラフ(満足度)

次に、グラフを作成します。表のどこかを選択した状態で、ピボットテーブル分析タブからピボットグラフをクリックします。

先程と同じく円グラフの中の、円グラフを選択してOKをクリックします。するとグラフを作成することができました。

グラフのドラッグして移動したり、四隅をドラッグすることでサイズを整えます。

タイトルはそのタイトルを選択した状態で、再度クリックして、満足度と変更します。変更できたら、Shiftキーを押しながらドラッグして、垂直に位置を調整します。

次にグラフを見やすく調整します。グラフを選択した状態で、上のデザインタブからグラフ要素を追加をクリックし、データラベルからデータ吹き出しを選択します。

右側に表示されている、凡例(はんれい)は不要なので、クリックして選択し、Deleteキーで削除します。

次にドラッグでグラフの大きさやラベルの吹き出しの位置を調整します。

最後に、行を削除して集計表やグラフの見える範囲を増やします。削除したい行をドラッグで選択して、右クリックから削除で削除することができます。

これで、回答数と比率の集計表とグラフは完成です。

シートをダブルクリックしてシート名を集計(比率)と変更しておきます。ドラッグで使いやすいように並び替えておきます。

保存

ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

ピボットテーブル(年代別・きっかけ)

次に、きっかけの年代別の集計表を作成します。

集計元となる表のどこかのセルを選択した状態で、上の挿入タブから、ピボットテーブルをクリックします。

集計の元となるテーブルが選択されてることと、ピボットテーブルを作成するシートが新規ワークシートを選択されていることを確認したら、OKをクリックします。

今回は、左側にきっかけの項目を表示して、上側に年代の項目を表示して、

• きっかけ(集計用)の項目名をドラッグして行のボックスに入れ、
• 年代(集計用)の項目を列のボックスに入れ、
• きっかけ(集計用)の項目を値のボックスに入れます。

これで、きっかけの年代別の集計表を作成することができました。

最後に各項目名を編集すれば完成です。

ピボットテーブル(年代別・満足度)

次に、満足度の年代別の集計表を作成します。

今、作成したピボットテーブルの集計表を選択した状態で、Ctrlキーを押しながらCでコピーします。

満足度の年代別の集計表を作成したいセルを選択した状態で、Ctrlキーを押しながらVで貼り付けます。

行ラベルを満足度と変更しておきます。

次に、その集計表のどこかを選択した状態で、右側に表示されるピボットテーブルのフィールド設定メニューを設定していきます。もし、ピボットテーブルのフィールド設定メニューが表示されていない場合は、集計表のどこかを選択した状態で上のピボットテーブル分析タブから、フィールドリストをクリックして表示させます。

今回は、満足度を年代別に集計したいので、

• 行のボックスのきっかけ(集計用)を削除して、満足度(集計用)をドラッグして入れます。
• 値のボックスも回答数を削除して、満足度(集計用)をドラッグして入れます。

次に、無駄な行を削除して集計表やグラフを上に詰めます。削除したい行をドラッグで選択して、右クリックから削除をクリックします。

これで、年代別の集計表の完成です。

シート名をダブルクリックで編集モードにし、集計(年代別)と書き換えて、ドラッグで並び替えておきます。

仮のデータを削除する

最後に、仮のデータを削除しておきます。データの1行目以外の行は右クリックで削除をクリックして削除して、入力用データはDeleteキーで削除しておきます。

データを入力していくと、自動でテーブルの範囲が広がり、VLOOKUP関数も追加されることが分かります。

保存

ここでCtrlキーを押しながらSキーを押して、ファイルを保存しておきましょう。

さいごに

以上が、エクセルでアンケート結果を集計してグラフ化したり、年代ごとの集計表を作成する方法でした。