Excelで、こんな経験はないでしょうか。
数式を入れたのは1つのセルだけ。なのに、下のセルにまで数字が勝手に並んでいる。自分で入れた覚えのないものが出てくるので、戸惑うところです。
これはスピルという仕組みです。こぼれる、あふれる、という意味の言葉です。
困ったことではありません。むしろ、ここが分かると下までドラッグする作業そのものが要らなくなります。
この記事では、次の順で説明します。
- スピルとは何か（1つ入れて10個出す）
- 薄いグレーの数式「ゴースト」と、スピルの消し方
- 広がる関数と、広がらない関数の見分け方
#SPILL!エラーの直し方- SEQUENCE：連番を作る
- SORT：元の表を残したまま並べ替える
- UNIQUE：重複を消す（＋
#で数える） - RANDBETWEEN：ダミーの数字を作る
- TEXTJOIN：区切り文字でつなぐ
- FILTER：条件に合う行だけ取り出す
- 使えないバージョンがあります
1. スピルとは何か
まず、実際に見てみましょう。
A1をクリックして、次の数式を入れてEnterを押します。
=SEQUENCE(10)
1から10までの数字が、A1からA10まで縦に並びます。
入力したのはA1だけです。A2以降には何も入力していません。それでも数字が出ています。
これがスピルです。Excelは、1つの数式から答えが複数出るとき、必要なセルまで自分で広げます。
昔のExcelにはこの仕組みがありませんでした。数式を1つ入れて、下までドラッグしてコピーする。それが当たり前でした。今は、1つ入れれば済むものがあります。
2. 薄いグレーの数式「ゴースト」と、スピルの消し方
さきほどの状態で、A2をクリックしてみてください。
数式バーを見ると、=SEQUENCE(10) という数式が薄いグレーで表示されています。
これをゴーストと呼びます。「A1の数式の結果が、ここまで届いています」という印です。
A2の中身は、実は空です
グレーで見えていても、A2自体には何も入っていません。A1の数式の結果が表示されているだけです。
だから、こういうことが起きます。
- A2を選んでDeleteを押しても、消えません
- 数字を上書きすると、A1が
#SPILL!になります
消したいときは、先頭のセルを消す
スピルした結果を消す方法は1つだけです。
先頭のセル（この例ではA1）を消してください。 全部まとめて消えます。
「一部だけ残したい」はできません。スピルの結果は、全体で1つのかたまりだからです。
どうしても1つだけ返したいとき
広がってほしくない、答えを1つだけ返してほしい、という場面もあります。そのときは、関数の前に @ を付けます。
=@SEQUENCE(10)
これで、広がらずに先頭の1つだけが返ります。ただし普段は使いません。スピルを止める必要が出たときの逃げ道として、頭の片隅に置いておく程度で十分です。
3. 広がる関数と、広がらない関数の見分け方
すべての関数が広がるわけではありません。
たとえば、C1に次の数式を入れてみてください。
=RANDBETWEEN(1,20)
1から20までのランダムな整数が、1つだけ出ます。C2やC3には何も出ません。
RANDBETWEENは答えが1つしかない関数なので、広がる必要がないのです。
見分け方は簡単です
1つ入れて、周りのセルまで埋まったら、それは広がる関数です。 それだけです。
この記事で扱う5つは、きれいに分かれます。
| 関数 | 何をする | |
|---|---|---|
| 広がる3つ | SEQUENCE | 連番を作る |
| SORT | 並べ替える | |
| UNIQUE | 重複を消す | |
| 広がらない2つ | RANDBETWEEN | ランダムな整数を作る |
| TEXTJOIN | 文字をつなぐ |
先にこの2グループに分けてしまうと、5つを5回覚えずに済みます。
4. #SPILL! エラーの直し方
スピルで一番よく出るエラーが、これです。
セルに #SPILL! と表示されて、答えが出てきません。
原因のほとんどは「広がる先が空いていない」
試しに、E2に適当な文字を入力しておいてから、E1に次の数式を入れてみてください。
=SEQUENCE(3)
#SPILL! が出ます。
理由は単純です。E1からE3まで広がろうとしたのに、E2にすでにデータが入っていたからです。
Excelは、勝手に上書きしません。だから「広がれませんでした」と教えてくれています。
E2のデータを消すと、そのまま通ります。
#SPILL! が出たら、まず広がる先に何か入っていないかを確認してください。空白に見えても、スペースが1つ入っているだけで止まります。
もう1つ、意外な原因があります
テーブル（Ctrl+Tで作る、あの縞模様の表）の中では、スピルできません。
テーブルは1行ずつ数式を持つ仕組みなので、1つの数式が複数行に広がることを許しません。テーブルの中で #SPILL! が出たら、データが邪魔をしているのではなく、場所が悪いということです。テーブルの外に数式を置いてください。
5. SEQUENCE：連番を作る
連番を作る関数です。請求番号や、名簿の通し番号で使います。
=SEQUENCE(10)
これで1から10まで。カッコの中の数字が「何行分か」です。
引数は4つあります
前から順に、行数、列数、開始する数、増える数です。
=SEQUENCE(10,1,1001,10)
この式だと、1001、1011、1021…… と、1001から10ずつ増える連番が10個できます。
列をまたぐ連番も作れます
=SEQUENCE(3,2)
3行2列の範囲に、1から6までが並びます。こういう形はオートフィルでは作りにくいので、SEQUENCEの出番です。
オートフィルとの違い
「連番ならドラッグでいいのでは」と思われるかもしれません。実際、1列の単純な連番なら大差ありません。
違いが出るのは、個数を変えたいときです。
オートフィルで作った連番は、20個に増やしたければ、もう一度ドラッグし直します。SEQUENCEはカッコの中の数字を10から20に書き換えるだけです。
6. SORT：元の表を残したまま並べ替える
並べ替えの関数です。
=SORT(A2:D21,3,-1)
引数は前から、並べ替える範囲、何列目を基準にするか、順序です。
順序は 1が小さい順、-1が大きい順。ここは覚えるしかないところです。上の式なら「A2:D21を、3列目（金額）の大きい順に並べる」という意味になります。
並べ替えボタンとの決定的な違い
データタブの並べ替えボタンを押すと、元の表そのものが動きます。
SORTは違います。元の表は1行も動きません。 別の場所に、並べ替えた結果だけを作ります。
しかも、元の数字を書き換えると、並び順が自動で直ります。
元の表は入力した順のまま残したい。でも金額順の一覧も見たい。その両方を同時に持てるのがSORTです。
7. UNIQUE：重複を消す（＋# で数える）
同じ取引先が何度も出てくる表から、取引先の一覧だけを抜き出します。
=UNIQUE(A2:A21)
重複が消えて、社名が1つずつ並びます。これも元の表は触りません。
# を使うと、数え直しが要らなくなります
ここからが便利なところです。
いまUNIQUEをF2に入れたとします。この一覧が何件あるかを数えるには、こう書きます。
=COUNTA(F2#)
F2# の # は、「F2から広がっている範囲ぜんぶ」という意味です。スピル範囲演算子と呼びます。
取引先が増えて一覧が伸びても、数式を直す必要がありません。F2:F7 のように範囲を書いてしまうと、伸びたときに数え漏れます。
各社が何回出てくるかを数える
「全部で何社か」ではなく「各社が何回出てくるか」を知りたいときは、COUNTIFを使います。
=COUNTIF(A2:A21,F2#)
各社の件数が、そのまま縦に並びます。
数える範囲は元の表の列（重複が入っているほう）。条件のほうに F2# を置きます。条件が6つあるので、答えも6つに広がるわけです。
ここが今回いちばんの見どころです。下にドラッグしません。 ということは、固定するための $ も要りません。絶対参照で悩む必要がなくなります。
組み合わせられます
=SORT(UNIQUE(A2:A21))
重複を消して、そのまま五十音順に並べ替えます。UNIQUEの結果をSORTが受け取っている形です。
「重複の削除」という機能もExcelにはありますが、あれは元の表からデータを消してしまいます。UNIQUEは元を残すので、性格が違います。
⚠️ # は先頭のセルにしか付きません
F2# は書けますが、F3# は書けません。F3はゴーストだからです。# を付けられるのは、数式が実際に入っている先頭のセルだけです。
8. RANDBETWEEN：ダミーの数字を作る
指定した範囲で、ランダムな整数を作ります。
=RANDBETWEEN(1,20)
1から20までのどれかが1つ出ます。広がらないので、3つ埋めたければ3つに入れます（コピーするか、範囲を選んで入力後に Ctrl + Enter）。
値が残らないことに注意
RANDBETWEENは揮発性関数と呼ばれるもので、シートを触るたびに計算し直されます。別の場所に何か入力しただけで、数字が入れ替わります。
作ったダミーデータをそのまま残したいときは、値として貼り直します。
- 対象のセルをコピー
- Ctrl + Alt + V（形式を選択して貼り付け）
- 「値」を選んでOK
これで数式が消え、数字だけが残ります。以降は何をしても変わりません。
「ランダムな数字なんて何に使うのか」とよく聞かれますが、主な出番は練習用・確認用のダミーデータ作りです。本物のデータを使えない場面で、形だけ整えた数字が欲しいことがあります。
9. TEXTJOIN：区切り文字でつなぐ
都道府県・市区町村・番地が別々の列に入っている住所を、1つにまとめます。
=TEXTJOIN(" ",TRUE,A2:C2)
引数は前から、区切り記号、空のセルを無視するか、つなぐ範囲の3つです。
区切り記号のところを "," にすればカンマ区切り、"" にすれば区切りなしでそのまま連結されます。
2番目の引数が、この関数の一番おいしいところ
TRUE と FALSE の違いを、実際に比べてみてください。番地が空欄の行で差が出ます。
| 2番目の引数 | 番地が空欄の行の結果 |
|---|---|
TRUE |
東京都 練馬区 ← 余計な区切りが入らない |
FALSE |
東京都 練馬区␣ ← 末尾に区切りだけが残る |
TRUE にしておくと、空欄を飛ばしてくれます。住所の最後に不自然な空白がぶら下がりません。
& でつなぐのと何が違うのか
「& でつなげばいいのでは」と思われるかもしれません。できます。ただし違いが2つあります。
&は区切り記号を1つずつ全部書く必要があります。 列が10個あれば9回書きます。TEXTJOINは1回書けば済みます&は空欄を判断してくれません。 空の列があっても、区切りをそのまま出力します
この2つが、TEXTJOINを使う理由です。
10. FILTER：条件に合う行だけ取り出す
最後に、実務でおそらく一番使う関数です。
=FILTER(A2:D21,D2:D21="高橋")
担当が「高橋」の行だけを、まるごと抜き出せます。
引数は、取り出したい範囲と、条件の2つです。条件の書き方が少し特殊で、「どの列が」「何と等しいか」を範囲ごと書きます。
オートフィルターとの違い
- 元の表を触りません（フィルターは元の表の見え方を変えます）
- 結果が自動で更新されます（フィルターは条件が変わるたびにかけ直します）
- 別のシートに置いておけます
SORTと組み合わせる
=SORT(FILTER(A2:D21,D2:D21="高橋"),3,-1)
「高橋さんの案件だけを、金額の高い順に」が1つの数式で出せます。
抽出して並べ替えて、という手作業がまるごと消えます。
11. 使えないバージョンがあります
最後に、大事な確認です。
SORT・UNIQUE・SEQUENCE・FILTERは、買い切り版のExcel 2019以前では使えません。
Microsoft 365、またはExcel 2021以降が必要です。
関数名を入力しても候補に出てこない場合、やり方が悪いのではなくバージョンの問題です。TEXTJOINとRANDBETWEENは、もう少し古いバージョンでも使えます。
お使いのExcelがどれなのかは、[ファイル]→[アカウント]から確認できます。
まとめ
スピルは、1つの数式から答えが複数出るとき、Excelが必要なセルまで自分で広げてくれる仕組みです。
つまずいたときに戻る場所は、2つだけです。
- 答えが広がる関数か、広がらない関数か（1つ入れて周りが埋まれば、広がる関数）
- 広がる先が空いているか（
#SPILL!は、広がる場所が足りないという意味）
そして、広がるということはドラッグしないということです。ドラッグしないなら、$ で固定する必要もありません。数式が短くなり、行が増えても直さなくてよくなります。
覚える前に、まず1つ入れてみてください。広がるところを一度見てしまえば、あとは早いと思います。
実際に広がる様子は、動画のほうが分かりやすいかもしれません。この記事の内容を、画面を動かしながら解説しています。
「自分の仕事のExcelでもできますか？」というご相談も受け付けています。
今の状況を聞かせていただければ、どこから始めると良いか一緒に整理していきます。
