Excelで行間にある小計の値を除外した合計値を求める手順を紹介します。
現象の確認
下図の表を作成します。
日付 | 取引先 | 取引額 |
2025/4/8 | ぺんぎん商事 | 548,000 |
2025/4/17 | しろくまファクトリー | 1,234,000 |
2025/4/24 | らくだ金属 | 35,000 |
4月合計 | | |
2025/5/13 | かるがもスタジオ | 480,000 |
2025/5/22 | ぺんぎん商事 | 642,000 |
2025/5/30 | くじら海運 | 2,580,000 |
5月合計 | | |
合計 | | |
D6セルに次の数式を入力します。
D3~D5セルの合計値が表示されます。
同様に、D10セルに次の数式を入力します。
合計値が表示されます。
D11セルに次の数式を入力します。
合計値が表示されます。D3セルからD10セルまでのすべての値の合計値がD11セルに表示されます。
実際の利用では、D6セルの[4月合計]とD10セルの[5月合計]の小計値は含めない合計値をD11セルに表示したいです。
方針
小計の値を除外して合計を求める場合には、SUBTOTAL 関数を利用すると簡単に実現できます。
書式
または
SUBTOTAL([集計方法], [集計範囲1], [集計範囲2], ... [集計範囲n])
[集計方法]には以下の値が設定できます。
非表示の行も集計対象とする | 非表示の行は集計から除外する | 処理 | 実行する関数 |
1 | 101 | 平均値 | AVERAGE |
2 | 102 | 数値を含むセルの個数 | COUNT |
3 | 103 | 空白ではないセルの個数 | COUNTA |
4 | 104 | 最大値 | MAX |
5 | 105 | 最小値 | MIN |
6 | 106 | 乗算 | PRODUCT |
7 | 107 | 標本の標準偏差 | STDEV |
8 | 108 | 母集団の標準偏差 | STDEVP |
9 | 109 | 合計 | SUM |
10 | 110 | 母集団の分散 | VAR |
11 | 111 | 母集団全体の分散 | VARP |
SUBTOTAL関数では、入れ子の集計値は除外されます。
手順
先の表のD6セルに次の数式を入力します。
数式を確定します。D3~D5セルの合計値が表示されます。先ほどと同じ値です。
D10セルに次の数式を入力します。
数式を確定します。D7~D9セルの合計値が表示されます。こちらも先ほどと同じ値です。
D11セルに次の数式を入力します。
数式を確定します。D3~D10セルの合計値が表示されますが、D6, D10セルの小計の値は合計には含まれないことが確認できます。
D3,D4,D5,D7,D8,D9セルの合計とD11セルの値が一致しており、小計の数値は合計には含まれません。
このページのキーワード
- 行間にある小計の値を除外した合計値を取得する - SUBTOTAL関数の利用
著者
iPentecの企画・分析担当。口が悪いのでなるべく寡黙でありたいと思っている。が、ついついしゃべってしまい、毎回墓穴を掘っている。
最終更新日: 2024-04-09
作成日: 2024-04-09