名前の定義を使ったリストを動的に

質問

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」のように固定してしまえば上手くいくのですが、これだとデータを付け加えるたびに修正しないといけません。

二つを組み合わせてリストを使う方法はありませんか？

回答No.6

kagakusuki · ベストアンサー

例えば、次の様な方法は如何でしょうか。
　まず、Sheet2の

B1セルに　　名前1
C1セルに　　名前2
D1セルに　　名前3

と入力しておき、
B列～D列の2行目以下に、Sheet1のB1セルに表示させる各リストのデータを入力しておきます。
　そして、Sheet1のA1セルの入力規則の「元の値」欄には、

=INDIRECT("Sheet2!B1:D1")

と入力しておきます。
　続いて、Sheet1のB1セルの入力規則の「元の値」欄には、

=OFFSET(INDIRECT("Sheet2!B2"),,MATCH($A1,INDIRECT("Sheet2!B1:D1"),0)-1,COUNTA(OFFSET(INDIRECT("Sheet2!A:A"),,MATCH($A1,INDIRECT("Sheet2!B1:D1"),0)))-1)

と入力しておけば良いと思います。

お礼

すごい！
名前の定義を使わなくてもできるものなんですね。
関数の意味はよく分からないんですが、これを元に勉強してみます。
ありがとうございました！

回答No.7

keithin

ご理解していただいたとおり，1000個でも１万個でも必要にして十分の範囲で指定します。
結果はきちんと可変の範囲で表示されます。

とは言え，それではこんな手もあります。

myRng1として
=Sheet2!$B$2:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B))
myRng2として
=Sheet2!$C$2:INDEX(Sheet2!$C:$C,COUNTA(Sheet2!$C:$C))
などでそれぞれ可変範囲の名前を定義。（実際の数式は趣味で作成してください）

更に
myRngListとして
={"myRng1","myRng2"}
を登録しておきます。

A1セルに
myRng1
などを記入。

入力規則の数式は
=CHOOSE(MATCH(A1,myRngList,0),myRng1,myRng2)
としておきます。

お礼

定義の数式は、範囲にINDEXなどの関数も当てることができるんですね。
勉強になりました。ありがとうございます。

回答No.5

keithin

ん？？
回答の手順をきちんと実施してみた上で「無理」と仰有っているのでしょうか？
まさか試しもせずに最初の一言であきらめたという事では無いと思いますが…

＞あくまで「２行目からデータが入っている範囲」にこだわってるのですが・・・。
＞無理そうですよね。

回答の手順を行うことで，あなたが欲しかった「２行目からデータが入っている範囲」がリストとして表示されます。

それともあなたのやりたかった事は「入力規則に必要なリストを表示する」のとは違うのでしょうか？
私があなたのコダワリポイントを読み違えているのかも知れませんので，回答の手順で何が無理なのか，もうちょっとしっかり補足をお願いします。

お礼

ありがとうございました。

補足

ANo.4での方法はB2:B50といった範囲を固定した方法ですよね。
この部分をどうしても可変にしたいんです。
ANo.4も試しましたが、B50を超えてデータを入れたりすると対応できなくなりますよね？
まあ、B1000ぐらいにしておけば良いとは思うんですが、今回はあくまで可変にこだわっているということなんですよ。
この範囲の部分だけはどうにもなりませんか？

回答No.4

keithin

たとえばSheet2のB列のデータ範囲の十分大きな範囲（B2:B50など）にmyName01
Sheet2のC列の十分大きな範囲（C2:C50など)にmyName02と名前を定義しておきます

A1に
myName01
などを記入
入力規則のリストの式は
=OFFSET(INDIRECT(A1),0,0,COUNTA(INDIRECT(A1)),1)
などのようにします。

お礼

ありがとうございました。

補足

やはりそういう指定しかできないでしょうか？
「十分大きな範囲」ではなく、あくまで「２行目からデータが入っている範囲」にこだわってるのですが・・・。
無理そうですよね。

回答No.3

KURUMITO

補足のようなことでしたらB列ならB、C列ならCの文字をA1セルに入力し、B1セルの入力規則では次の式を入力すればよいでしょう。

=INDIRECT("Sheet2!"&A1&"2:"&A1&COUNTA(B:B))

お礼

ありがとうございました。

補足

シート名が間違えていました。

「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet2!$B:$B)-1)」を「名前１」と定義。
「=OFFSET(Sheet2!$C$2,,,COUNTA(Sheet2!$C:$C)-1)」を「名前２」と定義。

「名前１」のリストはSheet2のB列２行目から下すべてで、データが入ってるセルのみ。
「名前２」のリストはSheet2のC列２行目から下すべてで、データが入ってるセルのみ。
二つともSheet2の書かれてあります。

これをSheet1のA1セルに「名前１」と入れると、同じSheet1のB1セルで「名前１」のリストが選べるようになり、A1セルに「名前２」と入れるとB1セルで「名前２」のリストが選べるようにしたいです。

ANo.3でアドバイスいただいた式を入れてみましたが、上手くいきませんでした。
たびたび申し訳ありません。

回答No.2

KURUMITO

回答No1です。ごめんなさい表現が悪かったですね。
お示しした式はB1セルに入力するのではなくB1セルの入力規則を設定する際に「リスト」で数式の窓に入力する式です。

お礼

ありがとうございました。

補足

ちょっと書き方が難しく、私の方こそ上手く伝え切れなくて申し訳ありません。

リストは一つではなく、いくつかあるんです。
「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」を「名前１」と定義。
「=OFFSET(Sheet2!$C$2,,,COUNTA(Sheet1!$C:$C)-1)」を「名前２」と定義。

このように1列につき一つの名前を定義して、sheet1のA1セルにその名前を書き換えるだけで、B2セルのリストを切り替えるということをしたいのです。

回答No.1

KURUMITO

シート1のB1セルに次の式を入力することで解決できますね。A1セルに式を入力する必要もありません。

=INDIRECT("Sheet2!B2:B"&COUNTA(B:B))

お礼

ありがとうございました。