この記事では、エクセルのプルダウンにて隣のセルや別シートを連動させる方法を解説していきます。
プルダウンを連動させる方法は以下の４ステップです。
・名前の定義をつける
・テーブルの設定
・入力規則の設定
・INDIRECT関数の設定（隣のセルや別のシートなど参照）
では実際のサンプルを用いて詳細を見ていきましょう。
エクセルのプルダウンにて隣のセルを連動させる方法
今回は文具、飲料、お菓子とカテゴリが変わるごとに商品内容が変わるプルダウンを作成していきます。
・文具、飲料、お菓子＝大項目
・それぞれの商品＝小項目
となっています。
表とデータはこちらを使用していきます。
名前の定義
初めに名前の定義をしていきましょう。
名前の定義とは、任意の範囲に名前を付けて関数の引数や、名前を付けた範囲を見つけやすくする操作です。
カテゴリである文具、飲料、お菓子を選択します。
左上にF1と書かれているボックスがあります。これを名前ボックスといいますが、ここにカーソルを立てます。
青色になったらbackspaceキーで消し、任意の大項目を入力しましょう。
今回はプルダウンを表示する表に添って“カテゴリ”と入力し、Enterキーで確定します。
これで名前の定義ができました。
名前の定義ができているか確認するため、一度名前ボックスを見てみましょう。
入力した名前がリストの中に入っていたら操作はきちんとできています。
テーブルの設定
次に、テーブルを設定していきます。
テーブルを設定するメリットとしては、小項目が今後増えても自動でリストに追加してくれるので、増えるたびに設定しなおす手間をなくすことができます。
では大項目含む小項目を選択し、挿入タブのテーブルを選択します。
テーブル作成のダイアログボックスが出ます。
“先頭行をテーブルの見出しとして使用する”にチェックがあることを確認してOKボタンを押します。
するとカテゴリ名の右側に▼のフィルターがついたテーブルに変換されます。
カテゴリごとにすべてテーブルを設定しましょう。
フィルターはテーブルデザインタブの真ん中“フィルターボタン”のチェックを外すとなくなります。
これでテーブルの設定ができました。
見やすいようにカテゴリのフォントの色を変えています。
のちに関数で使用するので、テーブル名を変更しておきましょう。
テーブルデザインタブの一番左、テーブル名をカテゴリ名に変えます。
ボックスにカーソルを立て、”テーブル１“から”文具“に変更しました。
入力規則の設定
ここからはプルダウンを表示させるために入力規則を設定していきます。
はじめにカテゴリの設定をしていきましょう。カテゴリを表示したいセルを選択し、データタブの“データの入力規則”をクリックします。
するとデータの入力規則のダイアログボックスが表示されます。
次に、種類にて「リスト」を選択します。
元の値を入力します。
ボックスにカーソルを立て、数式タブ→数式を使用から「カテゴリ」を選択しましょう。
すると、“=カテゴリ”が挿入されました。OKボタンを押して確定しましょう。
入力規則を設定したセルにカーソルを合わせると▼が現れました。
▼を押すとプルダウンのリストが表示されるようになっています。
INDIRECT関数の設定（隣のセル）
カテゴリと同じように入力規則を設定していきますが、今回は関数を組み合わせていきます。
まず商品名のプルダウンを表示するセルを選択し、データタブからデータの入力規則をクリックします。
入力値の種類は“リスト”を選びます。
元の値に=INDIRECT(B2)と入力します。
B2のところはセル参照をしても良いですが、デフォルトで絶対参照がつくのでF４キーを3回押して“＄マーク”を消しておきましょう。
そしてOKボタンを押します。
INDIRECT関数とは、参照したセルの文字列を返す関数です。
数式は、
=INDIRECT(参照文字列,参照形式)
今回はテーブル名をカテゴリ名に変更したので、カテゴリ名が定義されているテーブルを参照するようになる仕組みになっています。
このようなポップアップが出たら「はい」を押しましょう。
これは、大項目がまだ選択されていないために出るので問題ありません。
カテゴリを選ぶと商品が表示されるようになります。
別のカテゴリからは商品名が変化しているのがわかります。
テーブルを設定している為、商品名を足しても自動でリストに追加されます。
エクセルのプルダウンにて別シートのセルを連動させる方法
続いて、プルダウンにて別シートのセルを連動させる手順もご紹介します。
基本は隣のセルを連動させる方法と同じです。
違いは入力規則の設定時に別シートを使うところのみです。
まず、名前の定義をしましょう。
次に挿入タブ→テーブルから、地区ごとにテーブルの設定をしていきます。
テーブル名の変更もしましょう。
入力規則の設定をしていきます。データタブ→データの入力規則からダイアログボックスを出しましょう。
“リスト”を選択、元の値に数式タブ→数式を使用から“地区”をクリックしOKボタンを押します。
次に別シートの入力規則を設定します。
設定範囲を選択し、データタブ→データの入力規則を選択します。
ダイアログボックスが出たら“リスト”を選びます。
元の値に、=INDIRECT(地区別名簿!B2)と入力、またはセル参照をします。
「地区別名簿!」と入っていることで別シートを参照していることになります。
入力出来たらOKボタンを押しましょう。
これで別シートのプルダウンを連動させることが出来るようになりました。
まとめ エクセルのプルダウンにて隣のセルや別シートを連動させる方法
今回の記事では、エクセルのプルダウンにて隣のセルや別シートを連動させる方法について解説していきました。
プルダウンを連動させる方法は以下の４ステップです。
・名前の定義をつける
・テーブルの設定
・入力規則の設定
・INDIRECT関数の設定
プルダウンは入力ミスを防ぐことや入力を短縮することで業務効率をあげることができる便利な機能です。
一度設定すれば入力が簡単になるのでぜひ使ってみてください。
