INDIRECTと名前の定義セル参照拡張範囲

質問

只今エクセル勉強中です。よろしくお願いします。
画像の貼り付けがよくわからなかったので
シート画面のコピーを添付データにしています。

A列に分類（データA4:A5）
B・C列に商品というデータがあるとします。
名前の定義　範囲から作成でA3:C5を上端行基準で
分類・パソコン・プリンタと名前をつけます。

E2セルにデータの入力規制で
=分類
とします。
E2の入力値に対してF2の入力範囲を切替えたいと思います。
そこでＦ2の入力規則で
=INDIRECT(E2)
とします。
ここまではOKなのですが、分類・パソコン・プリンタの種類が増える事を
想定し名前範囲を拡張したいと思います。
そこで名前の定義で
分類の範囲を
=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,1)
に変更
パソコンの範囲を
=OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B:$B)-1,1)
プリンタ範囲も同様に変更しました。

そして6行目以降に項目を追加すると
E2セルではプルダウンに追加したものが反映されます。
しかしＦ2ではプルダウン項目がなくなりました。

何が間違っているのでしょうか？
Excel2007使用です。
よろしくお願いします。

回答No.3

tom04 · ベストアンサー

No.1です。

補足の件について・・・
前半部分は仰る通りで間違いありません。

後半部分の
＞その後の
＞COUNTA (OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))
＞ここがよく解っていません。
＞OFFSETの基準がA:Aというのはどういう事なのでしょうか？
＞Ａ列全体？？セルに=A:A　は0になりますが・・・？？？
に関してですが、

ココで一番重要なのは
COUNTA関数の「範囲」を決定してやらなければならないコトです。
仮にその範囲が　B列と判っている場合は
=COUNTA(B:B)
でOKなのですが、今回の質問の場合はE列入力規則のデータによって列が決まってきます。
それを判定するために
A列を基準として何列右側の列をCOUNTA関数の範囲にするか？という数式です。
それをMATCH関数で判定しています。
結局上記数式の「B:B」部分が
前回投稿した数式の
＞OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)
の部分となります。
すなわち　A列全体を基準列として、MATCH関数でE2と一致する列番号を取得します。
仮にそればB列にあれば「2」という結果になりますが、A列から2列右ではC列になってしまいますので
「-1」としてB列全体がCOUNTA関数の「値1」の範囲となります。

以上長々と書きましたがこの程度でよろしいでしょうか？m(_ _)m

回答No.4

tom04

NO.1・3です。
たびたびごめんなさい。

No.1の数式で少し抜けている部分がありました。
おそらくリストで余計な空白が一つ表示されていたと思います。
数式を↓に変更してください。
=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))-1)

数式でなぜCOUNTA関数が必要か？という説明が抜けていました。
リスト表示させる際に余分な空白セルを表示させないためのCOUNTA関数です。
No.3の説明で列決定方法は理解していただいたとおもいますが、
OFFSET関数の「高さ」部分にCOUNTA関数を使用していますので、
検索した列のデータ数だけをリスト候補に表示させるために上記のような数式にしています。
そして、COUNTA関数で得られたデータ数は「項目」も含まれていますので、
OFFSET関数で項目行の1行下を基準として、その列のデータ数より「-1」の高さ分だけをリスト表示させています。

何度も失礼しました。m(_ _)m

お礼

tom04様

こんにちは、度々お忙しいところご丁寧にありがとうございました。

手とり足とりのご解説で、頭の固い私にもようやく理解できました。

私、OFFSET関数根本的に理解していなかったようですね。
OFFSET関数：=OFFSET(基準，行数，列数[，高さ][，幅]）

この基準と言うものは、必ず１つのセルでありそこを基準に
行数、列数移動と思い込んでいました。

質問の状態
E2=パソコンなら
=COUNTA(B:B)= OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)
B列全体と言う事なのですね。

OFFSETはここを基準にいくつかずれて、ずれた所からこれだけの範囲と
しか頭に無かったです。この式は列全体を基準にする式だから高さや幅が不要なのですね。

ようやく理解できました。本当にありがとございます。
もうおひとりお付き合いを頂いているnishi6様の
基準に名前範囲ということも、B:B列全体と置き換えて
名前範囲全体となるのですね。

しかし皆さん凄いですね～知識力に柔軟性
エクセルを使いこんでいる方々からすればこんな事は初歩中の初歩ってところなのでしょうか
まだまだ、マスターでききれていませんので九九の表でも利用してOFFSET関数
完全に理解を深めたいと思います。

お世話になりありがとうございました。

回答No.2

nishi6

質問内容はこういうことでしょうか。

【参考】
EXCEL 入力規則・リスト応用編 いろいろ
http://note.chiebukuro.yahoo.co.jp/detail/n149222

ポイント：大分類（例では「種類」）リストを、「入力データ数に応じて範囲を変更する」方法で名前を付けた場合の２層目リスト表示方法
・「元の値」に、「=INDIRECT(A2)」と入力しても、リストは表示されません
・名前ボックスに表示されない名前は、「=INDIRECT(A2)」では表示されません

とあります。本当にINDIRECTを使うと名前ボックスに表示されませんね。

対応を考えてみました。（なぜダメかは考えないようにしました）

●範囲名の設定
　分類: =OFFSET(Sheet1!$A$4,,,COUNTA(Sheet1!$A$4:$A$11))
　パソコン: =Sheet1!$B$3:$B$100
　プリンタ: =Sheet1!$C$3:$C$100

「分類」は質問通りの設定で、「パソコン」と「プリンタ」の範囲名は行数を多目にしています。【参考】にあるように「２層目リスト」にINDIRECTを使うとダメということなのでこうしました。

●入力規則の元の値
　E2の入力規則の元の値
　　　=分類

　F2の入力規則の元の値
　　　=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)

「パソコン」と「プリンタ」の範囲名は行数を多目に設定していますが、「入力規則の元の値」の設定でデータ数にしています。質問にあった範囲名の設定に近い算式です。これを「元の値」の設定で使っています。添付図のように不要なスペースはなくなるはずです。

参考URL：

http://note.chiebukuro.yahoo.co.jp/detail/n149222

お礼

nishi6様

こんにちは、早々にご回答頂きありがとうございました。
ご返答が遅くなりました。申し訳ございませんでした。
お示し頂いたサイト等で悪戦苦闘しておりました。

範囲を広範囲にしておきながら、COUNTAで整えるなど
知識もさることながら、柔軟な頭おもちですね～
素晴らしいです。ありがとうございました。

ただ、こちらでももし良かったらお伺いしてよろしいでしょうか？

まず、参考でお示し頂いたサイトの

I．リストから入力したデータに応じた、リストを表示させる
ここの分ですが
肉の名前の範囲を
可変に
=Sheet1!$B$2:OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1)
変更し説明どおり
２層目リスト表示方法
=OFFSET(INDIRECT(A2),,,COUNTA(INDIRECT(A2)),)
の入力規則設定でもやっぱりプルダウンが選択できなくなるのですが
説明通りになりますか？

あと、ご説明いただいた

F2の入力規則の元の値
　　　=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)
ここがよく解っていません。

COUNTA(INDIRECT(E2))-1
ここでのINDIRECT(E2)
はCOUNTAですから名前”プリンタ”範囲のデータ数-1
で宜しいのですよね。

=OFFSET(INDIRECT(E2),
ここのOFFSETの基準が名前範囲？
というのが理解できていません。
これはどういった意味になるのでしょう？

結果はおかげをもちましてＯＫですが、もしよろしかったらお暇な時にでも
またご教授下さい。

補足

nishi6様

こんにちは、先日はありがとうございました。

ご回答お礼で

=OFFSET(INDIRECT(E2),
ここのOFFSETの基準が名前範囲？
というのが理解できていません。
これはどういった意味になるのでしょう？

こちらの分ですが、もうおひとりのご回答を頂いていた
tom04様のご解説により基準を範囲指定するって事が理解できました。

F2の入力規則の元の値
　　　=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)

すなわち、名前範囲のパソコン（Sheet1!$B$3:$B$100）から１行ずれて
B4からパソコン範囲分のCOUNTAで4そこから-1で３行分で
結果B4:B6の範囲が指定される訳ですね。

私、OFFSET関数根本的に理解していなかったようです
お世話になりありがとうございました。

回答No.1

tom04

こんばんは！

E列のリストの「元の値」の欄はそのままで
F列のリストの「元の値」の欄の数式を
=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)))
としてみてはどうでしょうか？

※　お示しの数式は余計な空白セルをリスト表示させないためだと思います。
Excel2010で試したらOKでしたが、もしExcel2007でダメでしたら
名前定義の範囲をデータ数以上にして、少し空白を表示させる方法になるかと思います。m(_ _)m

お礼

Tom04様

こんにちは、早々にご回答頂きありがとうございました。
結果から申しますと、お示し頂きました分で2007でもできました。
すばらしいです。ありがとうございました。
ただ、知識不足でなぜ？どういうこと？となかなか数式が理解できず
ご返答が遅くなりました。申し訳ございませんでした。

もし、お時間ありましたらで結構なのですが、
お示し頂きました数式

=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)))

INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))ここの部分は
3で３行目
Ｅ2の値をMATCH関数で３行目全体から抽出で2
よってB2基準で1つ下がってまでは理解できます。（合っていますよね？）

その後の
COUNTA (OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))

ここがよく解っていません。
OFFSETの基準がA:Aというのはどういう事なのでしょうか？
Ａ列全体？？セルに=A:A　は0になりますが・・・？？？

結果はおかげをもちましてＯＫですが、もしよろしかったらお暇な時にでも
またご教授下さい。