エクセルの集計術：4つの機能（小計、ピボットテーブル、統合、関数）
Mana Kotani
エクセルは集計作業において不可欠なツールです。ただし、集計に関してはさまざまな方法が存在し、どの機能を選択すればよいか悩むこともあります。
頻繁に使用されるSUM関数は慣れているため、多用されがちですが、他の機能を活用することで格段に効率的に作業を進めることができることもあります。
エクセルでの集計作業において、主に以下の4つの機能が活用されます。
- 小計
- ピボットテーブル
- 統合
- 関数
これらの機能はそれぞれ特有の特性を持っており、全てを理解することで様々なシーンで活用でき、柔軟に対応することができ、非常に便利です。
それぞれの特性を理解し、使いやすい方法を見つけてみましょう。
集計術1：小計
「小計」はエクセルの機能の一つで、表を使用してグループごとに合計を求める際に便利な機能です。
通常、同じく合計を求める場合には、多くの人がSUM関数を使用します。ただし、グループが多いとその設定が煩雑に感じられることもあります。
ここで小計が役立ちます。小計を使用すれば、簡単に集計を行うことができます。では、具体的な使い方について詳しく見ていきましょう。
日付ごとの販売金額を求める場合
- 該当する一覧を選択し、「データ」の「小計」をクリックします。
- 「集計の設定」ダイアログボックスが表示されるので、以下の通りに選択しOKをクリックします。
- グループの基準：日付
- 集計の方法：合計
- 集計するフィールド：金額（チェックボックスにチェックを入れる）
これで、日付ごとの集計が表示されます。
左端の「1」をクリックすると、総計だけが表示されます。
左端の「2」をクリックすると、日付ごとの集計と総計だけが表示されます。
購入商品ごとの販売金額を求める場合
集計したい項目がバラバラに配置されている場合、商品ごとに整理する必要があります。まず、集計したい表の範囲を選択します。
「データ」の「並べ替え」をクリックし、表示された並べ替えダイアログボックスの「優先されるキー」を「購入商品」と指定します。
「並べ替え」ダイアログボックスが表示されるので、「並べ替えのキー」は「セルの値」、「順序」は「昇順」と指定したら「OK」ボタンをクリックします。
購入商品ごとに並べ替えられるので「日付ごとの販売金額を求める場合」と同じ手順で購入商品ごとに集計します。
これで、購入商品ごとの集計が表示されます。
小計を解除したい場合
小計を解除したい場合は、該当一覧の範囲を選択し、「データ」→「小計」と進み、表示される「集計の設定」ダイアログボックスの左下にある「すべてを削除」をクリックします。
すると、左端の数字と集計が消え、元の表に戻ります。
集計術2：ピボットテーブル
ピボットテーブルは、エクセルの強力な機能で、膨大なデータの集計や分析が可能です。元データを変更することなく、容易に集計や分析が行えます。
また、元データが修正された場合も、ピボットテーブルに反映することができるので、同じデータを様々な視点から分析できます。
ただし、ピボットテーブルを使用する際には、次の2つの注意点にもとづいて元データを整える必要があります。
- 1行目にはタイトル（ヘッダー）を表示すること
- 2行目以降には空白など不要なセルを置かないこと
元データを整えたら、ピボットテーブルを設置していきます。
ピボットテーブルの設置方法
10月の購入商品ごと/顧客ごとの販売金額を確認できる表を作成します。
「挿入」タブの「ピボットテーブル」を選択し、「テーブルまたは範囲内から」をクリックします。
表示されたダイアログボックス内の「テーブル / 範囲」欄には元データの範囲を指定します。
ピボットテーブルを配置する場所は「既存のワークシート」内とし、テーブルを表示する起点を指定してOKをクリックします。
表示された「ピボットテーブルのフィールド」内で、表示されている項目を選択します。集計したい項目にチェックを入れると、下の「行」や「値」に振り分けられます。
これで、ピボットテーブルが表示されました。
集計術3：統合
統合は、エクセルの機能で、異なるワークシートデータのセルの位置や項目の並び順が異なる場合でも、表の見出しの内容を基に集計することができます。集計対象の表は同じ形である必要はありません。
統合を利用する前に、集計する表の見出しが「行」にあるのか「列」にあるのかを確認し、見出しの場所も確認しておきましょう。
統合の使用方法
8月、9月、10月とシートを分けて計算された、購入商品の売上を集計して一覧表を作成します。
集計を表示するシートにセルを置き、「データ」タブから「統合」を選択します。
「統合の設定」ダイアログボックスの中で、「集計の方法」で「合計」を選択し、「統合元範囲」では、各月のシートを開いて集計する表全体を選択します。選択できたら「追加」をクリックし、「統合元」に表示されたことを確認します。
「統合元」欄に集計する全てのシートが表示されたことを確認し、「統合の基準」にチェックを入れます。今回は列も行も基準とするため、両方にチェックを入れて「OK」をクリックします。
これで、集計表が表示されました。
集計術4：関数
関数は定型の計算を行うための数式であり、複数のセルの合計値を集計したり、条件に合う項目を取り出したりできます。手間のかかる計算を簡単に行うための機能です。
関数を使用して集計表を作成すると、さまざまな条件の計算が可能であり、項目を入力するたびに自動で計算されるため手間が省けます。以下はいくつかの代表的な関数の例です。
- SUM関数
- SUMIF関数
- SUMIFS関数
- COUNT関数
- COUNTIFS関数
- DSUM関数
ただし、エクセルでさまざまな関数を組み合わせて作成した表は、数式が複雑になりがちです。社内で使用する表の数式が複雑だと、数式が壊れたり、参照が崩れたりして不具合が生じた場合、数式を作成した人以外が原因を特定することが難しくなる可能性があります。
数式はわかりやすく、他の人が見ても理解できる程度に使用するよう心がけましょう。
SUM関数
SUM関数は、選択した範囲の数字を合計する関数で、指定する範囲は連続したセルでも、セル1つずつでも可能です。以下がその表記方法の例です。
- 連続したセル：（A1:A23）
- 個別のセル：（A1, A5, A10, A23）
また、SUM=（A1, 200）のようにセルと数字を組み合わせて計算することも可能です。SUM関数の基本的な表記は以下の通りです。
| SUM関数＝SUM（合計範囲） |
SUM関数を利用して、行や列をそのまま集計する場合に用いられます。この表では、販売金額を集計するよう設定します。
合計を表示したい場所にセルを置き、「ホーム」タブの「Σ（シグマ）」をクリックし、集計したい項目をドラッグして範囲を指定し、エンターキーを押すことで計算が完了します。
SUMIF関数
SUMIF関数は条件に一致した数値を合計する関数です。指定された範囲から検索条件と一致するセルの合計範囲を集計します。その基本的な表記は以下の通りです。
| SUMIF関数＝SUMIF（範囲,検索条件,合計範囲） |
SUMIF関数を利用して、一定商品の売上金額を算出する場合などに用いられます。この表では、購入商品ごとの集計が表示されるよう設定します。
集計結果を表示したい場所のセルを置き、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力して「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されるため、選択し、それぞれの項目を指定します。
「範囲」には、購入商品欄を選択し、「検索条件」ではそれぞれ購入商品が記載されているセルを選択します。そして、「合計範囲」では販売金額欄を選択してOKをクリックすれば、設定が完了します。
SUMIFS関数
SUMIFS関数は、複数の条件を指定して数値を合計する関数で、先に述べた「SUMIF関数」と同様に、指定された合計対象範囲から複数の検索条件と一致するセルを探し、合計範囲を集計します。条件は最大で127組まで指定できます。
| SUMIFS関数＝SUMIFS（合計対象範囲,条件範囲1,条件1,条件範囲2,条件２…） |
SUMIFS関数を利用して、いくつかの条件に合う項目だけを合計する場合に用いられます。以下は、具体的な使用例です。
例：「ファンデーションを2つ購入した人の販売金額の合計額」の集計
集計結果を表示したい場所のセルを選択し、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力し、「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されますので、選んでOKをクリックします。
各条件を指定し、「OK」をクリックすることで、設定が完了します。
| 記入項目 | 範囲指定する理由 | 範囲指定する場所 |
|---|---|---|
| 合計対象範囲 | 販売金額の合計を集計するため | 販売金額の欄 |
| 条件範囲1 | 購入商品名を探すため | 購入商品の欄 |
| 条件1 | 検索条件 | 「ファンデーション」と記載されたセル |
| 条件範囲2 | 数量から2を探すため | 数量の欄 |
| 条件2 | 2つ購入した人を探すため | “=2” と入力 |
COUNT関数
COUNT関数は、数値が入ったセルを数える関数で、数値には文字列、論理値、空白のセルは含まれません。これらを含めたセルを数えたい場合は、別の関数（COUNTA関数）を使用します。COUNT関数の基本表記は以下の通りです。
| COUNT関数＝COUNT（値1,値2,値3…） |
COUNT関数を利用して、10月中に何回取引が行われたのかを数える例を以下に示します。
集計結果を表示したい場所のセルを置き、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力し、「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されますので、選んでOKをクリックします。
項目の範囲を指定し、「値1」には単価欄を選択し、OKをクリックすることで完了します。
COUNTIF関数
COUNTIFS関数は、複数の条件に一致するデータの個数を求める関数です。COUNTIFS関数の基本表記は以下の通りです。
| COUNTIF関数＝COUNTIF（範囲,検索条件） |
COUNTIF関数を利用して、10月の購入商品のうち「ファンデーション」が何回取引されたかを数えます。
集計結果を表示したい場所のセルを置き、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力し、「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されますので、選んでOKをクリックします。
項目の範囲を指定します。具体的には、「範囲」には購入商品欄を選択し、「検索条件」には「ファンデーション」と入力してからOKをクリックすれば、設定は完了です。
COUNTIFS関数
COUNTIFS関数は、複数の条件に一致するデータの個数を求める関数です。
| COUNTIFS関数＝COUNTIFS（範囲1,検索条件1,範囲2,検索条件２…） |
COUNTIFS関数を利用して、10月中にファンデーションを2個以上購入した人数を数えます。
集計結果を表示したい場所のセルを選択し、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力して「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されるため、選んでOKをクリックします。
項目の範囲を以下の通り指定し、OKをクリックして完了です。
| 記入項目 | 範囲指定する場所 |
|---|---|
| 検索条件範囲1 | 販売金額の欄 |
| 条件範囲1 | "ファンデーション" と入力 |
| 検索条件範囲1 | 数量の欄 |
| 条件範囲2 | "<=2" と入力 |
DSUM関数
DSUM関数は、データベースから複数の条件に合うデータの合計を算出する関数です。SUMIF関数は条件が1つなのに対しDSUMは複数の条件に対応できるので、使い方が異なるもののSUMIFS関数と同等の機能を持つ関数です。DSUM関数の基本表記は以下の通りです。
| DSUM関数＝DSUM（データベース,フィールド,条件） |
DSUM関数を利用して、「ファンデーションを購入した山田さんの販売金額の合計額」を集計するよう設定します。
集計結果を表示したい場所のセルを選択し、関数を表す「fx」ボタンをクリックして「関数の挿入」ダイアログボックスを表示させます。
「関数の検索」欄に関数名を入力して「検索開始」ボタンを押すと、「関数名」欄に該当の関数が表示されるため、選んでOKをクリックします。
「データベース」とは、複数のデータが規則的な形式で集められている一覧を指します。「フィールド」はデータベースにある列の見出しのことで、見出しの下にある行は「レコード」といいます。したがって、「データベース」では表全体を、「フィールド」には販売金額を指定します。
条件はシートのセルに記述します。今回のケースでは、集計したい表の右下にある「山田」「ファンデーション」および対応する「フィールド」を選択してOKをクリックすれば完了です。
まとめ
エクセルの集計作業において、小計、ピボットテーブル、統合、関数など様々な機能が利用できます。それぞれの機能には特性があり、効率的かつ柔軟にデータを扱うことが可能です。
小計はグループごとに合計を求める際に簡便で、ピボットテーブルは大量のデータを整理し、分析するのに便利です。統合は異なるワークシートデータを効果的に集計でき、関数は様々な条件での計算やデータの抽出を手軽に行えます。
集計方法が複雑になれば、作成した集計表も複雑になり、取り扱いが困難になることが予想されます。これらの機能を理解し、適切に組み合わせることで、エクセルをより効果的に活用できるでしょう。
