エクセルの質問です。

質問

エクセルの質問です。
(質問用の画像を添付してあります)

献立シート側で[品目]を選択することで、主菜・副菜・汁物シートから
該当の[値段]と[カロリー]の値を読み込みたいのですが、
どのようにデータを取り込んだたら良いのでしょうか？

*質問のための画像を用意するために、例として今回の[献立]Bookを作りました。
紛らわしくてすいません。

回答No.2

kagakusuki

写真が小さくて良く見えないため、もしかするとセルの位置や項目の取り方を見間違えているかも知れませんが、もし、以下の方法で上手く行かなかった場合には、補足欄等でお知らせ下さい。

　最初に確認致しますが、
Bookには、「献立」、「主菜」、「副菜」、「汁物」という名称の4枚のSheetがあり、
全てのSheetのB1セルには「品目」、C1セルには「値段」、D1セルには「カロリー」と入力されていて、
献立SheetのA2セルには「主菜」、A3セルには「副菜」、A4セルには「汁物」、A5セルには「計」と入力されていて、
「主菜」、「副菜」、「汁物」の各SheetのB2以下には料理の名前、C2以下にはその料理の値段、D2以下にはその料理のカロリー、を予め入力して置くと、
献立SheetのB2、B3、B4の各セルを選択した際に、「▼」ボタンが現れて、そのボタンをクリックすると、左隣のセルの内容と同じ名称のSheetに入力した、料理の名前がドロップダウンリストに表示されるので、
料理を選択すると、同じ行のC列にはその料理の値段、D列にはその料理のカロリーが自動的に表示され、
主菜、副菜、汁物に関して、料理を選択すると、自動的に値段とカロリーの合計値が5行目に表示される様にすれば宜しいのですね。

　それでしたら、まず献立SheetのA2以下のセルに、献立Sheet以外のSheetの名称を、一字一句、間違い無く入力して下さい。
　例えばSheet名が「汁物」である処を、献立SheetのA列のセルに「しる物」、「汁　物」、「汁物　」、「【汁物】」、「汁物1」等と入力されていると、正常に動作しません。（但し、セルに入力する順番は、順不同でも構いません）
　次にB2セルを選択した後、次の様な作業をして下さい。
(1)メニューの[データ]ボタンをクリック
　　↓
(2)現れた選択肢の中にある「入力規則」をクリック
　　↓
(3)現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
(4)「入力値の種類」欄をクリックして、現れた選択肢の中にある「リスト」をクリック
　　↓
(5)「元の値」欄をクリックしてから、「元の値」欄に次の数式を入力する

=INDIRECT($A2&"!B2:B"&COUNTIF(INDIRECT($A2&"!B:B"),"<>"))

　　↓
(6)現れた「データの入力規則」ウィンドウの[OK]ボタンをクリック

　次にC2セルに次の数式を入力して下さい。

=IF($B2="","",VLOOKUP($B2,INDIRECT($A2&"!B:D"),2))

　次にD2セルに次の数式を入力して下さい。

=IF($B2="","",VLOOKUP($B2,INDIRECT($A2&"!B:D"),3))

　そして、B2～D2の範囲をコピーして、B3～D4の範囲に貼り付けて下さい。
　次にB5セルに次の数式を入力して下さい。

=SUM(C$2:C4)

　そして、B5セルをコピーして、C5セルに貼り付けて下さい。

　以上で準備完了ですから、後はB列で好きな料理を選択なさってみて下さい。

　尚、１度に出す料理の品数を増やす場合には、2行目～5行目の間の適当な位置に行を挿入した後、B2～D2の範囲をコピーして、挿入した行の同じ列に貼り付けてから（この時に現れるエラー表示は気にしないで下さい）、挿入した行のA列に「主菜」、「副菜」、「汁物」等の献立Sheet以外のSheetの名称を入力して下さい。
　この時、A列のセルに入力する項目名を「副菜2」などとされてしまうと、「副菜2」というSheetは作成していないため、エラーになります。
　但し、「副菜2」や「デザート」といった、新たなSheetを作成して、同じ様式で料理のリストを作成すれば、献立SheetのA列に、「副菜2」や「デザート」という項目を増やす事も出来ます。

回答No.1

tom04

こんばんは！
一つの案です。

「献立Sheet」の品目を手入力するのではなく、リスト表示させるようにしてみてはどうでしょうか？

当方使用のExcel2003の場合ですが・・・
↓の画像で説明します。すべて同じBOOKで別Sheetになっているとします。

まず、「主菜」SheetのB1以降の列を範囲指定　→　挿入　→　名前　→　作成　→　上端行　を選択
（これでB2以降がB1セル（主菜）と名前定義されます。B2以降を範囲指定し、直接名前ボックスに入力してもかまいません。）
同様の操作を最後の「汁物」Sheetまですべて行います。

次に「主菜」SheetのB2～D列のデータが入っている範囲すべてを範囲指定　→　名前ボックスに
仮に　主菜範囲　と名前定義するとします。
これも同様に最後の　「汁物」Sheetまで　副菜範囲　汁物範囲　と名前定義したとします。

「献立」SheetのB列（品目）セルすべてを範囲指定　→　データ　→　入力規則　→　リスト　を選択「元の値」の欄に

=INDIRECT(A2)
と入力してOK
これでB列のすべてのセルに隣のA列にあるSheetのリスト表示ができます。

最後にC2セルに
=IF($B2="","",VLOOKUP($B2,INDIRECT($A2&"範囲"),COLUMN(B1),0))
という数式をいれ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

準備段階で少し手間がかかりますが、範囲指定　→　名前定義　さえきっちり行えば
後は簡単だと思います。

以上、長々と書きましたが
参考になれば幸いです。m(__)m