エクセルのリストとINDIRECT関数

質問

エクセル初心者です。
過去問検索をしたところ、QNo.766752で、まさに私が知りたいことが質問されていました。
Ａ列に「乗り物」が入力された場合、Ｂ列リストには飛行機・新幹線・自動車・・・
Ａ列に「食べ物」が入力された場合、Ｂ列リストには寿司・天婦羅・ラーメン・・・
のように、Ｂ列のリストにはＡ列に関係のあるものだけを表示させたい。

そこで、＃２の方の回答を参考に、やってみたのですが、うまくいきません。
回答にあるように、Ｅ１のセルとＦ１のセルに設定すると、その２つのセルでは望んだ通りになるのですが、Ｅ２、Ｅ３・・・と下の行には何の設定もできていない感じです。
また、新しいシートに設定した場合には、最初の行だけはうまくいくものの、既存の入力済みシートではできません。

回答者さんが説明するまでもないと考えたことが、私には理解できていないのだと思います。(^^;
どこが悪いのか教えてください。よろしく御願いします。

回答No.4

shiga_3 · ベストアンサー

＃１です。
まずお礼欄のことについて、
＞すでに数百列が入力されていて、Ｂ列、Ｃ列に入力済みの値は、リストとして管理されています
この数百列とそのリストには手をつけずに、それ以降のセルにご質問の連係式の入力規制をかけたい、ということであれば、
・Ｂ列で新しく入力規制をかけたい行以降の範囲（例えば300行目まで入力済みであればB301:B1000など）を選択して、データ→入力規制の条件の設定で、元の値を「=物の名前」とする。
・Ｃ列で例えばC301を選択して、同じく入力規制の条件の設定で、元の値を「=INDIRECT(B301)」としてＯＫ、以下C1000までドラッグ→コピー
でうまくいくはずです。

補足欄のことについて、
＞新しいシート１にリストを作成し、次に新しいシート２を作って、そこに入力規制の設定をすると、リストの最初の項目（リストが、「食べ物、乗り物、履物、飲み物」だとすると、最初の「食べ物」）だけしかポップアップされません。
名前の定義、または入力規制で参照する名前の記述が間違っているのではないでしょうか。次の点を確認してみて下さい。
・「食べ物、乗り物、履物、飲み物」と書かれた４つのセルを選択して、名前が定義されているか、数式バー（セルを選択するとその数式が表示される所）の左側の名前ボックス（いつもはセル番地が表示されているところ）に範囲の名前が表示されるか、また自分の定義した名前に間違いはないか。
・「食べ物」だけしかポップアップされないセルを選択して、データ→入力規制の条件の設定で、元の値が「食べ物、乗り物、履物、飲み物」で定義した名前になっているか。

＃３さんのお礼欄のことについて、
＞Ｂ５のセルをドラッグでコピーしていくと、Ｂ６、Ｂ７・・・のセルがすべてＦ５と同じ分類になってしまいます。
INDIRECT関数の参照セルが絶対参照（$付き）になっているからです。
セルを参照する場合には相対参照と絶対参照があります（下記参照）。参照元が固定であるかそうでないかによって使い分ける必要があります。
http://www.zaurusworld.ne.jp/menu/appli/man/spsh_e1/hk_23.html
この場合は、行方向にE5、E6、E7…と参照元が変わっていきますから、「=INDIRECT(E5)」または列だけ固定して「=INDIRECT($E5)」とします。

＞ＥとかＦなどの列に入力規制を設定すると、ポップアップの項目がリストの名前だけになってしまい、それを選ぶと「＃VALUE!」と出てきます。
「#VALUE!」は参照する値（引数）に誤りがある場合に出るエラーです。
http://allabout.co.jp/study/pcbasic/closeup/CU20030804/index.htm
ＡＢ列からＥＦ列に入力規制ごとコピーされた時に参照元がずれてしまっているのではないでしょうか。セル選択→データ→入力規制の条件の設定で確認してみて下さい。

参考URL：

http://www.zaurusworld.ne.jp/menu/appli/man/spsh_e1/hk_23.html,http://allabout.co.jp/study/pcbasic/closeup/CU20030804/

お礼

shiga_3さん、何度もご回答をありがとうございます。
ＵＲＬもとても参考になりました。
絶対参照と相対参照はよくわかりました。

既存のシートに新たに入力規制の設定をする場合ですが、リストの名前に間違いがないかなど、何度も確認してみましたが、どうしてもうまくいきません。

例えば300行めから設定しようとして、リスト設定がされている範囲を示すグレーの枠の中にある300行めと301行目だけセルを選択しても、１行目から全部青く選択されてしまいます。
また、元の値を「=INDIRECT(E300)」と入力しても、なぜかもう一度開くと「=INDIRECT(E300)の」と、ひらがなの「の」が付いています。
この部分が文字化けしていることもあります。

新しいブックを作ってやってみたところ、すべてうまくいきました。Ａ列から始めず、Ｃ列やＥ列を表の左端に設定しても大丈夫でした。

既存のシートは、私がよくわからないで変なものを色々設定してしまっていて、それが邪魔しているのだと思います。

ともあれ、新しいブックではできることがわかりましたので、ブックを作り直して全部入力しなおせばいいのだと思いますが、もう500行以上も入れてしまったので。。。

何度も詳しいご説明をしていただき恐縮です。どうもありがとうございました。

回答No.5

ippu

E5、F5　の下に同じようにセルの設定をしたいわけですね。
E列はそのまま下にコピーすればＯＫですが、F列では「元の値」が
　=INDIRECT($Ｅ$5)　とE5 セルが絶対参照先になっている為にご質問のような結果になります。
多くのセルにコピーするのであれば、=INDIRECT($Ｅ5)　とすれば可能ですが
それ程多くも無いのであればF5 の場合と同じように設定してください。
最初の中は、その方が後々のトラブルも起こりにくいと思います。

お礼

ippuさん、何度もご回答ありがとうございます。

絶対参照と相対参照の違い、＄記号の意味はわかりました。
どうもありがとうございました。

＃４のご回答へのお礼にも書いたのですが、新規にブックを作ってやってみたところ、すべてうまくいきました。
なぜか色々と書き込んだブックだと、できないんです。。。

ご回答どうもありがとうございました。

補足

shiga_3さん、ippuさん、何度も辛抱強く教えてくださいましてありがとうございます。

新しいブックでは希望通りの設定ができましたし、これ以上ご面倒をかけるのは申し訳ないので、この質問は締め切らせていただきます。
既存のブックでうまくいかないのは、私がごちゃごちゃ余計な設定をしたせいだと思います。(^^;
それはまた別の問題なので、新たに同じカテでＱを立てることにします。もしお時間があったらまた教えてください。

お二人ともどうもありがとうございました。大変勉強になりました。m(__)m

回答No.3

ippu

基本的なモデルを作ってみました。
番号に従って進んでみてください。

下準備としてリストを作成します。

(1)　たとえばＧ1：Ｇ3に　乗り物、食べ物、装飾品　等の分類を入力します。
(2)　乗り物、食べ物、装飾品　の下に各所属品名を入力します。
具体的には乗り物の下には飛行機、新幹線、自動車・・・・
　　　　　　　食べ物の下には天婦羅、寿司、ラーメン・・・・
　　　　　　　装飾品の下には指輪、ネックレス、ブローチ・・・
(3)　乗り物から下に、最後の品名まで選択し、「挿入」「名前」「作成」「上端行」(チェック済)で「ＯＫ」。
(4)　食べ物、装飾品についても同様に範囲指定します。

Ｅ5に分類を、F5に所属品名を入力するとして、

Ｅ5に入力規則で乗り物、食べ物、装飾品が入力できる様にする。
Ｆ5の入力規則「リスト」で「元の値を」に　=INDIRECT($Ｅ$5)　と入力。

お礼

ippuさん、ご回答ありがとうございます。

新しいワークシートを作ってやってみました。
Ａ５のところで「食べ物」を選択すると、Ｂ５で「天麩羅、ラーメンetc.」だけがポップアップされるところまではうまくいきましたが、Ｂ５のセルをドラッグでコピーしていくと、Ｂ６、Ｂ７・・・のセルがすべてＦ５と同じ分類になってしまいます。

つまり、Ａ５とＢ５が「食べ物」のカテゴリーになっていると、Ｂ５で「寿司」をＢ６で「ラーメン」を選択することはできるものの、Ａ６が「装飾品」になっていても、Ｂ６では「ネックレス、ブローチetc.」は選択できず、Ｂ５と同じ「ラーメン、寿司etc.」の中からしか選択できないんです。

いったいどこがいけないのでしょう・・・
また、なぜか、入力規制を設定する列を、シートの左端のＡ列から始めれば、リストの項目がポップアップで選べるのですが、ＥとかＦなどの列に入力規制を設定すると、ポップアップの項目がリストの名前だけになってしまい、それを選ぶと「＃VALUE!」と出てきます。

何か基本的なことが間違っているんでしょうか。。。

せっかく回答してくださったのに、飲み込みが悪くてすみません。

回答No.2

shiga_3

＃１です。
まず、とりあえず先程の回答中の誤りの訂正を。
・Ｅ２、Ｆ１以下の→Ｅ２、Ｆ２以下の
・Ｅ列Ｆ列の適当な範囲→Ｅ列の適当な範囲（※Ｆ列はドラッグ→コピーの方を使ってください）

さて追加のご質問についてですけど、
＞既存の入力ずみシート（リストが設定されているのとは別のシート）で、すでにリストが設定されているものに、同じ入力規制の設定をするには、どうしたらいいんでしょう？

ちょっと分かりづらい点があるので、違っていたら申し訳ないのですが、例えば
・Sheet1に先程の「乗り物・食べ物」のリストと、そのリストを参照するＥ列Ｆ列がある。
・Sheet2に例えば「履き物・飲み物」のリストと、そのリストを参照するＥ列Ｆ列がある。
・そのSheet2のＥ列Ｆ列も、Sheet1の「乗り物・食べ物」のリストを参照するようにしたい。
ということでしょうか。

だとすると、Sheet1のＥ列を範囲選択してコピー→Sheet2のＥ列に貼り付けるか、またはＥ列を選択してデータ→入力規制の条件の設定で、元の値を「=(Sheet1のA1:B1に定義した名前)」から「=(Sheet2のA1:B1に定義した名前)」にすればできると思いますが・・・。（Ｆ列は変更する必要はありません。なおSheet2のＥ列Ｆ列に既に入力してある文字があればそのまま残ります）

もし違う意味でしたら、２つのシートの関係についてもう少し具体的に書いてもらえれば、回答できるかもしれません。

お礼

shiga_3さん、二度もご回答くださいましてありがとうございます。

私の質問のしかたが悪くてすみません。別のシートにあるというのは、例えば
Sheet１に「乗り物、食べ物、履物、飲み物」のリストがある。
このリストに「物の名前」という名前がついている。

Sheet２には、「物の名前」リストを作成する前から入力している表がある。
「Ａ列：４月１日、Ｂ列：乗り物、Ｃ列：飛行機、Ｄ列：20,000円」
「Ａ列：４月２日、Ｂ列：食べ物、Ｃ列：ラーメン、Ｄ列：900円」
という具合です。すでに数百列が入力されていて、Ｂ列、Ｃ列に入力済みの値は、リストとして管理されています（ポップアップで選べます）。

Sheet２に今後入力していくとき、例えばＢ列で「乗り物」を選んだら、Ｃ列に「飛行機、電車、タクシーetc.」だけがポップアップされるようにしたいのですが。。。

ご面倒をおかけして申し訳ございません。
よろしくお願い致します。

補足

もう一つ質問ができてしまいました。

新しいシートにリストを作成し、それと同じシートに入力規制の設定をした場合には、うまくいくのですが、新しいシート１にリストを作成し、次に新しいシート２を作って、そこに入力規制の設定をすると、リストの最初の項目（リストが、「食べ物、乗り物、履物、飲み物」だとすると、最初の「食べ物」）だけしかポップアップされません。
そのため、隣の列にも「食べ物」の下位カテゴリーである「寿司、天麩羅、ラーメンetc.」しか出てきません。

別のシートに設定する場合には、特別なことをする必要があるんでしょうか。。。
レベルが低い質問ですみません。

回答No.1

shiga_3

QNo.766752の＃２の回答はＥ１のセルとＦ１のセルだけの場合のものです。
Ｅ２、Ｆ１以下のセルで同様のことを行いたい場合には、Ｅ１のセルとＦ１のセルを選択した後、右隅にカーソルを持っていって、カーソルが十字になったところで下にドラッグすれば入力規制の設定もコピーされます。
またあらかじめＥ列Ｆ列の適当な範囲を指定した後で、＃２の回答の後半の手順を行っても同じです。

お礼

できました！　どうもありがとうございました。m(_ _)m

あの・・・既存の入力ずみシート（リストが設定されているのとは別のシート）で、すでにリストが設定されているものに、同じ入力規制の設定をするには、どうしたらいいんでしょう？

もしお時間があったら、教えていただけると助かります。
ご回答ありがとうございました。