Excelの自動集計に役立つ、関数の活用法(お弁当注文の自動集計)

当教室では、お仕事でExcelを使っていて「ここがわからない!」「これをもっとこうしたい!」といった、ものすごく具体的なご質問もどしどし受け付けています。

コースごとのテキストはあり、それにそったレッスンが基本なのですが、それだけでは、「習ったはずなのに、実際やるとどうやっていいか分からない」となることが多いですからね。パソコンの使い方を身に着けるには、「実際に試す」「小さな失敗をたくさん繰り返す」のが、一番の早道だと思います。

さて、そんなわけで、先日ちょっと大きめのご質問をいただきました。

「会社で毎日注文する、お弁当の集計を自動化したい」

というご質問というか、ご希望というか・・・慣れないExcelで懸命に毎日仕事をしている、この方の切実な「夢」ですね・・・

これから、必要なことを一つずつ、レッスンさせていただくのですが、まずはサンプルがあったほうがいいだろう、ということで、当教室で完成見本を作ってみました。

まず画像一枚目は、お弁当の注文を入力するシートです。手入力は、おもにこのシートのみに対して行います。

お弁当注文の集計表サンプルをつくってみました

続いて次のシートは、お弁当屋さんにFAX注文するための、FAX注文票を出力するシートです。右上の日付を変更すると、該当の日の注文が一覧になって自動出力されます。

毎日の注文を自動集計して、FAX注文票を生成します

さらに別シートに、メニューと価格の一覧表が入っています。これはVLOOKUP関数で他のシートから参照しています。

お弁当の価格表が別シートになっています

最後に、一か月分の注文金額を自動集計するシートです。別に入力してあるメニュー表と照合して、金額を自動集計します。一人一人の注文金額と、会社全体の注文金額が出ますので、お弁当屋さんの請求書とも照合できます。

さらに毎月の社員別注文金額、全社合計の金額も自動集計します

いろんな作業をできるだけ自動化するために、いろんな工夫をしているのですが、全部いっぺんにはご紹介しきれません。

そこで、今回の記事では、「検索と集計」の部分に絞って、具体的にExcelの関数をどんなふうに使っているのか、ご紹介してみたいと思います。

あるメニューの注文数を数える、COUNTIF関数

まずは、「どのメニューが何個注文されたのか」を集計するために「COUNTIF関数」を使っています。

まずは、その日にあるメニューがいくつ注文されたのかを集計するためにCOUNTIF関数を使用します。

このセルに入っている数式は

=COUNTIF(OFFSET(注文入力マスタ!$C$5:$C$34,0,$H$1-1),FAX送付表!B17)

となっています。とても複雑ですが、少し簡略化してご説明しますと、

=COUNTIF( [その日の注文入力欄全体] , “とんかつ”)

となっています。

注文日の「とんかつ」の個数を数える—COUNTIF(範囲,検索条件)

=COUNTIF(範囲,検索条件)　という関数が、このセルの数式のメインです。「ある特定の日」の欄にある、「とんかつ」という文字の個数を数えています。

COUNTIF関数で「とんかつ」を数えています。

日付に応じて検索範囲を変動させる–OFFSET関数

さらに、この「数える範囲」を、日付に応じて変動させるのに使っているのが、OFFSET関数です。

OFFSET ( [もとになる参照範囲] , [行方向の変動] , [列方向の変動])

ちょっと分かりにくいですが、図にするとこんなイメージになります。

OFFSET関数を使って、注文日付によって集計範囲をずらすイメージ

実際に使っている数式で見ると、

OFFSET(注文入力マスタ!$C$5:$C$34,0,$H$1-1)

の赤字の部分が「もとの範囲」です。そして、行方向には動かさないので「0」、列方向には「$H$1-1」つまり「H1セルにある日付から1を引いたもの」を指定しています。

これをCOUNTIF関数の「検索範囲」に指定しているので、「選択した日付の注文の集計」ができるようになっています。

このほかのさまざまな集計技法

このほか、今回のサンプルでは、次のような集計技法を使っています。

3F、2F、1Fと、それぞれの階での注文数を集計する、COUNTIFS関数

FAX注文票には、全体合計だけでなく、各階ごとの注文数内訳も出力しています。これは、さきほどのように「とんかつ」をカウントする、というだけではなく、「3階のとんかつだけをカウントする」というように複数の条件を同時に満たすセルだけをカウントしています。

このためには、COUNTIF、ではなく、COUNTIFS関数を使っています。

お休みの日を自動でグレー色にする「条件つき書式」

月ごとにかわるお休みの日を、自動でグレーにするために「条件付き書式」を使っています。

メニュー表から自動で金額を拾ってくる、VLOOKUP関数

別のシートにメニュー表が入っていて、そこから自動的に各メニューの金額を拾ってくるために、VLOOKUP関数を使っています。

未入力のため計算エラーになる日を空白表示にする、IFERROR関数

最後の金額計算のシートで、まだ注文を入力していない日の計算がエラーになり、表示がきたなくなるので、エラーになったら空白を返すようにIFERROR関数を使っています。

Excelを使って、自分の本当にやりたい作業を自動化してみましょう!

ここで使っているさまざまな関数は、当教室のコースでいいますと、「Excel基礎」と「Excel応用」にまたがる内容になります。

ひとつひとつの関数をレッスンしているうちは、どう役立つのか分かりにくいものです。でも、必要な関数を組み合わせて実際のお仕事に使ってみると、びっくりするような自動化ができてしまうものです。

みんながやるような業務は、ネット上にできあがったExcelのシートが掲載されている場合もありますが、本当に自分が毎日やっている業務の自動化は、結局は自分でやるほかない場合が多いですよね。

今回は、「やればこんなことができるんだ!!」という、「Excelの可能性」を知っていただくために、サンプルを作成してみました。「できる」ということが分かれば、次は「実際にやってみる」番です!

ぜひ、チャレンジしてみてください!