Excel動的配列関数の実務｜FILTER・SORT・UNIQUE・スピルで財務分析を速くする

結論：「1セル1数式」から「1数式1範囲」へ

従来のExcelでは、1つのセルに数式を1本書き、それを下方向・右方向にコピー（フィル）して範囲全体に適用するのが基本動作でした。セルの数だけ数式のコピーが存在し、参照はコピー先ごとに少しずつずれていきます。これが「1セル1数式」の考え方です。

動的配列関数（Dynamic Array Functions）を使うと、これが「1数式1範囲」に変わります。FILTER・SORT・UNIQUE・SEQUENCEなどは配列（複数の値の集まり）を返す関数で、先頭セルに数式を1つ入力するだけで、結果が必要な分だけ自動的に隣接セルへ広がります。この「広がる」動作をスピル（Spill）と呼びます。数式は1本だけなので、コピーミスによる参照ずれが起きず、元データが増減すれば結果範囲も自動で追従します。財務モデルにおいては、抽出・並べ替え・重複除去・連番生成といった前処理をヘルパー列なしで完結できる点が実務上の価値になります。

スピルの仕組みと#記法・ゴースト

スピルした結果のうち、実際に数式が入っているのは先頭セル（上の例ではC2）だけです。C3からC7は「ゴースト」と呼ばれ、薄い青の破線枠で表示されるが、値をダブルクリックしても編集できません。C2の数式を消すか変更すると、ゴースト部分も連動して消える・変わります。

スピルした範囲全体をほかの数式から参照するときは、先頭セルの後ろに#を付ける「スピル範囲演算子」を使います。たとえば=SUM(C2#)はC2からC7まで、そのときスピルしている実際の範囲を自動的に対象にします。C7のように個別セルを直接指定すると、元データの行数が変わった際に参照範囲がずれるリスクがあるため、集計や後続の数式ではC2#のような#記法を使うのが安全です。

なお、Excelには古くからCtrl+Shift+Enterで確定する配列数式（CSE配列、{=…}と表示される）があるが、これは入力時にあらかじめ選択した範囲に固定され、自動的には広がりません。動的配列は結果に応じて範囲そのものが伸縮する点が本質的に異なります。

FILTER・SORT・UNIQUEの基本

部門別の売上・費用データで実際に検算しながら確認します。以下はA2:D7に入力された6部門分の整数設例（単位：百万円）です。

FILTER関数は=FILTER(array, include, [if_empty])という書式で、arrayのうちincludeがTRUEになる行だけを取り出します。赤字部門だけを抽出するには、D列（損益）のセルをD2:D7として=FILTER(A2:D7, D2:D7<0, "該当なし")と入力します。

手計算で検算すると、6部門のうち損益がマイナスなのは開発部（▲70）・管理部（▲60）・海外事業部（▲40）・新規事業部（▲70）の4部門で、FILTERの結果もこの4行がC2からスピルします。赤字4部門の損益を合計すると▲70▲60▲40▲70＝▲240百万円。黒字2部門（営業部70＋マーケティング部20＝90百万円）を合わせた全社合計は90▲240＝▲150百万円で、6部門の損益を単純に足し合わせた値と一致します。この行数・合計の一致を確認してから次の作業に進む習慣が、数式の入力ミスを早期に発見する助けになります。

SORT関数は=SORT(array, [sort_index], [sort_order], [by_col])で、抽出結果をそのまま並べ替えられます。=SORT(FILTER(A2:D7, D2:D7<0), 4, 1)とすれば、赤字4部門を損益の小さい順（赤字の大きい順）に並べ替えた表がスピルします。FILTERの結果をSORTでそのまま包むだけで、抽出と並べ替えを1本の数式で完結できます。

UNIQUE関数は=UNIQUE(array, [by_col], [exactly_once])で重複を除いた一覧を返します。たとえば取引明細の支店欄に「東京・大阪・東京・名古屋・大阪・福岡」という6件のデータが並んでいる場合、=UNIQUE(範囲)は「東京・大阪・名古屋・福岡」の4件だけをスピルします。6件中2件が重複だったことが、結果の行数からそのまま確認できます。

SEQUENCEとタイムライン生成（財務モデルの年次ヘッダー）

SEQUENCE関数は=SEQUENCE(rows, [columns], [start], [step])という書式で、連続した数値の配列を生成します。財務モデルでは、年次の見出し行を作る場面で使いやすいです。たとえば2026年8月時点で今期を起点に5期分の年次ヘッダーを作るなら、=SEQUENCE(1,5,2026,1)と入力すると、C2からG2に「2026・2027・2028・2029・2030」という5つの数値が横方向にスピルします。

行数を2以上にすれば縦横同時の連番表も作れ、stepをマイナスにすれば降順の年次ヘッダーになります。月次モデルで四半期ごとの見出しが欲しい場合は、SEQUENCEで生成した整数をEDATE関数などと組み合わせて日付に変換するのが一般的なやり方です。ヘルパー列に年数を手入力して埋める代わりに、モデルの前提（開始年・期間数）を変数化してSEQUENCEに渡せば、前提を1か所変えるだけで見出し行全体が追従します。

財務モデルでの使いどころ・使ってはいけない場所

向いている場面：自分（または同じチーム）が管理する内部ワークブックで、Microsoft 365など動的配列に対応した環境が前提の場合。ダッシュボードやサマリーシートで、元データの追加・削除に応じて集計範囲を自動追従させたい場合。抽出・並べ替え・重複除去のためだけに作っていたヘルパー列を削減したい場合。

避けるべき場面は主に3つあります。第一に、クライアントや社外の関係者にファイルを共有する場合。相手がExcel 2019やExcel 2016など動的配列に対応しない旧バージョンを使っていると、スピル数式が正しく表示・再計算されないことがあります。外部提出用のファイルは、動的配列の結果を値に変換（コピー＆形式を選択して貼り付け）してから送るほうが安全です。第二に、Excelのテーブル機能（Ctrl+T）の内部にスピル数式を置くこと。テーブル内ではスピル配列が正しく動作しないため、テーブルの外側のセルに数式を置く必要があります。第三に、スピルする範囲の直近に別のデータを置くこと。空白に見えても文字列（=””など）が入っていると、そこがスピルの障害物になり#SPILL!エラーの原因になります。

なお、旧来のCSE配列（{=…}）と混在させる場合、SUMやCOUNTなど一部の関数は後方互換のため単一セル入力時に暗黙的な交差（@演算子）が自動的に付くことがあります。古いブックを動的配列前提で書き換える際は、数式バーに意図しない@が付いていないかを確認するとよいでしょう。

#SPILL!エラーの原因と対処

#SPILL!エラーは、動的配列の結果を書き出すべき範囲が空でないために発生します。主な原因は次のとおりです。

• スピル範囲の途中に別のデータ（数値・文字列・空白文字列””など）が入っている
• スピル範囲の中にセル結合がある
• スピル範囲がExcelのテーブル（Ctrl+Tで作成した範囲）と重なっている
• スピル範囲がワークシートの端（列XFDや行1048576）を超えてしまう
• 計算のたびに結果サイズが変わる揮発性の数式で、範囲が安定しない

対処は、セル左上に表示される警告アイコンから「障害となっているセルを選択」を選ぶと、原因となっているセルへ即座にジャンプできます。そのセルの内容を削除するか、別の場所に移動すればエラーは解消します。原因が結合セルやテーブルの場合は、結合を解除する、またはスピル数式をテーブルの外側に置き直します。

面接での答え方（30秒回答例）

Q：Excelの動的配列関数について説明してください。
「FILTER・SORT・UNIQUE・SEQUENCEなどの動的配列関数は、条件抽出や並べ替えの結果を配列として返し、先頭セルの数式1本だけで必要な範囲へ自動的に展開される点が特徴です。この展開をスピルと呼び、元データが増減しても結果範囲が自動で追従するため、財務モデルではヘルパー列や数式コピーを減らせます。ただし社外共有時は旧バージョンでの互換性に注意し、テーブル内部や結合セルの近くには置かないようにしています。」

よくある質問（FAQ）

Q. 動的配列関数はどのExcelバージョンで使えますか。
A. Microsoft 365のExcel、およびExcel 2021以降で利用できます（2026-08-01時点）。Excel 2019やExcel 2016など、それより前の永続版（買い切り版）では動的配列関数自体が存在しないか、正しくスピルしない場合があるため、バージョンが混在する環境では事前に確認したほうがよいでしょう。

Q. スピルした結果を別の数式から参照するにはどうすればよいですか。
A. 先頭セルの後ろに#を付けて範囲全体を参照します（例：=SUM(C2#)）。C3やC7のような個別セルを直接指定すると、元データの行数が変わったときに参照範囲がずれてしまうため、集計や後続の数式では#記法（スピル範囲演算子）を使うのが安全です。

まとめ

動的配列関数は、「1セル1数式をコピーする」発想から「1数式で1範囲を返す」発想への転換です。FILTER・SORT・UNIQUEを組み合わせれば、抽出・並べ替え・重複除去という前処理をヘルパー列なしで1本の数式にまとめられ、SEQUENCEは財務モデルの年次・期間ヘッダーを前提条件から自動生成できます。整数設例では、6部門中4部門・合計▲240百万円という結果を手計算で検算できることを確認しました。効果が大きい一方で、社外共有時の旧バージョン互換、テーブルや結合セルとの干渉による#SPILL!エラーには注意が必要です。

出典・参考（2026-08-01確認）

• Microsoft Support「FILTER function」 https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Microsoft Support「SORT function」 https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c
• Microsoft Support「UNIQUE function」 https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
• Microsoft Support「SEQUENCE function」 https://support.microsoft.com/en-us/excel/functions/sequence-function
• Microsoft Support「Dynamic array formulas and spilled array behavior」 https://support.microsoft.com/en-us/excel/dynamic-array-formulas-and-spilled-array-behavior
• Microsoft Support「How to correct a #SPILL! error」 https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023

※本記事は教育目的の一般的な解説であり、法務・税務・投資助言ではありません。設例は理解のための仮設例です。