久しぶりのOffice（オフィス）記事です。
先日、Excel（エクセル）365では、入力規則に設定されたリストボックス（ドロップダウンリスト）が表示されるのに、Excel2007では入力規則がなくなっているとの指摘を受けました。
Excel2007以前では、別のシートを参照してリスト範囲を作成できない
僕が作成した訳ではないのですが、Excel365で開くと問題なくリストが表示されます。ここから先のデータはすべて機能紹介用の簡単なデータを利用しています。
データの入力規則を見ると、リストの「元の値（S）」が別シートのセルを参照していました。
入力規則の「元の値」リストに、別シートのセル参照が利用できるようになったのは、Excel2010以降のことで、2010以降のExcelで作成した入力規則のリストで、別シートを参照したセル範囲を設定すると、Excel2007以前で開くと、何も入力規則が設定されていないようになります。
リスト範囲に名前をつけて解決
では、Excel2007以前でも別シートのリストを参照したい場合はどうすればいいかということですが、リストに「名前」をつけることで解決します。
リストにしたいセルを選択し、左上の「名前ボックス」に分かりやすい名前（ここでは「リスト」としています）を入力してEnterキーを押します。
そして、「データの入力規則」ダイアログ画面の「元の値」の欄に「=リスト」（先ほど定義した名前）を入力して「OK」ボタンをクリックします。
これで解決です。簡単ですよね。
リスト範囲にセル参照するといろいろと不都合が
Excel2010以降は、リストのセル範囲、「元の値」を別シートのセルを参照できるようになりました。便利なようですが、これはこれでリストが増えたときは修正が大変になります。例えば、「1月」から「12月」と12枚のシートで同じように、「元の値」を別シートに設定しているとします。そのようなファイルでもし、リストを追加する必要が出てきた際は、12枚のシートをそれぞれ「データの入力規則」ダイアログボックスを開いて1つ1つセルの範囲を変更する必要があります。しかし、セル範囲を「名前」で定義していれば、「名前」を再定義するだけで対応可能になります。
「数式」「名前の管理」で、「参照範囲（R）」を変更し、「チェック」ボタンを押すだけで一括で12枚のシートの「データの入力規則」の「元の値」を変更することができます。
リスト範囲を可変にするにはOffset関数を利用
応用編として、あらかじめリストを多めに設定しておき、その時点でのリストの個数で参照範囲を可変にすることができます。可変する「名前」を作成するためにはOffset関数を使います。
Offset関数は、指定した参照から指定した行数、列数の範囲への参照を返す関数です。
「=Offset（基準位置となるセル,基準位置から移動して参照したい行数,移動して参照したい列数,参照したい高さ,参照したい幅）」で表します。「参照したい高さ（行）」と「参照したい幅（列）」は省略可能です。
今回は例として、「Sheet2」の「A列」に「リスト」という名前で、最大100個のリストを想定して、リストの長さをリストの個数に合わせて可変する名前を作成します。
再び「名前の管理」ダイアログボックスを開き、今回は例として先ほど作成した「リスト」の参照範囲を自動的に変更するようにします。
参照範囲には、「=Offset（Sheet2!,$A$1,0,0,counta(a1:a100))と入力しています。
まず、最初の引数である「基準位置」はリストの先頭セル、2つ目の引数「行数」、3つ目の引数「列数」は「0」にしています。こちらを1にすると、例えば行や列が1つ移動した範囲を参照しますので、今回は「0」（移動しない）です。
4つ目の引数「高さ」（行）は、参照する行を可変にするため、「COUNTA」関数で、リストの個数をカウントし、高さ（ここでは行数）を設定しています。5つ目の引数「幅」は今回はやはり増やすことはないので「0」または省略（高さと幅は本来省略可）しています。
確認していただくとわかるのですが、リストの個数に合わせて可変する参照範囲の完成です。とっても便利ですよ。
Offset関数は慣れれば応用が利く
Offset関数は、初めは難しいですが、慣れればいろいろと応用が利くとても便利な関数です。みなさんもこの可変式リストを作成してみて、Offset関数を利用して、さまざまな場面で活用してみてはいかがでしょうか。
コメント