エクセルのフィルター機能を使っていると抽出したデータのみが集計できたらという場面が多くあります。
例えば、
・商品名をフィルターで抽出したらその在庫の合計が表示される
・社員の名前で抽出したらその社員の残業時間の平均が表示される
この記事ではそんな、フィルターで抽出したデータを集計する方法を解説していきます。
方法としては
・SUBTOTAL関数
・便利マクロ
を使う方法があります。
最近のエクセルの傾向として「テーブル」機能が充実してきています。
テーブルを使った方法から解説していきます。
【エクセル】可視セルのみのデータを集計したい場合の操作法【テーブル化編】
サンプルの表をテーブル化していきます。
手順としては、
①表を範囲選択
②リボンの「挿入」タブを選択
③「テーブル」をクリック
④「テーブル作成」メニューを確認しOKをクリック
⑤テーブルデザイン
⑥集計行
の順に選択していきます。
①表を範囲選択
テーブル化したい表を全体を選択しましょう。
②、③リボンの「挿入」タブを選択し、「テーブル」をクリック
④「テーブル作成」メニューを確認しOKをクリック
 |
「テーブル」のデザインをテーブルを選択すると、
リボンに「テーブルデザイン」タブが追加表示されるので、それで変更することができます。
いろいろなレイアウトがあるので、いろいろ選択して試してみましょう。
⑤、⑥テーブルデザインと集計行
テーブルを集計する場合にも「テーブルデザイン」タブを開きます。
「リボン」→「テーブルデザイン」→「集計行」と選択していきましょう。
「集計行」にチェックが入ることで、表の下に「集計行」が追加されました。
「集計行」を挿入するとデフォルトでは合計の設定になっています。
「集計行」の右下にある選択ボタンをクリックすると、いろいろな集計を選択できます。
今回は合計を求めたいので、このままにしておきます。
「テーブル」になると、フィルターが自動で付けられています。
フィルターから商品Aのみを選択し、OKをクリックしましょう。
商品Aのみの合計を求めることができました。
【エクセル】可視セルのみのデータを集計したい場合の操作法【SUBTOTAL編】
表をテーブル化しての集計の方法を解説しましたが、SUBTOTAL関数を使用してもフィルター後の可視セルのみの集計を行う事ができます。
手順としては
①表の下に関数を入力
②フィルターを設定
③フィルターでデータを抽出
という順に行っていきます。
①表の下に関数を入力
表の下に
＝SUBTOTAL(集計番号,集計範囲)
と入力しましょう。
表の下に入力しないとフィルターでデータを抽出した時に隠れて消えてしまうことがあるので、入力する箇所は注意してください。
よく使う集計番号は以下のようになります。
① = AVERAGE
② = COUNT
③ = COUNTA
④ = MAX
⑤ = MIN
⑥ = PRODUCT
⑦ = STDEV.S
⑧ = STDEV.P
⑨ = SUM
今回は合計なので9を入力しました。
販売金額の合計が表示されました。
②フィルターを設定
フィルターを付けて、商品Aのみのデータを選択しOKをクリックしましょう。
③フィルターでデータを抽出
商品Aのみの合計が表示されました。
【エクセル】可視セルのみのデータを集計したい場合の操作法【便利マクロ編】
範囲選択をしてマクロを動かすと自動で表をテーブルにして、下に「集計行」を挿入するマクロを作りました。
頻繁に同じ処理をするときは、マクロを作って「クイックアクセルツールバー」に登録させたり、ショートカットキーに登録すると楽に作業を進めることができます。
一つ一つの作業はそれほどかからなくてもたくさん処理を行う場合は、けっこうな時間がかかるものです。
そこを簡略化させることで業務の効率をアップさせることができます。
以下の手順で確認していきましょう。
①コードの紹介
②コードのコピペ
③コードの起動
④結果の確認
①コードの紹介
マクロのコードは以下になります。
Sub テーブル作成マクロ()
Dim a
Dim b
Dim c
b =
ActiveSheet.ListObjects.Count
c = "テーブル" & b + 1
ActiveSheet.ListObjects.Add(xlSrcRange,
Range(a), , xlYes).Name = c
ActiveSheet.ListObjects(c).ShowTotals
= True
End Sub
コードの解説は以下になります。
マクロの名前です
Dim a
Dim b
Dim c
上記のa,b,cの変数を宣言しています。
a =
Selection.Address
選択している範囲のアドレスを取得して変数aに代入しています。
b =
ActiveSheet.ListObjects.Count
ワークシートにあるテーブルの数を数えています。
c = "テーブル" & b + 1
テーブルの名前を「テーブル〇」として、変数ｃに代入しています。
ActiveSheet.ListObjects.Add(xlSrcRange,
Range(a), , xlYes).Name = c
表をテーブルに変換して、名前を付けています。
ActiveSheet.ListObjects(c).ShowTotals
= True
集計行を「合計」で表示させています。
End Sub
マクロの記述終了です。
②コードのコピペ
コードを「個人用マクロブック」か「作業中のブック」の「標準モジュールにコピペ」しましょう。
③コードの起動
エディターの「実行」から「Sub/ユーザーフォームの実行」をクリックし実行させましょう。
④結果の確認
まとめ この記事のまとめです。
この記事ではフィルターでデータを抽出した際に、抽出したデータのみを集計する（今回は合計）方法の紹介を行いました。
方法としては「テーブル化」や「SUBTOTAL関数」で集計する方法と「便利マクロ」の紹介を行いました。
自分の使いやすい方法を見つけて、業務の効率化を目指しましょう。
0 件のコメント:
コメントを投稿