EXCEL VBA Union関数でデータの複数範囲を一つにまとめる!・条件に基づくセル色の変更
EXCEL VBA Union関数でデータの複数範囲を一つにまとめる!
今回説明するサンプルプログラムは、Union関数に関する内容について説明いたします。Union関数は複数の範囲を一つにまとめることができ、データ操作を格段に便利にします。大量のデータセットを扱う際や、様々な範囲に分散したデータを一元化したい場合など、Union関数の活用は無限大です。今回のサンプルプログラムは、Union関数の基本的な使い方や初心者でも理解できるように解説します。それでは、サンプルプログラムを交えて順番に説明いたします。
●【EXCEL VBA セルで選択した場所に画像を挿入については、下記を参照して下さい】
●【UNIONメゾットについて (Excel)、下記を参照して下さい】(Microsoft社 様)】
https://learn.microsoft.com/ja-jp/office/vba/api/excel.application.union
● Union関数を利用するには、下記の通りに設定を行います。
【構文説明】
Union(Arg1, Arg2, …, Arg30)
この関数では、引数として最大30の範囲を指定できます。
【引数の詳細】
Arg1, Arg2, …, Arg30: 範囲を指定する引数です。これらは範囲オブジェクト、つまりExcelのセルやセルの集合を指定します。
【サンプルプログラムの詳細説明】
下記のサンプルプログラムでは、Union関数を用いて、”A1:C3″と”D4:F6″の2つの範囲を一つにまとめ、その範囲全体の背景色を黄色に変更します。
1 2 3 4 5 6 7 8 9 |
’ ’ Sub Sample1() Union(Range("A1:C3"), Range("D4:F6")).Interior.Color = RGB(255, 255, 0) End Sub ’ ’ |
【Union関数の注意点】
① Union関数は同じワークシート内の範囲のみを結合することができます。別のワークシートの範囲を結合することはできません。
② Union関数で結合する範囲に数値以外の値(文字列やエラー値など)が含まれていると、数値計算関数(Max、Minなど)の使用時にエラーが発生します。
③ Union関数の引数は最大で30までです。それ以上の範囲を結合したい場合は、別の方法を考える必要があります。
④ Union関数で結合した範囲内のセルに対して行う操作(色の変更、値の設定など)は、結合したすべてのセルに対して適用されます。
⑤ Union関数は、範囲が重複する場合でも問題なく動作します。しかし、重複する範囲に対して何らかの操作を行うと、その操作は重複する回数だけ適用されます。
EXCEL VBA Union関数でExcel内の特定範囲の数値を一気に集計する!
下記のサンプルプログラムは、ExcelのUnion関数を活用し、2つの指定した表範囲(“B4:D6″および”G4:I6”)のセル内の数値を集計し、その合計をメッセージボックスで表示します。Union関数を使う事で、同一のワークシートでの離れたセル範囲を選択する事ができるので、セル範囲が離れているときにが集計する時に便利だと思います。今回のサンプルプログラムでは、集計結果を合計値としてメッセージボックスに表示されます。
【プログラムの流れ】
① cellおよびtotalという2つの変数を宣言します。
② Union関数を使用して範囲”B4:D6″と範囲”G4:I6″を結合します。
③ For Eachループを用いて結合した範囲の各セルを処理し、各セルの値を変数totalに加算します。
④ 集計結果をメッセージボックスに合計値として表示します。
【プログラム実行条件・注意事項】
① 指定した範囲内のセルに数値以外の値(文字列やエラー値など)が含まれている場合、エラーが発生します。
② Union関数は同じワークシート内の範囲のみを結合できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
' ' Sub Sample10() '2つのセル範囲の合計値を集計する。 Dim cell As Range 'セルを参照するための変数を宣言 Dim total As Double '合計値を保存するための変数を宣言 'Union関数で2つの範囲を結合し、For Eachループで各セルを処理 For Each cell In Union(Range("B4:D6"), Range("G4:I6")) total = total + cell.Value '各セルの値を合計値に加算 Next cell '合計値をメッセージボックスで表示 MsgBox "合計 : " & total End Sub ' ' |
(画面クリックして拡大)
EXCEL VBA Union関数を使った条件に基づくセル色の変更・数値による色分け方法
下記のサンプルプログラムは、Excelのセル範囲”B4:D6″および”G4:I6″の値をチェックし、その値に基づいてセルの背景色を変更します。値が80以上の場合は、背景色をRGB(144, 238, 144)(薄い緑色)に変更し、値が50以下の場合は、背景色をRGB(255, 0, 0)(赤色)に変更します。
【プログラムの流れ】
① Range型の変数rngを定義します。
② ”B4:D6″と”G4:I6″の範囲にあるすべてのセルに対してループ処理を実行します。
③ セルの値が80以上の場合、そのセルの背景色を薄い緑色に変更します。
④ セルの値が50以下の場合、そのセルの背景色を赤色に変更します。
【プログラム実行条件・注意事項】
① このプログラムは数値のみに対応しています。文字列や日付など数値以外のセルの値が含まれるとエラーが発生します。
② 51から79の範囲の値に対しては、何も処理を行いません。その範囲の値に対する色の変更が必要な場合、コードを修正する必要があります。
③ コードは特定のセル範囲に対してのみ動作します。他のセル範囲に対しても同様の操作を行いたい場合は、コードを修正する必要があります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
’ ’ Sub Change_Background_Color()’条件により背景色を設定 Dim rng As Range 'Range型の変数を定義 ' "B4:D6"と"G4:I6"のセル範囲に対してループ処理 For Each rng In Union(Range("B4:D6"), Range("G4:I6")) ' 値が80以上なら背景色を薄い緑色に If rng.Value >= 80 Then rng.Interior.Color = RGB(144, 238, 144) ' 値が50以下なら背景色を赤色に ElseIf rng.Value <= 50 Then rng.Interior.Color = RGB(255, 0, 0) End If Next rng End Sub ’ |
(画面クリックして拡大)
EXCEL VBA データ分析: Union関数を使った円グラフ作成の自動化・データを結合する。
下記のサンプルプログラムは、2つの異なるセル範囲からラベルと値を取得し、それらを一つの円グラフにまとめます。具体的には、今回のサンプルプログラムでは、ラベルと値のセル範囲を指定し、それらのデータから円グラフを作成します。特定のセル範囲に格納されているラベル(2行目の2列目から6列目)と値(5行目の2列目から6列目)を取得し、それらを結合して一つのデータセットを作ります。次に、そのデータセットを元に円グラフを作成します。円グラフにはタイトルが付けられ、データラベルとしてパーセンテージが表示されます。
【プログラムの流れ】
① 必要なセル範囲(ラベルと値)を定義します。
② 指定したセル範囲からラベルと値を取得します。
③ 取得したラベルと値を結合して一つのデータセットを作成します。
④ 作成したデータセットを元に、新しい円グラフを作成します。
⑤ 円グラフにタイトルを設定します。
⑥ 円グラフのデータラベルとしてパーセンテージを表示します。
【プログラム実行条件・注意事項】
① 定数で指定されているセル範囲には、適切なデータが存在している必要があります。存在しないセル範囲を指定すると、エラーが発生します。
② このコードはアクティブシート(現在開いているシート)に対して作用します。他のシートに円グラフを作成したい場合は、適切にコードを修正する必要があります。
③ 円グラフに表示されるラベルと値は、指定したセル範囲のデータに依存します。円グラフの内容を変更するには、これらのセル範囲のデータを更新する必要があります。
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 |
' ' Sub Pie_Chart() 'Union関数を使って離れたデータで円グラフを作成 ' 必要な定数を設定 Const START_ROW_LABELS As Long = 2 Const START_COLUMN_LABELS As Long = 2 Const END_COLUMN_LABELS As Long = 6 Const START_ROW_VALUES As Long = 5 Const START_COLUMN_VALUES As Long = 2 Const END_COLUMN_VALUES As Long = 6 ' ラベルの範囲と値の範囲を定義 Dim labelRange As Range Dim valueRange As Range ' ラベルと値の範囲にデータを設定 Set labelRange = Range(Cells(START_ROW_LABELS, START_COLUMN_LABELS), Cells(START_ROW_LABELS, END_COLUMN_LABELS)) Set valueRange = Range(Cells(START_ROW_VALUES, START_COLUMN_VALUES), Cells(START_ROW_VALUES, END_COLUMN_VALUES)) ' 新しいチャートを作成 With ActiveSheet.Shapes.AddChart.Chart ' チャートの種類を設定(この場合は円グラフ) .ChartType = xlPie ' データソースを設定 .SetSourceData Source:=Union(labelRange, valueRange) ' タイトルを設定 .HasTitle = True .ChartTitle.Text = "項目別支出" ' データラベルを表示(パーセンテージのみ) .SeriesCollection(1).ApplyDataLabels ShowPercentage:=True, ShowValue:=False End With End Sub ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。