2段階リストをOFFSET、MATCH関数で作る

質問

2段階リストを、「名前の定義」つまり「INDIRECT関数」を使わず、OFFSET関数とMATCH関数で作るのが目的ですが、成功しません。

添付した画像の「A列目」に1段目リストを、「B列目」に2段目リストを作ります。
E列からH列は元データです。

たとえば「リスト」表示にせず、単に「りんご」をB2に表示させたい場合は

=OFFSET(D1,1,1,2,)

こうなるわけです。

なので「行数」はこれであっています。

問題の「列数」がMATCH関数になるのでわかりずらいです。

また、もうひとつの条件ですが、リストに空欄を作らない、
大項目、小項目共に（）などの記号が入っても、表示させることができればと思います（INDIRECT関数ではこれができません）。

ミスっている原因と正しい式をわかりやすく教えてください。

回答No.10

miso_kasu

>（空白のあるセル）➡　=OFFSET(■2段階リスト用!$A$1,1,MATCH(B11,■2段階リスト用!$B$1:$I$1,0),7)
>これを（空白のないセル）にするとエラーがでてしまいます。
「空白のあるセル」と「空白のないセル」とはどのような状況ですか？
あなたの脳の中で何を考えているかを推測できない状態なので回答困難です。
「空白のある」と「空白のない」の説明をお願いします。

>「=OFFSET(■2段階リスト用!$A$1,1,MATCH(B11,■2段階リスト用!$B$1:$I$1,0),COUNTA,OFFSET(■2段階リスト用!$A$1,1,MATCH),7)」
>関数は全くの素人なのに、いきなりこんな高レベルの関数をやらざるをえなくなり、ほんとに苦慮しております。
そのような数式を提示した覚えがありませんので添削が困難です。
若しかしてwatabe007さん提示の数式について理解できないということなら私の出番ではありません。

お礼

>関数は全くの素人なのに、いきなりこんな高レベルの関数をやらざるをえなくなり、ほんとに苦慮しております。

あなたの回答についてではなく、個人的な事由です。

回答No.9

miso_kasu

>=OFFSET(2段階リスト!$A$1,1,MATCH(B11,2段階リスト!$B$1:$I$1,0)7)
MATCH関数の第1引数（B11）にはデータの入力規則が設定されているシートの大分類の値が入力されていますよね？（質問の投稿時にはA2となっていますがB11に変更ですか？）
MATCH関数で列番号が算出されて後の第4引数（高さ）の前にカンマ（,）が無いことをお気付きになりましたよね？

> シートの名前を引用符で囲まないと、数式は #NAME? エラー.」となるのですが、"（文字列）"というように点をあらかじめつけなくてもいいわけですよね。
シート名はシングルクォーティションで囲むのでダブルクォーティションではエラーになりませんか？
シート名のシングルクォーティションを省いたときはExcelが自動的に付加するようです。

>アレンジ部分
>　　省略
>これで何か間違っていますか？
どのようにアレンジしても構いませんが自分で表の構成を理解して数式を考えられるようになってください。
質問するときは自分のやりたいことを第三者に理解してもらえるような情報を提供してください。

補足

お待ちしておりました。
はい、回答6で添付された画像のとおりの質問の主旨です。

ただ、例にだされたものを実践にあてはめますと、ミスしやすくなり、大変わかりにくいので、実践中の式に変えますのでそこはご理解ください。

（空白のあるセル）➡　=OFFSET(■2段階リスト用!$A$1,1,MATCH(B11,■2段階リスト用!$B$1:$I$1,0),7)

これは成功しました。

これを（空白のないセル）にするとエラーがでてしまいます。

=OFFSET(■2段階リスト用!$A$1,1,MATCH(B11,■2段階リスト用!$B$1:$I$1,0),COUNTA,OFFSET(■2段階リスト用!$A$1,1,MATCH),7)

関数は全くの素人なのに、いきなりこんな高レベルの関数をやらざるをえなくなり、ほんとに苦慮しております。。よろしくお願いします。

回答No.8

watabe007

>昨日から式をデータのリストに入れようとすると
>「条件　データの入力規則で他のブックへの参照を使用することはできません」

こちらでは問題なく表示されていますが･･･

大項目は、A2:A10を選択後 [データの入力規則]⇒[リスト]⇒元の値
=■2段階リスト用!E$1:H$1
小項目は、B2:B10を選択後 [データの入力規則]⇒[リスト]⇒元の値
=OFFSET(■2段階リスト用!D$1,1,MATCH(A2,■2段階リスト用!E$1:H$1,0),COUNTA(OFFSET(■2段階リスト用!D$1,1,MATCH(A2,■2段階リスト用!E$1:H$1,0),9,1)),1)

お礼

他の方の式ですが、
9の前に「,」が抜けておりました(^^ゞ

ご回答ありがとうございました！

回答No.7

miso_kasu

回答No.6にタイプミスがありました。
MATCH関数はOFFSET関数の列方向の隔たりを算出しますのでSheet2!$E$1:でなければ
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↓
MATCH関数はOFFSET関数の列方向の隔たりを算出しますのでSheet2!$E$1:$H$1でなければ

お礼

9の前に「,」が抜けておりました(^^ゞ

ご回答ありがとうございました！

補足

すいません。幅について未読でした。

回答No.6

miso_kasu

回答No.1の代わりです。
Sheet1がデータの入力規則を設定したシートです。
Sheet2はデータの入力規則で参照するリストの見本です。
先ず、大分類の入力セルとしてA2を選択し、リストの指定をSheet!$E$1:$H$1に指定します。
次に、小分類の品名を入力セルとしてB2を選択します。
リストの指定は添付画像で示しますので参考にしてください。
入力するOFFSET関数の式は次のようになります。
=OFFSET(Sheet2!$D$1,1,MATCH(A2,Sheet2!$E$1:$H$1,0),9)
OFFSET関数の第1引数は基準となるSheet2!$D$1です。（絶対番地で指定すること）
MATCH関数はOFFSET関数の列方向の隔たりを算出しますのでSheet2!$E$1:でなければB3セル以下からの参照のとき大分類が見つからなくなります。
OFFSET関数の第5引数（幅）は省略することができ、省略したときは1が指定されたものとなります。
A2セルとB2セルを選択して下へ必要数オートフィルでコピーできます。
当方ではExcel 2010で動作を確認しました。

お礼

補足の続きです。
エラーの原因では

「たとえば、同じブック内の "四半期データ" というシートのセル D3 から値を返すには、='四半期データ'!D3 という数式を使用します。 シートの名前を引用符で囲まないと、数式は #NAME? エラー.」

となるのですが、"（文字列）"というように点をあらかじめつけなくてもいいわけですよね。
というか、Excelがあとからつけてくれるのに･･･。
「＝」の前にも何もつけてませんし･･･。

補足

今度はこちらの式で試してみました。

回答の式では成功しましたが、

=OFFSET(2段階リスト!$A$1,1,MATCH(B11,2段階リスト!$B$1:$I$1,0)7)

実践用としてアレンジしたのですが、以下のエラーとなります。

「この数式には問題があります。

数式を入力していない場合は次の点に注意します。
最初の文字に統合（＝）やマイナス記号（－）を使用している場合は、数式として認識されます。」

アレンジ部分

小項目➡「2段階リスト」シート
小項目の基準➡A1
小項目の検索範囲➡同シートB列からI列
検索する高さ➡7
検索する幅➡？

これで何か間違っていますか？
またなぜこの式では「幅」がないのでしょう？

回答No.5

miso_kasu

>絶対値で設定すると、大項目をコピーをしたときに、リストがでなくなります。
大分類をデータの入力規則のリストからプルダウンで選択し、小分類の品名をデータの入力規則から2段目のリストから選択できるようにしたいのですよね？
作業内容が異なるのでしたら回答No.1を無視してください。

>これはどういう意味でしょうか？
前述のデータの入力規則について回答していますのでプルダウンリストを作成することについて述べています。
あなたの質問と異なる回答ならば無視してください。

補足

その前に･･･

昨日から式をデータのリストに入れようとすると

「条件　データの入力規則で他のブックへの参照を使用することはできません」

と何度やってもこのエラーがでます。

その前は「この式使えません」とでましたが、いきなりこのエラーに変わりました。

もちろん他のブックなんて開いてないですし、シートは同じブック内にあります（泣）。

回答No.4

watabe007

見落していました、A2:B10の全てでプルダウンリストを使うのですね
大項目は、A2:A10を選択後 [データの入力規則]⇒[リスト]⇒元の値
=■2段階リスト用!E$1:H$1
小項目は、B2:B10を選択後 [データの入力規則]⇒[リスト]⇒元の値
=OFFSET(■2段階リスト用!D$1,1,MATCH(A2,■2段階リスト用!E$1:H$1,0),COUNTA(OFFSET(■2段階リスト用!D$1,1,MATCH(A2,■2段階リスト用!E$1:H$1,0),9,1)),1)

補足

その前に･･･

昨日から式をデータのリストに入れようとすると

「条件　データの入力規則で他のブックへの参照を使用することはできません」

と何度やってもこのエラーがでます。

その前は「この式使えません」とでましたが、いきなりこのエラーに変わりました。

もちろん他のブックなんて開いてないですし、シートは同じブック内にあります（泣）。

回答No.3

watabe007

>=OFFSET(D1,1,MATCH(A2,E1:H1,0),COUNTA(OFFSET(D1,1,MATCH(A2,E1:H1,0),9,1)),1)
>のほうを選びました。
なら
大項目
=■2段階リスト用!E1:H1
小項目
=OFFSET(■2段階リスト用!D1,1,MATCH(A2,■2段階リスト用!E1:H1,0),COUNTA(OFFSET(■2段階リスト用!D1,1,MATCH(A2,■2段階リスト用!E1:H1,0),9,1)),1)

お礼

他のブックも開いてませんし、これは同一ブック内でのシートです…。

補足

他のブックへの参照はできませんとエラーがでるのですが‥‥

回答No.2

watabe007

>リストに空欄を作らない、=OFFSET(D1,1,MATCH(A2,E1:H1,0),COUNTA(OFFSET(D1,1,MATCH(A2,E1:H1,0),9,1)),1)
または =OFFSET(D1,1,MATCH(A2,E1:H1,0),SUMPRODUCT((OFFSET(D1,1,MATCH(A2,E1:H1,0),9,1)<>"")*1),1)

お礼

すいません、エラーとなった式は、ご回答のものではなく、こちらで作っているものです。
こちらのほうで直してください。よろしくお願いいたします。

補足

絶対値で設定すると、大項目をコピーをしたときに、リストがでなくなりますので、こちらの回答があったのは幸いでした。

=OFFSET(D1,1,MATCH(A2,E1:H1,0),COUNTA(OFFSET(D1,1,MATCH(A2,E1:H1,0),9,1)),1)

のほうを選びました。

最後に、小項目が別シートにある式を教えてください。

このように試しましたが、エラーとなってしまいます。

=OFFSET(■2段階リスト用!B1,1,MATCH(A9,■2段階リスト用!B1:I1,0),COUNTA,OFFSET(■2段階リスト用!B1,1,MATCH(A9,■2段階リスト用!B1:I1,0),9,1)),1)

回答No.1

miso_kasu

>2段階リストを、「名前の定義」つまり「INDIRECT関数」を使わず、OFFSET関数とMATCH関数で作るのが目的ですが、成功しません。
提示の数式は相対番地で指定していますので正しい返り値を期待できません。
また、MATCH関数の検索範囲に誤りがあります。
=OFFSET(D1,1,MATCH(A2,E2:H19,0),9,1)
D1→$D$1　OFFSET関数の参照セルは絶対番地参照にしないとB2セルからの参照で正しくてもB3セルから参照するとD2セルからの隔たりとして解釈されてしまいます。
また、MATCH関数の検査範囲は$E$1:$H$1にしないと大分類を検索できません。
B2セルからの参照のとき、正しい数式は次の通りです。
=OFFSET($D$1,1,MATCH(A2,$E$1:$H$1,0),9,1)

>また、もうひとつの条件ですが、リストに空欄を作らない。
リストを作成する人の問題なので此処での回答はできません。

お礼

絶対値で設定すると、大項目をコピーをしたときに、リストがでなくなります。

補足

＞リストを作成する人の問題なので此処での回答はできません。

これはどういう意味でしょうか？