担当者別に月別集計を行う方法

2023.05.08 |

こんにちは。すごい改善のAitoです。

今回は担当者別に月別集計を行う方法について解説していきます。

集計というと、大まかに二種類存在して、関数による集計とピボットテーブルによる集計です。関数による集計は一度式を作るとデータを入れ替えるだけで集計が自動的に完了するので自動化と相性が良いです。ピボットテーブルは簡単に集計出来て便利なのですが、何度も同じ作業を繰り返す際には不向きです。今回は今後の自動化も想定して、関数による集計を解説していきます。

まずは完成形をご覧ください。担当者の売上を月ごとに集計していくサンプルになります。

集計を始める前にまずはデータを用意しましょう。今回使う項目は、

・担当者

・日付

・売上

に補助としてNo.と月を加えたものになります。こちらはすでに用意したものがございますので、下リンクよりダウンロードしてお使いください。

集計_配布用

開くとこのようなデータが入っているはずです。

ではこれをどうやって集計していこうかと思考をめぐらせます。必要なのは担当者と月で、担当者はすでに項目としてあるし重複をなくすのかなとか、月は日付から取ってくればいいから……などなど。

思考がまとまったら手順を整理します。

1 月を抜き出す

2 担当者の重複をなくした一覧を作成する

3 集計する

3についてさらに深堀します。必要な関数は何でしょうか。これは弊社のセミナーでも強く言っているSUMIF(S)の出番ですね。今回は便利なのでSUMIFSを使うことにしましょう。便利なものは使ってかないとですね(笑)

1を片づけてしまいましょう。MONTH関数で簡単に実現できますね。E2セルに

=MONTH(C2)

と入力して下までコピーします。

2はUNIQUE関数でサクッと終わらせましょう。G3セルに

=UNIQUE(OFFSET($B:$B,1,0,COUNTA($A:$A)-1,1))

と入力します。OFFSET関数が入っているのはヘッダ(No.とか担当者とか)の部分を集計に含めないようにするためです。UNIQUE関数はスピル機能があるので自動的に入力されます。

これもコピーして値貼り付けを行うことで文字列にしてしまいましょう。

3の集計に入っていきます。

最初に月を準備します。H2に1、I2に2を入力して二つ選択した状態で12までドラッグして連番を作成します。

H3に次の数式を入力します。

=SUMIFS($D:$D,$B:$B,$G3,$E:$E,H$2)

$が大量についてややこしいですね。ちょっと解説します。SUMIFSは式を選択すると表示されるのですが、引数はこのようになっています。

H2に入力したものをコピーして表全体に適用できるようにしたいので、コピーの際に参照範囲がずれないように設定する必要があります。

合計対象範囲：D列(売上)

条件範囲1:B列(担当者)

条件範囲2:E列(月)

ここは表のどこでも変わらないのでずっと動いて欲しくないのです。ということで列全体参照をして$マークを付けて固定します。

次に条件1と条件2なのですが、こちらは条件1が担当者で縦に動きます。G3セルをクリックしてF4キーを3回押します。条件2は月で横に動きます。H2セルをクリックしてF4キーを2回押します。この絶対参照のコツは、「横2回縦3回」と覚えてしまうことです。いちいちどっちに$がつくのだったかなと悩まなくて済みます。