SUBTOTAL関数: Excelでフィルタリングされたリストの合計値を計算します

データをフィルタリングする際の合計関数は、Microsoft Excelユーザーにとって非常に重要です。この記事では、Excel で小計を使用してフィルター処理し、合計を計算する方法をまとめます。

スプレッドシートの列の合計を知ることは便利ですが、異なるグループの合計を知りたい場合もあります。 Excel の小計機能は、最終的な合計とは別に小額を計算する実用的な方法です。スプレッドシートでの使用は比較的簡単で、データを整理できます。

Excel の小計関数は、指定された演算に基づいて値の範囲を計算する数式です。これは =SUBTOTAL(function_num, ref1, [ref2],...) と表示されます。ここで、function_num は数式で実行する演算を指します。 ref1、[ref2]、...はExcelで計算に含める値です。小計関数の最も一般的な用途の 1 つは列の合計を計算することですが、次の操作も実行できます。

中くらい
カウント
すべてを数える
最大値
最小値
乗算
母集団の標準偏差
サンプル標準偏差
人口の分散
標本分散

このチュートリアルは Excel 2019 で行われますが、チュートリアルではExcel の小計関数を使用しているため、Excel 2007、Excel 2010、Excel 2013、Excel 2016 などの他のバージョンの Excel でもこの計算を適用できます。

Excelの小計関数を使用して、フィルタリングされたリストの合計値を計算する

次のような、フィルターがすでに作成されたデータテーブルがあるとします。

STT	製品タイプ	製品名	量	単価	合計金額
1	本	算数練習問題 3年生	2	10000	20000
2	ペンケース	TL 布製ペンケース	2	30000	60000
3	鉛筆	TL段落鉛筆	5	15000	75000
4	本	数学 3年生	3	18000	54000
5	本	英語3年生	3	19000	57000

SUBTOTAL関数: Excelでフィルタリングされたリストの合計値を計算します

書籍カテゴリに属する製品のリストを除外すると、次のスプレッドシートが作成されます。

SUBTOTAL関数: Excelでフィルタリングされたリストの合計値を計算します

要件は、書籍の合計コストを計算することです。上記のフィルタリングされたデータテーブルでSUM() 関数を使用すると、得られる結果は書籍の合計価格ではなく、すべての製品の合計価格になります。

この場合、SUBTOTAL 関数を次のように使用し、セル E13 に次のように入力します。

=SUBTOTAL(9,H7:H11)

ここで、9 は使用する関数に対応する引数値です。ここでは合計を計算したいので、使用する関数は SUM です。下の表に示すように、H7:H11 は合計を計算する範囲です。

結果として、書籍の合計コストは 131,000 であることが返されました。

SUBTOTAL関数: Excelでフィルタリングされたリストの合計値を計算します

SUBTOTAL()関数について

SUBTOTAL() 関数は、列 D の値のリスト全体を調べ、フィルターを満たす値のみを計算します。上の画像を見ると、引数 9 を宣言したためだと推測できます。ただし、この引数は参照された値の合計を計算することを Excel に指示します。次の表に、受け入れられる引数を示します。

隠れた価値を含める	非表示の値を無視する	顎
1	101	平均（）
2	102	カウント（）
3	103	COUNTA()
4	104	最大()
5	105	最小値()
6	106	製品（）
7	107	標準偏差()
8	108	STDEVP()
9	109	和（）
10	110	変数()
11	111	VARP()

上の表を見て、9 と 109 の違いは何だろうと疑問に思うかもしれません。引数 9 を使用すると、SUBTOTAL() 関数はすべての非表示の値を合計します。引数 109 を使用すると、SUBTOTAL() 関数は非表示の値を無視します。非表示の値と、フィルターを満たさないために除外される値を明確に区別する必要があります。行を非表示にするには、行の順序を右クリックし、[非表示]を選択します。これは、フィルターを満たしていないために表示されない行とはまったく異なります。

SUBTOTAL() 関数は、他の多くの便利なタスクを実行するためにも使用されます。 Quantrimang.com のSUBTOTAL 関数に関するチュートリアルを参照できます。

参照: