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

お弁当注文の集計表サンプル Excel(エクセル)
Excel(エクセル)今日のパソコン教室

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

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

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

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

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

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

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

お弁当注文の集計表サンプル

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

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

FAX注文票を生成します

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

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

お弁当の価格表です

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

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

合計金額も集計

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

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

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

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

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

注文数の集計

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

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

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

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

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

となっています。

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

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

COUNTIF関数で「とんかつ」を数える

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

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

さらに、この「数える範囲」を、日付に応じて変動させるのに使っているのが、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の可能性」を知っていただくために、サンプルを作成してみました。「できる」ということが分かれば、次は「実際にやってみる」番です!

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

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、本年よりオンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

スポンサーリンク
キュリオステーション志木店運営をフォローする
志木駅前のパソコン教室・キュリオステーション志木店のブログ

コメント

  1. こんにちは、コメントありがとうございます。
    共有ドキュメントを使って、自動集計をされるのですね。

    関数を無視して、BやCが入力してしまうと、関数は消えてしまいますので、違う方法が必要です。

    B,CがMSアカウントを持ち、OneDriveを使用して、アプリ版(通常版)のExcelで編集する場合は、次の方法が使えるのですが、Excel Onlineで試したところ、できないようです。

    Excel Onlineではできない方法
    ・共有するブックに、「集計用」「B」「C」と、三枚のシートを用意します。
    ・「集計用」の各セルに、「=SUM(B専用:C専用!B2)」などと入力します。
    ・B専用、C専用のシートは、B、Cそれぞれの入力したデータだけが入っている状態にします
    ・B、Cが入力して保存・アップロード→「集計用」シートには自動的に合計が入る

    ところが、試してみるとExcel Onlineでは、シート間の合計が入力できない仕様のようです。

    Excel Onlineでもできる方法
    ・同一シート内に、集計用、B用、C用に3つの表を並べて作成し、
    ・集計用の表には、「=(Bの該当セル)+(Cの該当セル)」といった単純な足し算で合計する数式だけ入れておきます。
    ・Bは同一シート内のB用の表に、Cは同一シート内のC用の表に入力します。

    いずれの方法でも、B,Cそれぞれ、お互いのデータと、集計結果は見ることができてしまいます。それでは都合が悪い場合は、Excel Onlineでご希望の作業を行うことはできないと思います。

    AだけOneDriveを持っている場合、次の方法もできます。

    AだけOneDriveを使う方法
    ・A用、B用、C用と、3つのExcelファイルを用意し、B、Cにそれぞれ該当のファイルを共有します
    ・B、Cが編集すると、AのOneDrive内の該当ファイルに反映されます。
    ・AだけはこのファイルをOneDriveからExcelで開き、
     各セルには、B、Cのブックを参照する数式をいれておきます。
     例 =SUM([B用.xlsx]Sheet1!B2,[C用.xlsx]Sheet1!B2))
    ・実際の計算は、A用、B用、C用のすべてのExcelブックを、AがOneDriveからすべて開いた際に実行されます。
      ※試したところ、すべてAが開かないと再計算されませんでした。
    この方法だと、全体を見ることができるのはAだけになります。

    また、BとCが変更したことがどのように通知されるか、という点ですが、
    BやCが編集中は、AのExcel Onlineの右上に「ゲストが編集中」と通知が出ます。
    これは、BやCがExcelを閉じてしばらく(数分程度)の間、表示されます。
    通知をクリックすると、B、Cそれぞれがどのセルを編集しているか(どのセルがアクティブになっているか)は見ることができます。

    編集完了の確認は、この通知が消えたことをもって確認するしかないと思います。

    私どもはふだんあまりExcel Onlineを使いませんが、
    試してみると、意外とまだまだ不便ですね。

  2. しろう より:

    Google検索で知りました。よろしくお願いいたします。

    >作業を自動化してみましょう!

    こういう場合はどうでしょうか。もし、よければアドバイスください。
    https://gyazo.com/16d11d120d4f33d79507fd5a67120a12

    Aが作成した4月までの表をBとCを招待し、5月の数字を記入してもらいます。
    Excel Onlineの使用が前提です。BとCはマイクロソフトのアカウントを持っていなくて、AはBとCのメールアドレスに送信します。
    Bは2、Cは3を記入すれば、Aの表には自動的に5が反映されるというシュミレーションです。

    1.AはMayの箇所に関数式を記入しなければなりませんが、それはどのような式でしょうか。
    2.BとCが受領したExcelの表ですが、それには関数の式が記入されていて、BとCはそれを無視してBは2、Cは3を記入すれば、Aの表には自動的に5が反映されるのでしょうか。

    3.AはBとCが数字を記入すれば、どのような感じで反映されるのでしょうか。Aは受信メールで知るのでしょうか

タイトルとURLをコピーしました