ExcelのGROUPBY関数で月別集計する方法
― 社員番号別の集計も解説
日付と価格が並んだ明細から「4月はいくら、5月はいくら」と月ごとの合計を出し、さらに社員番号ごとの内訳まで作る方法を解説します。ポイントは、日付をTEXTで年月へ変換し、複数の基準をHSTACKで横に並べてGROUPBYへ渡すことです。
目次(15項目)
Microsoft公式の関数一覧では、GROUPBYは「Microsoft 365」と表記されています。一方、同じ一覧でHSTACK・VSTACKには「2024」の表記があります。Excelの製品名だけで判断せず、数式バーへ
=GROUPBY(と入力して候補に表示されるかを確認してください。表示される環境では本記事の式を使えます。
SUMIFS関数で月別・社員番号別に集計する方法を使えば、同じサンプルデータから同じ合計結果を作れます。
1. はじめに
この記事では、次の2段階で集計します。
- 日付を年月に変換し、月ごとに価格を合計する
- 年月と社員番号を使い、月別・社員番号別に価格を合計する
GROUPBYの第1引数に1列を指定すれば、1つの項目で集計されます。
GROUPBYの第1引数に複数列を指定すれば、その複数列の組み合わせごとに集計されます。
2. サンプルデータ
A1:C7に、次のような売上データがあるものとします。1行目は見出し、2行目以降が集計対象です。
| 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 |
実際のデータが7行より多い場合も考え、数式では2行目から99行目までを指定します。
3. GROUPBY関数の基本
GROUPBY関数は、指定した項目ごとにデータをまとめ、合計や平均などを計算できる関数です。ピボットテーブルのような集計表を、数式だけで作れます。
今回の3つの引数は、次の意味です。
| 引数 | 今回指定する内容 |
|---|---|
| グループ化する項目 | 年月、または年月+社員番号 |
| 集計する値 | 価格 |
| 集計方法 | 合計(SUM) |
4. まず1列で月別集計する方法
最初は、社員番号を考えず、A列の日付を基準に月別の価格合計を出します。空いているセルへ次の式を入力してください。
この式は、A列の日付を「202604」のような年月へ変換し、同じ年月のB列の価格を合計します。入力した1セルから、年月と合計の2列が自動で展開されます。
5. 1列集計の数式の意味
数式を4つの部分に分けると、何をしているかが見えやすくなります。
1 TEXT(A2:A99,"yyyymm")
これは、A列の日付を202604や202605のような年月へ変換する部分です。日付の「日」を取り除いて同じ年月表記にそろえるため、4月の日付を1つのグループとして扱えます。
| 元の日付 | 変換後 |
|---|---|
| 2026/4/25 | 202604 |
| 2026/4/26 | 202604 |
| 2026/5/1 | 202605 |
TEXTの結果は文字列ですが、同じ年月をまとめるための見出しとして問題なく使えます。
2 B2:B99
これは合計する価格の範囲です。GROUPBYの第2引数には、「何を集計したいか」を範囲で指定します。
3 SUM
これは集計方法を「合計」にする指定です。ここではSUM(B2:B99)と計算してから渡すのではなく、関数名のSUMだけを指定します。
4 最後の0,0
1つ目の0は元データに見出しがないものとして、結果にも自動見出しを付けない設定です。2つ目の0は総計を表示しない設定です。見出しは、あとでVSTACKを使ってわかりやすく追加します。
6. 1列集計の結果イメージ
| 年月 | 合計 |
|---|---|
| 202604 | 4500 |
| 202605 | 6500 |
4月は1000+2000+1500で4500、5月は3000+2500+1000で6500です。この集計では社員番号を基準に含めていないため、社員番号に関係なく、同じ月の価格がすべて合計されます。
7. 見出し付きで表示する方法
結果の上に「年月」「合計」という見出しを付けるには、VSTACK関数を使います。
VSTACKは、配列や範囲を縦に重ねる関数です。ここでは、1行目に{"年月","合計"}を置き、その下へGROUPBYの結果を重ねています。
8. 2列で月別・社員番号別に集計する方法
次は、年月だけでなく社員番号も集計の基準にします。
この式は、年月と社員番号の2つの組み合わせごとに、B列の価格を合計します。違いは、GROUPBYの第1引数へHSTACKで作った2列を渡している点です。
9. なぜ社員番号もグループ化できるのか
ここが2列集計の大切なポイントです。GROUPBYの第1引数には、1列だけでなく複数列を渡せます。
HSTACKは、複数の列を横に並べる関数です。今回の場合は、年月の列と社員番号の列を横に並べて、GROUPBYに渡しています。
Excel内部では、次のような2列の表を一時的に作っているイメージです。
| 年月 | 社員番号 |
|---|---|
| 202604 | 101 |
| 202604 | 101 |
| 202604 | 102 |
| 202605 | 101 |
| 202605 | 102 |
| 202605 | 101 |
この2列をGROUPBYへ渡すと、Excelは「年月と社員番号の組み合わせごとにグループ化する」と判断します。
- 202604・101
- 202604・102
- 202605・101
- 202605・102
同じ4月でも、社員番号101と102は組み合わせが違うため、別グループとして集計されます。
第1引数が1列なら、その1項目ごとに集計。
第1引数が複数列なら、その複数項目の組み合わせごとに集計。
10. 2列集計の結果イメージ
| 年月 | 社員番号 | 合計 |
|---|---|---|
| 202604 | 101 | 3000 |
| 202604 | 102 | 1500 |
| 202605 | 101 | 4000 |
| 202605 | 102 | 2500 |
4月の社員番号101は1000+2000で3000、社員番号102は1500です。このように、同じ月のデータでも社員番号101と102が別々に集計されます。
11. 1列集計と2列集計の違い
| 集計方法 | 数式 | 集計内容 |
|---|---|---|
| 1列集計 | =GROUPBY(TEXT(A2:A99,"yyyymm"),B2:B99,SUM,0,0) | 年月ごとに合計 |
| 2列集計 | =GROUPBY(HSTACK(TEXT(A2:A99,"yyyymm"),C2:C99),B2:B99,SUM,0,0) | 年月と社員番号ごとに合計 |
- 1列集計では、同じ月の価格がすべて合計されます。
- 2列集計では、同じ月でも社員番号が違えば別グループとして集計されます。
見出し付きのおすすめ式
この式では、集計結果の上に「年月」「社員番号」「合計」という3つの見出しを追加します。別の表やグラフの元データとして使うときも、何の列かがすぐわかります。
12. 空白行がある場合の対策
A2:A99に空白行が含まれる場合、空白の日付までTEXTで変換され、不要な年月が集計結果に出ることがあります。日付が入っている行だけを対象にするには、LETとFILTERを組み合わせます。
条件,A2:A99<>""で「A列の日付が空白ではない行」を条件として名前を付けています。その条件を3つのFILTERで共通して使い、日付・社員番号・価格を同じ行だけに絞ってから集計します。
| 部分 | 役割 |
|---|---|
LET(条件,...) | 空白ではないという条件に名前を付ける |
FILTER(A2:A99,条件) | 日付がある行だけを取り出す |
FILTER(C2:C99,条件) | 同じ行の社員番号だけを取り出す |
FILTER(B2:B99,条件) | 同じ行の価格だけを取り出す |
13. テーブル化して使う方法
元データはExcelの「テーブル」にしておくと便利です。A1:C7などデータ範囲内のセルを選び、次のショートカットでテーブルへ変換できます。
テーブル名を「売上表」に変更した場合、セル番地の代わりに列名で数式を書けます。
月別集計
売上表の日付を年月へ変換し、価格を月ごとに合計します。
月別・社員番号別集計
売上表の年月と社員番号をHSTACKで横に並べ、その組み合わせごとに価格を合計します。
見出し付き
テーブル化しておくと、下の行へデータを追加したときに対象範囲が自動で広がります。毎月データが増える一覧では、A2:A99のような固定範囲より管理しやすくなります。
14. よくある間違い
次の式は、GROUPBYの引数の位置と内容が間違っています。
"yyyymm"は集計する値の範囲ではなく、ただの文字列です。- 第2引数には、合計したい価格の範囲
B2:B99を指定します。 - 第3引数には、
SUM(B2:B99)ではなく集計方法としてSUMを指定します。
正しくは、日付をTEXTで年月へ変換した配列を第1引数へ、価格の範囲を第2引数へ、SUMを第3引数へ渡します。
また、#SPILL!が出る場合は、結果が展開されるセル範囲に値や結合セルがないか確認してください。
15. まとめ
月別だけで集計する場合は、次の式を使います。
社員番号も含めて集計する場合は、次の式を使います。
見出し付きで使うなら、次の式がおすすめです。
GROUPBY関数は、ピボットテーブルのような集計を数式だけで作れる便利な関数です。
1列だけでなく複数列をグループ化の条件にできるため、月別集計だけでなく、社員番号別、商品別、店舗別などの集計にも応用できます。