Excelで数を数える、どのようにすれば効率的でしょうか

Question

よろしくお願いいたします。
皆さんのお知恵を貸してください。

添付の画像のように、1つのセルの中に複数の数字が入っている場合、
1は何個
2は何個

というのはどのように数えるのが効率的でしょうか。

1つのセルに1つの数ならcountifで簡単に数えられますが、、、、

よろしくお願いいたします。

goomania · Accepted Answer

No.3です。

>すみません、出来たのですが、式の意味を教えていただけると大変助かります。

というご要望なので、簡単に説明します。

前半のLEN(TEXTJOIN(～&",")･･･①というマイナス記号の直前までの部分は各セルの内容を","で連結し、さらに最後に","を付けたものの文字数･･･(a)を数えています。

後半のLEN(SUBSTITUTE(TEXTJOIN(～,E2&",","")))･･･②というスラッシュ記号の直前までの部分は①の数式の文字列をSUBSTITUTE関数を使って数える対象の数字と","の組合せを空白(長さ0の文字列)に置換しています。そうすることで、数える対象の数値と","の組合せがあれば、その分、文字列が短くなります。その短くなった後で文字数･･･(b)を数えています。

ここで(a)と(b)差は「数える対象の数値と","の組合せ」が幾つあるのかによって変わります。
組合せが1つなら「数える対象の数値と","の組合せ」の文字数分短くなるはずです。

従って
=((a)-(b))/「数える対象の数値と","の組合せの文字数」
を計算すれば「数える対象の数値と","の組合せ」が幾つ含まれるのか、つまり数える対象の数値の個数が判るということになります。

ただし、既にNo.10で修正数式をお示ししておりますが、No.3の数式はカウント対象のセル内に2桁以上の数値が混在する場合、正しい結果になりませんので、ご注意ください。

また、TEXTSPLIT関数が使用可能なら、F2セルに

=SUM((TEXTSPLIT(TEXTJOIN(" ",,TRIM(SUBSTITUTE($C$2:$C$7,","," ")))," ")*1=E2)*1)

というもっと短い数式でカウントできます。

fujillin · Answer

こんばんは

すでに、いくつもの回答がありますが、ユーザ定義関数での例をご参考までに。

使い方は、通常の関数と同様にセルに
　=countLetter( 検索文字 , 対象セル範囲 )
を入力するだけです。

・「検索文字」はご質問文の1～の検索したい値に相当します。
・「対象セル範囲」対象とするセル範囲。
例えば、Ａ1:Ａ10にカンマ区切りで文字がある場合に1の個数を数えたければ
　=couontLetter(1, A1:A10)
と入力する要領です。

1、2、3・・・と順に数えたければ
　=couontLetter(Row(A1), A$1:A$10)
などを入力して下方にフィルコピーすれば、順に1、2、3の個数を数えられます。
ひとつのセル内に複数同じ数字がある場合（例：「1,11,1,2」）、「1」の数は2とカウントします。
ただし、スペースを無視しませんので、 「1,11,　1,2」 などとなっている場合には「1」の数は1になります。

検索値は数字に限定してありませんので、文字を同様にカウントすることもできます。

※ 事前の準備として、VBE（＝マクロのエディター）の画面で、標準モジュールに以下の関数をコピペしておけば、関数 countLetter() が使用可能になります。

Function countLetter(ByRef s As String, ByRef rg As Range, _
Optional ByVal d As String = ",") As Long
Dim c As Range, ws, w
countLetter = 0
For Each c In rg
For Each w In Split(c.Text, d)
If w = s Then countLetter = countLetter + 1
Next w
Next c
End Function

goomania · Answer

No.9です。連投失礼します。

前回回答で配列数式を用いた数式を投稿しましたが、配列数式でなくても対応可能なことに気づきました。F2セルに以下の数式を記述する方法です。

=(LEN(" "&SUBSTITUTE(TRIM(SUBSTITUTE(TEXTJOIN(",",,$C$2:$C$7),","," "))," ","  ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(TRIM(SUBSTITUTE(TEXTJOIN(",",,$C$2:$C$7),","," "))," ","  ")&" "," "&E2&" ","")))/LEN(" "&E2&" ")

しかし、例によって正しい数式を投稿できないので、以下のURLで、正しい数式をご確認ください。

https://ideone.com/5iFZgU

goomania · Answer

No.3です。
enunokokoroさん、ご指摘ありがとうございます。
前回回答は、確かに2桁以上の数値が混在すると正しい結果を返さない数式でした。大変失礼いたしました。

TEXTSPLIT関数が使えないバージョンでも利用可能なものをと考えたのですが、考察が足りなかったようです。
お詫びして、F2に記述する数式を以下のように修正いたします。さらにこの数式はスピル機能のないバージョンでは、数式バーに入力後、ENTERではなく、「CTRL+SHIFT+ENTER」で確定して配列数式とする必要があります。
(※上記手順で数式全体が波括弧で括られた状態になるばずです)
残念ながら、結果としてとても長い数式になってしまいました。

=(LEN(" "&SUBSTITUTE(TRIM(TEXTJOIN(" ",,SUBSTITUTE($C$2:$C$7,","," ")))," ","  ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(TRIM(TEXTJOIN(" ",,SUBSTITUTE($C$2:$C$7,","," ")))," ","  ")&" "," "&E2&" ","")))/LEN(" "&E2&" ")

なお、このサイトの仕様として「半角スペースの繰返しは繰返し無しに修正される」ようです。
従って上記数式は当方投稿が正しく反映されません。仕方ないので以下のURLにアップしました。コピーしてご確認ください。

https://ideone.com/DnZWrj

sactaro04230601 · Answer

たびたびすみません。
No.7の補足です。

参照するセルを変更したい場合は
buf = Cells(i, 1)
をいじってください。
Cells(i+1,1)
とすればA2から始まります。
Cells(i,２)
とすればB1から始まります。
空白行があると止まりますが、数字以外が入力されたセルがあるとエラーになります。

結果の書き出し先を変更したい場合は
Cells(i, 2) = arr(i)
Cells(i, 3) = cnt(arr(i))
をいじってください。
動かし方は参照と同じです。

sactaro04230601 · Answer

NO.1です。

すいません。
そもそも質問文を読み違えていました。
1は何個、2は何個の数字を勝手にセルだと思い込みました。
考えてみれば当たり前ですね・・・。

というわけでお詫びもかねてセルにある数字を数えるコードを書いてきました。
VBEに突っ込んで動かしてみてください。
A列に突っ込んだらB列に存在する数字を書き出し、C列にその個数を書き出します。
2桁以上の数字があっても、数字の順番がばらばらでも大丈夫です。

Sub Sample()

Dim buf As String
Dim cnt() As Integer
Dim arr() As Integer
Dim i As Long
Dim j As Long
Dim k As Long
Dim buf1 As Long
Dim buf_max As Long

i = 1
k = 1
Do

buf = Cells(i, 1)
  If buf = "" Then Exit Do

j = 1
  Do

If Mid(buf, j, 1) = "" Then Exit Do

If InStr(j, buf, ",") = 0 Then
      buf1 = Right(buf, Len(buf) - j + 1)
      If buf1 > buf_max Then
        ReDim Preserve cnt(buf1) As Integer
        buf_max = buf1
      End If
      If cnt(buf1) = 0 Then
        ReDim Preserve arr(k) As Integer
        arr(k) = buf1
        k = k + 1
      End If
    
      cnt(buf1) = cnt(buf1) + 1

Exit Do

Else
      buf1 = Mid(buf, j, InStr(j, buf, ",") - j)
      j = InStr(j, buf, ",") + 1

End If

If buf1 > buf_max Then
      ReDim Preserve cnt(buf1) As Integer
      buf_max = buf1
    End If
    If cnt(buf1) = 0 Then
      ReDim Preserve arr(k) As Integer
      arr(k) = buf1
      k = k + 1
    End If
    
    cnt(buf1) = cnt(buf1) + 1

Loop

i = i + 1

Loop

For i = 1 To k - 1
  For j = k - 1 To i Step -1
    If arr(i) > arr(j) Then
      swap = arr(i)
      arr(i) = arr(j)
      arr(j) = swap
    End If
  Next j
Next i

For i = 1 To k - 1

Cells(i, 2) = arr(i)
  Cells(i, 3) = cnt(arr(i))

Next i

End Sub

enunokokoro · Answer

COUNTIF関数でワイルドカードを使えば、数字が一桁なら
対応できますよ。
　=COUNTIF($C$2:$C$11,"*"&E2&"*")
私の添付画像で確認できるかと思いますが、数えたい数字の
列がE列として、C列が文字列としてあれば対応できます。
（標準だと一つだけの数字のセルがカウントされないことも）

No.3の回答者さんの数式も二桁以上の場合、特定の数値では
正しく判定されないことがあるようです。

banzaiA · Answer

>1つのセルに1つの数ならcountifで簡単に数えられますが、、、、
それなら、1つのセルに1つの数にしましょう。
Ｄ３のセルに
=TEXTSPLIT(C3,",")
と入力しましょう。すると
D3 のセルから順に、1 2 3　とF3まで数値が入ります。

365ですが。

goomania · Answer

No.3です。
前回回答の補足です。数式中の「$C$2:$C$5」の部分はご質問者が掲出された画像にあわせたものです。実際のデータ範囲により調整してください。
同時に、E2以下に列挙する数値についても実際のセルの内容に応じて、下方向へコピーするセル数を調整してください。

goomania · Answer

ご質問者は

>1つのセルに1つの数ならcountifで簡単に数えられますが、、、、

と仰っているので、セルごとに数えるのではなく、掲出された画像で言えばC列全体の中に、1は何個、2は何個、3は何個･･･と数えたいということだと理解しました。

添付画像をご覧ください。
数えるべき数値をE列に列挙するためE2セルに

=ROW(A1)

という数式を記述して下方向へコピーしています。その上で、F2セルに

=(LEN(TEXTJOIN(",",,$C$2:$C$5)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",,$C$2:$C$5)&",",E2&",","")))/LEN(E2&",")

という数式を入れて下方向へコピーしています。

これで、E列に表示された数値のそれぞれの個数がF列に表示されることになります。

Excelで数を数える、どのようにすれば効率的でしょうか

No.3です。

こんばんは

No.9です。

No.3です。

たびたびすみません。

NO.1です。

COUNTIF関数でワイルドカードを使えば、数字が一桁なら

>1つのセルに1つの数ならcountifで簡単に数えられますが、、、、

No.3です。

ご質問者は

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング