Excelで集計表を作る機能と言えばピボットテーブルですが、これとは別に「小計」という機能があります。この小計のことを行集計ということもあります。状況に応じて使い分けると良いと思います。
ところで、小計の機能は事前に表の並べ替えをする必要があり、そのためには事前に並べ替えが可能な表を作る必要があります。つまり、Excelの集計機能を学習する順番は、リスト形式の表の作成→並べ替え→小計→ピボットテーブルの順となります(参考:【Excel】並べ替えの昇順と降順、優先順位、グループ化、一部分だけの並べ替え - わえなび ワード&エクセル問題集)。
今回は、「小計」を使って行集計をする練習問題を出題します。
目次
1.ピボットテーブルと小計に共通すること
Excelで集計するためには「リスト形式の表」を作らなければなりません。このことは、ピボットテーブルも小計も同じです。
2.小計の基本トレーニング
(1)グループの下に集計行を入れるにはグループを作らなければならない
問題
次の表は選手の50音順に並んでいる。各グループの、体力の合計点を求めなさい。
解説
各選手はPグループまたはCグループに属していることがわかります。このグループで合計点を求めようとしています。
そこで、最終的にどのような表になればよいかを考えます。
リストとは別の場所に合計を求めるパターンもありますが、通常は、グループごとにレコードをかためて、小計を求めます。このようにレコードの途中に集計行を入れることを、「行集計」といいます。
行集計はグループごとにするため、必ず並べ替えをします。ただし、グループ化をするための並べ替えなので昇順か降順かは問いません。
表を全部選択します。グループで並べ替えます。
小計のボタンを押します。
この画面は、1番目にグループの基準を見ます。集計の単位(まとまり)のことであり、どのように行を区切るのかを指定します。グループごとに集計をするので、「グループ」になります。
行集計をするときに並べ替えをします。この並べ替えのキーと、グループの基準は同じになります。
次に集計するフィールドを見ます。フィールドとは列のことです。
今回は体力の列で集計をします。総得点のチェックは外します。
最後に集計の方法を見ます。合計を求めます。
グループごとに体力の合計を求めることになります。
これで完成です。
(2)アウトラインの表示と集計行の削除
問題
集計行だけ表示しなさい。また、集計行を削除しなさい。
解説
行集計をすると、画面の左側にアウトラインレベルが表示されます。上にある[1]から[3]はボタンです。[1]のボタンは総合計だけを表示するボタンです。
[2]のボタンは途中の行に挿入された集計行だけが表示されます。
[3]のボタンを押すと元に戻ります。すべてのデータが表示されます。
一番下の集計行や総計も含めて、表を全部選択します。
小計のボタンを押して、集計の設定の画面を出します。すべて削除します。
集計がなくなります。
集計行をなくすには全部選択して、すべて削除します。
(3)2列を集計する
問題
各チームの、体力・学力の平均点を求めなさい。
解説
今度は各チームの、体力・学力の平均点を求めます。表を全部選択します。
4チームありますが、それぞれ平均点を求めますから、まずチームごとに並べ替えます。昇順でも降順でも構いません。
小計のボタンを押して、集計の設定の画面を出します。グループの基準はチームです。
集計するのは体力と学力の2列です。
そして平均を求めます。
レベル[1]は全体の平均、レベル[2]はそれぞれのチームの平均、レベル[3]は元のデータです。
一番下の集計行や小計も含めて全部選択します。小計のボタンを押して、集計の設定の画面を出します。すべて削除します。
(4)グループの基準を2つにする
問題
各グループ・各チームの、体力・学力・総得点の平均を求めなさい。
解説
さきほどチームで並べ替えました。チームBとチームLはグループP、チームDとチームGはグループCになっています。
そこで、グループで集計をして、さらに、各チームで集計をします。表を全部選択します。並べ替えもグループとチームで並べ替えます。
小計のボタンを押して、集計の設定の画面を出します。グループの基準はグループです。
集計するのは体力、学力、総得点の3列です。
そして平均を求めます。
とりあえずグループで集計できました。もう一度、小計のボタンを押して、集計の設定の画面を出します。今度は、グループの基準はチームです。
ここで、「現在の小計をすべて置き換える」のチェックを外します。
これで、さらに細かく集計することができます。
これに伴ってレベルも1つ増えて、4つになります。
(5)元に戻す
問題
最初の状態に戻して、設問(1)から繰り返し練習しなさい。
解説
一番下の集計行や小計も含めて全部選択します。
小計のボタンを押して、集計の設定の画面を出します。すべて削除します。
選手の昇順に並べ替えれば、最初の状態に戻ります。
3.合計と平均を両方求める
問題
次のリストは選手の50音順に並んでいる。チーム別で、得点の合計と平均を求めなさい。
解説
チームで並べ替えます。昇順でも降順でも構いません。
小計のボタンを押して、集計の設定の画面を出します。グループの基準はチームです。集計するのは得点の列です。そして合計を求めます。
さらに平均を求めるときは、もう一度小計のボタンを押して、集計の設定の画面を出します。
2つ目の集計をするので、「現在の小計をすべて置き換える」のチェックを外します。
さらに、グループの基準を変えたり、集計方法を変えて2つ目の集計をするときは、「現在の小計をすべて置き換える」のチェックを外します。
現在の集計を取り消して、新たな集計をする場合にはチェックを入れます。
4.○○別、○○別集計
(1)グループの基準に大小関係(包含関係)がある場合
問題
グループ別・チーム別で得点の平均を求めなさい。
解説
いったん、さきほどの集計を削除します。
グループ別・チーム別で集計するときは、並べ替えもグループとチームで並べ替えます。
小計のボタンを押して、集計の設定の画面を出します。グループの基準はグループです。得点の平均です。
とりあえずグループで集計できました。もう一度、小計のボタンを押して、集計の設定の画面を出します。今度は、グループの基準はチームです。
ここで、「現在の小計をすべて置き換える」のチェックを外します。
グループの中にチームがあります。
このように含まれる関係(大小関係・包含関係)にある場合は、大きい分類から順に集計をします。
並べ替えは大きいグループから設定をします。
集計をするときも大きいグループから順に設定します。
(2)グループの基準に大小関係(包含関係)がない場合
問題
チーム別・左右別で得点の合計を求めなさい。また、左右別・チーム別で得点の合計を求めなさい。
解説
チームと利き手はまったく関連性のないグループで、大小関係(包含関係)はありません。このように含まれる関係にない場合、優先順位を自分で決めて集計しなければなりません。
まずは、チームを優先して集計してみます。並べ替えはチームが優先です。
小計もチームの合計が先です。
利き手の合計を求めます。
これを、「チーム別、左右別集計」といいます。大きいグループから順に「~別、~別集計」といいます。
いったん集計を削除します。
今度は、利き手を優先して集計します。並べ替えの優先順位を逆にします。
小計も利き手の合計が先です。
チームの合計を求めます。
これを、「左右別、チーム別集計」といいます。「~別、~別」というのは集計の優先順位を表しています。
集計の優先順位を変えると、集計結果の形が変わります。
チームを優先すると、まずチームごとに集計され、チーム内で左右それぞれ合計を求めることになります。
チームの総合計はありますが、左右それぞれの総合計はありません。
左右を優先すると、まず左右それぞれ集計され、その中でチーム別に集計されます。
左右の総合計はありますが、チームの総合計はありません。
ところで、チームと利き手のように大小関係(包含関係)がない場合、集計方法は2つあります。
行集計とクロス集計です。
優先順位を決めて集計する場合、行集計(小計)を使います。大中小と、3つ以上の分類があっても集計ができます。
ただし、最優先でない分類は2箇所以上に分かれるので総合計を出すことはできません。
優先順位を決めずに、項目を縦横に並べて集計する方法があります。これを「クロス集計」といいます。この場合、両方とも合計を求めることができます。
しかし、縦と横しかないので、原則として3つ以上のグループのクロス集計をすることはできません。
解説は以上です。
付録1 サンプルのファイルはこちら
わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお使いください。7-19、7-20のサンプルファイル(ZIP)です。
付録2 動画版はこちら(無料)
この記事は、わえなびファンダメンタルExcel Program7-19、7-20 の動画の内容を書き起こし、加筆修正したものです。
- ファンダメンタルExcel 7-19 行集計1(小計のトレーニング)【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
- ファンダメンタルExcel 7-20 行集計2(小計のグループを考える)【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。
ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko