Excel2007 別シートからの入力について

質問

ドロップダウンで連動して表示する方法について教えてください
別シート
区分　　　　　　　　　野菜　　　　　　　肉　　　　　　　　魚
種類/価格　　　人参　100円　鳥　　100円　　さば　　50円
　　　　　　　　　　トマト 　50円　牛　　150円　　　鯛　 400円
上記のようなデータを
入力シートで
　　区分　　　種類　　価格
をドロップダウン等を使って連動させて価格まで表示させたいのですが、
区分/種類までは、何とか連動できますが、種類に付随した価格が上手く表示できません
いい方法がありましたら、教えてください。
別シートの表示が（横列）いいのかも分かりませんので
別シートの入力方法の変更等が必要であれば、そこらへん迄教えていただくと
大変助かります。

回答No.4

tom04 · ベストアンサー

続けてお邪魔します。

結局最初の「区分」のリスト　→　元の値の欄　の部分で余計に判りにくくしたみたいですね！
No.1の場合、「区分」の種類が少ない場合は
リストの元の値の欄に名前定義したものを入力するのではなく、
カンマで区切って直接「区分」の種類すべてを入力する方法ですので、「区分」の種類が多いと
「区分」も名前定義しておいた方が簡単だと思います。

そこで今までの回答はすべて無視してください。
もう一度画像をアップしてみます。
右側がSheet2で各列を「上端行」で名前定義しておいてください。
この方が簡単だと思います。

そしてSheet1のA列を範囲指定　→　データ　→　入力規則　→　リスト　→　「元の値」の欄に
区分
とだけ入力しOK

B列（B2セル以降）を範囲指定　→　・・・中略・・・　→　「元の値」の欄に
=INDIRECT(A2)
としてOK

最後のC2セルの数式が変わってきます。
画像のSheet2の配置だと
=IF(COUNTBLANK(A2:B2),"",INDEX(Sheet2!A$1:G$100,SUMPRODUCT((Sheet2!A$1:G$100=B2)*ROW(A$1:A$100)),MATCH(A2,Sheet2!$1:$1,0)+1))

という数式になります、これをオートフィルで下へコピーしてみてください。

何とかうまくいけばよいのですが・・・m(_ _)m

お礼

大変長い事、時間を要して
やっと出来ました。
色々教えていただき有難うございました。

回答No.3

tom04

No.1・2です。
お礼欄の質問について・・・

前回のSheet2は↓の画像の配置と同じコトになります。
前回の数式
=IF(COUNTBLANK(A2:B2),"",INDEX(Sheet2!$A$1:$F$100,SUMPRODUCT((Sheet2!$A$1:$F$100=B2)*ROW(Sheet2!A$1:A$100)),MATCH(A2,Sheet2!$1:$1,0)+1))
の説明をします。

Sheet2の商品名に重複はない！という前提の数式です。

最初の
COUNTBLANK(A2:B2),"",
は単にエラー処理でA・B列に入力がない場合はなにも表示させないだけ。
次に一番肝心な
INDEX(Sheet2!$A$1:$F$100,SUMPRODUCT((Sheet2!$A$1:$F$100=B2)*ROW(Sheet2!A$1:A$100)),MATCH(A2,Sheet2!$1:$1,0)+1)
の部分について、分けて説明します。

まずINDEX関数でSheet2のA1～F100セル（行数は種類の一番多い行数でOK、とりあえず100行としました）
を範囲指定し、
何行目を返すか？というところで
SUMPRODUCT((Sheet2!$A$1:$F$100=B2)*ROW(Sheet2!A$1:A$100))
という関数を使用しています。
↓の画像ではB列の「鮭」を検索していますので、Sheet2では5行目に存在します。
それを求めるためにSUMPRODUCT関数を使います。
SUMPRODUCT関数の前半部分
(Sheet2!$A$1:$F$100=B2)
で「鮭」がない行はすべて「FALSE」=0となり、5行目だけが「TRUE」=1となります。
それが画像のG列です。
これに
ROW(Sheet2!A$1:A$100)　←行番号を掛け算しています。これがお礼欄の質問の
＞因みに　ROWの前*は、何の意味ですか？
に当たります。
ROW(Sheet2!A$1:A$100)
を掛け算すると、画像のG列にH列を掛け算することになりますので、
結局Sheet1のB2セルが「鮭」の時はINDEX関数の行は「5」を返します。
※　注意点　※
ROW(Sheet2!A$1:A$100)
の行数は必ずINDEX関数で範囲指定した行数と合わせます。
仮に2行目～100行目を範囲指定したのであれば
ROW(Sheet2!A$1:A$99)
と必ず1行目からIndex関数で範囲指定した行数分！というコトです。
尚、実際は
ROW(A$1:A$100)
としてSheet名を指定する必要はありませんでした。
そしてA列に限定しなくてもB$1:B$100
のように別列でも構いません。

次に列の検索ですがこれは単純にMATCH関数を使用していますので、
Sheet1のA2セル（画像では「魚」）が何列目にあるか？を検索しています。
＞MATCH(A2,Sheet2!$1:$1,0)
で返る値＝列番号は「5」（E列）が返ります。
それに1をプラスしているので、最終的に隣のF列（列番号=6）を返す！というコトです。

結論として、
INDEX関数でSheet2のA1～F100セルの
5行目・6列目を表示！というコトになります。

＞＃REF!で上手くいきません
エラーの原因は色々考えられますが、
一番考えられるのはINDEX関数の範囲指定の行数と行番号を掛け算するところのような気がします。

以上、長々と書きましたが
ご理解いただけたでしょうか？m(_ _)m

補足

最初の回答で質問ですが、
「今更そこ？」と言われるかもしれませんが、
A2セル以降を範囲指定……野菜、肉、魚　と入力とありますが
これはどっちのシートに対してですか？

それと別の方法で
区分（野菜、肉、魚）は、
（Sheet2)A1:F1　入力規制→リスト→区分と設定して
　A2以降F100位までを「選択範囲から名前指定」で上端行と設定して
C2セル=IF(COUNTBRANK省略・・・・・・・と設定しても
価格を読んでくるのでしょうか？
（こうすることで、範囲が簡単に増やせそうなんで　　　　すみません。）

また=ＩＦ（COUNTBRANK 省略・・・・・を実行すると
値の更新　A２sHEET1でファイルを開く？になります

昨日から色々やっていますが、相変わらず＃REFや
別タイトルシートでは＃N/Aしか表示しません。
時間ばかり取らせて申し訳ありません。

回答No.2

tom04

No.1です！

前回の投稿で入力ミスがありました。
＞E2以降を範囲指定　→　魚　と範囲指定
は
＞E2以降を範囲指定　→　魚　と名前定義
の間違いです。

どうも失礼しました。m(_ _)m

回答No.1

tom04

こんばんは！

＞区分/種類までは、何とか連動できますが
とありますので、余計なお世話かもしれませんが・・・

↓の画像右側Sheet（Sheet2）に画像のような感じで表を作成しておきます。
Sheet2のA2以降を範囲指定　→　野菜　と名前定義
C2以降を範囲指定　→　肉　と名前定義
E2以降を範囲指定　→　魚　と範囲指定

A2セル以降を範囲指定　→　データ　→　入力規則　→　リスト　→　元の値の欄に
野菜,肉,魚
とデータ文だけカンマで区切ってデータを入力

B2セルを範囲指定　→　データ　→　入力規則　→　リスト　→　元の値の欄に
=INDIRECT(A2)
としてOK

C2セルに
=IF(COUNTBLANK(A2:B2),"",INDEX(Sheet2!$A$1:$F$100,SUMPRODUCT((Sheet2!$A$1:$F$100=B2)*ROW(Sheet2!A$1:A$100)),MATCH(A2,Sheet2!$1:$1,0)+1))
という数式を入れオートフィルで下へコピー！

これで画像のような感じになります。

※　Excel2010であれば　入力規則の元の値の欄に
直接別Sheetを参照する数式が使えるはずです。m(_ _)m

お礼

早い回答有難うございました。
Ｃ２セルについては、同様にシートを作成して実行しましたが、
＃REF!で上手くいきません。
関数の内容が十分に理解できていなもんで、
どこがＮＧなのかわからない状態です。
家に持ち帰って再度確認します。
因みに　ROWの前*は、何の意味ですか？
素人質問で申し訳ありません。