Excel

【Excel】エクセルで同じ項目でまとめる・集計(同一項目、関数、カテゴリーごと、同じデータ)する方法

当サイトでは記事内に広告を含みます
技術ブログ特化メルマガはこちら

この記事では、Excelで同じ項目ごとにデータを集計する方法について、UNIQUE関数やSUMIF関数などを使ったやり方と、それをマクロで自動化する方法を解説します。

ポイントは以下の3点です。

・UNIQUE関数で重複を削除し、項目一覧を作成する

・SUMIF関数で項目ごとの合計値を求める

・COUNTIF関数で項目ごとのデータ数を集計する

集計作業は関数を使えば簡単に効率化できるので、ぜひマスターしておきましょう!

エクセルで同じ項目ごとにまとめて集計する方法【UNIQUE関数】

それでは、サンプルデータを使って項目ごとの合計金額を求める方法を見ていきましょう。

まずは次のようなデータを用意します。

A列に和菓子の項目(商品名)、B列に売上金額を入力しておきます。

集計のためにはまず、ユニークな項目の一覧を作成する必要があります。

ここではE2セルに以下の数式を入力し、商品名の重複を排除します。

=UNIQUE(A2:A10)

UNIQUE関数は指定した範囲内のユニークな値だけを返してくれる関数です。

これにより、E列には桜もち、ういろう、ゆべしの3つの商品名が表示されます。

数式を入力したらENTERキーで確定し、E2セルの右下をダブルクリックして数式を下方向にコピーしましょう。

準備ができたら、F2セルに次の数式を入力します。

=SUMIF($A$2:$A$10,E2,$B$2:$B$10)

SUMIF関数は、指定した検索条件に合致するセルの合計を求める関数です。

第1引数で検索対象の範囲、第2引数で検索条件、第3引数で合計対象の範囲を指定します。

ここでは、商品名列であるA列を検索対象($A$2:$A$11)に、E2セルの商品名を検索条件(E2)に、金額列のB列を合計対象($B$2:$B$11)にしています。

$マークはセルの絶対参照を意味し、数式をコピーしても参照先が変わらないようにするために使います。

F2セルでENTERキーを押すと、桜もちの売上合計がF2セルに表示されます。

同様にF2の数式を下方向にコピーすれば、各商品の合計売上が一覧できます。

エクセルで同じ項目ごとにまとめて集計する方法【COUNTIF関数】

続いて、COUNTIF関数を使って項目ごとのデータ件数を数える方法を解説します。

先ほどのデータにG列を追加し、G2セルに以下の数式を入力しましょう。

=COUNTIF($A$2:$A$10,E2)

COUNTIF関数は指定範囲内で検索条件に一致するセルの個数を返します。

SUMIF関数と同様、検索対象をA列($A$2:$A$10)、検索条件をE2セルの商品名(E2)としています。

これを下方向にコピーすれば、各商品の販売件数がG列に表示されます。

このように、SUMIF関数とCOUNTIF関数を駆使することで、項目ごとの合計や件数を簡単に集計できるのです。

同じ項目ごとにまとめて集計する方法【マクロで実行】

最後に、UNIQUE関数とSUMIF関数の処理をマクロにして、ボタン操作で一括実行する方法をお伝えします。

Excelの「開発」タブを表示し、「Visual Basic」をクリックしてマクロエディタ(VBE)を開きます。

「挿入」メニューから「標準モジュール」を追加し、以下のコードを貼り付けましょう。


Sub ItemSubTotal()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim itemList As Range
    Dim itemCell As Range
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    With ws
        .Range("E2:G100").ClearContents
        Set itemList = .Range("E2")
        itemList.Formula = "=UNIQUE(A2:A" & lastRow & ")"
        itemList.AutoFill .Range("E2:E" & .Cells(Rows.Count, 5).End(xlUp).Row)
        
        For Each itemCell In .Range("E2:E" & .Cells(Rows.Count, 5).End(xlUp).Row)
            .Cells(itemCell.Row, 6).Formula = "=SUMIF($A$2:$A$" & lastRow & "," & itemCell.Address & ",$B$2:$B$" & lastRow & ")"
        Next itemCell
    End With
End Sub

コードの処理内容は次の通りです。

1. 「A列の最終行」と「E2セル」を変数として定義
2. E列とF列の既存データをクリア
3. E2セルにUNIQUE関数で商品名一覧を作成し、最終行までオートフィル
4. 商品名セルを1つずつ繰り返し処理し、SUMIF関数で対応する合計金額を算出

コードを保存したらマクロを実行する前に、Excelの「開発」タブで「マクロのセキュリティ」を「警告を表示してすべてのマクロを無効にする」に設定しておきます。

準備ができたらExcelシートに戻り、適当な場所にボタンを挿入します。

ボタンを右クリックして「マクロの割り当て」をクリックし、上で作成したマクロ(ItemSubTotal)を選択します。

あとはサンプルデータを入力し、ボタンをクリックするだけで、一瞬で商品別の合計売上が集計されるはずです。

まとめ Excelの関数とマクロを使って項目別の集計を行う方法

以上、Excelの関数とマクロを使って項目別の集計を行う方法をご紹介しました。

大量のデータを扱う際や、定型の集計作業が多い場合は特に便利だと思います。

ただし、マクロはセキュリティ上のリスクもあるため、信頼できるファイルでのみ使用するよう注意が必要です。

皆さんも業務の効率化や生産性アップのために、ぜひExcelの関数とマクロを活用してみてくださいね。

RELATED POST