SUBTOTAL関数：ExcelのSUBTOTAL関数の数式と使い方

ExcelのSUBTOTAL関数とは何ですか? Excelの小計数式とは何ですか? LuckyTemplates.com で調べてみましょう!

Microsoft Excel には合計を計算するために使用される関数が多数あり、SUBTOTAL もその 1 つです。ただし、小計は SUM 関数とは異なります。普通の金額ではないですよ。代わりに、リストやデータベースの小計の計算、平均の計算などに使用されます。

SUBTOTAL関数に関する主な内容

• SUBTOTAL 関数とは何ですか?何に使用されますか?
• Excelの小計数式
• ExcelのSUBTOTAL関数の使い方
  • 1. フィルタリングされた行の合計を計算します。 
  • 2. フィルタリングされた空でないセルを数える
  • 3. ネストされた小計式の値を無視する
• Excel で SUBTOTAL 関数を使用する前に考慮すべきこと
• ExcelでSUBTOTAL関数を実行するとエラーが発生する方法

SUBTOTAL 関数とは何ですか?何に使用されますか?

一見すると、SUBTOTAL 関数の目的は何だろうと疑問に思うかもしれません。結局のところ、SUMIFやAVERAGEなどの既存の関数を使用して、セル範囲から小計を見つけるのは簡単です。

SUBTOTAL を必ずしも使用する必要がないのは事実ですが、SUBTOTAL を使用すると、対象のデータの分析がはるかに簡単になります。SUBTOTAL を使用すると、SUBTOTAL 数式を含む他のセルが無視されるため、全体の合計に影響を与えることなくセル範囲に数式を挿入できます。

SUBTOTAL は、セルのフィルタリングなど、他の Excel 機能と連動します。値でテーブルをフィルターすると、SUBTOTAL 式が更新され、フィルターが認識され、対応するセルが除外されます。SUBTOTAL は、他の関数 (SUM など) ではできない非表示の値を無視する場合にも役立ちます。

幸いなことに、SUBTOTAL 式の一部として他の関数を使用できます。11 種類の小計方法が利用可能で、それぞれ既存の Excel 関数に適しています。フィルターされたセルを処理し、他の SUBTOTAL 式を無視する SUM 式が必要な場合は、SUBTOTAL を使用するのが最善ですが、ピボット テーブルでも同じように機能します。

Excelの小計数式

SUBTOTAL 関数は、別の数学関数に基づいてセル範囲の合計値を計算することで機能します。SUBTOTAL は、関数パラメーター内で動作しながら (つまり、他の SUBTOTAL 式を無視して)、最大 11 個の異なる数学関数 (AVERAGE から VAR.P まで) を使用して適切な合計値を見つけます。

SUBTOTAL 関数の構文は=SUBTOTAL(function_num, ref1, [ref2],…)です。そこで：

• Function_num: 1 ～ 11 および 101 ～ 111 の数字は、SUBTOTAL の計算に使用される関数を指定します。
• Ref1、Ref2、...: 小計を計算する 1 つ以上のセル、またはセル範囲 (最大 254)。

注記：

• SUBTOTAL 関数は、データの垂直列を計算するように設計されています。
• 引数 ref1、ref2、... に SUBTOTAL 関数が含まれている場合、それらは二重カウントを避けるために無視されます。
• function_num が 1 ～ 11 の場合、その範囲を含む行は非表示であるため、SUBTOTAL 関数はデータ セット内の非表示の値を含めて計算します。function_num が 101 から 111 の場合、SUBTOTAL 関数はデータ セット内の非表示の値のみを計算します (非表示の行の値はカウントしません)。
• Filter によって非表示になったデータ領域の場合、SUBTOTAL は無視されます。

ExcelのSUBTOTAL関数の使い方

1. フィルタリングされた行の合計を計算します。 

このタイプの合計をより深く理解するには、「Excel でフィルターされたリスト値の合計を計算する」の記事を参照してください。基本的に、この場合の SUBTOTAL 関数は次のようになります。

=SUBTOTAL(9,_range)

ここでのpham_rangeは、データをフィルタリングした後に合計する領域です。

2. フィルタリングされた空でないセルを数える

SUBTOTAL 3 または SUBTOTAL 103 を使用します。ただし、非表示の行がある場合は、SUBTOTAL 103 を使用して、表示されている空白以外のセルを正確に数える必要があります。

たとえば、上記のデータ テーブルでは 4 行と 5 行の 2 行が非表示になります。SUBTOTAL 3 または SUBTOTAL 103 を使用すると、2 つの異なる結果が生成されます。

数式を入力すると、Excel が自動的に関数セットを表示するので、覚えておく必要はありません。

SUBTOTAL 3を使用した場合の結果は、行内の非表示のセルも含めて 3 になります。

SUBTOTAL 103に関しては、表示されている空でないセルのみが表示され、非表示の行は無視されます。

3. ネストされた小計式の値を無視する

たとえば、倉庫 A1 と A2 にある生地の合計キログラムの平均を計算します。

倉庫 A2 の平均式はSUBTOTAL(1,C2:C4) で、結果は 19 になります。

倉庫A1 = SUBTOTAL(1,C5:C7)の平均式の結果は 38 になります。

ただし、2 つの倉庫の総生地数の平均を計算する場合、2 つの倉庫の平均結果は無視されます。数式 =SUBTOTAL(1,C2:C9) があり、その結果は、以前に計算された平均結果を自動的に削除します。

Excel で SUBTOTAL 関数を使用する前に考慮すべきこと

SUBTOTAL には利点もありますが、この関数の使用を開始する前に考慮すべき点がいくつかあります。

• 前述したように、function_num 引数に 1 ～ 11 を使用すると、SUBTOTAL に非表示の値が含まれるようになりますが、101 ～ 111 はそれらの値を無視します。
• 1 ～ 11 または 101 ～ 111 以外の値を使用すると、Excel は #VALUE! エラーを返します。これは 3D セル参照でも発生します (複数のシートにまたがる同じセルが範囲内で参照される場合)。
• A1:D1 などの水平セル範囲が使用される場合、(function_num 引数の値に関係なく) 非表示の値が自動的に含まれます。これは SUBTOTAL 関数の制限であり、克服することはできません。
• フィルターされたデータに対して SUBTOTAL を使用すると、使用される function_num 引数に関係なく、非表示の値が常に無視されます。
• SUBTOTAL は、IF 関数を含む数式など、他の関数の一部として入れ子関数として使用できます。
• 別の SUBTOTAL 式がデータ範囲 ref1 内にある場合、SUBTOTAL はそれを無視し、全体の計算から除外します。
• Excel では、SUBTOTAL 数式で最大 254 のセル範囲を使用できますが、数式が機能するために必要なセル範囲は 1 つだけです。

ExcelでSUBTOTAL関数を実行するとエラーが発生する方法

Excel で SUBTOTAL 関数を計算すると、次のような基本的なエラーが発生することがあります。

• #VALUE!: 関数識別子が 1 ～ 11 または 101 ～ 111 の範囲にないか、3D 参照を持っています。
• #DIV/0!: 特定の合計を 0 で除算する必要がある場合に発生します (たとえば、数値を含まないセル範囲の平均または標準偏差を計算する場合)。
• ＃名前？SUBTOTAL 関数名のスペルが間違っています。

Microsoft Excel の SUBTOTAL 関数の使用方法に関する、意外と知られていないヒント

ヒント 1:いずれかの倉庫で、すべての T シャツの色がすべてのサイズで入手可能であることを確認したいとします。次の手順に従ってください。

ステップ 1: [小計]をクリックします。このステップは、既存の小計データにさらに基準を追加することであることに注意してください。

ステップ 2:ドロップダウン メニューからCOUNTを選択し、 「小計フィールドを追加する」フィールドからサイズを選択します。次に、[現在の小計を置き換える] のチェックを外します。[OK]をクリックすると、次のデータ テーブルが表示されます。

これにより、十分な数の異なるディメンションが確保され、繰り返しの操作を必要としない方法でデータを分類できるようになります。

ヒント 2 : データを列ごとに並べ替えると常に便利です。

ヒント 3:最初の行にラベルを含めるのに役立ちます。

ヒント 4:データを集計する場合は、「小計を挿入するときにデータの下に集計を表示する」ボックスのチェックを外します。- SUBTOTAL関数を挿入する場合、以下のデータを要約します。

Excelの小計関数とは何ですか?

名前が示すように、Excel の SUBTOTAL 関数は小計を計算するために使用されます。Excel SUBTOTAL 関数は、指定された値の集計結果を返します。SUBTOTAL は、合計、平均、個数、最大値を計算できます。この Excel では、非表示の行の値を含めたり除外したりすることもできます。

• 目的: リストまたはデータベースの小計を計算します。
• 戻り値: 小計の種類を表す数値。
• 口論：
  • function_num- リストの小計を計算するために使用する関数を指定する数値。
  • ref1- 名前付き範囲または小計への参照。
  • ref2- [オプション] 名前付き範囲または小計への参照。

SUBTOTAL 関数を使用すると、より複雑なピボット テーブルに依存せずにデータ セットを迅速に分析できます。行き詰まった場合は、リボンの [データ] タブにある小計機能を使用して、小計数式を自動的に作成し、他の場所で編集またはコピーすることもできます。

続きを見る：

• ExcelのSUMPRODUCT関数：対応する値の積の合計を計算します。
• Excelで内容を失わずに2つの列のフルネームとファーストネームを結合する方法