Excel 配列数式の利用方法とメリット・デメリット

概要

あまり知られていないませんが

Excel2007以降であれば

使用可能な強力な機能です。

配列数式を適切に用いれば

中間計算セルや数式を省略して

シートを簡潔にすることが可能です。

使用例とメリット

単価×数量の総計金額を算出

例えば単価と数量を掛け算した上での

総合計金額を算出する場合は、

それぞれの行毎に掛け算した

中間計算セルを作るのが一般的です。

従来の方法。中間計算セル(販売金額:F列)を使用する場合
中間計算セル(販売金額:F列)を使用する場合

配列数式を使えば

この販売金額列を作らずに

合計を算出することが可能です。

配列数式ではC2セルに

下記のように数式を記述します。

=SUM(単価のセル範囲 * 数量のセル範囲)

なお二つのセル範囲は大きさが

一致している必要があります。

この状態で[Ctrl]+[Shift]+[Enter]と

数式が{}で囲まれ配列数式となります。

結果は単価と数量をそれぞれ

掛け算した上で合計します。

(中間計算式セルを使った結果と同じ)

中間計算セルを作らず、配列数式を使用した例
中間計算セルを作らず、配列数式を使用した例

これにより不要なセルを作らずに

合計販売金額で出せるため

数式やシートが簡潔になり

ファイルサイズも節約できます。

なお{}はキーボード入力しても

配列数式にはなりません。

[Ctrl]+[Shift]+[Enter]で

確定する必要があります。

条件に一致するものの集計

Excel2016より古い環境では

COUNTIF関数、SUMIF関数、

AVERAGEIF関数などはありますが、

MAXやMINには条件を指定を

出来るものがありません。

よってIF関数で条件指定をセルを

作ってからMAX関数などで

集計する必要があります。

先ほどと同様の例で

フードのみの最高販売金額を求めます。

中間計算セル(フードのみの販売金額:F列)を使用する場合
中間計算セル(フードのみの販売金額:F列)を使用する場合

この例ではF列にIF関数を使用し、

種類がフードのみの販売金額を算出し、

C2セルにF列へのMAX関数を

記述しています。

これを配列数式を使用すると

下記のような記述になります。

=MAX(IF(種類のセル範囲="フード",単価のセル範囲*販売数量のセル範囲,0))

こう記述した状態で[Ctrl]+[Shift]+[Enter]で

セル入力を確定させます。

中間計算セルを作らず、配列数式を使用した例

中間計算セルを作成したのと

同様の結果がセル一つで算出されます。

もちろんMIN関数でも可能です。

なおExcel2016以降は

MAXIFS関数やMINIFS関数が

追加されているため

それらを使用する方が適切です。

複数の条件に一致するセルの中の最大値を取得(MAXIFS関数)

複数の条件に一致するセルの中の最小値を取得(MINIFS関数)

単一セルの引数にセル範囲を指定

単一のセルしか指定できない関数に

セル範囲を対応させ記述を簡潔にする

利用法が可能です。

FIND・FINDB関数に複数の検索文字列を指定する方法

デメリット

あまり知られていないこと

配列数式はかなり便利な割に

知られていない機能のため

シート作成者以外の人が見ると

何をしているのか分からず

数式を破壊してしまう恐れがあります。

非常に壊れやすいこと

配列数式を使用しているセルに対して

セル入力モードに入って、

そこから離れると配列数式が解除され

エラーとなってしまいます。

何も変更していなくても

毎回、[Ctrl]+[Shift]+[Enter]で

セルを抜ける必要があります。

前述の知られていないことも含め

非常に壊れやすいことに

留意する必要があります。

(今後のアップデートなどで

 改善の可能性はありますが)

関連記事

IF系関数とその関連機能の記事一覧