カンマ区切り複数回答セルの値の、条件付き集計

質問

文章で説明するのがうまくできなさそうなので、画像添付いたします。

（画像参照）上の表が元データ。下の表が、集計したい表です。

最終的に、業種が1の人で回答に1が含まれているもの、業種が1の人で回答に2が含まれているもの・・・といったかたちで、集計しようとしています。

あちこちネットを放浪して、とりあえず業種は無視して、複数回答セルの集計は、全データの回答から=COUNTIF($B$3:$B$11,"*1*")のようなかたちで集計できるらしいというのを見て、実験したのですが、どうも思う回答と違う結果が出ます。

サンプルでは、元データには”1”の入ったデータは5件しかないのですが、集計結果は6になっています。ひょっとして、”10”も”1”に数えているのだろうか？と思ったのですが、”10”を入れると集計結果は7になるはずなので、これでもなさそうです。

また、業種も勘案したものを関数でどう書けばいいのかわかりません。

関数自体をあまり使ったことがないので、お恥ずかしい質問なのですが、やり方をご存じの方がいらしたら、是非教えて頂けるとありがたいです。

回答No.2

DJ-Potato · ベストアンサー

「10」とだけ入力されているセルが数値として認識されている可能性があります。
この場合、文字列での検索を掛けているために、数値のセルは無視されて、6と返されているのではないでしょうか。
カンマを入れず、１項目だけしか回答がない時は「'10」というようにアポストロフィ+数字で入力すると解決するかもしれません。

ただし、いずれにしても文字列で検索する場合は、「1」と「10」の区別が難しいので、１ケタの数字は「01」と入力するとか、10以降を「a」「b」「c」と読み替えるとか、何かしら方法が必要かもしれません。

ちなみに、COUNTIF関数のお仲間で、複数条件を設定できるCOUNIFS関数というものもあります。
たぶん、ご希望の結果を出すのに適したものではないかと存じます。

お礼

ご回答ありがとうございます。

あとで、「10」をなんとなく入れ直してみたら、サンプルでのテストはうまくいきました。1件合わなかったのは、皆さんのおっしゃる通り「10」のせいだと思います。
セル自体のプロパティは、「文字列」に一括変更していたのですが・・・うーん、それだけではダメなんですかね。

COUNTIFについてはいろいろ調べながらやっているのですが、そもそも関数をあまり使ったことがないので、苦戦中です。

もうちょっとがんばってみます。ありがとうございました。

補足

ここの補足に書くのもアレですが・・・。

結局、それぞれのセルで引数をあれこれ書いていくのも荷が重かったので、回答欄の1,5,8,10を、01,05,08,10に打ち直して、それでやってみることにしました。
テスト用のデータではうまくいったっぽいので、それでやってみます。多分うまくいくんじゃないかなと思っています。

皆さまありがとうございました。皆さんにベストアンサーを付けたいのですが、そういうわけにもいかないようなので、01,05を教えてくださったDJ-potatoさんに差し上げたいと思います。

回答No.5

MackyNo1

>サンプルでは、元データには”1”の入ったデータは5件しかないのですが、集計結果は6になっています。ひょっとして、”10”も”1”に数えているのだろうか？と思ったのですが、”10”を入れると集計結果は7になるはずなので、これでもなさそうです。

B5セルの10が数字として入力され、文字列として認識されていないためです。
解決方法は、B列のデータ範囲を選択して、「データ」「区切り位置」で「次へ」「次へ」で最後に「文字列」にチェックを入れれば、すべてのセルが文字列データになります。

>また、業種も勘案したものを関数でどう書けばいいのかわかりません。

2007以降のバージョンならCOUNTIFS関数を利用します。

ひとまず、B14セルに以下の式を入力して右方向および下方向にオートフィルしてください。

これでそれぞれの業種で１～１０を含むデータが表示されます。
しかし、１については１０を含むセルのカウントしてしましますので、回答の１をカウントするB14セルの数式を以下のように変更して、下方向にオートフィルしてください。

=COUNTIFS($A$3:$A$11,$A14,$B$3:$B$11,"*"&B$13&"*")-COUNTIFS($A$3:$A$11,$A14,$B$3:$B$11,"*10")+COUNTIFS($A$3:$A$11,$A14,$B$3:$B$11,"1*10")

修正部分は、１を含むデータから、末尾に10が含まれているセルの数を引いて、最後に１と１０を含むセル（１０があっても引いてはいけないセル）を加えています。

お礼

ご回答ありがとうございます。

関数が全く初心者なので、調べながらで大苦戦中ですが、”1”と”10”を識別集計するためには、他の方の回答にもありますが、なにやら引き算をしなきゃいけない・・・っぽい感じと見ましたが、実際のコードは”1”から”15”まであるので、11から15についても、何か特別なことをしなきゃいけないということかもしれないですね。
うううん・・・。

なお、文字列として認識させるための「解決方法は、B列のデータ範囲を選択して・・・」という方法は、全く知りませんでした。セルの書式設定でいいものだとばかり・・・。ありがとうございました。

回答No.4

mu2011

単に全角・半角が混ざっているだけだと思います。
もちろん*１*では１０も対象となるので、これを逆手にとって、１桁数値は全角、２桁数値は半角とすれば区別化できますが如何でしょうか。

仮に業種行が13行目とすると=COUNTIF($B$3:$B$11,"*"&B$13&"*")としておけば、検索文字列ごとに数式を変更する必要がありません。

お礼

ご回答ありがとうございます。

「10」の入力のところは、やはりおかしかったです。入れ直したら、サンプルデータではokでした。後半部分については、わたしの知識が追いついていなくて恥ずかしいのですが、ちょっと意味がわからず・・・勉強します。ごめんなさい。

回答No.3

CC_T

記入の数式では1と10の区別はしないはずですが、１と1の違い、つまり全角半角混じりになっているのでは？
ASC(半角変換)の関数を使用するなど、全て半角にして比較してみてください。

関数で処理するならば、上の表の右側にそれぞれの回答を分けてカウントする作業用列を入れておくのが確認も楽でしょう。
すでにご存じのCOUNTIFを使えば各数字分を取り出せますよね。
1と10や11、2と12や20を区別するには、余分もカウントして差し引きすれば良いです。

下の表ではそれを業種ごとに集計すれば良いわけです。
SUMIF関数を使いましょう。

添付の図では
D3セルに
　=COUNTIF($B3,"*1*")-COUNTIF($B3,"*10*")
　（=COUNTIF($B3,"*1*")-$M3でもいいですね）
E3セルに
　=COUNTIF($B3,"*2*")　'←以下、作業用セルで*3*、*4*…と変更していってください。
D15セルには
　=SUMIF($A$3:$A$11,$C15,D$3:D$11)
が入っています。

※ちなみに添付の図では、B11のセルのみ10を全角にしてあります。
やはり10の回答としてカウントされてないですね。

お礼

ご回答ありがとうございます。

やっぱり引き算しないとダメなんですねorz。
勉強になりました。
文字列と数値の違いを考えてやらないといけないんですね。
ワイルドカードの意味もわからなかったりだったので、かなり脳みそが痛くなりましたが、やっとわかったような気がします。

回答No.1

tom04

こんにちは！
一例です。
SUMPRODUCT関数を使っていますので、データ量が多すぎる場合は
PCに負担をかけるので、あまりオススメしません。
とりあえず100行目まで対応できる数式です。

↓の画像のような配置で、Sheet2に表示するとします。

Sheet2のB2セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(ISNUMBER(FIND(B$1&",",Sheet1!$B$1:$B$100&","))))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
（エラー処理はしていません）

参考になれば良いのですが・・・m（＿ ＿）m

お礼

回答ありがとうございます。
サンプルデータでやってみたらうまくいったので、本番データでやってみました。
一部、手計算と合わせてみたところ、合わない部分があり・・・。

わたしの関数の書き方が間違っているのかもしれませんが、その間違いが見つけられません。

『元データ（の一部）』
業種(I) 回答(J)
1　　 3
1　　 4,9,11
1　　 3,12
1　　 10
1　　 5,7
1　　 5,12,15
1　　 2,7
1　　 3,4
1　　 5
1　　 5,7
1　　 5
1　　 2,4
1　　 10
6　　 12
6　　 1,4
6　　 12
6　　 5,8
6　　 6

新しいシートには、以下を入れています。
=SUMPRODUCT((集計元!$I$1:$I$19=$A2)*(ISNUMBER(FIND(B$1&",",集計元!$J$1:$J$19&","))))

これで新シートを作成すると、業種が１で回答に２を選んだ人が4件と出るのですが、実際には２件しかないはずなので、なにかわたしが間違っているのかもです・・・。他の場所も微妙に手計算の数字と合わないところがあるので・・・。（合っているところもあります）

もうすこしいじってみます。

補足

すみません。数字が合わない原因自体は、多分、回答に2と答えている件数の中に、12が含まれているからではないかなぁと思います。1と答えている中に11が含まれている・・・と思って計算すると、合います。補足でした。