【Excel】スピルの基本:1つの数式から結果が広がる仕組みと#参照

Excelのスピルとは、一つの数式が返した複数の結果を、周囲のセルへ自動で表示する動作です。たとえば商品一覧から重複のない品名を取り出すとき、数式を下へ何行もコピーせず、先頭の一セルに入力できます。覚えたいのは「入力するセル」「結果が広がる場所」「数式が読む元データ」の三つを分けることです。

この記事ではMicrosoft 365、Excel 2024、Excel 2021などUNIQUE関数に対応する環境で、五件のデータを三件の一覧にする例から始めます。枠の操作ではなく、結果が伸び縮みする仕組みと、参照やエラーの基本を扱います。

1. 数式一つで複数の結果を表示する仕組み

通常の足し算は一つの数値を返しますが、一覧を処理する数式は複数行・複数列の値を返すことがあります。このまとまりを配列と呼び、動的配列数式では必要な大きさに応じて結果の表示範囲が変わります。その表示の広がりがスピルです。

古いExcelにも複数セルで結果を扱う配列数式はありました。違いは「以前は複数の結果が一切出せなかった」ということではなく、出力範囲をあらかじめ指定する旧方式と、数式の結果に合わせて範囲が決まる方式の違いです。現在のスピルの練習では、通常のEnterで数式を確定します。

一つの数式が複数セルを使うので、すぐ下や右に別の集計表を詰め込む配置は避けます。今日の結果が三行でも、明日は四行になるかもしれません。元データの列と出力の列を離し、伸びる方向に空きを残すと管理しやすくなります。

2. UNIQUEで五件から三件を取り出す

A1に「商品」、A2からA6へ順に「ノート」「ペン」「ノート」「付箋」「ペン」を入力します。C列の下方に別データがないことを確認し、C1に「商品一覧」、C2に次の数式を入力してください。

=UNIQUE(A2:A6)

C2からC4にノート・ペン・付箋が表示されます。数式を入力したのはC2だけです。A列の五件を削除したり書き換えたりしたわけではなく、元の一覧を残して別の場所へ重複のない結果を出しています。

次にA6の「ペン」を「封筒」へ変えます。入力範囲内の種類が一つ増えるため、C5まで四件に広がることを確認します。A6をペンへ戻すと、C4までの三件に戻ります。この練習では元データが五行のままでも、結果の行数は変わります。元の行数と結果の行数は同じとは限りません。C5に手入力の補足を書き込まず、結果が伸びる場所として空けておきます。

UNIQUEの引数を省略した基本形は、重複する品名を一件ずつ残します。「一度だけ登場する品名だけを残す」という指定とは異なります。この例では二回出てくるノートとペンも結果に一件ずつ含まれる点を確認してください。

3. 編集するのは左上、参照は必要に応じて#を付ける

C3を選ぶと数式バーに元の数式が薄く表示されることがありますが、C3だけを独立して変更することはできません。数式の編集は左上のC2で行います。結果の品名を訂正するなら、基本的にはA列の該当データを訂正します。

結果全体を別の数式から参照するときは、起点のセルに#を付けます。たとえばE2に次を入力すると、C2からの結果全体を並べ替えて表示します。E列にも十分な空きを用意してください。

=SORT(C2#)

C2だけなら先頭の一セル、C2#なら現在のスピル範囲全体です。C2の結果が四件に増えれば、E2側も四件を対象にします。日本語の並び順は環境などで確認し、練習では順序の暗記より、件数と品名が一致することを見ます。

#は絶対参照の$に代わる記号ではありません。$はコピーした際に参照位置を固定するための指定です。起点を固定してコピーしたいなら$C$2#のように併用します。役割が別だと理解すれば、スピルを使った途端に$が不要になるという誤解を避けられます。

4. 入力範囲の追加と出力の伸縮を区別する

先ほどの数式が読むのはA2:A6です。A7に新商品を書き足しただけでは、その行は数式の指定範囲に入りません。C2の式を=UNIQUE(A2:A7)に修正する必要があります。「スピルだからどこへ追加しても自動で取り込む」わけではありません。

追加が多い一覧では、元データをExcelテーブルにする方法があります。見出しを含むA1:A6を選んで[挿入]→[テーブル]を実行し、先頭行が見出しであることを確認します。[テーブルデザイン]で名前を「商品表」にした例では、テーブルの外の空きセルに次を入力します。

=UNIQUE(商品表[商品])

テーブルに行が追加されれば、構造化参照もその行を含むように調整されます。追加後は、テーブルの範囲に新しい行が実際に含まれているかを確認します。出力側のスピル数式はテーブルの中には置けません。「入力はテーブル、結果は外側」と分けて使いましょう。

5. #SPILL!が出たら出力先を先に確認する

#SPILL!は結果を展開できない状態です。原因の一つが、出力予定範囲に既存の値や数式があることです。見た目が空欄でも、空文字列を返す数式が入っていれば空のセルとは違います。

1. エラーのある起点セルを選び、エラーの説明と予定される出力範囲を確認します。
2. 範囲内のセルを選び、数式バーも見て内容の有無を調べます。
3. 必要なデータを見つけたら削除せず、まず別の十分に空いた場所で同じ数式を試します。
4. 値だけでなく結合セル、テーブル内への入力、シートの端を超える出力なども確認します。

妨げているセルが不要だと確認できたときだけ内容を消します。結合セルを含む帳票なら、既存帳票を崩すより出力先を別シートにするほうが扱いやすい場合があります。原因がサイズ未確定など別の説明なら、周辺セルを消し続けず、数式が参照する範囲や関数を見直します。

6. オートフィルとの使い分けと共有時の注意

各行で数量×単価を求める、行ごとに例外の計算がある、といった表には通常の数式コピーも適しています。一覧の抽出や並べ替えを一か所で管理したい場面ではスピルが便利です。どちらかへ全面的に置き換える必要はありません。

配布先が同じ関数に対応しているかも確認します。旧版で新しい関数が使えない問題は、Ctrl+Shift+Enterを押せば解消するとは限りません。編集不要の提出先には、別シートへ結果全体を値として貼り付ける方法があります。元の数式入りシートは残し、固定した一覧は以後の追加に追従しないことを明確にします。

別ブックを参照する動的配列には制限があり、参照元を閉じた状態で更新すると#REF!になる場合があります。基礎の練習は同じブック内で行い、外部ブックに広げる前に開閉と再計算を確認しましょう。対象を列全体へむやみに広げず、必要な元データの範囲を指定します。

7. 最後に元データと結果の対応を確認する

スピルの確認は、表示件数だけでは不十分です。追加した品名が含まれるか、削除した品名が消えるか、似た名前が別項目になっていないかを見ます。余分な空白や文字の違いによる別項目は、スピルの失敗ではなく元データの整理が必要な状態です。

数式を取り除くときは起点セルを対象にします。誤操作直後は元に戻し、保存済みの元データを基に再入力することもできます。まず五件の練習表で「一セルを編集すると結果全体が変わる」「指定範囲外の追記は別問題」の二点を確認すれば、実際の一覧へ移すときも迷いにくくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】