ARRAYFORMULA関数内でCOUNTIFS関数は使えない?代わりにFIND関数を使おう!

ARRAYFORMULAとCOUNTIFS関数は使えない?代わりにFIND関数を使おう!
mono
mono
こんにちは、monoです。

検索値が不変の場合におけるARRAYFORMULA関数とCOUNTIFS関数の使い方についてよく聞かれます。

しかし残念ながら検索値が不変の場合、ARRAYFORMULA関数とCOUNTIFS関数を組み合わせて使うことはできません(検索値が変動の場合は可能です。詳しくは「スプレッドシートの関数を自動コピー!ARRAYFORMULA関数の使い方と活用例7選」)。

その代わりに別の関数を使えば望むような動作を実現できるので、今回はその別の関数の使い方を覚えていきましょう。

当記事では、

  • ARRAYFORMULAとCOUNTIFS関数は使えない?
  • ARRAYFORMULA関数・FIND関数とは?
  • ARRAYFORMULA関数・FIND関数の組み合わせ方

について解説します。

目次

ARRAYFORMULAとCOUNTIFS関数は使えない

冒頭で申し上げた通り、ARRAYFORMULA関数とCOUNTIFS関数を同時には使えないのが結論です。

何度も試した方が多いと思いますが、ARRAYFORMULA関数とCOUNTIFS関数を同時に使うと、以下のように列全体を対象にしてCOUNTIFS関数が実行されてしまいます。

「本当は1行ずつ実行したいのにどうしたらいいんだー!」と叫びたいのはわかりますが、ここは冷静にFIND関数で代用しましょう。

ARRAYFORMULA関数・FIND関数とは?

「そもそもARRAYFORMULA関数・FIND関数って何?」という方のために、2つの関数についてサラッと解説します。

ARRAYFORMULA関数

ARRAYFORMULA関数とは「セル範囲を配列として扱える関数」です。

ここでいう配列とは、プログラミングでいう「配列」、数学でいう「行列」、スプレッドシートでいう「セル範囲」をイメージしてもらえればOKです。

ARRAYFORMULA関数の構文

=ARRAYFORMULA(セル範囲)

ARRAYFORMULA関数の動作例

ARRAYFORMULA関数についてさらに知りたい人は、こちらの記事をあわせてどうぞ。

あわせて読みたい
スプレッドシートの関数を自動コピー!ARRAYFORMULA関数の使い方と活用例8選 スプレッドシートの関数はコピーして貼り付けることで、列全体、行全体などに展開することができます。 でも、関数を逐一コピーして貼り付けるのは手間だし、セル範囲が...

FIND関数

=FIND(検索文字列, 検索対象のテキスト, 開始位置)

FIND関数は「特定の文字列がテキスト内で最初に現れる位置を返す関数」です。

文字列の中にある任意の文字列が何文字目にあるか知りたい場合は、FIND関数を使いましょう。

ARRAYFORMULA関数・FIND関数の組み合わせ方

さて、いよいよARRAYFORMULA関数とFIND関数を組み合わせて使う方法です。

単一条件での組み合わせ方(COUNTIF)

まずは簡単な単一条件での組み合わせ方について解説します。

結論、以下の構文で記述すればOKです。

ARRAYFORMULA+FINDの構文

=ARRAYFORMULA(IF(IFERROR(FIND(検索文字,セル範囲),0)>0,1,0))

以下に、「りんご」という文字列を含むときは1、含まないというは0を返す動作例を載せておくので、参考にしてみてください。


=ARRAYFORMULA(IF(IFERROR(FIND("りんご",A:A),0)>0,1,0))

複数条件での組み合わせ方(COUNTIFS)

次に、少し複雑な複数条件での組み合わせ方について解説します。

結論、以下の構文で記述すればOKです。条件式内の記述は、単一条件の場合と同じ書き方をしましょう。

=ARRAYFORMULA(IF(条件式1,IF(条件式2,1,0),0))

以下に、「りんご」と「あめ」という文字列を同時に含むときは1、含まないというは0を返す動作例を載せておくので、参考にしてみてください。


=ARRAYFORMULA(IF(IFERROR(FIND("りんご",A:A),0)>0,IF(IFERROR(FIND("あめ",A:A),0)>0,1,0),0))

まとめ

今回は「ARRAYFORMULAとCOUNTIFS関数は使えない?」「ARRAYFORMULA関数・FIND関数とは?」「ARRAYFORMULA関数・FIND関数の組み合わせ方」について解説しました。

ARRAYFORMULAとCOUNTIFSを組み合わせることはできませんでしたが、FIND関数で代用することはできました。

このように、スプレッドシートではもともと想定していた関数で動かなくても、別の関数を使えば実現できることもあるので、思うよう動かなくても、他にできる方法がないか考える習慣を付けておきましょう。

では、また。

あわせて読みたい
【スプレッドシートの関数一覧】おすすめ関数50選も紹介! スプレッドシートの事務作業、効率的に片付けたいですよね。 当記事では、 「スプレッドシートでおすすめの関数を知りたい」 「スプレッドシートの関数の一覧を知りたい...

あわせて読みたい
Googleスプレッドシートの使い方の勉強におすすめの本10選【実用書・入門書】 スプレッドシートの本選びで、以下のような悩みを抱えていませんか? スプレッドシートの使い方を書籍で勉強したいけど、どれが良い本か分からない 時間に余裕がないか...

目次
トップへ
目次
閉じる