スプレッドシートの、ARRAYFORMULA,ROUNDDOWN,IF文を組み合わせた数式の正しい書き方について質問です。

Question

下記のスプレッドシート
M列の「マージン」の欄に数式を入れようとしています。

内容としては、
D列の「所有者」のセルに「A」あるいは「B」と書かれていればL列の数値を2で割った数値を表記させ、「C」と書かれていればL列の数値を「0」と表記させる数式です。

そこで下記のシートの通り、

=ARRAYFORMULA(ROUNDDOWN(IF(OR(D76:D="A",D76:D="B"),L76:L/2)))

という数式を作ってみたのですが、これだとＤ列に一つでも「A」あるいは「B」と書かれていると、L列の全てのセルに「L列の数値÷2」の数値が表記されてしまい、所有者が「C」の場合に「０」と表記できないです。

数式の書き方が間違っているのかなと思い調べておりますが、これについてよろしければアドバイスをいただけないでしょうか？
すみませんがよろしくおねがしますm(_ _)m

Answer 1

ARRAYFORMULA 関数の中で、 OR 関数が期待する動作をしていない ことが原因です。同様の理由で AND 関数も期待通りには動作致しません。代わりに、例えば この Google Docs のフォーラム に御座いますように、 OR 関数は比較演算結果の和で、 AND 関数は比較演算結果の積で 置き換えることが有効です。

今回で言えば、 OR(D76:D="A", D76:D="B") とするのではなく、 (D76:D="A")+(D76:D="B") とするという事です。比較演算子の結果は TRUE または FALSE であり、またそれを算術演算すると TRUE は 1, FALSE は 0 と評価されますから、これにより OR 関数相当の結果が得られるかと存じます。

余談: 期待通りに動作してくれない理由

何故このような問題が生じるのかは、単純に =ARRAYFORMULA(OR(XX:XX="A")) のような形で、 OR 関数のみを ARRAYFORMULA にかけてやると分かりやすいかと思います。実際にやってみると、結果が複数のセルに展開されず、 数式を記入したセルだけに TRUE または FALSEが得られる はずです。

これはつまり、 OR 関数が ARRAYFORMULA 関数によって「配列の要素ごとに比較し、結果の配列を返す」という (直観的に期待する) 動作では無く、 「配列の要素ごとに比較した全ての結果を OR し、単一の値を返す」 という動作をしてしまっていることを意味します。イメージとしては、 SUM 関数に ARRAYFORMULA をかけている様な図を思い描くと良いかもしれません。結果、仰るような「D 列に一つでも…」という動作に繋がります。

Comments

[raddish]

ご回答ありがとうございます。
いただいたアドバイスをもとに修正してみました。

=ARRAYFORMULA(ROUNDDOWN(IF((D76:D="A")+(D76:D="B"),L76:L/2,0)))

という式に変更して実行してみたところ、正常に動きました！

ORの性質や、ARRAYFORMULAとの組み合わせについて理解が浅かったので、詳しく教えていただき非常に勉強になりました。お忙しい中ご教示いただき、誠にありがとうございましたm(_ _)m

ベストアンサーとさせていただきます。