この記事のもくじ
名前の定義
Excelの数式のところにある”名前”ですが、なんのことやらということで使っていない人も多いのではないでしょうか。
私もそうだったのですが、使ってみると結構便利で簡単に可変リストを作ることができるので、その方法を今回は紹介したいと思います。
名前を付ける意味
1つのセルや、複数のセルに特定の名称を付けることができるこの名前機能、基本的に複数セルであるセル領域を指定して名前を付けて使うことになるでしょう。名前を付ける意味は下記。
- 人間がそのセル領域をどういう種類のデータの集まりなのか判別しやすくなる
- Excelがそのセル領域をデータのまとまりとして認識してくれる
1.は作成者以外の人がExcelファイルを引き継ぎなどで見たときに、その領域がどういうセルの集まりなのかわかりやすくなるという親切心あふれる配慮になります。変な名前付けたらワケワカメですが。
2.が重要で、セルを変数で言うところの配列のようにまとめてExcelに認識させることができ、関数などで名前を指定することができるようになります。テーブルなどと同様に、データとしてセル領域を認識させることができるのが非常に重要になります。
本題:名前とINDIRECT関数で可変リストを作ってみる
というわけで名前とINDIRECTという関数を使って、可変するリストを作ってみます。
INDIRECT関数
……ピンと来ません。
A1セルにB2と入れて、B2に”あほ”と入れて、どこかのセルに=INDIRECT(A1)と入れるとそのセルが”あほ”になります。間接的にセルの内容を参照する関数ですが、どう使うのか？がピンと来ないですね。
ここについてはおいおい説明するため、とりあえずサンプルを紹介していきます。
サンプルの全体画面
下記のように作ります。
B3、C3セルはデータの入力規則にて後でリストにします。
E列以降はリストで選択されるデータ定義セル領域となります。
このシートでやりたいこと
- メーカー選択で日産を選択すると、車種選択で日産のNXクーペ他が出る。他メーカー選ぶと他の車種リストが出る。
では作っていきます。
まず名前を定義する
下記のように数式→名前の管理で上記の各赤枠を参照範囲として、名前を付けていきます。
5つの名前を作ります。名前の管理ダイアログボックスを見ると、下記のようになっています。
このダイアログだとピンと来ないと思いますので、シートでどこのセル領域を名前として設定したのか見てみましょう。下記のような領域として設定しています。＊頭に空白セルを入れているのは私の経験的なものです。
では続いて、リストの設定です。
データの入力規則を設定する その1
B3セルにデータの入力規則を設定します。
データ→データの入力規則の設定で入力値の種類で”リスト”、元の値に=INDIRECT(“メーカー”)を入れます。
これで、先程定義した名前の中のメーカーセルがリストとして設定されます。
INDIRECT関数にて名前を指定すると、名前で設定されているセル領域が引値として返され、それがリストになります。
これでB3セルにてトヨタ、日産、ホンダ、スズキが選べるようになります。
データの入力規則を設定する その2
続いてC3セルにデータの入力規則を設定します。
今回はデータ→データの入力規則の設定で入力値の種類で”リスト”、元の値に=INDIRECT($B$3)を入れます。
これにより、C3セルのリストが、B3セルのリストで指定した文字列の名前のリストになります。
文字で説明するとワケワカメなので、実行するとどうなるか下記で説明します。
各メーカーを選ぶと、各メーカーの車種が選べる
B3でトヨタを選ぶと下記、セラ・ラウム・カルディナといった一世を風靡した車種がC3セルに出てきます。
日産だと下記。
ホンダは下記。
スズキは下記。
このシートの動きまとめ
- B3セルの文字列を元にして、
- C3セルのINDIRECT関数がB3セルに入っている文字列と同じ名前を探しに行って、
- 適合する名前があれば、その名前で領域設定されているセル範囲をC3のリストとして持ってきます。
これでif文のネスト地獄だ、VBAで表示範囲を変えるだ、VBAでリストを生成するだなんだとせずともシンプルに可変リストができました。
この方法の他の活用方法
選択式でどんどん絞り込んでいくようなリストを作る際は、この方法だと簡単に作れます。
ゲーム機→ジャンル→メーカー→ソフト名、みたいな使い方ができると思います。
お試しアーレ。
名前をつける際の注意点
最初が数字
最初が数字だと、名前を付けた際に自動的に”_”が入ってしまいます。
117クーペなどだと、_117クーペになってしまい名前と文字列がリンクしなくなりますので注意です。
記号や半角スペース
これらも自動的に”_”が入ってしまうので、スカイラインGT-R(BCNR33)だとスカイラインGT-R_BCNR33_になってしまったり、オーテック ザガート ステルビオだとオーテック_ザガート_ステルビオになったりします。
リンクさせる際は名前の付け方に気をつけましょう！
名前の代わりにテーブルを使おう(2023/06)
範囲に名前を定義するのもアリですが、スマートに行くならテーブルにした方が良いです。
INDIRECT関数からは名前もテーブルの名前も同様に指定できます。
さらにテーブル化で名前を管理しやすくなる(テーブルを選択するとリボンにテーブル管理、変更するメニューが出る)のと、Excelからデータの集まりとして認識されるのでデータサイズを拡張・縮小したり、PowerQueryで料理する際に便利です。
コメント