文章で説明するのがうまくできなさそうなので、画像添付いたします。
(画像参照)上の表が元データ。下の表が、集計したい表です。
最終的に、業種が1の人で回答に1が含まれているもの、業種が1の人で回答に2が含まれているもの・・・といったかたちで、集計しようとしています。
あちこちネットを放浪して、とりあえず業種は無視して、複数回答セルの集計は、全データの回答から=COUNTIF($B$3:$B$11,"*1*")のようなかたちで集計できるらしいというのを見て、実験したのですが、どうも思う回答と違う結果が出ます。
サンプルでは、元データには”1”の入ったデータは5件しかないのですが、集計結果は6になっています。ひょっとして、”10”も”1”に数えているのだろうか?と思ったのですが、”10”を入れると集計結果は7になるはずなので、これでもなさそうです。
また、業種も勘案したものを関数でどう書けばいいのかわかりません。
関数自体をあまり使ったことがないので、お恥ずかしい質問なのですが、やり方をご存じの方がいらしたら、是非教えて頂けるとありがたいです。
No.2ベストアンサー
- 回答日時:
「10」とだけ入力されているセルが数値として認識されている可能性があります。
この場合、文字列での検索を掛けているために、数値のセルは無視されて、6と返されているのではないでしょうか。
カンマを入れず、1項目だけしか回答がない時は「'10」というようにアポストロフィ+数字で入力すると解決するかもしれません。
ただし、いずれにしても文字列で検索する場合は、「1」と「10」の区別が難しいので、1ケタの数字は「01」と入力するとか、10以降を「a」「b」「c」と読み替えるとか、何かしら方法が必要かもしれません。
ちなみに、COUNTIF関数のお仲間で、複数条件を設定できるCOUNIFS関数というものもあります。
たぶん、ご希望の結果を出すのに適したものではないかと存じます。
この回答への補足
ここの補足に書くのもアレですが・・・。
結局、それぞれのセルで引数をあれこれ書いていくのも荷が重かったので、回答欄の1,5,8,10を、01,05,08,10に打ち直して、それでやってみることにしました。
テスト用のデータではうまくいったっぽいので、それでやってみます。多分うまくいくんじゃないかなと思っています。
皆さまありがとうございました。皆さんにベストアンサーを付けたいのですが、そういうわけにもいかないようなので、01,05を教えてくださったDJ-potatoさんに差し上げたいと思います。
ご回答ありがとうございます。
あとで、「10」をなんとなく入れ直してみたら、サンプルでのテストはうまくいきました。1件合わなかったのは、皆さんのおっしゃる通り「10」のせいだと思います。
セル自体のプロパティは、「文字列」に一括変更していたのですが・・・うーん、それだけではダメなんですかね。
COUNTIFについてはいろいろ調べながらやっているのですが、そもそも関数をあまり使ったことがないので、苦戦中です。
もうちょっとがんばってみます。ありがとうございました。
No.5
- 回答日時:
>サンプルでは、元データには”1”の入ったデータは5件しかないのですが、集計結果は6になっています。
ひょっとして、”10”も”1”に数えているのだろうか?と思ったのですが、”10”を入れると集計結果は7になるはずなので、これでもなさそうです。B5セルの10が数字として入力され、文字列として認識されていないためです。
解決方法は、B列のデータ範囲を選択して、「データ」「区切り位置」で「次へ」「次へ」で最後に「文字列」にチェックを入れれば、すべてのセルが文字列データになります。
>また、業種も勘案したものを関数でどう書けばいいのかわかりません。
2007以降のバージョンならCOUNTIFS関数を利用します。
ひとまず、B14セルに以下の式を入力して右方向および下方向にオートフィルしてください。
これでそれぞれの業種で1~10を含むデータが表示されます。
しかし、1については10を含むセルのカウントしてしましますので、回答の1をカウントする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")
修正部分は、1を含むデータから、末尾に10が含まれているセルの数を引いて、最後に1と10を含むセル(10があっても引いてはいけないセル)を加えています。
ご回答ありがとうございます。
関数が全く初心者なので、調べながらで大苦戦中ですが、”1”と”10”を識別集計するためには、他の方の回答にもありますが、なにやら引き算をしなきゃいけない・・・っぽい感じと見ましたが、実際のコードは”1”から”15”まであるので、11から15についても、何か特別なことをしなきゃいけないということかもしれないですね。
うううん・・・。
なお、文字列として認識させるための「解決方法は、B列のデータ範囲を選択して・・・」という方法は、全く知りませんでした。セルの書式設定でいいものだとばかり・・・。ありがとうございました。
No.4
- 回答日時:
単に全角・半角が混ざっているだけだと思います。
もちろん*1*では10も対象となるので、これを逆手にとって、1桁数値は全角、2桁数値は半角とすれば区別化できますが如何でしょうか。
仮に業種行が13行目とすると=COUNTIF($B$3:$B$11,"*"&B$13&"*")としておけば、検索文字列ごとに数式を変更する必要がありません。
ご回答ありがとうございます。
「10」の入力のところは、やはりおかしかったです。入れ直したら、サンプルデータではokでした。後半部分については、わたしの知識が追いついていなくて恥ずかしいのですが、ちょっと意味がわからず・・・勉強します。ごめんなさい。
No.3
- 回答日時:
記入の数式では1と10の区別はしないはずですが、1と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
- 回答日時:
こんにちは!
一例です。
SUMPRODUCT関数を使っていますので、データ量が多すぎる場合は
PCに負担をかけるので、あまりオススメしません。
とりあえず100行目まで対応できる数式です。
↓の画像のような配置で、Sheet2に表示するとします。
Sheet2のB2セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(ISNUMBER(FIND(B$1&",",Sheet1!$B$1:$B$100&","))))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
(エラー処理はしていません)
参考になれば良いのですが・・・m(_ _)m
この回答への補足
すみません。数字が合わない原因自体は、多分、回答に2と答えている件数の中に、12が含まれているからではないかなぁと思います。1と答えている中に11が含まれている・・・と思って計算すると、合います。補足でした。
補足日時:2011/10/05 14:51回答ありがとうございます。
サンプルデータでやってみたらうまくいったので、本番データでやってみました。
一部、手計算と合わせてみたところ、合わない部分があり・・・。
わたしの関数の書き方が間違っているのかもしれませんが、その間違いが見つけられません。
『元データ(の一部)』
業種(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&","))))
これで新シートを作成すると、業種が1で回答に2を選んだ人が4件と出るのですが、実際には2件しかないはずなので、なにかわたしが間違っているのかもです・・・。他の場所も微妙に手計算の数字と合わないところがあるので・・・。(合っているところもあります)
もうすこしいじってみます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
カンマで区切った数値をCOUNTIFで数えたい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Outlook で宛先が複数の場合の人数
-
マクロ自動コピペ 貼り付ける場...
-
英数字のみ全角から半角に変換
-
Excel テーブル内の空白行の削除
-
会社PCのメールが更新されない
-
Microsoft365で写真をアルバム...
-
Microsoft Officeを2台目のPCに...
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft Formsの「個人情報や...
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
エクセルでXLOOKUP関数...
-
Outlook 電源OFFの受診の仕方
-
Office 2021 Professional Plus...
-
Officeを開くたびの「再起動メ...
-
会社のOutlookにてメールを予約...
-
何このステータスバー
-
エクセルのシフト表を簡単にGoo...
-
http://oshiete.goo.ne.jp/qa/1...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
英数字のみ全角から半角に変換
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
大学のレポート A4で1枚レポー...
-
Officeを開くたびの「再起動メ...
-
Microsoft Formsの「個人情報や...
-
one drive使えるpcを買う
-
マクロ自動コピペ 貼り付ける場...
-
PDFのハイパーリンクを自動的に変更し...
-
あらかじめ用意したテンプレー...
-
別シートの年間行事表をカレン...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
office365って抵抗感ないですか?
-
ステータスバーの合計に表示さ...
-
【スプレドシート】IF関数の複...
-
Teamsで課題を提出します。 画...
-
Outlook で宛先が複数の場合の人数
-
Microsoft Officeを2台目のPCに...
おすすめ情報