エクセル2003 文字のカウントについて

質問

エクセル2003の環境です。

1つのセルの中に複数の物件名が記入されています。
その物件数をカウントしたいのです。

参考画像の表の赤枠のセルにどのような数式を設定すればいいか教えて下さい。

尚、=COUNTA(B2:B7)を使うと答えは「3」となり、=COUNTA(B2:B7,"・")を使うとなぜか答えは「4」となります。
期待する値「5」を得る為にはどのような関数を利用すればいいでしょうか？

回答No.8

hige_082 · ベストアンサー

＞ずいぶん近づいて来ました
あっそう
質問の小出しは嫌われますよ
＞一つのセルに物件が2件以上来る場合もあります。
＞最後の行に改行が入っているとカウント数が相違します
回答するたびにこれではね、最後にしてもらいたいものです

'標準モジュールへ
Function セルの物件名を数える(セル範囲 As Range) As Integer
Dim Rng As Range
Dim tStr As Variant
Dim i As Integer
For Each Rng In セル範囲
If Rng.Value <> "" Then
tStr = Split(Rng.Value, vbLf)
For i = 0 To UBound(tStr)
If tStr(i) <> "" Then セルの物件名を数える = セルの物件名を数える + 1
Next i
End If
Next Rng
End Function

お礼

ありがとうございました！！
完璧です！

思った通りの数値を計算してくれました。

回答No.7

hige_082

＞一つのセルに物件が2件以上来る場合もあります。
そうですか
そうなると、関数を作った方がスッキリすると思います

'標準モジュールへ
Function セルの物件名を数える(セル範囲 As Range) As Integer
Dim Rng As Range
Dim tStr As Variant
For Each Rng In セル範囲
If Rng.Value <> "" Then
tStr = Split(Rng.Value, vbLf)
セルの物件名を数える = セルの物件名を数える + UBound(tStr) + 1
End If
Next Rng
End Function

上のマクロを標準モジュールにコピペすると
関数の選択ダイアログに「セルの物件名を数える」と言う関数が現れます
後はセルのB8へ
=セルの物件名を数える(B2:B7)
と、入力するだけ

以上参考まで

補足

回答ありがとうございます。
ずいぶん近づいて来ましたが、最後の行に改行が入っているとカウント数が相違します。

その辺りを何とかできないでしょうか？
よろしくお願いします。

回答No.6

tom04

こんばんは！
色々方法は出ていますが・・・
一つの案です。

あまりスマートではありませんが、
別表を作成し、それを合計する方法になります。

↓の画像でG2セル（別Sheetでも構いません）に
=IF(B2="","",LEN(B2)-LEN(SUBSTITUTE(B2,"・","")))

という数式を入れ、オートフィルで列方向と行方向にコピーします。

これを合計する方法です。
セルを
B8=SUM(G2:G7)
として列方向にコピー

E2セルに
=SUM(G2:I2)
として行方向にコピーしているだけです。

以上、かなり強引な方法になりますが、
参考になれば幸いです。m(__)m

回答No.5

KURUMITO

Ｅ2セルには次の式を入力してＥ7セルまでオートフィルドラッグします。
=COUNTIF(B2:D2,"・*"&CHAR(10)&"・*")+COUNTIF(B2:D2,"・*")
Ｂ8セルには次の式を入力してＤ8セルまでオートフィルドラッグします。
=COUNTIF(B2:B7,"・*"&CHAR(10)&"・*")+COUNTIF(B2:B7,"・*")

回答No.4

okormazd

=COUNTIF(B2:B7,"・*")+COUNTIF(B2:B7,"・*・*")

補足

一つのセルに物件が2件以上来る場合もあります。
いくつ来ても大丈夫な式にするにはどうすればいいでしょうか？

回答No.3

noname#204879

E2: {=SUM(LEN(B2:D2)-LEN(SUBSTITUTE(B2:D2,"・","")))}
B8: {=SUM(LEN(B2:B7)-LEN(SUBSTITUTE(B2:B7,"・","")))}

【注】何れも配列数式です。

回答No.2

hige_082

一案です
=COUNTA(B2:B7)+COUNTIF(B2:B7,"*"&CHAR(10)&"*")

補足

一つのセルに物件が2件以上来る場合もあります。
いくつ来ても大丈夫な式にするにはどうすればいいでしょうか？

回答No.1

akiomyau

何々を含むといった関数は用意されていませんので
あまりスマートな式とは言えませんが
以下のような式でどうでしょうか。

=LEN(CONCATENATE(B1,B2,B3,B4,B5,B6,B7))-LEN(SUBSTITUTE(CONCATENATE(B1,B2,B3,B4,B5,B6,B7),"・",""))