入力規則の可変リストを作りたい（EXCEL）

質問

エクセルの初心者です。どなたか教えてください。

添付を見てください。
●入力リストに「名前」、「申込時期」、「月」、「コース」があります。
●参照リストにある「申込時期」、「月」、「コース」をそれぞれ入力規則のリストにして
入力リストの名前以外は選択にさせたいのです。

例えば、申込時期の第一期を入力規則のリストから選択すると、月のセルは
自動で7月～9月が設定できるようにしたいです。

都合により、VBAを書くのではなく、関数で実現したいと思ってるんですが
これはできないのでしょうか？

回答No.3

kagakusuki · ベストアンサー

今仮に、入力リストがSheet1のB列～C列にあるものとします。
　又、画像のH列～J列にある参照リストは、見栄えを良くするために、Sheet2のA列～C列に移設するものとします。
　そして、Sheet3のA列～D列を作業列として使用するものとします。

　まず、Sheet2のA列～C列に、画像のH列～J列と同様の参照リストを作成して下さい。
　次に、Sheet3のA3セルに次の数式を入力して下さい。

=IF(OR(Sheet2!A3<>"",ROW()=MATCH("゛",Sheet2!$C:$C,-1)+1),ROW(),"")

　次に、Sheet3のC3セルに次の数式を入力して下さい。

=IF(ROWS($3:3)>COUNT(A:A)-1,"",INDEX(Sheet2!A:A,SMALL(A:A,ROWS($3:3))))

　次に、Sheet3のA3セルをコピーして、Sheet3のB3セルに貼り付けて下さい。
　次に、Sheet3のC3セルをコピーして、Sheet3のD3セルに貼り付けて下さい。
　次に、Sheet3のA3～D3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　次に、Sheet1のC3セルを選択し、入力規則の[リスト]の設定において、「元の値」欄に次の数式を入力して下さい。

=OFFSET(INDIRECT("Sheet3!C3"),,,COUNTIF(INDIRECT("Sheet3!C:C"),"><"))

　次に、Sheet1のD3セルを選択し、入力規則の[リスト]の設定において、「元の値」欄に次の数式を入力して下さい。

=OFFSET(INDIRECT("Sheet3!D1"),MATCH(VLOOKUP($C3,INDIRECT("Sheet2!A:B"),2,FALSE),INDIRECT("Sheet3!D:D"),0)-1,,COUNTIF(INDIRECT("Sheet3!B:B"),"<"&SMALL(INDIRECT("Sheet3!A:A"),RANK(MATCH($C3,INDIRECT("Sheet2!A:A"),0),INDIRECT("Sheet3!A:A"),1)+1))-COUNTIF(INDIRECT("Sheet3!B:B"),"<"&MATCH($C3,INDIRECT("Sheet2!A:A"),0)))

　次に、Sheet1のE3セルを選択し、入力規則の[リスト]の設定において、「元の値」欄に次の数式を入力して下さい。

=INDIRECT("Sheet2!C"&MATCH($D3,INDIRECT("Sheet2!B:B"),0)&":C"&MATCH(SMALL(INDIRECT("Sheet3!B:B"),RANK(MATCH($D3,INDIRECT("Sheet2!B:B"),0),INDIRECT("Sheet3!B:B"),1)+1),INDIRECT("Sheet3!B:B"))-1)

　次に、Sheet1のC3～E3の範囲をコピーした後、Excelの[形式を選択して貼り付け]機能を使用して、同じ列の4行目以下に、「入力規則」のみを貼り付けて下さい。

　以上です。

お礼

お礼が遅くなって大変申し訳ありません。
仕事が忙しくて、なかなか上記の関数を全て調べて理解する時間が取れませんでしたが、なんとかなりました。EXCELは関数が多くありすぎてとても難しいです。

本当にお世話になりました。どうもありがとうございます。

回答No.4

NuboChan

以下が大変わかりやすく、入力補助としては使いやすいです。
自分も利用してますが、シェアウェアなのが残念ですが安価なので
試用して気に入れば導入しても損は無いと思います。

　　1ヶ月間の試用が可能です。その間の機能制限はありません。

楽々階層入力
(シェアウェア：1,155円(税込))
階層形式のリストを右クリックメニューから
簡単に入力できるようにするExcel用アプリケーションです。
入力したいセルを右クリックし、表示されたメニュー上の階層を辿り
目的の値の所でクリックするだけで入力できます。

http://www.vector.co.jp/soft/win95/business/se385991.html

回答No.2

prius770c

VBAや作業列は、必要ありません。
名前の定義とINDIRECT関数を用いれば可能です。

まず名前の定義は、「申込時期」の「第一期に対する月」「第二期に対する月」と「それぞれの月に対するコース(計6つ)」の合計9種類を用意しておきます。

※名前の定義の使用法は、どのかのHPを参照してください。
※テストしてみると、名前の定義で使える名前の冒頭は英数ではダメみたいのなので月は漢数字に変換した方がいいです。
※定義で参照できる範囲は連続している必要があるので、私は、申込期間は通常のカンマ区切りのリストで作成しました。

次にINDIRECT関数の使い方です(月の列に対する例)

月の列に入れる入力規則のリストに「=INDIRECT(申込時期が入るセル番地)」とします。

この時に、申込時期が入るセル番地に入る値が名前の定義に使っているものと同様であれば定義したリストが表示されます。
サンプルの画像で言えば、申込時期に「第一期」を選んでいます。
この時に、名前の定義で「第一期」に対応するものとして「四月～六月」を参照しておけばプルダウンでその月たちのリストが出ます。
さらに、月を選択してそれに対応する名前の定義をしておけばコースも可変でリスト表示されます。

例にあるように、「四月」を選んでいるときと、「七月」を選んでいる時ではプルダウンのリストが違います。

色々書きましたが、下記のURLの方が分かりやすいと思います。

http://www.relief.jp/itnote/archives/000822.php

回答No.1

mshr1962

別シートでいいので下記のように設定
第1期　4月　5月　6月
第2期　7月　8月　9月
4月　　Aコース　Bコース　Cコース
5月　　Dコース　Eコース　Fコース
6月　　Gコース　Hコース　Iコース
7月　　Jコース　Kコース　Lコース
8月　　Mコース　Nコース　Oコース
9月　　Pコース　Qコース　Rコース

上記の範囲を選択して「挿入」「名前」「作成」「左端列」
申込時期(D3)は今のままの設定で
月(E3)は入力規則のリストで「=INDIRECT(D3)」
コース(F3)は入力規則のリストで「=INDIRECT(E3)」

補足

早速の回答ありがとうございます。

追加で情報を記載すると、「参照リスト」の方でコース数が変動する場合があるので、
なるべくそのあたりを自動で可変にできるようにしておきたいのですが。

最初にうまく説明できなくてすみません。
追って情報があればお願いします。