Excelの連動プルダウンの作成 INDIRECT関数の限界を突破せよ
目次
連動プルダウンの作成
こんにちは。伊川です。
今回は、連動プルダウンの作成について説明します。一般的な方法や、基本書に記載があるのは、INDERECT関数とセルに名前をつけて連動プルダウンを設定する方法があります。
しかし、この方法は、メンテナンスが非常に面倒であり、休み明けに会社に来てみると、作成した連動プルダウンが、グダグダになっていることがあります。そんな、非効率的なことは、この記事を読んだら、サクっと解決します。
この記事を読んで欲しい人
・INDERECT関数、セルに名前をつけないで連動プルダウンを作成したい人
・連動フルダウンは作成できるが、社内にメンテナンスをできる人がいない
使用する関数
OFFSET関数 COUNTA関数 MATCH関数 この３つの関数を使います。
この中で、OFFSET関数は、Excelの中で、結構難しい関数の部類に入ると思いますが、まずは、私の真似をしてください。
完成図はこんな感じになります。
セルB2にガンダムが選択されたら、ガンダムの登場人物が連動しています。
このシートの名前は【入力】です。
セルB2に仮面ライダーが選択されたら、仮面ライダーの人物が連動しています。
このシートの名前は【リスト】です。
通常のプルダウンであれば、項目が増えると、範囲の再設定が必要です。いつも、月初に、私は舌打をしながら、プルダウンの範囲の再設定をしていました。
項目が増えても再設定が不要なプルダウンの設定方法
- Excelのテータタブをクリック
- 【データの入力規則】をクリックすると、3つのメニューが表示されます。
- 【データ規則入力】をさらにクリックします。
そうすると、下記のような画面が表示されます。
下記のように変更します
変更後、赤枠の中に、関数を利用して、数式を入力します。
入力する数式は下記通りです。OFFSET関数と、COUNTA関数を利用して作成します。
意味がわからないと思うかもしれませんが、この通り入力してみてください。
このように入力することで、ガンダム、仮面ライダー以外に項目が増えても、プルダウンの範囲の再設定が不要になることがすごいのです。
ワンポイント解説
この数式の意味ですが、ミソは、COUNTA関数ですね。COUNTA関数は、文字が入力されているセルの個数をカウントします。つまり、文字ある限り、プルダウンの列の範囲を可変にすると理解すればいいと思います。OFFSET関数は、今回は、説明しません。OFFSET関数だけで、説明が終わってしまう可能性があるからです。
シート【リスト】に登場人物を入力する
登場人物は、【人物1】～【人物10】までを設定しています。
つまりK列までは、
・ガンダムに対応した人物
・仮面ライダーに対応した人物
それぞれを入力するだけで、プルダウンが連動します。
連動プルダウンの作成手順
連動させたいセルを選択します。この場合は、セルB3です。
上記のウィンドウを表示させるまでは、同じです。
その赤枠の中に、下記のような数式を入力します。
これで完成です。
今度は、MATCH関数も加わりました。難しいですか？
気にしないで、この数式をコピペしてみてください。プルダウンが連動します。
ワンポイント解説
この数式を入力する際には、下記の事項を絶対にまもってください
連動させたいセルの上、この場合は、セルB２、つまり、ガンダム、仮面ライダーが入力するセルを必ず相対参照にしましょう。
Excelには、相対参照と絶対参照がありますが、今回は、必ず相対参照です。
相対参照⇒B2
絶対参照⇒$B2$
絶対参照は、このように、B2の前後に$（ドルマーク）がつきます。こうなると今回の場合は反応しません。
まとめ
今回は、連動プルダウンの作成方法について紹介しました。
この動画のご視聴者様の感動の言葉を抜粋しています。
大変関係ないんですが、現時点(2019/2/23)で登録者数1117人。将棋の日(11月17日)だなぁと思いました。
藤井聡太7段2連覇を喜んでいる40代より
私は、入力値の種類の設定の、リスト→元の値の定義において、INDIRECT関数を使用して、リスト範囲を定義し、連動してドロップダウンリストを生成する方法を使っています。
リストの指定範囲が、シート上の任意のセルになるので、追加修正が容易で、可視化されます。
感動しました。
やってみてできたときの喜びは計り知れません。
これでプルダウンした時の項目が増えて
選択する時間の煩わしさが減ります。
本当にありがとうございます。
