動的リストの作成
前にいたことがある職場に今いる知人から相談を受けた。
秘伝のマクロが動かなくなって困っているらしい。
人助けのつもりでそのExcelを送ってもらって中を見てみた。
あー、確かにこんなのあったなー。おれは改造して使っていたけど。
簡単に言うと、
開始番号と終了番号を入力して実行すると、帳票にデータを次々に差し込んで印刷する
という、まあ差込印刷をExcelでやるようなやつ。
ただまあ、差し込むデータの数が番号ごとに異なるので、かなりめんどくさい処理を強引に書いている、というそんな秘伝マクロだった。
で、その
開始番号と終了番号を入力
の部分なんですけれど、無駄にInputBox関数なんか使っている。
しかも、開始番号を入力させるInputBoxには、
1～〇〇までの番号を半角で入力してください
とか、
存在しない番号を入力したら処理が止まります
とか書いてある。
もちろん、続けて出てくる終了番号を入力させるInputBoxには、
開始番号で入力した番号～〇〇までの番号を半角で入力してください
とか、
開始番号で入力した番号よりも小さな番号を入力すると処理が止まります
とか書いてある。
もう絵に描いたような
運用でカバー
だったのである。
「せっかくExcelでやってるんだから、セルに入力してもらったらええやん……。」と思った私は、さっそく改造することにした。
動的なリスト
InputBoxを使う代わりに、ユーザーフォームを使うという手もあるが、たかが入力を制限するだけのためにそこまでする必要もあるまい。
要するに、開始番号と終了番号が望ましい形でしか入力できないようにすればいいのだ。
ほれ、
Excelには「データの入力規則」という機能があるじゃないか！
ということですよ。
まずは静的リスト
たとえば、
こんなふうに表を用意しておいて、A2セルに「データの入力規則」を適用する。
「データ」タブから「データの入力規則」へと進み、
「設定」タブの「入力値の種類」のドロップダウンリストで「リスト」を選択。「元の値」のところに選択肢を並べたリスト範囲（今回の場合なら$D$1:$D$20）を指定してやればよい。
これで、静的なリストなら簡単に作ることができる。
ただし、これだけでは「自」に「至」よりも大きな数字を入れることができるため、困ったことになる。
そこで、動的リストの作成である！
動的なリスト
「自」欄（A2セル）で入力を許可する数値のリストを、「至」欄（B2セル）の値に応じて変化させたり、その逆をしたりしたい。
そこで、次のように考えた。
- 「データの入力規則」の「元の値」の指定に
INDIRECT関数を用いる INDIRECT関数の引数にする参照アドレスの文字列を「自」欄・「至」欄の値に応じて変化させるセルを作る
およそこのような感じ。
こうすれば、「自」欄・「至」欄入力時に表示されるドロップダウンリストが動的なリストになる。
動的リストの作成
次のような補助セルが必要だと考えた。
- 「自」リストの入力に応じて、「至」リストの上限を示すセルのアドレスを作成する
- 「至」リストの入力に応じて、「自」リストの下限を示すセルのアドレスを作成する
- 上記1.、2.を組み合わせて「自」リスト用の参照範囲アドレスを作成する
- 上記1.、2.を組み合わせて「至」リスト用の参照範囲アドレスを作成する
以上の四つだ。
今回は
このようにした。
F2セルの数式のみコメントで表示しているが、この場合だと
Ａ２セルの入力値が「至」欄の入力値の上限値になる
ようにしている。
「至」リストは、「自」欄の入力値から（つねに）最大の値（今回の場合は「20」）までを受け付ければよいので、「至」リストの参照アドレスは、
このように先ほどのF2セルの返り値に「":$D$20"」をくっつけてやればよい。
参考までにそれぞれの補助セルについて数式を記しておく。
至リストの先頭行（F2セル）
=IF(A2="","$D$1","$D$"&MATCH(A2,$D$1:$D$20,0))
自リストの先頭行（G2セル）
=IF(B2="","$D$20","$D$"&MATCH(B2,$D$1:$D$20,0))
自リストの参照アドレス（H2セル）
="$D$1:"&G2
至リストの参照アドレス（I2セル）
=F2&":$D$20"
あとは、「自リストの参照アドレス」欄（H2セル）の返り値を「自」欄（A2セル）のリストの「元の値」、「至リストの参照アドレス」欄（I2セル）の返り値を「至」欄（B2セル）のリストの「元の値」
で、それぞれINDIRECT関数の引数にすればよい。
使ってみる
こんな風に動きます。
いい感じではないでしょうか。
おわりに
私は乏しい知識と経験をもとにこのように考えましたが、他にもいろんなやり方があると思います。
ユーザーの入力に縛りをかける、というのはExcel仕事において非常に大切だと思います。
「もっと良いやり方があるぜ！」という方がいらっしゃいましたら、ぜひ教えろ教えてください。