Excel の「配列数式」をご存じですか？ 知っている人には取り立てて騒ぐことではないのでしょうが、私は最近知ってこんなことができるのかと感心しました。 これを知っていると配列を使って数式を書くことができ、今まで二段階の計算で求めていた値が一発で計算できるようになると思います。
1. #N/A を含む合計を計算
以下の表の No.1 ～ No.10 までの合計はどのように求めれば良いでしょうか？
実は単純に SUM(B1:B10) とすると #N/A が表示されてしまいます。
何故ならば、合計する対象にエラーが含まれていると SUM はエラーを返す仕様だからです。
～IF 関数も使えるかも知れませんが、ここでは配列数式を使います。 配列数式を使った以下のような数式で合計できます。 ただし、数式の入力確定時に Enter でなく、Ctrl + Shift + Enter を使います。
=SUM(IF(ISNA(B1:B10),0,B1:B10))
「B1:B10」のように関数の引数に範囲を指定しているところがミソです。
2. 行を選んで合計
例えば以下のような表があったとします。
男のみ、女のみの合計はどのように計算すれば良いでしょうか？
男の合計は、
=SUM(IF(B1:B6 = "男", C1:C6, 0))
女の合計は、
=SUM(IF(B1:B6 = "女", C1:C6, 0))
となります。更に、
=SUM((B1:B6 = "男") * C1:C6)
という書き方も可能です。
最後の例は TRUE = 1、FALSE = 0 であることを利用したものですね。 トリッキーな感じがしますが、Microsoft 公式サイトでも案内されています。 以下のページの「条件に基づいて値を合計する」の部分です。 AND 関数や OR 関数はそのままでは配列数式中で使えないのでこのようなテクニックが必要とのことです。
いずれの数式も Ctrl + Shift + Enter で入力する必要があるのでご注意ください。