エクセル リストの連動について教えてください。

質問

エクセルの入力規則のリストを連動させて使用させたいのですが上手くできないのでご教授願いたいと思います。

まず、A１に食材の分類（野菜、肉など）をリストで選択できるようにしてあります。
A1の結果によってB1に食材（人参、玉ねぎなど）をリスト表示したいのですが、
（別シートに分類ごとにセル範囲に名前を付けてあります）
元の値を=indirect(A1)とするとエラーが出ます。
（A1には先に入力してあります）

食材のセル範囲の名前をoffsetを使って定義しているのですが
（確認しましたが関数自体は間違っていません）
offsetを使っているとエラーになるようなのです。
（A1:A5のようにセル範囲名前を定義しているときはちゃんと出来ます。）

いろいろ調べましたが名前の定義、リストの連動の仕方自体は間違っていないと思うのですが・・・

連動させるときはセル範囲をoffsetなどを使って可変にすると出来ないのでしょうか？

回答No.4

kagakusuki · ベストアンサー

回答No.2です。

>お教えいただいたやり方ではちゃんとできるのですが
>=indirect(範囲を名前定義した値）
>とすると、エラーが出るのです。

との事ですが、何故、名前定義をしたリストを使われるのでしょうか？
　もしかしますと、質問者様が御使いになられているExcelのバージョンはExcel2007よりも前のバージョンで、ドロップダウンリストの元の値に、別のシートに設けたリストを設定する際には、

=Sheet2!A1:A5

や、或いは

=OFFSET($E$1,1,MATCH(A1,$E$1:$G$1,0)-1,COUNTA(INDEX($E:$G,,MATCH(A1,$E$1:$G$1,0)))-1)

の様な、普通のやり方でセル範囲を指定する事が出来なかったため、名前定義を使ってリストを設定しようとされているのではないかと思っていたのですが、違うのでしょうか？
　Excel2007よりも前のバージョンでは、入力規則や条件付き書式でセル範囲を指定する際には、

=Sheet2!A1:A5

の様な普通のセル範囲の指定方法は使う事が出来ず、セル範囲の指定を名前定義を使って行うか、或いは、INDIRECT関数を使って行うしかありません。
　そして、INDIRECT関数を使えばOFFSET関数と同様の処理を行う事が出来ますから、INDIRECT関数を使う場合にはOFFSET関数を使う意味があまりありません。
　そのため、回答No.2ではINDIRECT関数を使った方法を提示させて頂いた訳です。
　他のセルに入力された値によって、ドロップダウンリストの内容を変更するのでしたら、名前定義で指定したリストを複数使うよりも、INDIRECT関数を応用した方が簡単だと思うのですが、この方法を使わずに、複数の名前定義を切り替える方法でなければならないと仰るのは何故なのでしょうか？

　それと、

>お教えいただいたやり方ではちゃんとできるのですが

との事ですが、例えExcel2007以降のバージョンであっても、ドロップダウンリストの元の値にINDEX関数を使った指定方法は使う事は出来ません。
　つまり、回答No.1様が2番目に提示されている

=INDEX($E$2:$G$2,,MATCH($A1,$E$1:$G$1,0)):INDEX($E:$G,COUNTA(INDEX($E:$G,,MATCH($A1,$E$1:$G$1,0))),MATCH($A1,$E$1:$G$1,0))

という方法は、ドロップダウンリストの設定には使う事が出来ません。（確認済み）
　という事は、質問者様は本当に「ちゃんとできる」か否かを確認してはおられない可能性があると思います。
　もしも、「入力規則では、他のシートのセル番号を指定する方法は使えない」という固定観念から、十分には確認しないまま、「リストを名前定義するしか方法は無い」と考えておられる場合には、再度、回答No.2の方法を、「実際に」試してみて下さい。
　その際には、もし、御使いのExcelがExcel2007以降である場合には、回答No.1様が1番目に提示されておられる方法である

=OFFSET($E$1,1,MATCH(A1,$E$1:$G$1,0)-1,COUNTA(INDEX($E:$G,,MATCH(A1,$E$1:$G$1,0)))-1)

という方法を、Sheet2にリストを設定した場合に合わせて修正した方法も、併せて御確認された方が宜しいかと思います。

お礼

たびたびのご回答ありがとうございます。

＞複数の名前定義を切り替える方法でなければならないと仰るのは何故なのでしょうか？

パソコンに詳しくない者にも操作してもらうことを前提に作っていたので後々なるべく説明がしやすいようにしようと固執していました。

また、別シートのさまざまな計算等が入力してある一部に分類のリストを作っていたので、名前定義したほうが楽だろうという安易な考えでした。
不精はいけないということですね。
すみません。

ご教授くださったやりかたで分類のリストの専用シートを作ったら出来ました。

ありがとうございました。
　　　　追記　　お手数おかけして申し訳ございませんでした。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

No.1です

>ご指摘いただいたように分類の表は作ってあり、
>それぞれ範囲に名前を付けてあるのですが、
名前を付ける必要はありません。
(別シートで作ってある場合には使います。添付図参照)

>お教えいただいたやり方ではちゃんとできるのですが
可変にする方法をお望みかと思ったのですが違ったのでしょうか？

>=indirect(範囲を名前定義した値）
>とすると、エラーが出るのです。
その定義した名前が、名前ボックスで選べる状態ではない、
つまり、間接的な名前の定義方法だからできないのだと思います。

>名前の定義の仕方は間違っていないことを確認済みなのですが・・・
名前の定義の仕方、可変の数式、INDIRECTと名前の定義を使った２段階のリスト
おそらく、それぞれの指定方法は合っていると思います。
ただ、組み合わさるとできない仕様なのでしょう。
よって当方は、INDIRECTと名前の定義を使った処理をなくしているわけです。

B1セルの入力規則 － リストに
=OFFSET(起点,1,MATCH(A1,種類,0)-1,COUNTA(INDEX(表,,MATCH(A1,種類,0)))-1)
または
=INDEX(起点行,,MATCH($A1,種類,0)):INDEX(表,COUNTA(INDEX(表,,MATCH($A1,種類,0))),MATCH($A1,種類,0))

定義された名前
起点 =Sheet2!$A$1
起点行 =Sheet2!$A$2:$C$2
種類 =Sheet2!$A$1:$C$1
表 =Sheet2!$A:$C

お礼

たびたびのご回答ありがとうございます。

名前を定義して作ったほうが、
パソコンに詳しくない者でも理解しやすいのかな？と
固執していました。

仕様の問題で出来ないのは、残念ですが、
また、いろいろな方法にチャレンジしてみたいと思います。

説明が拙くご迷惑をおかけしましたが、
さまざまな方法をご教授下さりありがとうございました。

回答No.2

kagakusuki

まず、Sheet2の1行目に、「穀物」、「野菜」、「果物」、「肉」、「魚介類」等々の食材の分類のリストを、横一列に作成して下さい。
　次に、そのリスト中の各食材の分類に沿った食材名を、Sheet2の2行目以下に、縦方向に入力して下さい。
　つまり、例えば
　　　　　　A列　　B列　　　C列
1行目　　穀物　　野菜　　果物
2行目　　米　　　 白菜　　ミカン
3行目　　小麦　　大根　　リンゴ
4行目　　大豆　　人参　　梨
5行目　　小豆

という様な形式のリストを作成して下さい。
　次に、入力規則を設定するセルがあるシートのA1セルに、ドロップダウンリストを設定する際には、「元の値」欄に次の様な数式を入力して下さい。

=INDIRECT("Sheet2!A1:C1")

　尚、上記の数式は、食材の分類のリストがSheet2のA1～C1の範囲に作成されている場合のもので、もし、リストに入力すべき食材の分類の数が、もっと多い場合には、「元の値」欄で設定するリストのセル範囲を拡張して下さい。
　次に、入力規則を設定するセルがあるシートのB1セルに、ドロップダウンリストを設定する際には、「元の値」欄に次の様な数式を入力して下さい。

=INDIRECT("Sheet2!R2:R"&MATCH("*?",INDIRECT("Sheet2!C"&MATCH(A1,INDIRECT("Sheet2!1:1"),0),FALSE),-1)&"C"&MATCH(A1,INDIRECT("Sheet2!1:1"),0),FALSE)

　これで、食材名を入力した行数が変化した場合でも、ドロップダウンリストの設定を変更せずとも、Sheet2のリストに入力されている食材の内、指定した分類のものが全てドロップダウンリストに表示されます。

補足

ご回答有難うございます。
ご指摘いただいたように分類の表は作ってあり、
それぞれ範囲に名前を付けてあるのですが、
お教えいただいたやり方ではちゃんとできるのですが
=indirect(範囲を名前定義した値）
とすると、エラーが出るのです。
名前の定義の仕方は間違っていないことを確認済みなのですが・・・

説明がつたなくて申し訳ございません。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

B1セルの入力規則-リストで
=OFFSET($E$1,1,MATCH(A1,$E$1:$G$1,0)-1,COUNTA(INDEX($E:$G,,MATCH(A1,$E$1:$G$1,0)))-1)
または
=INDEX($E$2:$G$2,,MATCH($A1,$E$1:$G$1,0)):INDEX($E:$G,COUNTA(INDEX($E:$G,,MATCH($A1,$E$1:$G$1,0))),MATCH($A1,$E$1:$G$1,0))

リスト範囲は添付図下記参照 参考まで

補足

ご回答有難うございます。
ご指摘いただいたように分類の表は作ってあり、
それぞれ範囲に名前を付けてあるのですが、
お教えいただいたやり方ではちゃんとできるのですが
=indirect(範囲を名前定義した値）
とすると、エラーが出るのです。
名前の定義の仕方は間違っていないことを確認済みなのですが・・・

説明がつたなくて申し訳ございません。