UNIQUE 関数や FILTER 関数で一覧を作り、結果を =SUM(E2:E5) のような固定範囲で集計していませんか？ 数式の中で A2# や D2# を見かけて、「この # は何？」と手が止まった人もいるはずです。
固定の範囲は、データが増えた月にだけ静かにずれます。商品が1つ増えても、合計は前のまま。範囲の直し忘れに気づかず、漏れた数字で報告してしまうのは怖いですよね。
この記事では、Excel のスピル範囲演算子「#」の意味と使い方を、同じサンプルの数値で確かめていきます。A2# と A2:A20 の違いから、集計・ドロップダウンでの使い方、#REF! の原因まで順に見ていきましょう。
対応バージョン: スピル範囲演算子は、スピル（動的配列）に対応した Excel で使う記号です。Microsoft の公式ページの適用対象に挙がっているのは、Microsoft 365 と Microsoft 365 for Mac です。Excel 2019 以前は動的配列に対応していないため使えません。
Excel 2021/2024 や Excel for the web も動的配列に対応した版ですが、# のページには記載がありません。入力規則・名前の定義・グラフでの Web 版の挙動は、本文で「確認できた範囲」を分けて書いています。
Excelのスピル範囲演算子「A2#」とは？A2:A20との違い
結論：A2# は「A2 からスピルした結果ぜんぶ」を指す
A2# は、「A2 から広がった（スピルした）結果の範囲全体」を指す参照です。セル番地の後ろに付けるこの # を、スピル範囲演算子と呼びます。Microsoft の公式ページのタイトルでは「スピル済み範囲演算子」と表記されています。
スピルとは、1つの数式の結果が隣のセルへ自動で広がる仕組みのこと。UNIQUE・FILTER・SORT・SEQUENCE などの関数が、この形で結果を返します。スピルの基本や各関数の使い方は、ExcelのUNIQUE・FILTER・SORT・SEQUENCE関数入門で解説しています。
Microsoft の公式ページの例がわかりやすいので、まずはそれを見てみましょう。
A2 に入力：=SEQUENCE(10) → A2:A11 に 1〜10 がスピル
別のセル：=SUM(A2#) → =SUM(A2:A11) と同じ。結果は 55A2 の数式を =SEQUENCE(20) に変えると、結果は A2:A21 まで広がります。このとき =SUM(A2#) は書き換えなくても A2:A21 を合計し、結果は 210 に変わります。範囲の大きさを # が追いかけてくれる、というわけです。
書き方（構文）と各部分の意味
書き方はシンプルで、「スピルの起点のセル番地」の直後に # を付けるだけ。
| 部分 | 意味 | 例 |
|---|---|---|
| 起点のセル | スピルする数式を入れた左上のセル | D2 |
# | 起点から広がった範囲全体を指す | D2# |
| 使う場所 | 関数の引数や、別の数式の中 | =SUM(E2#) |
起点とは、スピルする数式を実際に入力した左上のセルです。スピル範囲の中で数式を編集できるのも、この左上のセルだけ。ほかのセルは結果が表示されているだけで、数式バーの数式はグレーで表示されます。
固定範囲と A2# の before-after（10行→12行）
ここからは、すべて次のサンプルで説明します。A列に商品、B列に金額が入った売上の明細です。最初は2〜11行目の10行で、あとから12・13行目の2行を追加します。
| 行 | A列：商品 | B列：金額 |
|---|---|---|
| 2 | りんご | 300 |
| 3 | みかん | 450 |
| 4 | りんご | 600 |
| 5 | バナナ | 200 |
| 6 | みかん | 150 |
| 7 | ぶどう | 800 |
| 8 | りんご | 300 |
| 9 | バナナ | 400 |
| 10 | みかん | 300 |
| 11 | ぶどう | 500 |
| 12 | メロン | 1,000（追加） |
| 13 | りんご | 250（追加） |
D2 と E2 には、次の2つの数式を入れておきましょう。D2 で商品の一覧を作り、E2 で商品ごとの合計を出す形です。
D2：=UNIQUE(FILTER(A2:A100,A2:A100<>"")) → 商品の一覧（重複なし）
E2：=SUMIFS(B2:B100,A2:A100,D2#) → 商品ごとの合計FILTER で空白の行を除いてから、UNIQUE で重複を消しています。明細の側は A2:A100 のように余裕を持たせた範囲で受け、結果の側は # で受けるのがポイント。スピルした結果は次のとおりです。
| 行 | D列（10行のとき） | E列 | D列（12行のとき） | E列 |
|---|---|---|---|---|
| 2 | りんご | 1,200 | りんご | 1,450 |
| 3 | みかん | 900 | みかん | 900 |
| 4 | バナナ | 600 | バナナ | 600 |
| 5 | ぶどう | 1,300 | ぶどう | 1,300 |
| 6 | （空白） | （空白） | メロン | 1,000 |
商品は4種類から5種類に増え、スピル範囲も1行伸びました。この結果を、固定範囲と # でそれぞれ集計して比べてみます。
| 数式 | 10行のとき | 12行のとき | どうなったか |
|---|---|---|---|
=SUM(E2:E5) | 4,000 | 4,250 | メロンの 1,000 が漏れる |
=SUM(E2:E20) | 4,000 | 5,250 | 合計は合う |
=ROWS(D2:D20) | 19 | 19 | 空白の行まで数える |
=SUM(E2#) | 4,000 | 5,250 | 自動で追従する |
=ROWS(D2#) | 4 | 5 | 自動で追従する |
範囲をぴったり決め打ちすると、増えた分が漏れます。かといって大きめに取ると、今度は件数を数える数式に空白の行が混ざってしまう。E2#・D2# で受ければ、この例ではどちらの問題も起きていません。
# と @ の違い（2行比較）
数式でよく見かける記号に @ もあります。役割はほぼ逆向きなので、表で並べておきましょう。
| 記号 | 役割 | 例 |
|---|---|---|
# | スピルした範囲全体を指す（広げて受け取る） | =SUM(E2#) |
@ | 複数の値を1つに絞る（暗黙の交差演算子） | =@A2:A11 |
「逆向き」というのはこの記事での整理で、Microsoft の公式ページが両者を対比しているわけではありません。@ は、旧来の Excel が黙って行っていた絞り込みを明示する記号です。詳しくはExcel数式の記号の意味一覧｜$ & — @ # { }早見表を見てみてください。
スピル範囲演算子の基本パターン｜SUM・ROWS・INDEX・XLOOKUP
ここからは、前の章のサンプル（D2 に商品の一覧、E2 に商品ごとの合計）を使います。どの数式も、明細が12行に増えたときは書き換えなしで結果が変わりますよ。
SUM と ROWS で合計・件数・平均を取る
合計は SUM、件数は ROWS で取ります。ROWS は範囲の行数を返す関数です。D2# を渡せば空白の行が混ざらないので、そのまま品目数として使えます。
=SUM(E2#) → 4,000（12行のとき 5,250）
=ROWS(D2#) → 4（12行のとき 5）
=SUM(E2#)/ROWS(D2#) → 1,000（12行のとき 1,050）3つ目は「1品目あたりの平均売上」です。12行のときは 5,250 ÷ 5 で 1,050 になります。
# を使った計算は、それ自体がスピルすることも覚えておくと便利。たとえば F2 に =E2#/SUM(E2#) と入れると、F2:F5 に商品ごとの構成比が並びます。表示形式をパーセントにすると、10行のときは次のとおりです。
- りんご：1,200 ÷ 4,000 = 30.0%
- みかん：900 ÷ 4,000 = 22.5%
- バナナ：600 ÷ 4,000 = 15.0%
- ぶどう：1,300 ÷ 4,000 = 32.5%
明細が12行になれば、構成比の列も F2:F6 の5行に伸びます。
INDEX(D2#,n) で n 番目を取り出す
INDEX 関数に D2# を渡すと、一覧の n 番目を取り出せます。ROWS と組み合わせれば「最後の項目」も取れるので、追加された品目の確認にぴったり。
=INDEX(D2#,3) → バナナ
=INDEX(D2#,ROWS(D2#)) → ぶどう（12行のとき メロン）
=INDEX(E2#,ROWS(E2#)) → 1,300（12行のとき 1,000）一覧の件数を超える番号を指定すると、INDEX 関数のエラー（#REF!）になります。10行のときに =INDEX(D2#,5) とすると、この状態です。
XLOOKUP の検索範囲に A2# を使う
G2 に商品名を入れ、その合計を H2 に出してみましょう。検索範囲と戻り範囲の両方を # で指定するのがコツです。
H2：=XLOOKUP(G2,D2#,E2#,"該当なし")G2 が「バナナ」なら、結果は 600 です。G2 が「メロン」のとき、10行の時点では「該当なし」、12行に増えたあとは 1,000 が返ります。
検索範囲と戻り範囲を同じ行数の # でそろえておけば、範囲の行数がずれる心配もありませんね。XLOOKUP が使えない版の症状は、ExcelのXLOOKUP・FILTERが使えない・#NAME?の原因と対処で確認できます。
並べ替えに使うなら、=SORTBY(D2#,E2#,-1) で合計の大きい順に商品が並びます。10行のときは「ぶどう・りんご・みかん・バナナ」。12行になると「りんご・ぶどう・メロン・みかん・バナナ」に変わります。
引数の詳しい指定方法はExcelのSORTBY関数の使い方｜複数条件・別列基準を参考にしてください。上位n件だけ取り出したいときは、ExcelのTAKE関数の使い方｜先頭/末尾の行・列を抽出も役立ちます。
UNIQUE の結果を SUMIFS の条件にする
サンプルの E2 に入れた =SUMIFS(B2:B100,A2:A100,D2#) が、このパターンです。条件に D2# を渡すと、一覧の商品ごとに合計が計算され、結果も一覧と同じ行数でスピルします。
検算してみましょう。10行のとき、りんごは2・4・8行目の 300＋600＋300 で 1,200。12行になると、13行目の 250 が加わって 1,450。件数も同じ形で取れます。
I2：=COUNTIFS(A2:A100,D2#) → 3, 3, 2, 2（12行のとき 4, 3, 2, 2, 1）数式が長くなってきたら、D2# を変数に入れて整理する手もあります。書き方はExcelのLET関数の使い方｜変数定義で数式を短く整理する方法が参考になりますよ。
入力規則・名前の定義・グラフで使う｜# の置き場所早見表
セルの数式以外の場所でも、# を使った参照が紹介されています。ただし、Microsoft の公式ページで手順まで確認できたものは多くありません。この章では、確認できた範囲とそうでない部分を分けて書いていきます。
ドロップダウン（入力規則）に =$D# を指定する
商品の一覧をそのままドロップダウンにする方法です。手順は次のとおり。
- ドロップダウンを付けたいセル（例：G2）を選びます。
- 「データ」タブの「データの入力規則」を開きます。
- 「入力値の種類」で「リスト」を選びます。
- 「元の値」に
=$D$2#と入力して確定します。
一覧が増えれば、ドロップダウンの選択肢も増えるとされています。G2 に付ければ、前の章の XLOOKUP と組み合わせて「選んだ商品の合計を出す」表が作れますね。
この使い方は、解説サイトで紹介されているものです。Microsoft 公式のドロップダウン作成ページは「表にしておくと自動で更新される」という案内で、# には触れていません。
Excel for the web で同じ指定が通るかは、確認できた範囲では公式の記述がありませんでした。可変ドロップダウンの作り方全体は、ExcelのUNIQUE・FILTER・SORT・SEQUENCE関数入門でまとめています。
名前の定義を経由してグラフに渡す
グラフの系列に E2# を直接書くと、受け付けられない場合があるとされています。そこで紹介されているのが、名前の定義を間にはさむ方法。
- 「数式」タブの「名前の定義」を開きます。
- 名前を「品目」、参照範囲を
=Sheet1!$D$2#にします。 - 同じように、名前「品目合計」の参照範囲を
=Sheet1!$E$2#にします。 - グラフの系列の値に、
=ブック名.xlsx!品目合計の形で名前を指定します。
名前の参照範囲に # を書けること自体も、解説サイトでの紹介にとどまり、公式の記述は確認できていません。Excel for the web で名前の定義やグラフの系列に # が使えるかも、未確認です。 実際に使う前に、手元の Excel で一度試してみてください。
他シート・別ブックから参照するときの書き方
別のシートや別のブックからは、シート名やブック名を前に付けて書きます。
別シート：=SUM(集計!E2#)
別ブック：=SUM([売上.xlsx]集計!$E$2#)別シートからの参照について、Microsoft の公式ページには記述がありません。 一方、別ブックについては公式の記述があります。動的配列の別ブック参照は、両方のブックが開いているときだけサポートされる仕様です。
元のブックを閉じると、# を使った参照は #REF! を返します。参照先のブックを開けば元に戻るので、慌てなくて大丈夫ですよ。
使える場所の早見表（○・△・？）
ここまでの内容を1枚にまとめます。○は公式の例や本記事のサンプルで説明した使い方、△は条件つきか裏付けが弱いもの、？は未確認です。
| 置き場所 | 書き方の例 | 目安 | 根拠・注意 |
|---|---|---|---|
| 関数の引数（SUM など） | =SUM(E2#) | ○ | 公式ページに例あり |
| 関数の条件・検索範囲 | =XLOOKUP(G2,D2#,E2#) | ○ | 本記事のサンプルで説明 |
| 入力規則のリスト | =$D$2# | △ | 解説サイトの紹介のみ。Web版は未確認 |
| 名前の定義 | =Sheet1!$D$2# | △ | 解説サイトの紹介のみ。Web版は未確認 |
| グラフの系列 | 名前を経由して指定 | △ | 直接は受け付けない場合あり |
| 他シート | =SUM(集計!E2#) | △ | 公式ページに記述なし |
| 別ブック | [売上.xlsx]集計!$E$2# | △ | 両方開いているときのみ。閉じると #REF! |
| 条件付き書式 | — | ？ | 未確認 |
△と？の行は、後から確認できしだい更新していきます。まずは○の使い方から試すのがおすすめです。
A2# が #REF! になる・使えないときの確認順
D2# と書いたのに #REF! エラーが出る。そんなときは、次の表の上から順に確かめてみましょう。
原因3パターン（症状→確認→対処）
| 原因 | 確認すること | 対処 |
|---|---|---|
| ① 起点のセルがスピルしていない | 起点を選んだとき、結果の範囲に枠線が出るか | スピルする数式のセルを起点にする |
| ② 起点以外のセルに # を付けた | D3# のように途中のセルを指定していないか | 左上の起点（D2）に # を付け直す |
| ③ 閉じた別ブックを参照している | 参照先のブックが開いているか | 参照先のブックを開く |
①は、起点に値や通常の数式しか入っていないケースです。公式ページに記述はないものの、スピルしていないセルに # を付けると #REF! になるとされています。 起点の数式を消したり、別の数式で上書きしたりしたあとに起こりがちですね。
②についても、確認できた範囲では公式の記述は見つかりませんでした。 ただ、# はスピルの起点の後ろに付ける記号です。数式を編集できるのも左上のセルだけなので、まずは起点のセル番地になっているかを見直しましょう。
③は、Microsoft の公式ページに既知の制限として載っています。スピル範囲演算子は、閉じたブックへの参照に対応していません。参照先のブックを開けば解消します。
テーブル内にはスピルできない制約
Microsoft の公式ページには、スピルする配列数式は Excel のテーブル内では使えないと明記されています。テーブルの中に UNIQUE などを入れると、スピルできずに #SPILL! エラーになるのはこのためです。
対処は、数式をテーブルの外の通常のセルに置くこと。このサンプルでも、D2 と E2 はテーブルにしていない普通のセルに入れています。
起点の数式が #SPILL! になっていると、その結果を参照する # の数式も正しい値を返せません。#SPILL! の原因と直し方は、ExcelのUNIQUE・FILTER・SORT・SEQUENCE関数入門のエラーの章で確認してください。
Excel 2019 以前では # が使えない
Excel 2019 以前は動的配列に対応していないため、# を使った数式はそもそも使えません。#NAME? などの版による症状は、ExcelのXLOOKUP・FILTERが使えない・#NAME?の原因と対処にまとめています。
公式ページによると、動的配列の数式は古い Excel では従来の配列数式（CSE 数式）として表示されます。範囲のサイズは変えられず、スピルの枠線も出ません。
ただ、# の部分がどう表示されるかは公式ページにも書かれていない点に注意。 古い版の人とファイルを共有するときは、事前に開いて確かめてもらうと安心です。
Googleスプレッドシートではどう書く？
社内で Excel と Googleスプレッドシートを併用している人も多いはず。同じ考え方を Sheets でどう書くか、確認できた範囲で整理しておきます。
# に相当する演算子は確認できていない
Google の公式ヘルプを調べた範囲では、Excel の # に相当する演算子の記述は見つかりませんでした。 そのため、D2# と同じ書き方は Sheets では通らない前提で考えておくのが安全です。
代わりに使われているのが、範囲の終わりの行を書かない指定。
=SUM(E2:E) → E2 から下の列全体を合計
=COUNTA(D2:D) → D2 から下の、空白でないセルの数E2:E のように終わりの行を省くと、下にデータが増えても範囲に含まれます（この指定の公式ヘルプは確認できていません）。Sheets の UNIQUE や SORT も結果が下へ広がるので、この形で受ければ件数の増減に追従できます。
ARRAYFORMULA で結果を広げる
Sheets で数式の結果を範囲全体に広げるなら、ARRAYFORMULA 関数の出番。書き方は =ARRAYFORMULA(配列数式) です。数式の入力中に Ctrl+Shift+Enter を押すと、ARRAYFORMULA が自動で付きます。
E2：=ARRAYFORMULA(IF(D2:D="","",SUMIF(A2:A,D2:D,B2:B)))この数式は、D列の商品ごとに合計を出し、空白の行は空欄のままにします。結果が広がる先のセルに値が入っていると展開できない点は、Excel のスピルと同じですね。
詳しい使い方はスプレッドシートのARRAYFORMULA関数の使い方｜範囲に一括適用で解説しています。UNIQUE や SORT で増減する一覧を作る方法は、スプレッドシートのUNIQUE・SEQUENCE・SORT関数で動的リストを作る方法も見てみてください。
まとめ｜スピル範囲演算子「A2#」の使いどころ
Excel のスピル範囲演算子「#」は、スピルした結果の範囲全体を指す記号です。この記事のポイントを振り返っておきましょう。
A2#は「A2 から広がった結果ぜんぶ」を指す。件数が増減しても参照が追いかける- 固定範囲は、ぴったりだと漏れ、大きめだと件数系の数式がずれる
- SUM・ROWS・INDEX・XLOOKUP・SUMIFS の範囲や条件に、そのまま渡せる
- 入力規則・名前の定義・グラフは紹介例があるものの、Web 版での可否は未確認
- #REF! が出たら、起点・# の位置・別ブックの開閉の順に確かめる
よくある質問：A1# の意味は？
「A1 からスピルした結果の範囲全体」という意味です。たとえば A1 に =UNIQUE(...) などのスピルする数式があり、結果が A1:A5 に広がっているとします。このとき A1# は A1:A5 を指します。結果が A1:A8 に増えれば、A1# が指す範囲も A1:A8 に。
よくある質問：グラフの範囲に直接使える？
グラフの系列に # を直接書くと受け付けない場合があり、名前の定義を経由する方法が紹介されています。公式の記述や Web 版での挙動は確認できていません。手元の Excel で試し、うまくいかなければ名前の定義を経由してみてください。
よくある質問：Excel 2019 で使える？
使えません。Excel 2019 以前は動的配列に対応していないので、# を含む数式は正しく計算できません。Microsoft 365 などの動的配列に対応した Excel で使いましょう。
まずはサンプルの D2・E2 を作って、=SUM(E2:E5) と =SUM(E2#) を並べてみてください。明細を1行足すだけで、# の便利さを実感できるはずですよ。
