「知っていると得するかもしれないExcelの癖」シリーズ第十六回、テーマは「入力規則が無効になる」になります。
Excel 2016バージョン1903を使用して確認しています。
入力規則の設定されたセルに値貼付けされると、折角の入力規則が働かずに値が貼り付いてしまいます。
今回のテーマは、そのような場合ではなく、入力値の種類を「リスト」にした時の「元の値」の設定の仕方により、入力規則が働かないケースについてになります。
元の値をセル範囲にした時の動作を確かめる
C2セルに設定してある入力規則は、データの入力規則の画面の元の値を見ると、G2:G6のセル範囲を参照しています。
同様にB2の入力規則はF2:F4を、D2の入力規則はH2:H5をそれぞれ参照しています。
C2セルにセル範囲に値が設定されていない日付を入力すると問題なくエラーが表示されて、入力は制限されます。
入力規則の残念な動き
少し話題から外れますが、リスト入力規則に空白値を選択に含めるために、元の値で「 ,2019/4/10」のような半角スペースにカンマ(,)をつけてもプルダウンメニューには反映されません。
全角スペースであれば可能ですが表示形式が異なってしまう場合は採用することができません。
この場合は、上図の「その2」とか「その3」のようにセル範囲にすることでプルダウンメニューに空白値を選択項目として表示させることができます。
このようなテクニックは既に使われていることと存じますが、「その3」のように選択項目の最後の方に空白項目を設定してしまうと、プルダウンメニューを選択した時にプルダウンメニューの空白値にフォーカスが設定されてしまい少し不便です。
そのような時は、「その2」のように先頭に空白項目を設定すれば解消することができます。
その3のプルダウンメニューをクリックした時は最後にフォーカスが移動してしまう。
その2のプルダウンメニューをクリックした時は先頭に移動する。
話は変わりますが、このセル値と同じ値のプルダウンメニューの選択値にフォーカルをセットする機能ですが、セルの表示形式をセル範囲に入力した選択値の表示形式から変更してしまうと、機能しなくなりますので注意が必要です。
C2セルが「短い日付形式」であれば、フォーカスがセットされます。
C2セルが「長い日付形式」になるとフォーカスはセットされません。
どちらもちょっと残念ですよね…
元の値をセル範囲から「名前」に変更した時
話を元に戻します。
先ほどセル範囲の時は問題なく動いていた入力規則ですが、セル範囲に名前を付けて、それを「元の値」にセットした時の動きを確認して見ます。
一般的にはわざわざ名前を付けることは無い事と存じますが、入力規則に設定する値の元がテーブルだったりすると名前を使いたくなるケースがあります。
F2:F4は「その1」、G2:G6は「その2」、H2:H5は「その3」と名前を定義し、B2の元の値は「=その1」、C2は図３のように、D2は「=その3」と設定します。
B2は図４のように設定された値にない値がセルに入力された場合はエラーとなります。
これに対して、C2とD2では値が入力できてしまいます。
図５ではD3にカーソルがセットされていますので、D2でも入力規則が働いていないことが分かります。
空白値を含むセル範囲に名前を付けなければならない時の対処法
①空白値はあきらめて「その1」に変更する
セルに入力たり選択したりしてしまった場合は、削除キーでセルの値を削除してもらう運用ルールにします。
②将来的に選択値が増える可能性があるために、空白値を残して置きたい場合は半角スペースを埋めて置く
「その2」と「その3」の場合、空白値に半角スペースを1つ埋めて置くと、C2、D2でも入力規則は働きます。
元の値に半角スペースを直接指定することはできませんでしたが、セル範囲でのセルの中に半角スペースを埋め込むと、プルダウンメニューで半角スペースを選択して、セルに値をセットすることができます。
ただ、お分かりのように、これだとセルに半角スペースが入ってしまいます。
もしも、それでは困るのであれば、例えば表の最後に1列増やして入力規則が設定されたセルからスペースを削除する下記の数式を設定するのは如何でしょうか?
=TEXT(TRIM(C2),"yyyy/mm/dd")
上記は、対象セルが短い日付型の場合になりすが、セルの表示形式をTEXT関数で指定すれば、TRIM関数を使用しても表示形式を元に戻すことができます。
最終的に「その2」C列にはスペースを入れたくない場合は、上記の数式の入った列をコピーして、C列に値貼付けすればスペースは取り除かれます。
このような作業をすることが難しい場合は、半角スペースのみ入っているセルの背景色を設定する条件付き書式を設定するのは如何でしょうか?
例えばB列にこの入力規則が設定されている時は下記のような条件付き書式になります。
条件付き書式の数式
=B1=" "
適応先
=$B:$B
背景色のついているセルには半角スペースだけが入力されていますので、対象のセルを選択して削除キーを押せば、背景色は消すことができます。
「そんな面倒臭いことはしたくない」と言われてしまうかもしれませんが、諸所事情を勘案してどうしても必要があれば、このやり方を採用されるのも一考です。
なお、見栄えの問題から半角スペースを特殊文字にすることも可能ですが、CHAR関数でCHAR(13)は改行コードになりますが、それをセットしたとして、もしもその項目が選択されてしまうと、セルに目に見えない改行コードがセットされてしまい、後々トラブルを引き起こす要因にもなりますので、スペースの代わりに特殊文字をセットすることはお勧めできません。
この現象を利用したくなるケース
この現象を逆手にとると、入力規則によりプルダウンメニューから選択させながら、直接セルに入力させることもできようになります。
ただしそれをするのであれば、入力規則の設定で、「無効なデータが入力されたらエラーメッセージを表示する」チェックボックスを外すのが正規の方法です。
しかしながら、この正規の方法を選択した場合は、上記チェックボックスを外しているらも関わらず、下記のエラーインジケーターが表示されてしまうのです。
このセルの値が無効か、または見つかりません。詳細については「フィールド型情報の表示」をクリックしてください。
「フィールド型情報」をクリックして表示される画面は図６になります。
入力規則の「無効なデータが入力されたらエラーメッセージを表示する」のオプションを外する設定が、エラーインジケーターの仕様に反映されていないのが原因であると推測されます。
そうなると、エラーインジケーターを表示させなくするためには、この現象を利用するも一考であります。
まとめ
なぜ、セル範囲を名前に変えると入力規則が働かなくなるのか?、その理由が不明です。
なお弊社コンテンツの中で、選択肢をテーブルで設定する場合は、選択肢をある程度の範囲で最初に設定をしておきます。
そのためつぎのような場合分けによって対応を変えています。
- もしも選択肢以外の値の入力を許さないのであれば、空白値に半角スペースをセットすることで対応
- 許す場合は、エラーインジケーターを表示させないために、この現象を逆手にとった方法で対応
ただし最初の場合は、選択肢の半角スペースを誤って削除してしまうと、入力規則が働かなくなってしまうので、選択肢を入力していただく列の隣に非表示で1列追加をして、そこに下記の数式をセットします。
=IF(ISBLANK(G2)," ",G2)
この例は前段で使用した「その2」G列の右隣に1列挿入した時の数式になりますが、残りの列のセルには、テーブルであればオートコレクト機能により設定されますし、普通の表であれば、数式コピーをして貼り付けします。
その上で、挿入した列を使用して名前を定義すれば、必ずスペースが入った形で動くことになりますので、入力規則が働かないというトラブルは起こりません。
なお、細かな動作の話ではありますが、将来的にMicrosoftの開発者によって解消されることを期待いたします。
以上、最後までご一読いただきありがとうございました。
