PR

【VBA】データバー表示と削除、コピー元範囲指定を解除する

これで楽に！？VBA活用
2017.11.14
記事内に広告が含まれています。

こんにちは、Ryoです。
何ともごちゃ混ぜなタイトルになってしまいましたが、Excelのシート上で集計したデータなどを容易に視認化できるデータバー表示というものがあり、これをVBAで処理することも出来ますので、結果出力の際に内容によっては有効活用出来るかと思います。

今回はそのデータバーの表示、及び削除と併せてサンプルコード内で使用するコピー元範囲指定（コピーモード）の解除について書いていきたいと思います。

スポンサーリンク

1.サンプル概要

◆Sample1・・・セルデータ上にデータバー表示

以下画像に示す表があるとして、その表内「金額」欄にデータバー表示するものです。

この表に対しサンプルコードを実行すると、データバーが表示されます。

◆Sample2・・・隣接セルにデータバー表示

Sample1ではデータ上に表示させましたが、場合によっては見難くなってしまいますので、隣接するセルにデータバーを表示して視認性を良くしたいケースもあるかと思います。
その場合のサンプルとして、以下の表内「簡易グラフ表示」欄にデータバーを表示します。

この表に対しサンプルコードを実行すると以下になります。

私個人としては見やすさもあって、こちらの方法で使うことが多いですね。
他にはデータバー削除や、Sample2の処理内でタイトルにも書きましたコピーモードの解除にも触れていきたいと思います。

2.サンプルコード

◆Sample1

Sub Sample1()

  '指定範囲（セルE4～E10)にデータバーを追加する
  Range(Cells(4, 5), Cells(10, 5)).FormatConditions.AddDatabar

  With Range(Cells(4, 5), Cells(10, 5)).FormatConditions(1)
     .ShowValue = True
     '最小値を指定範囲内の最小値に比例させる
     .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
     '最大値を指定範囲内の最大値に比例させる
     .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
     'データバーの色を青色にする
     .BarColor.Color = RGB(0, 0, 255)
  End With
  
End Sub

セル上にデータバーを表示する為にはAddDatabarメソッドを使います。

オブジェクト.AddDatabar

これは条件付き書式にデータバーを設定するもので、本サンプルでは指定したセル範囲内の数値に対して設定しています。

先ず指定範囲にデータバーを設定しますので、
Range(Cells(4, 5), Cells(10,5)).FormatConditions.AddDatabarの様に記述し、データバーを追加します。

次にデータバーに対する処理になりますが、 .ShowValue = Trueはデータバーを表示する際に元のデータ値を表示する指定なので、非表示とする場合はFalseとします。
（Withを使っていますので、Range(Cells(4, 5), Cells(10, 5)).FormatConditions(1)に続く記述として読んでもらえれば良いと思います）

MaxPoint、MinPointプロパティについては、Modifyメソッドを使ってデータバーに関する設定を行います。

.MinPoint.Modify newtype:=xlConditionValueAutomaticMin（最小値に比例）

.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax（最大値に比例）

このように引数を指定していますが、他にも以下の指定が出来ます。

定数 内容
xlConditionValueAutomaticMax 範囲の最大値に比例（最長データバー）
xlConditionValueAutomaticMin 範囲の最小値に比例（最短データバー）
xlConditionValueFormula 数式使用
xlConditionValueHighestValue 値一覧の最高値
xlConditionValueLowestValue 値一覧の最低値
xlConditionValueNone 条件値なし
xlConditionValueNumber 数字使用
xlConditionValuePercent パーセンテージ使用
xlConditionValuePercentile 百分位使用

指定に関しては処理内容に応じて適宜指定して使用していけば良いと思いますが、私の場合は運用上セル上で簡易的に視認出来れば良いので、xlConditionValueAutomaticMin(Max)で事足りている都合上、他の指定については正直よくわかりません（笑）

後はデータバーにカラーをRGB関数を使って指定します。
.BarColor.Color = RGB(0, 0, 255)とすることで、青色の指定になります。
（RGB（赤、緑、青））

◆Sample1用データバー削除

指定範囲のデータバーを削除するには以下の様にDeleteで指定します。

Sub Sample1_Delete()

  Range(Cells(4, 5), Cells(10, 5)).FormatConditions.Delete

End Sub

削除したい範囲を指定し、.FormatConditions.Delete とすることで削除できます。

◆Sample2

本サンプルはデータバーにする値を隣にコピーし、コピーしたデータの値を非表示にしてデータバーを表示するものです。

Sub Sample4()

  '表内データバー欄に「値のみ」をコピーする
  Range(Cells(4, 5), Cells(10, 5)).Copy
  Cells(4, 6).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
  SkipBlanks:=False, Transpose:=False
  
  'コピー元の範囲指定（コピーモード）を解除
  Application.CutCopyMode = False

  '指定範囲（セルE4～E10)にデータバーを追加する
  Range(Cells(4, 6), Cells(10, 6)).FormatConditions.AddDatabar

  With Range(Cells(4, 6), Cells(10, 6)).FormatConditions(1)
     '数値の非表示指定
     .ShowValue = False
     '最小値を指定範囲内の最小値に比例させる
     .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
     '最大値を指定範囲内の最大値に比例させる
     .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
     'データバーの色を青色にする
     .BarColor.Color = RGB(0, 0, 255)
  End With
  
  'セル範囲選択状態を解除
  Cells(3, 2).Select
  
End Sub

先ずデータをコピーし、隣の先頭行を選択して「値のみ貼付け」を行います。

その次にコピー先を選択して、そこに以下の指定で貼付けを実行します。

Cells(4, 6).Select 　’コピー先先頭セル
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

本題と反れてしまうので詳細な解説は割愛しますが、この記述で「値のみ」貼付けを実行しますので、数式などがある場合は有効な手段になります。
「形式を選択して貼り付け」などは、いずれ別記事でまとめてみようと思います。

この貼付けを実行しデータバー表示の処理を進めても良いのですが、その場合は以下画像のようにコピー元範囲指定（コピーモード）が維持された状態となります。

この状態で処理が完了すると何かと不都合もありますから解除する必要があります。
解除を行うには、

Application.CutCopyMode = False

このようにApplication.CutCopyModeプロパティを使い、False指定することによって解除することが出来ますので、知っておくと役に立つ機会も多いですからお薦めです。

以降の記述は値を非表示とするので、.ShowValue = Falseとしている点とデータバー指定範囲が隣接セルに変わっている点以外、Sample1同様です。

最後に記述しているCells(3, 2).Selectは、データバー全範囲が選択状態で終わってしますので、その選択範囲を解除する目的です。

◆Sample2用データバー削除

基本的にSample1同様なのですが、隣接セルにコピーしてデータバーを表示させている都合上、データバーのみ削除するとデータが残ってしまうので、ClearContentsでクリアする処理を追加したものです。

Sub Sample2_Delete()

  With Range(Cells(4, 6), Cells(10, 6))
       .FormatConditions.Delete
       .ClearContents
  End With

End Sub

３．まとめ

サンプルのような表であれば少ない情報なので敢えて視認化する必要もないのですが、実運用上では相当数のデータを取り扱うことも多いですし、表示する上での判りやすさも大事なことですから、データバーを有効活用する機会も多々あると思います。

VBAでの処理自体はサンプルで示した通り、特異なこともありませんから直ぐに使えますが、私の経験上では「使いすぎても逆に見難くなる」こともありますので、ご注意を（笑）
また、コピーモード解除方法は地味に使えますので機会あればご活用ください。




以上、データバー表示と削除、コピー元範囲指定を解除する方法についてでした！
今回の記事が何かの参考になれば幸いです。

Ryo

スポンサーリンク
タイトルとURLをコピーしました