Excelでは、列方向のセルに見出しをつけ、行方向のセルにその見出しに相当するデータを入力したリストを扱うのが基本です。その場合、各列を「フィールド」といい、その見出しを「フィールド名」といいます。
サンプルデータtotal_es.xlsx「データの集計・アウトライン・統合(演習)」を開きます。
グループごとに値を集計して挿入する集計
指定したフィールドごとに小計や平均、またデータの個数などを表示させることができます。
シート「データの集計」を選択します。
(1) 集計の手順
ここでは、商品コード別の販売台数と売上金額の合計を求めます。
集計を行う前に「商品CD」をキーに並べ替えを行います。
- 1. セルF3(商品CD)を選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「データ」タブに移動します。
- 4. Tab キーを押して「昇順」を選択してEnter キーを押します。リストが「商品CD」をキーに昇順で並べ替えられます。
＊ アクセスキー： Alt A A
【ワンポイント】 選択するセルは「商品CD」の列、F3からF150のどこでも構いません。
次に「商品CD」を基準に「販売台数」と「売上金額」の合計を求めます。
- 1. セルA3「売上日」を選択します。
- 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,I4:I5)」、J列に「=SUBTOTAL(9,J4:J5)」などの数式が表示されます。
(注2) また総計行には、F列に「総計」の見出しが、I列に「=SUBTOTAL(9,I4:I293)」、J列に「=SUBTOTAL(9,J4:J293)」の数式が自動的に入ります。
(2) 集計の解除
データの集計は以下の手順で解除することができます。
- 1. セルA3「売上日」を選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「データ」タブに移動します。
- 4. Tab キーを押して「小計」を選択してEnter キーを押します。「集計の設定」ダイアログ ボックスが開きます。
- 5. Tab キーを押して「すべて削除」ボタンを選択してEnter キーを押します。集計が解除され、元のリストに戻ります。
【ワンポイント】 選択するセルはA3からJ163のどこでも構いません。
グループごとに値を集計してまとめるアウトライン
アウトライン機能とは、リスト上のデータをグループごとにまとめて必要な項目だけを表示する機能です。例えば、4月から9月のデータは表示せず「上期計」だけを表示させたり、明細は省略して「合計」だけを表示させたりする場合に利用します。
アウトラインは表示レベルを8段階まで作成でき、作成方法には「自動作成」と「手動作成」がありますが、通常は「自動作成」を使用します。
シート「アウトライン」を選択します。
(1) アウトラインの自動作成
アウトラインの自動作成を行うと、リスト内のデータを集計した行と列とその参照データが自動的にグループ化されます。
自動作成は以下の手順で行います。
- 1. セルA3「商品名」を選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「データ」タブに移動します。
- 4. Tab キーを押して「グループ化」を選択してEnter キーを押します。下方向にメニューが表示されます。
- 5. 下方向 キーを押して「アウトラインの自動作成」を選択してEnter キーを押します。自動的にアウトラインが作成されます。また、アウトラインを作成すると、画面のグループの上または左に、アウトライン記号が表示されます。
【重要】 アウトラインを作成する際は、結合されたセルは選択しないようにします。また、セルはすべて表示させておきます。
【ワンポイント】 選択するセルはセル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月」を選択して、同じ手順で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行目の「ビデオ合計」だけが表示されます。
(4) 詳細データの再表示
非表示にした詳細データは以下の手順で再度表示させることができます。ここでは、4月から9月のデータを再表示させます。
- 1. セルH3を選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「データ」タブに移動します。
- 4. Tab キーを押して「詳細データの表示」ボタンを選択してEnter キーを押します。4月から9月のデータが再表示されます。
＊ アクセスキー： Alt A J
(5) アウトラインの解除
設定したアウトラインは以下の手順で解除できます。
- 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. Tab キーを押して「統合元範囲」エディット ボックスに移動して『統合(大阪支社)!A6:G10』と入力します。
- 9. Tab キーを押して「追加」を選択してEnter キーを押します。「統合元リスト」に大阪支社のデータが追加されます。
- 10. Tab キーを押して「統合元範囲」エディット ボックスに移動して『統合(名古屋営業所)!A5:G8』と入力します。
- 11. Tab キーを押して「追加」を選択してEnter キーを押します。「統合元リスト」に名古屋営業所のデータが追加されます。
- 12. Tab キーを押して「統合元範囲」エディット ボックスに移動して『統合(広島出張所)!A9:F11』と入力します。
- 13. Tab キーを押して「追加」を選択してEnter キーを押します。「統合元リスト」に広島出張所のデータが追加されます。
- 14. Tabキーを押して「統合の基準」グループに移動して「上端行」と「左端列」チェック ボックスにチェックをつけます。
- 15. 「OK」ボタンを押します。シート「統合(全国集計)」に行列の項目名に従って本社、大阪支社、名古屋営業所、広島出張所のデータが集計されたリストが作成されます。
【重要】 この処理を行うためには、各リストの見出しの順序は異なっていても構いませんが、見出しの名称は一致していることが必要です。例えば、本社では「旅費交通費」となっているのに大阪支社では「交通費」となっている場合は一つのデータとしては集計されません。
操作の結果はサンプルデータtotal_kk.xlsx「データの集計・アウトライン・統合(結果)」をご覧ください。