[[20170729205747]]　『指定文字が入力されたセルの数を数える』（あらいぐま）　ページの最後に飛ぶ

『指定文字が入力されたセルの数を数える』（あらいぐま）

次のような表で、新潟と入力されたセルの数を数えたいと思います。

 　　A　　 B 　　C　　 D    E 　　F 
1　新潟　秋田　福岡　山形　　　　山梨
2
3  東京　新潟　新潟　秋田　山形
4
5
6　山梨　山形　福岡　　　　富山　福井　
7

小生の知識ではエクセルの関数COUNTIFを使って
=COUNTIF(A1:F1,"新潟")+COUNTIF(A3:F3,"新潟")+COUNTIF(A6:F6,"新潟")
と結果を表示するセルに式を入力することで処理をすることしか考えつきません。

(A1:F1,A3:F3,A6:F6,"新潟")のように複数の検索範囲を一度に指定して処理する方法（関数）はありませんか？

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 =COUNTIF(A1:F6,"新潟")
 では問題あり？
（ seiya） 2017/07/29(土) 23:00

seiya様、ありがとうございます。
小生がCOUNTIF()＋COUNTIF()＋COUNTIF()＋･･･を考えたのは、A2セルなど県名の下のセルに文章を入力する仕様であり、それらのセルに検索する文字列、例えば「新潟」という文字列が入力された場合、逸れもカウントされてしまうのではないかと心配したからです。
COUNTIFの引数は完全一致、例えば"新潟"では、"新潟"という言葉が含まれる言葉や文ではカウントされないということでしょうか。（セルA2に「新潟」を入力するとカウントされてしまいました。しかし、「新潟砂丘」「新潟は県都」などを入力し、試してみるとカウントされませんでした。）
もし、A2セルなどに検索する文字列と全く同じ文字列が入力されても検索対象としない（カウントしない）
方法はあるのでしょうか？
ご教示いただければ幸いです。
（あらいぐま） 2017/07/30(日) 06:14

 そういうことでしたら私もCOUNTIF()＋COUNTIF()＋COUNTIF()ですね。

 CountIf関数の検索条件にワイルドカードを使用した場合は"含む"をカウントしますが、それ以外は完全一致ですよね。
（ seiya） 2017/07/30(日) 11:31

飛び飛びのセル範囲をに名前を定義したらお望みのような？形の式でいける
かも知れないですね。
試してないですが。
（カリーニン） 2017/07/30(日) 12:36

 ＞飛び飛びのセル範囲をに名前を定義したらお望みのような？形の式でいける 
 ＞かも知れないですね。 
 ＞試してないですが。

 私も試していないですが、数式解析上、実体は同じなので、
 評価も同じ事になってしまい、旨く行かないと私は思います。
 複数の範囲をカッコで囲うと旨く行くようなケースだと、別かも知れませんが・・・

 以下、OFFSET()は揮発性関数ですが、それでよければ・・・

  =SUM(COUNTIF(OFFSET($A$1,{1,3,6}-1,0,1,6),"新潟"))

（半平太） 2017/07/30(日) 14:01

seiya様、ご教示ありがとうございました。「CountIf関数の検索条件にワイルドカードを
使用した場合は"含む"をカウント」は初めて知りました。今後に生かしたいと思います。

カリーニン様、半平太様、「セル範囲に名前を定義」ですが、範囲の所に"Value!"の表示が
出て、「この関数に対して、多すぎる因数が入力されています。」という警告が発せられま
した。やはり無理のようです。

なお、半平太様の「=SUM(COUNTIF(OFFSET($A$1,{1,3,6}-1,0,1,6),"新潟"))」では、期待
どおりの結果が得られました。この式ではどのような処理をして結果を導き出しているのか
お教えいただけるとありがたいです。

（あらいぐま） 2017/07/30(日) 16:32

 ＞OFFSET($A$1,{1,3,6}-1,0,1,6)

 A1セルからみて、1,3,6(より1少ない）行分、下に飛んだセルを参照しています。

 行高は１行、列幅は６列に指定します。

 以上により、飛び飛びの3行(つまり、A1:F1,A3:F3,A6:F6）が参照できる事になります。

 それら参照範囲をCOUNTIFの第1引数にすると"新潟"の数が、3行分、別々に(＝配列として）カウントされます。

 その数値配列をSumで合算します。

（半平太） 2017/07/30(日) 18:57

 外出先から思いつきで書き込みましたが、名前の定義案はやはり駄目でしたね。
 失礼しました。
（カリーニン） 2017/07/30(日) 19:24

カリーニン様、お返事ありがとうございました。
もしかしてと、飛んでいる範囲を一括名前を定義し直してみました。「この関数に対して、多すぎる
因数が入力されています。」という警告が発せられなかったのですが、範囲の所に"#Value"表示が出
て、結果も"#Value"となりました。名前定義のアイデアはダメということになりそうです。

半平太様、解説ありがとうございました。
配列ですか・・・　勉強して、今後に生かしていきます。

コメントくださいました皆様、ありがとうございました。
（あらいぐま） 2017/07/30(日) 20:44

 それぞれ連続した範囲を「範囲1」「範囲2」「範囲3」と名前を付けて
 =SUMPRODUCT(COUNTIF(INDIRECT("範囲"&ROW(1:3)),"新潟"))
 でも。
（ねむねむ） 2017/07/31(月) 11:13

コメント返信：

[ 一覧(最新更新順) ]

[[20170729205747]] 『指定文字が入力されたセルの数を数える』（あらいぐま） ページの最後に飛ぶ

[[20170729205747]]　『指定文字が入力されたセルの数を数える』（あらいぐま）　ページの最後に飛ぶ