Excelで入力規則のリストボックス(プルダウンリスト)が表示されない?バージョン違いによる問題はリスト範囲に名前をつけることで解決! Offset関数を利用してを可変リストも作成可能 プルダウン可変式リストは超便利!

スポンサーリンク

久しぶりのOffice(オフィス)記事です。

先日、Excel(エクセル)365では、入力規則に設定されたリストボックス(ドロップダウンリスト)が表示されるのに、Excel2007では入力規則がなくなっているとの指摘を受けました。

スポンサーリンク

Excel2007以前では、別のシートを参照してリスト範囲を作成できない

僕が作成した訳ではないのですが、Excel365で開くと問題なくリストが表示されます。ここから先のデータはすべて機能紹介用の簡単なデータを利用しています。

Office365ではリストが表示される

データの入力規則を見ると、リストの「元の値(S)」が別シートのセルを参照していました。

「元の値(S)」が別シートのセルを参照
入力規則のデータの参照元が別シートになっていた

入力規則の「元の値」リストに、別シートのセル参照が利用できるようになったのは、Excel2010以降のことで、2010以降のExcelで作成した入力規則のリストで、別シートを参照したセル範囲を設定すると、Excel2007以前で開くと、何も入力規則が設定されていないようになります。

リスト範囲に名前をつけて解決

では、Excel2007以前でも別シートのリストを参照したい場合はどうすればいいかということですが、リストに「名前」をつけることで解決します。

リストにしたいセルを選択し、左上の「名前ボックス」に分かりやすい名前(ここでは「リスト」としています)を入力してEnterキーを押します。

リスト範囲に「名前」をつける

そして、「データの入力規則」ダイアログ画面の「元の値」の欄に「=リスト」(先ほど定義した名前)を入力して「OK」ボタンをクリックします。

「元の値」に定義した「名前」を入力

これで解決です。簡単ですよね。

エクセルのドロップダウンリスト(プルダウンリスト)を4段階(4階層)連動させる方法(365,2019,2016,2013,2010,2007対応)
以前、エクセルのドロップダウンリスト(プルダウンリスト)で、2007以前のエクセルでは、別シートの参照元を名前で定義しないとリストを取得できないという記事を投稿しました。今回は、結構需要が高いと思われる、ドロップダウンリス...

リスト範囲にセル参照するといろいろと不都合が

Excel2010以降は、リストのセル範囲、「元の値」を別シートのセルを参照できるようになりました。便利なようですが、これはこれでリストが増えたときは修正が大変になります。例えば、「1月」から「12月」と12枚のシートで同じように、「元の値」を別シートに設定しているとします。そのようなファイルでもし、リストを追加する必要が出てきた際は、12枚のシートをそれぞれ「データの入力規則」ダイアログボックスを開いて1つ1つセルの範囲を変更する必要があります。しかし、セル範囲を「名前」で定義していれば、「名前」を再定義するだけで対応可能になります。

「数式」「名前の管理」で、「参照範囲(R)」を変更し、「チェック」ボタンを押すだけで一括で12枚のシートの「データの入力規則」の「元の値」を変更することができます。

「名前の管理」で「参照範囲」を変更

リスト範囲を可変にするにはOffset関数を利用

応用編として、あらかじめリストを多めに設定しておき、その時点でのリストの個数で参照範囲を可変にすることができます。可変する「名前」を作成するためにはOffset関数を使います。

Offset関数は、指定した参照から指定した行数、列数の範囲への参照を返す関数です。

「=Offset(基準位置となるセル,基準位置から移動して参照したい行数,移動して参照したい列数,参照したい高さ,参照したい幅)」で表します。「参照したい高さ(行)」と「参照したい幅(列)」は省略可能です。

今回は例として、「Sheet2」の「A列」に「リスト」という名前で、最大100個のリストを想定して、リストの長さをリストの個数に合わせて可変する名前を作成します。

リストの個数で「名前」の参照範囲を自動で変更する

再び「名前の管理」ダイアログボックスを開き、今回は例として先ほど作成した「リスト」の参照範囲を自動的に変更するようにします。

名前の管理ボックスでOffset関数を利用して参照範囲を自動で変更する

参照範囲には、「=Offset(Sheet2!,$A$1,0,0,counta(a1:a100))と入力しています。

まず、最初の引数である「基準位置」はリストの先頭セル、2つ目の引数「行数」、3つ目の引数「列数」は「0」にしています。こちらを1にすると、例えば行や列が1つ移動した範囲を参照しますので、今回は「0」(移動しない)です。

4つ目の引数「高さ」(行)は、参照する行を可変にするため、「COUNTA」関数で、リストの個数をカウントし、高さ(ここでは行数)を設定しています。5つ目の引数「幅」は今回はやはり増やすことはないので「0」または省略(高さと幅は本来省略可)しています。

確認していただくとわかるのですが、リストの個数に合わせて可変する参照範囲の完成です。とっても便利ですよ。

Offset関数は慣れれば応用が利く

Offset関数は、初めは難しいですが、慣れればいろいろと応用が利くとても便利な関数です。みなさんもこの可変式リストを作成してみて、Offset関数を利用して、さまざまな場面で活用してみてはいかがでしょうか。

最新Office(オフィス)2019よりOffice365の方が最新? Office365は進化し続ける!
Microsoft(マイクロソフト)Office(オフィス)の最新バージョン、2019が2019年1月22日より、日本でも販売が開始されました。Office2019は買い切り型の永久ライセンスOffice2019は...
スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

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