Excel 配列数式の利用方法とメリット・デメリット
配列数式は知名度が低いですが、Excel2007以降であれば使用可能な強力な機能です。
配列数式を適切に用いれば中間計算セルや数式を省略してシートを簡潔にすることが可能です。
強力ですがデメリットも多いため採用に当たっては、そのトレードオフを検討する必要があります。
なおExcel2019以降ではスピルというデメリットの多くを解消したより便利な機能が追加されています。
配列数式よりこちらを推奨しますしマイクロソフトもそちらに移行の方針です。
使用例とメリット
共通の数式を一つの数式で実現
例えば表に数量と価格があり、それらを掛け算した金額を出す場合、
従来の数式は数量*価格の数式を下にコピーして実現します。
この配列数式の場合、金額の範囲を選択した上で、
数量と価格を範囲で指定し（マウス操作でもキー入力でも可）
[Ctrl]+[Shift]+[Enter]を押すと{}で囲まれた特殊な数式が
範囲を選択した全セルに共通の数式が適用されます。
![[Ctrl]+[Shift]+[Enter]で確定](https://blog-tips.sekenkodqx.jp/wp-content/uploads/2020/04/image-114.png)
この数式はF6セルのみ編集のようなことが出来ません。
配列数式の範囲は全て同じ数式になります。
数式のコピー忘れや部分的なセルのみ壊してしまうことがなくなるため
安全性の高いシートになるメリットがあります。
また料率計算などで慣れない人に使いづらい絶対参照が不要になります。
ただしデメリットも多いため、検討する必要があります。
まず配列数式の範囲が変わるような列や行の追加・削除が出来ません。
また[Ctrl]+[Shift]+[Enter]を押さないと入力モードからの離脱ができないため
配列数式を知らない人には初見殺しになります。
なお、スピルであればこの欠点が解消されています。
中間集計セルの削減（単価×数量の総計金額など）
例えば単価と数量を掛け算した上での総合計金額を算出する場合は、
それぞれの行毎に掛け算した中間計算セルを作るのが一般的です。
配列数式を使えばこの販売金額列を作らずに合計を算出することが可能です。
配列数式ではC2セルに下記のような数式を記述します。
=SUM(単価のセル範囲 * 数量のセル範囲)
なお二つのセル範囲は大きさが一致している必要があります。
この状態で[Ctrl]+[Shift]+[Enter]と数式が{}で囲まれ配列数式となります。
結果は単価と数量をそれぞれ掛け算した上で合計します。
（中間計算式セルを使った結果と同じ）
これにより不要なセルを作らずに合計販売金額で出せるため
数式やシートが簡潔になりシートを簡潔にできます。
なお{}はキーボード入力しても配列数式にはなりません。
[Ctrl]+[Shift]+[Enter]で確定する必要があります。
条件に一致するものの集計
Excel2016より古い環境ではCOUNTIF関数、SUMIF関数、AVERAGEIF関数などはありますが、
MAX関数やMIN関数には条件を指定を出来るものがありません。
よってIF関数で条件指定をセルを作ってからMAX関数などで集計する必要があります。
先ほどと同様の例でフードのみの最高販売金額を求めます。
この例ではF列にIF関数を使用し、種類がフードのみの販売金額を算出し、
C2セルにF列へのMAX関数を記述しています。
これを配列数式を使用すると下記のような記述になります。
=MAX(IF(種類のセル範囲="フード",単価のセル範囲*販売数量のセル範囲,0))
こう記述した状態で[Ctrl]+[Shift]+[Enter]でセル入力を確定させます。
中間計算セルを作成したのと同様の結果がセル一つで算出されます。
もちろんMIN関数でも可能です。
なおExcel2016以降はMAXIFS関数やMINIFS関数が
追加されているため、それらを使用する方が適切です。
単一セルの引数にセル範囲を指定
単一のセルしか指定できない関数にセル範囲を対応させ
記述を簡潔にする利用法が可能です。
FIND関数に複数の検索文字列を指定する方法
1行おきに合計する方法
配列数式が必要な関数
配列数式で記述する関数があります。
区間に含まれる数値の個数を取得し度数分布表を作成(FREQUENCY関数)
最頻値（現れる頻度が最も高い数値）を取得(MODE.SNGL関数、MODE.MULT関数)
表の行・列を入れ替える(TRANSPOSE関数)
デメリット
配列数式の範囲は別の値・数式を混ぜることが不可能
安全性のトレードオフですが、販売金額の特定行のみ
別の数式を入れたり、固定を入れるといった柔軟な編集が不可能です。
配列数式の範囲に影響する列・行の追加・削除が不可能
強烈なデメリットです。これがあるためセルの構成が固定のシートにしか使えません。
知られていない
配列数式は便利な割に知られていない機能のため
[Ctrl]+[Shift]+[Enter]を押す必要があるとはわからず
シート作成者以外の人が見ると何をしているのか分からず
数式を破壊してしまう恐れがあります。
壊れやすい
配列数式を使用しているセルに対して入力モードに入って、
そこから離れると配列数式が解除されエラーとなります。
何も変更していなくても毎回、[Ctrl]+[Shift]+[Enter]で
セルを抜ける必要があります。
前述の知られていないことも含め
非常に壊れやすいことに留意する必要があります。
デメリットの多くはスピルで解消可能
「配列数式の範囲は別の値・数式を混ぜることが不可能」以外のデメリットは
新機能のスピルで解消されています。
Excel2019以降かOffice365に限定されますが、スピルの使用を推奨します。
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。