この記事では、条件付き書式のデータバーを使ってフォルダにあるファイルサイズを横棒グラフ表示させる方法についてご説明します。
今回の記事でご紹介するマクロは、は以下のマクロを活用しています。
【ExcelVBA】セルの値を横棒グラフで表示させるには(条件付き書式のデータバーを使用)
上の記事でもご説明していますが、今回扱う横棒グラフとは、条件付き書式のデータバーのことを指します。
下の画像が条件付き書式のデータバーが出力された結果です。
見た目は横棒グラフのように見えて、ファイルサイズの値の大小が確認しやすくなっているのが特徴です。
【動画】条件付き書式のデータバーを使ってフォルダにあるファイルサイズを横棒グラフ表示させる実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずは指定されたファイルの置き場のファイル名とファイルのサイズを取得してExcelのシートに出力します。
ファイルサイズが出力されたセルの値を参照し、条件付き書式のデータバーを使ってセルの値を横棒グラフでD列に表示させています。
マクロ作成の流れ
今回のマクロでは次の設定を行います。
・データバーが表示されているセルには値を表示させない
・データバーの最大値(今回は100)と最小値(今回は0)を設定する
・データバーに色を設定する(今回はオレンジ色)
・データバーを塗りつぶし設定にする
Excelファイルの例
今回は次のExcelファイルを用意しました。
A2の黄色いセルに、ファイルサイズを取得したいファイルの置き場を入力して実行ボタンをクリックすると、下のとおりにフォルダにあるファイルサイズが横棒グラフで表示されます。
ちなみに、A2の黄色いセルに入力されたファイルの置き場は下のとおりです。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'ワークシート用変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim folder As Object 'フォルダ用変数
Dim file As Object 'ファイル用変数
Dim filePath As String 'ファイルのパスを格納する変数
Dim cnt As Integer 'カウンタ用変数
Dim valRng As range 'Rangeオブジェクト格納用変数(ファイルのサイズと項番用)
Dim dataRng As range 'Rangeオブジェクト格納用変数(データ出力範囲用)
Dim maxVal As Double 'データバーの最小値用変数
'データバーの最小値を設定
Const minVal As Double = 0
'データを出力する開始行位置を設定
Const bgnLPos As Long = 8
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'セルをクリアする
ws.range("A" & bgnLPos & ":CA1048576").ClearContents
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
'データを出力する開始行位置の値でカウンタを初期化する
cnt = bgnLPos
'Folderオブジェクトを取得する
Set folder = fso.GetFolder(filePath)
'フォルダ内のファイルの数分処理を行うFor文
For Each file In folder.files
'B列にファイル名を出力する
ws.range("B" & cnt).Value = file.Name
'C列にファイルサイズをメガバイト単位で出力する(小数点以下8桁まで)
ws.range("C" & cnt).Value = Format(file.Size / 1024 / 1024, "0.00000000")
cnt = cnt + 1
Next file
'データバーを表示させる(D列の)セルの範囲を取得する
Set valRng = ws.range("D" & bgnLPos & ":D" & bgnLPos + folder.files.Count - 1)
'セルに計算式を設定する(C列のセルの値を参照する数式)
valRng.Formula = "=C" & bgnLPos
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
'B列からD列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":D" & bgnLPos + folder.files.Count - 1)
'ファイルサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(2), Order1:=xlDescending, Header:=xlNo
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を100に設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
'A列の項番を表示させるセルの範囲を取得する
Set valRng = ws.range("A" & bgnLPos & ":A" & bgnLPos + folder.files.Count - 1)
'セルに計算式を設定する(A列のセルに項番を出力させる数式)
valRng.Formula = "=row()-" & bgnLPos - 1
End Sub
注目すべきコード①
最初に見て頂きたいのは41行目から55行目です。
If Right(ws.range("dirPath").Value, 1) = "\" Then
'入力された「ファイルの置き場」のパスの末尾に「\」が付いている場合
'「ファイルの置き場」のパスを変数filePathに格納する
filePath = ws.range("dirPath").Value
Else
'入力された「ファイルの置き場」のフルパスの末尾に「\」が付いていない場合
'「ファイルの置き場」のパスの末尾に「\」を付けて、パスを変数filePathに格納する
filePath = ws.range("dirPath").Value & "\"
End If
コードの説明
以上のコードは、ファイルの置き場のパスを取得するコードです。
トップフォルダのパスは、A2のセル(dirPathという名前付きセル)に入力されたパスから取得しています。
※A2の黄色のセルには「dirPath」という名前を付けています。
コードの詳細
30行目のコードは、A2セルに入力されたパスの末尾に「¥」の文字が含まれているか判定するIFです。
もし「¥」の文字が含まれている場合は、35行目で変数filePathにそのままパスを格納します。
「¥」の文字が含まれていない場合は、42行目でパスの末尾に「¥」を付けて変数filePathに格納します。
注目すべきコード②
次に見て頂きたいのは50行目から63行目です。
'Folderオブジェクトを取得する
Set folder = fso.GetFolder(filePath)
'フォルダ内のファイルの数分処理を行うFor文
For Each file In folder.files
'B列にファイル名を出力する
ws.range("B" & cnt).Value = file.Name
'C列にファイルサイズをメガバイト単位で出力する(小数点以下8桁まで)
ws.range("C" & cnt).Value = Format(file.Size / 1024 / 1024, "0.00000000")
cnt = cnt + 1
Next file
コードの説明
以上のコードは、指定されたファイルの置き場のフォルダ内のファイル名とファイルサイズをすべて取得してExcelのシートに出力する処理のコードです。
コードの詳細
50行目のコードでは、GetFolderメソッドの引数に指定されたファイルの置き場を指定して実行し、ファイルの置き場の情報取得しています。
生成されたfolderのインスタンスから、ファイルの名前やファイルサイズを取得することができます。
52行目のコードは、指定されたファイルの置き場にあるファイルの数だけ処理を繰り返すFor文です。
56行目のコードでは、指定されたファイルの置き場にあるファイル名をB列に出力します。
59行目のコードでは、指定されたファイルの置き場にあるファイルのサイズをC列に出力します。
なお、ファイルのサイズはMB単位で計算し、その値を小数点以下8桁までとして表示させています。
注目すべきコード③
次に見て頂きたいのは66行目から69行目です。
'データバーを表示させる(D列の)セルの範囲を取得する
Set valRng = ws.range("D" & bgnLPos & ":D" & bgnLPos + folder.files.Count - 1)
'セルに計算式を設定する(C列のセルの値を参照する数式)
valRng.Formula = "=C" & bgnLPos
コードの説明
以上のコードは、データバーを表示させる(D列の)セルの範囲を取得するコードです。
データバーを表示させるのは、列はD列で先頭行はbgnLPosの値(今回は8行目)を行位置とし、その行から下にデータ件数から1を引いた値の行位置まで(最終行)としています。
また、上の画像のとおりC列の値を、D列のセルにグラフ表示させたいので、69行目でファイルサイズが出力されているC列のセルに対して「”=C” & bgnLPos」の数式を設定しています。(bgnLPosはデータの開始行の行位置。(今回のマクロでは「=C8」))
この数式の設定は、左隣のセルを参照するために行います。
C列のセルはファイルサイズの値を、D列は隣のC列のファイルサイズの値をグラフ表示したいので左隣のセルを参照するよう「”=C” & bgnLPos」と数式を設定しています。
ちなみに、データの最終行までのセルに「”=C” & bgnLPos」の数式を設定すると、C列データが存在している行までのセルすべてに「”=C” & bgnLPos」が設定されてしまい、すべてC列のファイルサイズが入っている先頭行の値を参照するのでは？と思われるかもしれませんが、そうはならずに例えばD9のセルなら「=C9」、D19のセルなら「=C19」と設定されます。(相対参照により、D列それぞれの左隣のセルを参照する)
注目すべきコード④
次に見て頂きたいのは72行目です。
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
コードの説明
以上のコードは、C列に出力されたファイルサイズから最大値を取得するコードです。
WorksheetFunction.Maxメソッドに、ファイルサイズが入ったセルの範囲を引数に指定することで、最大のファイルサイズを取得することができます。
最大のファイルサイズはデータバーの表示も最大にするため、最大値を取得します。
ちなみに、最大値のファイルサイズのグラフは、セルいっぱいに表示されます。
注目すべきコード⑤
次に見て頂きたいのは75行目から78行目です。
'B列からD列のセル範囲を取得する
Set dataRng = ws.range("B" & bgnLPos & ":D" & bgnLPos + folder.files.Count - 1)
'ファイルサイズでソートさせる(降順)
dataRng.Sort Key1:=dataRng.Columns(2), Order1:=xlDescending, Header:=xlNo
コードの説明
以上のコードは、B列からD列のセル範囲を取得し、2列目(C列)の値で降順にソートする処理のコードです。
今回はファイルサイズが大きい順で表示させるためファイルサイズをキーに降順でソートしています。
注目すべきコード⑥
次に見て頂きたいのは81行目です。
'既存の条件付き書式設定を削除する
valRng.FormatConditions.Delete
コードの説明
以上のコードは、既存の条件付き書式設定を削除する処理のコードです。
条件付き書式のデータバーを表示させるセルに対して、既存の条件付き書式設定を削除しておきます。
注目すべきコード⑦
次に見て頂きたいのは83行目から100行目です。
With valRng.FormatConditions.AddDatabar
'データバー内に値を表示しないようにする(Trueなら表示させる)
.ShowValue = False
'データバーの最小値を0に設定する
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=minVal
'データバーの最大値を100に設定する
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=maxVal
'データバーの色を設定する(オレンジ色を設定)
.BarColor.Color = RGB(255, 153, 0)
'データバーを塗りつぶし状態に設定する(xlDataBarFillSolidは塗りつぶし)
.BarFillType = xlDataBarFillSolid
End With
コードの説明
以上のコードは、条件付き書式のデータバーの表示とデータバーの各設定を行う処理のコードです。
コードの詳細
83行目のコードは、条件付き書式のデータバーを表示させるコードです。
86行目のコードでは、データバー内に値を表示しないよう設定します。
なお、Trueならデータバー内に値を表示させます。
89行目のコードではデータバーの最小値を、92行目のコードではデータバーの最大値を設定します。
今回のコードでは、最小値と最大値を以下のコードで設定しています。(最小値は0、最大値はファイルサイズの最大値)
'データバーの最小値を設定
Const minVal As Double = 0
'データバーの最大値を設定
maxVal = Application.WorksheetFunction.Max(valRng)
95行目のコードでは、データバーの色オレンジに設定します。
98行目のコードでは、データバーを塗りつぶし状態に設定します。
動作確認
「Excelファイルの例」をご覧ください。
最後に
この記事では、条件付き書式のデータバーを使ってフォルダにあるファイルサイズを横棒グラフ表示させる方法についてご説明しました。
条件付き書式のデータバーを使うと、ファイルサイズの大小が見て分かりやすいようになります。
条件付き書式のデータバーを使ってフォルダにあるファイルサイズを横棒グラフ表示させたい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
