excelで質問いたします。

質問

入力規則のリストを使って店舗のメニューの売上表を作っています。しかしメニューの種類が多いためリストから選ぶのが面倒です。
そこでリストを大まかに（ソフトドリンク・カクテル・丼ものなど）系列で分けて、例えばソフトドリンクを選ぶとそのリストからコーラ・オレンジ・トマトジュースなどの小分類が選べるようにしたいと思っています。
入力規則のリストだけでは不十分でしょうか？
できましたら小分類で選んだ商品の単価を一緒に表示させて、売り上げた個数を入力すると売上合計が出るようにまでしたいと思います。
別の関数などを使用して出来るのであれば関数の名前を教えてください。
何卒よろしくお願いいたします！

回答No.3

kagakusuki · ベストアンサー

御質問に対する直接の回答からは少々逸脱してしまいますが、
元データとして縦3列のリストに左列にメニュー、2番目の列に単価、右端の列に大分類を入力しておくと、
別表に大分類毎に仕分けた料理のリストが自動的に作成されて、その自動的に仕分けられたリストをドロップダウンリストの設定に利用する様にしておき、
又別の入力規則のドロップダウンリストによって売上表に大分類を選択入力すると、
売上表の「注文された料理欄」を選択した際に現れる入力規則のドロップダウンリストには、選択した大分類に含まれている料理名だけが表示される、
という方法は如何でしょうか？
　こうしますと、新メニューが追加された等により、元データのリストに料理名を入力する際に、大分類毎に仕分ける表の何処に料理名を入力するのかを、一々考えながら作業をする必要は無く、
縦3列の表に単純に料理名と単価を入力して行き、後から、リストに入力済みの料理は、どの大分類に属するのかを入力するだけで良くなります。
　勿論、元データのリストの右端の列に大分類を入力する際にも、ドロップダウンリストを使用する事が出来ます。
　その設定方法は以下の通りです。

　今仮に、元データのリストがSheet2のA列にあり、その1行目には「料理名」、「単価」、「大分類」といった項目名が入力されていて、実際のデータは2行目以下に入力するものとします。
　又、大分類毎に仕分ける表はSheet2のE列～G列ににあり、その1行目には「ソフトドリンク」、「カクテル」、「丼もの」といった大分類名が入力されていて、その下の2行目には各大分類に属する料理の種類数が自動表示され、実際のデータは3行目以下に自動表示されるものとします。
　又、売上表はSheet1に作成し、そのA列は日付の入力欄、B列は大分類の入力欄（ドロップダウンリスト入力）、C列は料理名の入力欄（ドロップダウンリスト入力）で、C列に料理名が入力されると、D列に単価が自動表示されるものとします。

　まず、Sheet2のE1セルからG1セルにかけて、「ソフトドリンク」、「カクテル」、「丼もの」といった大分類名を入力して下さい。（鉤括弧は入力無用）
　次に、Sheet2のE2セルに次の関数を入力して下さい。

=COUNTIF(E3:INDEX(E:E,ROWS(E:E)),"*?")

　次に、Sheet2のE3セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,E$1),"",INDEX($A:$A,MATCH(E$1,INDEX($C:$C,MATCH(IF(ROWS($2:2)=1,$A$1,E1),$A:$A,0)+1):INDEX($C:$C,ROWS($A:$A)),0)+MATCH(IF(ROWS($2:2)=1,$A$1,E1),$A:$A,0)))

　次に、Sheet2のE2～E3の範囲をコピーして、Sheet2のF2～G3の範囲に貼り付けて下さい。
　次に、Sheet2のE3～G3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
　次に、以下の操作を行って、Sheet2のC2セルに入力規則のドロップダウンリストを設定して下さい。

【Excel2007よりも前のバージョンの場合】
Sheet2のC2セルを選択
　　↓
「メニュー」バーの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に次の数式を入力

=$E$1:$G$1

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

【ExcelのバージョンがExcel2007以降のバージョンの場合】
Sheet2のC2セルを選択
　　↓
Excelウィンドウの上の方にある[データ]タブをクリック
　　↓
現れた「データツール」グループの中にある[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に次の数式を入力

=$E$1:$G$1

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

　次に、以下の操作を行って、Sheet2のB2セルに書式を設定して下さい。

B2セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式を設定]をクリック
　　↓
現れた「セルの書式を設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[通貨]をクリック
　　↓
現れた「記号」欄をクリック
　　↓
現れた各種の通貨記号の中から、[\]を選択してクリック
　　↓
「セルの書式を設定」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet2のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet1のD2セルに対して、Sheet2のB2セルと同じ書式を設定して下さい。
　次に、Sheet1のB2セルに対して、Sheet2のC2セルに行ったのと同様の操作を行い、「データの入力規則」ダイアログボックスの[設定]タブの「元の値」欄に次の数式を入力して下さい。

=INDIRECT("Sheet2!E1:G1")

　次に、「データの入力規則」ダイアログボックスの[OK]ボタンをクリックして下さい。
　次に、Sheet1のC2セルに対して、Sheet2のC2セルに行ったのと同様の操作を行い、「データの入力規則」ダイアログボックスの[設定]タブの「元の値」欄に次の数式を入力して下さい。

=INDIRECT("Sheet2!R3:R"&ROW(INDIRECT("Sheet2!E1"))+1+HLOOKUP(INDEX($B:$B,ROW()),INDIRECT("Sheet2!E1:G2"),2,FALSE)&"C"&MATCH(INDEX($B:$B,ROW()),INDIRECT("Sheet2!E1:G1"),0)+COLUMN(INDIRECT("Sheet2!E1"))-1,FALSE)

　次に、「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリックして下さい。
　次に、「データの入力規則」ダイアログボックスの[エラーメッセージ]タブの中にある[無効なデータが入力されたらエラーメッセージを表示する]と記されている箇所の左にあるチェックボックスをクリックして、チェックを外して下さい。
　次に、「データの入力規則」ダイアログボックスの[OK]ボタンをクリックして下さい。
　次に、Sheet1のD2セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet2!$A:$A,INDEX($C:$C,ROW())),VLOOKUP(INDEX($C:$C,ROW()),Sheet2!$A:$C,2,FALSE),IF(INDEX($C:$C,ROW())="","","(なし)"))

　次に、Sheet1のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet2のA2以下にメニューの料理名を、B2以下にその単価を、それぞれ入力し、ドロップダウンリストを利用するなどしてC2以下に大分類を入力して下さい。

　これで準備は完了で、後はドロップダウンリストを使用するなどして、Sheet1の売上表のB列に大分類を、C列に料理名を入力しますと、C列に単価が自動的に表示されます。

お礼

ご丁寧にありがとうございます。
初心者にも分かりやすく教えていただきとても助かりました！

回答No.2

koko88okok

> できましたら小分類で選んだ商品の単価を一緒に表示させて、売り上げた個数を入力すると売上合計が出るようにまでしたいと思います。

まず、ドロップダウンリストで大分類と小分類を２段階で表示し、商品の単価は小分類の商品名からVlookup関数による数式で表示させる方法は如何でしょう？
勿論、エラーを表示させないために「IF関数」を使う必要があります。

「エクセル ドロップダウンリスト」
http://www.kenzo30.com/ex_kisopoint/onepoint_sonota3.htm

「VLOOKUP関数を使ってみよう」
http://www.yoshikawa.co.jp/ybs/skilup/ybs0308.htm

お礼

ドロップダウンリストが2段階で表示できるやり方はとても勉強になりました！
大変にありがとうございました。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

いちばん基本的なやり方と思われる方法で。

D3:F7セルのような表を作成する
D3:F7セルを選択して[Ctrl]+[Shift]+[F3]名前の作成 [レ]上端行
これで3つの名前が定義される

A2セルの入力規則のリスト =$D$3:$F$3

B2セルの入力規則のリスト =INDIRECT(A2)

「入力規則 INDIRECT」で調べるといろいろ出てきます

お礼

図解入りで教えていただきありがとうございました！
とても参考になりました！