いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Gスプレッドシート)AND,OR+ARRAYFORMULA関数のはたらき

 Googleスプレッドシートで、AND関数やOR関数とARRAYFORMULA関数を併用することで配列状の結果を得ようとして、期待どおりの結果が得られないことがあります。ここではその例と代わりの方法をみてみます。

  • AND関数やOR関数とARRAYFORMULA関数と併用しても、結果は配列ではなく1つの値にしかなりません。
  • 配列状の結果を得る場合はAND関数の代わりに「*」を,OR関数の代わりに「+」を用います。

手順

f:id:accs2014:20201205170528p:plain:right:w550

 画像では、B,C,D列に数値が並んでおり、それぞれ1回目,2回目,3回目と題されています。
 また、F3セルには次のような式が入力されています。

 F3セル

=AND(B3<C3,C3<D3)

 この式はB3<C3かつC3<D3のときにTRUEを返します。
 言い換えれば1回目<2回目<3回目のときにTRUEとなります。
 なお実際はB3<C3ではあるもののC3<D3ではないため結果はFALSEとなっています。
 

f:id:accs2014:20201205170532p:plain:right:w550

 さて、F3に入力されている式を、ARRAYFORMULA関数を併用して次のようにしてみました。
 一見ARRAYFORULA関数の効果により、F3~F8に、それぞれの行を評価した6つの結果がいっぺんに表示されるのではないかとも思われます。
 しかし実際にはFALSEという1つの結果しか得られません。

=ARRAYFORMULA(AND(B3:B8<C3:C8,C3:C8<D3:D8))

 なぜかというと「AND関数が、ARRAYFORMULA関数により展開される延べ12個(6行×2)の条件式をまとめて評価しているから」で、理屈としては「=SUM(B3:D8)」が1つの数値しか返さないのと同じことです。
 

f:id:accs2014:20201205170537p:plain:right:w550

 試しに表内の数値を変更し、すべての行についてB<CかつC<Dとなるようにしたところ、結果はTRUEとなりました。
 延べ12個の条件がすべてTRUEとなったからです。

 

f:id:accs2014:20201205170541p:plain:right:w550

 数値を元に戻しました。
 さて、AND関数ではうまくいかないので、ここでは次のような式にすることで、それぞれの行について評価した結果を得ることができました。

=ARRAYFORMULA((B3:B8<C3:C8)*(C3:C8<D3:D8))

 AND関数の代わりにカッコと「*」を使って評価式をつないでいます。
 これにより、それぞれの行でB<CかつC<Dの場合に「1」が、そうでない場合に「0」が得られます。
 TRUE/FALSEではなく1か0であることに注意してください。


f:id:accs2014:20201205170545p:plain:right:w550

 それでもIF関数とは支障なく組み合わせることができます(0以外の数値はTRUE、0はFALSEとみなされるため)。

=ARRAYFORMULA(IF((B3:B8<C3:C8)*(C3:C8<D3:D8),"連続↑","-"))

 IF関数のはたらきにより、B<CかつC<Dの場合に「連続↑」が、そうでない場合に「-」が表示されています。
 

f:id:accs2014:20201205170550p:plain:right:w550

 また、OR関数の代わりになるのは「+」です。

=ARRAYFORMULA((B3:B8<C3:C8)+(C3:C8<D3:D8))

 OR関数の代わりにカッコと「+」を使って評価式をつないでいます。
 これにより、それぞれの行でB<CまたはC<Dの場合に「1」または「2」(片方のみ満たす場合に1、両方満たす場合に2)が、そうでない場合に「0」が得られます。
 

f:id:accs2014:20201205170554p:plain:right:w550

 これもIF関数にそのまま当てはめて利用することができます。

=ARRAYFORMULA(IF((B3:B8<C3:C8)+(C3:C8<D3:D8),"1回以上↑","-"))

 IF関数のはたらきにより、B<CまたはC<Dの場合に「1回以上↑」が、そうでない場合に「-」が表示されています。