SUMIFS関数で月別集計する方法
― 社員番号別の集計も解説
GROUPBY関数が使えない環境でも、SUMIFS関数なら月別集計を作れます。月の開始日以上・翌月の開始日未満という2つの日付条件を指定し、社員番号の条件も追加する方法を順番に解説します。
目次(13項目)
1. GROUPBYがない場合はSUMIFSで集計できる
GROUPBYは、元データからグループを自動で見つけて集計表を展開できる便利な関数です。しかし、使えないExcelではSUMIFSを使い、集計したい年月や社員番号を先に集計表へ並べることで同じ合計を求められます。
集計表の項目は自分で用意する必要がありますが、Excel 2016・2019・2021・2024・Microsoft 365で使いやすい、安定した方法です。
2. サンプルデータ
A1:C7に、次の売上明細があるものとします。
| A列:日付 | B列:価格 | C列:社員番号 |
|---|---|---|
| 2026/4/25 | 1000 | 101 |
| 2026/4/26 | 2000 | 101 |
| 2026/4/27 | 1500 | 102 |
| 2026/5/1 | 3000 | 101 |
| 2026/5/2 | 2500 | 102 |
| 2026/5/3 | 1000 | 101 |
このデータから、まず月ごとの合計を作り、その後で月別・社員番号別の合計を作ります。
3. SUMIFS関数の基本
SUMIFSは、すべての条件に合う行だけを合計する関数です。今回の「2026年4月」を表すには、A列の日付へ次の2条件を指定します。
- 4月1日以上
- 5月1日未満
この2つを同時に満たす日付は、4月中の日付だけです。
4. 集計表へ月初日を入力する
E1に「年月」、F1に「合計」と入力します。E2には文字列の202604ではなく、実際の日付として2026/4/1を入力してください。
| E列:年月 | F列:合計 |
|---|---|
| 2026/4/1 | 数式を入力 |
| 2026/5/1 | 下へコピー |
表示を「202604」や「2026年4月」にしたい場合は、セルの表示形式だけをyyyymmまたはyyyy"年"m"月"へ変更します。中身を日付のまま保つことが大切です。
翌月を自動で作る
EDATEは、指定した日付から何か月後・何か月前の日付を返す関数です。ここではE2の1か月後、つまり2026/5/1を作ります。
5. まず月別だけで合計する
F2へ次の式を入力し、下へコピーします。
この式は、A列の日付がE2の月初日以上で、E2の翌月初日より前にある行の価格を合計します。
数式を分解すると
| 数式の部分 | 意味 |
|---|---|
$B$2:$B$99 | 合計する価格の範囲 |
$A$2:$A$99 | 日付を調べる範囲 |
">="&E2 | E2の月初日以上 |
"<"&EDATE(E2,1) | E2の翌月初日より前 |
日付に時刻が含まれていても、その月の最終日を漏らさず集計できるためです。2月やうるう年の日数を考える必要もありません。
6. 月別集計の結果
| 年月 | 合計 |
|---|---|
| 202604 | 4500 |
| 202605 | 6500 |
4月は1000+2000+1500で4500、5月は3000+2500+1000で6500です。ここでは社員番号を条件に入れていないため、同じ月の価格がすべて合計されます。
7. 次に月別・社員番号別で合計する
H列からJ列へ、集計したい年月と社員番号の組み合わせを先に並べます。H列の日付は各月の1日を入力し、表示形式をyyyymmにします。
| H列:年月 | I列:社員番号 | J列:合計 |
|---|---|---|
| 202604 | 101 | 数式 |
| 202604 | 102 | 下へコピー |
| 202605 | 101 | 下へコピー |
| 202605 | 102 | 下へコピー |
J2へ次の式を入力します。
月別集計の式へ、$C$2:$C$99,I2という社員番号の条件を追加しています。日付がH2の月内であり、さらに社員番号がI2と同じ行だけが合計されます。
8. 月別・社員番号別集計の結果
| 年月 | 社員番号 | 合計 |
|---|---|---|
| 202604 | 101 | 3000 |
| 202604 | 102 | 1500 |
| 202605 | 101 | 4000 |
| 202605 | 102 | 2500 |
同じ4月でも、社員番号101と102は別の条件で集計されます。GROUPBYのように組み合わせは自動生成されませんが、必要な組み合わせを並べれば同じ結果を作れます。
9. 実務ではクロス集計表も便利
年月を縦、社員番号を横に置くと、少ない入力で見やすい集計表を作れます。
| 年月 | 101 | 102 |
|---|---|---|
| 202604 | 3000 | 1500 |
| 202605 | 4000 | 2500 |
E2に月初日、F1とG1に社員番号がある場合、F2へ次の式を入力して右と下へコピーします。
$E2:E列を固定し、下へコピーすると月だけが変わるF$1:1行目を固定し、右へコピーすると社員番号だけが変わる
10. テーブル化すると元データの追加に強くなる
元データを選択し、Ctrl + Tでテーブル化して、テーブル名を「売上表」にすると次の式になります。
新しい明細をテーブルの下へ追加すると、日付・価格・社員番号の参照範囲が自動で広がります。
11. よくある間違い
| 間違い | 起きること | 対策 |
|---|---|---|
| 年月セルへ文字列の202604を入力 | 日付条件と正しく比較できない | 2026/4/1を入力し、表示形式をyyyymmにする |
| 月末日を毎月手入力 | 2月・30日・31日の違いでミスしやすい | EDATEで翌月初日を求める |
| 比較演算子とセルを分けて書かない | 条件が正しく伝わらない | ">="&E2のように&でつなぐ |
| 合計範囲と条件範囲の行数が違う | #VALUE!や誤集計の原因になる | すべて2行目から99行目のようにそろえる |
12. GROUPBYとSUMIFSの違い
| 比較 | GROUPBY | SUMIFS |
|---|---|---|
| 集計項目の作成 | 自動 | 年月・社員番号を先に用意 |
| 結果の展開 | 1つの式からスピル | 数式を下や右へコピー |
| 複数項目の集計 | HSTACKで列を渡す | 条件範囲と条件を追加 |
| 対応環境 | Microsoft公式ではMicrosoft 365 | Excel 2016以降で広く利用可能 |
GROUPBYが使えるなら、一覧を自動生成できるGROUPBYが便利です。使えない場合や、集計表の行・列を決まった並びにしたい場合はSUMIFSが安定しています。
13. まとめ
月別だけで合計する式は次のとおりです。
月別・社員番号別で合計する式は次のとおりです。
SUMIFSでは、年月と社員番号の一覧を自分で用意する必要があります。その代わり、旧バージョンでも使いやすく、集計表の並びを固定しやすいのが強みです。