Excel。入力規則のリストでアイテム数が多すぎて、選ぶのが大変なのでどうにかしたい。
＜入力規則・名前の定義&INDIRECT関数＞
発注書や納品書など、入力ミスをすると致命的になりかねない書類って結構あるわけです。
そこで、VLOOKUP関数をつかったりしてミスを抑制するわけですが、さらに便利で入力ミスを抑制することができる、『入力規則のリスト』をつかうと便利ですね。
ただ、便利なのですが、リストに表示されるアイテム数が多いと、スクロールしなくてはならないし、探すのも大変。
結局入力したほうが早いというのでは、入力ミスを抑制する効果が減ってしまいます。
そこで、ジャンルやカテゴリーという区分けできるものがあれば、区分けするものを選択した後に、該当するアイテムだけをリストに表示できれば、入力する速度を改善でき、入力ミスも抑制することができます。
今回は、このような表を用意しました。
E:Fは、商品リストです。入力規則のリストを作る時には、E列の値をつかうわけですね。
そして、ポイントなのが、H列。
今回は、商品コードの頭文字で管理しているので、ジャンルとしてA・B・Dを用意しました。
A列にジャンルという列を設定しております。
入力規則のリストで、探しやすくするためで使う場合は、印刷時に外すようにするといいですね。
では、設定していきます。
A2:A5に入力規則のリストを設定します。
A2:A5を範囲選択して、データタブの「データの入力規則」をクリックします。
データの入力ダイアログボックスが表示されます。
設定の入力値の種類を「リスト」にして、元の値にH2:H4を範囲選択します。
絶対参照が自動的に設定されますので、あとはOKボタンをクリックします。
A2をクリックすると、▼のリストが設定されていることが確認できます。
B列の商品コードに入力規則のリストを設定したいのですが、このままでは、頭文字がAの商品だけをリストに表示することはできません。
名前の定義をつかって、このアイテムはAというようにわかるようにしていきます。
頭文字がAの商品を範囲選択します。
A2:A4を範囲選択して、名前ボックスにAと入力して設定します。
これで、名前の定義が完成しました。
同じように、BとDも設定します。
ところで、なんで、「C」じゃなくて「D」にしているのかというと、名前の定義。
CとRが予約語として設定されているんで、使えないんです。
あと、セル番地のような「AA1」などの名前も使えません。
なので、人に説明する時には、AとBまでにしておくとビックリしなくてすみます。
次に、A2にダミーデータをいれておきましょう。今回は「A」をいれておきます。
B列の入力規則のリストを設定していきます。
B2だけを範囲選択して、データタブの「データの入力規則」をクリックします。
データの入力ダイアログボックスが表示されます。
入力値の種類を「リスト」にして、元の値には、
=INDIRECT($A2)
という数式を設定します。
INDIRECT関数は、引数の文字自体を使うことができる関数です。
A2には、「A」が入力されていますので、Aと名前の定義した範囲を元の値としてつかうことができます。
また、引数の$A2は、このあと、フィルハンドルをつかって、セルをコピーするので、絶対参照にしてしまうと、常にA2を参照してしまうので、複合参照にしておく必要があります。
OKボタンをクリックします。
セルB2の設定をフィルハンドルでB5までコピーしたら、確認してみましょう。
B列の▼をクリックして、ジャンルに該当するアイテムしかリストに表示されていません。
今回のように、INDIRECT関数と名前の定義を組み合わせて入力規則のリストを設定すると、2段階式の入力規則のリストをつくることができます。
