今回は、Excelの小計とグループ化で一覧を集計する方法を紹介します。
小計は分類ごとの合計を入れる機能
Excelで売上明細、経費一覧、在庫一覧などを扱っていると、部門別、担当者別、月別などのまとまりごとに合計を見たいことがあります。ピボットテーブルを使う方法もありますが、元の一覧の中に集計行を入れて確認したい場合は小計が役立ちます。
小計は、指定した分類が変わる位置に集計行を挿入する機能です。合計、平均、件数などを選んで、分類ごとの結果を一覧の中に表示できます。さらに、グループ化された表示を折りたためるため、明細と集計を切り替えながら確認できます。
ただし、小計を正しく使うには、あらかじめ集計したい列で並べ替えておく必要があります。分類がばらばらに並んでいると、同じ分類が複数の場所に分かれて集計されてしまいます。
小計を入れる前の準備
小計を使う前に、表の形を整えます。1行目に見出しがあり、各列に同じ種類のデータが入っている状態が扱いやすいです。途中に空白行や空白列があると、範囲が正しく認識されないことがあります。
準備として確認する点は次の通りです。
- 1行目に列見出しがあるか
- 途中に空白行がないか
- 集計したい分類で並べ替えているか
- 合計したい列が数値になっているか
- 既存の小計行が残っていないか
たとえば部門別に売上を集計したい場合は、部門列で並べ替えます。担当者別に集計したい場合は、担当者列で並べ替えます。並べ替えが済んでから小計を実行すると、分類ごとにまとまった集計行が入ります。
小計を設定する流れ
小計は、データタブから設定します。表の中のセルを選択し、小計を開きます。そこで、集計の基準にする列、使用する関数、集計対象の列を指定します。
基本の流れは次の通りです。
- 集計したい分類で表を並べ替える
- 表の中のセルを選択する
- データタブから小計を開く
- グループの基準となる列を選ぶ
- 合計や平均などの集計方法を選ぶ
- 集計する列にチェックを入れる
設定すると、分類が変わる位置に小計行が追加されます。表の左側にはアウトラインが表示され、明細行を折りたたんだり表示したりできます。
グループ化表示を使う
小計を設定すると、Excelは自動的に明細行をグループ化します。左側に表示される番号やプラス、マイナスのボタンを使うと、集計だけを表示したり、明細まで展開したりできます。
全体の傾向を見たいときは小計行だけを表示し、内容を確認したいときは明細を展開します。会議や確認作業では、最初に集計だけを見せ、必要な分類だけ明細を開くと説明しやすくなります。
グループ化表示で確認したい点は次の通りです。
- 小計行だけで分類ごとの結果が分かるか
- 必要な明細をすぐ展開できるか
- 総計行が表の最後に入っているか
- 折りたたんだ状態でも見出しが分かるか
明細が多い表では、グループ化により表示を切り替えられることが大きな利点です。確認したい粒度に合わせて、表示を変えながら使います。
小計を入れ直すときの注意
小計を一度設定した後に別の分類で集計したい場合は、既存の小計を削除してから設定し直すのが基本です。小計行が残ったまま再設定すると、集計行が重複して表が分かりにくくなることがあります。
小計の設定画面には、すべて削除するためのボタンがあります。集計をやり直す場合は、この機能で小計行を消し、表を元の明細状態に戻してから再度設定します。
また、小計行を手作業で削除すると、グループ化や集計の状態が中途半端に残る場合があります。なるべく小計の削除機能を使って整理します。
フィルターや並べ替えとの関係
小計を使う表では、並べ替えが重要です。小計は分類が変わる位置を基準に集計行を入れるため、後から並べ替えを行うと集計行の位置が崩れることがあります。
小計を入れた後に大きく並べ替える必要がある場合は、いったん小計を削除し、並べ替え後に再設定するほうが安全です。フィルターを使う場合も、表示されている行と集計行の関係を確認します。
作業の順番としては、次の流れが扱いやすくなります。
- 元データを整える
- 分類列で並べ替える
- 小計を設定する
- グループ化表示で確認する
- 必要に応じて小計を削除してやり直す
表を継続的に更新する場合は、小計よりもピボットテーブルのほうが向いていることもあります。小計は、一覧表の中で明細と集計を同時に見たい場面に向いています。
印刷前に表示状態を確認する
小計を使った表を印刷する場合は、どの階層を表示した状態で印刷するかを確認します。明細をすべて表示するとページ数が増えます。小計行だけを表示すれば、分類ごとの集計をコンパクトに確認できます。
印刷前には、見出し行、改ページ位置、列幅、小計行の表示を確認します。折りたたんだ状態で印刷する場合は、読み手が何の集計か分かるように列見出しやタイトルを整えます。
また、PDFで共有する場合も、表示状態がそのまま反映されます。明細を見せる資料なのか、集計だけを見せる資料なのかを決めてから保存すると、受け手が迷いにくくなります。
元データの保管も忘れない
小計を入れると、一覧表の中に集計行が追加されます。見た目には分かりやすくなりますが、元の明細データとは形が変わります。後から別の集計をしたり、他のシステムへ取り込んだりする場合は、元データを残しておくと安心です。
おすすめは、元データのシートをコピーし、コピーしたシートで小計を設定する方法です。元データは並べ替えや小計を入れない状態で残し、確認用のシートだけを加工します。
また、小計行が入った表でさらに集計関数を使うと、小計行を含めて二重に計算してしまう場合があります。別の集計を作るときは、小計を削除するか、集計対象の範囲を確認します。
共有用の表では、小計を入れた目的が分かるように、シート名や見出しにも集計単位を書いておくと親切です。部門別、月別、担当者別など、何で集計しているかを明確にします。
まとめ
Excelの小計は、一覧表の中に分類ごとの集計行を入れ、明細と集計を切り替えながら確認できる機能です。部門別、担当者別、月別など、分類ごとに合計や平均を見たいときに役立ちます。
使う前には、集計したい分類で並べ替え、表の空白行や数値列を確認します。小計を入れるとグループ化表示も使えるため、明細を折りたたんで集計だけを確認できます。集計をやり直す場合は、既存の小計を削除してから設定し直すと、表を整理しやすくなります。