マスター Excel 小計:正確なデータ要約のための 7 つの実証済みテクニック

方法 1 – Excel に小計を自動的に挿入する

• ランダムなセルを選択し、データ に移動します。 タブ> [並べ替え] をクリックします。 .

• 並べ替え ダイアログ ボックスがウィンドウにポップアップ表示されます。
• 並べ替えで フィールドで、小計を挿入する基準となる列を選択します。
• セル値を保持する 並べ替えで フィールド。
• 順序は昇順になります:A から Z .
• [OK] をクリックします。 .

• これにより、営業担当者 が並べ替えられます。 列はアルファベット順

Excel の小計ショートカットとも呼ばれる組み込み機能を使用します。

• 範囲内のセルをクリックし、小計 を選択します。 概要 の下 データ のグループ タブ。

• 小計 ダイアログボックスが表示されます。 SUM を使用して Excel に小計を挿入します。 関数。
• 変更のたびに フィールドには、小計を挿入するデータを保持する必要があります。
• 合計を入力します。 関数を使用する の関数 合計値を計算するフィールド。
• [OK] をクリックします。 .

Excelで小計を挿入できるようになります。 営業担当者 列には小計と総計が表示されます。 .

• 左上隅にある数字 1、2、3 のアイコンは、Excel で小計を使用する方法を示しています。 Excel でグループを処理したり、小計を表示または非表示にしたりできるようになります。

方法 2 – 複数の小計を追加する

2.1.複数の小計を異なる列に挿入

前のセクションでは、小計を Sales Rep 列に適用する方法を示しました。 カテゴリに基づいて別の小計を追加します。 .

• 並べ替えを開きます データ のダイアログ ボックス タブ。
• 営業担当者順に並べ替えます .
• 別のレベルを追加します。
• 営業担当者別に並べ替えました。 次にカテゴリで並べ替えます。 .
• [OK] をクリックします。 .

• 小計 を開きます データ のダイアログ ボックス 方法 1 で説明されているタブ .
• 営業担当者 を選択します 変更のたびに フィールド。

• Excel は 営業担当者 に小計を挿入します。 列。
• 小計 を開きます ポップアップ。
• 今回はカテゴリの各変更を確実にします .
• 現在の小計を置き換える フィールドはマークされていません。そうでない場合は、前の小計がクリアされ、1 つの小計が作成されます。
• [OK] をクリックします。 .

複数回グループ化し、Excel に小計を挿入します。

小計 で別の変更を選択します 小計をさらに追加したい場合は、ボックスをクリックします。

2.2.同じ列内の複数の小計を検索

同じ列に複数の小計を挿入します。合計を計算した後、平均と標準偏差を計算します。さらに 2 つの関数が必要になります。 平均を選択します 関数、次に StdDev 機能。

営業担当に小計を挿入してから開始します (方法 1 から取得 ) SUM を使用 機能。

• 小計 を開きます ボックスを 2 倍にします。
• 平均を選択します 関数を選択し、2 回目は StdDev を選択します。 関数。

• 平均 この関数は、選択された列の平均を求めます。
• 標準開発 関数は標準偏差を計算します。

これらの関数に基づいて、小計がデータに挿入されます。

方法 3 – Excel テーブルに小計を挿入する

Excel テーブルに小計を直接適用することはできません。 小計 この機能はここでは動作しません。

小計を挿入したい場合は、まずテーブルを範囲に変換する必要があります。

• 表内のランダムなセルを選択し、表のデザインに移動します。 タブ> 範囲に変換を選択します ツール の下 グループ。

• Microsoft Excel では、テーブルを範囲に変換するかどうかを尋ねられます。 [はい] をクリックします。 確認するため。

Excel はテーブルを通常の範囲に変換します。 小計を挿入できます。 小計機能を適用することによって。

方法 4 – フィルターを使用して Excel で SUBTOTAL 式を適用する

小計 を使用しました。 特徴。 Excel にはSUBTOTAL 関数もあります。 データに小計を追加することができます。 フィルタリングされたデータには関数を使用します .

小計の構文 関数:

SUBTOTAL(function_num,ref1,[ref2],…)

最初の引数:function_num いくつかの数字 (1 ～ 9) で表されます。 と101-109 ) 異なる機能を示します。

引数 機能 1 ～ 9 フィルタリングされた行は無視されますが、手動で非表示にした行は含まれます。101 ～ 109  フィルターされた行と手動で非表示にした行の両方を無視します。

4.1.フィルタリングされた行の小計

次の小計を適用しました。 小計を挿入する数式。

最初の引数 9 を使用したため、フィルタリングされたセルは無視されます。

• 営業担当のドロップダウン矢印をクリックします。 列をクリックして名前を選択します (つまり、アダム) ) その名前でフィルタリングします。

フィルタリングされたセルのみの小計を計算する関数を観察し、残りは無視します。

下の画像は小計の出力を示しています。 両方の引数 9 に対する関数 そして109 .

4.2.手動で非表示にした行の小計

小計の出力を確認してください。 手動で非表示にした行の関数。行番号を右クリックし、[非表示] を選択して行を非表示にします。 コンテキスト メニューから。

引数 9 の出力を比較します。 そして109 。引数 9 手動で非表示にした行のセルが含まれますが、引数 109 それらは無視されます。

方法 5 – VBA コードを使用して小計を検索する

• ALT+F11 を押します。 Visual Basic Editor を起動します。 ウィンドウ。
• [挿入 ] をクリックします。 ツールバーで>モジュールを選択します。 .

• モジュール に VBA コードを挿入します。 ウィンドウ。

コード :

Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
    If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
    Rows(iValue + 1).Insert
    Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
    For iColumn = 7 To 8 'Columns to Calculate Sum
    Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
    Next iColumn
    Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
    iValue = iValue + 2
    xValue = iValue
    Else
    iValue = iValue + 1
    End If
    Loop
Application.ScreenUpdating = True
End Sub

• [実行 ] をクリックします。 VBA を実行するボタン コード。

Excel は営業担当者に基づいてデータに小計を挿入します。 .

方法 6 – Excel ピボット テーブルに小計を追加する

ピボット テーブルには 4 つのフィールドがあり、フォーマットされたピボット テーブルがあります。 これらのフィールドで当社のデータを使用すると、次のようになります。
1.フィルター :営業担当者

<オル>

列 :空白

行 :カテゴリ、製品

価値観 :数量、単価、合計価格、利益

• デザイン に移動します。 タブ> 小計 のドロップダウンをクリックします。 [レイアウト] のオプション グループ。

• [すべての小計をグループの一番下に表示する] を選択します。 ドロップダウン メニューから選択します。

ピボット テーブルは、行ラベルに基づいて各グループの下部に小計を挿入します。 。グループの総計を表の一番下に追加します。

方法 7 – Power Query を使用して小計を挿入する

パワークエリを使用してグループの小計を挿入します。 .

• 範囲内のセルをクリックし、データ に移動します。 タブ> テーブル/範囲からをクリックします。 [データの取得と変換] の下 .

• [OK ] をクリックします。 テーブルの作成 ダイアログボックス。

• Excel から Power Query エディター に移動します。 ウィンドウ。
• 列を選択します (例:営業担当者 ) とカテゴリ ) 処理するデータ内で> [グループ化 ] をクリックします。 変形 のアイコン タブ。

• グループ化内 ダイアログ ボックスで、新しい列名を指定します。
• 実行する操作の種類 (SUM など) を選択します。
• 列 を選択します。 計算を実行する場所 (つまり、合計価格)。
• [攻撃性の追加] をクリックします。 さらにグループ化します。

• 利益の小計を計算するには 、 [利益 ] を選択します。 コラム フィールドと合計 作戦中 フィールド。この攻撃に名前を付けます (つまり、利益) ).
• [OK] をクリックします。 .

• 営業担当者とカテゴリに基づいて挿入された小計が表示されます。
• ホーム に移動します タブ>閉じてロードのドロップダウンをクリックします> [閉じてロード] を選択します。 .

パワークエリ エディタは、グループ化されたデータと小計を Excel ワークシートにロードします。

Excel で小計を削除する方法

• 小計を開きます データ のダイアログ ボックス タブ> [すべて削除] をクリックします。 .

• データからグループと小計を削除して、データを通常の範囲に変換します。

注意 :小計を削除すると、データは並べ替えられたままになり、並べ替え前の元のデータとは異なります。

よくある質問

１． Excel で小計を削除できないのはなぜですか?

小計はいくつかの理由で機能しない場合があります。

i) 保護されたワークシート:シートがパスワードで保護されている場合は、保護を解除する必要があります。

ii) 共有ワークブック:ワークブックの共有を解除するか、ワークブックが複数のユーザー間で共有されているかどうかを確認する必要がある場合があります。

iii) カスタムの数式またはマクロ:カスタムの数式またはマクロを使用して小計が追加された場合、それらを削除するには変更または削除が必要になる場合があります。

２． Excel で小計を折りたたんだり展開したりするにはどうすればよいですか?

グループ化されたアウトライン機能を使用すると、Excel で小計を折りたたんだり展開したりできます。 Excel では、小計グループを折りたたんだり展開したりできるアウトライン記号が自動的に追加されます。グループ行の横にあるマイナス (-) またはプラス (+) 記号をクリックすると、それぞれ小計が折りたたまれたり、展開されたりします。

３．データが変更されたときに Excel の小計を自動的に更新できますか?

はい、データが自動的に変更されたときに小計を更新するように Excel を設定できます。小計行を右クリックして「更新」 を選択することで、小計を手動で更新することもできます。 コンテキストメニューから。自動的に更新されない場合は、ワークブックで数式が自動計算されるようにしてください。 .

４． AGGREGATE は Excel の SUBTOTAL 関数と同じものですか？

いいえ、集計には違いがあります。 と 小計  構文は似ていますが、機能します。

記事からの重要なポイント

• Excel の「小計」機能を使用すると、指定した列に基づいてデータ グループの小計を挿入できます。
• 小計を挿入するには、データの範囲を選択して [データ] タブに移動し、[アウトライン] グループの [小計] ボタンをクリックします。
• 小計する複数の列を選択することで、複数レベルの小計を追加できます。
• 小計は、マイナス (-) 記号とプラス (+) 記号で示されるグループ化されたアウトライン機能を使用して折りたたんだり展開したりできます。
• Excel のテーブルまたは動的な名前付き範囲を使用すると、データが変更されたときに小計を自動的に更新できます。

練習ワークブックをダウンロード

演習ワークブックはここからダウンロードできます。

<

ソリューション付きの高度な Excel 演習を無料で入手しましょう!