スポンサーリンク
はじめに
前回の記事で、Excelのリスト形式の入力規則について扱った。
その最後で、下図のように「入力」「マスタ」シートがあったとき
=マスタ!$B$2:$B$11
という決まりきった範囲を選択肢として設定する例を取り上げた。
今回は、この選択肢がB11セル以降にも伸びていく場合のやり方、すなわち範囲が可変の場合にも対応できる設定方法を書いてみる。
セル範囲を指定するOFFSET関数
さてこういう場合、セル範囲を指定する関数と言えばOFFSET関数だ。
正直、難易度はけっこう高い方に分類される関数なので、あまり深入りして解説したくはない。
今回の場合は、B2セルから始まって下方向に伸びていく例だけど、それを示すなら次のようなOFFSET式になる。
要は
- 「マスタ」シートの$B$1セルを基準として(本当は選択肢の開始点であるB2セルを起点にしたいが、B2セルが削除されたりしたときのために、絶対に削除されないタイトルであるB1セルを起点にする)
- 下に1個ズラしたB2セルを起点にする
- B列の中にあるデータ数をCOUNTA関数でカウントし、そこからタイトルであるB1セルの分を1個除くCOUNTA(マスタ!$B:$B)-1
難解なOFFSET式は名前定義に組み込んでから入力規則のリストに用いる
そして、こういう複雑な式を少しでも簡易化するのに役立つのが、名前定義だ。
名前定義にOFFSET式を組み込む
以前の名前定義に関する記事で、OFFSET式なんかを名前定義に組み込む方法を紹介した。
そして同記事では、そんなOFFSET式でセル範囲を定義するためだけに名前定義を使うのはナンセンスだみたいに書いたのだけど、今回のように入力規則のリストに用いるOFFSET式の場合に限っては、この名前定義組み込みを使わないと面倒だ。
というかExcel2003までは、入力規則の選択肢が別のシートにある場合は、その選択肢のセル範囲は名前定義に組み込まないと、入力規則用に使うことはできなかった。
Excel2007以降は、
=マスタ!$B$2:$B$11
のように別シートを参照する計算式でも普通に、入力規則のリスト用に使えるようになった。
一応、名前定義にOFFSET式を組み込む方法を書いておくと、
- ショートカットキーCtrl+F3から「新規作成」を押す
- 「名前」欄に「会社名」とか入力
- 「参照範囲」欄には、先程のOFFSET式「=OFFSET(マスタ!$B$1,1,0,COUNTA(マスタ!$B:$B)-1,1)」をコピペする。
といった手順になる。
設定作業をした直後は、最初の「名前の管理」画面に戻るわけだけど、ここでTabキーを押して下の「参照範囲」欄に移動してみよう。
そうすると、OFFSET式が示しているセル範囲が破線で示されるので、正しく式を組めたかどうか確認できる。
この小難しいOFFSET式は、下書きしといてからコピペする方法でないと、私もそうそうスムーズに書くことはできない。
なんならIMEとかの漢字変換の辞書に、
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
という式をテンプレ登録しておけば良いかと思う。「お」と入力して変換すればこのOFFSET式が出てくるというようにね。
名前定義を入力規則のリストに適用する
いま示したのは、OFFSET式を名前定義に組み込む方法だけど、これをリスト入力規則の中に入れるまでが勝負だ。
その手順をまた示すと、
- 入力規則を設定したいセル範囲を選択して、入力規則の設定画面を開く
- 「入力値の種類」で「リスト」を選択
- 「元の値」欄にカーソルを合わせ、ショートカットキーF3を押して、名前定義の貼付け画面から先程設定した名前「会社名」を選択する
という感じだ。
これで、OFFSET式で定義したセル範囲が、入力規則のリストとして適用されるようになる。
Ctrl+F3(名前の管理画面)とかF3(名前の貼付け)とか、名前定義に関するショートカットキーの使い方は、こちらの記事に書いてある。
リスト形式の入力規則については、基本的には解説する事項は大体このくらいになる。
このOFFSET関数を使う方法まで知っていれば、いっぱしのレベルだと言えるだろう。
ただ、もう少し便利にしたい要素がまだあり、それはExcelの基本機能を使うだけの正攻法では実現できず、マクロを使う必要がある。
それについては次回の記事で述べることにする。
スポンサーリンク
