今回はスパークラインの作成と動的にデータ範囲を変更方法をご説明します。
スパークラインのデータ範囲を指定する方法は「A1形式」で記載する必要があります。
そのため、ただ最終行と最終列を取得すればよいという単純なコードでは対応できません。
スパークラインの作成方法と動的にデータ範囲を設定する方法を順にご説明します。
1.スパークラインとは？
リボンにもありますが、意外と知られていないスパークラインについてご説明します。
スパークラインはExcel 2010で追加された新しい機能です。
グラフをセル内に表示します。
折れ線、縦棒、勝敗の３種類を設定する事ができ、折れ線と縦棒はセルの値を範囲内の相対でグラフ化します。
勝敗はセルの値の大きさではなく正か負でグラフ化されます。
グラフを作成しなくても「視覚化」出来るので、数値だらけのデータを見やすくします。
記述方法は次のように記述します。
SparklineGroups.Add(Type,SourceData)
タイプ一覧
| スパークラインのタイプ | 説明 |
| xlSparkLine | 折れ線 |
| xlSparkColumn | 縦棒 |
| xlSparkColumnStacked100 | 勝敗 |
スパークラインのメソッド一覧
| メソッド | 説明 |
| Add | 指定した範囲にスパークラインを追加します。 |
| ClearGroups | スパークライングループを削除します。 |
| Clear | 指定した単体のスパークラインを削除します。 |
| Ungroup | 指定したセルまたはセル範囲のスパークライングループを解除します。 |
| Group | 指定したセル範囲のスパークラインをグループ化します。 |
2.スパークラインの作成方法
スパークラインを作成するには、「SparklineGroups」オブジェクトの「Add」メソッドを使用します。
固定のデータ範囲のスパークラインを作成するコードです。
データは先ほどのスパークラインのデータを使用します。
折れ線スパークライン
Sub Sample1()
Range("H2:H6").SparklineGroups.Add _
Type:=xlSparkLine, SourceData:="B2:G6"
End Subこの様にH列にスパークラインが追加されました。
縦棒スパークライン
次は縦棒です。
スパークラインのTypeを変更します。
Sub Sample2()
Range("H2:H6").SparklineGroups.Add _
Type:=xlSparkColumn, SourceData:="B2:G6"
End Sub
3.スパークラインのデータ範囲を動的に指定する
文頭でも記載しましたが、スパークラインは「A1形式」で記載する必要があります。
そのため、動的に行列を動的にするのが意外と難しいです。
方法としては、最終行と最終列を数値で取得してから「A1形式」で記載出来るように、列を数値からA1形式に変換してデータ範囲を指定します。
それぞれ順番に説明します。
列をA1形式に変換
まずは取得した列の数値をA1形式に変換するコードを先にご説明します。
下記コードを標準モジュールに記述します。
Option Explicit
Function ConToLet(ByVal ColNo As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int((ColNo - 1) / 26)
iRemainder = ColNo - (iAlpha * 26)
If iAlpha > 0 Then
ConToLet = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConToLet = ConToLet & Chr(iRemainder + 64)
End If
End Function「Function」プロシージャで作成します。
「(ByVal ColNo As Integer)」で受け取った値をA1形式に変換します。
動的にスパークラインを作成
最終行と最終列を数値で取得してから、上記で作成した「Function ConToLet」プロシージャに最終列の数値を渡して、変換後にスパークラインのデータ範囲を指定します。
Sub Sample3()
Dim MaxRow As Long
Dim MaxCol As Long
Dim EndCol As String 'アルファベット変換
Dim OutCol As String 'スパークライン出力列
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列を取得
EndCol = ConToLet(MaxCol - 1) '範囲の最終列をアルファベットに変換
OutCol = ConToLet(MaxCol) 'スパークライン出力列をアルファベットに変換
Range(OutCol & "2:" & OutCol & MaxRow).SparklineGroups.Add _
Type:=xlSparkLine, SourceData:="B2:" & EndCol & MaxRow
End Sub「MaxRow = Cells(Rows.Count, 1).End(xlUp).Row」で最終行を取得しています。
「MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column」で最終列を取得します。
「EndCol = ConToLet(MaxCol – 1)」で「Function ConToLet」プロシージャ に値を渡してA1形式に変換します。
-1しているのはスパークラインの列を含めないようにするためです。
「OutCol = ConToLet(MaxCol) 」でスパークライン出力列をアルファベットに変換します。
最後にスパークラインのデータを設定して作成します。
