EXCEL VBA 同じ色のセル値の合計・集計方法・カウント（テクニック）
EXCEL VBA 同じ色のセル値の合計・集計方法・カウント（テクニック）
●はじめに
EXCELで計算書や勤務表などを作成して、データを集計する際に、通常は文字列を参照して一致する文字列に対して集計を行いますが、文字列だけで表現されている表は、見づらいのでセルに色を（背景色）付けて表現をすると分かりやすくなると思います。EXCELで作成した表などが背景色で色分けし集計が出来ると便利だと思います。ここでは、背景色を利用した集計するテクニックの方法を説明いたします。
同じセルの背景色同士の集計・カウント①
●プログラム説明 （サンプルプログラム①）
下記のサンプルは、指定したセルの背景色に合う数値の合計を求めるプログラムです。
※下記の赤色の背景色の合計値を求めます。（セル範囲じは、A2～D９の範囲です。）
※下記の赤色の背景色の合計値を求めます。（セル範囲じは、A2～D９の範囲です。）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub ColorRange() '指定した背景色を集計 Dim Gokei As Long Dim ColorRange As Range For Each ColorRange In Range("A2:D9") If ColorRange.Interior.ColorIndex = 3 Then Gokei = Gokei + ColorRange End If Next ColorRange MsgBox "赤色のセルの合計値は" & Gokei & "です。" End Sub |
●実行結果 ：実行後、赤色の背景色のセルの合計値を求めました。
赤色の背景色・計算内訳（２０＋１０＋１０＋１０＋２０＝７０）
赤色の背景色・計算内訳（２０＋１０＋１０＋１０＋２０＝７０）
同じセルの背景色同士の集計・カウント②
●プログラム説明 （サンプルプログラム②）
下記のサンプルプログラムは、下表に色分けしている支店毎のデータが勘定科目の列にデータとして登録されています。このデータは、背景色（支店）で分けられており、支店毎の合計金額を集計して求めるプログラムです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Sub ColorRange02() '支店毎に色分けした合計値を算出します。 Dim Gokei, ColorA, I As Long Dim ColorRange As Range For I = 2 To 6 'A支店⇒E支店までループ（２列⇒６列） Gokei = 0 '合計値を０にする。 ColorA = Cells(16, I).Interior.ColorIndex '支店の色を取得する。（１６行名） For Each ColorRange In Range("B4:G13") '表の範囲をしていします。 If ColorRange.Interior.ColorIndex = ColorA Then Gokei = Gokei + ColorRange '対象の背景色（支店）の数値を加算します。 End If Next ColorRange Cells(17, I) = Gokei '背景色毎（支店）に集計した合計値を１７行目の合計金額に代入します。 Next I End Sub |
●実行前～実行後 ※プログラム実行後、勘定科目の毎に登録されている支店データ（値）を集計して、各支店毎に合計値を算出しました。
同じセルの背景色同士の集計・カウント③
●プログラム説明 （サンプル③）
下記のサンプルプログラムは、下表にシフト勤務表があります。AシフトとBシフトは背景色を色分けして区別しています。勤務日ごとにAシフトとBシフトの勤務人数を集計するプログラムです。勤務表内に休日も表示していますので、休日のカウントも出来る様に作成しております。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Sub ColorRange03() '勤務表集計 Dim Gokei, ColorA, I, L As Long Dim ColorRange As Range For I = 3 To 12 'NO.1～10をループ Gokei = 0 '合計値を０にする。 ColorA = Cells(2, "R").Interior.ColorIndex '[R2]のセルの背景色を取得する。 For Each ColorRange In Range("C" & I & ":Q" & I) '表の範囲(行ごと) If ColorRange.Interior.ColorIndex = ColorA Then Gokei = Gokei + 1 '個別の休日を加算します。 End If Next ColorRange Cells(I, "R") = Gokei '個別の休日の合計日数を代入します。 Next I '--------------------------------------------------------------------- For L = 13 To 14 ' シフト勤務 A.Bをループ For I = 3 To 17 '1日～15日をループ Gokei = 0 '合計値を０にする。 ColorA = Cells(L, "B").Interior.ColorIndex 'シフト勤務のセルの背景色を取得する。 For Each ColorRange In Range(Cells(3, I), Cells(12, I)) '表の範囲(列ごと) If ColorRange.Interior.ColorIndex = ColorA Then Gokei = Gokei + 1 '個別のシフト勤務を加算します。 End If Next ColorRange Cells(L, I) = Gokei '個別のシフト勤務の合計日数を代入します。 Next I Next L End Sub |
●実行前～実行後 ※プログラム実行後、勤務表にあるAシフト勤務・Bシフト勤務を日別に集計を行い、勤務者ごとに休日日数もカウントしました。文字列より、色分けする事により見た目で勤務状況を把握する事が出来ます。
最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。