エクセル2007での表の作り方

質問

エクセル2007で次のような表を作りたいのですがやり方を教えて下さい。

Ａ列（日付） B列（氏名Aさん）　C列(氏名Bさん）　D列(氏名Cさん）金額合計

10/20
10/21
10/22

Ａ定食300円
Ｂ定食400円
Ｃ定食500円

トッピングA100円
トッピングB200円
トッピングC300円

Ａさん、Ｂさん、Ｃさんの10/20のセルは食べた物を選択し合計金額を金額合計の列に出るようにしたいのです。

例　10/20
Ａさん　Ａ定食＋トッピングＡ　合計400円
Ｂさん　Ｂ定食＋トッピングＢ　合計600円
Ｃさん　Ｃ定食＋トッピングＣ　合計800円

10月20日合計　1800円

金額合計欄に1800円が出るようにする。

このような関数をエクセル2007で作る方法を教えて下さい。

宜しくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！

日付毎に個人の合計と全体の合計金額を表示したい！というコトですね。

一例ですが、↓のように表の作り方を変えてみます。
1人に付き3列使用するようにしています。
元データはSheet1でSheet2に定食とトッピングの表を↓の画像のように作成しておきます。

Sheet1のD3セルに
=IF(COUNTA(B3:C3),SUM(IFERROR(VLOOKUP(B3,Sheet2!$A:$B,2,0),0),IFERROR(VLOOKUP(C3,Sheet2!$C:$D,2,0),0)),"")
という数式を入れます。

そして、D3セルの四辺のどこでも良いのでマウスポインタを移動させると
上下左右の小さな矢印になるところがあります（フィルハンドルではありません）
そこでCtrlキーを押しながらG3セルまでドラッグ
同じようにG3セルをJ3セルまでドラッグ
これでD3・G3・J3セルに同じ数式が入ります。
各々のセルをフィルハンドルでずぃ～～～！っと下へコピーしておきます。

最後にK3セルに
=IF(COUNT(D3:J3),SUM(D3,G3,J3),"")
という数式を入れフィルハンドルで下へコピー！
これで画像のような感じになります。

※　「定食」や「トッピング」はいちいち手入力するより
入力規則の「リスト」設定した方が良いかもしれませんね。m(_ _)m

お礼

早速のご回答ありがとうございます。
入力規則のリスト設定方法も教えていただけないでしょうか。
すみません。

回答No.4

tom04

No.1・3です。

＞対象のセル（例えばＣ３」を選択すると「=Sheet2!$A$2:$A$4」と出てしまいます。
そうですかぁ～～！
前回書いたようにExcel2007での検証ができませんでしたので、ごめんなさいね。
それでは他の方法です。
Sheet2の各範囲を「名前定義」してそれをリスト表示させる方法にしてみます。

まずSheet2のA2～A4セルを範囲指定　→　名前ボックス（画面左上数式バー左側のセル番地が表示されているところ）に
現在表示されているセル番地（おそらくA2と表示されているはずです）を消去し、仮に
定食　と入力しEnter
これでSheet2のA2～A4セル範囲が　定食　と「名前定義」されました。
同様にSheet2のC2～C4セルを範囲指定　→　名前ボックスの「C2」を消去　→　トッピング　と入力　→　Enter
これでSheet2のC2～C4セル範囲が　トッピング　と「名前定義」されましたので
これらを利用します。

リスト設定の方法は前回同様ですが、Sheet1のB列のリストの「元の値」の欄に
=定食
としてOK

同様にC列の範囲指定　→　リストの元の値の欄に
=トッピング
としてOK

他の列の操作方法は前回同様です。
結局、リストの元の値の欄の数式だけを上記のように名前定義して変更してみてください。

こんどは大丈夫のはずです。
ちゃんと検証できずに投稿してごめんなさいね。m(_ _)m

お礼

ありがとうございました。
ちゃんとできました。
大変お手数をおかけし、申し訳ございませんでした。

回答No.3

tom04

No.1です。

＞入力規則のリスト設定方法も教えていただけないでしょうか。
というコトですので・・・
Excel2010の場合は直接別Sheetをドラッグで範囲指定できますが、
手元にないので確認できませんが、2007の場合はおそらく数式を直接手入力する必要があったと思います。
前回通り↓の画像通りSheet2に金額表が作ってあるとします。

（1）Ctrlキーを押しながらSheet1の「定食」の列（B・E・H列）のリスト表示させたい行だけ
セルを範囲指定
（2）リボン（メニューバー）　→　データ　→　データの入力規則　→　「入力値の種類」の右側▼で「リスト」を選択
（3）「元の値」の欄に手入力で
=Sheet2!$A$2:$A$4
と入力しOK（$マークは忘れないでください）
データ範囲は実際のデータ数だけ指定してやります。
（Excel2010以降の場合は数式を入れずに直接別Sheetが範囲指定できます）
これで「定食」の列はSheet2のA2～A4セルがリスト表示されます。

「トッピング」の列（C・F・I列）も同様に設定します。
途中までの操作は同じですが、「元の値」の欄の数式が
=Sheet2!$C$2:$C$4
のように、Sheet2のC2～C4セルを指定してやります。

これで「定食」「トッピング」の列は手入力する必要はなく、そのリストから選ぶことができます。

※　行範囲が多すぎて3列まとめて範囲指定し難い場合は
まずB列とC列だけを一旦入力規則のリスト設定します。
そしてB・C列を範囲指定　→　範囲指定したセルの両サイドのどちらかにマウスポインタを移動させると
上下左右の小さな矢印のマークになりますので、Ctrlキーを押しながら2列まとめて
E3セルへドラッグ・そのままH3セルへドラッグ
これで各列をリスト設定したことになります。

※　数式は前回そのままで大丈夫です。m(_ _)m

お礼

ありがとうございます。
何度もすみませんが、対象のセル（例えばＣ３」を選択すると「=Sheet2!$A$2:$A$4」と出てしまいます。
これをちゃんと表示させるにはどうすればよいでしょうか？
なんどもすみません。

回答No.2

bunjii

貼付画像のような表を作成すると良いでしょう。
各自のメニュー選択はSheet2で入力します。
定食（定）の種類とトッピング（上）の選択はデータの入力規則でリストから選べばA、B、C以外の文字列でも簡単に入力できます。
各自の日毎の金額は次の数式で算出できます。
B2=IFERROR(VLOOKUP(TRIM(INDEX(Sheet2!$A:$G,MATCH($A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0))),Sheet2!$I$3:$K$5,2)+VLOOKUP(TRIM(INDEX(Sheet2!$A:$G,MATCH($A2,Sheet2!$A:$A,0),MATCH(Sheet1!B$1,Sheet2!$1:$1,0)+1)),Sheet2!$I$3:$K$5,3),"")
B2セルを右と下へオートフィルでコピーすれば希望通りになるでしょう。
日毎の合計は次の式です。
E2=IF(COUNT(B2:D2),SUM(B2:D2),"")
E2を下へオートフィルでコピーします。

お礼

ありがとうございました。
大変助かりました。