今回は、Excelの動的配列とスピル範囲を扱う方法を紹介します。
動的配列は結果が複数セルに広がる仕組み
Excelの動的配列では、1つの数式から複数の結果を返せます。FILTER関数で条件に合う行を抽出したり、UNIQUE関数で重複しない一覧を作ったりすると、結果が下方向や右方向へ広がって表示されます。この広がった範囲をスピル範囲と呼びます。
従来は、複数セルに同じような数式をコピーして一覧を作ることが多くありました。動的配列では、先頭セルに数式を入れるだけで必要な範囲へ結果が展開されます。数式をコピーするのではなく、結果が広がる場所を管理することがポイントです。
一覧の抽出、並べ替え、重複削除、必要列の取り出しなどで使うと、作業列を減らしやすくなります。ただし、スピル範囲の考え方を知らないと、結果が出ない原因を見つけにくくなります。
スピル範囲の基本
スピル範囲は、動的配列の数式が返した結果を表示する範囲です。結果の件数が変わると、スピル範囲の大きさも変わります。
先頭セルだけに数式が入る
動的配列の数式は、基本的に先頭セルにだけ入力します。結果が表示されている他のセルには、同じ数式を手入力する必要はありません。結果セルを選択すると、数式の元になっている先頭セルが分かります。
スピル範囲内の一部だけを直接編集しようとすると、変更できないことがあります。結果全体は先頭セルの数式から作られているため、内容を変えたい場合は元の数式や元データを修正します。
空きスペースが必要になる
スピル範囲に結果を表示するには、展開先のセルが空いている必要があります。途中に文字、数値、空白に見える値、結合セルなどがあると、結果が展開できずエラーになることがあります。
動的配列を使う表では、結果が広がる可能性のある範囲を空けておきます。抽出結果の件数が増えることを考え、下方向に余裕を持たせると扱いやすくなります。
スピルエラーの確認
動的配列でよく起きるのが、結果を展開できないエラーです。数式自体が間違っているのではなく、表示先に問題がある場合があります。
展開先のセルを確認する
スピルエラーが出たら、まず結果が広がる範囲に何か入っていないか確認します。見た目は空白でも、スペースや数式の結果で空文字が入っていることがあります。
不要な値を削除すれば、結果が表示されることがあります。表の周囲にメモや補助計算を置いている場合は、動的配列の結果範囲と重ならない位置に移動します。
結合セルに注意する
スピル範囲に結合セルが含まれていると、結果を展開できないことがあります。動的配列を使う場所では、結合セルを避けるほうが安定します。
見た目を整える目的で結合セルを使っている場合は、中央揃えや罫線、セルの幅調整で代替できないか考えます。計算や抽出に使う範囲では、セル構造を単純に保つことが大切です。
スピル範囲参照を使う
動的配列の結果範囲は、先頭セルにシャープ記号を付けて参照できます。たとえば、結果の先頭セルがE2なら、E2#でスピル範囲全体を参照できます。
結果件数が変わっても追随できる
スピル範囲参照を使うと、抽出結果の件数が増減しても参照範囲が自動で変わります。集計やグラフ、別の関数へ渡す範囲として使うと便利です。
たとえば、FILTER関数で抽出した一覧を別の関数で数える場合、固定範囲ではなくスピル範囲参照を使うと、結果の変化に対応しやすくなります。
参照先を分かりやすくする
スピル範囲参照は便利ですが、どの数式の結果を参照しているか分かりにくくなることがあります。先頭セルの近くに見出しを置き、何の結果なのか分かるようにしておきます。
複数の動的配列を使うシートでは、結果範囲同士が重ならないように配置します。更新時に件数が増えて、別のスピル範囲とぶつからないよう余白を取ります。
表として運用するコツ
動的配列は便利ですが、入力用の表と出力用の一覧を分けると管理しやすくなります。同じ場所で入力と抽出結果を混ぜると、スピル範囲を誤って消したり、入力場所が足りなくなったりします。
元データはテーブル化する
元データをExcelのテーブルにしておくと、行を追加したときに関数が追随しやすくなります。列名で参照できるため、数式の意味も読み取りやすくなります。
抽出結果は別の場所に出し、元データは入力用として整理します。元データ、抽出条件、結果一覧を分けるだけでも、シートの見通しがよくなります。
結果範囲には手入力しない
スピル範囲に手入力すると、動的配列の結果と衝突する原因になります。結果一覧にメモを付けたい場合は、別列に置くか、元データ側にメモ列を作るほうが安全です。
動的配列の結果は、元データや条件に応じて変わります。結果行に直接メモを書くと、並び替えや抽出条件の変更で対応関係がずれることがあります。
共有する表での注意点
動的配列を使ったブックを共有する場合は、どのセルに数式があり、どこまでが結果範囲なのか分かるようにしておきます。結果範囲を入力欄と誤解されると、スピルエラーの原因になります。
入力欄と出力欄を見た目で分ける
入力するセルには薄い背景色を付け、動的配列の結果には別の見出しを置くなど、役割が分かるようにします。保護機能を使って、数式セルを編集できないようにする方法もあります。
説明用のメモをシート上部に置く場合は、長い文章にせず、入力場所と結果場所が分かる程度にします。使う人がスピル範囲を壊さない設計にしておくと、表を安定して共有できます。
結果が変わる前提で配置する
動的配列の結果は、元データや条件によって行数が変わります。今日は数行でも、来月には数十行に増えることがあります。結果の下に別の表やメモを置くと、更新時にぶつかる可能性があります。
定期的に更新するブックでは、動的配列の結果をシートの右側や別シートに出す方法もあります。入力欄、条件欄、結果欄を分けておくと、後から行数が増えても調整しやすくなります。
まとめ
Excelの動的配列では、1つの数式から複数セルへ結果が広がります。この広がった範囲がスピル範囲です。先頭セルに数式を入れ、展開先のセルを空けておくことが基本です。
スピルエラーが出たら、展開先の値や結合セルを確認します。スピル範囲参照を使えば、結果件数の変化に追随できます。入力用の元データと出力用の一覧を分けて設計すると、動的配列を使った表を扱いやすくできます。