【Excel】エクセルの集計方法を解説（SUM・SUMIF・SUBTOTAL・小計・ピボットテーブル・マクロ・解除）

Excelで売上、数量、勤務時間、経費などを集計するときは、表の目的と更新頻度に合う機能を選ぶことが大切です。

単純な合計ならSUM関数、条件を指定するならSUMIF関数、絞り込みと連動させるならSUBTOTAL関数、複数項目を分析するならピボットテーブルが役立ちます。

さらに、毎月同じ処理を行う場合はマクロを使うことで、作業時間と入力ミスを減らせるでしょう。

集計の仕組みを先に理解すると、データ量が増えても迷わず作業できるようになります。

この記事では、1行目に見出しがあり、2行目以降にデータが入力されている表を例に、Excelの代表的な集計方法を順番に解説します。

数式の入力方法だけでなく、解除や更新時の注意点も確認していきましょう。

SUM関数による基本集計

それではまず、もっとも基本となるSUM関数による合計について解説していきます。

連続したセル範囲の合計

売上金額がD列に入力されている場合は、集計結果を表示したいセルにSUM関数を入力します。

この数式はD2からD4までの数値を合計するため、結果は3500になります。

SUM関数では、開始セルと終了セルをコロンでつなぐことで、連続範囲を指定できます。

数式を手入力するときは、半角のイコールから入力し、セル参照の列記号と行番号を間違えないようにしましょう。

セルをクリックしながら範囲をドラッグすれば、参照範囲を自動入力できるため、長い表でも操作しやすくなります。

オートSUMボタンによる入力

数式を直接入力する以外に、ホームタブのオートSUMボタンを使って合計できます。

合計を表示するセルを選択してからオートSUMをクリックすると、Excelは近くにある数値の連続範囲を自動判定します。

候補として表示された範囲が正しいことを確認し、Enterキーを押せば計算が確定します。

たとえばD5を選択した状態で実行すると、D2からD4が候補になるケースが一般的です。

空白行や文字列が途中にある場合は、Excelが想定と異なる範囲を選ぶことがあるため、数式バーを確認してください。

離れたセルと複数範囲の合計

SUM関数では、離れたセルや複数の範囲をまとめて合計することもできます。

この数式ではD2とD4、さらにD7からD10までの値を一度に計算します。

対象外の行を除いて合計したい場合や、月ごとに別シートへ分かれた表を集計したい場合に活用できる方法です。

ただし、参照先を細かく並べすぎると数式が読みにくくなり、修正漏れが起きやすくなります。

継続して追加されるデータは、できるだけ一つの表にまとめ、連続範囲で計算する設計が安心です。

SUMIF関数による条件別集計

続いては、特定の商品、担当者、部門だけの金額を求められるSUMIF関数を確認していきます。

商品名を条件にした売上集計

ノートだけの売上金額を合計したい場合は、条件範囲に商品列、合計範囲に売上金額列を指定します。

この数式ではC2からC4の中でノートと一致する行を探し、その行に対応するD列の金額を合計します。

条件に一致するデータが複数ある場合でも、SUMIF関数はすべての対象をまとめて計算します。

条件範囲と合計範囲は、必ず同じ行数で指定することが重要です。

たとえば条件範囲がC2からC100であるのに、合計範囲がD2からD99となると、ずれた行の金額を参照する可能性があります。

セル参照を使った条件の切り替え

条件を数式内に固定せず、別のセルに入力した商品名を参照する方法も便利です。

F2の内容をノートからペンへ変更するだけで、集計結果が自動的に切り替わります。

商品別集計表や担当者別の成績表を作るときは、条件をセル参照にすることで数式のコピーがしやすくなります。

集計表を下方向へコピーする場合は、元データの範囲を絶対参照にすると、参照範囲がずれません。

絶対参照では、範囲を指定したあとにF4キーを押して、列記号と行番号の前にドル記号を付けます。

数値条件とワイルドカードの活用

SUMIF関数では、金額が一定以上の行だけを集計するような数値条件も指定できます。

この数式はD列のうち、10000以上である金額だけを合計します。

条件をセル参照で指定する場合は、比較演算子とセル参照を文字列としてつなげます。

また、商品名の一部が一致するデータを探すには、アスタリスクを含むワイルドカードを使えます。

たとえばノートを含む商品を対象にしたい場合は、条件としてノートの前後にワイルドカードを付けて指定します。

数値が文字列として入力されていると、期待どおりに集計できない場合があります。

SUBTOTAL関数と小計機能の使い分け

続いては、フィルターで絞り込んだデータだけを集計できるSUBTOTAL関数と小計機能を確認していきます。

フィルターと連動するSUBTOTAL関数

SUBTOTAL関数は、オートフィルターで非表示になった行を除外して集計できる関数です。

数式内の9は合計を表す集計番号で、D2からD100の表示中の数値を合計します。

営業一課だけにフィルターをかけると、その部門の表示行だけを対象にした合計へ自動更新されます。

通常のSUM関数はフィルターで隠れた行も含めますが、SUBTOTAL関数は表示状態を反映します。

部署ごと、月ごと、担当者ごとに絞り込みながら集計結果を確認したい場面で特に便利です。

集計番号の違い

SUBTOTAL関数には、合計以外にも平均、件数、最大値、最小値などを求める集計番号があります。

たとえば平均を求める場合は1、数値が入っているセルの個数を数える場合は2、最大値を求める場合は4を指定します。

また、101から111までの番号を使うと、フィルターで非表示になった行だけでなく、手動で非表示にした行も除外できます。

9と109はいずれも合計ですが、手動で隠した行を含めるかどうかが異なります。

共有用の資料で行を一時的に隠す可能性があるなら、109を使う方法も検討しましょう。

小計機能によるグループ別集計

Excelのデータタブにある小計機能では、分類ごとに小計行とアウトラインを自動追加できます。

利用する前に、部門や商品名など、小計を入れたい基準列でデータを並べ替えておくことが必要です。

その後、データタブのアウトラインにある小計を選択し、グループの基準、集計方法、集計する列を設定します。

小計を実行すると、各グループの末尾に小計行が入り、左端のマイナス記号から明細の折りたたみもできるようになります。

ピボットテーブルによる多角的分析

続いては、項目の配置を変えながら大量データを分析できるピボットテーブルを確認していきます。

ピボットテーブルの作成手順

元データの表内にある任意のセルを選択し、挿入タブからピボットテーブルを選択します。

テーブルまたは範囲が正しく認識されていることを確認し、新規ワークシートまたは既存のワークシートを出力先として指定します。

作成後は右側にピボットテーブルのフィールド一覧が表示されます。

元データには空白の見出しを作らず、1行目に一意の列見出しを入れておくことが重要です。

途中に空白行や結合セルがあると、範囲の自動認識や更新で問題が起こる場合があります。

行列値フィルターの配置

フィールド一覧では、分析したい項目を行、列、値、フィルターの四つの領域へドラッグします。

たとえば部門別の商品売上を作る場合は、部門を行、商品を列、売上金額を値へ配置します。

値の領域へ数値列を置くと、通常は合計として集計されます。

件数になってしまった場合は、値フィールドの設定を開き、集計方法を合計へ変更しましょう。

行と列を入れ替えるだけで、同じ元データから別角度のクロス集計表を作成できます。

データ更新と集計表の解除

元データに新しい行を追加しても、ピボットテーブルは自動で更新されないことがあります。

ピボットテーブル内を右クリックし、更新を選択すると最新のデータを反映できます。

分析が不要になった場合は、ピボットテーブル全体を選択して削除できますが、元データまで消えないように注意が必要です。

削除したいのが集計表だけなら、ピボットテーブル上で選択してから全体を選択し、Deleteキーで消去します。

ピボットテーブルの削除は元データの削除とは別の操作です。

マクロによる定型集計と解除

続いては、毎回同じ集計作業を自動化するマクロと、不要になったマクロの解除方法を確認していきます。

マクロ記録による集計の自動化

開発タブのマクロの記録を使うと、操作内容をVBAコードとして保存できます。

たとえば売上金額列の下に合計を入れ、表示形式を通貨に変更し、罫線を付ける作業を記録しておけば、次回から同じ手順を再現できます。

記録を開始する前に、どのセルを起点にするかを決めておくと、不要な操作が記録されにくくなります。

マクロ記録は、毎月同じ形で届くデータを整形して集計する作業に向いています。

VBAによる合計行の作成

集計する最終行が毎回変わる場合は、VBAで最終行を取得してSUM関数を設定すると便利です。

Sub 売上合計を作成()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    Range("C" & lastRow + 1).Formula = "=SUM(C2:C" & lastRow & ")"
End Sub

このコードでは、C列の最終入力行を探し、その一つ下のセルへ合計数式を入力します。

1行目を見出しとしているため、SUM関数の開始セルはC2です。

VBAコードを実行する前には、対象シートと対象列が想定どおりであることを確認してください。

マクロの削除と実行ブロックの解除

不要なマクロを削除するには、開発タブのマクロを開き、対象のマクロ名を選択して削除を実行します。

マクロ付きブックを通常のExcelブック形式で保存すると、マクロは保存されないため、保存形式にも注意が必要です。

外部から入手したマクロ付きファイルで実行がブロックされた場合は、ファイルの安全性を確認したうえで、Windowsのプロパティにあるブロック解除を確認します。

信頼できない送信元のファイルでは、安易にコンテンツを有効化しないことが重要です。

マクロの解除とセキュリティ設定の変更は、ファイルの信頼性を確認してから行いましょう。

まとめ エクセルの集計方法とマクロブロックの解除

Excelの集計では、単純な合計にはSUM関数、条件付きの合計にはSUMIF関数、フィルターと連動した計算にはSUBTOTAL関数を使い分けます。

部門、商品、月、担当者など複数の項目を比較したい場合は、ピボットテーブルを使うと集計表を素早く作成できます。

繰り返し発生する定型作業はマクロで自動化できますが、マクロ付きファイルの実行や解除ではセキュリティにも配慮しましょう。

まずは元データの見出しと入力形式を整え、目的に合う集計機能を選ぶことが、正確で効率的なExcel作業につながります。