Excelでエラーインジケーターを表示させない工夫

「知っていると得するかもしれないExcelの癖」シリーズ第十三回、テーマは「エラーインジケーターを減らす」になります。
Excel 2016バージョン1902を使用して確認しています。

1週間もたたずに再開しますことご容赦ください。
本来はコンテンツ作成に注力するために、一旦小休止したいと考えたのですが、コンテンツを作成していると、「あんな事もあるし、こんな事もある」と言った具合にいろいろとお伝えしなければならない事が発生してしまいます。

それらをあまり溜め込み過ぎるのも良くないというか、後でまとめて書くのは大変な事になりますし、「コンテンツを充実させるのが先か?、記事を増やすのが先か?」という鶏と卵の関係のような話の中で、そんなに先を急がなくても結局はお客様に来ていただいて、満足していただける充分な話題が揃っていなければ「元も子もない」結果になることと存じます。

従いまして、今後は小休止するのではなく両方を順次進めて参る所存です。

エラーチェックオプションの確認
文字列設定のセルに数値を入力する場合
セル範囲を数式に設定する時の注意点
まとめ

エラーチェックオプションの確認

前置きはさておきとして、エラーチェックオプションの設定は「セル左上隅のエラー表示を有効に使う」のなかで記載しておりますが、「エラーチェックルール」の設定部分だけ下記に再掲いたします。

左図ルールの中で、チェックを外さなくても数式や値の持ち方を工夫すればエラーインジケーターが表示されなくなるケースがありますので、減らすための心得をまとめて置きたく存じます。

文字列設定のセルに数値を入力する場合

「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています。」
このメッセージは「数値のセルにアポストロフィで始まる数値が入っている」ケースではとても有難いのですが、その逆の「文字列設定のセルに数値型の値が入力されている」ケースでも表示されてしまうのが「たまにきず」のエラーインジケーターになります。

上図の「文字列形式の数式、またはアポストロフィで始まる数値」のチェックボックスを外せば表示されなくなりますが、それはお勧めできないというか、エラーインジケーターとは仲良く共存すべきであると本サイトでは考えます。

①数値を入力した後に、表示形式を文字列に設定する

まあ、「論外でしょ」とは言われると思いますが、始めからセルに入力されている数値の表示形式を文字列形式にしてもエラーインジケーターは表示されません。

ただお分かりのように、このやり方はゼロから始まる数値には対応できません。
しかしそうではありますが、ゼロで始まらないのであれば、このやり方はベターになります。

②数値の最後にマイナスを付ける

これも、「そもそものコードと違う」と言われてしまうので駄目だとは思いますが、数値の最後にマイナス記号を付けるだけでエラーインジケーターは表示され無くなります。

③数値の最後にASCII 非印字制御文字を付ける

見た目を変えたくないということであれば、このような方法も考えられます。
ただ、制御文字をショートコードでキーボードから入力できれのであれば実用化の可能性はあると思いますが…

Altキーを押しながらテンキーで「13」(13はCRで行頭復帰の制御文字です)

上記の操作をしても残念ながら制御文字は挿入できませんでした。
ちなみに13以外に、2(テキスト開始)、9(水平タブ)、28～31(各種区切り)も確認して見ましたが、やはり駄目でした。

キーボートで入力できないとすると、例えば”0001″を文字列として入力するのであれば、まず下記の数式を適当なセルに入力します。

="0001"&CHAR(13)

数式のセルをコピーして目的のセルに値貼付けをすれば、制御文字を含めて値が貼り付きますので、文字列と認識されてエラーインジケーターは表示されません。
ただし、この文字列を例えばVLOOKUP関数などで参照する場合は、”0001″だけでは「#N/A」になってしまいますので、次のように設定する必要があります。

=VLOOKUP("0001"&CHAR(13),セル範囲,列番号,FALSE)

いちいち「&CHAR(13)」を付与するのはとても現実的では無いですよね…

解決にはルールが必要

思いつく範囲ではExcelの機能だけで、この問題を解消するのは難しい認識です。

となるとルールを決めて運用することで「対応の負荷を減らす」しか手段はありません。
なお一般的に文字列として扱いたい数値はコード類になるかと思いますので、コードに関するルールとして記述します。

コードは数値だけにしない。最初にアルファベット文字を付ける。アルファベットもキーボードで打ちやすい「a、s、z、x」などにする。
コードにエラーインジケーターが表示されていても「エラーを無視する」操作はしない。

「減らす」というテーマからは外れてしまうのですが、操作をしないことで負荷を軽減できると思います。

なお「ルールがあるからミスが起こる」という主張もありますが、「ミスがあったらルールがある」というのも事実です。これも「卵が先か、鶏が先か」の話になりますが、最終的に「負荷なくミスを減らす」ことが望まれる姿であると考えます。

とは言いながらできることであれば、Microsoftの開発者が「文字列設定のセルに数値を入力する場合」と「数値のセルにアポストロフィで始まる数値が入っている」場合とでエラーチェックオプションを分けてくれることが望まれます。

セル範囲を数式に設定する時の注意点

話題は変わりますが、例えば1月から6月の数値が行で入っている表があり、集計列に1月から6月の合計を計算するSUM関数がセル範囲の数式で設定されているとします。

イレギュラーな作業として1月から3月の3ヶ月分の集計を頼まれた時に、集計列のセル範囲を1月から3月に修正すると、集計列にエラーインジケーターが表示されます。
数式表示の画面キャプチャーを下図に掲示します。

一旦エラーインジケーターの話から外れて、セル範囲の動きを整理致します。
なおテーブルだと一般的ではないかもしれないので、普通の表で動きを確認してみます。
ちなみに4月から6月はまだ未入力であるとします。

「このセルにある数式は、隣接したセル以外の範囲を参照します。」

このエラー表示の意味としては「セル範囲として、除かれている数値項目を含めなくても良いのですか?」という指摘だと推測されますが…

この状態で、2行目の6月
3～4行目の4月に数値を入力して見ます。

ご存知かもしれませんが、1月から3月のセル範囲の合計を数式に設定している時に、隣接する4月に数値を入力すると、セル範囲が自動拡張されます。
左図では2行目だけがA:Dに変わります。

3～4行目のような場合は拡張されていません。

ちなみに上図の状態でE2セルに数値「5」を入力すると、G2セルにもエラーインジケーターが表示されます。

2行目の集計列はエラーインジケーターは表示されず、セル範囲はそのままです。

それに対して3～4行目の集計列にはエラーインジケーターが表示されますが、セル範囲はそのままです。

上記の動きから解ることとしては、「隣接していないセルに数値を入力した場合は、セル範囲の自動拡張はされず、また一度拡張されなかったら、後から隣接するセルに数値を入力しても、再び自動拡張することはありません。」となります。
そして自動拡張されなかった時にはエラーインジケーターが表示されます。

ここで最初のテーブルの図に戻って考えてみたいと思います。
あの図で表示されたエラーインジケーターの意味としては、『「設定されているセル範囲は自動拡張できるけどしていません」という事を表示しているのだ』、と考えるのが正しいと思います。

直前の図の場合は「3行目は、おそらくC→E、4行目はC→Dへの自動拡張はできるけどしていない」ことを表していいると考えると何となく、このエラーインジケーターが意味する事が見えてくる気がします。

ここで本題に戻って「エラーインジケーターを表示させない工夫」としては、「SUM関数のセル範囲の指定の仕方」を考慮するになります。
この考慮とは、相対参照を絶対参照に変更することになります。

1月から3月までの範囲のセル範囲の指定として、3月のセルは列を絶対参照にすることで、自動拡張は必要ないことをExcelに明示する(理解させる)ことができます。

このようにすると、左図のようにエラーインジケーターは表示されなくなります。

なお、この状態だと数式を確認しないと、集計がどこの月までされているのか判断できません。
そこで、もしも可能であれば3月と4月の間に1列挿入すると、先ほどと同様にエラーインジケーターは表示されなくなりますので、このやり方も一考ですし、本テーマからは外れますが、エラーインジケーターをそのまま残して置くのも一考です。

まとめ

Excelの30年を超える歴史のなかで培われた、いろいろな知見がエラーインジケーターには詰まっている認識です。

世の中のAI化がもう少し進めば、エラーインジケーターの機能も今よりは賢くなるものと思いますが、「それまで使わない」というのもひとつの考え方ではありますが、現時点で少しでも有効活用できる方法を考えるもの大切な姿勢です。

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

【追記】(2019/4/15)
p.s.
少し特殊なケースとなりますが、入力規則を設定した際に表示される「このセルの値が無効か、または見つかりません。詳細については「フィールド型情報の表示」をクリックしてください。」のエラーインジケーターを表示させない工夫につきまして、「リストの「元の値」の設定方法により入力規則が働かないケース」に記載しましたので、合わせてご参照いただければ幸いです。//