Microsoft Excel で SUBTOTAL 関数を使用する方法

Microsoft Excel では、関連するアイテムのグループの小計を簡単に取得できます。 実際、これを行うには 2 つの方法があります。 SUBTOTAL 関数または Subtotal 機能のどちらか最適な方を使用できます。 その方法をご紹介します。

Excel の小計とは

前述のように、Excel の SUBTOTAL を使用すると、リスト内の同じアイテムまたは関連するアイテムをグループ化し、関数を使用して値を計算できます。 たとえば、月ごとの売上や学生ごとの平均成績を合計するために使用できます。 商品ごとに在庫を追加したり、来週の請求書の数を数えたりすることもできます。

関連: Microsoft Excel で COUNT 関数を使用する方法

数式を作成して SUBTOTAL 関数を使用するか、小計機能を使用すると、必要な計算をわずか数ステップで取得できます。

Excel SUBTOTAL 関数を使用する

Excel の SUBTOTAL 関数を使用して数式を作成し、非表示にした行を柔軟に含めたり除外したりできます。

式の構文はSUBTOTAL(function_number, reference 1, reference 2,...)で、最初の 2 つの引数が必要です。 必要に応じて、残りの引数に追加のセル参照または名前付き範囲を使用できます。

function_number引数を使用すると、対応する番号を使用して 11 個の関数のいずれかを挿入できます。 最初の 11 には非表示の行が含まれ、2 番目の 11 には非表示の行が含まれません。 したがって、必要な関数の番号と非表示の行をどのように処理するかを挿入するだけです。

SUBTOTAL 関数の例として、次の数式を使用してセル範囲 B2 から B4 の売上合計を合計します。

 =小計(9,B2:B4) 

この次の例では、行 4 と 5 を非表示にしました。この最初の式を使用すると、最初の引数に数値 9 を使用して合計を取得し、非表示のデータを含めることができます。

 =小計(9,B2:B6) 

ここで、最初の引数に数値 109 を使用して、これらの非表示の行を合計から除外します。

 =小計(109,B2:B6) 

小計を取得したら、もう一度 SUBTOTAL 関数を使用して、最下部の総計を計算できます。 または、SUM 関数を使用して小計を追加することもできます。

関連: Microsoft Excel で列を合計する方法

別の例として、次の式で非表示の行 3 と 4 を含めるために average 関数を使用します。

 =小計(1,C2:C6) 

次に、次の式で非表示の行を除外します。

 =小計(101,C2:C6) 

SUBTOTAL関数に関する注意事項

Excel で SUBTOTAL 関数を使用する場合は、次の点に注意してください。

• この関数は、行または水平範囲ではなく、列または垂直範囲に対して機能します。
• 参照引数領域にネストされた小計がある場合、Excel は小計結果でこれらを無視します。
• フィルターを使用すると、フィルターで除外されたデータは小計結果から除外されます。

小計機能を使用する

Excel で SUBTOTAL 関数を使用するもう 1 つの方法は、小計機能を使用することです。 このようにして、自動的に計算を追加し、同時に項目をグループ化できます。 Excel では、SUBTOTAL 関数を使用してこれを実行します。 いくつかの例を見てみましょう。

関連: Microsoft Excel の関数を使用して稼働日を計算する方法

月ごとの売上の小計機能を使用して、データを月ごとにグループ化し、各グループを合計して、下部に総計を表示できます。

グループ化して小計するすべての行を選択します。 [データ] タブに移動し、[アウトライン] ドロップダウン メニューで [小計] を選択します。

[小計] ボックスが開いたら、小計の追加方法を選択します。

• At Each Change In :グループ化に使用する列を選択します。 ここでは、月がシートで変更されたときに新しいグループが作成されるように、月を選択しました。
• 関数の使用:合計、平均、最小、最大、または別のオプションなど、使用する関数を選択します。 この例では、Sum を選択しました。
• 小計を追加:小計を計算する場所のボックスをオンにします。 この例では、Sales を選択しました。

必要に応じて、追加項目の下部にあるボックスにチェックを入れます。 「OK」をクリックします。

次に、データが更新されて行がグループ化および小計され、下部に総計が作成されます。 プラス、マイナス、および数字のボタンを使用して、グループを折りたたんだり展開したりして、見やすくします。

別の例として、学生の成績を使用します。 小計機能とグループ機能を使用して、各生徒の平均成績を表示します。 これがプロセスです。

行を選択し、[データ] に移動して、[アウトライン] ドロップダウン メニューで [小計] を選択します。

[小計] ボックスで、変更ドロップダウン リストから [学生] を選択し、関数リストから [平均] を選択します。 次に、グレードのチェックボックスをオンにします。 「OK」をクリックします。

行が生徒ごとにグループ化され、それぞれの平均成績と全体の平均が下部に表示されます。 ここでも、左側のボタンを使用して、グループを折りたたんだり展開したりできます。

小計機能を使用した後で行のグループ化を解除すると、行は通常の状態に戻ります。 ただし、Excel で使用される SUBTOTAL 関数を含むものは、引き続き使用するか、必要に応じて削除することができます。

多くのグループを使用する予定の場合、小計機能は複雑になる可能性があります。 ただし、この関数に興味がある場合は、これらの基本的な例が役立つはずです。

注意すべきことの 1 つは、この機能では Excel テーブルの小計を追加できないことです。 テーブルにデータがある場合は、前述のように SUBTOTAL の Excel 数式を挿入するか、テーブルをセル範囲に変換して機能を使用することができます。 後者を選択すると、テーブルの機能が失われます。

関連Microsoft Excel でテーブルを範囲に、またはその逆に変換する方法

Excel に小計を挿入する方法がわかったので、重複行を削除する方法を確認してください。