LWP | 昔からあるExcel関数もスピルする

LWP TECHNICAL ARTICLE | 198

記事要約

Excelのスピルは、FILTER、UNIQUE、SORT、SEQUENCEのような新しい関数だけの機能ではありません。動的配列に対応したExcelでは、LEN、IF、ROUND、YEAR、VLOOKUPなどの昔からある関数も、範囲や配列を受け取って複数の結果を返せば、その結果を隣接セルへスピルできます。

この記事では、6枚の掛け合いインフォグラフィックを中心に、文字列、論理・判定、数値・丸め、日付、検索・参照という5種類の例を確認します。スピルするかどうかは関数の新旧や入力範囲の有無ではなく、数式全体の最終結果が1個か複数かで考えると見通しがよくなります。

本記事の対象とゴール

想定読者

• スピルはFILTERやUNIQUEなどの新関数だけで起きると考えている方

• LEN、IF、ROUND、YEAR、VLOOKUPを下方向へコピーして使っている方

• 暗黙の交差と@演算子の関係を整理したい方

• 数式をセル単位から配列単位へ切り替えたい方

本記事で得られること

1. 昔からある関数がスピルする理由を、戻り値の個数から説明できます。

2. 要素ごとに結果を返す関数と、1つの値へ集約する関数を区別できます。

3. VLOOKUPやINDEXが複数結果を返す使い方を理解できます。

4. 既存関数を、要素ごとの処理、配列・範囲の返却、1値への集約という3種類で整理できます。

1 文字列関数も範囲を丸ごと処理できる

LENやLEFTは、動的配列より前からある文字列関数です。現在のExcelでは、1つのセルではなく範囲を渡すと、範囲内の各要素を計算した結果が配列として返ります。

図1　関数が新しくなったのではなく、複数の結果をそのままワークシートへ展開できるようになりました。

2 論理・判定関数も一気に結果を返せる

比較式は、数値だけでなくTRUEとFALSEの配列も作れます。IFやISNUMBERは、その論理値や入力値の各要素を処理し、文字列または論理値の配列を返します。

図2　値の配列から判定の配列を作り、IFで表示用の結果へ変換する流れです。

3 数値・丸め関数も列全体を加工できる

ROUNDやINTも、配列内の各数値へ処理を行います。一方、SUMやAVERAGEは複数の値を受け取っても、通常は1つの集計値へまとめます。

図3　「範囲を受け取る」ことと「複数結果を返す」ことは同じではありません。

4 日付関数も日付列をまとめて分解できる

YEARやMONTHへ日付列を渡すと、各日付から年や月を取り出した配列が返ります。補助列の各行へ同じ式をコピーせず、1つの式で列全体の変換を表せます。

図4　設計の単位を、各行のセルから入力範囲と出力配列へ広げられます。

5 検索・参照関数も1件ずつとは限らない

VLOOKUPの検索値は1個に限定されません。動的配列対応Excelでは、検索値として範囲を渡すと、各検索値に対応する複数の結果を返せます。

図5　検索値や位置番号が配列なら、検索・参照結果も配列になり得ます。

6 なぜ昔からある関数がスピルするのか

既存関数を「スピル関数か、そうでないか」という固定ラベルで分けるよりも、数式を評価した後に何個の値が残るかを見る方が正確です。

図6　数式全体の最終結果が1個なら1セル、複数なら配列としてスピルすると捉えます。

まとめ

• スピルするのはFILTERやUNIQUEなどの新しい関数だけではありません。

• 昔からある関数でも、範囲や配列を受け取って複数の結果を返せばスピルします。

• 範囲を受け取っても、SUMのように1つの値へ集約する数式は通常スピルしません。

• 判断の軸は、関数の新旧ではなく数式全体が返す結果の個数です。

セルごとのコピーを前提にしていた数式も、入力範囲と出力配列を一つの単位として見直すと、より短く、意図の分かりやすい設計に変えられます。

出典メモ

• 元記事: 作業開始時は C:\Users\hoehoe\Downloads\02_記事掛け合い\昔からあるExcel関数もスピルする ― インフォグラフィック入力用原稿.md。記事完成後は同フォルダーの 記事作成済み へ記事198の接頭辞付きで保管します。

• 使用画像: 同フォルダーの番号①〜⑥の掛け合いインフォグラフィック6枚。公開記事用コピーは 02記事ソース\記事198_昔からあるExcel関数もスピルする_画像 に保管します。

• 図5は、検索値5件と整合させるため、元画像内の検索範囲2か所をA2:A5からA2:A6へ訂正した修正版を使用しています。元画像は上書きしていません。

• 生成指示・初期化プロンプト: コイ丸先輩で始まるMarkdown 2件は、画像制作意図を理解する参考資料としてのみ確認し、記事本文の一次情報や完了移動対象にはしていません。

• 技術確認: Microsoft Support「Dynamic array formulas and spilled array behavior」「Guidelines and examples of array formulas」「Implicit intersection operator: @」「Excel functions that return ranges or arrays」「How to correct a #SPILL! error」。

• 確認日: 2026-08-25

参考リンク

• https://support.microsoft.com/en-us/excel/dynamic-array-formulas-and-spilled-array-behavior

• https://support.microsoft.com/en-us/excel/guidelines-and-examples-of-array-formulas

• https://support.microsoft.com/en-us/excel/implicit-intersection-operator

• https://support.microsoft.com/en-us/excel/excel-functions-that-return-ranges-or-arrays

• https://support.microsoft.com/en-us/excel/how-to-correct-a-spill-error

Copyright © LWP 山中 一弘 |