いくつの質問に答えたかエクセルで集計したい

質問

私はもう既に1500近い回答をしていますが、ひとつの質問に複数回答える（訂正、追加などで）ため、いくつの質問に回答したか分かりません。

そこで、

・「答える履歴」の表を１画面分ずつコピー
・エクセルシートに貼り付け
・次の画面をコピー
・エクセルシートの直後の行に貼り付け

という形でエクセルにもって行って、一枚のシートに答える履歴がなっているとします。

このとき、エクセルでどういう処理をすると、質問数が分かるでしょう。

できれば、質問数だけでなく、

「通し番号」「質問タイトル」「日付」「質問者」「回答数」「良回答ポイント」「この質問に何度答えたか」

というようなシートを作りたいのですが。

こんなこと、できるでしょうか？

回答No.5

noname#9284 · ベストアンサー

たびたびすみません。

> Ａ列：シリアル番号（最古を１として、最新にむかい１つずつ増やした数値、あとから入力）

webと逆の昇順になっていたのですね。
ではI1は
=SUMPRODUCT(1/COUNTIF(C1:C1500,C1:C1500))-2
にします。最終行が1500行目としてありますので、これ以下でしたらその行番号を1500の代わりに入れてください。
最終行のすぐ下にも見出し行を入れてください。

I2を
=COUNTIF($C$2:C2,C2)&"/"&COUNTIF(C:C,C2)
にしてみてください。
データの追加の仕方は最終行とその下の見出しの間に挿入する形で追加してください。
データを追加するたびにこの数式をWクリックで下までフィルしてください。

一旦これで様子を見られ、補足をお願いいたします。

お礼

何度も、ありがとうございました。
とってもクリアというか、きれいにできました。
特に、常にいくつの質問に答えているかがわかるのが感激です。
たくさん時間を使っていただき、ありがとうございました。

回答No.7

noname#10331

#5さんの訂正しておきます。
（きっとタイプミスだと思います。）
I1は
=SUMPRODUCT(1/COUNTIF(C1:C1500,C1:C1500))-2
ではなく…
=SUMPRODUCT(1/COUNTIF(C1:C1500,C1:C1500))-１
としたかったと思います。
お邪魔致しましたm(__)m

お礼

ありがとうございました。
このような形で助けていただき、感謝しています。

回答No.6

k0000

同じ表の中に結果を出さないといけないのですか？
別シートで集計結果をみられればよいのであれば
回答の履歴をデータとして使い
別シートにピボットテーブルをつかった集計結果を表示させるのがいいのでは？

質問タイトル／日付／質問者／ポイント　というエクセルシートを元に

１．データ→ピボットテーブルレポート　を開く
２．エクセルのリスト／データベースをチェックして次へ
３．範囲（自動選択されるのでそのまま）　次へ
４．行フィールドに「質問者」「タイトル」
　　データフィールドに「タイトル（データの個数）」「ポイント（合計）」をいれて　次へ
５．新規ワークシートをチェックし完了
６．出来上がった表の「質問者」をダブルクリックして「集計」をなしにしてｏｋ
７．「データ」を右上にドラッグ

あとは参照範囲を下にひろげておけば、もとデータが増えても
「データ」→「データの更新」で対応できますよ

お礼

回答していただき、ありがとうございました。
このやりかたはまだ試していませんが、参考にいたします。
時間をつかっていただき、ありがとうございました。

回答No.4

noname#9284

I2の参照を絶対参照にするのを忘れました。
=COUNTIF(C2:$C$1500,C2)&"/"&COUNTIF(C:C,C2)
にしてみてください。

回答No.3

noname#9284

C2から下にはタイトルが隙間無くぎっしり入力されているのが条件です。

I1（アイ１）に「何個の質問に答えたか」の数が出ます。もちろん重複はカウントしていません。
I2から下には「1/2」のように、「２回答えたうちの1番目」のように出ます。

I1
=SUMPRODUCT(1/COUNTIF(C1:C1500,C1:C1500))-1

I2
=COUNTIF(C2:C1500,C2)&"/"&COUNTIF(C:C,C2)

で、どんどん回答が増えて行った場合、１，２行目の間に挿入して行って下さい。
1行挿入した場合元I2だった、現在I3の数式は
=COUNTIF(C3:C1501,C3)&"/"&COUNTIF(C:C,C3)
になりますので、
この数式をI2にもコピーしてやってください。
(I1の数式はそのままにしてください）

昨日ご質問を拝見しててっきり2行ずつのデータになっていると思っていましたので
時間がかかってしまってすみません。2行ずつですともっと複雑になるんです。
どういう手順でExcelに転記していただこう、どこを手入力していただこう
と考えている時間で遅くなってしまいました。
これからご質問される時には最初から具体的なフォーマットを示して下さると
もっと早く回答がつくと思いますよ＾＾

回答No.2

Levia

計算式、スペルミスがありますもんねぇ。
失礼しました。

その表が既に作成されているとして、
I2=COUNTIF($C$2:$C$11,C2)
を下方向にコピーすることで(2)ができます。
（後で重複データを実際に削除するのでしたら、計算結果が異なってきますので、I列をコピーして同じI列に「形式を選択して貼り付け」で「値」を選択して貼り付けておきます）

その後、(1)を実現するために
J=IF(C2="","",IF(COUNTIF(C$2:C2,C2)>1,ROW(),""))
と入力して下方向にコピーします。
コレで重複データの場合のみその２つめ以降に行番号がつきます。
一行目にカーソルがある状態で「データ」→「フィルタ」→「オートフィルタ」でJ列の▼をクリックして「空白のセル」を選択すると完了です。
（重複データを削除するのでしたら、「空白以外のセル」を選択し、現れた行を削除します）

データベースとして考えるなら、（　　）内をやってしまうとトータル回答数の算出が面倒になりますので、やらないほうがいいように思えます。

お礼

大変ありがとうございました。
このやりかたでやってみたところ、1161個の質問に、1507回答えたことが分かりました。
そのうち488の質問に良回答ポイントがつきました。
488÷1161≒0.42、ということで、良回答率42％だぁ…といったような「自己満足」にひたりたかったわけです(^_^;)。
すみません、こんな理由で(^_^;)。
でも、本当にたくさん時間を使っていただいて、ありがとうございました。

回答No.1

Levia

1.選択してコピーした範囲をテキストとして貼り付けます。
2.すべてがA列に貼り付いてしまいますのでそれを補正します（具体的なコードが思い浮かびませんが、マクロの出番でしょう）。
　・質問当たり4行を使用しますので、ROW関数を使用したり、オートフィルタなどで抽出し、カテゴリやご質問者の名前をB列やC列に移動します。
　・ナンバリングと表題、カテゴリと回答日付、質問者のお名前と受付中か回答済みかがブランクをはさんで同じセルに入ってしまいますので、 「区切り位置指定ウィザード」などで分割します。
　・ひとつの質問が一行に収まるように体裁を整えます。
　・不要な列を削除します。
3.重複データのカウント（ひとつの質問に何回答えたか、など）は
=COUTIF($C$3:$C$12,C3)
で算出できると思います。
4.回答した質問の総数はフィルタオプションの「重複するレコードは無視する」で算出できますし、オートフィルタを用いるなら
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,ROW(),""))
を実行すると重複データの2番目以降にのみ行番号が表示されますので、オートフィルタで「空白」だけを表示させて、空白データのみを合計すればOKかと思われます。

お礼

お答えいただきまして、ありがとうございました。
すごく時間を使っていただいたとは思うのですが、せっかくのご回答ではあるのですが、まったく理解できませんでした。すみませんでした。
よろしければ、補足欄で状況を書きましたので、お付き合いいただければ幸いです。

補足

すみません、書いていただいたことが、ぜんぜん分かりません。

とりあえず、次のシートをつくったところまでしました。
Ａ列：シリアル番号（最古を１として、最新にむかい１つずつ増やした数値、あとから入力）
Ｂ列：Ｎｏ
Ｃ列：質問タイトル
Ｄ列：ジャンル
Ｅ列：日時
Ｆ列：質問者
Ｇ列：回答件数
Ｈ列：ポイント（締め切り前も０が入っている）

ＡとＨは私が入力した部分です。

このような状態のシートで

１）Ｃ列の重複しているレコードを１つにして（重複レコードの一本化）
２）Ｃ列がいくつ重複していたか（質問毎にいくつ答えたか）をＩ欄に

できないものでしょうか？