エクセル2003でお弁当の集計

質問

５０種類お弁当（単価は同じではない）を、１５人の人が２０日間それぞれに注文するものとして、次のように集計できるワークシートを作りたいです。

・毎日、たとえば該当するお弁当の種類を書いた行にあるセルにチェックを入れる操作だけで、日々のお弁当屋さんに支払う金額の集計ができて、かつ、
・初日からチェックを入れた日までの各人の合計支払い金額が勝手に集計できている、

というようにしたいです。
日々の合計位ならまだ単純に思えるのですが・・。どうやったらいいでしょうか？

回答No.5

tom04

No.2です。
補足の件で・・・

とりあえず名前定義の方は上手くいったようですね？

エラーの件ですが、おそらく名前定義した範囲の行数とB列の行数が一致していないのではないかと思います。
前回の画像でA2～A10セルの範囲が名前定義した「種類」の範囲にしていますので、
質問の
＞列方向の指定範囲はどこまででしょうか？
はA2～A10とします。
※　この範囲指定は実際のデータに合わせてください。

それに伴って数式もB列の範囲が変わってきます。
仮にA2～A50セルの範囲を名前定義したのであれば、数式も
=IF(COUNTA(B2:E2),SUMPRODUCT((B2:E2=種類)*(Sheet2!$B$2:$B$50)),"")
のようになります。

ただ、本来の希望としてはある日付の「弁当の種類」・「個数」・「その日の金額」を把握したい！
というのが本筋のようですので、余計なお世話かもしれませんが、
↓の画像のようにSheet3のA1セルに日付の数値を入力するとその日のデータを表示する方法はどうでしょうか？
もちろんSheet1の空いているところでも構いませんが、今回はSheet3に作成してみました。
名前定義の部分は前回同様とします。
（前回は品名のプルダウン表示にしていましたが、当然数字でも構いません）

画像で説明させていただくと、
Sheet3に作業用の列を2列設けています。
作業列1のE2セルに
=INDEX(OFFSET(Sheet1!$B$1:$E$1,MATCH($A$1,Sheet1!$A$2:$A$32,0),,1),,ROW(A1))
作業列2のF2セルに
=IF(AND(E2<>0,COUNTIF($E$2:E2,E2)=1),MATCH(E2,種類,0),"")
という数式を入れ両列ともSheet1の人数分だけ下へオートフィルでコピーしておきます。

A4セルに
=IF(COUNT(F:F)<ROW(A1),"",INDEX(種類,SMALL($F$2:$F$5,ROW(A1))))

B4セルは
=IF(A4="","",COUNTIF(OFFSET(Sheet1!$B$1:$E$1,MATCH($A$1,Sheet1!$A$2:$A$32,0),,1),A4))

C4セルに
=IF(COUNTBLANK(A4:B4),"",B4*VLOOKUP(A4,Sheet2!A:B,2,0))

という数式を入れ、最後にA4～C4セルを範囲指定　→　C4セルのフィルハンドルで人数分だけ下へコピー！
これで画像のような感じになります。

A1セルに表示したい日付を入力すればその日のデータが表示されるはずです。

※　Sheet1のデータは1日～31日まで（2行目～32行目）
※　Sheet1の人数はB～E列の4人
としていますので、数式の範囲指定部分は適宜変更してみてください。

以上、長々と書きましたが参考になりますかね？m(_ _)m

回答No.4

ka28mi

No.１，３で回答した者です。

＞Ｄ３からＨ３にはどんな式が入っていますか？

個人のお名前と、お弁当のお名前が交差する場所ですよね？
ここには、式は入りません。
No.３の回答の下から１１行目、「それぞれのお名前と、お弁当名が交差する場所に、注文個数を入れます」に該当しますので、直接、申し込み個数を入力します。質問者さまの最初の想定「チェックを入れる」代わりに、申し込み個数を入力するわけです。

蛇足ながら、Ｄ３からＨ３となっているのは、私が、その数で仮に作ってみたからで、実際の人数や余白行によっては、当然変えてください。ここは、お弁当１種類ごとに、人のお名前が入っている列分の申し込み個数を合計するのが目的です。

＞何とか理解できそうですが、まだ先に進めません。
ネット上で、文字だけで、ご説明するのは、なかなか難しいですし、読んでおられても理解しづらいですよね。
もっと説明がうまければ良かったのですが。申し訳ありません。

ただ、読んで理解しようとなさるよりも、遠回りに感じられると思いますが、一度、順番に作って見ることを、おすすめします。
それで、「ここに、こんな変なエラーが出ちゃったよ」、「ここが、こういう値になるはずなのに、ならないよ」と言っていただく方が、説明もしやすいです。

ハードルが高いかもしれませんが、一種類作れば自信も付きますから、頑張ってくださいね。

回答No.3

ka28mi

えすNo.１で回答した者です。

＞書くお弁当の番号も入るようにしたい。
単純な方法としては、１日あたりの列を３つに増やします。
お弁当、お弁当番号、単価ですね。

ＩＮＤＥＸ関数は、（導出元になる表、行、列）
ＭＡＴＣＨ関数は、（照合元、照合先、同一か近似値かの区分「ＦＡＬＳＥ」は同一）です。
詳しくは、ネットで検索して頂くと、もっと詳しい説明が出てきますから、検索してみてください。

お弁当番号はＡ列に入れていますから、Ａ列の情報を引いてくるには、
=INDEX(Sheet1!$A:$C,MATCH(Sheet2!D2,Sheet1!$B:$B,FALSE),1)
になります。
単価は３列目に入っていたので、最後に「３」としたものが、今度は「１」になるわけです。

ただ、「お弁当を番号で注文したい」なら、毎日の値段の合計と、毎月の個人の合計額以外に、「どのお弁当番号がいくつか」も必要ではないでしょうか？
そうするとチェック式になりそうですね。ざっくり作って見ると５００ｋｂでした。
文字が多少大きくなっても１０００にはいかないでしょうから、作業に手間取るほどの大きさにはなりませんけれど。

形としては、１日あたりの発注シートと、月ごとの合計シートを作る方法です。

まず、シートの基本形を作ります。
Ａ列にお弁当番号、Ｂ列にお弁当名、Ｃ列に金額を入れます。
３行目くらいから始めるといいですよ。
お弁当情報を足したくなった時の用心に、２つ３つ列を空けて、Ｇ列くらいから２行目に人の名前を入れていきます。
最後の２列は合計個数、合計金額としますが、これも人が増えた時の用心に、１０列くらい空けた方がいいですよ。
合計個数には、「=SUM(D3:H3)」、合計金額には「=C3*人の最終列3」として、全部の行にコピーすると、その日のお弁当あたりの個数と金額が出ますね。
ちなみに、お弁当の種類が増えた時の用心に、行も１０行ほど空けた方がいいと思います。
最終行の最終列に、合計を入れると、１日あたりの合計額が出ます。
Ｄ３くらいでウィンドウ枠を固定しておくと、人やお弁当の欄を間違いにくくなるので、おすすめです。
また、名前の行でフィルタリングを設定しておくと、注文するお弁当の種類をチェックしやすくなります。

これを基本シートとして、日付分増やします。
まずは、シートの名前を「１」としてシートコピーをしていきます。最初にできるコピーは「１（２）」となるでしょうから、次は２つとも選んでコピー、その次は４つとも選んでコピーとしていくと、「１（３２）」まで簡単にできると思います。
なぜ、３２まで作るかと言うと、暦通りの３１日分＋合計シートです。
２０日というのは営業日だろうと思いますが、２１日の時もありますよね？発注する時に「今日は何営業日目だっけ？」となる時もあるでしょう。ややこしくならないように、日付とシートを同じにしておくと、迷わないで済みます。
シートの名前を１～３１と合計に変えます。

次に合計シートに合計のための関数を設定します。
下のＨＰを見ていただくと分かりやすいです。
http://kokoro.kir.jp/excel/3d.html
お弁当と名前が交差する欄に、「=SUM('1:31'!D3)」と直接入力でも大丈夫ですが。
ただ、ここで出したいのは個数ではなく金額なので、この後ろに、単価と掛ける式を足します。「=SUM('1:31'!D3)*$C3」とするわけです。
そのまま、全部に式をコピーしてしまい、最終行に列ごとの合計式を入れます。これで月当たりの個人合計額が分かります。

実際の使い方ですが、その日の日付のシートを開きます。
それぞれのお名前と、お弁当名が交差する場所に、注文個数を入れます。
１日の注文個数と合計額は、各シートで分かります。
月当たりの、個人からの集計額は、合計シートで分かります。何をどれだけ注文したかも、このシートで分かります。

使い方の注意ですが、私は６０×３０で作ってみました。
データが小さいですから、実際よりコンパクトになっていると思いますが、許容範囲だと思います。ただ、これ以上、大きくなるようだと、マクロとかを使う方が、実際に使う時にストレスが少ないかなと思いますね。
そして、大事な事は、月の途中でお弁当や人を訂正しない事です。
集計は、位置だけで計算していますから、内容を訂正してしまうと正しくなくなります。
一部の訂正は追加で対応し、全面的に変わる場合はファイルを新しいものにしてしまうことを、お勧めします。もともと、１カ月１ファイルの想定です。

これで、質問者さまの要望どおりになるでしょうか？

補足

すみません、基本シート作りのところで、個数を求めるＤ3からＨ３にはどんな式が入っていますか？月ごとの合計シートは何とか理解できそうですが、まだ先に進めません。

回答No.2

tom04

こんにちは！
色々方法はあると思いますが・・・
一案です。

↓の画像のようにSheet2に弁当表を作成しておきます。
やり方だけの説明になりますので、データは少なくしています。

Sheet2のA2～最終行までを範囲指定　→　名前ボックス（画面左上のセル番地が表示されるところ）に
仮に　種類　と入力しEnter！
これでSheet2のA2以降の範囲指定セルが「種類」と名前定義されました。
（A1セル以降を範囲指定　→　メニュー　→　挿入　→　名前　→　作成　→　「上端行」を選択でも同じです。

次にSheet1のB2～E32（最終日）までを範囲指定　→　メニュー　→　データ　→　入力規則　
→　リスト　→　「元の値」の欄に
=種類
としてOK
これでSheet1の範囲指定したセルすべてにSheet2のA列データがリスト表示できます。
この中から弁当種類が選択できます。

次にSheet1のF2セルに
=IF(COUNTA(B2:E2),SUMPRODUCT((B2:E2=種類)*(Sheet2!$B$2:$B$10)),"")
としてずぃ～～～！っと下へコピー！

合計欄のB33セルに
=SUMPRODUCT((COUNTIF(B2:B32,種類)*Sheet2!$B$2:$B$10))
として列方向へオートフィルでコピーすると
画像のような感じになります。

※　日計・合計が交差するF33セルだけは別にSUM関数で行か列の合計をします。

あくまで一案ですので、他にも良い方法はたくさんあると思います。
参考になりますかね？m(_ _)m

補足

御回答ありがとうございます。仰る通りにやってみているつもりなのですが、まだうまくいきません。sheet1のB2からE2までのセルにはリストから選んだお弁当の種類がはいりましたが、

（18行目）Sheet1のF2セルに=IF(COUNTA(B2:E2),SUMPRODUCT((B2:E2=種類)*(Sheet2!$B$2:$B$10)),"")

のところでF2セルに「＃Ｎ/Ａ」のエラーが出ます。
確認ですが、（6行目）「Sheet2のA2～最終までを範囲指定」の際、列方向の指定範囲はどこまででしょうか？ここで少し迷いました。

回答No.1

ka28mi

エクセルの典型例ですので、色々な方法があると思います。

私でしたら、あまりサイズを大きくしたくない、内容に変更があった時のメンテナンスを少なくしたい、詳しい知識がない人でも処理できるようにしたので、下のような手順にします。

(1)お弁当表の作成
・シート１に、Ａ列「番号」、Ｂ列「お弁当名」、Ｃ列「単価」を入れます。
・Ｄ列以降は、販売停止になったり、新商品発売開始などの情報を、随時入れたくなれば入れます。

番号は別になくてもよいのですが、個人的にデータを扱う時は番号を入れておく方が、便利になります。

(2)集計シートの作成
・Ａ列には、シート１のＢ列の情報を「=Sheet1!B3」のように、数式で持ってきます。

・Ｃ列３行目から人名を入力します。
・２行目にはＤ列から１行おきに、１日、２日という日付データを入力します。
　これで縦に人名、横に日付けの表が出来上がります。
　日付データを１列おきにするのは、お弁当の種類と金額を設定するためです。

・Ｄ列以降のお弁当の種類を入れるセルに、
　データの入力規則で「リスト」を使い、プルダウンでお弁当を選べるようにします。
　リスト設定を＄Ａ：＄Ａにしておけば、お弁当に追加があっても選べます。
　ご存知と思いますが、１つのセルに設定をして複写すれば大丈夫です。

・Ｅ列以降の金額を入れる欄に、シート１から単価を引いて来ます。
　下の関数になります。
　「=INDEX(Sheet1!$A:$C,MATCH(Sheet2!D2,Sheet1!$B:$B,FALSE),3)」

・縦横それぞれで合計を計算します。
　縦の合計が１日単位、横の合計が一カ月の人単位です。

質問者さまのイメージされているチェック式だと、１日あたり１シートになるので大きいんですよね。
集計は、そちらの方が単純ですが。

補足

適切で詳しいご回答ありがとうございます。追加となって申し訳ございません。お弁当の注文の際に、お弁当の番号で注文したいため、集計表にはお弁当の品名だけではなくsheet1でつけた各お弁当の番号も入るようにしたいです。落ち着いて考えれば良いのでしょうがまだINDEX関数等うまく使いこなせないため、お教え下さい。