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

2019年7月31日

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

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関数が

追加されているため

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

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

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

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

利用法が可能です。

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

1行おきに合計する方法

配列数式が必要な関数

配列数式で記述する関数があります。

区間に含まれる数値の個数を取得し度数分布表を作成(FREQUENCY関数)

最頻値(現れる頻度が最も高い数値)を取得(MODE.SNGL関数、MODE.MULT関数)

表の行・列を入れ替える(TRANSPOSE関数)

デメリット

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

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

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

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

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

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

非常に壊れやすいこと

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

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

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

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

何も変更していなくても

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

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

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

非常に壊れやすいことに

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

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

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

関連記事

IF系関数の使い方・活用方法の記事一覧