例外を認める入力規則
Excelの「小粋なテクニック集」的な記事を書くとき、昔からよくご紹介する鉄板ネタなんですけど、そういえば、まだコンテンツとして書いていなかったので書きます。もちろん、実務で役立つケースもあります。ちなみに私は、ときどき使います。
セルに入力規則の「リスト」を設定しました。よく使う値は、リストから簡単に選択できます。便利ですね。もちろん、リストにない値を入力しようとすると
拒否られます。まぁ、これは見慣れた光景ですね。ご存じの方も多いでしょう。しかし、実務にはいろいろなケースがあります。「何度も入力する値は、毎回手入力すると大変なのでリストから選択したい。でも、ときどき、リストにない新しい値を入力したいこともある。ふだんはリストを出すんだけど、リストにない値も入力できるようにしたい」なんてこと、ありませんか？これ、実は簡単にできるんです。
入力規則を設定するときに使う[データの入力規則]ダイアログボックスなんですけど、ここに[エラーメッセージ]タブがあります。ここは「指定した規則に違反した値が入力されたとき、どーするか？」を決める設定項目です。[スタイル]リストを見てください。ここ、標準では"停止"が選択されていますが、ほかにも"注意"と"情報"があります。この選択肢って「表示するアイコンを変える」機能だと思っていませんか？違うんですよ。アイコンを変えるだけじゃ、ありません。その後の動作自体が変わるんです。それぞれの違いをご覧ください。
【停止】
【注意】
ボタンの数と種類が変わりました。ここで[はい]ボタンをクリックすると、リストにない値(ここでは"田中")を入力できます。
【情報】
今度は[OK]ボタンと[キャンセル]ボタンです。もちろん、[キャンセル]ボタンをクリックすれば、入力のやり直しになります。でも[OK]ボタンをクリックすると、リストにない値であっても、普通に入力できます。
つまり、スタイルの"停止"では、入力させません。入力という操作を停止する設定です。"注意"は、入力させないのではなく「それって、規則に違反してますよ」と注意を促すだけです。注意するだけなのですから、その後の動作はユーザーの自由です。標準では[いいえ]ボタンにフォーカスが当たっていますが、ユーザーが「いいんだよ、分かってるから！入力させてくれよ！」という意味で[はい]ボタンをクリックすると、何でも入力できちゃいます。最後の"情報"は、一番緩いです。注意ですらありません。ただ「規則に違反している」ということを、情報として伝えるだけです。なので、普通に何でも入れられます。実は入力規則機能って、こういう例外に対する処理も考慮されていたんです。ちなみに、[タイトル]と[エラーメッセージ]には好きな文字列を入力できますので、ダイアログボックスの表記も自由自在です。さらに、[無効なデータが入力されたらエラーメッセージを表示する]チェックボックスをオフにすると、こうしたメッセージすら表示されません。
新規データもリストに追加する
ここからは、オマケです。上記のように「いつもはリストから選択するけど、ときどき新しいデータも手入力したい」ようなとき、どうですか？新しく入力したデータって、次回のリストには表示されてて欲しくありませんか？たとえば、こんなやり方もありますよって方法をご紹介します。
上図のケースで解説します。まず、入力するA列とB列の表は"テーブル"にしてあります。テーブルの名前は「Data」です。「テーブル1」だと長いので短くしました。ここでは、ある程度の名前が入力されているとします。ああ、A列の日付は関係ありません。名前の列だけだと寂しいので作っただけです。
まず、[名前]列に入力されているデータ群から「重複しないユニークリスト」を作成します。それには、UNIQUE関数を使います。
UNIQUE関数の結果を、入力規則のリストに設定します。
なお、ここでは先に解説した[無効なデータが入力されたらエラーメッセージを表示する]チェックボックスをオフにしてあります。なので、リストにないデータも好きに入力できます。さて、新しいデータを追加してみましょう。
ここで、"田中"など、リストにないデータも入力できるのですが、ちょっと待ってください。UNIQUE関数の結果を見ると、最後に「0」が追加されてしまいました。当然、入力規則のリスト項目も、最後が「0」になっています。まぁ、関係ないっちゃ関係ないし、「別にいいじゃん」というO型ならいいのですが、こういう細かいことが気になるA型だと困ります。この「0」を表示したくないです。こんなときは、FILTER関数を使います。
この状態で、リストにないデータを手入力します。
すると、次のリストからは、新しく手入力した"田中"もリストに表示されるようになります。
オマケなので、このくらいにしておきます。だけど、書いてて思ったんですけどｗ だったら何も、入力規則のリストを使わなくたって、新しい名前を入力するところで、Alt + ↓矢印で「すでに入力しているデータリスト」を出して、そこから選択した方が早いかってｗ まぁ、こんなこともできるというオマケです。