Excelで入力規則のリストを別シートに設定する

Blog Admin
Blog Admin

Excelの入力規則で、リストは別シートに隠してしまいたいという依頼をいただきました。
結論から言えば、可能です。
設定もそこまで難しくないのですが、知っておくべき要素がややマイナーです。

入力規則
別シートのリストを使う

マイナー機能かどうかはさておき。
やるべきことは以下の2点です。

  1. 名前の定義
  2. 入力規則

たったこれだけですので、分かる方は流し読みで。
今回初めてという方も、まずはやってみて、ぜひ活用してみてください。

今回の確認は、Microsoft 365版で実施しています。
Excel 2010以降は、操作系に大差ないと思いますので、各バージョン共通という認識です。

元の値を名前にする

Excel

やることは2つと書きましたが、作業ステップは3つにします。
つまり、準備 → 設定1 → 設定2 です。
とはいっても、今回の例題を作るには1分もかからないレベルだと思います。(慣れればですが)

空のブックを準備して、Sheet1とSheet2がある状態を前提とします。
Sheet1の任意のセルに入力規則を設定します。Sheet2の任意のセルにリストを作成します。
これらは自身の環境に合わせて、適宜読み替えてください。

最終形のイメージは以下のとおりです。

リストから選択
これを作ります

候補リストを作る

Sheet2に候補リストを作ります。
例のとおりですが、以後、管理しやすいようにタイトルを入れています。
見た目は大事ですよ!

Sheet 2

タイトルをつけておくと、名前の定義の際にも便利なのです。

これで準備完了です。

名前を定義する

今回は、A1からA4を選択した状態で名前を定義します。

名前の定義

(選択状態で)「数式」タブ → 名前の定義

名前の定義
名前の定義

「名前の定義」をクリックすると、「新しい名前」ウィンドウが出てきます。

新しい名前

A1はタイトル用に選択していたので、「参照範囲」の右側のボタンをクリックして、A2からA4に変更しておきます。
OKを押したら、「List」という名前が定義できました。

「名前」は見えにくく、乱立させると管理しきれないことにも繋がりかねません。
分かりやすい名前を定義しましょう。

入力規則を設定する

Sheet1の好きなセルに、入力規則を設定します。
「入力値の種類」を「リスト」とします。

リスト

「元の値」に、先ほど定義した名前を設定します。

=List

半角イコールをお忘れなく。
これで設定は完了です。

リストを変更するには

元のリストを編集するだけです。
候補数が変わらないときは書き換えでよいのですが、候補数の増減がある場合には、挿入や削除を活用します。

リスト

名前の定義で設定した範囲は、数式の指定のように、挿入や削除に合わせてExcelが自動調整してくれます。
ドルマーク($)は、相対位置を変えないという意味で、範囲を絶対値として固定するものではありません。

上の例では、BとCの間に2行追加して、C、D、Eを書き換えました。

維持管理も考えてあげてください

別シートに候補リストがあると、ブック全体の見とおしは悪くなります。
名前の定義も確認するステップが多くなり、管理が難しいです。

個人で使っているだけのものであれば、いくらでも好きなように設定を入れてしまえばよいのですが。
お仕事で使うものであれば、チームや取引先との関係もあると思います。
担当が変わって引き継ぐことになるかもしれません。
自分が使いやすいものでも、他人には謎な可能性もあります。
ここで調べたことは、さっきまで知らなかった(忘れていた?)ことなのですから!

Excel

なんだか分からないけれど、修正方法が謎だから、不便でもそのまま使い続けているとかいうことにならないよう。。
ゾンビエクセルを作り出さないように、分かりやすくしてあげてくださいね。

この投稿を書いたのは・・・
Blog Master

ガジェットが大好きで、毎月何かしら買っております。
無駄遣い扱いされたくないのと、何かの役に立つかと思い、記録を書くことにしたのでした。

お出かけのときには、スマホを複数台とタブレットとパソコンと、Pocket WiFiを持ち歩きます。
両手首にはスマートウォッチです。
こんなスタイルで生活している中での備忘録を書いています。

「Blog Master」をフォローしてみる
Excel関連
「Blog Master」をフォローしてみる
wnkhs.net

コメント

タイトルとURLをコピーしました