文字列が入っているセル数をカウントしたいのですが

質問

Excell2000です。初心者です。よろしくお願いします。

シート１にざっと1万件ぐらいデータがあるのですが、
A列に取引先のID
B列に取引先名
C列に日付
D列に評価（数値）
E列にコメント欄、が設けてあります。

日ごと更新のためAB列のデータの重複はあります。
E列のコメントは20件に1件ぐらい書かれている程度で、
記入がない場合は空欄です。

やりたいことは、シート２に取引先IDごとにコメントが何件あるのか出したいのです。
例えば
A列のIDが１の場合でコメントが入力されているのは何件あるか。

そしてできれば、その数を出した隣にリストでコメントが出るようになるとうれしいです。
コメント数が５なら、そのコメントがリストに５行出るようにしたいです。

「特定でない文字列が入っている場合」というのはやはりマクロをかかないとだめなのでしょうか。（やったことがないので）
いや、マクロでもがんばりますのでどなたか教えていただけませんか？

回答No.5

matsu_jun · ベストアンサー

以下のようにすれば、お好きな場所にコメントをリスト化して収納いたします。
デフォルトではセルB1をリスト化しておりますが、前回同様変更しやすいようにしておきました。
今まで「結果コメント列」、「結果記入列」としていたところをそれぞれ「セル」としておいたので、セル名をそのまま記入してみてくださいませ。

Sub コメント検索()
　　Dim 元シート, 移動先シート, ID記入列, コメント記入列, 結果コメントセル, 結果記入セル As String

'必要に応じて変更してください---------------------------------

　　元シート = "Sheet1"
　　ID記入列 = "A"
　　コメント記入列 = "E"
　　移動先シート = "Sheet2"
　　結果コメントセル = "B1"
　　結果記入セル = "A1"

'ここまでが変更できる範囲です---------------------------------

　　Dim IDCOL, COMMCOL As Integer
　　Dim COMMCNT As Long
　　Dim RSLTTMP As String
　　IDCOL = Asc(LCase(ID記入列)) - 96
　　COMMCOL = Asc(LCase(コメント記入列)) - 96
　　COMMCNT = 0
　　RSLTTMP = Empty
　　'IDナンバーを入力します
　　IDNUM$ = InputBox("コメントを表示したいIDを選択してください", "ID選択")
　　'キャンセル時の処理
　　If IDNUM = Empty Then Exit Sub
　　'前回の結果を消去します
　　Worksheets(移動先シート).Range(結果コメントセル).Validation.Delete
　　Worksheets(移動先シート).Range(結果記入セル).Clear
　　'ID列の書込済最下行まで入力したIDを検索します
　　For i# = 2 To Worksheets(元シート).Cells(65536, IDCOL).End(xlUp).Row
　　'IDがヒットしたら、その列のコメントをシート2に転記します
　　　　If CStr(Worksheets(元シート).Cells(i, IDCOL).Value) = IDNUM _
　　　　And Worksheets(元シート).Cells(i, COMMCOL).Value <> Empty Then
　　　　　　RSLTTMP = RSLTTMP & "," & Worksheets(元シート).Cells(i, COMMCOL).Value
　　　　　　COMMCNT = COMMCNT + 1
　　　　End If
　　Next i
　　'ヒットした件数をシート2に記入します
　　Worksheets(移動先シート).Range(結果記入セル).Value = COMMCNT & "件"
　　If COMMCNT = 0 Then Exit Sub
　　RSLTTMP = Right(RSLTTMP, Len(RSLTTMP) - 1)
　　Worksheets(移動先シート).Range(結果コメントセル).Validation.Add Type:=xlValidateList, Formula1:=RSLTTMP
End Sub

実行してみた上で、リストボックスに対する解釈が異なる場合はもう一度報告ください。修正いたします。

お礼

うわーすごいですね！
何がなにやらですが、
これをプリントアウトして明日じっくりやってみて
また報告させていただきますね。
ありがとうございました！

回答No.9

macchan1

ごめんなさい。検証不足でした。
正しい数式は以下のとおりです。
別シートの適当なセルにコピーし、Ctrl+Shift＋Enterで配列数式にしてください。

=IF(SUMPRODUCT((Sheet1!$A$1:$A$10000=1)*(Sheet1!$E$1:$E$10000<>""))>=ROW(A1),INDEX(Sheet1!$A$1:$E$10000,LARGE((Sheet1!$A$1:$A$10000=1)*(Sheet1!$E$1:$E$10000<>"")*ROW($A$1:$A$10000),ROW(A1)),5),"")

お礼

おかげさまで解決できました。
ありがとうございました！

回答No.8

macchan1

>上の式はどこにはればよいのでしょうか。

別シートのどのセルに貼り付けてもOKです。
ただし、この数式をコピー貼り付け後、Ctrl+Shift+Enterで確定して下さい。

＞ただ、やってみたら実際のコメントは５つしかなくても表示が６になるのです。

A列で1と入力されていてかつE列が空白でないセルの合計が計算されています。空白に見えるセルの中にスペースなどが入力されていたりするとそのセルもカウントします。

E列のデータ範囲を選択し、「編集」「ジャンプ」「セル選択」で「空白セル」にチェックを入れて実際に空白に見えるセルで選択されていないセルを調べてみてください。

お礼

＞空白に見えるセルの中にスペースなどが入力されていたりするとそのセルもカウントします。

なるほど！
空白のセルの調べ方も勉強になりました。
ありがとうございました！

回答No.7

macchan1

ご質問はコメントだけでよいのですね。

その場合は以下の数式でOKです（縦方向にオートフィル）。

=IF(SUMPRODUCT(($A$1:$A$10000=1)*($E$1:$E$10000<>""))>ROW(A1),INDEX(Sheet1!$A$1:$E$10000,LARGE((Sheet1!$A$1:$A$10000=1)*(Sheet1!$E$1:$E$10000<>"")*ROW($A$1:$A$10000),ROW(A1)),5)),"")

また、該当データ数は以下の式です。

=SUMPRODUCT(($A$1:$A$10000=1)*($E$1:$E$10000<>""))

実際の数式では「1」の部分はセル参照（絶対参照）すると良いと思います。

お礼

回答ありがとうございます！
上の式はどこにはればよいのでしょうか。
オートフィルということはシート１の方ですか。

下の式はわかりました！
空欄じゃないのは「<>""」とかくのですね。
勉強になりました。
ただ、やってみたら実際のコメントは５つしかなくても表示が６になるのです。
これはなぜ１つ多くでてしまうのでしょうか。

回答No.6

macchan1

関数で対応するなら以下のような式でできます。
例えば、Sheet1のA列のID番号1のデータを表示するなら以下の式を入力し、Ctrl+Shift+Enterで確定し配列数式にし下方向、及び右方向にオートフィル(コピー）します。

=IF(SUMPRODUCT(($A$1:$A$10000=1)*($E$1:$E$10000<>""))>ROW(A1),INDEX(Sheet1!$A$1:$E$10000,LARGE((Sheet1!$A$1:$A$10000=1)*(Sheet1!$E$1:$E$10000<>"")*ROW($A$1:$A$10000),ROW(A1)),COLUMN(A1)),"")

ちなみにLARGE関数を使ったのでSheet1のデータが下から順に表示されます。

回答No.4

matsu_jun

マクロを作りましたのでご利用ください。下の「ここから」から「ここまで」を貼り付けた上、必要に応じて値を変更してご利用ください。
「'必要に応じて変更してください」と書いてある下の行6行は、実際の状況に合わせて変更してください。
「" "」に囲まれた部分が自由に変更できる部分です。シート名や、ID・コメントが記載されている列、結果を残したい列などが変更できます。

'ここから-------------------------------------------------------------------------------------

Sub コメント検索()
　　Dim 元シート, 移動先シート, ID記入列, コメント記入列, 結果記入列, 結果コメント列 As String

'必要に応じて変更してください---------------------------------
　　元シート = "Sheet1"
　　ID記入列 = "A"
　　コメント記入列 = "E"
　　移動先シート = "Sheet2"
　　結果コメント列 = "B"
　　結果記入列 = "A"
'ここまでが変更できる範囲です---------------------------------

　　Dim IDCOL, COMMCOL As Integer
　　Dim COMMCNT, COMMROW As Long
　　IDCOL = Asc(LCase(ID記入列)) - 96
　　COMMCOL = Asc(LCase(コメント記入列)) - 96
　　COMMCNT = 0
　　COMMROW = 1
　　'前回の結果を消去します
　　Worksheets(移動先シート).Range(結果記入列 & "1").Clear
　　Worksheets(移動先シート).Range(結果コメント列 & "1:" & 結果コメント列 & "65536").Clear
　　'IDナンバーを入力します
　　IDNUM$ = InputBox("コメントを表示したいIDを選択してください", "ID選択")
　　'キャンセル時の処理
　　If IDNUM = Empty Then Exit Sub
　　'ID列の書込済最下行まで入力したIDを検索します
　　For i# = 2 To Worksheets(元シート).Cells(65536, IDCOL).End(xlUp).Row
　　'IDがヒットしたら、その列のコメントをシート2に転記します
　　　　If CStr(Worksheets(元シート).Cells(i, IDCOL).Value) = IDNUM _
　　　　And Worksheets(元シート).Cells(i, COMMCOL).Value <> Empty Then
　　　　　　Worksheets(移動先シート).Range(結果コメント列 & COMMROW).Value = Worksheets(元シート).Cells(i, COMMCOL).Value
　　　　　　COMMCNT = COMMCNT + 1
　　　　　　COMMROW = COMMROW + 1
　　　　End If
　　Next i
　　'ヒットした件数をシート2に記入します
　　Worksheets(移動先シート).Range(結果記入列 & "1").Value = COMMCNT & "件"
End Sub

'ここまで-------------------------------------------------------------------------------------

上の貼り付け方が分からない場合は、以下ご覧下さい。

1) ツール(T)－マクロ(M)－新しいマクロの記録(R)を開く
2) 「マクロの記録」ウィンドウが表示されたら、何も変更せずにOKをクリック
3) 画面上に二つのボタンが表示されたツールバーが現れたら、左側の「■」をクリックして記録終了
4) Altキーを押しながらF8キーを押して、マクロウィンドウを開く
5) マクロウィンドウから、編集(E)ボタンをクリック
6) 現れた「Microsoft Visual Basic」の右側に
Sub Macro1()
'
'
'
End Sub
と書いてある部分を削除して、上の「ここから」から「ここまで」を貼り付ける。
7) もう一度Altキー＋F8キーでマクロウィンドウを開き、実行(R)ボタンをクリック

お礼

さっそくやってみたらできました！
明日会社でもやってみますね！

できればコメントはリストボックスに出せるといいのですが、
それはわがまますぎるのでしょうか。
コメントを表示させるスペースがあまりとれないのです。
もし無理であれば、コメントなしで済ませようと思ってます。

補足

今日さっそく会社でやってみてできましたよ！
ありがとうございました。

これはボックスが出てIDを入力をする方法ですが、
例えばA2にID番号を入力するとB2にコメント数が出たり
C2にリストができたりってことはできるのでしょうか。

回答No.3

noname#118337

No.2です。回答書きましたがもっといいのが思い浮かんだので書きます。

シート1のデータの最後の列にコメントがあれば1が入るように設定し、シート２でIDごとにその1の数の合計を表示する方法です。
これだとフィルタしなくても良く、一覧で表示されるので、大量のデータでも楽かと思います(^-^)
データは10000件、IDはa01からa50まであるとして書きますね。
(1)F1に、=IF(NOT(E1=""),1,"")と入力する。
　 （コメントがあれば1と表示され、なければ空白になる）
(2)F10000までオートフィルする。
　 F10000は=IF(NOT(E10000=""),1,"")となる
(3)Sheet2のA1:A50にIDの一覧を作る。
(4)Sheet2のB1に=SUMIF(Sheet1!$A$1:$F$10000,a01,Sheet1!$F$1:$F$10000)と入力する。
(SUMIFの引数は、(範囲,検索条件,合計範囲)です。これでは、A1:F10000が表の範囲でIDがa01でF列に1が入っているデータのみを検出したいのでこうしています)

(5)(4)のセルをA50までオートフィルする。
(6)A1:A50までが全てa01の結果になってしまうので、関数のIDの箇所のみ変更する。

これで一覧になるかと思います。

補足

またまた回答ありがとうございました（^^）
数値にしてSUMIFを！の件は私も考えたのですが
できれば「文字列をカウント」＆集計をしたかったんです。
でもそれはVBAとか使わないとやはり不可能なんですね。
教えてくださってありがとうございました！

回答No.2

noname#118337

No.1さんのやり方でSheet2にコピーし、Sheet2ではA列の前に1列挿入してA列にカウンタが出るようし、フィルタすればいいんじゃないかと思います。

A列の前に1列挿入した状態で、値の入っている表がB1:E500だとするなら、、
(1)A1に=SUBTOTAL(102,B$1:B1)と入力する。
(2)A1のデータをA500までオートフィルする。
　 (A500は=SUBTOTAL(102,B$1:B500)になる。
(3)B1:E500にオートフィルタがかかる状態にする。

これでIDでフィルタリングすると、フィルタのかかった行に行数が表示されます。
SUBTOTALはフィルタされたデータのみで、集計したり平均値を出したりできるので便利ですよ。

補足

回答ありがとうございました。
SUBTOTALは初めて見ました。
102は集計方法ですよね？ヘルプに102は載ってのかったのでなぜ102なのかわかりません。
ほんとわからなすぎですいません。

回答No.1

harukabcde

ＡとＥでソートして、
Ｅが入っているのだけオートフィルで表示し
シート２にコピーした方が早いと思う。

それをマクロで記録してもいいしね。

補足

回答ありがとうございます。
実はシート２には会社ごとのデータ集計や
グラフなどがあり、スペース的に
会社ごとに変動するコメント数に対応することが
できないため、リストという考えが浮かんだんです。