Excel(エクセル)には『配列数式』という一度に計算を実行する便利な機能が存在します。
配列数式は、表の数値を集計する際に、小計を求めずに一度に合計を算出したい時などに活用できます。
例えば「{=SUM(B2:B5*C2:C5)}」といったように、数式や関数が中括弧「{}」で囲われた数式の使い方が配列数式です。
この記事では、配列数式の使い方の手順と、配列数式を使った事例を紹介します。
Excelの『配列数式』とは？
「配列数式」は、複数のセルに対して同じ計算式を使って一度に計算を実行する機能です。
例えば、Excel2019以前のバージョンのエクセルであれば、「=SUM(B3:B6*C3:C6)」といった計算式を作ってもエラーが発生します。
しかし、配列数式を行うと、下の表のように、各行の「単価×数量」の結果を一度に最下行で合計することができ、「=(B3*C3)＋(B4*C4)＋(B5*C5)+(B3*C6)」の計算を求めることができます。
上の計算を配列数式を使わずに実行した場合は、Ｄ列に「数量×単価」を算出してから、最下行にSUM関数を挿入する必要があるので、数式を作成するのに時間がかかります。
このように配列数式を利用する最大のメリットは、作業効率UPによる時短です。
SUM関数以外にも、配列数式を利用することができます。後半の見出しで事例集を紹介しているので、実務に活用してください。
『配列数式』の使い方
それでは、実際に上記で紹介した表を使って、配列数式の使い方を解説します。
配列数式を実行するショートカットキー
配列数式を使う手順を紹介するまえに、先に配列数式を使うショートカットキーを紹介します。
配列数式を使うためには、数式を確定させる前に、「Ctrl＋Shift+Enter」キーというショートカットキーを利用する必要があります。(CSEキーの利用)
キーボードのキー配置は以下の赤枠の位置です。
「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押す、という操作が必要だということを覚えてください。
配列数式を使用する手順(その１．SUM関数)
配列数式をSUM関数で使用して、「数量×単価」をまとめて合計する手順を紹介します。
手順１．SUM関数を挿入してセル範囲を選択
SUM関数を挿入し、B列の「数量」列のセル範囲をドラッグして選択します。
=SUM(B3:B6
手順２．「*」を入力
セル範囲とセル範囲を掛け算したいので、「*」を入力します。
=SUM(B3:B6*
手順３．２つ目のセル範囲をドラッグで選択
C列の「単価」列のセル範囲をドラッグして選択します。選択したら、SUM関数を閉じるために「)」を入力します。
=SUM(B3:B6*C3:C6)
手順４．CSEキーのショートカットキーを利用
配列数式のショートカットキーを使用します。「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押します。そうすると、各行の「数量×単価」の結果が一度に計算されて、その合計が表示されます。
数式バーで数式を確認すると、配列数式を使用した数式の先頭と末尾に、中カッコ「{}」が追加されます。
{=SUM(B3:B6*C3:C6)}
また、数式バーをクリックすると、この中カッコは一時的に非表示となり触ることはできない特殊な記号です。そのため、数式を作成した後に、中カッコを追加しても配列数式は動作しません。
配列数式を使う際の重要なポイントは、必ず数式を確定させる時にCSEキーのショートカットキーを利用するということです。
配列数式を使用する手順(その２：隣接セルに結果が拡張)
上記の例では、配列数式の結果が１つのセルに出力されました。
配列数式には、隣接したセルに結果を表示させるもう一つの使い方があるので紹介します。
今度はSUM関数を使わずに、下の数式のようにセル範囲(数量列)とセル範囲(単価列)を掛け算させた数式を作成します。
=B3:B6*C3:C6
この数式を確定させる際に、「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押します。
そうすると、下のセルにも各行の「数量×単価」の結果が拡張されて表示されます。
配列数式を削除する方法
配列数式の結果が隣接したセルにも拡張して出力された場合、配列数式の結果は１セルずつ削除することはできません。
削除する場合は、数式を挿入したセルと隣接したセルすべてを選択してから、「Delete」キーを押してください。
『配列数式』を使った事例
配列数式はSUM関数や四則演算以外にも、さまざまな関数と組み合わせて利用することができます。
この見出しでは、配列数式を使った以下の３つの事例を紹介します。
ポイント
事例１．AVERAGE関数で平均値を一括で算出
事例２．LEN関数×SUM関数で文字数を合計
事例３．SUMIFS関数で複数条件(OR条件)に合う値を集計
事例４．TRANSPOSE関数で行と列を入れ替える
配列数式/事例１．AVERAGE関数で平均値を一括で算出
AVERAGE関数を配列数式を使用することもできます。
例えば、下の表には「数量」と「単価」が表示されていますが、各商品の販売高は計算されていません。この表から、各商品の販売高の平均値を直接算出したい時は、以下のAVERAGE関数を使用します。
=AVERAGE(B3:B6*C3:C6)
AVERAGE関数を確定させる際に、「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押して、配列数式を使います。
そうすると、下の図のように、各商品の販売高を算出しなくても、ダイレクトに平均販売高を算出することができます。
配列数式/事例２．LEN関数×SUM関数でまとめて文字数をカウント
LEN関数は、文字数をカウントする関数です。
LEN関数で数えた文字数を合計したい時、SUM関数とLEN関数を組み合わせた数式を配列数式で利用すると１つのセルで完結させることができます。
=SUM(LEN(B3:B6))
関数を確定させる際に、「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押して、配列数式を使います。
そうすると、下の図のように、B3セルからB6セルまでの文字数を一度に合計することができます。
配列数式/事例３．SUMIFS関数をOR条件で集計
SUMIFS関数は複数の条件に一致する値を合計する関数です。
ただ、SUMIFS関数はAND条件(なおかつ)で利用できますが、OR条件(もしくは)では利用できません。
例えば、「A製品すべての数量とB製品すべての数量を合計」といった計算を行いたい場合です。
そんな時、配列数式とSUM関数を組み合わせることで、SUMIFS関数をOR条件で合計することができます。
例えば下の表のC列が「A製品」もしくは「B製品」の数量を合計したいときは、以下の数式を挿入します。
=SUM(SUMIFS(D3:D10,C3:C10,{"A製品","B製品"}))
そうすると、A製品とB製品の数量の合計が「1,300」を算出することができます。
以下の記事で、このSUMIFS関数をOR条件で利用する方法を詳しく紹介しているので、手順や仕組みを知りたい方は参考にしてください。
配列数式/事例４．TRANSPOSE関数で行と列を入れ替える
TRANSPOSE関数(トランスポーズ関数）は、行と列のデータを入れ替えする関数です。
例えば、左下の表の行と列を入れ替えするために、以下のTRANSPOSE関数を挿入します。
=TRANSPOSE(B2:E7)
関数を確定させる際に、「Ctrl」キーと「Shift」キーを押しながら「Enter」キーを押して、配列数式を使います。
そうすると、下の図のように、左側の表の行と列が入れ替わった表を出力することができます。
『動的配列数式(スピル)』を使う方法
今回紹介した「配列数式」は、Excel2019以前のバージョンで利用する機能です。
Excel2021以降のバージョンやMicrosoft365(サブスク)を利用しているユーザーの場合は、「配列数式」が進化した『動的配列数式(スピル)』を利用しましょう。
スピルは、XLOOKUP関数やFILTER関数、SORT関数など様々な関数に使用することができます。
例えば、XLOOKUP関数をスピルで利用すると、下の図のように、H3セルに以下の数式を入力するだけで、商品番号「15」「18」「78」の値段を一度に抽出することができます。
=XLOOKUP(G3:G5,B:B,E:E)
スピルは将来のエクセル処理に革新を起こす新機能です。スピルの使い方は以下の記事で詳しく紹介しているので、興味のある方は参考にしてください。
