Excelで特定セルの書き込みを検索・まとめたい

質問

欲張ったタイトルですみません。
アンケートをExcelで集計しています。
選択肢の中からいくつかを選ぶ質問と、
「ご自由にご記入ください」の質問があります。

Excelワークシートで配布し、回答をもらいました。
すべてのワークシート（70枚前後）ひとつのブックにまとめて集計しています。
選択問題については、COUNTAで集計したのですが、
自由記入問題をどうしようか考えています。

やりたい方法は次の通り。
1.この質問は答えている場合と空白の場合があるので、
その回答部分のセルが「空白になっていない」シートを検索したい。

2.できれば、そのセルに記入されている回答をひとつのセルに自動的にまとめたい。（文字列での回答です）

2は無理かもしれませんが、できれば1だけでも実現できないかと思います。

OSはwindows2000、ソフトはExcel2002です。
その他質問で不足があれば補足します。
よろしくお願いいたします。

回答No.7

chirop · ベストアンサー

再びNO.2の者です。昨日は時間がなかったので、中途半端な回答ですいませんでした。（NO.3）
NO.5の方のシート名変更の方法はとても参考になりました。使わせていただきます！
マクロを使わない方法であれば、下記もおすすめです。
まずは複数シートを1つのデータベースにまとめた方が何かと扱いやすいと思います。
前提として、シート名に何らかの通し番号がついている方が手間が少なくて済みます。
（回答1,回答2やsheet1,sheet2のように。なっていなければNO.5のマクロで変更できますね。）

【回答用シートのイメージ】シート名：sheet1
　｜ 　Ａ　
--------------
１｜＜問１:設問～＞
２｜○
３｜＜問２:設問～＞
４｜あああ

↑イメージ合ってますか？
（レイアウトがうまく表示されていなかったらごめんなさい）

【集計用データベースのイメージ】これを作成する。
　｜　Ａ　　 ｜ Ｂ ｜ Ｃ
---------------------------
１｜シート名｜問１｜問２
２｜sheet１ ｜○　｜あああ
３｜sheet２ ｜×　｜いいい

【集計用データベース作成手順】

1.集計用シートを新規で作成。

2.1行目は項目名を入力。

3.回答シートのシート名をA2以降に入力。（連番であればオートフィルで一発コピー）

4.下記の計算式を入力。
B2セル：=INDIRECT($A2&"!A2")
C2セル：=INDIRECT($A2&"!A4")
※$A2は集計用シートで回答用シート名が入力されているセル名。
※＆以降は回答用シートからひっぱってきたい回答欄のセル名を入力。
※D2セル以降も同様に入力していく。

5.2行目の計算式を下までコピー。

以上の手順で複数シートを１つのデータベースにまとめることが出来ます。
要望の解釈さえ合っていれば、この手の作業にいつも使用している方法なので大丈夫だと思います。
データベースになってしまいさえすれば、あとはどんな集計でも自由自在です！

お礼

ありがとうございました。
この方法はまだ試していないのですが、やってみたいと思います。

みなさま、具体的な例を挙げた丁寧な回答を頂き、本当にありがとうございました。勉強になりました。

回答No.6

imogasi

＃４です。
＃４でアンケートのシートの例示の確認を頂けなかったので下記を仮定して、（ＶＢＡを使わない）集計方法を例示します。ご参考に。
ＡＮＱ１　満足ですか（１）はい（２）いいえ（３）不明
ＡＮＱ１回答　２
ＡＮＱ２　欲しいですか（１）欲しい（２）いいえ（３）
安ければ
ＡＮＱ２回答　３
ＡＮＱ３　そう思いますか（１）思う（２）思わない（３）思うときもある
ＡＮＱ３回答　１
以下１２セットある。
ここで回答数字を、数字に応じて場所（セル、列）の「１」に置き変える。Ｃ２，Ｄ２，Ｅ２・・を考えて左からの位置に１を入れる。この１を全シートのクシ刺し加算すると、その選択肢に何人選択したたか判る仕組みに組みたて直すわけです。
例えばＢ２に回答１とあれば、Ｃ２に１
Ｂ２に２とあれば、Ｄ２に１
Ｂ２に３とあれば、Ｅ２に１と入れる。
それをやるには、関数式はＣ２に=IF($B2=COLUMN()-2,1,"")といれ右方向へ式を選択肢数だけ複写する。
Ａ３は第2質問文で、飛ばして、
Ｂ４（第２質問の回答）に付いてはＣ４：Ｅ４にＣ２：Ｅ２を複写する。
これを７０人分行う（ちょっとつらいが1時間で終るでしょう。
そして集計シートにＣ２：Ｅ２，Ｃ４：Ｆ２、Ｃ６：Ｅ６
・・（セルは１かスペース）を３Ｄ集計する。
それで回答状況がわかる。
３Ｄ集計は=SUM(Sheet1:Sheet3!C2)のようにシートについて、「から」「まで」を指定できるのでそれを使う。

お礼

ありがとうございました。
この方法はまだ試していないのですが、やってみようと思います。

回答No.5

tascany

2はNo.2の回答を支持します。1については、全シート書式が同じなら、簡単なマクロでかなりの部分を自動化できると思います。
1）70枚のシートの一番左端に集計用のシートを用意する（書式は他のシートと同じで）。
2）集計用シートで自由記入問題１の問題が入ったセルをA1とすると、A2からA71まで空白セルを挿入する。
3）A2をアクティブにしたうえで、下のマクロを実行する（バックアップをとっておくことをお忘れなく・・・）。※ビジュアルベーシックエディタの標準モジュールにそのまま貼り付けたら使えます。

Sub 集計()
For counter = 2 To 71
ActiveCell = "=Sheet" + CStr(counter) + "!$A$2"
ActiveCell.Offset(1, 0).Select
Next
End Sub

4）集計用シートで問題２のセル（A72）をアクティブにする。
5）他の70枚のシート上、問題２の回答はA4にあるはずなので、うえの"!$A$2"の部分を"!$A$4"に書き替える。
6）以下、問題12まで同様に。縦に長くなりすぎるのがいやなら、問題2の問題セルをB1に移動させても、先のマクロは使えます（空白以外のセルの抽出については、No.2の回答参照）。
7）ただし、うえのマクロを使うには、左から二番目のシートから順にSheet2、Sheet3・・・というふうに並んでなければだめです。ですので、もしそうなってないなら、下のマクロを実行しておいてください。

Sub シート名変更()
For counter = 2 To 71
Sheets(counter).Select
ActiveSheet.Name = "Sheet" + CStr(counter)
Sheets(counter + 1).Select
Next
End Sub

マクロについては、いちおうテストしてますが、実行のさいは、とくにシートの枚数に注意してください。不足してたらエラーになりますので。

お礼

ありがとうございます！
できました！すごいすごい！
勉強になりました。

回答No.4

imogasi

＃１です。補足有難うございました。
エクセルで、数十人数百人のアンケート集計や集約をするのは難しいことだと思いますよ。アンケートを回答者に出す前に、返ってきてから後のことを良く考えるべきだった。
まとめの１ブックに、回答者別に７０シートあり（各ブックより手作業でコピーした？ご苦労さん）。
各シートは
ＡＮＱ１　ＸＸＸＸＸ（文章？）
ＡＮＱ１回答　ＹＹ(数字？）
ＡＮＱ２　ＸＸＸＸＸ
ＡＮＱ２回答　ＹＹ
・・・・
・・・１２セット
らしい。
最終形として、エクセルは２次元（縦横）の表なので、どういう形にしたいのでしょうか？縦方向に何を並べ、横方向に何を並べるのか。
アンケートはいわば３次元（縦横のシートを７０枚積み上げたイメージ）を２次元に変えるって難しそう。
エクセルの関数はこう言うのが苦手で、ＶＢＡでも使わないと無理ではないかなと思います。
最終形の前に、出来れば１シートにまとめるのが定石でしょうか。

お礼

ありがとうございました。
VBA・・ちょっと難しそうですね。

＞エクセルで、数十人数百人のアンケート集計や集約をするのは難しいことだと思いますよ。アンケートを回答者に出す前に、返ってきてから後のことを良く考えるべきだった。

そうなんですね。
Accessとかのほうがいいんでしょうか。
お薦めのソフトがあれば教えていただけますか。

回答No.3

chirop

NO.2の者です。
補足を拝見したら、シートが複数にまたがっているようで、やはり内容が少し違っていましたね。すいません。
まず複数のシートからindirect関数で１つのシートにデータベース化した方が何かと加工しやすいと思うので、その作業をしてから、NO.2の作業を行えば可能だと思います。
もっと効率的な方法があれば別の方アドバイス願います。

お礼

ありがとうございました。

回答No.2

chirop

ある程度想像で解釈した内容に対して回答しますので、要望と違っていたらすいません。

1.については、単純にオートフィルタ（データ／フィルタ／オートフィルタ）をかけて「空白以外のセル」で抽出すれば出来ると思います。

2.については裏技的ですが、下記の手順で出来ると思います。
・１で抽出したデータの該当列の可視セルだけ選択（編集／ジャンプ/セル選択／可視セル）し、コピー。
・一旦、Word等の文書にテキストで貼付。
・一旦貼り付けたテキストを再度コピー。
・貼り付けたいセルをクリックし、上部の文字入力欄に貼付。（そのまま貼り付けると複数セルにまたがってしまう）
※上記のやり方であれば、１つ１つの回答で改行されるので見やすいと思います。（但し、あまりにデータが多いと途中で切れてしまう可能性があります。）
※改行したくなければ、セルの値を結合すればいいと思うのですが、＆やconcatenate関数では件数が多いと指定が面倒です。
⇒他にいい結合方法（複数セルを，等で区切って１つのセルに結合する方法）があれば、私も知りたいので、どなたかアドバイス願います。

お礼

ありがとうございました。
オートフィルタというワザを忘れていました。

回答No.1

imogasi

＞アンケート
質問は沢山ありましたか（例えば問１－問１０までとか）
＞Excelワークシートで配布し、回答をもらいました
７０ブック（中身のあるのは１シートのみ）あるということですよね。
＞ワークシート（70枚前後）ひとつのブックにまとめて集計しています。
７０ブックを１ブックにどのような方法でまとめましたか？
現在ブックやシートの状態はどうなっていますか。シートの数は？
凡その、列・行のレイアウトは。

補足

(1)質問数は全部で12問です。
(2)まとめは、それぞれのブックから回答されたワークシートをひとつのブックに移動してまとめました。
ひとつのブックに70枚のシートが入ってる状況です。
(3)列・行のレイアウトは、質問の入ったセルの下に解答欄が入ったセルがあり・・の繰り返しです。
選択問題は選択肢をひとつずつ縦に並べています。
・・・補足になっているでしょうか（汗）