わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

Excelの「小計」を使って行集計をするには並べ替えをする必要がある


Excelで集計表を作る機能と言えばピボットテーブルですが、これとは別に「小計」という機能があります。この小計のことを行集計ということもあります。状況に応じて使い分けると良いと思います。

f:id:waenavi:20190406021115j:plain

 

ところで、小計の機能は事前に表の並べ替えをする必要があり、そのためには事前に並べ替えが可能な表を作る必要があります。つまり、Excelの集計機能を学習する順番は、リスト形式の表の作成→並べ替え→小計→ピボットテーブルの順となります(参考:【Excel】並べ替えの昇順と降順、優先順位、グループ化、一部分だけの並べ替え - わえなび ワード&エクセル問題集)。

今回は、「小計」を使って行集計をする練習問題を出題します。

目次

1.ピボットテーブルと小計に共通すること

Excelで集計するためには「リスト形式の表」を作らなければなりません。このことは、ピボットテーブルも小計も同じです。

 

2.小計の基本トレーニング

(1)グループの下に集計行を入れるにはグループを作らなければならない

問題

次の表は選手の50音順に並んでいる。各グループの、体力の合計点を求めなさい。

f:id:waenavi:20190405141521j:plain

 

解説

各選手はPグループまたはCグループに属していることがわかります。このグループで合計点を求めようとしています。

f:id:waenavi:20190405142716j:plain

 

そこで、最終的にどのような表になればよいかを考えます。

f:id:waenavi:20190405142719j:plain

 

リストとは別の場所に合計を求めるパターンもありますが、通常は、グループごとにレコードをかためて、小計を求めます。このようにレコードの途中に集計行を入れることを、「行集計」といいます。

f:id:waenavi:20190405142722j:plain

 

行集計はグループごとにするため、必ず並べ替えをします。ただし、グループ化をするための並べ替えなので昇順か降順かは問いません

f:id:waenavi:20190405142727j:plain

 

表を全部選択します。グループで並べ替えます。

f:id:waenavi:20190405142731j:plain

f:id:waenavi:20190405142736j:plain

 

小計のボタンを押します。

f:id:waenavi:20190405142739j:plain

 

この画面は、1番目にグループの基準を見ます。集計の単位(まとまり)のことであり、どのように行を区切るのかを指定します。グループごとに集計をするので、「グループ」になります。

f:id:waenavi:20190405142745j:plain

 

行集計をするときに並べ替えをします。この並べ替えのキーと、グループの基準は同じになります。

f:id:waenavi:20190405142751j:plain

 

次に集計するフィールドを見ます。フィールドとは列のことです。

f:id:waenavi:20190405142754j:plain

 

今回は体力の列で集計をします。総得点のチェックは外します。

f:id:waenavi:20190405142759j:plain

 

最後に集計の方法を見ます。合計を求めます。

f:id:waenavi:20190405142804j:plain

 

グループごとに体力の合計を求めることになります。

f:id:waenavi:20190405142807j:plain

 

これで完成です。

f:id:waenavi:20190405142811j:plain

 

(2)アウトラインの表示と集計行の削除

問題

集計行だけ表示しなさい。また、集計行を削除しなさい。

解説

行集計をすると、画面の左側にアウトラインレベルが表示されます。上にある[1]から[3]はボタンです。[1]のボタンは総合計だけを表示するボタンです。

f:id:waenavi:20190405142815j:plain

 

[2]のボタンは途中の行に挿入された集計行だけが表示されます。

f:id:waenavi:20190405142818j:plain

 

[3]のボタンを押すと元に戻ります。すべてのデータが表示されます。

f:id:waenavi:20190405142822j:plain

 

一番下の集計行や総計も含めて、表を全部選択します。

f:id:waenavi:20190405142825j:plain

 

小計のボタンを押して、集計の設定の画面を出します。すべて削除します。

f:id:waenavi:20190405142829j:plain

 

集計がなくなります。

f:id:waenavi:20190405142832j:plain

 

集計行をなくすには全部選択して、すべて削除します。

f:id:waenavi:20190405142835j:plain

 

(3)2列を集計する

問題

各チームの、体力・学力の平均点を求めなさい。

解説

今度は各チームの、体力・学力の平均点を求めます。表を全部選択します。

f:id:waenavi:20190405160127j:plain

 

4チームありますが、それぞれ平均点を求めますから、まずチームごとに並べ替えます。昇順でも降順でも構いません。

f:id:waenavi:20190405160130j:plain

 

小計のボタンを押して、集計の設定の画面を出します。グループの基準はチームです。

f:id:waenavi:20190405160136j:plain

 

集計するのは体力と学力の2列です。

f:id:waenavi:20190405160142j:plain

 

そして平均を求めます。

f:id:waenavi:20190405160148j:plain

f:id:waenavi:20190405160152j:plain

 

レベル[1]は全体の平均、レベル[2]はそれぞれのチームの平均、レベル[3]は元のデータです。

f:id:waenavi:20190405160156j:plain

f:id:waenavi:20190405160159j:plain

f:id:waenavi:20190405160203j:plain

 

一番下の集計行や小計も含めて全部選択します。小計のボタンを押して、集計の設定の画面を出します。すべて削除します。

f:id:waenavi:20190405160207j:plain 

f:id:waenavi:20190405160212j:plain

 

(4)グループの基準を2つにする

問題

各グループ・各チームの、体力・学力・総得点の平均を求めなさい。

解説

さきほどチームで並べ替えました。チームBとチームLはグループP、チームDとチームGはグループCになっています。

f:id:waenavi:20190405161436j:plain

 

そこで、グループで集計をして、さらに、各チームで集計をします。表を全部選択します。並べ替えもグループとチームで並べ替えます。

f:id:waenavi:20190405161441j:plain

 

小計のボタンを押して、集計の設定の画面を出します。グループの基準はグループです。

f:id:waenavi:20190405161449j:plain

 

集計するのは体力、学力、総得点の3列です。

f:id:waenavi:20190405161455j:plain

 

そして平均を求めます。

f:id:waenavi:20190405161515j:plain

 

とりあえずグループで集計できました。もう一度、小計のボタンを押して、集計の設定の画面を出します。今度は、グループの基準はチームです。

f:id:waenavi:20190405161519j:plain

 

ここで、「現在の小計をすべて置き換える」のチェックを外します

f:id:waenavi:20190405161523j:plain

 

これで、さらに細かく集計することができます。

f:id:waenavi:20190405161528j:plain

 

これに伴ってレベルも1つ増えて、4つになります。

f:id:waenavi:20190405161532j:plain

f:id:waenavi:20190405161535j:plain

f:id:waenavi:20190405161538j:plain

 

(5)元に戻す

問題

最初の状態に戻して、設問(1)から繰り返し練習しなさい。

解説

一番下の集計行や小計も含めて全部選択します。

f:id:waenavi:20190405161542j:plain

 

小計のボタンを押して、集計の設定の画面を出します。すべて削除します。

f:id:waenavi:20190405161547j:plain

f:id:waenavi:20190405161552j:plain

 

選手の昇順に並べ替えれば、最初の状態に戻ります。 

f:id:waenavi:20190405161555j:plain

 

3.合計と平均を両方求める

問題

次のリストは選手の50音順に並んでいる。チーム別で、得点の合計と平均を求めなさい。

f:id:waenavi:20190405233922j:plain

 

解説

チームで並べ替えます。昇順でも降順でも構いません。

f:id:waenavi:20190405234853j:plain

 

小計のボタンを押して、集計の設定の画面を出します。グループの基準はチームです。集計するのは得点の列です。そして合計を求めます。

f:id:waenavi:20190405234858j:plain

 

さらに平均を求めるときは、もう一度小計のボタンを押して、集計の設定の画面を出します。

f:id:waenavi:20190405234903j:plain

 

2つ目の集計をするので、「現在の小計をすべて置き換える」のチェックを外します。

f:id:waenavi:20190405234908j:plain

f:id:waenavi:20190405234912j:plain

 

さらに、グループの基準を変えたり、集計方法を変えて2つ目の集計をするときは、「現在の小計をすべて置き換える」のチェックを外します。

f:id:waenavi:20190405234917j:plain

 

現在の集計を取り消して、新たな集計をする場合にはチェックを入れます。

f:id:waenavi:20190405234920j:plain

 

4.○○別、○○別集計

(1)グループの基準に大小関係(包含関係)がある場合

問題

グループ別・チーム別で得点の平均を求めなさい。

f:id:waenavi:20190405233922j:plain

 

解説

いったん、さきほどの集計を削除します。

f:id:waenavi:20190405235700j:plain

 

グループ別・チーム別で集計するときは、並べ替えもグループとチームで並べ替えます。

f:id:waenavi:20190405235703j:plain

 

小計のボタンを押して、集計の設定の画面を出します。グループの基準はグループです。得点の平均です。

f:id:waenavi:20190405235708j:plain

 

とりあえずグループで集計できました。もう一度、小計のボタンを押して、集計の設定の画面を出します。今度は、グループの基準はチームです。

f:id:waenavi:20190405235712j:plain

 

ここで、「現在の小計をすべて置き換える」のチェックを外します。

f:id:waenavi:20190405235716j:plain

f:id:waenavi:20190405235721j:plain

 

グループの中にチームがあります。

f:id:waenavi:20190405235724j:plain

 

このように含まれる関係(大小関係・包含関係)にある場合は、大きい分類から順に集計をします。

f:id:waenavi:20190405235727j:plain

 

並べ替えは大きいグループから設定をします。

f:id:waenavi:20190405235732j:plain

 

集計をするときも大きいグループから順に設定します。

f:id:waenavi:20190405235735j:plain

 

(2)グループの基準に大小関係(包含関係)がない場合

問題

チーム別・左右別で得点の合計を求めなさい。また、左右別・チーム別で得点の合計を求めなさい。

f:id:waenavi:20190405233922j:plain

 

解説

チームと利き手はまったく関連性のないグループで、大小関係(包含関係)はありません。このように含まれる関係にない場合、優先順位を自分で決めて集計しなければなりません。

f:id:waenavi:20190406001301j:plain

 

まずは、チームを優先して集計してみます。並べ替えはチームが優先です。

f:id:waenavi:20190406001318j:plain

 

小計もチームの合計が先です。

f:id:waenavi:20190406001322j:plain

 

利き手の合計を求めます。

f:id:waenavi:20190406001327j:plain

 

これを、「チーム別、左右別集計」といいます。大きいグループから順に「~別、~別集計」といいます。

f:id:waenavi:20190406001331j:plain

f:id:waenavi:20190406001335j:plain

 

いったん集計を削除します。

f:id:waenavi:20190406001339j:plain

 

今度は、利き手を優先して集計します。並べ替えの優先順位を逆にします。

f:id:waenavi:20190406001343j:plain

 

小計も利き手の合計が先です。

f:id:waenavi:20190406001348j:plain

 

チームの合計を求めます。

f:id:waenavi:20190406001353j:plain

 

これを、「左右別、チーム別集計」といいます。「~別、~別」というのは集計の優先順位を表しています。

f:id:waenavi:20190406001357j:plain

 

集計の優先順位を変えると、集計結果の形が変わります。

f:id:waenavi:20190406001401j:plain

 

チームを優先すると、まずチームごとに集計され、チーム内で左右それぞれ合計を求めることになります。

f:id:waenavi:20190406001305j:plain

 

チームの総合計はありますが、左右それぞれの総合計はありません。

f:id:waenavi:20190406001308j:plain

 

左右を優先すると、まず左右それぞれ集計され、その中でチーム別に集計されます。

f:id:waenavi:20190406001312j:plain

 

左右の総合計はありますが、チームの総合計はありません。

f:id:waenavi:20190406001315j:plain 

 

ところで、チームと利き手のように大小関係(包含関係)がない場合、集計方法は2つあります。

f:id:waenavi:20190406001404j:plain

 

行集計とクロス集計です。

f:id:waenavi:20190406001408j:plain

 

優先順位を決めて集計する場合、行集計(小計)を使います。大中小と、3つ以上の分類があっても集計ができます。

f:id:waenavi:20190406001411j:plain

 

ただし、最優先でない分類は2箇所以上に分かれるので総合計を出すことはできません。

f:id:waenavi:20190406001414j:plain

 

優先順位を決めずに、項目を縦横に並べて集計する方法があります。これを「クロス集計」といいます。この場合、両方とも合計を求めることができます。

f:id:waenavi:20190406001421j:plain

 

しかし、縦と横しかないので、原則として3つ以上のグループのクロス集計をすることはできません。

f:id:waenavi:20190406001425j:plain

 


解説は以上です。


付録1 サンプルのファイルはこちら

わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお使いください。7-19、7-20のサンプルファイル(ZIP)です。

 

付録2 動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-19、7-20 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。

ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ