アンケート集計 COUNTIFS関数 2

質問

先ほどの質問は説明が抽象的でしたので、図を添付します。

お世話になります。
アンケートの集計をしております。
年齢層および性別が記入されている場合は、下記の関数で集計しています。
＝COUNTIFS(年齢層,40代,性別,男性,A項目,”<>”)
年齢層または性別が無記入で、ある項目には答えてくれている場合の集計が出来ません。
年齢層または性別が無記入でも、答えてくれている人の意見も「その他」の人として集計したいと思います。
「年齢層または性別が無記入」をどう処理すればよいのでしょうか。
恥かしながら、以下ではだめでした。
＝COUNTIFS(OR(年齢層<>”?*”,性別<>”?*”),A項目,”<>”)
よろしくお願いいたします。

回答No.2

bunjii · ベストアンサー

>「年齢層または性別が無記入」をどう処理すればよいのでしょうか。
COUNTIS関数の中でOR関数を使うことができませんのでCOUNTIFS関数+COUNTIFS関数のような方法で条件に合うカウント数を加算してください。
尚、年齢層と性別が双方共空欄のカウントはどうしますか？
貼付画像はExcel 2013で検証した結果です。
次の数式は年齢層と性別の双方がブランクもカウントできるようにしました。
G24=COUNTIFS(G$2:G$14,1,$B$2:$B$14,"",$C$2:$C$14,"<>")+COUNTIFS(G$2:G$14,1,$C$2:$C$14,"",$B$2:$B$14,"<>")+COUNTIFS(G$2:G$14,1,$B$2:$B$14,"",$C$2:$C$14,"")
G24セルを右へ必要数コピーしてください。
但し、行番号は質問で提示の行数となっていますので必要に応じて変更してください。

お礼

bunjii さん、ありがとうございます。
<COUNTIS関数の中でOR関数を使うことができませんのでCOUNTIFS関数<+COUNTIFS関数のような方法で条件に合うカウント数を加算してください。
なるほどそうだったんですね。
おかげさまで解決できました。
本当にありがとうございました。
またよろしくお願いいたします。

回答No.1

kagakusuki

集計表をアンケートの記入欄の下に設ける様になっているため、処理が面倒になっていますが、御質問内容がそうなっているので致し方ありません。
　それと、質問者様の添付画像では文字が潰れかけているために、集計表が何行目から始まっているのか良く解りませんでしたので、取り敢えず仮の話として、集計表の中で40代以下と入力されているセルがD62セルであるものとします。
　それで、

>年齢層または性別が無記入でも、答えてくれている人の意見も「その他」の人として集計したいと思います。

という事であれば、下の添付画像の様に集計表の「年齢層」欄に「40代以下」、「50代」、「60代」、「70代以上」の他にも「その他」という項目を設けた上で、「性別」欄も各年代ごとに「男性」と「女性」だけではなく「その他」という項目を設ける事で、各年代の集計結果を2行ではなく3行を使って集計する様にして下さい。
　又、集計表にも61行目を使用して、「A」、「B」、「C」・・・等の項目名を入力する様にして下さい。
　後、前提条件として、A列にはアンケートの結果の回答番号を数値で入力する様にしておき、尚且つ、A2よりも下(A14:A61及びA62以下の範囲も含む)には、回答番号以外の数値や日時データを入力しない様にして下さい。

　その上で、まずG62セルに次の様な関数を入力して下さい。

=IF(OR(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="",$E62="",G$61=""),"",COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),">-9e307"))

或いは

=IF(OR(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="",$E62="",G$61=""),"",COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),"<9e307"))

　そして、G62セルをコピーして、G2～S76の範囲に貼り付けて下さい。

　尚、上記の関数はアンケートの回答内容が数値のみとなっている場合に対応するものであり、回答内容が文字列データである場合には集計する事が出来ません。
　もし、回答内容が文字列データ"のみ"となっている場合には、次の様な関数となります。

=IF(OR(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="",$E62="",G$61=""),"",COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),"*?"))

　こちらは逆に回答内容が数値のものを集計する事は出来ませんので、数値と文字が混在している場合には、次の様な関数となります。

=IF(OR(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="",$E62="",G$61=""),"",COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),">-9e307")+COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),"*?"))

或いは

=IF(OR(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="",$E62="",G$61=""),"",COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),"<9e307")+COUNTIFS($B$1:INDEX($B:$B,MATCH(9E+99,$A:$A)),IF($E62="その他","",$E62),$C$1:INDEX($C:$C,MATCH(9E+99,$A:$A)),IF(INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)="その他","",INDEX($D$62:$D62,FLOOR(ROWS($D$62:$D62)-1,3)+1)),INDEX($G$1:$S$1,COLUMNS($G$61:G$61)):INDEX($G:$S,MATCH(9E+99,$A:$A),COLUMNS($G$61:G$61)),"*?"))

お礼

kagakusuki さん、ありがとうござます。
大変お世話になりました。
ご丁寧なRe.本当にありがとうございます。
今回は、COUNTIFSを使うことで解決できました。
またよろしくお願いいたします。