データサイエンティストを超えた！Excelの条件付き書式設ティストに俺はなる！〜VBA行別列別条件付き書式設定自動化マクロ

VBA

闇の中答えを探し求めている〜こんにちはVBA界の若頭です。



データ解析しているとExcelのことが愛おしくなって、「お前だけはもう離さない」なんてことになりますよね。
そんな僕らの友達、Excelの素晴らしい機能の1つに「条件付き書式設定」があります。
これはExcelのセルの色を指定の範囲内で数値に応じて塗り替える機能です。




(総務省「家計調査」より引用 )



データ解析の基本は散布図とヒストグラムなんて言われますが、こいつを使うと複数の変数間の関係を可視化できるので、
業務でデータを解析している人にとっては、必須のツールですね。
最近は統計学とかデータマイニングが流行っていて、数学のわかる人が幅をきかしているみたいですが、これだけ見ておけばよいです。
高度なデータマイニング手法なんておまけみたいなもんです。って最近自分に言い聞かせています。
目指せExcelの条件付き書式設ティストということです。


で、これは指定したセルの範囲内の値に応じてデータを分割して色を分けるのですが、


１．行に個別の顧客が並んでいて、列に売上や単価や来店頻度が並んでいる時等に、顧客を比較する時には変数の単位が異なるので列別に条件付き書式設定を、
２．行に個別の商品が並んでいて、列に各月別の売上が並んでいる時等に、時系列での売上比較のために、行別で条件付き書式設定を、


行いたいことがあります。


下の例は2011年度の家計調査の消費項目別都市階級別の支出の値です。
行に支出項目が、列に都市階級が並んでいます。この場合先の１．と２．はそれぞれ


１．列別条件付き書式設定：パンやコメを比較する。

小都市B・町村ではご飯が、それ以外ではパンの方が人気なようです(総務省「家計調査」より引用 )


２．行別条件付き書式設定：大都市や小都市比較する。

ご飯は【大都市＜中都市＜小都市A＜小都市B・町村】
パンは【中都市＞大都市＞小都市A＞小都市B・町村】
単に反比例しているのではなく、ピークが中都市になっています
(総務省「家計調査」より引用 )


ということになります。同じデータをこうして簡単に見るだけでも異なる結論が簡単に導けます。
本当に強力なツールです。


これくらいのサイズのデータならすぐに手で設定しても出来るのですが、
データ解析をしているとこれが200×200のマトリックスになったりして、
（実際にこのExcelデータも消費項目数が200くらいあります。）
一つ一つやっているととても骨が折れます。
そこで、この行別列別の書式設定を自動化するマクロを作りました。


使い方は以下の通り、

１．マクロを実行（やり方はバージョンに応じてググりましょう。私は2007を使用しています。）
２．セルの選択を求められるので、
　行別の場合は条件付き書式設定を設定したい行の一番上の行の範囲のセルを、
　列別の場合は条件付き書式設定を設定したい列の一番左の列の範囲のセルを、
　　選択して、OKを押す。
３．行別の場合は行数、列別の場合は列数を入力してOKを押すと自動的に条件付き書式設定を設定してくれます。

Sub RepeatConditionalFormat()
    On Error GoTo ErrorHandler
    
    ' 条件付き書式設定を適用するタイプ
    Dim verticalhorizontype As Integer
    verticalhorizontype = 0
    
    ' 条件付き書式設定を適用するセルを選択する。
    Dim ran As Range

    Set ran = Application.InputBox(Prompt:="条件付き書式設定を適用するセルの開始範囲を選択してください(行数が１もしくは列数が１の範囲のみOK）", Type:=8)
    ' エラーチェック
    If ran.Columns.Count > 1 And ran.Rows.Count = 1 Then
        ' 行数が１の場合は、縦に条件付き書式設定を適用していく。
        verticalhorizontype = 0
    ElseIf ran.Columns.Count = 1 And ran.Rows.Count > 1 Then
        ' 列数が１の場合は、横に条件付き書式設定を適用していく。
        verticalhorizontype = 1
    Else
        ' それ以外の場合はエラー
        Err.Raise 1
    End If
    
    
    ' 条件付き書式設定を適用する行数、列数を選択する。
    Dim num As Integer
    num = Application.InputBox(Prompt:="条件付き書式設定を適用する行数、列数を選択してください。", Type:=1)
    
    ' 条件付き書式設定を適用する
    For i = 1 To num
        ran.FormatConditions.AddColorScale (3)
        Set ran = ran.Offset(1 - verticalhorizontype, verticalhorizontype)
    Next
    Exit Sub
ErrorHandler:
     MsgBox ("セルの入力範囲は行数、列数のどちらか一方のみ１にしてください。")
End Sub


今後、ボタン1つで出来るように改良したいと思います
Excelの条件付き書式設ティストに俺はなる！



*多分、2007以上じゃないと動かないと思われます。