Excelの表を集計するときは、合計を求める関数と、必要なデータだけを表示するフィルターを使い分けることが大切です。
売上表や経費表、在庫表などは、行数が増えるほど目視での確認が難しくなりますが、集計の基本を押さえれば短時間で数字を確認できます。
まずはSUM関数で全体の合計を求め、条件がある場合はSUMIF関数やフィルターを組み合わせる流れを覚えると、さまざまな表に応用しやすくなります。
表を集計する基本の流れ
・合計を出す場合はSUM関数を使う
・条件別の合計にはSUMIF関数を使う
・表示するデータを絞る場合はフィルターを使う
・大きな表ではテーブルやピボットテーブルも便利です
ここでは、1行目に見出しがある売上表を例にして、Excelで表を集計する具体的な方法を解説します。
エクセルの表をSUM関数で合計する方法
それではまず、表全体の金額をすばやく合計する方法について解説していきます。
| 商品名 | 担当者 | 売上金額 |
|---|---|---|
| ノート | 田中 | 1200 |
| ペン | 佐藤 | 800 |
| ファイル | 田中 | 1500 |
上のように、A列からC列にデータがあり、C列の売上金額を合計したい場面を想定します。
SUM関数の基本入力
SUM関数は、指定したセル範囲にある数値を合計する関数です。
売上金額がC2からC4までに入力されている場合は、合計を表示したいセルにSUM関数を入力します。
=SUM(C2:C4)
この数式では、C2、C3、C4の数値をすべて加算します。
集計結果をC5などの空いているセルに表示すると、元データと結果の位置関係もわかりやすくなります。
数式を入力した後にEnterキーを押すと、1200、800、1500の合計である3500が表示されます。
セル範囲の指定では、最初のセル番地と最後のセル番地の間に半角のコロンを入力します。
表の行が追加される可能性がある場合は、あらかじめ余裕を持った範囲にするより、後述するテーブル化を利用するほうが管理しやすいでしょう。
オートSUMによる素早い集計
数式を手入力するのが不安なときは、ホームタブのオートSUMを使う方法が便利です。
合計を表示したいセルを選択し、ホームタブの編集グループにあるΣオートSUMをクリックします。
Excelは、選択セルの上または左に連続している数値を自動で判定し、合計範囲を点線で表示します。
表示された範囲が正しければ、そのままEnterキーを押すだけで集計完了です。
列の直下に合計セルを置く場合、オートSUMは縦方向の数値を認識しやすいため、初めて集計する人にも向いています。
途中に空白行や文字列があると、意図した範囲まで選ばれないことがあります。
その場合はマウスで範囲をドラッグし直すか、数式バーでセル範囲を修正してください。
複数列と離れたセルの合計
複数の列をまとめて合計したい場合も、SUM関数で対応できます。
たとえば、C列に売上金額、D列に送料があり、それぞれの合計を一度に出したいなら、各列の下にSUM関数を入力します。
一方で、C2とC4だけのように離れたセルを合計する場合は、セル番地をカンマで区切ります。
=SUM(C2,C4)
このように記述すると、指定した2つのセルだけが集計対象になります。
連続する範囲にはC2:C4、離れたセルにはC2,C4という違いを理解すると、SUM関数の使い方が安定します。
【操作のポイント】合計セルは、金額列の最終行のすぐ下に配置すると、どの列の集計値なのかを確認しやすくなります。
エクセルの表をフィルターで絞り込む方法
続いては、表から必要なデータだけを表示して確認するフィルターの使い方を確認していきます。
| 日付 | 担当者 | 売上金額 |
|---|---|---|
| 4月1日 | 田中 | 1200 |
| 4月2日 | 佐藤 | 800 |
| 4月3日 | 田中 | 1500 |
フィルターはデータを削除せず、条件に合わない行を一時的に非表示にできる機能です。
フィルターの設定
フィルターを設定するときは、見出しを含む表の中にある任意のセルをクリックします。
その後、データタブを開き、並べ替えとフィルターのグループにあるフィルターを選択してください。
または、CtrlキーとShiftキーを押しながらLキーを押しても、フィルターを設定できます。
設定後は、1行目の見出しセルに下向き三角のボタンが表示されます。
見出し行を正しく含めてフィルターを設定することが、項目名を基準にデータを絞り込むための基本です。
表の途中に完全な空白行や空白列があると、Excelが表の範囲を正確に判断できない場合があります。
必要に応じて表全体を選択してからフィルターを設定しましょう。
担当者や商品名による絞り込み
特定の担当者だけを確認したい場合は、担当者列の下向き三角をクリックします。
一覧に表示されたチェックボックスからすべて選択を外し、表示したい担当者だけにチェックを入れます。
たとえば田中だけを選ぶと、田中が担当した行だけが表示され、佐藤の行は非表示になります。
フィルターで非表示になった行は削除されたわけではないため、条件を解除すれば元の表に戻せます。
文字列が多い列では、検索ボックスに名前や商品名の一部を入力して候補を探すこともできます。
表の規模が大きくても、条件を変えながら必要な情報にすばやくたどり着けるでしょう。
数値フィルターと日付フィルター
売上金額のような数値列では、数値フィルターを使うと金額条件で絞り込めます。
たとえば1000円以上の売上だけを確認する場合は、売上金額列のボタンから数値フィルターを選び、指定の値以上を設定します。
条件の例
売上金額が1000以上の行だけを表示する
日付列であれば、日付フィルターから今月、先月、指定期間などの条件を選べます。
条件を複数の列に設定すると、すべての条件に一致するデータだけが表示されます。
絞り込みを解除するには、対象列のボタンからフィルターをクリアを選択します。
【操作のポイント】フィルターのアイコンが漏斗の形に変わっている列には、何らかの絞り込み条件が設定されています。
SUBTOTAL関数による絞り込み後の集計
続いては、フィルターで表示されている行だけを合計するSUBTOTAL関数について確認していきます。
| 担当者 | 売上金額 | 表示状態 |
|---|---|---|
| 田中 | 1200 | 表示 |
| 佐藤 | 800 | 非表示 |
| 田中 | 1500 | 表示 |
通常のSUM関数は、フィルターで隠れている行も含めて合計します。
表示中のデータだけを集計したいときは、SUBTOTAL関数を使うのが適切です。
SUBTOTAL関数の数式
フィルターで表示されている売上金額だけを合計する場合は、次の数式を入力します。
=SUBTOTAL(9,C2:C4)
最初の9は、合計を求めるSUMの集計方法を表す番号です。
後ろのC2:C4は、合計したい売上金額のセル範囲を示します。
SUBTOTAL関数では、フィルターによって非表示になった行が自動的に集計対象から除外されます。
担当者を田中だけに絞り込んだ状態では、1200と1500だけが合計され、結果は2700になります。
集計番号の意味
SUBTOTAL関数では、合計以外にも平均、件数、最大値、最小値などを求められます。
よく使う集計番号として、9は合計、1は平均、2は数値が入っているセルの個数です。
=SUBTOTAL(1,C2:C4)
表示中の売上金額の平均を求める数式です。
集計方法を変えるだけで、同じフィルター条件に対して複数の指標を確認できます。
集計結果をダッシュボードのように使いたい場合は、合計と平均を並べておくと変化を把握しやすくなります。
フィルター操作を再現した画面イメージ
ここでは、担当者で絞り込んだ後にSUBTOTAL関数の結果を確認する画面をイメージで見ていきましょう。
このように、まずフィルターで確認したいデータを残し、集計セルではSUBTOTAL関数を使うと、条件変更のたびに結果が自動更新されます。
SUM関数とSUBTOTAL関数の使い分けは、非表示の行を合計に含めるかどうかで判断してください。
【操作のポイント】フィルター付きの表では、合計欄にSUBTOTAL関数を設定しておくと、担当者や期間を切り替えるだけで集計値を確認できます。
SUMIF関数による条件別の集計
続いては、条件に合うデータだけを数式で合計するSUMIF関数を確認していきます。
| 担当者 | 商品名 | 売上金額 |
|---|---|---|
| 田中 | ノート | 1200 |
| 佐藤 | ペン | 800 |
| 田中 | ファイル | 1500 |
SUMIF関数は、指定した条件に一致する行だけの数値を合計する関数です。
担当者別の売上合計
田中が担当した売上だけを合計する場合は、次のように入力します。
=SUMIF(A2:A4,”田中”,C2:C4)
最初のA2:A4は、条件を調べる範囲です。
次の田中は、集計したい条件を表します。
最後のC2:C4は、実際に合計する売上金額の範囲です。
条件範囲と合計範囲は、同じ行数にそろえることが重要です。
この例では、田中に該当する1200と1500が合計され、2700になります。
セル参照による条件変更
数式内に担当者名を直接書く代わりに、別のセルを参照すると、集計対象を簡単に切り替えられます。
=SUMIF(A2:A4,E2,C2:C4)
この場合、E2セルに田中と入力すれば田中の合計が表示され、E2を佐藤に変更すれば佐藤の合計に自動更新されます。
条件を入力するセルと集計結果を分けることで、集計表として再利用しやすくなります。
担当者名の一覧を別に作り、各担当者の横にSUMIF関数を並べれば、担当者別の売上一覧を作成できます。
数値条件による売上集計
SUMIF関数では、文字だけでなく数値の条件も指定できます。
たとえば1000円以上の売上だけを合計するなら、条件に”>=1000″を指定します。
=SUMIF(C2:C4,”>=1000″,C2:C4)
この数式では、C列の金額が1000以上である行の金額を、同じC列から合計します。
条件に比較記号を使う場合は、比較記号と数値を二重引用符で囲むことを覚えておきましょう。
【操作のポイント】条件が1つだけならSUMIF関数、複数の条件を同時に指定したい場合はSUMIFS関数を使います。
テーブル機能とピボットテーブルによる集計
続いては、データ量が多い表を効率よく管理するためのテーブル機能とピボットテーブルを確認していきます。
| 日付 | 担当者 | 売上金額 |
|---|---|---|
| 4月1日 | 田中 | 1200 |
| 4月2日 | 佐藤 | 800 |
| 4月3日 | 田中 | 1500 |
毎月データを追加する表では、通常のセル範囲よりもテーブル機能を活用したほうが集計漏れを防ぎやすくなります。
テーブル化による範囲の自動拡張
表の中のセルを選択し、挿入タブからテーブルを選ぶと、データをテーブル形式に変換できます。
先頭行をテーブルの見出しとして使用するにチェックが入っていることを確認してから、OKを選択します。
テーブル化するとフィルターが自動設定され、新しい行を表の末尾に入力した場合も集計対象の範囲が広がります。
データが追加される表では、数式の範囲を毎回修正する手間を減らせる点がテーブルの大きな利点です。
見出し名を使った構造化参照も利用できるため、数式の意味を読み取りやすくなる場面もあります。
ピボットテーブルの作成
担当者別、商品別、月別など、複数の切り口で集計したいときはピボットテーブルが役立ちます。
元の表内にあるセルを選び、挿入タブからピボットテーブルを選択します。
作成先を新規ワークシートにすると、元データと集計表を分けて管理できます。
フィールド一覧では、担当者を行、売上金額を値のエリアへドラッグすると、担当者ごとの売上合計が作成されます。
ピボットテーブルは関数を1つずつ入力しなくても、ドラッグ操作で集計軸を変更できる機能です。
集計結果の更新
元データを追加または修正した後は、ピボットテーブルを右クリックして更新を選択します。
更新を実行すると、変更後のデータが集計結果に反映されます。
元データをテーブル化しておくと、新しく追加した行もピボットテーブルの元データ範囲に含まれやすくなります。
定期的にデータを扱う業務では、入力用の表と分析用のピボットテーブルを分けておくと、作業の見通しがよくなるでしょう。
【操作のポイント】ピボットテーブルの数字が古いと感じたら、まず更新を実行して元データとの同期を確認してください。
まとめ エクセルの表を関数とフィルターで集計する方法
Excelの表を集計するときは、目的に応じて関数とフィルターを選ぶことで、正確で見やすい結果を作れます。
表全体の合計にはSUM関数を使い、条件に合うデータだけを合計したいときはSUMIF関数を使います。
フィルターで表示中の行だけを集計する場合は、SUM関数ではなくSUBTOTAL関数を利用することがポイントです。
表示を絞り込む操作と、数式で条件別に計算する操作は似ているようで役割が異なります。
フィルターはデータを確認するための機能であり、SUMIF関数は条件に一致する値を直接計算するための関数です。
行数が増える表はテーブル化し、複数の視点から分析したい場合はピボットテーブルを活用すると、集計作業をさらに効率化できます。
まずは小さな売上表でSUM関数、フィルター、SUBTOTAL関数を試し、日々の業務に合う集計方法を身につけていきましょう。
