文章で説明するのがうまくできなさそうなので、画像添付いたします。
(画像参照)上の表が元データ。下の表が、集計したい表です。
最終的に、業種が1の人で回答に1が含まれているもの、業種が1の人で回答に2が含まれているもの・・・といったかたちで、集計しようとしています。
あちこちネットを放浪して、とりあえず業種は無視して、複数回答セルの集計は、全データの回答から=COUNTIF($B$3:$B$11,"*1*")のようなかたちで集計できるらしいというのを見て、実験したのですが、どうも思う回答と違う結果が出ます。
サンプルでは、元データには”1”の入ったデータは5件しかないのですが、集計結果は6になっています。ひょっとして、”10”も”1”に数えているのだろうか?と思ったのですが、”10”を入れると集計結果は7になるはずなので、これでもなさそうです。
また、業種も勘案したものを関数でどう書けばいいのかわかりません。
関数自体をあまり使ったことがないので、お恥ずかしい質問なのですが、やり方をご存じの方がいらしたら、是非教えて頂けるとありがたいです。
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.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.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で質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- 統計学 前回イベントと今回イベントのデータ集計について 質問させてください。 イベント参加者の内アンケートを 3 2022/09/30 22:23
- Excel(エクセル) Excelのマクロを教えていただけないでしょうか? 1 2023/07/06 19:56
- Excel(エクセル) EXCELピボットテーブル関数について 2 2023/04/10 20:35
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- Excel(エクセル) EXCEL値貼り付け(ある条件のもと自動化) 5 2023/06/06 12:21
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
ウィンドウィズ メモ帳で日付だ...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
Microsoft Formsの「個人情報や...
-
microsoft office
-
MicrosoftOfficeについて質問で...
-
マイクロソフト 一時使用コード...
-
outlookで宛先が異なるメールを...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
自分の専門分野の仕事。初見で...
-
Microsoft Officeを2台目のPCに...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報