Excel（エクセル）で名前定義したリストから選択入力する方法
- Excel（エクセル）の名前定義をしたリストから、indirect関数を利用して選択入力できるようにしたい場合、参照範囲に複数の範囲があるとリストが表示されないことがあります。解決法と表示されない理由について教えてください。
- Excel（エクセル）の選択入力で、名前定義したリストを使いたい場合、OFFSET関数を利用する方法もありますが、こちらでもリストが表示されないことがあります。解決法と表示されない理由について教えてください。
- Excel（エクセル）で名前定義したリストから選択入力する方法で、参照範囲に複数の範囲がある場合やOFFSET関数を利用する場合に、リストが正しく表示されない理由と解決法について教えてください。
- ベストアンサー
Excel（エクセル） 名前定義をしたリストから、indirect関数を利用して選択入力できるようにしたいのですが…
次のようなことができず困っています。 Sheet1とSheet2があり、Sheet2には"数字"、"英語"と名前定義された２つのリストがあります。Sheet1のセルA1には"数字"か"英語"どちらかの文字列を選択入力し、B1には、もしA1に"数字"を入力していればリスト"数字"から、もし"英語"を入力していればリスト"英語"から選択入力できるようにしたいのです。つまり、Sheet1のセルA1、B1の入力規則の種類をリストとして、元の値をそれぞれ、 ・Sheet1!A1には=数字,英語 ・Sheet1!B1には=indirect($A1) としています。 質問(1)） ここまでなら何の問題もないようですが、問題はSheet2の"数字"と"英語"の参照範囲です。 ・"数字"の参照範囲は=Sheet2!$A$1:$A$10,Sheet2!$C$1:$C$10 ・"英語"の参照範囲は=Sheet2!$B$1:$B$10,Sheet2!$D$1:$E$10 と複数範囲におよんでいる場合、Sheet1!B1にはうまくリストが表示されません。解決法、およびなぜ表示されないのか、どなたかご教示お願いいたします。 質問(2)） Sheet2の"数字"と"英語"の参照範囲を ・=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A)) ・=OFFSET(Sheet2!$B$1,,,COUNTA(Sheet2!$B:$B)) とした場合も同様にSheet1!B1にはうまくリストが表示されません。こちらの方も解決法と表示されない理由をお願いいたします。
- cpdqp454
- お礼率41% (7/17)
- オフィス系ソフト
- 回答数6
- ありがとう数4
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
- ベストアンサー
（シート名を指定する必要がない）次のような手法は如何でしょうか？ Sheet2 A B C D 1 数字1 英語1 数字2 英語2 2 11 Andrew 31 Siew 3 12 Andy 32 Steve 4 13 Bob 33 Stu 5 14 Byron 34 Tibor 6 15 Carol 35 Tim 7 16 Chris 36 Tom 8 17 Clarence 37 Wellington 9 18 Dan 38 Wes 10 19 Dennis 39 William 11 20 Don 40 Wing Sheet2 において １．範囲 A1:D100 を選択 ２．[挿入]→[名前]→[作成]を実行 ３．“上端行”だけにチェック入れ ４．[OK]をクリック Sheet1 において ５．セル A1 および B1 に[入力値の種類]を“リスト”とし、かつ、[元の値]を 次とする[入力規則]を設定 A1: 数字1,数字2,英語1,英語2 B1: =OFFSET(INDIRECT(A1),,,COUNTA(INDIRECT(A1)))
その他の回答 (5)
[ANo.5この回答へのお礼]に対するコメント、 》 [挿入]→[名前]→[作成]を実行」で具体的に何をしてるのかわかりませんが… [ヘルプ]の「ブック内のセルに名前を付ける」を読めばよろしいかと。 要は、セル（範囲）に名前を付けているのですよ！
[ANo.2この回答へのお礼]に対するコメント、 「元の値はエラーと判断されます。続けますか？」というメッセージの再現テストをしていますが、成功しません。ところで… 最初の質問中で、「Sheet1のセルA1」「の入力規則の種類をリストとして、元の値を」 「=数字,英語」「としています。」「ここまでなら何の問題もないようですが…」と書いておられますが、私の場合、問題が出ます。 [データの入力規則]メニューの[元の値]ボックス内に式 =数字,英語 と入力して[OK]をクリックした時点で「条件 データの入力規則 には、OR 演算、AND 演算、配列定数は使用できません。」というエラーが出るのです。 貴方の場合は本当に「何の問題もない」のですか？
補足
すいません。誤記です。「=」は不要でした。正確にはデータの入力規則]メニューの[元の値]ボックス内に「数字,英語」と入力しました。
》 質問(2)） Sheet2の"数字"と"英語"の参照範囲を 》 ・=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A)) 》 ・=OFFSET(Sheet2!$B$1,,,COUNTA(Sheet2!$B:$B)) 》 とした場合も同様にSheet1!B1にはうまくリストが表示されません。 何かエラーメッセージが出るならそれもキチンと示すべきかと。 「抽出条件 データの入力規則 で、他のワークシートまたはブックへの参照は使用しません」では？
補足
エラーメッセージは 「元の値はエラーと判断されます。続けますか？」 です。 「抽出条件データの入力規則で、他のワークシートまたはブックへの参照は使用しません。」 のエラーメッセージは、Sheet1のセルB1の元の値に直接 =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A)) とした時に現れます。
[入力規則]が受け入れる「リスト」は“単一列内の連続した範囲”の単一リストでなければならない、が私の理解です。 貴方は、Sheet1!B1 において（結果的に）複数のリスト（範囲）を指定しているので、期待する結果が得られないのだと思います。
お礼
回答ありがとうございます。 どうやらそのようですね。試しに"数字"の参照範囲である Sheet2!$A$1:$A$10,Sheet2!$C$1:$C$10 の値を Sheet1!$A$2:$A$11,Sheet2!$C$2:$C$11 にコピーして、 Sheet1!のセルB1の入力規則の元の値を上記と同じ =Sheet1!$A$2:$A$11,Sheet2!$C$2:$C$11 にすると、エラーメッセージが出ました。 質問(1)に関してはそういうもんなんだということで納得しました。 どなたか質問(2)についてお分かりになられる方おられましたらお願いいたします。
実験はしていませんが、 ・"英語"の参照範囲は =Sheet2!$B$1:$B$10,Sheet2!$D$1:$D$10,Sheet2!$E$1:$E$10 と、複数列を一度に指定せず、一列づつに分解して指定したらどうでしょう。
お礼
回答ありがとうございます。 やってみましたが駄目なんです。 "数字"の方もうまくいきませんから…。
関連するQ&A
- 名前の定義を使ったリストを動的に
Excel2003を使っています。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」という形で名前の定義を行います。 入力規則のリストにこの名前を指定することで、後でSheet2にデータを付け加えても修正する必要のない動的なリストを作ることができますよね。 それともう一つ、入力規則のリストに「=INDIRECT(A1)」と指定することで、A1セル内に書かれてある文字の名前を指定したことになり、リストとして扱えますよね。 この二つの組み合わせはできないのでしょうか？ 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」に「名前１」とつけたとし、Sheet1のA1セルに「名前１」と記入し、B1セルの入力規則に「=INDIRECT(A1)」を指定してもエラーとなってしまいます。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」という形でいくつか動的なリストを作り、Sheet1のA1セル内を書き換えることでB1セルのリストが動的に変わるということをしたいのですが上手くいきません。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」ではなく、「=Sheet2!$B$2:$B10」のように固定してしまえば上手くいくのですが、これだとデータを付け加えるたびに修正しないといけません。 二つを組み合わせてリストを使う方法はありませんか？
- ベストアンサー
- その他MS Office製品
- エクセル2007 OFFSET関数 INDIRECT関数
エクセル2007でOFFSET関数とINDIRECT関数を用いてセルの入力規則をして、リスト表示したいと思っています。 入力規則のリストは動的な値を設定したいと思っています。 ＡＢＣＤＥ １あいうえお ２かきくけこ ３さしすせそ ４たちつてと ５なにぬねの というシートA列に”データベース1”B列に”データベース2”・・・といった具合に名前を定義します。このデータベースは行がどんどん増えていく可能性があるため、名前の定義の参照範囲欄に (1)=OFFSET($A$1,0,0,COUNTA(A：A),1) としました。 別シートに 表示したいセルで入力規則→設定タブ→入力値の種類→リストを選択。 元の値の欄に (2)=INDIRECT(A2&"1",FALSE) としました。 ※A2は”データベース”と入力してあるセルです。 ここからがわからないのですが 上記式(1)、(2)の両方とも単独で使用した場合は欲しい値が得られるのですが、組み合わせて使用した場合はリストが出てこなくなってしまいます。 1)組み合わせて使うことはできないのですか 2)ほかにいい方法はありますか ということを質問します。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- INDIRECTと名前の定義でリストを作成
どなたか分かる方教えください。 A1のセルにコードを入力、B1のセルに商品 を入力したいと考えています。 但し、B1の商品はA1で指定したコードの商品郡を入力するようにしたいのです。 コードと商品郡は別シートに作成しています。 別シート） Ａ Ｂ Ｃ Ｄ Ｅ 1 2 A B C D E 3 ・ ・ ・ ・ ・ 4 ・ ・ ・ ・ ・ ２行が全てコード、３行以降がＡ～Ｅの商品郡 になります。 ３行目以降のセルには関数を入れて 別シート_２ より集計したものになります。 商品郡の数はコードによってバラバラです。 ２行目の記入セルを「名前の定義」で「コード」としてあります。 各列を 「名前の定義」、「Ａ」 ｛範囲：A3：A150｝ 「Ｂ」 ｛範囲：B3：B150｝のようにしました。※今後増減するため、セルは多く設定しました 入力シートの A1にリスト範囲"=コード" B1に"=INDIRECT（A1）" リストはでたのですが、空白セルが多いため使いづらく 空白セルを表示させないために 別シート!A1 に ="別シート!A3:A"&COUNTA(A3:A150） と入力し自分なりに可変するような式を作ったのですが、 入力シート B1に リスト範囲"=Ａ"とすると 望むようなリストが出来たのですが、 リスト範囲"=INDIRECT（入力シート!A1）" とすると、 元の値はエラー・・・とエラー表示がでて、リストが表示されません。 どのようにしたらリストが表示されるようになりますでしょうか。 もしくは、増減するセル範囲を空白なしに表示させるにはどのようにしたらいいでしょうか。 質問の説明が不十分すぎて申し訳ないですが、分かる方教えていただけたら嬉しいです。
- ベストアンサー
- オフィス系ソフト
- エクセル名前の管理
エクセルの名前の管理について質問です。 Aのシートでデータの入力規則に従い、別シート（B)で作成したリストを指定してプルダウンメニューで選択できるようにしています。 Bのシートでは列単位でリストを作成しています。 リストを追加すると自動でプルダウンメニューに追加するには、COUNTA関数を使って、入力の個数を戻り値としてとらえればできるのはわかったのですが、 名前の管理の参照範囲を =OFFSET(B!$R$3,0,0,COUNTA(B!$R:$R)-1,1) とすると上手くいくのですが、 COUNTAの範囲を「R列」全てではなく、R3～R100までとした時 =OFFSET(リスト!$R$3,0,0,COUNTA(リスト!R$3:R$100),1) なぜかエラーになってしまいます。 R２にデータが入っておりこれはカウントさせたくない為、上では戻り値-1させています。 これをR3～R100までのカウントにして-1を省いてみました。 セルに =COUNTA(リスト!R$3:R$100） として、R列のデータ入力されている個数を表示させるときちんと出るのですが、上の構文だとおかしいのでしょうか？ また、気になるのは、名前の管理を保存して閉じ、再度変更した箇所を開くと =OFFSET(リスト!$R$3,0,0,COUNTA(リスト!XF$3:XF$100),1) と指定範囲が勝手に変わっています。 名前の管理の参照範囲が勝手に変わることなどあるのでしょうか？ バグなのか、関数の指定がおかしいのかがわかりません。 どこが悪いのか、またどのようにすればいいのかわかる方ご教授お願い致します。 使用しているのはMSオフィスのエクセル2007です。
- ベストアンサー
- その他MS Office製品
- 連動したドロップダウンリストを INDIRECT関数を利用して作成したのですが・・・
お世話になります。EXCEL2003での質問です。 県選択→結果を受けて市町村選択 というドロップダウンリストを下記の要領で作成しました。 名前の定義 ： 名前；都道府県 参照範囲；シート名!$A$2:$A$7 名前の定義 ： 名前；○○県 参照範囲；シート名!$B$2:$Z$2 各県の名前を同様に定義する。 一つ目のドロップダウンリスト（別シートのA1) 入力規則 ： 入力値の種類；リスト 元の値；=都道府県 二つ目のドロップダウンリスト（別シートのB1) 入力規則 ： 入力値の種類；リスト 元の値；=INDIRECT($A$1) 上記で問題なく連動したドロップダウンリストを作成できました。が、市町村合併に対応させたくて参照範囲を可変に・・・と思い、名前の定義の参照範囲のところを次のように変更しました。 名前の定義 ： 名前；○○県 参照範囲；=OFFSET(シート名!$B$2,0,0,1,COUNTA($2:$2)-1) すると二つ目のドロップダウンにリストが出てきません。 B1の入力規則に戻って元の値が=INDIRECT($A$1)になっているのを確認してOKをクリックしたところ"元の値がエラーと判断されます"と警告が出ました。 そこでINDIRECT関数を噛ませずに直接、元の値を=○○県 にしたところ正常にドロップダウンにリストが表示されました。 これは、名前の定義の参照範囲に関数を使用したときにはINDIRECT関数が使用できない、ということなのでしょうか。どなたか、お詳しい方、教えていただけませんでしょうか。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル リストの連動について教えてください。
エクセルの入力規則のリストを連動させて使用させたいのですが上手くできないのでご教授願いたいと思います。 まず、A１に食材の分類（野菜、肉など）をリストで選択できるようにしてあります。 A1の結果によってB1に食材（人参、玉ねぎなど）をリスト表示したいのですが、 （別シートに分類ごとにセル範囲に名前を付けてあります） 元の値を=indirect(A1)とするとエラーが出ます。 （A1には先に入力してあります） 食材のセル範囲の名前をoffsetを使って定義しているのですが （確認しましたが関数自体は間違っていません） offsetを使っているとエラーになるようなのです。 （A1:A5のようにセル範囲名前を定義しているときはちゃんと出来ます。） いろいろ調べましたが名前の定義、リストの連動の仕方自体は間違っていないと思うのですが・・・ 連動させるときはセル範囲をoffsetなどを使って可変にすると出来ないのでしょうか？
- ベストアンサー
- オフィス系ソフト
- 入力規則-リスト選択で，リストの頭の行から表示させたいのに。
1シート目に入力規則-リスト選択したいセルがあり， 2シート目にあるデータから選択するため，名前-定義で「行」に名前をつけて，範囲設定した。 ↓ 入力規則設定では空白を無視にチェックしてみたのですが・・・ 1シート目でプルダウン？（▼）で選択しようとすると，窓には2シート目リストの入力のない行（空欄）がデフォルトとして表示されてしまいます。 ↓ リストの先頭行から表示されるようにするにはどうしたら良いでしょうか？そのような技がありましたら教えて下さい。 （「行」を範囲選択するのがマズイでしょうか？行が増えても1シート目の範囲を設定し直さなくて良いかな，と思って行を範囲設定してみました。） ちなみに，Offset関数（？）は全くわかりません。
- ベストアンサー
- オフィス系ソフト
- Excel2010 入力規則のリスト自動対応
こんにちは。 EXCEL2010 入力規則のリストについて質問です。 名前の定義の設定とINＤＩＲＥＣＴを使ってリストを連動させる方法は、 理解できています。 選択リスト選択する内容が毎月追加・削除され一つずつ参照範囲を 直すのが大変です。 リスト選択する内容を自動で更新できる方法はないでしょうか？ リストデータシート ・部署名→1行目（現在Ａ1～Ｇ1にあり以下式で名前「部署名」と付けました。 =OFFSET('リストデータ'!$A$1,0,0,1,COUNTA('リストデータ'!$1:$1)) 氏名→それぞれ50名程あります。Ａ2～Ｂ56 入力シート Ａ6～Ａ56まで部署を選択します。（部署の選択設定は出来ています。） Ｂ6～Ｂ56にそれぞれのＡ列で選択した部署の名前リストが出るようにしたい。 希望通りの設定が可能でしょうか？ どうぞ宜しくお願い致します。
- ベストアンサー
- その他MS Office製品
- EXCELのリストについて（相対？
EXCELのリストで別シート参照してます。 シート１ A B C D ・・・・ １○○○ リストでシート２のA1を参照 ２××× リストでシート２のA２を参照 ３△△△ リストでシート２のA３を参照 ・ ・ ・ シート２ A B C D ・・・・ １○○○ リストでシート１のA1を参照 ２××× リストでシート１のA２を参照 ３△△△ リストでシート１のA３を参照 ・ ・ ・ という形でワークブックを作りたいのですが、シート１（２）のB1セルの入力データでリストを選択して、元のデータの欄に =INDIRECT("Sheet2!A1") と入れ、別シートを参照するようにし、それを相対的にコピーしたいのですがやり方がわかりません。 セルの右下にカーソル持って行って黒い十字にして引っ張ってっても 下のセルも参照位置が=INDIRECT("Sheet2!A1")になってしまうんです。 手打ちで直そうかと思ったんですが、効率が悪すぎるので・・・ 間違ってるところがあれば教えてください。 もしくは別の方法で同じことができるのであればそれでももちろんかまいません！ おねがいします。
- ベストアンサー
- オフィス系ソフト
- エクセルの名前の定義について。
ご覧いただきありがとうございます。 エクセルの名前の定義についての質問です。 (1)【sheet1】 A2：A6に『aa』、 B2：B10に『bb』、 C2：C6に『cc』という名前を付けたとします。 ちなみにそれぞれのセルには文字列が入力されています。 (名前の定義の《範囲》はブックではなく、sheet1) (2)【sheet2】 B2：D6に『aa』、 B7:D15に『bb』、 B16：D20に『cc』と、sheet1と同じ名前を付けました。 それぞれB列・C列・D列には数字が入力されています。 (名前の定義の《範囲》はブックではなく、sheet2) ちなみにA列には、sheet1に入力されている文字列と同じものが入力されています。 A2：A6にはsheet1で『aa』と名前をつけた範囲と同じ文字列。 A7:A15にはsheet1の『bb』と同じ文字列。 A16：A20には『cc』と同じ文字列です。 ここからが本題です！ (3)【sheet3】 A2に何らかしら関数を入れて、【sheet1】で作った名前(aaやbbやcc)が1つ表示されるようにしたとします。 そしてB2には、A2で表示された名前の中の文字列が1つ表示されるようにします。 (例えばA2に『aa』と表示されたら、B2には◯◯) 更にC2には、B2に表示された◯◯という文字列を使って、【sheet2】の名前『aa』の中のB列と、A列にある◯◯とが交差するセルに入力されている数字(図でいう(5))を表示させる関数を入力したいのです。(INDEXやINDIRECT、MATCH関数など…) 他に例えば、A2に『bb』(sheet1の名前)、B2に★★と表示されたら、C2には【sheet2】の名前『bb』の中のB列と、A列にある★★が交差するセルに入力されている数字を表示させたいです。 同じように、今度は、D2には、B2に表示された◯◯という文字列を使って、【sheet2】の名前『aa』のC列と、A列にある◯◯の交差するセルに入力されている数字を表示させたいです。 長々とややこしくてすみません… 説明分かりますでしょうか？？ つまりは【sheet3】のC2・D2・E2に入力する関数が知りたいです。 自分なりに関数を入力してみたのですが、A2で表示された名前がsheet1での名前なので、sheet2の情報を引き出す事が出来ません。 わざわざsheet1とsheet2でシートを分けたのは、同じシート内で同じ名前が付けられないと思ったからです！ どなたか分かりましたら教えて下さい！ どうぞよろしくお願い致します。 ※ちなみに、貼付した図の『・』は以下省略の意味で使ってます。
- 締切済み
- Excel（エクセル）
お礼
お礼遅れました。 できました。すばらしい。諦めかけてましたができるもんなんですねー。ありがとうございました。 ただ、「２．[挿入]→[名前]→[作成]を実行」で具体的に何をしてるのかわかりませんが…。すいません、素人質問で…。これくらいは自分で調べます。 親切に教えていただき本当にありがとうございました。