Excelで売上、在庫、出勤、申込件数などのデータを扱うと、一覧を眺めるだけでは全体の傾向を把握しにくくなります。
そこで役立つのが、個数を数える関数、条件付きで集計する関数、そして分類ごとに小計を表示する機能です。
集計の目的に合わせてCOUNT系関数、SUMIF系関数、テーブル、ピボットテーブルを使い分けると、手作業の計算ミスを減らせます。
データ集計でよく使う機能は、数値の件数を数えるCOUNT、空白以外を数えるCOUNTA、条件に合う件数を数えるCOUNTIF、条件に合う金額を合計するSUMIFです。
本記事では、1行目に見出しがある売上表を例に、Excelで個数をカウントし、小計や合計を見やすくまとめる方法を解説します。
サンプル表では、A列を日付、B列を担当者、C列を商品名、D列を数量、E列を売上金額として扱います。
エクセルでデータを集計する基本手順
| 日付 | 担当者 | 商品名 | 数量 | 売上金額 |
|---|---|---|---|---|
| 4月1日 | 佐藤 | A商品 | 3 | 4,500円 |
| 4月1日 | 田中 | B商品 | 2 | 3,000円 |
| 4月2日 | 佐藤 | A商品 | 1 | 1,500円 |
それではまず、データを集計する前に確認したい基本手順について解説していきます。
最初に表の列ごとの意味を固定し、1行目を見出しとして整えることが、正確な集計の土台です。
見出し行とデータ範囲の整理
集計用の表では、1行目に日付、担当者、商品名、数量、売上金額のようなヘッダーを配置します。
2行目以降には、1件ごとの取引や記録を横方向の列に対応させて入力します。
途中に空白行や結合セルがあると、オートフィルター、並べ替え、ピボットテーブルの対象範囲が途切れる場合があります。
そのため、元データは1つの連続した表として管理するのが基本です。
データ範囲がA1からE101の場合、A1からE1が見出し、A2からE101が集計対象の明細です。
金額列には数値を入力し、セル内に円という文字を直接付けず、表示形式で通貨や桁区切りを設定すると計算しやすくなります。
日付も文字列ではなく日付形式で入力すると、月別や期間別の集計に活用できます。
テーブル機能による集計範囲の固定
データ内の任意のセルを選択し、挿入タブからテーブルを選ぶと、見出し付きの表としてExcelに認識させられます。
テーブルの作成画面では、先頭行をテーブルの見出しとして使用する項目を確認しましょう。
テーブル化した表は新しい行を追加しても対象範囲が自動で広がるため、集計式の修正回数を抑えられます。
通常のセル範囲に式を設定すると、明細を追加した際に参照範囲を手動で変更しなければならないことがあります。
継続して更新する売上台帳、出勤簿、顧客リストでは、テーブル形式が特に便利です。
集計先を分ける考え方
元データの右側や別シートに集計欄を作ると、明細を消さずに合計、件数、担当者別の数字を確認できます。
たとえばG列に商品名、H列に販売件数、I列に売上合計を配置すれば、商品別の集計表を作成できます。
集計表の見出しも1行目にそろえ、元データとは色を変えると閲覧時の見間違いを防げます。
【操作のポイント】元データは入力専用、集計表は確認専用として役割を分けると、数式を上書きする事故を防ぎやすくなります。
COUNT関数とCOUNTA関数による個数集計
| 項目 | 集計式 | 意味 |
|---|---|---|
| 数量が入力された件数 | =COUNT(D2:D101) | 数値があるセル数 |
| 担当者が入力された件数 | =COUNTA(B2:B101) | 空白以外のセル数 |
続いては、個数を数えるCOUNT関数とCOUNTA関数について確認していきます。
Excelで件数を集計するときは、数値だけを対象にするか、文字列を含む入力済みセルを対象にするかを最初に決めます。
COUNT関数で数値データを数える方法
COUNT関数は、指定範囲に入っている数値のセル数を返す関数です。
=COUNT(D2:D101)
この数式を入力すると、D2からD101までのうち、数値が入っているセルの個数を数えます。
数量列や点数列、金額列に入力済みのレコード数を調べたい場合に向いています。
COUNT関数は文字列を数えないため、商品名や担当者名の件数を求める用途には適しません。
また、数式の結果が空文字列になっているセルは、見た目が空白でも扱いが異なることがあります。
集計結果が予想と違うときは、対象セルが数値として入力されているか確認しましょう。
COUNTA関数で入力済みセルを数える方法
COUNTA関数は、文字列、数値、日付など、空白ではないセルの数を数える関数です。
=COUNTA(B2:B101)
B列に担当者名が入力されている場合は、この式で処理件数や担当件数の目安を把握できます。
商品コード、受付番号、顧客名のように文字列が中心の列を集計する場合にも利用できます。
COUNTA関数はスペースだけが入力されたセルも空白以外として数えるため、入力ルールを統一することが重要です。
不要なスペースが混ざるデータでは、TRIM関数で余分な空白を除去してから確認する方法もあります。
COUNTBLANK関数との使い分け
未入力の件数を確認したいときは、COUNTBLANK関数を使います。
=COUNTBLANK(E2:E101)
この数式では、売上金額が未入力のセル数を確認できます。
COUNT、COUNTA、COUNTBLANKを組み合わせると、登録済み件数だけでなく、入力漏れの有無も確認しやすくなります。
ただし、数式で空文字列を返しているセルは、COUNTBLANKで空白として扱われることがあるため、表の設計と数式の内容をあわせて確認しましょう。
【操作のポイント】数値の件数ならCOUNT、文字を含む入力済み件数ならCOUNTA、未入力件数ならCOUNTBLANKを選びます。
COUNTIF関数による条件付きカウント
| 商品名 | 販売件数 | 使用する式 |
|---|---|---|
| A商品 | 2 | =COUNTIF($C$2:$C$101,G2) |
| B商品 | 1 | =COUNTIF($C$2:$C$101,G3) |
続いては、特定の商品や担当者だけを数えるCOUNTIF関数について確認していきます。
COUNTIF関数を使うと、一覧から条件に一致するデータだけを自動でカウントできます。
COUNTIF関数の基本構文
=COUNTIF(範囲,条件)
第1引数の範囲には検索する列を指定し、第2引数の条件には数えたい文字、数値、またはセル参照を指定します。
たとえばA商品が何件あるかを直接調べる場合は、=COUNTIF(C2:C101,”A商品”)と入力します。
商品名をG2セルに入力している場合は、=COUNTIF($C$2:$C$101,G2)とすると、条件を変更しやすい集計表になります。
オートフィルで式を下へコピーする場合、検索範囲は$記号で絶対参照に固定するのが基本です。
数値条件と比較演算子の指定
数量が3以上の明細を数える場合は、条件に比較演算子を含めます。
=COUNTIF(D2:D101,”>=3″)
条件をセル参照で指定する場合は、演算子とセル番地を文字列としてつなげます。
=COUNTIF(D2:D101,”>=”&G2)
G2セルに3と入力しておけば、G2の数字以上の件数を数えられます。
日付や金額も同じ考え方で条件を設定できますが、日付の表示形式と実際のシリアル値が異なる点には注意が必要です。
COUNTIFS関数による複数条件の集計
担当者が佐藤で、なおかつ商品名がA商品のように、複数条件を満たす件数を調べるときはCOUNTIFS関数を使います。
=COUNTIFS(B2:B101,”佐藤”,C2:C101,”A商品”)
COUNTIFS関数では、条件範囲と条件を順番に指定します。
すべての条件を満たした行だけがカウントされるため、担当者別、月別、商品別のクロス集計にも便利です。
条件範囲は同じ行数でそろえる必要があるため、B2:B101とC2:C100のように範囲の大きさが違わないよう注意しましょう。
【操作のポイント】条件を入力するセルを集計表に用意し、COUNTIFやCOUNTIFSから参照すると、条件変更だけで結果を更新できます。
SUMIF関数と小計機能による金額集計
| 商品名 | 売上合計 | 集計式 |
|---|---|---|
| A商品 | 6,000円 | =SUMIF($C$2:$C$101,G2,$E$2:$E$101) |
| B商品 | 3,000円 | =SUMIF($C$2:$C$101,G3,$E$2:$E$101) |
続いては、条件別の売上金額を求めるSUMIF関数と、小計機能について確認していきます。
件数だけでなく金額や数量の合計を知りたい場面では、SUM関数、SUMIF関数、集計機能を使い分けます。
SUMIF関数で商品別売上を求める方法
=SUMIF($C$2:$C$101,G2,$E$2:$E$101)
この式では、C列の商品名がG2セルと一致する行を探し、その行のE列の売上金額を合計します。
第1引数は条件を判定する範囲、第2引数は条件、第3引数は合計する範囲です。
商品名で判定する範囲と、金額を合計する範囲を取り違えないことが、SUMIF関数を正しく使うコツです。
G2にA商品、G3にB商品を入力し、H2の数式を下方向にコピーすれば、商品別の売上表を効率よく作成できます。
SUBTOTAL関数で絞り込み後の合計を表示する方法
オートフィルターで表示する行を絞り込んだ後の合計を表示したい場合は、SUBTOTAL関数が便利です。
=SUBTOTAL(9,E2:E101)
最初の引数9は合計を意味し、E2:E101の数値を合計します。
SUBTOTAL関数はフィルターで非表示になった行を集計から除外できるため、表示中のデータだけを確認したい場面に向いています。
通常のSUM関数では、フィルターで隠した行も合計に含まれます。
担当者別や月別に絞り込みながら合計を確認するなら、SUBTOTAL関数を集計欄に置くと実務で役立ちます。
データタブの小計機能による分類別合計
小計機能を使うと、担当者や商品名が変わる位置に合計行を自動で追加できます。
まず集計の基準にしたい列を選び、データタブの並べ替えで同じ項目を連続させます。
次にデータタブのアウトラインにある小計を選択し、グループの基準、集計の方法、集計するフィールドを指定します。
担当者ごとの売上金額を合計したい場合は、グループの基準を担当者、集計の方法を合計、集計するフィールドを売上金額に設定します。
小計機能は明細の間に小計行を挿入するため、元データを別シートに複製してから実行すると管理しやすくなります。
【操作のポイント】条件別の金額集計にはSUMIF、フィルター表示中の合計にはSUBTOTAL、分類の切り替わりごとの合計には小計機能を使います。
ピボットテーブルによる多角的な集計
| 担当者 | A商品 | B商品 | 総計 |
|---|---|---|---|
| 佐藤 | 6,000円 | 0円 | 6,000円 |
| 田中 | 0円 | 3,000円 | 3,000円 |
続いては、複数の切り口で集計できるピボットテーブルについて確認していきます。
ピボットテーブルは、数式を複雑に組まなくても、担当者別、商品別、月別の集計表を作り替えられる機能です。
ピボットテーブルの作成手順
元データ内の任意のセルを選択し、挿入タブからピボットテーブルを選びます。
テーブルまたは範囲に元データが正しく指定されていることを確認し、新規ワークシートまたは既存のワークシートを出力先に選びます。
作成後は右側にピボットテーブルのフィールド一覧が表示されます。
担当者を行、商品名を列、売上金額を値のエリアへドラッグすると、担当者別かつ商品別の売上集計が作成されます。
値が個数になった場合は、値フィールドの設定で合計を選択しましょう。
個数と合計の表示設定
商品名や受付番号を値エリアに置くと、通常は個数として集計できます。
売上金額、数量のような数値列を値エリアに置くと、通常は合計として扱われます。
同じ項目を値エリアへ2回配置すれば、売上合計と件数を同じピボットテーブル内で比較できます。
金額の表示が見づらいときは、値フィールドの設定から表示形式を開き、数値や通貨の桁区切りを設定します。
元データの数値が文字列になっていると合計できないため、必要に応じて数値へ変換してください。
更新とフィルターによる確認
元データに新しい明細を追加した後は、ピボットテーブルを右クリックして更新を実行します。
テーブル形式の元データを使っていれば、新しく追加した行も更新時に反映されやすくなります。
フィルターエリアへ日付や担当者を置くと、確認したい期間や担当者だけに絞り込めます。
定例レポートでは、元データを更新してピボットテーブルを更新する流れを決めておくと、集計作業を標準化できます。
【操作のポイント】行、列、値、フィルターの4つのエリアへ項目を配置し、見たい切り口に合わせて集計表を組み替えます。
まとめ エクセルでデータを集計する方法
Excelでデータを集計する方法では、まず1行目にヘッダーを用意し、明細を連続した表として整えることが重要です。
数値が入力された件数を数えるならCOUNT関数、文字を含む入力済みセルを数えるならCOUNTA関数を使用します。
特定の商品、担当者、金額条件に絞った件数にはCOUNTIFやCOUNTIFSが適しています。
条件別の売上や数量の合計にはSUMIF関数を使い、フィルターで絞った表示中の合計にはSUBTOTAL関数を使うと便利です。
担当者別、商品別、月別など複数の視点を行き来したい場合は、ピボットテーブルを使うと集計の手間を大きく減らせます。
小計機能は、並べ替えた明細表の途中に分類ごとの合計を挿入したい場合に役立ちます。
元データを整え、目的に合う関数や機能を選ぶことが、正確で見やすいExcel集計表への近道です。
まずはCOUNTIF関数やSUMIF関数を使った小さな集計表から作成し、必要に応じてテーブルやピボットテーブルへ発展させていきましょう。
