Excel の機能について検索していたら東京工科大学の先生が Excel に困ったバグが残っているというエッセイを書いているのを発見しました。
この先生は、Excel には Excel の参照式に所属するシート名を含めて関数を書くと、並べ替えの時に $ がなくても絶対参照として扱われるのはバグであり、いまだこんなバグが残っているのは困ったことだと書いています。
しかし私の考えでは、これは Excel のバグではなく意図的な仕様であり、必要だと思います。
この先生は、同じシート (Sheet1) 上のセルを参照するのにわざわざシート名 (Sheet1) を付けたうえで参照を書いています (例えば、Sheet1!B1)。しかし、普通はこんな参照を書きません。通常、参照にシート名を付するのは、別シートからそのシートを参照する場合です。
例えば Sheet1 に次のようなデータが入っていたとします。
これに対し、Sheet2 に以下の参照式を入れて、Sheet1 のデータを参照するとします。
すると Sheet2 には Sheet1 のデータがそのまま参照され、Sheet1 のデータがそのまま Sheet2 にコピーされたように見えます。
そこで、Sheet2 のデータを逆順になるように行を並べ替えてみます。
すると、意図通り並べ変わりました。
この時参照式がどうなったか見てみます。
参照式が確かに並べ変わっていますが、これはまさに東京工科大学の先生が言った通り、シート名を付けた相対参照式が並べ替えの際絶対参照扱いされたためです。仮に、並べ替えの際にそのまま相対参照扱いなら、並べ替えても、結局式は変わらないことになり、結局データも並び変わりません。つまり並べ替え自体が無意味になってしまいます。
従って、並べ替えを無効にしないためには、並べ替えの際にシート名を付けた相対参照式は、絶対参照扱いする必要があるのです。
ちなみに同じシート (Sheet1) 上に以下のように相対参照で式を入れた場合はどうでしょうか?
今わかりやすいように式を表示させていますが、実際には、式を入れた部分 (H1: L7) は、A1: E7 と同じデータが複写されて見えます。
そして式の部分 (H1: L7) を並べ替えてもデータは並べ変わりません。一旦式が並べ変わっても、相対参照のため、すぐに式が元と同じに式に戻り、データ表示は並べ替え前と変わりません。
この時式を =Sheet1!A1 というようにセル参照の前にシート名を付けておくと、相対参照にもかかわらず式はちゃんと並べ変わり、データの並びも変わります。
もちろん、式がちゃんと並べ変わってほしい場合は、式を絶対参照に書いておくことが原則、基本だろ、とこの先生は言いたいと思います。しかし、Excel 2019 以前では、複数のセル範囲の式を簡単に相対参照から絶対参照に変換することができません。複数の範囲の式を一斉に相対参照から絶対参照に書き換えるにはかなり面倒なことをしなければなりません。従ってこの仕様は必要なのです。
なぜ必要なのかその理由ももうちょっと詳しく説明すると... まず上の図の相対参照の式をワークシート上で入力する場合、以下の手続きになります。
まず、最初に以下のように入力します。
この式を左右にコピーしていけば、2つ上の図のように式が入力されます。
しかし、これは式が相対参照だからです。しかし式を絶対参照で入力したい場合、最初のコピー元の式が絶対参照ですといくらコピーしても、すべてのセルが同一の式になってしまい、全ての式が同一の値になります。当然のことですが。
全セルが 101 になってしまう
ということは、以下のような式を簡単に入力することができません。どうしてもやろうとするのなら VBA でスクリプトを書く必要があります。
そのため、シート名を付けたセル参照は並べ替え時に扱いを変える必要があるのです。
但し、Excel 2021 および それと同時に出た Excel 365 のアップデートでは、単独のセル同様、複数のセルを範囲指定して F4 キーを押すことで、そのセル範囲を一斉に参照方式を変更できるようになりましたので、一旦、相対参照の式を入れておき、範囲全体を指定してから F4 キーを押せば、上記のような式を簡単に入力できるようになりました。そのため原則に立ち戻った運用をやろうと思えば可能になりましたが...
ちなみに Libre Office Calc における、シート名をつけた参照の並べ替えの動作はどうでしょうか?
やはり Excel と同じ動作です。つまり、やはりシート名を付けた参照の、並べ替え時のこの動作はバグなどではなく、意図的な仕様なのです。
東京工科大学の先生に置かれましては、もうちょっとご研究いただくことを期待したいと思います。
ちなみにシート名を付けた参照の書式が、Excel と Calc では異なります。
Excel ですと =Sheet1!A1 という形ですが、Calc では =$Sheet1.A1 となります。したがって Excel の式を Calc のシートにそのまま貼り付けることができません。書式が指定されていないテキストの貼り付けを使うと、式の評価した値を貼り付けることはできるようですが...
さらに、Libre Office 7.6 では、複数のセルに相対参照式を入力してそれを一気に F4 キーで絶対参照に変換する、Excel では 2021 以降で可能な機能がしっかりサポートされています。これはさすがです。これは、Libre Office 3.x ですでに Shift + F4 で同様な機能がサポートされていたようです。Ver. 5.x で F4 だけでも OK になったようです。
Libre Office Calc はひょっとすると Excel よりもむしろ優れているかもしれません。もっとも多少操作体系が異なるので、慣れるまで時間がかかるかもしれませんが... 積極的移行を検討しても良いかもしれません。
