Excel 「スピル」の重大な欠点

Excel 「スピル」機能の使い所

2020年9月19日

前回の記事で、Excelの「スピル」機能についてメリット・デメリットを書いていった。

しかし、自分で使ってみて、あまりに看過できないデメリットを新たに見付けたので、別記事としてそれについて取り上げることにした。

なにも難しいことではなく、なんのことはない事柄なのだけど、正直、これを知ったらスピルをそう気軽に使う気はしなくなるかと思う。

並べ替えができない

スピルの大きなデメリットとは単純なことで、スピルを使用した表では並べ替えができなくなるということだ。

下図ではD2セルを起点として
=B2:B11-C2:C11
というスピル式が使用されているが、この状態で表の並べ替えをしようとすると、「配列の一部を変更することはできません。」などというエラーが出てしまう。

これは、スピル式を入れたD列についてだけ並べ替えができないという話ではない。
スピル式の入っていない、A列～C列についても一切並べ替えはできず、このエラーが出てしまう。

配列？

エラーメッセージで配列とか言われてるけど、まあスピルというのは、以前のExcelでアホみたいに使いにくい項目だった「配列数式」というのを簡単に使えるようにしたものだ。

その配列が、並べ替え処理をしたら変えられてしまうから駄目だというが・・・。

いやこれはちょっと、致命的なレベルではないのか？

並べ替えたのに応じて、配列も融通を利かせて変形されてくれないといけないのでは？

今回のようなケースで、差額が大きい順・小さい順に並べ替えたいというのは当然のことのはずだが。

フィルタリングはできるのだが・・・

スピルの使われた表でも、オートフィルタによるデータのフィルタリングはできる。

下図は、D列について「-5より小さい」条件でフィルタリングしたもので、こういうことは普通にできる。

なのにどうして、並べ替えだけができない仕様のままにしているのか・・・。

代わりの並べ替え方法

スピル新関数

スピル機能の追加に伴って、6つの新しい関数がExcelに追加された。
それらは下記の通りだ。

この中でXLOOKUP関数だけは超重要で、これはスピルとか配列とかそういうのを超越したところにある関数なので、別枠扱いとする。

で、今回問題となる並べ替えを実現できるのはSORT関数とSORTBY関数で、普通にSORTBY関数がSORT関数の上位互換と思って良いだろう。

SORT関数は、列番号を数字で指定しないといけないとか、並べ替え項目を1個しか指定できないとか、悪いが欠点しか見当たらない。

SORTBY関数

では今回並べ替えに使うSORTBY関数だが、

という構成になる。

並べ替えの順序は、
1 : 昇順
-1 : 降順
だ。

それに従って、今回の表をD列で昇順に並べ替えるSORTBY関数を書くと

というようになる。

並べ替える列であるD列について
$D2#
と#マークが付いてるのは、このD列にスピル式が埋め込まれてるから自動的にこうなるというだけ。

で、下図のようにF2セル起点に式を埋め込んでみたけど、タイトル行は除いて範囲指定している。

タイトル行を除いて指定しないと、タイトル行まで並べ替えの範囲として巻き込まれるからだが・・・。

スピルの使い所が難しい

いやはやもう、関数を別途書くだけで面倒なのに、タイトル行を外して書かなきゃいけないし、外しっぱなしじゃ分かりにくすぎるから上図のF1:I1セルのように自分でタイトル行を追加してあげないといけないし・・・。

それに、関数を使った別の表を作るスペースまで必要になってるし・・・。

普通にA列～D列を並べ替えすれば良いだけのはずが、こんなことをしなければならないようでは、スピルで不便しか起きていないだろう。

このSORTBY関数も、そう気軽に使いまくれる関数でもあるまい。

計算式がある表で並べ替えを考えること自体が間違い？

スピルの入った表では並べ替えができないということで、「そもそも計算式の入った表で並べ替えをするべきではない」とでもいうことなのだろうか？

しかし、

• 項目ごと集計値をSUMIFS式で1列に出した表を作成し、その集計値を昇順・降順で並べ替えてみたい
• コードに対応する名称をVLOOKUP関数で1列に出力した表を作成し、その名前を名前順で並べ替えたい。

というのは、当然あることだ。

そしてそういった場合のSUMIFS関数やVLOOKUP関数にも本当はスピルを活用したいところだ。

しかしここまで見たように、並べ替え用途が絡む表ではスピルは使えない。

Excel 「スピル」機能について

2020年9月13日

スピルを使うのは、そういう並べ替え用途は決して発生しないと言い切れる場合に限られるということで、かなり使い所を限定されるということになる。

あるいは「並べ替えがしたければピボットテーブルに組み替えてからすれば良い」という考えもあるかもしれないが・・・

結論？

うーんなんだろう。

きちんとした体裁の表にして、他の人に配布もして恒常的に使用するなると、並べ替えは絶対に絡まないなどと言い切れることはまずあるまい。

「スピル式を入れてるから並べ替えはしないで下さい」なんてわけの分からない注文を相手に押し付けるようでは完全に駄目だ。

もうこうなるとスピルは、自分だけが使うファイル専用にするべきじゃないだろうか。

そして「単に答えを一回求めたらそれで終わり」的なもので、後で並べ替えるとか細かい分析まではしないようなものに使っていくべきなのではないだろうか。

いやもう、かなりスピルをディスったような記事になってしまったが、そのくらいスピルで並べ替えできないという事実には唖然としてしまったのだ。

少なくとも、スピルで並べ替えできないというその事実については、もっと大きくあちこちで取り上げてほしいものだ。