リスト形式で作成された集計表を「集計」する方法を比較します
リスト形式で作成された「集計表」を集計・合計する方法は何種類かあります。
それぞれの方法に特徴があり、状況によって使い分ける必要があります。
- アウトライン
- フィルター機能（テーブル化）
- ピボットテーブル
種類の異なる「DATA」を一つのリストにする
リスト形式の集計表を作る際に、データの種類別に集計表を作成する場合が多いと思います。
例えば、毎月の業者への支払いの「銀行の振込一覧表」作成のためのデータであれば業者別に集計した方が管理しやすくなります。
しかし、この方法では「調査・分析」する際には不都合が生じます。
調査分析するためには、その「目的に沿った」集計表を作ることになると思います。
そして問題なのがこの「目的に沿った」集計表は、言い換えれば「その目的のためだけの」集計表になってしまうことです。
さらに、一度分析すると違う角度からの分析も見たくなり次々と「集計表」を作成することになってしまいます。
Excelは「調査・分析」資料の作成が得意
Excelの本来の目的は、単なる「集計」ではなく、「調査・分析」用の資料を作ることです。
古いバージョンのExcelでは使いにくかった各種の機能が、近年の新しいExcelでは操作方法が改善され、中には「Access」でなければできなかった作業も「Excel」で簡単に使えるようになりました。
異なる種類（分類）のデータを一元管理する
データの種類別に分けることなく、全てのデータを一つの集計表にしたもの。
ツアー会社の１０月の売上一覧
集計表を種類（分類）別に分ける
一つの集計表にすべてのデータを集約しているので、項目別に「５種類の集計表」を作成することができます。
- 「支店」別の売上合計
- 「温泉地」別の利用状況
- 「宿泊先」別の利用状況
- 「顧客区分」別の利用状況
- 「月」別（※ 今回は「10月」だけになっているが、月別に分ける必要はありません）
さらに「クロス集計」や「複合集計」などの集計方法をすれば、種類はさらに増えていきます。
「支店」ごとの「宿泊先名」別（複合集計）
「宿泊先名」毎の「支店」別（複合集計）
「支店」ごとの「顧客区分」別（複合集計）
「支店」別の「日付」ごとの「顧客区分」別（複合・クロス集計）
このようにデータを分類せずに、平打ち（一元的に）入力して「一つの表」としておけば後で様々な「分析・調査」資料が作成できます。
請求書や納品書も「業者毎」や「月別」などに分ける必要はありません、極端に言えば「年度」も気にせず一元化すれば良いのです。
「年度」を区分せずに「一元化」しておけば「年度比較」も簡単にできます。
「先月はどうだったか」だけではなく「前年同月はどうだったか」が分析できるのです。
データの中には「毎年同じ月が同様の傾向」を示すものが多いので、このような場合は「前月比」ではなく「前年比」での分析が重要になります。
そのほかにも「前年同月比」や「前年同日比」だけではなく「四半期ごと」の分析も簡単にできます。
「３年間の同月（同日）推移」なども簡単に作成できます。
１．「アウトライン」の機能を使って集計する
アウトラインは以前からある簡単にできる集計方法です。
この機能を使うためにはデータを集計したい項目をキーにして並べ替える必要がありデータの数が多くなると操作が難しくなります。
この機能の欠点は、画面上で確認する際には操作が簡単で便利なのですが、プリントアウトして「資料」を作成する際には「可視セルにジャンプする」する必要があるなど少し面倒です。 ※ 最近のバージョンでは改善されました
そして「グラフ」を作成するコマンドは用意されていないので、グラフ作成は通常の操作が必要です。
１．アウトラインを使って集計する
アウトラインを使って集計するためには、集計のキーとなる項目を並べ替えておく必要があります。
「支店ごとの宿泊先名別」に並べ替えます
- 並べ替える部分を範囲選択します
- 「データ」タブを選択
- 「並べ替え」をクリック
- 最優先されるキーで「支店名」を選択
- 「レベルの追加」をクリック
- 次に優先されるキーで「宿泊先名」を選択
「支店別」の小計を設定します
アウトラインの機能の「小計」を使って集計します。
- 「表」の中のどこか1カ所を選択します
- 「データ」タブの「アウトライン」をクリック
- アウトラインの「小計」をクリック
「支店名」で集計します
- グループの基準で「支店名」を選択します
- 集計方法で「合計」を選択します
- 集計するフィールドで「合計金額」にチェックを入れます
複合条件として「宿泊先名」を設定します
レベルを切り替えることで複数の条件で「小計」を作成することができます。
- グループの基準を「宿泊先名」にする
- 集計方法を「合計」にする
- 集計するフィールドを「合計金額」にする
- 「現在の小計をすべて置き換える」のチェックを外す
レベルを変更して集計方法を変更することができます
「レベル３」で表示
「宿泊先別」と「支店別」と「総計」で集計されます。
「レベル２」で表示
「支店別」と「総計」で集計されます。
「レベル１」で表示
「総計」で集計されます。
このように、比較的簡単な操作で「集計」はできますが、「分析資料」にはなりません。
２．表を「テーブル化」し、「フィルター」機能を使って集計する
表をテーブル化すると「フィルター」機能を使って簡単に集計することができます。
- 「表」のどこか１カ所のセルを選択状態にする
- 「挿入」タブを選択
- 「テーブル」をクリック
- 「先頭行をテーブルの見出しとして使用する」にチェックを入れる
- 「OK」をクリック
テーブル化されました
表がテーブル化され、項目名にフィルターボタン「▼」が表示されます。
「フィルター」の機能を使って、集計します
集計したいキーとなる項目の「ラジオボタン「▼」」をクリックするだけでダイアログが表示され目的に応じた集計ができます。
- 集計したいフィールド（項目）のラジオボタン「▼」をクリック
- 「すべて選択」のチェックを外す
- 集計したいデータ（要素）を選択
「広島支店」で集計されました
集計行を追加します
コマンドにチェックを入れるだけで、フィルター機能で集計されたリストの最下行に「集計行」が追加されます。
この「集計行」は、リストの行数が変わっても自動的に最下行に表示されます。
- 「テーブルツール」を選択します（※ 表（テーブル内）のどこかを選択した状態にしておく）
- 「デザイン」タブを選択します
- 「集計行」にチェックを入れる
- 集計を追加したい部分にカーソルを合わせると出現するラジオボタン「▼」をクリック
- 集計方法を選択する（※ 集計方法は合計以外にも、色々は方法が用意されています）
複数の「フィルター」を適用する
複数の「フィルター」を使うことで、複合集計ができます。
- 追加で適用したいフィルターを選択
- 集計したいデータを選択します
※ フィルターが適用されているラジオボタン「▼」のアイコンは形が変わります
このようにテーブル化した上でフィルター機能を使って集計すれば、簡単に集計業務を行うことができます。
しかし、このフィルターでは個々の合計は分かりますが全体の状況を知ることは難しく分析資料としては情報不足です。
そして、このフィルター機能を使った集計は自身の「確認作業」としてはとても便利な機能ですが、他の人に見せるための資料としてはあまり適していません。
３．「ピボットテーブル」の機能を使って「分析資料」を作成する
「ピボットテーブル」を使えば単なる個別集計ではなく、全体の状況が把握できる集計資料を作ることができます。
集計結果の数字をクリックすると「ドリルダウン」の機能が働いて、集計内容の詳細を見ることができます。
さらに集計表に連動した「グラフ」を作成することができるので「他の人に見せる」資料を作成することができます。
- 表の中のどこか１カ所のセルを選択状態にする
- 「挿入」タブを選択
- 「ピボットテーブル」をクリック
- 「OK」をクリック
ピボットテーブルが作成されました
ピボットテーブルは「別シート」に作成されるので、元のデータには影響しません。
支店ごとの宿泊先名別（複合集計）をします
「支店名」と「宿泊先名」の２つの項目を行のボックスにドラッグするだけで使って複合集計ができます。
- 「支店名」を行のボックスにドラッグします
- 「宿泊先名」を行のボックスの「支店名」の下にドラッグします
- 「合計金額」を「∑ 値」のボックスにドラッグします
数値に「桁区切り」を設定します
「値フィールド」の設定をすれば、集計表の表示形式を変更することができます。
- 表の中のどこか１カ所のセルで「右クリック」する
- 「値フィールドの設定」をクリック
- 「表示形式」をクリック
- 分類で「数値」を選択
- 「桁区切り（，）を使用する」にチェックを入れる
桁区切りが設定されました
１カ所を設定すれば、テーブル全体に桁区切りが設定されます。
グラフを作成して、資料を「ビジュアル化」します
ピボットテーブルを作成すると、そのテーブルに連動したグラフが自動作成されます。
縦棒グラフを作成します
- 「表」内のどこか1カ所を選択状態にする
- 「ピボットテーブルツール」を選択
- 「分析」タブを選択
- 「ピボットグラフ」をクリック
- 「縦棒」を選択
- 「集合縦棒」を選択
グラフが作成されました
グラフはピボットテーブルと同一シートに作成されます、「グラフの移動」コマンドで別シートに移動させることができます。
グラフを別シートに移動します
- グラフを選択します
- 「ピボットグラフツール」を選択
- 「デザイン」タブを選択
- 「グラフの移動」をクリック
- 「新しいシート」にチェックを入れる
※ 「オブジェクト」を選択すると、同一シートにオブジェクトとしてグラフが作成されます
新しいシート「グラフ1」が作成されました
「グラフの移動」を使えば、別シートに移動することができます。
グラフを、支店別の日付ごとの顧客区分別（複合・クロス集計）に変更します
- 「日付」を「支店名の下」にドラッグします
- 「顧客区分」を「凡例」のボックスにドラッグします
グラフの種類を「積み上げ縦棒グラフ」に変更します
- グラフを選択します
- 「ピボットグラフツール」を選択
- 「デザイン」タブを選択
- 「グラフの種類を変更」をクリック
- 「縦棒」を選択
- 「積み上げ縦棒」を選択
「積み上げ縦棒」グラフに変更され、情報が見やすくなりました
「顧客区分」が１つの「棒」に積みあがります。
簡単な「ドラッグ」操作だけで、様々な分析資料（グラフ）を作成できます
「軸」と「凡例」のボックスにフィールド（項目）を配置することでクロス集計ができます。
「支店名」と「顧客区分」のクロス集計
年度の「2期比較」の資料も簡単に作れます
日付を「複合条件」に設定する必要がある「２期比較」は、グラフ作成ではかなりハードルの高い作業になりますが、ピボットテーブルを使えば簡単にできます。
「日付」をボックスにドラッグすると、自動的に「グループ化」されます
※ 特殊なグループ化をしたい場合には、設定が必要です
「支店名」と「年度」のクロス集計での「２期比較」
「支店名」と「年度」で複合集計し、さらに「顧客区分」でクロス集計
「支店名」、「年度」、「顧客区分」の３つの要素で集計されます。
作成される「表」には様々な機能があります
ピボットテーブルで作成した集計表（結果）のデータをクリックするだけでドリルダウンの機能でそのデータの詳細な内容が表示されます。
- 表示されている「集計データ」をクリックすれば、そのデータの「詳細（集計前のデータ）」が別シートで作成されます
- 「フィルターボタン」を使うことができます
- 「折り畳みボタン」を使うことができます
このように「ピボットテーブル」を使えば、複雑な「分析作業」を簡単な操作で実現できます。
同じような「分析作業」を関数を使ってやろうとすれば大変な作業になってしまいます。
数式（関数）を使う方法だと、SUMIFS関数で集計し、様々な「テーブル（表）」を作成しそれらを組み合わせて「集計表」を造らざるを得ません。
数式（関数）を使って複合集計やクロス集計のグラフを作成するとなると、とても複雑な作業となります。
ピボットテーブルを使うことで、あらかじめ資料を用意（作成）せずにその場で即座に資料を作成して「プロジェクター」等でプレゼンすることができます。
最大のメリットは「元データ」が単純な一枚の表なので、データの管理がとても簡単なことです、「月」や「年度」で表を作り変える必要がなく、単純にデータを追加していくだけです。
複数の年度を一つの集計表に集約している場合も、「タイムライン」の機能を使えば「期間」を設定するだけで必要な期間の資料を作成できます。
「アウトライン」を使った集計（小計）では、データを並べ替える必要があり元の表の形が変わってしまいます。
そして、並べ替えが必要なことで「長期間（複数年次）」のデータを一元管理するのが難しい面があります。
「ピボットテーブル」は「アウトライン」のようにあらかじめ並べ替えられたレコードの種類ごとの「小計」を求めるのではなく、条件にあったデータの合計等を計算しています。
従来は「VBA」や「GETPIBOTDATA関数」等を駆使しなければならなかった作業を、「ドラッグ」操作だけで可能にした優れた機能です。
集計作業をする際には「ピボットテーブル」を使ってみてください。
- リスト形式で作成された集計表を「集計」する方法を比較します
