プチアプリケーションのお勧め

ワークシート関数はじめの一歩 (Excel2002)

アンケート処理 (Excel2002)

アンケート処理 (Excel2002)

街頭や何かの会場で行うアンケート処理の事例です。VBAは使っていません。
アンケートの内容(設問と選択肢)は次のようなかんたんなものとしておきます。

1. 設問と選択肢

設問1) 性別を選択してください。[必須 該当するものを○囲み]

   男   

設問2) 年齢をお答え下さい。[必須 月の端数は切り捨ててください]

設問3) お好きな飲み物をつぎの中から選んでください。
     最大3つまで選び、○で囲ってください。
     ひとつもなければお答えいただかなくてかまいません。

    牛乳豆乳果実ジュース野菜ジュース炭酸果汁飲料
    コーラコーヒー紅茶ウーロン茶緑茶

以上3問とします。
設問1は択一で必須、設問2は正整数で必須、設問3は複数回答ありでひとつもない場合も考えられます。

何人かの人にアンケート用紙に記入してもらい、それを回収してきました。
次はアンケート回答をExcelワークシートに入力しようと思います。
1人分の回答を1行とする形式にします。入力準備として回収したアンケート票に連番を振っておきます。

それでは、回答入力用のワークシートを準備しましょう。

2. 回答入力用シート

ブック名は「アンケート処理.xls」、シート名は「回答」としておきます。

「連番」には手作業でアンケート票に振った連番を入力します。
「設問1」「設問2」は複数の回答はあり得ませんから1列づつですが、「設問3」は最大で3つの回答があり得ますから3列用意します。
これでトコトコと入力していってもよいのですが、入力にはミスがつきものです。
入力ミスを極力減らすために、入力規則を設定しておこうと思います。

入力規則設定

入力規則は各セルごとに設定し規則に従わない入力を防ぐものです。
項目(列)ごとに、ルールを決めます。

(1) 連番

1から始まる整数とし、アンケート票は100枚回収できたと仮定します。
したがって規則は「1以上100以下の整数であること」。
空白は不可とします。

(2) 設問1 (性別)

「男」「女」以外は不可(空白も不可)。リストから選択する形にします。

(3) 設問2 (年齢)

連番と似ていますが「0以上200未満の整数であること」とします。
0歳児がアンケートに答えてくれる可能性も排除せず、200歳以上の人はさすがにいないだろうということで。
必須の設問ですから空白は不可とします。

(4) 設問3-1 3-2 3-3 (好きな飲み物)

選択肢以外不可、ただし空白はOK。リストから選択する形にします。

入力規則設定の前に、(2)(4)でリスト入力できるようにするため選択肢リストを作成しておきます。
規則設定の際に選択肢をカンマで区切って登録することもできますが、ここでは「選択肢」シートをこしらえてそこにリストを作成しておきます。

選択肢の入力ができたらリスト部分に名前を付けておきます。
リスト部分を選択しておいて、メニュー [挿入] -> [名前] -> [定義]
「名前の定義」というフォームが開きます。

参照範囲を確認した上で、適当な名前を入力してOKをクリックしてください。
ここでは性別の選択肢は「選択肢1」、飲み物選択肢は「選択肢3」としておきます。
これ以降、ここで付けた名前でセル範囲を指定することができます。

では、「回答」シートに戻って入力規則を設定しましょう。

「連番」から「設問3-3」までそれぞれ設定を行っていきますが、入力規則はコピーできますので、とりあえず入力先頭行(第3行)で設定を行いあとは必要行数分コピーすることにします。

(1) 連番

A3セルを選択しておき、メニュー [データ]->[入力規則]
「データの入力規則」というフォームが開きます。
タブ形式になっています。

[設定]タブ

連番は1から始まる整数ということにし図のように設定します。
空白不可ですから「空白を無視する」のチェックは外します。

[入力時メッセージ]タブ

特に必要ないので「セルの選択時にメッセージを表示する」のチェックは外しておきます。
メッセージ表示といっても、OKボタンをクリックしなければならないようなメッセージボックスが現れるわけではありませんから、「アンケート票連番入力」などのメッセージを設定してもかまいません。

[エラーメッセージ]タブ

「無効なデータが入力されたらエラーメッセージを表示する」はチェックしておきます。
チェックを外しておくと規則に反する入力が行われても無視されますので、規則設定の意味がなくなってしまいます。
タイトル、エラーメッセージはブランクにしておいてもかまいません。既定の文言が表示されます。
面倒でなければ気の利いた台詞を設定してください。
スタイルは開かれるメッセージボックスのタイプを指定するもので、アイコンなどが変わります。
お好みでどうぞ。

[日本語入力]タブ

半角数字入力ですから「オフ」または「無効」を選択します。

(2) 設問1 (性別)

B3セルを選択しておいて「データの入力規則」フォームを開きます。
入力値の種類は「リスト」、元の値欄は「=選択肢1」とします。先頭に等号を付けてください。
空白不可ですから、「空白を無視する」のチェックは外します。

入力時メッセージ、エラーメッセージは(1)と同様に設定してください。
日本語入力は「オン」。

(3) 設問2 (年齢)

(1)連番と数値の範囲が若干異なる以外は同じ設定です。

(4) 設問3 (好きな飲み物)

(2)性別とほとんど同じです。
元の値欄が「=選択肢3」であることと「空白を無視する」をチェックするという点が違うだけです。

入力先頭行(3行目)の設定ができたら、3行目をコピーしておいて、 4行目以降必要な行数分「形式を選択して貼り付け」を行います。貼り付けるのは「入力規則」です。

3. 回答入力

入力規則を設定できましたから後はトコトコと入力していけばよいのですが、トコトコと進めるために入力後の移動先の指定を変更しておいた方がよいかもしれません。
デフォルトでは下のセルに移動することになっており、今回の回答入力用シートでは不便です。
そこで、メニュー[ツール] -> [オプション]で「オプション」フォームを開きます。
[編集]タブを表示させたら「入力後にセルを移動する」がチェックされていることを確認して「方向」を「右」にしたらOKボタンをクリックします。

それでは入力を始めましょう。
設問3の入力が終わったら下矢印キーで次行に移りHomeキーを押してやればワンタッチで連番に移動できます。

4. 集計

まだ20件しか入力していませんが、集計表をこしらえましょう。

集計シートを設け下図のような感じで集計表をこしらえていきます。

設問1および設問3

この二つは基本的に同じです。

選択肢欄には、「=INDEX(選択肢1,B5)」という式を埋め込みました。
直接入力したり選択肢シートからリストをコピーして貼り付けてもいいのですが、 変更があった場合、あちこち修正が必要だと修正忘れが起こりがちなためこのようにしました。

条件にあった件数を数えるにはCOUNTIF関数を使います。

 COUNTIF(範囲, 検索条件)

[範囲]には、回答が入力されている領域を、[検索条件]には件数欄の左隣(選択肢欄)を指定します。
たとえば、設問1の「男」の件数を数える式は下図のようになります。

範囲を実際に入力されている「回答!B3:B22」ではなく「回答!B3:B202」としたのは追加入力を想定してのことです。

設問3の場合、範囲が1列ではなく3列になります。すなわち、「回答!D3:F202」

設問2

回答の年齢をそのまま集計したのでは散漫な集計になってしまいますから、10代、20代と10歳づつクラス分けすることにします。
ただし、100歳以上は一まとめにします。

まず、回答シートにおいて設問2の各回答をクラス分けします。
年齢を10で割って小数点以下を切り捨てればいけそうです。
未使用のH列を「設問2クラス」としてクラスを求める式を埋めていきます。

通常は、「=INT(C3/10)」でいいのですが、100歳以上に対しては一律10にする必要があるためIF関数を使います。
(INTは、小数点以下を切り捨てる関数です。)

 IF(論理式, 真の場合にとるべき値, 偽の場合にとるべき値)

具体的に回答シートの3行目で見てみましょう。

 論理式は、C3>=100
 真の場合(100歳以上)にとるべき値は、 10
 偽の場合(100歳未満)にとるべき値は、 INT(C3/10)

となります。

設問2の代わりに設問2クラスを範囲として件数を数えればよいことになります。

それでは、集計表に取り掛かりましょう。
設問1や設問2で「No」としていた欄を「CL」としてクラス(番号)0から10を入力します。
年齢層の欄は直接入力でかまいません。(元になるリストがありませんから)

最後に、件数欄にCOUNTIF関数を設定します。
範囲は回答シートの「設問2クラス」(H列)、検索条件はC列ではなくB列です。

これでアンケート票を追加入力すれば即座に集計表に反映される仕組みができました。

2010/10/20

ページ先頭