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

2020-03-01

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活用例7選

FIND関数

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

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

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

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

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

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

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

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

ARRAYFORMULA+FINDの構文

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

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

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

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

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

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

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

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

 command
=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関数で代用することはできました。

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

では、また。

【保存版】スプレッドシート関数一覧!業務が爆速化する関数33選

【2019年】スプレッドシートの勉強におすすめの本7選【網羅しました】

  • この記事を書いた人

mono

26歳♂️関東在住のサイドFIREを目指すブロガー/座右の銘は「知識は誰にも奪われない」/ゆるく稼いで楽しく暮らすライフハック情報をお届けします/アイコンは黎(クロイ)さん。

スキルを磨く スプレッドシート

ARRAYFORMULA スプレッドシート関数

© 2021 monoblog