「知っていると得するかもしれないExcelの癖」シリーズ第十二回、テーマは「オートコレクト機能の例外」になります。
Excel 2016バージョン1902を使用して確認しています。
なお第十一弾の最後で一旦小休止すると書きましたが、その後で本事象を発見したため急遽追加いたしました。
本シリーズ第九弾「Excelでテーブルに数式をセットする際の注意点」のなかで、「オートコレクト機能が働く場合と、働かない場合」について記述していますが、どっちつかずに機能が残るために間違った形でオートコレクトしてしまうケースを発見したのでお伝えしたく存じます。
絶対参照なのに間違ったオートコレクトが設定される
この数式は0から始まる行番号を表示します。
数式、表示される値ともに同じになるように設定した、「No、列1、列2」の3項目からなるテーブルがあります。
ただ、それぞれの項目に対するオートコレクトの設定状態は異っています。
| 項目 | 状態 | オートコレクト |
| No | 間違ったオートコレクトの動きをする状態。 | 働いていない |
| 列1 | B2セルに入力した数式がオートコレクトが働いて全行にセットされた状態 | 働いている |
| 列2 | オートコレクトは働かせずに、C2セルに入れた数式をC3:C6に数式コピーした状態 | 働いていない |
※A2セル、C2セルの数式を変更すると、次の行に「オートコレクトのオプション」アイコンが表示されますので、オートコレクト機能は働いていない状態です。
このテーブルに対して、3行目と4行目の間に1行、行挿入すると下記のような結果になります。
赤枠が追加された行になります。それぞれの列で値が異なります。
列2はオートコレクトが働いていないので空白になっていますが、これは正しい動きです。
問題はNo列で、ちなみにA4セルの数式は下記になっています。
=ROW()-ROW($A$3)
なぜか、絶対参照の設定が「$A$2」から変わってしまっています。
そもそもオートコレクトが働いていないので、列2のように空白になるのが本来の正しい動きになります。
今回の例では、挿入された行の中身を確認すれば間違いに気が付くことはできますが、これが複雑な数式であった場合は見過ごされてしまう危険性があります。
このような結果を引き起こす操作
最初はなぜこうなるのか?不明でしたが、本シリーズの「行を追加削除してもメンテナンスがほぼ要らない行番号を付ける」で最初の行を削除した時の動作を確認していたことを思い出しました。
そこでつぎのような設定をしたところ、上記の動きを再現することができました。
まとめ
操作ミスと言われれば、その通りなのですが消されたはずの絶対参照の$A$2が、オートコレクト機能が働かないはずのところで、挿入時に亡霊のように蘇ってくる現象になります。
このような現象になる要因としては
- #REF!に対する処置
- オートコレクト機能に対する処置
このダブルエラーに対するリカバリー対応がExcelとして想定外なのでは?と推察されます。
従いまして、Excelがテーブル内で#REF!エラーを返した時の処置には十分な配慮が必要になると思いますが、まずは#REF!エラーが起こったら、それを起こした操作まで立ち戻ってもらい、やり直すのがルールとしては守り易いのでは?と考えます。
以上、最後までご一読いただきありがとうございました。
