Excel 2013　集計・アウトライン・統合

Excelでは、列方向のセルに見出しをつけ、行方向のセルにその見出しに相当するデータを入力したリストを扱うのが基本です。その場合、各列を「フィールド」といい、その見出しを「フィールド名」といいます。
このフィールドを利用してさまざまな集計を行うことができます。

サンプルデータtotal_es.xlsx「集計・アウトライン・統合(演習).xlsx」を開きます。

グループごとに値を集計して挿入する集計

指定したフィールドごとに小計や平均、またデータの個数などを表示させることができます。
ここではサンプルデータのシート「データの集計」の商品コード別の販売台数と売上金額の合計を求めます。
シート「データの集計」を選択します。

(1) 集計を行う項目での並べ替え

集計を行う場合は集計する項目でデータを昇順に並べ替えておく必要があります。
ここでは「商品CD」をキーに昇順で並べ替えます。

1. セルF5(商品CD)に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「昇順」ボタンに移動してEnter キーを押します。リストが「商品CD」をキーに昇順で並べ替えられます。

＊アクセスキー： Alt A S A

【ワンポイント】選択するセルは「商品CD」の列、F5からF152のどこでも構いません。

(2) 集計の実行

次に「商品CD」を基準に「販売台数」と「売上金額」の合計を求めます。

1. セルA5「売上日」に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「小計」に移動してEnter キーを押します。「集計の設定」ダイアログボックスが開きます。

＊アクセスキー： Alt A B

5. 「集計するフィールド」リストが選択されているので「売上金額」にチェックが入っていることを確認します。
6. 上方向キーを押して「販売台数」に移動し、Space キーを押してチェックをつけます。
7. Shift + Tab キーを押して「集計の方法」コンボボックスに移動し、「合計」が選択されていることを確認します。
8. Shift + Tab キーを押して「グループの基準」コンボボックスに移動し、下方向キーを押して「商品CD」に移動してEnter キーを押します。
9. Tab キーを押して「OK」ボタンに移動してEnter キーを押します。商品CD別の「販売台数」と「売上金額」の小計行が追加され、最終行に総計が表示されます。

【ワンポイント】「販売台数」にSpace キーを押しただけではチェックがつかない場合は、Ctrl + Space キーを押すことでチェックをつけることができます。

(注1) 小計行には、F列に「C1020150 集計」などの見出しが、I列に「=SUBTOTAL(9,I6:I17)」、J列に「=SUBTOTAL(9,J6:J17)」などの数式が表示されます。

(注2) 総計行には、F列に「総計」の見出しが、I列に「=SUBTOTAL(9,I6:I163)」、J列に「=SUBTOTAL(9,J6:J163)」の数式が自動的に入力されます。

集計の解除

データの集計は以下の手順で解除することができます。
ここではサンプルデータのシート「データの集計の解除」に設定された集計を解除します。
シート「データの集計の解除」を選択します。

1. セルA3「売上日」を選択します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「小計」に移動してEnter キーを押します。「集計の設定」ダイアログボックスが開きます。
5. Tab キーを押して「すべて削除」ボタンに移動してEnter キーを押します。集計が解除され、元のリストに戻ります。

【ワンポイント】選択するセルはA3からJ163のどこでも構いません。

アウトライン機能とは、リスト上のデータをグループごとにまとめて必要な項目だけを表示する機能です。
例えば、4月から9月のデータは表示せず「上期計」だけを表示させたり、明細は省略して「合計」だけを表示させたりする場合に利用します。
アウトラインは表示レベルを8段階まで作成でき、作成方法には「自動作成」と「手動作成」がありますが、通常は「自動作成」を使用します。
シート「アウトラインの設定」を選択します。

(1) アウトラインの自動作成

アウトラインの自動作成を行うと、リスト内のデータを集計した行と列とその参照データが自動的にグループ化されます。
アウトラインの自動作成は以下の手順で行います。
ここではサンプルデータのシート「アウトラインの設定」のセルA3からP29までのリストにアウトラインを自動作成します。

1. セルA3「商品名」に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「グループ化」に移動してEnter キーを押します。「グループ化」のメニューが開きます。
5. 下方向キーを押して「アウトラインの自動作成」に移動してEnter キーを押します。自動的にアウトラインが作成されます。また、アウトラインを作成すると、画面のグループの上または左に、アウトライン記号が表示されます。

＊アクセスキー： Alt A G A

【重要】アウトラインを作成する際は、結合されたセルは選択しないようにします。また、セルはすべて表示させておきます。

【ワンポイント】選択するセルはセルA3からP29までのリスト内のどこでも構いません。

(注1) アウトラインを設定したリストには、各グループを1レベルとして、最大8段階のレベルのアウトラインが作成されます。レベルはもっとも上位のものが1で、数字が大きくなるほど下位になります。サンプルデータのリストでは、「合計」がレベル1で、「上期計」「下期計」がレベル2、各月の明細がレベル3になります。

(注2) アウトライン記号は、アウトラインを作成したワークシートの詳細データの表示/非表示を切り替える際に使用する記号です。+(プラス)や-(マイナス)のアイコン、また、1、2、3などのレベルを表す数字で表示されます。

(2) 詳細列の非表示

アウトラインを作成したリストでは、月別や商品別などの詳細データを表示せず、合計だけを表示させることができます。
ここではサンプルデータのシート「アウトラインの設定」で「上期計」「下期計」「合計」だけを表示させます。

1. セルB3「4月」に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「詳細を表示しない」ボタンに移動してEnter キーを押します。4月から9月までのデータが非表示になります。

＊アクセスキー： Alt A H

5. セルI3「10月」に移動して、上記2.から4.の手順で10月から3月までの詳細データを非表示にします。

【ワンポイント1】選択するセルはセルB3からG3までのリスト内のどこでも構いません。

【ワンポイント2】この後、セルA3からP3までを範囲選択して上記2～4の操作をさらに実行すると、P列の「合計」だけが表示されます。

(3) 詳細行の非表示

アウトラインを作成したリストでは、列だけでなく行単位で詳細データの非表示を行うことができます。
ここではサンプルデータのシート「アウトラインの設定」で商品別の詳細データは表示せず、品種別の合計だけを表示させます。

1. セルA3からA29までを範囲選択します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「詳細を表示しない」ボタンに移動してEnter キーを押します。品種別の合計行だけが表示されます。

＊アクセスキー： Alt A H

【ワンポイント】この後、上記1～4の操作をさらに実行すると、19行目の「書籍合計」と29行目の「ビデオ合計」だけが表示されます。

詳細データの再表示とアウトラインの解除

(1) 詳細データの再表示

非表示にした詳細データは以下の手順で再度表示させることができます。
ここではサンプルデータのシート「アウトラインの解除」の4月から9月のデータを再表示させます。

1. セルH3に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「詳細データの表示」ボタンに移動してEnter キーを押します。4月から9月のデータが再表示されます。

＊アクセスキー： Alt A J

(2) アウトラインの解除

設定したアウトラインは以下の手順で解除できます。
ここではサンプルデータのシート「アウトラインの解除」に設定されたアウトラインを解除します。

1. セルA3「商品名」に移動します。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「グループ解除」に移動してEnter キーを押します。「グループ解除」のメニューが開きます。
5. 下方向キーを押して「アウトラインのクリア」に移動してEnter キーを押します。アウトラインが解除され、非表示になっていた詳細データが表示されます。

【ワンポイント】選択するセルはセルA3からP29までのリスト内のどこでも構いません。

複数のシートの値を1つのシートに集計する統合

統合機能を使うと、複数のブックやワークシートに作成されている項目の数や並びが一致しないリストを集計できます。
ここではサンプルデータの4枚のシート「統合(本社)」「統合(大阪支社)」「統合(名古屋営業所)」「統合(広島出張所)」に入っているそれぞれフォーマットの異なる経費一覧表のリストを「統合（全国集計)」にまとめます。
まず、シートを切り替えながら各シートの内容を確認します。
その際、各シートのデータ範囲、つまり見出しから合計までのセル範囲を記録しておきます。それぞれのシートのデータ範囲は以下の通りです。

本社： A4からF10
大阪： A6からG10
名古屋： A5からG8
広島： A9からF11

内容を確認したら、以下の手順でデータを集計するための統合の設定を行います。
シート「統合(全国集計)」を選択します。このシートが集計先になります。

1. セルA4に移動します。ここに集計結果を表示させます。
2. Alt キーを押してリボンに移動します。
3. 左右方向キーを押して「データ」タブに移動します。
4. Tab キーを押して「統合」に移動してEnter キーを押します。「統合の設定」ダイアログボックスが開きます。

＊アクセスキー： Alt A N

5. Tab キーを押して「集計の方法」が「合計」になっていることを確認します。
6. Tab キーを押して「統合元範囲」エディットボックスに移動して『統合(本社)!A4:F10』と入力します。
7. Tab キーを押して「追加」ボタンに移動してEnter キーを押します。「統合元リスト」に本社のデータが追加されます。
8. 「統合元範囲」エディットボックスが選択されているので、続けて『統合(大阪支社)!A6:G10』と入力します。
9. Tab キーを押して「追加」ボタンに移動してEnter キーを押します。「統合元リスト」に大阪支社のデータが追加されます。
10. 「統合元範囲」エディットボックスが選択されているので、続けて『統合(名古屋営業所)!A5:G8』と入力します。
11. Tab キーを押して「追加」ボタンに移動してEnter キーを押します。「統合元リスト」に名古屋営業所のデータが追加されます。
12. 「統合元範囲」エディットボックスが選択されているので、続けて『統合(広島出張所)!A9:F11』と入力します。
13. Tab キーを押して「追加」ボタンに移動してEnter キーを押します。「統合元リスト」に広島出張所のデータが追加されます。
14. Tabキーを押して「統合の基準」グループに移動し「上端行」と「左端列」チェックボックスに移動してSpace キーを押してチェックをつけます。
15. Tab キーを押して「OK」ボタンに移動してEnter キーを押します。シート「統合(全国集計)」に行列の項目名に従って本社、大阪支社、名古屋営業所、広島出張所のデータが集計されたリストが作成されます。

【重要】この処理を行うためには、各リストの見出しの順序は異なっていても構いませんが、見出しの名称は一致していることが必要です。例えば、本社では「旅費交通費」となっているのに大阪支社では「交通費」となっている場合は一つのデータとしては集計されません。