Office365に新規に追加された機能「スピル」ですが、どのような用途で使っていますか?
自分はXLOOKUPやXMATCHなどの汎用的に使える便利関数はもちろん使うし、FILTERを使用して重複削除、TRANSPOSEで縦横変換とかもやっています。
ただ勉強でやっている感が強く、別に今までの方法で良いという気もしています。
おそらく業務では、フィルターやソートの代わりで使うことが多くなるかなというイメージです。
ピボットテーブルを使用しない場合、表をカスタマイズするには、元の表に対してフィルターやソートをかけてあげる必要がありました。
↓フィルターを使用して表を絞込む

面倒なのが、フィルターを使用すると元の表が見えなくなってしまうし、条件を変更するのに全て解除しなければいけないです。
そしてフィルター条件に関数などを使えないため、高度な絞り込みは出来ませんでした。あと縦(行)のフィルターは強いのですが、横(列)のフィルターにめちゃくちゃ弱い。
また、ソートをかけると元のデータの並びが完全に変わってしまうという欠点があります。
しかしスピルを使用することで、元のデータを保持したまま見たいデータだけを表示したり集計したりすることが出来ます。
スピルで表の終端を参照範囲とする
スピルでフィルターを掛ける場合の式を考えてみます。
ちょっと前にも出した下記のフィルターの動作をスピルで表現してみます。
↓フィルター

↓スピル

=FILTER(B2:E7,C2:C7="果物")
ここで仮に行が1個追加されたとするとどうなるでしょうか。

↑「ぶどう」という列が追加されましたが、スピルの範囲外であるため、スピルの表に反映されません。
そこで、FILTERの式を「=FILTER(B:E,C:C=”果物”)」とすると自動で列が追加されるようになりますが、1シートに表を並べるような使い方がしにくくなります。
おすすめの方法は、参照したい表に名前をつけてしまうということです。
まず、参照元の表を選択します。

そして、テーブルとして書式設定を選択します。

その後で書式を選んで、テーブルをクリックするとテーブルデザインタブが新しく出現します。

ここでテーブルの名前を変更することが出来ます。

仮にテーブル名を「table」とした場合、スピルを以下のように設定します。

=FILTER(table,table[分類]="果物")
テーブル名を指定することで、そのテーブルに存在する見出し以外の範囲を配列で取得することが出来ます。
また、 テーブル名 [カラム名]とすることで、その列にある値を配列で返してくれるので、これをFILTERの条件に使用しています。
テーブルの特性として、連続した範囲はすべてテーブルとみなしてくれるので、行を追加すると自動で範囲が拡張されます。
これで元の表の大きさが変更になっても、自動でスピルの参照先に適用することが出来ます。
その他に使いそうなところだと…

テーブル名 [#見出し]とすることで、見出しの配列を得ることが出来ます。
これをスピルで作成した表の見出しにそのまま使えば、元の表の見出しが変更になっても自動で変更してくれます。
列のフィルターの列名を動的に取得する
上記例で「No」という列はシステム的な列なので、スピルで見る表上は特に必要ないとします。
その場合、横(列)のフィルターで消せるのがスピルの大きなメリットですね。
スピルの式を以下に設定します。
=FILTER(FILTER(table,table[分類]="果物")&"",table[#見出し]<>table[[#見出し],[No]])

↑No列が削除された形で表示されました。FILTERで「見出しがNoでない」という条件を追加しているためです。
上記は単純にFILTER関数の使用例という感じですね。
今回紹介したテーブルの書式設定を組み合わせると何が良いかと言うと、列名が変更になってもフィルターが崩れないということです。
仮に「No」列を「番号」列に変更してみます。
↓スピルの式を変更すること無く、フィルタリングすることが出来ています。

コメントを残す