【Excel新機能】データが増えるたびに数式を直すのはもう終わり。動的配列関数とスピル演算子（#）の威力とは：Tech TIPS
Excel作業で「データ量の増減に合わせて数式をコピペし直す」無駄な作業をしていないだろうか？ Excel 2021以降で導入された「スピル演算子（#）」を使えば、1つの数式で全データが自動集計できる。テーブル機能（構造化参照）と組み合わせた、メンテ不要で自動更新される最強の集計システム構築術を解説しよう。
対象：Excel 2021／2024／365
動的配列関数と魔法の記号「#」スピル演算子を使いこなせ
Excel作業で「データ量の増減に合わせて数式をコピペし直す」無駄な作業をしていないだろうか？ Excel 2021以降で導入された「スピル演算子（#）」を使えば、1つの数式で全データが自動集計できる。テーブル機能（構造化参照）と組み合わせた、メンテ不要で自動更新される最強の集計システム構築術を解説しよう。
これまでの「Microsoft Excel」では、1つのセルに1つの計算結果を表示するのが基本であった。そのため、複数の結果を得るには数式を必要な分だけコピー＆ペーストしなければならず、データ量の増減に合わせて範囲を手動で修正する作業が不可避となっていた。
しかし、Excel 2021以降ならびにExcel 365で「動的配列」と呼ばれる機能がサポートされた。「UNIQUE関数」や「SORT」関数などの動的配列関数と、「スピル演算子（#）」という特殊な記号を組み合わせることで、1つの数式で複数の値を返し、隣接するセルへ自動的に結果を「溢（あふ）れ出させる（スピルさせる）」ことが可能になった。これは単なる効率化にとどまらず、Excelを「静的な計算表」から「自動更新されるシステム」へと変貌させる進化といえる。
本Tech TIPSでは、動的配列関数とスピル演算子（#記号）の使い方を紹介しよう。
スピル演算子（#）とは？
スピル演算子（#）は、「スピルによって動的に変化している範囲全体」を指し示すための記号である。
通常、範囲を指定する際は「A1:A10」のように「どこからどこまで」と固定の番地（絶対参照など）を指定する。対してスピル演算子は、「スピルが始まった先頭のセル番地」に「#」を付けるだけでよい。その結果が5行でも100行でも、Excelが自動で末尾までを範囲として認識する。
このスピル演算子を使えば、データ量の増減に合わせて自動的に範囲を伸縮するため、数式をコピーし直す作業が不要になる。
スピル演算子の具体的な活用例
「販売記録」の表を例にして、具体的にスピル演算子の使い方とメリットを見ていこう。以下のように時系列で販売された商品名と売上が並んだ表があったとする。期間内に販売された商品ごとの売上を集計することを考える。
「販売記録」の表
この表を使って、動的配列関数とスピル演算子について解説していく。「A」列には日付、「B」列には商品名、「C」列に売上が並んだものだ。「B」列の商品名から重複のない商品名一覧を抽出し、各商品の売上合計を算出していく。
まず、動的配列関数のUNIQUE関数を使って重複のないリストを「E2」セルに作成する（UNIQUE関数については、Tech TIPS「【Excel新関数】UNIQUE関数で重複データを除いた自動更新リストを一発作成するテクニック」参照のこと）。
=UNIQUE(B2:B21)
「E2」セルから下に向かって重複のない商品名が自動的に表示されるはずだ。これが「スピル」した状態である。
次に、この抽出されたリストを使って「F」列に各商品の売上合計を計算する。それには、「F2」セルに以下の数式を入力すればよい。
=SUMIF(B2:B21, E2#, C2:C21)
ポイントは、SUMIF関数の「検索条件」（2番目の引数）として「E2#」と入力することだ。「E2からスピルしている範囲の全て」を条件として認識するからだ。これにより、UNIQUE関数を使って抽出した重複のない商品名が検索条件となる。
商品ごとの売上合計を計算する（1）
SUMIF関数を使って、セル範囲「B2:B21」から作成した商品一覧を「検索条件」として検索、該当する商品の売上をセル範囲「C2:C21」から抽出して合計する。ここで「検索条件」として「E2#」とスピル演算子を指定するのがポイントだ。これにより、UNIQUE関数で抽出した商品名が増えても、自動的に「検索条件」として認識されるからだ（後述）。
商品ごとの売上合計を計算する（2）
商品ごとの売上合計が計算できる。動的配列に対応したSUMIF関数を使って、「検索条件」にスピル演算子を使うことで、「F2」セルにしか数式を入力していないのにもかかわらず、「F3」セルと「F4」セルにも売上合計が入力される。
自動更新とメンテナンス性の向上
もし元データに新しい商品（例えば「キウイ」）が追加されたとする。すると、UNIQUE関数の結果として自動的に「キウイ」が増える。それと同時に「E2#」を参照しているSUMIF関数も自動的に追加された「キウイ」が計算される。数式コピーや範囲修正しなくても、新しい商品の追加が反映されるわけだ。
これまでは集計行が増えるたびに「数式のコピー漏れ」がないかを気にする必要があった。しかし、スピル演算子を使うことで、記述する数式は先頭の1セル分だけで済むようになる。修正が必要な際も、先頭の数式を直すだけで全データに反映されるため、保守性が飛躍的に向上する。
動的配列関数を使えば新しい商品は自動的に反映される
「18」行を挿入し、データとして「2月11日」「キウイ」「300」を入力してみた。UNIQUE関数で抽出している商品名には「キウイ」が追加され、SUMIF関数によって商品合計も計算される。
自動反映されるドロップダウンリストを作成する
また、この仕組みは、「データの入力規則」によるドロップダウンリスト（プルダウンリスト）でも利用できる。UNIQUE関数などの動的配列関数を使って抽出したリストを、スピル演算子を使って指定すればよい。例えば、「販売記録」の表ならば、ドロップダウンリストを表示したいセルを選択して、［データ］タブの［データの入力規則］をクリック、開いた［データの入力規則］ダイアログの［設定］タブの「入力値の種類」欄で［リスト］を選択、「元の値」欄で「=E2#」と入力すればよい。
このようにしておけばデータが増えるたびに選択肢が勝手に増える「メンテナンスフリーなプルダウン」が作成できる。
テーブル（構造化参照）との組み合わせ
商品名の抽出において、「=UNIQUE(B2:B21)」のようにセル範囲を指定していると、範囲外（22行目以降など）に追加されたデータが反映されない。この問題を解決するには、表（セル範囲）を「テーブル」に変換し、構造化参照を活用するのがよい。
テーブルにしたいセル範囲を選択し、［ホーム］タブの［テーブルとして書式設定］をクリック、好みのテーブルデザインを選択する。これでセル範囲がテーブルになり、見出し行にフィルターが設定される。
テーブルにしたセル範囲内にあるセルを選択すると、［テーブルデザイン］タブが現れるので、これをクリックし、「テーブル名」欄に分かりやすい名前を入力する。関数内ではこの名前を使って参照するので、分かりやすく、かつ短いものにするのがよいだろう。
これで構造化参照の準備ができたので、UNIQUE関数を以下のように書き換えてみよう。ここではテーブル名を「販売管理」としている。見出し行のセルの値が自動的に列の名前になる。
=UNIQUE(販売管理[商品名])
直接セル範囲を指定したのと同じ商品名が並ぶはずだ。同様にSUMIF関数も以下のように書き換えれば、追加した行が自動的に反映されるようになる。
=SUMIF(販売管理[商品名], E2#, 販売管理[売上])
ここで「B22」セルに「レモン」、「C22」セルに「150」と入力してみてほしい。自動的にテーブルの範囲が拡大され、UNIQUE関数で抽出したリストに「レモン」が追加され、SUMIF関数によって売上合計が計算されるはずだ。なお、テーブルの範囲が拡大されない場合の対処方法は、Tech TIPS「Excelで表を『テーブル』にしたけど数式の自動反映や行／列の自動拡張が働きません」を参照してほしい。
構造化参照を使ってセル範囲を指定する（4）
テーブルにした表の下（「22」行）に「2月14日」「レモン」「150」を追加する。自動的にUNIQUE関数で抽出した商品名に「レモン」が追加され、SUMIF関数で売上合計が計算される。
動的配列関数を使う場合、セル範囲を構造化参照とすると行の追加が反映されるため、引数の書き換えが不要になる。また、どこの列を参照しているか分かりやすくなるためメンテナンス性も向上する。組み合わせて利用するのがおすすめだ。
Copyright© Digital Advantage Corp. All Rights Reserved.