エクセルの入力規則のリストを連動させて使用させたいのですが上手くできないのでご教授願いたいと思います。
まず、A1に食材の分類(野菜、肉など)をリストで選択できるようにしてあります。
A1の結果によってB1に食材(人参、玉ねぎなど)をリスト表示したいのですが、
(別シートに分類ごとにセル範囲に名前を付けてあります)
元の値を=indirect(A1)とするとエラーが出ます。
(A1には先に入力してあります)
食材のセル範囲の名前をoffsetを使って定義しているのですが
(確認しましたが関数自体は間違っていません)
offsetを使っているとエラーになるようなのです。
(A1:A5のようにセル範囲名前を定義しているときはちゃんと出来ます。)
いろいろ調べましたが名前の定義、リストの連動の仕方自体は間違っていないと思うのですが・・・
連動させるときはセル範囲をoffsetなどを使って可変にすると出来ないのでしょうか?
No.4ベストアンサー
- 回答日時:
回答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
- 回答日時:
No.1です
>ご指摘いただいたように分類の表は作ってあり、
>それぞれ範囲に名前を付けてあるのですが、
名前を付ける必要はありません。
(別シートで作ってある場合には使います。添付図参照)
>お教えいただいたやり方ではちゃんとできるのですが
可変にする方法をお望みかと思ったのですが違ったのでしょうか?
>=indirect(範囲を名前定義した値)
>とすると、エラーが出るのです。
その定義した名前が、名前ボックスで選べる状態ではない、
つまり、間接的な名前の定義方法だからできないのだと思います。
>名前の定義の仕方は間違っていないことを確認済みなのですが・・・
名前の定義の仕方、可変の数式、INDIRECTと名前の定義を使った2段階のリスト
おそらく、それぞれの指定方法は合っていると思います。
ただ、組み合わさるとできない仕様なのでしょう。
よって当方は、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
- 回答日時:
まず、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
- 回答日時:
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(範囲を名前定義した値)
とすると、エラーが出るのです。
名前の定義の仕方は間違っていないことを確認済みなのですが・・・
説明がつたなくて申し訳ございません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) 出勤簿の土、日、休日に色付けできない 2 2022/08/04 20:10
- Excel(エクセル) [入力規則]のリストボックス内の“不揃いの林檎たち” 3 2022/09/15 18:32
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/02/10 17:07
- Excel(エクセル) Excel VBAプルダウンの値を変えながら2枚ずつ印刷する方法? 4 2022/05/27 13:04
- Excel(エクセル) エクセルの数式について教えて下さい。 8 2023/05/27 12:17
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) 並べ替え、ソートの構文がわからない。 お世話になります。VBA超初心者です。 エクセルでワークシート 2 2023/06/28 21:00
- Excel(エクセル) エクセルVBA 複数行にまたがっている選択を判定するには 2 2023/05/21 21:54
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの関数について教えてくだ...
-
VBAについての質問です
-
Excelにいついて質問です。
-
一つのセルに(例えばA1)入力...
-
エクセルファイルがファイルの...
-
【マクロ】名前を保存する際に...
-
Excel 複数列 A列B列C列一致 D...
-
エクセル vbaについて教えてく...
-
エクセルであるセルに数字を入...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
Excel分析ツールでのポアソン回...
-
【Excel】数字を3倍にし、なお...
-
エクセルのデーターが2か月前の...
-
LOOKUP関数を使えばいいのでし...
-
エクセルの神よ、ご回答を! エ...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報