段階7:スピル関数で、Excelの設計思想が変わる時期
段階7は、関数を1つずつ覚える時期ではありません。スピル関数、LET、実データ範囲の考え方を組み合わせて、Excelシート全体を可変構造として設計し始める時期です。
Excelのスピル関数、つまり動的配列を最初に見たとき、正直そこまで感動はありませんでした。FILTER関数を見ても、最初の感想は「便利そうだけど、これ前もできたよね?」というものだったからです。
実際、私の場合はVBA禁止になったあと、旧世代の関数をかなりやり切った後でした。FILTER相当の処理はINDEX + SMALLで組んでいたし、UNIQUE相当の重複除去もCOUNTIFで作っていた。SORTやSORTBYに近いことも、作業列を使えばなんとかできていました。
だから最初は、スピル関数を見ても「やりやすくなった」とは思っても、「革命だ」とは思えなかったのです。
ただ、後から考えが大きく変わります。今振り返ると、スピル関数は便利な新機能ではなく、Excelの設計そのものを変える技術でした。
下まで数式をコピーしておく発想から、最終行を決め、実データ範囲だけを配列化し、1セルの数式から必要な結果をスピルさせる発想へ変わります。ここでExcelは、セル単位の作業表から、変化に追従する設計物に近づきます。
この記事で扱う流れ
この記事の位置づけ
この記事は、FILTER関数やUNIQUE関数の構文を解説する記事ではありません。テーマは、Excelの技術がどのような流れで進化してきたのか、そしてスピル関数をどの視点で捉えると実務設計が変わるのかです。
テクニックとしての「使い方」よりも、技術の流れとしての「意味」を整理します。
振り返ると、自分のExcelは何回か世界が変わっている
自分のExcelスキルを振り返ると、考え方が根本から変わったタイミングが何度かありました。
1. 関数時代:セルごとに数式を書く世界
最初は普通のExcelでした。IF、VLOOKUP、COUNTIF、SUMIF。いわゆる「Excelそこそこできます」レベルです。この頃はまだ、セルごとに数式を書く世界で、Excelの可能性も「表計算ソフト」以上には見えていませんでした。
2. VBA時代:作業が設計に変わった世界
次に来たのがVBAです。これはかなり衝撃でした。ボタン1つで処理が完了する。最終行を自動で取得して、必要な分だけ処理する。大量ファイルを一括で処理する。Web操作、データ加工、一括印刷までできる。
特に大きかったのは、人がやっていた作業が消えるという感覚です。VBAを覚えると、仕事の見え方が変わります。作業が設計になる感覚と言ってもいいと思います。
3. VBA禁止時代:使えたものが突然使えなくなる世界
しかし、ある日環境が変わります。VBA禁止。セキュリティポリシーの変更、外部監査、ガバナンス強化。理由はいろいろありますが、結果は同じです。マクロが使えない。
これは正直、かなり苦しかったです。周りも「VBAが使えないなら無理」という状態でした。実際、VBAを使っていた人ほど手も足も出ないケースが多かったと思います。
VBA禁止は、できる人ほどメンタルが折れる
VBA禁止になって一番きつかったのは、スキルの問題ではありませんでした。メンタルです。
目の前の仕事を見たときに、「これ、VBAなら3分で終わるのに」「ループを回せば一瞬なのに」「最終行を取得して処理するだけなのに」という考えが、毎日、何度も頭をよぎります。
これが本当に消耗します。手は動かしているのに、頭の中ではずっと「本来ならもっと早く終わる仕事」という意識が消えない。つまり、自分の作業を自分でずっと否定し続けている状態になるのです。
VBAならすぐ終わる処理の姿を知っているからです。理想の処理速度を知ってしまっている人ほど、現実の手作業や旧式関数とのギャップが大きくなります。
集中力が落ちるという、地味だけど深刻な問題
メンタルが削られると、当然集中力も落ちます。関数を組んでいる途中で集中が切れる。「VBAなら」と考えて思考が止まる。作業時間が延びる。そして、また「VBAなら早いのに」と思う。
この悪循環に入ります。集中力が落ちているから、本来できるはずの関数すら組むのに時間がかかる。そして時間がかかるたびに、またVBAと比較してしまう。
VBA禁止初期、自分も周りも、たぶん本来のパフォーマンスの半分以下しか出ていなかったと思います。スキルの問題ではなく、完全にメンタルの問題でした。
むしろ知らない人のほうが集中できている現実
現場で見ていて一番意外だったのが、Excelの効率化手段を知らない人のほうが、集中して仕事ができていることでした。
これは「知らない人のほうが優秀」という意味ではありません。そうではなく、VBAも知らない、効率的な関数も知らない、Power Queryも知らない人は、今やっている作業が非効率だという自覚がないのです。
自覚がないから、目の前の作業を「これが普通」として淡々とこなせる。もっと早い方法があるのに、という雑念がない。比較する対象がない。だからストレスも少なく、集中力も削られません。
VBA禁止環境で起きた逆転現象
- 知らない人:比較対象がないため、今の作業に集中できる。
- 知っている人:理想の処理が頭にあるため、集中が削られる。
- できる人:過去の成功パターンが強いほど、別の技術に切り替えるのが難しい。
VBA使いほど苦戦する、もうひとつの理由
メンタル面に加えて、もうひとつ大きな理由があります。それは、頭が完全にVBA思考になっていることです。
VBAでは、変数を置き、ループを回し、配列に格納し、条件分岐し、最終行を取得して処理します。どう処理するかを順番に書く、手続き型の世界です。
しかし関数の世界は、まったく別物です。手続き型ではなく宣言型に近く、「どう処理するか」ではなく「どういう結果が欲しいか」を書く世界です。
ここで変数を置きたい。ここでループを回したい。ここで最終行だけ処理したい。こう考えるほど、関数がVBAの劣化版に見えて苦しくなります。
欲しい結果を配列として捉え、抽出、整形、集計、並べ替えを数式の構造として表現する。比較をやめた瞬間、関数は別の技術として見えてきます。
正直、自分も数年伸び悩みました。理由は単純で、頭がVBAのままだったからです。無理やりVBA思考で関数を書いていた。だから伸びない。
ある時から変わったポイントは、関数はVBAの代替ではないと理解したことでした。関数は手抜き版のVBAではなく、まったく別の設計思想なのだと気づいた瞬間、世界が変わりました。
旧世代関数を極めていく時代
そこから、関数をかなり深くやるようになります。INDEX + SMALL + ROWの配列的な抽出。MIDBによる固定長帳票の解体。可変抽出、親子構造の展開、複数条件の抽出、可変明細、作業列の設計。
今のFILTER関数に相当する処理も、普通に組めるようになりました。しかも、実務で壊れず、毎月安定して回るレベルまで作り込む。
さらに、ここでPower Queryも併用するようになりました。VBAが使えた頃は、何でもVBAで解決できてしまうので、Power Queryに手を出す動機が弱かったのだと思います。
でも逆に、VBA禁止だったからこそ、関数とPower Queryを深く学ばざるを得ませんでした。そして結果的に、これが効きました。
だからスピル関数の価値が、最初はわからなかった
その状態でスピル関数が登場しました。でも正直、最初はそこまで感動しませんでした。なぜなら、前もできたからです。
FILTER関数の処理も、INDEX + SMALLでやっていた。SORTもSORTBYも、作業列で並べ替えていた。UNIQUEもCOUNTIFで重複を除いていた。
だから「便利にはなった。でも何がそんなに違うの?」と思っていました。実際、できること自体は大差ないケースも多かったのです。
でも後から気づく。「最終行」が消えた
使っていくうちに、決定的な違いに気づきます。本当の価値は「できること」ではありませんでした。
昔は、行数が毎月変わるデータに対して、将来増えることを見越して5万行、10万行先まで数式を仕込むのが定番でした。そして、IF(A2="","",処理)で後続を停止させる。
これは旧世代Excelではほぼ常識のテクニックです。ただ、実際にやると問題は山積みでした。数万セルに数式が存在し、作業列が大量に並び、ファイルサイズが肥大化し、再計算は重く、保守は属人化する。
「動くけど重い」「動くけど触れない」ファイルが量産される。これが旧世代の構造的な限界でした。
1セルに数式を書く。必要な分だけ展開される。データが増減しても、結果範囲が動的に変わる。つまり、数式を大量に敷き詰める設計から、結果を動的に生ませる設計へ変わったということです。
スピルはVBAの最終行思想に近づいた
VBAでは、最終行を取得して、必要な範囲だけを処理するのが基本です。無駄な空白行を10万行分処理し続けるような設計は、普通は避けます。
スピル後のExcel関数は、その考え方に近づきました。1セル。必要分だけ。結果は動的に伸びる。
正直、一番驚いたのはFILTERが便利という話ではなく、ファイルサイズが大きく軽くなったことです。今まで20MBあったファイルが、数MBで済む。再計算も速い。シートの見通しもいい。
ここで初めて、「Excelの設計そのものが変わっている」と気づきました。スピルは関数の追加ではなく、シート全体の作り方を変える技術だったのです。
| 観点 | 旧世代関数設計 | スピル後の設計 |
|---|---|---|
| 数式の置き方 | 将来行数を見越して大量コピー | 起点セルに1つ置いて動的展開 |
| 行数変化への対応 | 空白判定や余分な式で吸収 | 結果範囲が自動で伸縮 |
| ファイルサイズ | 数式セルが増えるほど肥大化 | 必要な式が少なく軽くなりやすい |
| 保守性 | 作業列と範囲管理が属人化しやすい | 設計意図が数式の起点に集約される |
実務での設計形:最終行を決めてからスピルさせる
スピル関数を使うときに一番伝えたいのは、難しい範囲指定ではありません。まず最終行を1か所で決める。その最終行を使って、INDEXで必要な範囲だけを作る。それだけです。
たとえば、A列に必ず値が入るデータで、1行目が見出し、2行目から明細だとします。この場合は、作業セルで最終行を持たせるとイメージがかなり分かりやすくなります。
まずは作業セルで最終行を出す
たとえば H1 に、A列の件数を数える式を入れます。A1に見出しがあり、A列に途中空白がない前提なら、これで最終行番号として使えます。
ここで H1 が 20000 なら、データは20,000行目まである、という意味です。あとはこの値を、範囲の終点として使います。
INDEXで範囲の終点を作る
通常なら A2:C20000 と手で書くところを、INDEXで終点だけ動かします。
これは「A2から、C列のH1行目まで」という意味です。H1 が 20000 なら A2:C20000、H1 が 35000 なら A2:C35000 になります。
その範囲をFILTERに渡す
条件列も同じ考え方です。B列の2行目から、B列の最終行までを条件範囲にします。
これだけで、処理対象は「A2からC列の最終行まで」に絞られます。数式を10万行分コピーするのではなく、最終行を1回決め、その範囲だけをFILTERに渡す形です。
昔は「将来増えるかもしれないから、下まで数式を入れておく」設計でした。スピル後は「今ある実データ範囲だけを作り、その結果を必要な分だけ出す」設計にできます。
同じことをLETで書く
作業セルで考え方が分かったら、次はLETで同じ処理を1つの数式にまとめます。やっていることは同じです。H1 に置いていた最終行を、式の中の last に入れているだけです。
lastが最終行、dataが抽出対象の範囲、keyが条件を見る範囲です。最後に FILTER(data,key="X","") と書くので、数式の意味も読みやすくなります。
並べ替えまで含める場合
FILTERした結果をさらにSORTしたい場合も、考え方は同じです。FILTER結果を result に一度入れて、それをSORTします。
この形にすると、最終行取得は1回、処理対象は実データ範囲だけ、数式本体は1セル、出力はスピルになります。VBAでいう「最終行を取って、必要な範囲だけ処理する」感覚にかなり近いです。
なお、A列に途中空白がある場合は、件数を数える列を変えるだけです。社員ID、伝票番号、日付など、必ず埋まる列で COUNTA すれば、後ろの INDEX の考え方は同じまま使えます。
どれぐらいの人ができるか
厳密な統計ではなく現場感ですが、スピル関数を「便利な関数」として使う人と、「Excelの設計を変える技術」として使う人の間にはかなり差があります。
FILTERやUNIQUEを単体で使える人は、Microsoft 365環境では少しずつ増えています。ただし、LETで最終行を一度だけ持ち、INDEXで実データ範囲を作り、FILTERやSORTで一括処理し、スピルで出力するところまで設計できる人はまだ少数派です。
| 状態 | できること | 現場での人数感 |
|---|---|---|
| 単体関数として使う | FILTER、UNIQUE、SORTを個別に使う | 365環境では増えている |
| LETと組み合わせる | 中間結果や最終行を式内で管理する | Excelが得意な人の中でも少数派 |
| 設計思想として使う | 実データ範囲だけを配列化し、1セルから可変出力する | 現場ではかなり希少 |
この段階の人は、新関数を覚えた人ではありません。旧世代関数、VBAの最終行思想、Power Queryの構造化、そしてスピルの可変出力をつなげて考えられる人です。
VBA禁止は苦しかった。でも今思うと意味があった
正直、VBA禁止はかなり苦しい経験でした。メンタルは削られるし、集中力も落ちる。「VBAなら」と何度も思う。何も知らない人のほうが穏やかに作業している姿を見て、複雑な気持ちになる。
でも今振り返ると、Excelの考え方を根本から変えてくれた出来事でもありました。VBAだけでは気づかなかった関数設計、配列的思考、可変構造、Power Query、そしてスピル。これらを深く学ぶことになったからです。
制限環境で鍛えられた人ほど、環境が変わったときに大きく伸びる可能性があります。VBAが使えた頃は、何でもVBAで解決できてしまうから、関数の限界を突破する動機がなかった。Power Queryに触る必要もなかった。スピル関数の本当の価値にも、たぶん気づかなかったと思います。
制限は、ときに最大の学びになります。
DXという言葉が当たり前になった今、Excelに求められるのは「マクロで自動化された属人ツール」だけではありません。関数とクエリで構造化され、誰でも保守できるシートです。スピル関数は、その世界に向かうための設計思想でもあります。
この段階を通過したサイン
- LETで最終行を1回だけ取得して使い回せる
- INDEXで実データ範囲を動的に作れる
- FILTERやSORTで可変出力を設計できる
- 数式を大量にコピーせず、1セルから結果をスピルさせる設計にできる
まとめ
スピル関数を便利機能として使うだけなら、価値は「FILTERが簡単になった」「UNIQUEで重複除去が楽になった」で止まります。
しかし、設計を変える技術として捉えると、見え方が変わります。数式を大量に埋めるのではなく、起点から結果を生ませる。将来行数を予測して仕込むのではなく、データ量に合わせて動的に伸縮させる。作業列を積み上げるのではなく、構造として見える形にする。
VBA禁止という数年の苦しさがなければ、たぶんここには辿り着けませんでした。スピル関数は、Excelを「セルごとに処理する道具」から「可変構造を設計する道具」へ進める技術だったのです。