Windows 10 & Office 2016 Excel 2016

データが入っている行を項目(グループ)ごとに計算する[小計]機能

投稿日:2017年11月20日 更新日:

Excelには、データをグループごとに集計してくれる機能があります。

わざわざ行を挿入して、集計行を作成する必要はありません。

[小計]というボタンを押すと、瞬時にグループごとに集計行が挿入されて、項目ごとに合計や平均、個数などを求めることができます。

小計が追加された表

参考すでに数式を入力している表であれば、以下の記事が参考になると思います。

アウトラインの自動作成とグループ化

データをグループ化すると、アウトラインを作成することができます。 アウトラインが設定された表は、目的にあったレベルの詳細を表示したり、部分的 ...

続きを見る

データの並べ替え

この[小計]機能を使うには、まずグループごとにデータを並べ替えておく必要があります。

サンプルデータは、下のようになっています。

元の表

都道府県ごとに売上の集計を行いたいので、B列(都道府県の列)内のセルを選択します。

B列内のセルであれば、どこでもいいです。

ここでは、セルB4をアクティブにしています。

[データ]タブの[並べ替えとフィルター]グループにある[昇順]ボタンをクリックします。

並べ替えは、[降順]で並べ替えても構いません。

参考表内のセルを選択して並べ替えを行うと、自動的にセル範囲が認識されます。セル範囲は空白行と空白列に囲まれた範囲となります。

アクティブセル領域

注意セル範囲が正しく認識されない場合は、結合されたセルがないか、表の上の行にタイトルや日付などが入力されていないかなどをチェックしてください。

また、空白のセルは、昇順でも降順でも表の最後に並びます。

項目行もデータとして認識されてしまうような場合は、項目行にデータが入力されている行と異なる書式設定をしてみてください。

データと区別されて、並べ替えの対象にはならないと思います。(例えば、文字サイズ変更、太字設定、セルの色を付けるなど)

並べ替え

[データ]タブの[小計]

データが並べ替えられたことを確認します。

集計を求めたいグループごとにまとまっていることを確認します。

そして、表内のセルが選択されていることを確認します。表内のセルであれば、どこでいいです。

[データ]タブの[アウトライン]グループにある[小計]をクリックします。

[データ]タブの[アウトライン]グループにある[小計]

ポップヒントには、以下のように記されています。

小計

小計や合計を挿入し、関連するデータが入っている行を瞬時に計算します。

小計ボタン

自動的にセル範囲が選択されて、[集計の設定]ダイアログボックスが表示されます。

[グループの基準]の▼ボタンをクリックして、プルダウンメニューから[都道府県]を選択します。

[集計の方法]は、[合計]になっていることを確認します。

[集計するフィールド]で[売上額]のチェックボックスをオンにします。

[現在の小計をすべて置き換える]と[集計行をデータの下に挿入する]のチェックボックスはオンになっていると思いますが、念のため確認してください。

[OK]ボタンをクリックします。

集計の設定ダイアログボックス

[集計の設定]ダイアログボックス

  • グループの基準 集計の基準とするフィールドを選択します。
  • 集計の方法 集計方法を選択します。[合計]、[個数]、[平均]、[最大]、[最小]、[積]、[数値の個数]、[標本標準偏差]、[標準偏差]、[標本分数]、[分数]の11個があります。
  • 集計するフィールド 集計を求めたいフィールドを選択します。
  • 現在の小計をすべて置き換える すでに表に集計行が設定されている場合に使います。チェックボックスがオンの場合は、既存の集計行が削除されて、新規の集計になります。チェックボックスをオフにすると、既存の集計行に追加されます。集計行の追加で解説しています。
  • グループごとに改ページを挿入する グループごとに自動的に改ページが挿入されます。グループごとに印刷したい場合に便利です。
  • 集計行をデータの下に挿入する チェックボックスがオンの場合は、各グループの下に集計行が挿入され、チェックボックスがオフの場合は、各グループの上に集計行が挿入されます。

[小計]ボタンが無効な時は

[小計]ボタンが無効になっている場合は、表内のセルを選択して、[テーブルツール]タブが表示されないかを確認してください。

表がテーブルになっていると、[小計]の挿入はできないので、テーブルを解除して通常の表にしましょう。

小計ボタンが無効

[デザイン]タブの[ツール]グループにある[範囲に変換]をクリックします。

または、テーブル内で右クリックして、ショートカットメニューから、[テーブル]→[範囲に変換]をクリックします。

範囲に変換にすると、[小計]ボタンは有効になります。

テーブルはそのままにしておきたい場合は、シートをコピーして、そのシートでテーブルを範囲に変換して操作されることをおすすめします。

範囲に変換すると、セルの背景色、文字色、罫線などの書式はテーブルのままになります。

テーブルを範囲に変換

参考テーブルについては、以下の記事で解説しています。とても便利な機能です。

表をテーブルに変換してデータベース管理などの作業効率をアップ

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

[集計]設定後の表

結果、下のようになります。

都道府県ごとのグループの下に売上の集計行が挿入されて、表の最後には、総計が表示されています。

集計行の追加とアウトライン

参考アウトランの展開[+]、折りたたみ[-]ボタンの位置は、詳細データの上に設定することもできます。

展開と折りたたみボタンの位置

集計行の数式

集計行には、[SUBTOTAL関数]が設定されています。

SUBTOTAL関数

=SUBTOTAL(集計方法,参照)

集計方法には、リストの集計に使用する関数を1~11の番号で指定します。

SUBTOTAL 関数 - Office サポート(Microsoft)

1:AVERAGE

2:COUNT

4:MAX

5:MIN

9:SUM

アウトライン記号の役割

指定したレベルを表示するには、アウトラインの番号をクリックします。

アウトラインのレベル

[1]をクリックすると、下のように[総計]のみ表示になります。

レベル1をクリック

[2]をクリックすると、下のように各グループの[集計]のみの表示になります。

レベル2をクリック

展開[+]ボタンをクリックすると、

展開ボタン

下のようにそのグループの詳細データを表示できます。

[折りたたみ]ボタン[-]をクリックすると、詳細データは非表示になります。

折りたたみボタン

レベル[3]のボタンをクリックすると、

レベル3をクリック

すべてが展開されて、グループの詳細を表示できます。

すべて展開された表

参考行列をたたんだ状態をコピーして、別シートへ貼り付けようとすると非表示の行や列も貼り付けられてしまいます。

たたんだ状態のままコピーして貼り付けたい場合は、見えているセル(可視セル)をコピーします。

可視セル選択]のショートカットキーは[Alt]+[;]です。以下で解説しています。

可視セルのみをコピー

アウトライン記号が表示されない場合

[アウトラン]グループの[小計]をクリックして、集計行を挿入してもワークシートの左側にアウトライン記号が表示されない場合は、オプションの設定を確認してください。

まず、[ファイル]タブをクリックします。

そして[オプション]をクリックして、[Excelのオプション]ダイアログボックスを表示します。

[詳細設定]をクリックして、[次のシートで作業するときの表示設定]にある[アウトラインが適用されている場合はアウトラン記号を表示する]のチェックボックスをオンにします。

ここのチェックボックスがオフの場合は、集計行は挿入されても、アウトラン記号は表示されません。

Excelのオプション 詳細設定

ショートカットキーでアウトライン記号の表示/非表示

アウトライン記号の表示/非表示のショートカットキーは、[Ctrl]+[8]です。

[Excelのオプション]ダイアログボックスの設定と連動します。

キーボード[Ctrl]+[8]

集計行の追加

都道府県ごとの売上額の合計に加えて、さらに各都道府県の販売単価の平均も追加してみましょう。

表内のセルで選択して、[データ]タブの[アウトライン]グループにある[小計]をクリックします。

小計ボタン

自動的にセル範囲が選択されて、[集計の設定]ダイアログボックスが表示されます。

[グループの基準]の▼ボタンをクリックして、プルダウンメニューから[都道府県]を選択します。

[集計の方法]の▼ボタンをクリックして、プルダウンメニューから[平均]を選択します。

[集計するフィールド]で[販売単価]のチェックボックスをオンにします。

[現在の小計をすべて置き換える]のチェックボックスはオフにします。

[OK]ボタンをクリックします。

集計の設定ダイアログボックス

結果、下のようになります。

売上額の合計行はそのままで、販売単価の平均行を追加することができます。

平均行が追加された表

集計行の削除

集計行を削除して元の表に戻すには、表内のセルを選択して、[データ]タブの[アウトライン]グループにある[小計]をクリックします。

小計ボタン

[集計の設定]ダイアログボックスが表示されます。

[すべて削除]ボタンをクリックします。

集計の削除

関連以下は、Excel2007での解説です。

集計行の挿入

Excel2003でも解説しています。

自動集計(リストの項目ごとの集計)

Excelの[集計]という機能を使うと、簡単にリストの項目ごとに集計することができます。 [データ]メニューの[集計]をクリックして、[集計 ...

続きを見る

アウトラインの操作も便利ですが、[ユーザー設定のビュー]に登録して切り替える方法もあります。

フィルターの抽出結果や印刷設定の切替は[ユーザー設定のビュー]が便利

Excelには、画面表示やフィルターの設定、印刷設定などを登録して、必要に応じて素早く切り替えられる[ユーザー設定のビュー]という機能があり ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

関連記事30件

Profile

-Windows 10 & Office 2016 Excel 2016

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.