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

2020年5月17日

配列数式は知名度が低いですが、Excel2007以降であれば使用可能な強力な機能です。

配列数式を適切に用いれば中間計算セルや数式を省略して

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

強力ですがデメリットも多いため採用に当たっては

そのトレードオフを検討する必要があります。

なおExcel2019以降ではスピルという

デメリットの多くを解消したより便利な機能が追加されています。

配列数式よりこちらを推奨しますしマイクロソフトもそちらに移行の方針です。

使用例とメリット

共通の数式を一つの数式で実現

例えば表に数量と価格があり、それらを掛け算した金額を出す場合、

従来の数式は数量*価格の数式を下にコピーして実現します。

この配列数式の場合、金額の範囲を選択した上で、

数量と価格を範囲で指定し(マウス操作でもキー入力でも可)

[Ctrl]+[Shift]+[Enter]を押すと{}で囲まれた特殊な数式が

範囲を選択した全セルに共通の数式が適用されます。

金額の範囲を選択した上で数量と価格を範囲で指定
金額の範囲を選択した上で数量と価格を範囲で指定
[Ctrl]+[Shift]+[Enter]で確定
[Ctrl]+[Shift]+[Enter]で確定

この数式はF6セルのみ編集のようなことが出来ません。

配列数式の範囲は全て同じ数式になります。

数式のコピー忘れや部分的なセルのみ壊してしまうことがなくなるため

安全性の高いシートになるメリットがあります。

また料率計算などで慣れない人に使いづらい絶対参照が不要になります。

ただしデメリットも多いため、検討する必要があります。

まず配列数式の範囲が変わるような列や行の追加・削除が出来ません。

また[Ctrl]+[Shift]+[Enter]を押さないと入力モードからの離脱ができないため

配列数式を知らない人には初見殺しになります。

なお、スピルであればこの欠点が解消されています。

中間集計セルの削減(単価×数量の総計金額など)

例えば単価と数量を掛け算した上での総合計金額を算出する場合は、

それぞれの行毎に掛け算した中間計算セルを作るのが一般的です。

従来の方法。中間計算セル(販売金額: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]を押す必要があるとはわからず

シート作成者以外の人が見ると何をしているのか分からず

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

壊れやすい

配列数式を使用しているセルに対して入力モードに入って、

そこから離れると配列数式が解除されエラーとなります。

何も変更していなくても毎回、[Ctrl]+[Shift]+[Enter]で

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

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

非常に壊れやすいことに留意する必要があります。

デメリットの多くはスピルで解消可能

「配列数式の範囲は別の値・数式を混ぜることが不可能」以外のデメリットは

新機能のスピルで解消されています。

Excel2019以降かOffice365に限定されますが、スピルの使用を推奨します。

関連記事

スピル(動的配列数式)の使い方とメリット・デメリット

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