Excelテーブルのオートコレクト機能で想定外な動きをした時

知ってたら得するかもしれないExcelの癖-その1
この記事は約4分で読めます。

知っていると得するかもしれないExcelの癖」シリーズ第十二回、テーマは「オートコレクト機能の例外」になります。
Excel 2016バージョン1902を使用して確認しています。
なお第十一弾の最後で一旦小休止すると書きましたが、その後で本事象を発見したため急遽追加させていただきました。


本シリーズ第九弾「Excelでテーブルに数式をセットする際の注意点」のなかで、「オートコレクト機能が働く場合と、働かない場合」について記述していますが、どっちつかずに機能が残るために間違った形でオートコレクトしてしまうケースを発見したのでお伝えしたく存じます。

スポンサーリンク

絶対参照なのに間違ったオートコレクトが設定される

数式表示の状態
この数式は0から始まる行番号を表示します。
数式により表示される値

数式、表示される値ともに同じになるように設定した、「No、列1、列2」の3項目からなるテーブルがあります。
ただ、それぞれの項目に対するオートコレクトの設定状態は異っています。

項目状態オートコレクト
No間違ったオートコレクトの動きをする状態。働いていない
列1B2セルに入力した数式がオートコレクトが働いて全行にセットされた状態働いている
列2オートコレクトは働かせずに、C2セルに入れた数式をC3:C6に数式コピーした状態働いていない

※A2セル、C2セルの数式を変更すると、次の行に「オートコレクトのオプション」アイコンが表示されますので、オートコレクト機能は働いていない状態です。

このテーブルに対して、3行目と4行目の間に1行、行挿入すると下記のような結果になります。

赤枠が追加された行になります。それぞれの列で値が異なります。
列2はオートコレクトが働いていないので空白になっていますが、これは正しい動きです。
問題はNo列で、ちなみにA4セルの数式は下記になっています。

=ROW()-ROW($A$3)

なぜか、絶対参照の設定が「$A$2」から変わってしまっています。
そもそもオートコレクトが働いていないので、列2のように空白になるのが本来の正しい動きになります。

今回の例では、挿入された行の中身を確認すれば間違いに気が付くことはできますが、これが複雑な数式であった場合は見過ごされてしまう危険性があります。

このような結果を引き起こす操作

最初はなぜこうなるのか?不明でしたが、本シリーズの「行を追加削除してもメンテナンスがほぼ要らない行番号を付ける」で最初の行を削除した時の動作を確認していたことを思い出しました。

そこでつぎのような設定をしたところ、上記の動きを再現することができました。

A列は2行目を参照し、比較のためにB列は1行目を参照する数式をテーブルにオートコレクトで設定します。
左図で2行目を行削除すると、A列は$A$2が参照できないため、数式には「#REF!」が表示されます。
誤りに気付いたので、A2セルを数式バーの赤枠のように修正します。
この時画面赤枠のオートコレクトオプションのアイコンが表示されているのですが、これを使わずA2セルをコピーしてA3:A5セルに数式コピーしてしまいます。これでオートコレクト機能は働かなくなります。

値としては正しく修正されています。ここで3行目と4行目の間に行挿入をしてみます。
挿入された4行目のA4セルの値が正しくない現象が再現しました。数式も絶対参照の$A$1が$A$2に変わっています。本来であればオートコレクト機能は働かないはずです。

まとめ

操作ミスと言われれば、その通りなのですが消されたはずの絶対参照の$A$2が、オートコレクト機能が働かないはずのところで、挿入時に亡霊のように蘇ってくる現象になります。

このような現象になる要因としては

  • #REF!に対する処置
  • オートコレクト機能に対する処置

このダブルエラーに対するリカバリー対応がExcelとして想定外なのでは?と推察されます。

従いまして、Excelがテーブル内で#REF!エラーを返した時の処置には十分な配慮が必要になると思いますが、まずは#REF!エラーが起こったら、それを起こした操作まで立ち戻ってもらい、やり直すのがルールとしては守り易いのでは?と考えます。

以上、最後までご一読いただきありがとうございました。