INDEX
～
ad. 価格範囲を指定して商品を探せます
月ごとの合計を求める方法
Excelで日付が入った売上データなどから、月ごとの合計を求めたい場合があります。
例えば、1月・2月・3月の売上をそれぞれ集計したい場合は、SUMIFS関数やピボットテーブルを使うと便利です。
データの例
次のような売上データがあるとします。
| A | B | C | |
|---|---|---|---|
| 1 | 年月日 | 商品名 | 価格 |
| 2 | 2024/01/05 | 商品A | 1,000 |
| 3 | 2024/01/15 | 商品B | 2,500 |
| 4 | 2024/01/22 | 商品C | 3,000 |
| 5 | 2024/02/02 | 商品A | 1,200 |
| 6 | 2024/02/10 | 商品B | 2,800 |
| 7 | 2024/02/18 | 商品C | 3,500 |
| 8 | 2024/03/03 | 商品A | 1,100 |
| 9 | 2024/03/12 | 商品B | 2,600 |
| 10 | 2024/03/20 | 商品C | 3,200 |
方法1：SUMIFS関数で月ごとの合計を求める
SUMIFS関数を使うと、「指定した月の範囲に入っている日付だけ」を条件にして合計できます。
月ごとの集計では、月初以上かつ翌月の月初より前という条件にすると、月末日を意識せずに集計できます。
集計表の例
| F | G | |
|---|---|---|
| 1 | 月 | 合計 |
| 2 | 2024/1/1 | =SUMIFS(C$2:C$10,A$2:A$10,">="&F2,A$2:A$10,"<"&EDATE(F2,1)) |
| 3 | 2024/2/1 | =SUMIFS(C$2:C$10,A$2:A$10,">="&F3,A$2:A$10,"<"&EDATE(F3,1)) |
| 4 | 2024/3/1 | =SUMIFS(C$2:C$10,A$2:A$10,">="&F4,A$2:A$10,"<"&EDATE(F4,1)) |
数式の意味
=SUMIFS(C$2:C$10,A$2:A$10,">="&F2,A$2:A$10,"<"&EDATE(F2,1))C$2:C$10：合計したい価格の範囲A$2:A$10：日付が入力されている範囲">="&F2：F2の日付、つまり月初日以上"<"&EDATE(F2,1)：翌月の月初日より前
この方法では、1月なら「2024/1/1以上、2024/2/1より前」のデータだけを合計します。
計算結果
| 月 | 合計 |
|---|---|
| 2024年1月 | 6,500 |
| 2024年2月 | 7,500 |
| 2024年3月 | 6,900 |
EOMONTH関数を使う方法
月末日を使って集計したい場合は、EOMONTH関数を使うこともできます。
=SUMIFS(C$2:C$10,A$2:A$10,">="&F2,A$2:A$10,"<="&EOMONTH(F2,0))この数式では、F2の日付が含まれる月の月末日までを条件にして合計します。
ただし、日付に時刻が含まれているデータでは、月末日の一部データが集計されない場合があります。そのため、実務では翌月の月初日より前という条件を使う方法が扱いやすいです。
方法2：ピボットテーブルで月ごとの合計を求める
大量のデータを集計したい場合や、商品別・月別など複数の条件で集計したい場合は、ピボットテーブルが便利です。
手順
- データ範囲を選択します。例：A1:C10
- 「挿入」タブをクリックします。
- 「ピボットテーブル」を選択します。
- 作成先を選択し、ピボットテーブルを作成します。
- フィールド一覧で「年月日」を行エリアに配置します。
- 「価格」を値エリアに配置します。
- 行ラベルの日付を右クリックし、「グループ化」を選択します。
- 「月」を選択して集計します。
これで、月ごとの価格合計が自動的に表示されます。
SUMIFSとピボットテーブルの使い分け
| 方法 | 向いているケース |
|---|---|
SUMIFS関数 |
決まった形式の集計表を作りたい場合 |
| ピボットテーブル | 大量データを素早く集計したい場合や、商品別・月別など柔軟に分析したい場合 |
注意点
- 日付が文字列として入力されていると、正しく集計できない場合があります。
- F列の月は、単なる「1月」という文字ではなく、実際の日付として入力するのがおすすめです。
- 月を入力したセルが「45322」のように表示される場合は、表示形式を日付に変更してください。
- ピボットテーブルで日付をグループ化できない場合は、日付列に空白や文字列が混ざっていないか確認してください。
まとめ
- 月ごとの合計は
SUMIFS関数で求められる。 - 条件は「月初以上、翌月の月初より前」にすると扱いやすい。
EOMONTH関数を使って月末日を条件にする方法もある。- 大量データや柔軟な分析にはピボットテーブルが便利。
- 日付が文字列になっていると正しく集計できないため注意する。
使用した関数について
SUMIFS関数で複数条件を満たすセルの合計値を求める方法と以上やOR条件についてわかりやすく解説
SUMIFS関数についてSUMIFSの概要複数の条件を満たすセルの合計値Excel関数/数学=SUMIFS( 合計対象範囲 , 条件1範囲 , 条件1 , 条件2範囲 , 条件2 , ....) 概要 複数の条件を満たす合計対象範囲の数値の合計を求める 合計対象範囲 には実際に合計する数値の範囲を指定します 条件範囲 ...
programming-cafe.com
EOMONTH関数で指定月後の月末を求める方法とDATEを使って表す方法についてわかりやすく解説
EOMONTH関数についてEOMONTHの概要指定月後の月末を求めるExcel関数/日付=EOMONTH( 開始日 , 月 )概要 指定月後の月末の年月日(シリアル値)を求める 開始日にはスタート地点となる月日 月には、何ヶ月後になるかを入れる 同じ日を求めるときは、EDATEを用いる Oはアルファベットであってゼロで...
programming-cafe.com