サンプルファイルをご覧になられた方は、なんでこんなに横長で複合参照のわかりづらいシートにしているのだろう？と思われたかも知れません。

できるだけコピー＆貼り付けで集計表やグラフを作成するために行っている訳ですが、最後にコピー＆貼り付けで、集計表及びグラフの作成を行う動画を作ってみました。（小さくで見づらい場合はこちらから。）

【サンプルファイルをダウンロードする】
●output.lzh (51KB) をダウンロード
●output.xls (258KB) をダウンロード

DCOUNTA関数を使って、条件に該当するデーターの数を数えている訳ですが、DCOUNTA関数に限らず、データーベース関数は引数のCriteria（クライテリア）を指定する時に、フィールド名を含める必要がある（最低２行必要になってしまう）ので、どうしてもセルの参照が複雑になってしまいますね。

他に何かもっとうまい方法がある気もしますが、私が思いつくのはこのくらいです。色々解析して頂き何かの参考になれば幸いです。

筆が遅いのと、少し趣向を変えたものにしたいので、Excelネタはしばらくお休みします。ある程度書きためてから公開する事にします・・・。

この記事のリンク | コメント[0]

EXCELネタは書くのが大変なので実は心が折れていましたが、ここ最近「EXCELでアンケート集計」へのアクセス数がかなり増えているので、もう一度頑張って書こうと思います

【サンプルファイルをダウンロードする】

●output.lzh (51KB) をダウンロード

●output.xls (258KB) をダウンロード

それではアンケート集計編の続きで、今回は「性別」シートを基にデーターベース関数であるDCOUNTAの基本的な部分を解説します。

DCOUNTAは、Database(データベース)の指定されたフィールド(列)を検索し、条件を満たすレコードの中の空白でないセルの個数を返す関数です。と言っても分かりづらいと思いますので、実際の計算式をみながら解説します。

言葉にすると分かりづらいのですが、実際に計算式を見ると結構シンプルですので、まずはB5のセルの計算式を見てみましょう。（たぶんヘルプの解説で心が折れちゃう人が多いのだろうなー、と思います。）

B5の計算式：　=DCOUNTA(RAWDATA,$Q6,$T5:$U6)

ざっくり言うと、RAWDATAという名前をつけた範囲で、性別が「男性」で評価１に「良い」と入力されているものをカウントする式になっています。データベース関数を使う時には少し注意点がありますので、引数の指定方法について説明します。

Databace（データーベース）の指定

DCOUNTA(Database,フィールド,Criteria)のDatabese部分の指定になります。
アンケート結果の入力　その２で触れましたが、よく参照する範囲は名前をつけておくと便利です。
今回はデータ（アンケート結果）を入力した範囲（「入力」シートの「A3:H203」）に、生データという意味でRAWDATAという名前をつけています。

で、重要な事は、範囲を指定するときに項目名（フィールド名）まで含めて指定する事です。

フィールドとCriteria（クライテリア）の指定

性別が「男性」で評価１に「良い」となっているものの数を計算したいので、フィールドにはカウントする項目名である「評価１」を指定しています。また、Criteriaには「性別」の項目が「男性」、「評価１」の項目が「良い」となるように指定します。

このようにデータベース関数は「項目名」で指定するというのが、他の関数とは少し違う所ですね。

また、COUNTIF関数など似たようなものはありますが、データーベース関数では複数の条件で集計できるのも便利です。Excel2007からCOUNTIFSなどの関数も出てきていますが、下位互換を考慮すると少し使いづらいですね。

この記事のリンク | コメント[0]

今回は全数集計の評価１の集計で使った計算式をコピーして評価２の集計を作る部分を解説します。前回でも少し書きましたが、できるだけコピー＆貼り付けで計算式が書けるようになりたいですね。

【サンプルファイルをダウンロードする】
●output.lzh (51KB) をダウンロード
●output.xls (258KB) をダウンロード

まずは、B6のセルをコピーします。

そのままG6に貼り付けます。

B6とG6の計算式を見比べてみましょう。

B6の計算式
=COUNTIF(入力!$E$4:$E$203,A6)

G6の計算式
=COUNTIF(入力!$E$4:$E$203,F6)

COUNTIF関数の引数を見ると、検索条件はA6からF6へと相対的に変わっていますが、範囲が全く同じになってしまっていますね。

評価２は、入力シートのF列に入力されているので、範囲の指定をE列からF列に変更してあげればOKです。

G6をアクティブセルにし、F2キーを押します。
F2を押すと、Enterキーを押すか、他のセルをアクティブにしない限り、矢印キーで自由にカーソルが移動できるようになります。

計算式が編集できるようなりますので、セルの計算式を直接修正しても良いのですが、今回は数式バーをクリックし、$E$4:$E$203の部分のEをFに修正してみて下さい。（範囲の始点と終点の二ヶ所です。）

G6のセルを下方向にコピーすれば評価２の集計も完了です。評価３の集計は範囲がG列になるだけで同様にできます。

前回解説した関数の挿入を３回を繰り返して計算式を入力するより手数は減りますね。
こんな風に計算式をコピーして、一部分だけ変更という手法をマスターできると便利ですよ。

この記事のリンク | コメント[0]

今回はCOUNTIF関数による集計部分の解説をしていきたいと思います。

COUNTIFは、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返す関数です。

それでは、サンプルの「全数」シートをもとに計算式を入れてみたいと思います。

【サンプルファイルをダウンロードする】
●output.lzh (51KB) をダウンロード
●output.xls (258KB) をダウンロード

COUNTIF関数自体は引数も少なく直接計算式を書いたほうが楽ですが、今回はあえて「関数の挿入」を使って計算式を入力してみたいと思います。

下図のようにB6のセルをアクティブにし、ここに計算式を入れていきます。
（あらかじめA6～A10のセルに評価の項目を入力しておきます。）

「挿入」→「関数」を選択します。

「関数の挿入」のダイアログより、関数の分類を「統計」にし、COUNTIFを選択します。

Excel2007の場合、「数式」→「その他の関数」→「統計」よりCOUNTIFを選択します。
また、数式より関数の挿入をクリックすると、上図とまったく同じ関数の挿入ダイアログが表示されるので、慣れると楽そうですね。

では、関数の引数を入力していきます。（引数は「ひきすう」と読みましょう。）

まず、範囲をアクティブにします。

入力シートの評価１を対象の範囲と指定したいので、「入力」シートをクリックし、E4のセルをクリックします。すると、範囲には自動的に入力!E4と入力されます。

続けて、E203まで範囲指定します。
広範囲の指定は、[Shift]+[Ctrl]+[End]を使用すると楽です。

E4をクリックした後、:(コロン）で区切ってE203をクリックするといった方法でも可能です。色々方法はありますので、やりやすい方法で行えばよいと思います。

[

この範囲は、計算式を下方向にコピーした時も参照範囲は変えたくないので、「絶対参照」にしておきます。[F4]を押すと行列ともに絶対参照になります。

次の引数の検索条件をアクティブにします。
全数シートのA6をクリックします。
こちら下方向にコピーした時、一緒に参照範囲も下方向に移動させたいので相対参照のままにしておきます。

以上で引数の指定は完了ですので、ダイアログの[OK]をクリックします。
ワークシートに計算式が入力できました。これを下方向にコピーしていけば評価１に対する、それぞれの評価項目の人数を計算できます。

同じような計算式を何度も書くのは大変です。また、計算式を入れる回数が増えるという事はそれだけ間違う可能性も増えるという事なので、できるだけコピー＆貼り付けで対応できるようにしたいですね。

そこで避けて通れないのが相対参照と絶対参照、その両方を用いる複合参照です。

そんな訳で、次回は割合の部分と評価１で使用した計算式をコピーし、評価２の部分の計算式を作成する部分を解説します。

セルの参照についてあまり細かい解説をする予定はありませんが、実践してみてイメージを掴んで欲しいと思います。

この記事のリンク | コメント[0]

集計編を書き終えるまでに随分時間がかかりそうなので、まずは集計表のサンプルを掲載します。今回はシート構成など全体的な部分を少しだけ解説したいと思います。

入力編のサンプルを元にデータを200件ほど入れ、それを集計しています。

PDFで集計結果を確認する
●output.pdf (53KB)

ファイルをダウンロードする
●output.lzh (51KB) をダウンロード
●output.xls (258KB) をダウンロード

■ワークシートの構成

【属性】
回答者の属性毎の割合をCOUNTIF関数で求めています。

【全数】
サンプルでは、汎用的な例にしたので、評価１、評価２、評価３と言った名称にしていますが、スタッフの接客、設備の清掃状況などといった評価を集計していると思って下さい。

【性別】
性別毎の評価の集計になります。DCOUNTA関数を使って複数条件の集計を行っています。

【年代】
年代毎の評価の集計になります。DCOUNTA関数を使って複数条件の集計を行っています。

入力では、16才未満、16～19才、20才代となっているものを、20才代以下といったようにまとめて集計する事もできます。

【ピボットグラフ】
ピボットグラフを使って、ちょっとこういった形で集計したい・・・という事ができるモノを作りました。

今回のサンプルは属性が性別と年代の２つだけですが、実際はもっと多くの属性や評価項目があるので、できるだけコピー＆貼り付けで集計表が作成できるように設計したいものです。

また、この手の集計表やグラフを作るとアレもコレもという要望が出てくるものです。

全ての要望を聞いていると際限が無くなってしまうので、ピボットグラフによって見たいデータの集計が簡単できるようにしておくと便利です。

とりあえず次回はCOUNTIF関数による全数の集計部分を書こうかと思っていますが、この集計編は全くプロットが練れていません。

何をどこまで書こうか？と、根本的なとこから考え中ですので、急遽変更するかも知れませんがご容赦下さい。

この記事のリンク | コメント[0]

文字の長さが特定できない場合、文字の制御を

・折り返して全体を表示する
・縮小して全体を表示する

にする事で、入力した文字をセル内に表示させる事ができます。

とは言っても前者の場合は、行の高さを固定にしてしまうと、下図のように折り返した事によって行の高さを超えたデータを表示しきれなくなってしまうし、後者の場合は、あまり文字数が多くなると文字のサイズが小さくなって何が書いてあるか判別できなくなってしまいます。

その辺を踏まえて、自由な意見を入力する欄としては
・行の高さを自動に
・折り返して全体を表示する

という設定にしておくと良いでしょう。
そんな訳で、それぞれの設定方法を記載します。

まずは行の高さから。
EXCELの初期値では、行の高さは自動になっているので、特に指定してしまわない限り必要はありませんが、念のために「行の高さを自動調整にする」方法を記載しておきます。

行を選択します。

「書式」→「行」→「自動調整」を選択します。

続いて折り返して全体を表示する方法です。
設定したいセル（サンプルの場合ならH4のセル）をアクティブセルにします。

[CTRL]を押しながら[1]を押すか、「書式」→「セル」を選択します。
「配置」のタブを選択し、「折り返して全体を表示する」のチェックをONにします。

以上で完了です。

まずは、最初の1行をきっちりつくりあげましょう。

入力テストを数件行ってみます。入力規則のリスト、制限、変換モードなどが正しく行われるか確認します。それで問題がなければ、アンケート入力に必要な件数分コピーして行を増やします。入力が終わった時点で、不要な行は消せばよいので少し多めに作成しておくと良いですね。

今回は省略しますが、EXCEL2003にはリストという機能があり（入力規則のリストではなく）、行数を動的に増やす表をつくる事もできます。個人的にはあまり便利に感じないので、使ったコトは少ないですが。

ようやく・・・ですが、次回からアンケート結果の集計編になります。

この記事のリンク | コメント[0]

それでは、入力規則の画面から名前をつけた範囲を参照し、リストを定義してみたいと思います。
サンプルのEXCELファイルのダウンロード input.xls (20.5KB)

まずは、性別のセルに入力規則を設定してみます。

性別は男性か女性かだけの選択なので、「定型の選択項目のみ入力できるパターン」での設定になります。

「データ」→「入力規則」を実行します。

まず、入力の種類を「リスト」にし、空白を無視するのチェックをOFFにします。
元の値に　=性別　と入力します。

始めに=（イコール）を入力し、その後に定義済みの名前を指定する事によって、入力規則上でその範囲を参照できるようになります。

今度は居住地を例に、定型の選択項目を選択できるし自由に入力もできるパターンの作り方を解説します。

前述の性別の部分と、ほとんど同じ設定ですが、参照する範囲（リストの元の値）が=居住地となる点、空白を無視するのチェックをONのままになる点が異なります。

リストからの入力だけでなく自由に入力するため、もう一工夫加えておきます。

複数人で入力する場合や、シート作成者と入力者が異なる場合、ここにはリストからの入力だけでなく自由に入力できる旨を明示しておくと、共通の認識を得やすく便利です。

入力時のメッセージのタブを選択し、「セルの選択時にメッセージを表示する」のチェックをONにし、メッセージの内容を入力します。

もう１点、いちいち変換モードを切り替えなくて済むように、居住地がアクティブセルになった時、日本語入力のモードがONになるように設定します。

日本語入力のタブを選択し、リストからオンを選択します。

選択時のメッセージはやりすぎると「うざったい」です。代替としてコメント表示という方法もありますので、必要かどうかよく考えて設定しておくと良いですね。

入力規則で設定しているのは、概ねこんなところです。次回はセルの書式設定の部分を解説します。

この記事のリンク | コメント[0]

それでは、今回からアンケート結果の入力シートをサンプルをベースに、作り方を解説していきたいと思いますが、リストの選択項目を簡単に追加変更できるようなつくりにしているので、今回はそのあたりから解説していきます。

サンプルのEXCELファイルのダウンロード input.xls (20.5KB)

では、ブックの構成から。
ワークシートは入力用とリスト用を分けて作成しています。

（リストでの選択項目を作成したシート）

上図のようにリストの選択項目だけまとめて１つのシートにし、入力規則でリストを設定していきたいと思います。

入力規則でリストを使用する際に、直接選択項目の値を指定する方法もありますが、件数が多い場合は設定が面倒なので、リストとして参照したい範囲に名前をつけて使用する方法をとると便利です。

参照範囲に名前をつける。
EXCELでは頻繁に参照したいセル範囲、入力規則や条件付き書式などから参照したいセル範囲に名前をつけておくコトができます。

ここから、サンプルのEXCELファイルを元に年齢の選択項目に名前を設定する手順を解説していきます。

リストのシートをアクティブにし、B4からB13までの範囲を指定します。

値は12行目までしか入っていませんが、追加があっても簡単にできるように少し冗長化しています。

続けて「挿入」→「名前」→「定義」を選択します。

名前に「年齢」と入力します。
参照範囲を確認し、OKをクリックします。

名前はわかりやすいもので構いません。より汎用的に作りたい場合は、項目１といった名称でも構わないと思います。（サンプルでは既に名前を定義してあります。）

このように参照範囲に名前を定義しておくことによって、このブック上から「年齢」と指定すると、B4からB13の範囲を参照できるようになります。

実際に値が入っているのはB5からB12までですが、サンプルではB4からB13まで、前後に空白のセルを含めて範囲指定をし、「年齢」と名前を定義しています。
この前後に空白を含めて参照範囲を指定するのがポイントになります。

下図では一番下が選択された状態でリストが表示されてしまっています。

B4の空白セルを指定せずB5から範囲指定した場合、このようになってしまいます。
入力しようとしているセルの値が空白のため、予備の空白部分（B13）が選択され状態でリストが表示されてしまいます。

そんなわけで、B4から範囲指定しているのは、リストの初期値をコントロールするためのちょっとしたコツだったりします。（参照範囲を冗長化している場合は必須ですね。）

次回は、ここで定義した名前を入力規則で参照する方法を解説していきます。

この記事のリンク | コメント[0]

前回からだいぶ間が空いてしまいましたが、EXCELでアンケート集計の入力編です。

本来であれば入力と出力（集計）の因果関係を無視できないのですが、そうするとわかりづらくなってしまうので、今回は「どんな集計を行うか」という部分はあえて無視して、こんな場合にこんな入力方法ができます！という例をあげていきたいと思います。

１．定型の選択項目のみ入力できる
性別や年代などの回答者の属性を入力する場合や、該当するものにチェックを入れるというような、固定の回答のみのパターンですね。EXCELで実現する場合は入力規則を使いリストから選択できるようにします。

リストに無い項目を入力する事はできません。

２．自由に入力できる
自由に意見を書いてもらう場合です。長さが一定ではないので、EXCELでは行の高さや配置（文字の制御）で少し設定が必要です。文字入力が多いので、入力規則では「変換モードをON」にしておくと便利です。

３．定型の選択項目でも入力できるし、自由に入力もできる

上記１と２の組み合わせですね。
集計方法によっては使えないかも知れませんが、例として定型の回答以外に、「その他」を用意し、その他を選択した場合は内容を入力してもらうパターンがあります。

また、すべてを定型化してしまうと不便になる事もあります。よくネット上で住所を入力する際、４７都道府県すべてリストに表示されると、探すのが大変だったりします。ある程度限定できるもの（多数になるもの）はリストから、そうでないもの（少数）は直接入力してしまうという使い方も、考え方の１つではないでしょうか。

リストから選択するのではなく、直接入力できた方が楽な場合もあります。
全ての市区町村をリストに登録すると件数が膨大になり、探すのが大変になってしまうので、この例では、よく出てくる地域だけリストに登録し、それ以外の場合は任意に入力するようにしています。

入力規則というと、なんだか規制するだけで使い道が無いように感じますが、実際は入力を支援（サポート）するものですので、うまく使えると便利です。

次回から、実際にこれらの作成方法を解説していきます。

この記事のリンク | コメント[0]

よくファミレスなどで見かけるアンケート。
主にCS（顧客満足）調査などに使われるもので、やはり当社でも行っており、ある程度の規模のものであれば自社で結果のとりまとめを行っています。

その時に実際作ったものをベースに、「紙で記入してもらったアンケートをEXCELで統計をとる」という部分の解説をしていきたいと思います。（他社ではどの様に集計しているのでしょうか、気なるところです。）

アンケートは質問と回答を定形化したものです。

目的などを度外視すれば結構単純なものなので、結果入力用のワークシートを作ること自体は誰でも可能です。ですが、アンケート結果を入力する時に、1件1件は大した手間でなくても、数百件、数千件と繰り返し行うと膨大な手間になってしまいますので、生産性を意識した工夫が重要です。

繰り返し行うものこそ、いかその仕組みを作るかによって、その後の生産性が大きく影響してくるので、どうしたらより良くなるか考え、工夫できる人材になりたいものですね。（これはEXCEL限った話ではありません。）

そんなコトを踏まえつつ、

入力編では入力規則を使って定型化された回答の入力を省力化する方法を、集計編ではデーターベース関数（DCOUNTA関数）を使った集計表の作成と、それを元にグラフを作っていく方法を。と言うように入力編と集計編をわけて解説していきたいと思います。

アンケートを題材にしていますが、雑多な情報を整理し集計するという意味では、色々応用がきくので是非習得して欲しいと思っています。

私がアンケートの集計を依頼された時に、まず何を行ったかというと、アンケート用紙をじっくり見る事から始めました。どんな質問と回答方法があるのか。それぞれ、どのようにEXCELで結果を入力をさせるのが良いか想定しました。

１．定型の選択項目のみ入力できる
２．自由に入力できる
３．定型の選択項目でも入力できるし、自由に入力もできる

アンケートの結果を入力する場合、概ねこの３パターンがありました。EXCEL上でもこの３パターンに対応した入力方法を準備できると便利です。

次回はもう少しこの辺を掘り下げて解説したいと思います。

この記事のリンク | コメント[0]