エクセルの区切り数字を個別に集計する方法を教えてください。

質問

エクセルでアンケート集計しているのですが、
カンマ区切りの数字から特定の数字の個数を
カウントする方法をご存じでしたら教えてください。

現在、複数回答の項目を下記の様に入力。
A4のセル　1,2,3,5,10,15,21
A5のセル　1,2,12,22

例えばA6のセルで、「1」と答えた数をカウントしたい。

複数回答の項目が一桁だと、
A6=COUNTIF("A4:A5","*1*")
で集計できたのですが、
今回のように二桁になると
A6=COUNTIF("A4:A5","*1*")=5
となってしまいました。

よろしくお願いします。

回答No.2

mtaka2 · ベストアンサー

=COUNTIF(A4:A5,"1")+COUNTIF(A4:A5,"1,*")+COUNTIF(A4:A5,"*,1,*")+COUNTIF(A4:A5,"*,1")

でどうでしょうか。
「1だけのセル」「1で始まるセル」「中に1があるセル」「1で終わるセル」の足し算です。

お礼

教えて頂いた計算式だけではなく、
その後の質問にも丁寧にお答え頂き、有り難うございました。
「*(アスタリスク）」を使った「COUNTIF」関数、
これからもどんどん活用して行きたいと思います。

補足

お返事ありがとうございます。
教えて頂いた数式で集計できました！

また、この数式の今後の流用・汎用性を鑑みて、
下記２つの質問にもお答え頂けますでしょうか。

１）「2」と答えた人、「3」と答えた人、「4」と答えた人と、いったように、
例えば「50」まで集計しようとした場合は、50個のセルに対して、
教えて頂いた数式をコピー＆ペーストして、さらに、それぞれのセル(数式）に対して、
一つずつ抽出したい数値を入力変更していけば宜しいのでしょうか。
もしさらに簡単な操作方法で、一連の数式データが出来る手法があるようでしたらお願い致します。

２）教えて頂いた数式中の「1,*」「*,1,*」「*,1」ですが、
「1*」「*1*」「*1」と、カンマの有り無しで、どのように変わってくるのでしょうか。
あれからネットで色々調べてみたのですが・・・
こちらも全く解決しないもので。宜しくお願い致します。

回答No.5

mtaka2

> ２）教えて頂いた数式中の「1,*」「*,1,*」「*,1」ですが、
> 「1*」「*1*」「*1」と、カンマの有り無しで、どのように変わってくるのでしょうか。
> あれからネットで色々調べてみたのですが・・・
> こちらも全く解決しないもので。宜しくお願い致します。

アスタリスク(*)は、どんな文字列にもマッチします(文字列無しもあり)。コンマ(,)は、文字通りコンマです。

ですから、「1*」だと「1」で始まるセルを数えるので、「12,3」も数えてしまいます。
一方、「1,*」は、「1,」で始まって、その後に何かの文字列が入っているセルを数えます。ですから、
「1,2,3」は計測対象ですが、「12,3」は対象外。

同様に、「*1」だと「1」で終わるセルを数えるので、「3,21」も数えてしまいます。
「*,1」は、何かの文字列が入っている最後が「,1」で終わるセルを数えます。ですから、
「3,2,1」は計測対象ですが、「3,21」は対象外。

最後に、「*1*」だと、中に「1」が入っているセルを数えるので、「3,12,4」「3,21,4」なんかも数えてしまいますが、
「*,1,*」だと、中に「,1,」が入っているセルを数えるので、
「3,1,4」は計測対象ですが、「3,12,4」「3,21,4」は対象外になります。

> １）「2」と答えた人、「3」と答えた人、「4」と答えた人と、いったように、
> 例えば「50」まで集計しようとした場合は、50個のセルに対して、
> 教えて頂いた数式をコピー＆ペーストして、さらに、それぞれのセル(数式）に対して、
> 一つずつ抽出したい数値を入力変更していけば宜しいのでしょうか。
> もしさらに簡単な操作方法で、一連の数式データが出来る手法があるようでしたらお願い致します。

例えば、計測対象がSheet1、集計結果をSheet2とするなら、
Sheet2のA1～A50に、1～50を入れておいて、
B1に =COUNTIF(Sheet1!A1:A999,A1)+COUNTIF(Sheet1!A1:A999,A1&",*")+COUNTIF(Sheet1!A1:A999,"*,"&A1&",*")+COUNTIF(Sheet1!A1:A999,"*,"&A1)
を入れてから、B1をB2～B50にコピーペーストすれば、
1～50をそれぞれ集計できます。

回答No.4

bin-chan

＃１です。
＃２さんの方法が美しいですね。
＃２さんへの追加質問の件ですが横レスします。

１）データと同じシートの、ちょっと離れた場所で１～５０のデータを作る。（セルC1～C50とします）
・セルC1に値[1]、セルC2に値[2]を入れ、セルC1：C2を選択して右下にある■を下方向にドラッグ。
２）セルＤ１に式[=COUNTIF(A$4:A$5,TEXT(C1))+COUNTIF(A$4:A$5,TEXT(C1)&"*")+COUNTIF(A$4:A$5,"*," & TEXT(C1) &",*")+COUNTIF(A$4:A$5,"*,"& TEXT(C1))]を入れる。
３）セルＤ１の内容（式）をセルＤ５０までコピーする。
・セルD1を選択して右下にある■をダブルクリック。
※$は必須です。絶対参照（コピーしても範囲を示す数値が変化しない）です。
※TEXTは冗長かも。書かなくても動くと思います。

>「1*」「*1*」「*1」と、カンマの有り無しで、どのように変わってくるのでしょうか。
選択肢が1～9までなら、差は生じません。
選択肢が10以上ある場合に、差が生じます。

「1,*」だと「1"のみ"で始まるものすべて（=1のみ）」、ですが
「1*」だと「"1で始まる"ものすべて」であるため、"10,*”や"11,*"も含んでしまいます。
同様に
「*1*」だと"「11でも1でも、途中に1を含むものすべて」であるため、"2,11,3”や"3,21,4"も"1"の回答として含んでしまいます。
「*1」だと「11でも1でも、1で終わるものすべて」であるため、"6
,5,11”や"3,11"も含んでしまいます。

＃３さんがおっしゃるのは、
選択肢が99以下なら2桁（01、02という書き方）に統一ということです。
抽出セルの式を「01*」「*01*」「*01」と記述することになります。

選択肢が100を超えるなら3桁にしてください。
抽出セルの式を「001*」「*001*」「*001」と記述することになります。

お礼

カンマで区切られた数字を分割する方法や、
＃２さん、＃３さんの補足までして頂き、有り難うございました。
１００を超える場合の３桁入力方法までありましたので、
しっかりと理解することが出来ました。

回答No.3

mu2011

一例です。
現在の入力桁を２桁で統一すれば、数式パターンを考慮せず、COUNTIF関数で簡単に集計ですますが駄目でしょうか。
又は、NO1様がご回答されている「セル分割」した方が良いと思います。

回答No.1

bin-chan

列Ｂ以降（右側）が使い放題だったら、
列Ａを区切り記号にカンマを指定して分解してしまうのは？

１）列Ａを選択
２）データ＞区切り位置をクリック
３）カンマなどの・・・、を選択して、次へ
４）「カンマ」にチェック