Aさん ワークシートによってはデータをなるべく増やさずに数式で計算したい場合があります・・・。
こんな場合、何か良い方法はありますか？
森田 その場合、「配列数式」を使うことで解決できる可能性があります。
では、配列数式について解説していきますね。
はじめに
この記事は数式の概要を把握していることが前提です。
数式の概要の詳細は以下の記事をご参照ください。
Excelの「数式」はどんな機能ですか？ 「数式」と聞くと数学のイメージしかないんですが・・・。 …
Excelの「配列数式」とは
配列数式は、配列（複数データの集合体）を用いた数式のことです。
配列数式の特徴は数式が中カッコ（{}）で囲われていることです
配列は大きく3パターンあります。
- 行のみ
- 列のみ
- 行×列
配列数式では、計算対象となる配列の組み合わせにより、戻り値（計算結果）となる配列のサイズが決まります。
なお、配列同士以外にも、配列に対し単一データを計算させることもできるため、配列数式の組み合わせは計9パターンあります。
| 組み合わせパターン | 計算対象① | 計算対象② | 戻り値 |
|---|---|---|---|
| 1 | 配列（行のみ） | 単一データ | 配列（行のみ） ※計算対象①と同じサイズ |
| 2 | 配列（行のみ） | 配列（行のみ） ※計算対象①と同じサイズ |
配列（行のみ） ※計算対象①と同じサイズ |
| 3 | 配列（行のみ） | 配列（列のみ） | 配列（行×列） ※計算対象①×②のサイズ |
| 4 | 配列（行のみ） | 配列（行×列） ※計算対象①と同じ行数 |
配列（行×列） ※計算対象②と同じサイズ |
| 5 | 配列（列のみ） | 単一データ | 配列（列のみ） ※計算対象①と同じサイズ |
| 6 | 配列（列のみ） | 配列（列のみ） ※計算対象①と同じサイズ |
配列（列のみ） ※計算対象①と同じサイズ |
| 7 | 配列（列のみ） | 配列（行×列） ※計算対象①と同じ列数 |
配列（行×列） ※計算対象②と同じサイズ |
| 8 | 配列（行×列） | 単一データ | 配列（行×列） ※計算対象①と同じサイズ |
| 9 | 配列（行×列） | 配列（行×列） ※計算対象①と同じサイズ |
配列（行×列） ※計算対象①と同じサイズ |
パターン2・6・9は同じ配列サイズ同士で計算しない場合、不足する要素部分はエラー値「#N/A」が表示。
パターン4・7は同じ配列の行数/列数同士で計算しない場合、不足する要素部分はエラー値「#N/A」が表示。
この9パターンをワークシート上で確認すると、次のイメージとなります（すべて計算対象①②の加算）。
通常の数式と概念が異なり、最初は戸惑うかもしれませんが、少しずつイメージを湧かせていきましょう。
【参考】配列定数とは
配列はセル範囲を指定することが一般的ですが、定数として直接数式に入力して指定することも可能です。
これを「配列定数」と言います。
配列定数を使うことで、仮想的な表を数式内で表すことが可能です。
配列定数の部分は中カッコ（{}）で配列部分を示すとともに、行はセミコロン（;）、列はコンマ（,）で表現します。
実際にExcel上で配列の3パターン（行のみ、列のみ、行×列）を表現した結果が以下です。
なお、こちらは配列定数で指定した表形式が分かりやすいようにワークシート上で表示していますが、Excel2021以降またはMicrosoft365で使用可能な「スピル」を用いています。
これより前のバージョンの場合、配列定数は基本的に関数の引数として使いますが、実務であえて配列定数を使うことはほぼありません。
どちらかと言うと、数式の検証等で配列定数の形式で表示された場合に混乱しないよう、頭の片隅に置いておくと良いでしょう。
ちなみに、配列定数は数値、文字列、論理値(TRUE・FALSE)、エラー値を指定することが可能です。
それ以外のセル参照や別の配列、数式、各種記号（配列定数に使うもの以外）は使用できません。
配列数式の使い方2選
配列数式は、主に次の2種類の使い方があります。
【使い方①】複数セルへ同じ数式をまとめてセットする
配列数式の使い方の1つ目として、複数セルへ同じ数式をまとめてセットすることが可能です。
冒頭で解説した配列同士の計算がそのままこの使い方に該当します。
一例として、九九の計算を行うケースで解説します。
9行の配列×9列の配列を乗算し、戻り値となる配列の要素（計81セル）へ同じ数式を一括でセットしたものが以下です。
先述の配列数式の組み合わせパターン3に該当。
結果、各要素は同じ数式がセットされていますが、それぞれ適切な九九の計算結果が表示されました。
この使い方の場合、通常の数式と比較すると、配列数式は次のようなメリットがあります。
- ベースとなる数式のコピペ作業が不要になる
- （コピペが不要になるため）ベースとなる数式の参照形式（絶対参照/相対参照/複合参照）の設定も不要になる
※ただし、配列数式自体をコピペで使い回す場合は参照形式の設定が必要なケースあり。
【使い方②】1つの数式内で複数の計算/処理をまとめて行う
配列数式の使い方の2つ目として、1つの数式内で複数の計算/処理をまとめて行うことが可能です。
こちらは、関数と配列数式を併用することで、配列数式の計算結果を関数で利用できます。
一例としてSUMと配列数式の併用したものが以下です。
先述の配列数式の組み合わせパターン2に該当。
本来は「単価」列×「数量」列を計算した（計算列）をワークシート上に用意した上で、その列をSUMの計算対象にすることが一般的ですが、上記のように配列数式を併用することで計算列を省略することができました。
つまり、1つの数式の中で計算列と同様の計算ができたと言えます。
また、別の例として、SUMとIFと配列数式を組み合わせることで、SUMIFのように「商品別の金額を合計する」といったことも可能です。
先述の配列数式の組み合わせパターン2に該当。
こちらは、IFによってD列（商品名）が「いちご」と等しいか行ごとに判定し、等しい（TRUE）行のG列（金額）のみがSUMの計算対象になります。
このように、本来は単一セルが対象のIF等の関数も、配列ならまとめて複数セルを対象に計算/処理を行うことが可能です。
応用すると、通常の関数では実現が難しい計算/処理を行えます。
配列数式の挿入手順
配列数式の挿入手順は以下の通りです。
- 数式を挿入する単一セルまたはセル範囲を選択
- 任意の数式を入力
- 「Ctrl」+「Shift」+「Enter」キーで確定
使い方①の場合、手順①は戻り値となる配列のサイズを予め指定する必要あり。
※指定したセル範囲が戻り値の配列サイズより大きい場合、超過した要素部分はエラー値「#N/A」が表示。
※指定したセル範囲が戻り値の配列サイズより小さい場合、指定したセル範囲に該当する要素の計算結果のみ表示（非表示の要素が発生する）。
手順③が必要なことから、各キーの頭文字をとり、配列数式のことを「CSE数式」と呼ぶ場合あり。
配列数式の注意事項4選
先述の通り、配列数式は便利な側面もありますが、取り扱う際は注意も必要です。
注意事項を大別して4つにまとめましたので、順番に解説します。
【注意事項①】配列数式を使いこなせるユーザーは少ない
配列数式は一般的な数式ではなく、使いこなせるユーザーも多くはありません。
よって、自分以外のユーザーが触るExcelブックでは必要最小限の使用に留めると良いでしょう。
【注意事項②】配列数式の修正時もCSEが必要
配列数式を修正する際もCSE（「Ctrl」+「Shift」+「Enter」キーで確定）が必要です。
Excel2021以降またはMicrosoft365の場合、CSEなしでも問題なし（スピルとして表示される）。
CSEがない場合、中カッコ（{}）が消えてしまい、正しく計算されない恐れがあるため、ご注意ください。
なお、配列数式は戻り値の一部を削除することはできず、次のようなエラーメッセージが表示されます。
配列の一部を変更することはできません。
よって、配列数式の戻り値のサイズを後から変えたい場合、一旦すべてのセルの配列数式を削除し、再度任意のサイズのセル範囲を指定の上、配列数式をセットし直しましょう。
【注意事項③】複数条件を設定時、ANDやORが使えない
配列数式でIF等を活用する場合、関数のANDやORを使うことができません。
AND条件やOR条件にする場合は、それぞれ次のルールで記述しましょう。
- AND条件：(条件1)*(条件2)
- OR条件：(条件1)+(条件2)
条件部分はカッコ()で囲む。
条件は3種類以上も可能（連結する「*」や「+」を増やす）。
実際にSUMとIFと配列数式を組み合わせ、2種類の条件で合計を集計したものが以下です。
同じく複数条件で集計できるSUMIFSやCOUNTIFS等はAND条件が基本です。
OR条件の場合は「=SUMIFS(G2:G22,D2:D22,"バナナ")+SUMIFS(G2:G22,D2:D22,"いちご")」のように複数の数式を加算する必要があるため、配列数式の方がシンプルになるケースもあります。
【注意事項④】Web版のExcelは配列数式のサポートなし
配列数式はWeb版のExcelではサポートされていません。
具体的には、配列数式の挿入・編集・再計算が不可となります。
Web版のExcelでは配列数式の計算結果の表示のみが可能となります。
配列数式をセットする際は、デスクトップアプリケーションのExcelから行うようにしましょう。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
サンプルファイルには上記解説の元データも含まれる。
九九表の計算を配列数式でセットしてみましょう。
上記と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
配列数式の利用頻度は高くないですが、次のようなメリットがあります。
- 最小限のデータで計算が可能
- 通常よりも簡潔な数式に表現できる場合あり
- 通常の関数では実現できない計算/処理が可能
先述の通り注意事項も多いため、使いどころは考慮しないといけませんが、必要な場合は最小範囲に絞って実務へ活用していただければと思います。
なお、数式や関数を拙著で体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 配列数式はなかなか理解が難しいため、私も実務で多用することはしていません。
ただし、Excel2021以降またはMicrosoft365で使用可能な「スピル」を活用するにあたり、配列数式を理解しておくとスムーズに学習できます。
スピルを活用していきたい方は、ぜひ頑張って配列数式を理解していきましょう。
なお、スピルに関しては別記事にまとめていく予定です。
