お弁当の注文をExcelでラクラク集計!

友人からお弁当集計表を作って欲しいと頼まれたので作ってみました。

※SUMPRODUCT関数を使っているので、Excel2007以降対応

< スポンサーリンク >





頼まれた内容は

・お弁当の種類は5種類
・お弁当を注文する人は30人以内
・お弁当はプルダウンで選びたい
・毎日、どのお弁当が何個必要か知りたい
・毎日、お弁当の合計金額が知りたい
・1ヶ月単位でお弁当を頼んだ人に集金したい

複雑ではないようなので、1つのSheetで済ませてみました。

 

行1に日付、A列にNO、B列に氏名を入れます。

bentou-shuukei

 

行1の末尾に「合計金額」と入力します。

bentou-shuukei

  

A列のNOの下にお弁当の種類を入力し、お弁当の種類のセル範囲を選択した状態にして[名前ボックス]に「弁当」と入力して名前をつけます。

bentou-shuukei

 

B列の氏名の下にお弁当の単価を入力し、お弁当の単価のセル範囲を選択した状態にして[名前ボックス]に「単価」と入力して名前をつけます。

bentou-shuukei

 

お弁当をプルダウンで選択できるようにします。

A1~AG31を選択状態にします。

bentou-shuukei

 

リボンの「データ」タブをクリックし、「データの入力規則」>「データの入力規則」の順にクリックします。

bentou-shuukei

 

「入力値の種類」は「リスト」を選択、「元の値」は「=弁当」と入力します。 ※=は半角

bentou-shuukei

 

1日の幕の内の欄に数が計算できるように式を入力します。

=COUNTIF(C$2:C$31,弁当)

※唐揚げから下にコピーするので行は絶対参照「$」にします。

bentou-shuukei

 

唐揚げから焼き肉まで式をコピーします。

bentou-shuukei

 

1日の合計個数の欄にその日の全てのお弁当の数が集計できるように式を入力します。

=SUM(C32:C36)

bentou-shuukei

 

1日の合計金額欄にその日の全てのお弁当の金額が集計できるように式を入力します。

=SUMPRODUCT(C32:C36,単価)

bentou-shuukei

 

1日の幕の内の個数から合計金額までを31日までコピーします。

bentou-shuukei

 

月単位で誰がいくらだったかわかるように、AHの列に式を入力します。

=SUMPRODUCT((COUNTIF(C2:AG2,弁当)*単価))

bentou-shuukei

 

30人目までコピーします。

bentou-shuukei

 

その月の全員の金額がわかるようにAH列の合計金額の行に式を入力します。

=SUM(AH2:AH31)

bentou-shuukei

 

入力しやすいように行毎に背景色を変えます。

bentou-shuukei

 

これで簡単なお弁当集計表ができました。

 

サンプルが必要な方はこちらからダウンロードしてください。
※ 損害やトラブルには責任は負えませんのでご了承ください。

SUMPRODUCT関数って、とっても便利な関数ですね!

 

< スポンサーリンク >※広告先のお問い合わせは広告主様にお願いします