友人からお弁当集計表を作って欲しいと頼まれたので作ってみました。
※SUMPRODUCT関数を使っているので、Excel2007以降対応
< スポンサーリンク >
頼まれた内容は
・お弁当の種類は５種類
・お弁当を注文する人は３０人以内
・お弁当はプルダウンで選びたい
・毎日、どのお弁当が何個必要か知りたい
・毎日、お弁当の合計金額が知りたい
・１ヶ月単位でお弁当を頼んだ人に集金したい
複雑ではないようなので、１つのSheetで済ませてみました。
行１に日付、A列にNO、B列に氏名を入れます。
行１の末尾に「合計金額」と入力します。
A列のNOの下にお弁当の種類を入力し、お弁当の種類のセル範囲を選択した状態にして［名前ボックス］に「弁当」と入力して名前をつけます。
Ｂ列の氏名の下にお弁当の単価を入力し、お弁当の単価のセル範囲を選択した状態にして［名前ボックス］に「単価」と入力して名前をつけます。
お弁当をプルダウンで選択できるようにします。
Ａ１～ＡＧ３１を選択状態にします。
リボンの「データ」タブをクリックし、「データの入力規則」＞「データの入力規則」の順にクリックします。
「入力値の種類」は「リスト」を選択、「元の値」は「=弁当」と入力します。 ※=は半角
１日の幕の内の欄に数が計算できるように式を入力します。
=COUNTIF(C$2:C$31,弁当)
※唐揚げから下にコピーするので行は絶対参照「$」にします。
唐揚げから焼き肉まで式をコピーします。
１日の合計個数の欄にその日の全てのお弁当の数が集計できるように式を入力します。
=SUM(C32:C36)
１日の合計金額欄にその日の全てのお弁当の金額が集計できるように式を入力します。
=SUMPRODUCT(C32:C36,単価)
１日の幕の内の個数から合計金額までを３１日までコピーします。
月単位で誰がいくらだったかわかるように、AHの列に式を入力します。
=SUMPRODUCT((COUNTIF(C2:AG2,弁当)*単価))
３０人目までコピーします。
その月の全員の金額がわかるようにAH列の合計金額の行に式を入力します。
=SUM(AH2:AH31)
入力しやすいように行毎に背景色を変えます。
これで簡単なお弁当集計表ができました。
サンプルが必要な方はこちらからダウンロードしてください。
※ 損害やトラブルには責任は負えませんのでご了承ください。
SUMPRODUCT関数って、とっても便利な関数ですね！
こんな記事も書いています