エクセル、COUNTIFS関数の範囲指定について

質問

エクセル2010です。

「A1の文字列が、B列とC列の中から合致し、さらにD列の"○○"という条件を満たすものはいくつあるか」
という数式を作りたいです。
写真でいうと、B・C列の中から中央区があるかどうか、さらにその中に山田がいるか、という検索式です。

そこで、COUNTIFS関数を使い、fxボタンで半自動で作ったところ、
=COUNTIFS(B:C,A1,D:D,"山田")

となったのですが、なぜか結果はエラーになってしまっています。
どうやら検索条件の範囲が「B:C」になっているのが問題なのか、B:BやC:Cだけにすると、ひとつの列なら正常に計算するようです。

そこで式を二つに分け、プラスでつなげてみました。
=COUNTIFS(B:B,A1,D:D,"山田")+COUNTIFS(C:C,A1,D:D,"山田")

こうしてみると一応成功しました。
しかし動作が重くなり、処理に数秒かかるようになってしまいました。
（B:Bなどひとつの列なら、数式を記入した瞬間に結果がでます）
できれば数式は短くシンプルにしたいので、前述の短い形式のようにならないものか、と思います。
COUTIFS関数で、検索範囲を「○列～△列」のようにできないものでしょうか？

アドバイスをよろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

COUNTIFS関数を使う限り、動作が重くなるのは避けることができません。ただし列単位の範囲の指定ではなく行を入れた範囲の指定によって多少動作を軽くできるでしょう。
データが多くなる場合には作業列を作って対応するのが最も動作を軽くする方法です。
例えばE1セルには次の式を入力して下方にドラッグコピーします。

=B1&C1&D1

その上で次の式でカウントすればよいでしょう。

=COUNTIF(E:E,"*"&A1&"*山田*")

お礼

ありがとうございます。
今回のような大量のデータの場合、COUNTIFS関数よりもこのような方法のほうが軽くできるのですね。
これも試してみたところ、かなり軽く動き、動作も実現できました。

実際のデータはもう少し複雑で順番やシートなどの構成も複雑なのですが、アスタリスクや&の使い方がいまひとつわからないので、こちらももう少し試行錯誤してみます。

回答No.4

kagakusuki

作業列の1行目のセルであるE1セルに入力する関数を、

=B1&C1&D1

だけで済ませたり、カウントするための関数を

=COUNTIF(E:E,"*"&A1&"*山田*")

としただけでは、例えばA1セルに東区と入力した場合に、もしも、城東区に小山田さんという人がいた場合には、その「城東区の小山田さん」も、「東区の山田さん」のデータとしてカウントされてしまいます。
　ですから、E1セルに入力する関数を、

="◎"&B1&"◆◎"&C1&"◆◇"&D1&"●"

の様に、地域の名前や人の名前の前後を、地名や人名には使われる事のない記号で区切る様にする関数としておいた上で、カウントするための関数を次の様にされた方が良いと思います。

=COUNTIF(E:E,"*◎"&A1&"◆*◇山田●")

お礼

すいません、昼から教えていただいた数式とずっとにらめっこして、やっと理解しました。
アスタリスクはワイルドカードというものなのですね。それと記号を組み合わせて、いったんE列で文字列化したものから、条件に合う文字列部分とのマッチングをかける、ということなのですね。
今、テストで一部にこの方法を実践してみたところ、非常に動作も軽く、思う結果を得ることができました。
ありがとうございました。

解決につながるとても重要なヒントをいただいたので、ベストアンサーを差し上げたいのはやまやまなのですが、順番として、先にこの方法（別の作業列を作るやり方）を教えていただいた方に差し上げようと思います。

補足

少し追加で質問なのですが、

="◎"&B1&"◆◎"&C1&"◆◇"&D1&"●"

に対しての、=COUNTIF(E:E,"*◎"&A1&"◆*◇山田●")

の式は、例えば"*◎"の部分は、E1セルに記入する要素の何にあたるのでしょうか。
アスタリスクはどういう働きをするのですか？
また、E列に記入するセルの順番と、COUNTIF関数の条件の順番を同じにするということですか？

回答No.3

MackyNo1

>=COUNTIFS(B:B,A1,D:D,"山田")+COUNTIFS(C:C,A1,D:D,"山田")
こうしてみると一応成功しました。
しかし動作が重くなり、処理に数秒かかるようになってしまいました。
（B:Bなどひとつの列なら、数式を記入した瞬間に結果がでます）

基本的に2つのCOUNTIFS関数を使用した場合は、1つだけの場合の2倍の時間がかかるだけです。
数式の再計算処理に時間がかかるようになったのは、数式をつなげたことが原因ではありません。

再計算に時間がかかる原因としては、上記のような数式を大量に入力した、あるいは別の配列数式（SUMPRODUCT関数などを含む）を多用していることが考えられます。

また、配列数式の場合はデータ範囲を必要以上に大きくすると極端に再計算に時間がかかるようになります。
COUNTIFS関数の例で言うと、検索条件範囲を列全体を指定するのではなく、$B1$B1000のように実際のデータ範囲よりも少し大きめの範囲を指定してください。

＞できれば数式は短くシンプルにしたいので、前述の短い形式のようにならないものか、と思います。
COUTIFS関数で、検索範囲を「○列～△列」のようにできないものでしょうか？

基本的にCOUNTIFS関数の範囲は同じ配列の大きさの範囲を指定する必要がありますので、2つの数式に分割するしか方法はありません。

ちなみに、COUNTIFS関数よりも圧倒的にパフォーマンスが悪く再計算に時間がかかりますのでお勧めできませんが、例えばSUMPRODUCT関数を使用すれば1つの数式にすることができます。

=SUMPRODUCT((B:C=A1)*(D:D="山田"))

実際にエクセル2003以前のバージョンではCOUNTIFS関数が使用できないので、この関数を使用する必要がありますが、この場合は(エクセル2007以降で利用する場合でも)、列全体ではなく以下のように範囲を指定したほうが再計算に時間がかかりません。

=SUMPRODUCT(($B$1:$C$100=A1)*($D$1:$D$100="山田"))

お礼

ありがとうございます。
SUMPRODUCT関数も試してみました。これも希望する結果を得ることができましたが、COUNTIF関数よりも動作が重くなってしまい、実際の運用は諦めざるをえないようです。
ただ、B:Bのような列指定よりも、実際のデータ範囲をきちんと指定するほうが動作の負担が減るということを教えていただき、勉強になりました。

回答No.1

aokii

=COUNTIFS(B:C,A1,D:D,"山田")では、「A1かつ山田」の条件が満足されるセル個数になりますので、B:CとD:Dでは人間にもカウントできません。

=COUNTIFS(B:C,A1,D:E,"山田")ならカウントできます。

お礼

ありがとうございます。
=COUNTIFS(B:C,A1,D:E,"山田")を試してみたところ、うまくいきました。同じ範囲の大きさが必要なのですね。勉強になりました。
ただ今回のような大量のデータの場合、COUNTIFS関数を使うとどうしても重くなりがちということなので、別の方法も模索してみたいと思います。