目次
- 「無効データのマーク」に存在する“255個の壁”
- 入力規則の基本 リストとは？
- 別のシートの値をリストに反映する方法
- 「空白を無視する」の意味
2021.01.03
Excelの「入力規則」の使い方。ほかのシートを参照する方法は？
みなさんはExcelテクニックをどれだけ使いこなせている自信がありますか？
仕事のなかでも再頻出のツールの一つですが、その機能のほとんどは十分に発揮されず眠っている現状があります。
そこで、毎月ビジネスパーソンに向けて明日から使えるExcelテクニックをレクチャーするのが「田中亨先生がみんなの悩みを華麗に解決する『今月のExcelテクニック」」日本人で初めてExcel技術のMVPをMicrosoftから授与された田中亨（たなか・とおる）先生から直接Excelテクニックを学べる人気授業シリーズです。
9月のテーマはExcelの「入力規則」について。「入力規則に反している際に『無効データのマーク』を使用しているが、該当データが255個以上ある場合にエラーが出てしまって困っている」という受講生の悩みを受けて設定されました。
そもそも入力規則とはという基礎から機能の持つ穴まで学べる授業の内容を抜粋してお届けします！
「無効データのマーク」に存在する“255個の壁”
入力規則の基本 リストとは？
つづいて話は入力規則の基本に移ります。そもそも入力規則を使うのは、意図に沿わない入力値をあらかじめ排除するためです。
例えば人数がマイナスになることや「田中」や「鈴木」といった単語になることは基本的にあり得ません。そこであらかじめ0以上の数字しか入力できないように設定するというわけです。
もう一つ入力規則に付随する機能としてよく使われるのが「リスト」です。「田中」「広瀬」「桜井」と各セルに名前が入力されたエクセルシート。同じ文字を何度も打つのは非効率です。そこで、「入力規則」において、入力値の種類を「リスト」、元の値を「田中」「広瀬」「桜井」が入力されたセルに設定します。そうすると、簡単にプルダウンメニューから選択して任意の名字を呼び出せるリストが作成されました。
また、元の値の部分に「,（カンマ）」で区切って入力することでセルにない単語をリスト化することも可能です。ちなみに、この元の値として入力できる文字数もカンマを含めて255文字未満に限定されるということです。
「この機能は実務でよく使うから覚えておいた方がいい」と先生。リストにもしも新たな値を追加したい場合には、Excelのテーブル機能を使いましょう。「元の値」として参照しているセルをあらかじめテーブル化しておけば、新たにテーブルに追加した値はリストに自動で反映させられます。
基本的にリストはテーブル化しておくことが推奨されるようです。
別のシートの値をリストに反映する方法
別のシートに入力された値を参照して、リストを作成したいという場合もあるでしょう。そんなときはテーブルに名前をつけましょう。例えば「Data」とテーブルに名付けた場合、別のシートでリストを作成する際「元の値」に「=Data」と入力すれば、参照元のテーブルの変更を反映させることが可能になります。
ここで「元の値に関数が使えませんでした」というリアルタイム受講生からのコメントが。先生は「使える関数と使えない関数があります」と返答します。関数が二次元配列の値を返すことで齟齬が生じるという仕組みがあるようですが、そこまでは実務で押さえる必要はなく、「できないものもある」ということだけ覚えておけばいいそうです。
便利な関数をそれでも使いたいという場合もあるでしょう。そんなときは、ここで紹介したように、作業用のワークシートに関数を設定し、そのワークシート内に作成したテーブルを名前で指定して参照するというやり方が有効です。
「空白を無視する」の意味
つづいて先生がフォーカスしたのが「空白を無視する」という設定。「ちゃんとわかっている人はほとんどいないと思う」と田中先生は話します。
空白のセルを含む範囲（東京,神奈川,【空白】,大阪）に名前を付け、リストの「元の値」として呼び出せるようにしました。ここで、入力規則が設定されたセルにリストにない値「福岡」を入力することは通常であればできないのですが、この場合は可能になってしまいます。それは“Excel内部で【空白】を特殊なものとして扱ってしまっているから”。
そこで使うのが「空白を無視する」です。チェックを外して再度「福岡」を入力しようとすると「この値はこのセルに定義されているデータ入力規則の制限を満たしていません。」と警告文がポップアップされました。
ここで、「リストが長くなったときカーソルを置けるようにわざとブランクを入力する」というTipsがリアルタイム受講生から共有されました。先生は、決まっていないという選択肢をリストに含めたい時のように「ブランクを選ばせたい」ときもあると話します。このように使い道がありつつも、「わかりにくい設定のひとつです」ということです。
また「値の貼り付け」などの機能を使えば入力規則にない数値を入れることが可能になってしまうという機能の穴もあると先生。このようにExcelを知り尽くした視点ならではの「Excelというソフト」自体への批評が聞けるのもこの授業の醍醐味です。
文＝宮田文机
今回ご紹介した授業はこちら！気になった方はぜひチェックしてみてください。
『田中亨先生がみんなの悩みを華麗に解決する「今月のExcelテクニック」 第24回 田中亨先生が教える「今月のExcelテクニック」（2020年9月）』http://schoo.jp/class/7116/room
最初の教材として提示されたのが「数字の4」が264個入力されたシート。現在入力規則は設定されていません。「ここに後から入力規則を設定することもできるんですよ」と田中先生。リボンの「データタブ」を選択し、データツールのグループに含まれる「データの入力規則」のボタンをクリックします。
そこで「入力値の種類」として「整数」を選択し、「1～3の値の間の数値」しか入力できなくなるように入力規則を設定します。あらかじめ入っているのは4ですから、この条件には当てはまりません。
こんなとき、無効なデータをチェックするために使うのが「無効データのマーク」機能。データの入力規則の下に表示される同機能の呼び出しボタンをクリックします。
そうすると、「ワークシートには255個以上の無効なセルが含まれています。最初の255個のセルだけマークされます。」と警告がポップアップされました。これが冒頭で触れた、受講生の悩みのタネです。
「OKボタン」を選択すると、254個までの「4」がすべて赤い丸で囲まれました。しかし、255個目以降の4には変化がありません。
そして、「この問題を解決手段はありません」と田中先生。マクロ・VBAを使えばできる可能性もありますが、かなり面倒な作業が必要になるため実質不可能と考えた方が良いそうです。