EXCELのアンケート集計方法を教えてください

質問

お世話になります。

ＥＸＣＥＬを使ってアンケートの集計をしたいのですが、下記のような表の計算式はありますでしょうか？

簡単に内容を説明しますと、一人の人が1番～3番まで気に入ったものを選択し、
選択項目ごとに、1番を選んだ人の名前、2番を選んだ人の名前・・・というように
個数の合計ではなく、名前を列挙することができたらと思っています。

いろいろ検索してみたのですが、どうしても個数の合計方法しか見つけることができませんでした。

ご意見をいただけますと助かります。
よろしくお願いします。

回答No.6

kagakusuki

アンケートの回答者の中に同姓同名の人物がいても構わない方法です。
　今仮に、御質問欄の添付画像にある様なアンケートの回答が入力された表がSheet1上に作成されていて、Sheet3のA列を作業列として使用して、Sheet2に選択項目と回答内容ごとの、回答者の名前を表示させるものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","","◆"&INDEX(Sheet1!$B:$F,ROW(),1)&"◇"&COUNTIF(INDEX(Sheet1!$B$1:$F$1,1):INDEX(Sheet1!$B:$F,ROW(),1),INDEX(Sheet1!$B:$F,ROW(),1))&"◆"&INDEX(Sheet1!$B:$F,ROW(),2)&"◇"&COUNTIF(INDEX(Sheet1!$B$1:$F$1,2):INDEX(Sheet1!$B:$F,ROW(),2),INDEX(Sheet1!$B:$F,ROW(),2))&"◆"&INDEX(Sheet1!$B:$F,ROW(),3)&"◇"&COUNTIF(INDEX(Sheet1!$B$1:$F$1,3):INDEX(Sheet1!$B:$F,ROW(),3),INDEX(Sheet1!$B:$F,ROW(),3))&"◆"&INDEX(Sheet1!$B:$F,ROW(),4)&"◇"&COUNTIF(INDEX(Sheet1!$B$1:$F$1,4):INDEX(Sheet1!$B:$F,ROW(),4),INDEX(Sheet1!$B:$F,ROW(),4))&"◆"&INDEX(Sheet1!$B:$F,ROW(),5)&"◇"&COUNTIF(INDEX(Sheet1!$B$1:$F$1,5):INDEX(Sheet1!$B:$F,ROW(),5),INDEX(Sheet1!$B:$F,ROW(),5))&"◆")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA2以下に、a～e等の、回答者の名前を抽出すべき選択項目を入力して下さい。

　次に、Sheet2のB2以下に1～3などの、回答者の名前を抽出すべきアンケートの回答内容を入力して下さい。
　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(OR($B2="",$A2=""),"",IF(COUNTIF(Sheet1!$B$1:$F$1,$A2),IF(COLUMNS($C:C)>COUNTIF(Sheet3!$A:$A,SUBSTITUTE(REPT("◆*",COLUMNS(Sheet1!$B$1:$F$1))&"◆","*",$B2&"◇*",MATCH($A2,Sheet1!$B$1:$F$1))),IF(COLUMNS($C:C)=1,0,""),INDEX(Sheet1!$A:$A,MATCH(SUBSTITUTE(REPT("◆*",COLUMNS(Sheet1!$B$1:$F$1))&"◆","*",$B2&"◇"&COLUMNS($C:C),MATCH($A2,Sheet1!$B$1:$F$1)),Sheet3!$A:$A,0))),IF(COLUMNS($C:C)=1,"「その項目はありません」","")))

　次に、Sheet2のC2セルをコピーして、C3～C16の範囲に貼り付けて下さい。
　次に、C2～C16のセル範囲をまとめてコピーして、C列よりも右にある列に貼り付けて下さい。
　すると、下の添付画像にある様に、回答者の名前が抽出されて表示されます。

お礼

kagakusukiさん

2回にわたって詳細な計算式を教えてくださりありがとうございました。

おかげさまで、あっという間に望んでいた集計をすることができ、とても助かりました。

回答No.5

bunjii

作業用テーブルを使えば簡単な関数のみで目的に合う集計ができます。

添付画像の作業テーブルは別シートにすることも可能です。

I2=IF(B2=1,$A2,"")
I2の式をJ2～M2へコピー

I3=IF(B3=1,I2&" "&$A3,I2)
I3の式をI3～M7へコピー

B24=SUBSTITUTE(TRIM(I7)," ",CHAR(10))
B24の式をC24～F24へコピー

これで回答1の集計は完了
回答2～3は回答1と同等の方法になるので応用してください。

お礼

bunjiiさん

ご丁寧にありがとうございました。
このように簡単な方法があるのですね。

参考にさせていただきたいと思います。

回答No.4

tom04

No.2です。

＞また、書いてくださったコードはどちらに入力するのでしょう。
の件について・・・

もう一度詳しく書いてみます。
前回の画面上のコードを（Sub ○○～End Sub ’この行まで）をドラッグ　→　右クリック　→　コピー！

次にExcelが立ち上がっている状態で　Altキーを押しながらF11キー（ファンクションキー）を押下
→　「Microsoft Visual Basic for Applications」という画面が立ち上がります　→　そのメニューの中に
「ファイル」「編集」等々がありますので、その中の「挿入」をクリック　→　「標準モジュール」を選択
→　白い画面（VBE画面が表示されます）カーソルが点滅していると思いますので、
そこで右クリック　→　貼り付け　
これでコードが貼りつきましたので、Excel画面に戻って
Altキーを押しながらF8キー（ファンクションキー）を押してみてください。
マクロダイアログボックスが表示されます。
マクロの種類は一つしかないはずですので、
そのマクロが選択されているのを確認して「実行」をクリックしてみてください。

おそらくご希望通りになると思います。m(_ _)m

お礼

tom０４さん

2回にわたって詳細に教えてくださってありがとうございました。

このような方法があることを知らなかったのでとても勉強になりました！

回答No.3

kagakusuki

前提として、アンケートの回答者の中に同姓同名の人物がいた場合には、「鈴木1」、「鈴木2」等の様に、区別がつく様になっているものとします。（同姓同名の人物の区別がつかない場合には、以下の方法のままでは駄目で、もう少し工夫が必要となります）

　まず、H2以下に、a～e等の、回答者の名前を抽出すべき選択項目を入力して下さい。
　次に、I2以下に1～3などの、回答者の名前を抽出すべきアンケートの回答内容を入力して下さい。
　次に、J2セルに次の関数を入力して下さい。

=IF(OR($I2="",$H2=""),"",IF(COUNTIF($B$1:$F$1,$H2)=0,"「その項目はありません」",IF(COLUMNS($J:J)>COUNTIF(OFFSET($A:$A,,MATCH($H2,$B$1:$F$1,0)),$I2),IF(COLUMNS($J:J)=1,0,""),INDEX($A:$A,MATCH($I2,INDEX($B:$F,IF(COLUMNS($J:J)=1,0,MATCH(I2,$A:$A,0))+1,MATCH($H2,$B$1:$F$1,0)):INDEX($B:$F,ROWS(J:J),MATCH($H2,$B$1:$F$1,0)),0)+IF(COLUMNS($J:J)=1,0,MATCH(I2,$A:$A,0))))))

　次に、J2セルをコピーして、J3～J16の範囲に貼り付けて下さい。
　次に、J2～J16のセル範囲をまとめてコピーして、J列よりも右にある列に貼り付けて下さい。
　すると、下の添付画像にある様に、回答者の名前が抽出されて表示されます。

回答No.2

tom04

こんにちは！
一つのセルに表示させる場合、関数では厄介ですので手っ取り早くVBAでやってみました。

↓の画像で左側がSheet1で右側のSheet2に表示させるようにしています。
尚、Sheet2のA列・1行目の項目は入力済みだとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, n As Long, endRow As Long
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
endRow = wS2.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS2.Cells(2, "B"), wS2.Cells(endRow, "D")).ClearContents
Range(wS2.Cells(2, "B"), wS2.Cells(6, "D")).ClearContents
For i = 2 To wS1.Cells(Rows.Count, "A").End(xlUp).Row
For j = 2 To wS1.Cells(1, Columns.Count).End(xlToLeft).Column
If wS1.Cells(i, j) <> "" Then
k = WorksheetFunction.Match(wS1.Cells(i, j), wS2.Rows(1), False)
n = WorksheetFunction.Match(wS1.Cells(1, j), wS2.Columns(1), False)
If wS2.Cells(n, k) = "" Then
wS2.Cells(n, k) = wS1.Cells(i, "A")
Else
wS2.Cells(n, k) = wS2.Cells(n, k) & "," & wS1.Cells(i, "A")
End If
End If
Next j
Next i
wS2.Columns.AutoFit
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m（_ _）m

お礼

tom04さま

早速のご回答いただきありがとうございます。

ＶＢＡというのを初めて知りました。

教えていただいた通りに進めてみて、ＶＢＡ画面を表示し、Alt+F8でマクロの画面まで
出すことはできたのですが・・・

その先の手順がちょっとわからず、進められませんでした。
マクロの画面が出ますが、その画面はすべて空白で「実行」ができません。

また、書いてくださったコードはどちらに入力するのでしょう。

せっかく教えていただいたのに理解できず申し訳ありません。
なにぶん知識が薄いもので、再度ご説明いただけたら助かります。

回答No.1

noname#243276

補足お願いします。
　エクセルのバージョン。

補足

説明が不十分で申し訳ありません。

ＥＸＣＥＬ　2010　です。

よろしくお願いします。