2021/04/05

出現回数ごとにデータを色分け

• １．背景
• ２．システム概要
• 　２－１．システム操作手順
• 　２－２．データ範囲への「条件付き書式」の確認
• 　２－３．SUMPRODUCT関数の使い方
• 　２－４．条件付き書式の数式の意味
• 　２－５．SUMPRODUCT以外の手法
• ３．プログラムの流れ
• ４．標準モジュール（Module1）
• 　４－１．変数・定数の宣言
• 　４－２．InputBoxでのデータ範囲の取得
• 　４－３．同じデータを持つセルの個数を配列に代入
• 　４－４．条件付き書式の作成
• 　　４－４－１．個数データを大きい方から取り出し
• 　　４－４－２．数式の書込み
• 　　４－４－３．書式の書込み
• ５．最後に

１．背景

例えば、図１－１のようなデータ（私が適当に作ったもの）があるとします。その日に「何が良く売れたか」を表にしたもののつもりです。

図１－１

データは取れたけど、パっと見ただけでは「何を示しているのか分からない」という時には「グラフ化」をします。Excelの大得意なところです。通常だったら図１－２のような積み上げ棒グラフがまず思いつきます。
棒グラフから分かることは「暑い時にはAが売れる」「寒い時にはBとCが売れる」くらいでしょうか。

図１－２

このグラフを、今回作ったシステムで処理したのが図１－３です。A・B・Cを色分けした様な処理です。図１－３で分かるのは、棒グラフの「暑い時はA」「寒い時はBとC」に加え、「Bは休日に売れる」「Cは平日に売れる」という傾向もあるように見えます。
このように「グラフさえ作れば、全てが解析できた」と思うのは危険で、データの大切な「メッセージ」を見逃している可能性があるのです。

図１－３

実は図１－３は、「濃いオレンジ色」は「B」に対して色付けしているのでは無く「出現回数の最も多い種類」であることを表してます。また、少し薄いオレンジ色は「出現回数が２番目に多い種類」、最も薄いのは「３番目」を表します。

今回は３種類のデータでしたので「種類に対する色分け」っぽくなってしまいましたが、多種類のデータを今回システムで処理することで、「出現の多い種類の把握」と同時に「その出現条件」や「分布」みたいなものも見えてくると思い、今回紹介することにしました。

なお、図１－３は「文字データ」でしたが、「数値データ」でも同様に処理できます。時系列データ等ではあまり意味が無いかもしれませんが、例えば「50代の人は〇〇の傾向がある」などの解析には使えるかもしれません。
また、図１－３は「出現回数の多い方から１～３番目」を色付けしましたが、もっと多く設定することも可能です。

２．システム概要

２－１．システム操作手順

本システムは、Excelのアドインに登録することを考慮し作っています。アドインに登録されていれば、図２－１のようにリボン上のボタンをクリックすることで、②のようなインプットボックスが現れます。
なお、サンプルファイルではシートの左上に起動用のボタンを配しています。

図２－１

インプットボックス②の中のテキストボックス枠内にカーソルがあることを確認し、対象とするデータ範囲（図２－２で言うと「C4：AM15」）を選択③します。選択範囲はインプットボックス内のテキストボックス枠に自動的に入力されます。

図２－２

範囲指定後「OKボタン⑤」をクリックすると、インプットボックス⑥が消え、データ範囲⑦に対して図２－３のように「出現回数の多いデータ」のセル背景色が変更になります。

図２－３

なお背景色変更について、今回システムでは以下のように設定をしています。
・範囲内で、出現回数の多いデータに対してセル背景色を変更
・最も回数の多いデータが濃い色、少なくなるに従って色も薄くなる（＝色が明るくなる）
・サンプルマクロでは、出現回数の多い順に３種に対して着色。なお、マクロの定数を調整することで色の濃淡の段階を変更可。
・なお、異なる種類なのに出現回数が同じ場合には、その異なる種類は同色になる（出現回数のみで、着色を決めています）。

２－２．データ範囲への「条件付き書式」の確認

システム内で行っているのは、「指定したデータ範囲内」に対して「条件付き書式」を設定しているだけです。言わば、手動でも実施できるものです。
今回システムで作った「条件付き書式」を確認するには、まず、条件付き書式が設定されているセル（今回で言えば、背景色がオレンジ色になっているセル）を選択した上で、図２－４のように「ホーム」タブ→「スタイル」グループ→「条件付き書式」を選択し、一番下の「ルールの管理」をクリックします。

図２－４

すると、図２－５のように「条件付き書式ルールの管理」が現れます。今回は「３種類」を設定するプログラムになっているので、３段の条件付き書式が作成されています。

図２－５

例えば、この３段の内一番上（濃いオレンジ色）の条件付き書式を選択し「ルールの編集」ボタンをクリックすると、図２－６のように「数式」を確認することが出来ます。

図２－６

数式は「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=138」です。SUMPRODUCT関数を使ったこの式の意味を次項で整理します。

２－３．SUMPRODUCT関数の使い方

まず「SUMPRODUCT関数」の使い方について説明します。１つ目の使い方は、図２－７のように「複数のセル範囲のそれぞれの値を掛け、最後にそれらを足し合わせる」というものです。図２－７ならば（100 x 119）+（200 x 138）+（100 x 108）＝50300 という計算式です。

図２－７

もちろん１つのみのセル範囲でも計算できますが、これは「SUM関数」と同等です（図２－８）。

図２－８

SUMPRODUCT関数の２つ目の使い方は「条件に合致する個数を計算」することです。
図２－９のSUMPRODUCT関数のカッコ内では「 (B3:B5 = B3) * 1 」としています。式の意味は「B3:B5のセル範囲に、B3と同じ値の『A』が入っているセルの数を数える」というもので、その結果「２」がE6セルに表示されています。なお、自分（この場合はB3セル）もカウントに含めます。

図２－９

この式は、どういう意味なのかを図２－１０に示してみました。

図２－１０

まず「B3:B5 = B3」は、セル範囲の１つ１つに対して「B3セルと値は同じですか？」ということを確認しています。ここで同じ値が入っているのは、B3セル（比較しているセルと同じなので当然です）とB5セルの２つです。「同じか否か」を問われているので、結果はTrueかFalseになります。
このTrue・Falseの状態では、足し算などの計算は出来ません。しかしTrue・Falseに１を掛けると「True * 1 = 1」「False * 1 = 0」と計算が可能な数値として得ることが出来ます。
なお、文字列の比較だけで無く図２－１１のように数値の比較も出来ますし、また等号（＝イコール）だけなく不等号（＞、＜、＞＝、＜＝）も使用できます。
文字列の比較でも不等号を使用できますが、比較の大小は「文字コード」で比較をしているようで、複数文字の場合は、まず「１文字目の文字コード」で比較をし、次に「２文字目の文字コード」で比較をする・・・となっているようです。

図２－１１

２－４．条件付き書式の数式の意味

このSUMPRODUCT関数の２つ目の使い方で作成したのが、図２－６の「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=138」です。この中の「$C$4:$AM$15」は「ユーザーが指定したデータ範囲」を差し、「C4」は指定したデータ範囲の左上角のセル位置です。
ですので数式の前半「=SUMPRODUCT(($C$4:$AM$15=C4)*1)」の意味としては、「ユーザー指定したデータ範囲の中でC4セルと同じ値のセルの数」という意味になります。

この条件付き書式の数式は、「適用先のセル範囲の『左上角のセル』の数式」を表しており、その他の適用先セルには「この数式のコピー」が適用されるわけです。
もう一度数式「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=138」を見てみると、中央当たりの「$C$4:$AM$15」には行・列番号の先頭に「$」マークが付いていますので「絶対セル位置」を表します。つまり「コピーをされても、絶対に変わらないセル範囲」となります。
一方その後ろの「C4」には行・列とも「$」マークがついていませんので、「行・列とも相対セル位置」となります。

条件付き書式の数式の元データ部分、つまり数式前半の「=SUMPRODUCT(($C$4:$AM$15=C4)*1)」を適用先セル全てに適用すると、図２－１２のような結果が得られることになります。

図２－１２（計算過程の値であり、実際にセル上に表示される値ではありません。）

図２－１２に表示されている数値は「各セルの値が適用先セルにいくつ存在するか」を表している訳ですが、条件付き書式の数式の比較データ部分、つまり数式の後半の「=138」に当てはまるセルだけが「条件付き書式」の設定書式（この場合は濃いオレンジ色）になります。

図２－５の２番目、３番目の条件付き書式の数式を見てみると「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=119」「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=108」になっていると思いますが、種類Bが138個・種類Aが119個・種類Cが108個であったため、「=119」の場合は少し薄いオレンジ色、「=108」は最も薄いオレンジ色に設定してあります。
この各種類の個数を数え、背景色を含めた条件付き書式の設定をマクロで自動化しています。

２－５．SUMPRODUCT以外の手法

今回、図２－６では「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=138」という数式を使っています。しかし「範囲内の数を調べるならCOUNTIFで良いのでは？」と思われた方もいると思います。確かに「=COUNTIF($C$4:$AM$15,C4)=138」という数式を使っても同様の結果が得られます。

但し、条件付き書式に「COUNTIF」を使用するとCOUNTIFの計算処理に時間がかかるようです。今回のサンプルファイル（444セル）で試したところ、私のPCで約３倍（InputBoxで範囲の取得後～条件付き書式の表示完の時間（ScreenUpdating = False は無し）：SUMPRODUCT＝2.1秒、COUNTIF＝5.8秒）の開きがありました。
（ScreenUpdating = False を設定すると書式表示完までの時間は大幅に短縮されますが、見た目ですが書式処理の段階でInputBoxの方が一呼吸遅れる感じです。）
少しでも処理の早い方が良いと考え、今回の条件付き書式には「SUMPRODUCT関数」を使用しています。

３．プログラムの流れ

プログラムを起動するとInputBoxが表示され、ユーザーが指定するセル範囲を取得します。
次に、指定セル範囲内の各セルに対して「範囲内で、当セルの値と同じ値を持つセルの数」を計算し、配列（RsAry）に代入していきます。
続いて、指定したセル範囲内の「古い条件付き書式」を削除し、配列RsAryの中から最も大きな値を呼び出し、その値を使って条件付き書式を作成します。最も大きな値での条件付き書式の作成が完了したら、次に大きな値を呼び出し、また条件付き書式を作成する作業を、設定回数分繰り返します。

図３－１

４．標準モジュール（Module1）

今回のマクロは標準モジュールのみ、プロシージャも起動プロシージャ１つのみです。

1. '==========　⇩(1)　システム実行部　============
2. Public Sub LargeCountData()
4. 　Const SPLT As Integer = 3 　　　 '←ﾃﾞｰﾀの多い方から〇番目まで書式を設定（split(分割)の略）
6. 　Dim Rs As Range 　　　　　　　　 '←データ範囲
7. 　Dim r As Range 　　　　　　　　　 '←データ範囲内の１セル
9. 　Dim RsAry() As Long 　　　　　　 '←同じﾃﾞｰﾀのｾﾙ数の配列
10. 　Dim uniRsAry As Variant 　　　　'←RsAry配列の１データ
12. 　Dim rCnt As Long 　　　　　　　　 '←カウンタ変数(ﾃﾞｰﾀ範囲のｾﾙ数)
13. 　Dim spCnt As Long 　　　　　　　　 '←カウンタ変数(条件付き書式の数)
14. 　Dim currentCnt As Long 　　　　　 '←処理中のｾﾙ値のｾﾙの数
15. 　Dim LargeNo As Long 　　　　　　　 '←同じﾃﾞｰﾀのｾﾙ数
17. 　On Error Resume Next
18. 　　Set Rs = Application.InputBox _
19. 　　　　　　(prompt:="データ範囲を選択して下さい", Title:="同データの数の多いものを彩色", Type:=8)
20. 　　If Not Err.Number = 0 Then Exit Sub
21. 　On Error GoTo 0
23. 　ReDim RsAry(1 To Rs.Count)
25. 　For Each r In Rs
26. 　　rCnt = rCnt + 1
27. 　　RsAry(rCnt) = Application.WorksheetFunction.CountIf(Rs, r)
28. '　　RsAry(rCnt) = Application.WorksheetFunction.SumProduct (Evaluate("(" & Rs.Address & "=" & r.Address & ")*1"))
29. 　Next r
31. 　Application.ScreenUpdating = False
33. 　Rs.FormatConditions.Delete
35. 　For spCnt = 1 To SPLT
36. 　　If currentCnt >= Rs.Count Then Exit For
38. 　　LargeNo = Application.WorksheetFunction.Large(RsAry, 1 + currentCnt)
40. 　　For Each uniRsAry In RsAry
41. 　　　If uniRsAry = LargeNo Then currentCnt = currentCnt + 1
42. 　　Next uniRsAry
44. 　　With Rs.FormatConditions.Add(Type:=xlExpression, _
45. 　　　　Formula1:="=SUMPRODUCT((" & Rs.Address & "=" & Rs(1).Address(False, False) & ")*1)=" & LargeNo)
47. '　　With Rs.FormatConditions.Add(Type:=xlExpression, _
48. '　　　　Formula1:="=COUNTIF(" & Rs.Address & "," & Rs(1).Address(False, False) & ")=" & LargeNo)
50. 　　　With .Interior
51. 　　　　.ThemeColor = msoThemeColorAccent4
52. '　　　　.Color = RGB(255, 192, 0)
53. 　　　　.TintAndShade = (spCnt - 1) * 0.9 / (SPLT - 1)
54. 　　　End With
56. 　　End With
58. 　Next spCnt
60. 　Set Rs = Nothing
62. 　Application.ScreenUpdating = True
64. End Sub

図４－１

４－１．変数・定数の宣言

４行目の「Const SPLT As Integer = 3」は、条件付き書式を何個作るかの設定です。今回の「３」だと、同じ値を持つセル数の内「１～『３』番目に多いものに対して条件付き書式を作る」という事を表します。
６～１５行目は、変数の宣言です。それぞれコメントに示した通りです。

４－２．InputBoxでのデータ範囲の取得

１７～２１行目は、「データ範囲」を取得します。
１８～１９行目では「InputBoxメソッド」を使っています。
InputBoxメソッドと似たものとして「InputBox関数」がありますが、InputBoxメソッドでは引数Typeに図４－２の値を指定することで、様々なデータを取り込むことができます。今回「Type:=8」を指定することで「セル参照（セル範囲）」を取得しています。

InputBoxメソッドの引数Typeの値

値	説明
0	数式
1	数値
2	文字列（テキスト）
4	理論値(True 又は False)
8	セル参照（Rangeオブジェクト）
16	#N/Aなどのエラー値
64	値の配列

図４－２

取得したセル範囲は変数Rsに代入しています。
そのInputBoxメソッド（１８～１９行目）を、１７行目の「On Error Resume Next」と２１行目の「On Error GoTo 0」で囲んでいます。もしInputBoxメソッドでエラーが出た時には、２０行目の「If Not Err.Number = 0 Then Exit Sub」でマクロを終了することになります。
どういう時にエラーが発生するかと言うと、InputBoxが表示された後で「キャンセル」ボタンがクリックされた時や「ダイアログ右上の×印」をクリックされた時（両方ともエラー番号424が返されます）です。その時には「処理を中止する意図がある」と判断し、マクロを中止させています。

４－３．同じデータを持つセルの個数を配列に代入

２３行目の「ReDim RsAry(1 To Rs.Count)」では、変数RsAryの配列サイズを「データ範囲のセル数」にしています。なお、サンプルファイルは３７ｘ１２の「二次元」ですが、変数RsAryは一次元にしています。
この変数RsAryを処理するのは、２７行目の「RsAry(rCnt) = Application.WorksheetFunction.CountIf(Rs, r)」で値を代入する所と、３８行目の「LargeNo = Application.WorksheetFunction.Large(RsAry, 1 + currentCnt)」で「Large関数」を使って値を取り出す所、及び４０行目の「For Each uniRsAry In RsAry」で配列内の１つ１つを取り出してから順番を計算する所の３か所です。

３８行目の「Large関数」も４０行目の「For Each～In」も、データが「データ範囲の形の二次元配列」であっても「並び直された一次元配列」でも、データさえ正しく格納されていれば正しく動いてくれます。
ですのでデータを変数RsAryに代入するのに、わざわざFor～Nextを二重にネストして二次元配列に入れなくても、２７行目のようにコードの簡単なFor Each～Inを使って一次元配列に代入する方法を採用しました。

２５～２９行目では、データ範囲の１つ１つのセルについて「データ範囲内に、そのセルと同じ値を持ったセルがいくつあるか」の値を配列RsAryに入れていきます（２６行目の「rCnt = rCnt + 1」で１つずつ配列の場所を変更させています）。
２７行目で使っているのは「CountIf関数」です。見え消しにしている２８行目の「SumProduct関数」も使うことは出来ますが、見て分かるように式が長くなりますし処理時間も変わらかったので、単純な「RsAry(rCnt) = Application.WorksheetFunction.CountIf(Rs, r)」としました。

４－４．条件付き書式の作成

条件付き書式を削除・作成する処理をする間はセルの書式が変更になりますので、画面チラつきが発生し、また処理時間も多く掛かります。ですので３１行目の「Application.ScreenUpdating = False」で、処理の間は画面更新を中止させています。
処理終了後の６２行目で画面更新を再開しています。

３３行目の「Rs.FormatConditions.Delete」で、既存の条件付き書式を削除しています。なお削除範囲は「Rs（今回指定したデータ範囲）」ですので、それ以外の範囲に条件付き書式が存在すれば、それは残ります。
図４－３のように「元の条件付き書式の適用範囲①」に対して「新たな適用範囲②」をDeleteすれば、右側のように②の部分が欠けた条件付き書式範囲①が残ることになります。

図４－３

一方、図４－４の左側のように「元の条件付き書式の適用範囲①」よりも「広い適用範囲（同じ範囲も含む）②」をDeleteすれば、元の条件付き書式は消去されます。また、Delete範囲が小さい場合は、図４－４の右側のように「真ん中がスッポリ抜けた」条件付き書式が残ります。

図４－４

「Deleteせずに、上から条件付き書式を重ねる」という方法も考えられますが、新しく設定した条件をすり抜けてしまった場合には、古い条件に合致して「予想外の書式が設定されてしまう」場合がありますので、今回はDeleteしてから新しい設定を行っています。

３５行目の「For spCnt = 1 To SPLT」から５８行目の「Next spCnt」の間で、繰り返し「条件付き書式を作成」します。「spCnt」はカウンタ変数で、「SPLT」は４行目で設定した条件付き書式の数量です。

３６行目の「If currentCnt >= Rs.Count Then Exit For」は、「データの種類より書式の数の方が多い」時に、設定できるところまでで中止するものです。例えば図４－５のように「適用範囲内に２種類しか文字列が無いのに、３種類の書式を設定」しようとした場合には、３８行目の「Large関数を使って、配列RsAry内から配列より大きな順番の値を取り出す」ところでエラーが発生してしまいますので、防いでいます。

図４－５

４－４－１．個数データを大きい方から取り出し

３８行目の「LargeNo = Application.WorksheetFunction.Large(RsAry, 1 + currentCnt)」は、「配列RsAry内から、１番大きな値、次に大きな値、・・・という順に取り出す」ことをしています。値は「LargeNo」に代入されます。
しかし図４－５のように、「配列RsAry内から１番大きな値（12）」を取り出すのは簡単ですが、「次に大きな値（８）」を取り出すのは大変です。というのはLarge関数の構文「Large（配列,順位）」の順位を「２」に設定しても「１２」の値が返ってきてしまいます。原因は「配列内を大きい順に並べ、先頭からの『順位』のものを取り出す」というものだからです。

そのため、２番目以降を取り出す際には「一つ前までの数量（currentCnt）」を数えてやる必要があります。それが４０～４２行目の計算になります。
まず４０行目の「For Each uniRsAry In RsAry」で、配列RsAry内の値を全て調べます。取り出した１つ１つの値は「uniRsAry」です。（「For i = 1 to Ubound(RsAry,1)」を使っても良いです。）
４１行目の「If uniRsAry = LargeNo Then currentCnt = currentCnt + 1」で、値が３８行目で取り出した「〇番目に大きな値（LargeNo）」と同じであれば、変数currentCntを１つ増やしていきます。
currentCntは「１つ前までの大きな値の数」を引き継いでいますので、図４－５のように２番目の大きな値を計算した段階では「１番目に大きな値＋２番目に大きな値」が変数currentCntに入ることになります。なお、ここで計算したcurrentCnt値は、１周回って次の３６行目で「配列の大きさをオーバーしていないか」のチェックを受け、３８行目の「次の大きな値の取り出し」の時に使われます。

なお、似たような機能にFilter関数というものがあります。これを使って「Filter(RsAry, CStr(LargeNo))」とすると同じ数値の配列となるのですが、「Filter関数は部分一致」ですので使えません。例えば「1000,100,10」となった場合、一番多い1000の個数は正しく求められますが、２番目の100の計算には1000も含まれてしまうことになります。

４－４－２．数式の書込み

４４～４５行目の「With Rs.FormatConditions.Add(Type:=xlExpression, Formula1:="=SUMPRODUCT((" & Rs.Address & "=" & Rs(1).Address(False, False) & ")*1)=" & LargeNo)」で、適用先（Rs）に対して条件付き書式の内「数式部分」を追加（Add）しています。
「Type:=xlExpression」とTypeには「xlExpression（演算）」を指定していますが、Typeに指定できるものは図４－６のように多種あります。

定数	値	内容
xlcellvalue	1	セルの値
xlexpression	2	演算
xlColorScale	3	カラー スケール
xlDataBar	4	DataBar
xlTop10	5	上から 10 個の値
xlIconSet	6	アイコン セット
xlUniqueValues	8	一意の値
xlTextString	9	テキスト文字列
xlBlanksCondition	10	空白の条件
xlTimePeriod	11	期間
xlAboveAverageCondition	12	平均以上の条件
xlNoBlanksCondition	13	空白の条件なし
xlerrorscondition	16	エラー条件
xlnoerrorscondition	17	エラー条件なし

図４－６

数式（Formula1:=）は、図２－６で示したように「=SUMPRODUCT(($C$4:$AM$15=C4)*1)=138」のように作ります。
「$C$4:$AM$15」の部分は適用範囲（データ指定範囲）です。数式はアドレス形式で表記するため「Rs.Address」とします。
ここでAddressプロパティの５つの引数について説明します。全て省略可の引数です。

構文：　セル範囲.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

引数	値１(既定)	値２	内容
RowAbsolute	True	False	行の絶対参照／相対参照
ColumnAbsolute	True	False	列の絶対参照／相対参照
ReferenceStyle	xlA1	xlR1C1	A1参照形式／R1C1参照形式
External	False	True	ﾛｰｶﾙ参照／外部参照
RelativeTo	相対参照形式での基準となるRangeｵﾌﾞｼﾞｪｸﾄ

図４－７

なお引数RelativeToは、「RowAbsolute又はColumnAbsolute（又は両方）をFalseに設定」＋「ReferenceStyleをxlR1C1に設定」した場合に有効になります。例えば、R[3]C[4]のように「どこかのセル（RelativeTo）を基準にしたセル位置」を指定する場合です。

「Rs.Address」には引数を１つも指定していないため、行・列とも絶対参照（$マーク付き）でA1形式、且つ（シート名が入らない）ローカル参照のため「$C$4:$AM$15」のような文字列になります。絶対参照になっていますので、「条件付き書式を左上角セル」用に数式を作り、それを適用範囲全体にCopyしても「参照先が変わらない」ことになります。

次の「Rs(1).Address(False, False)」の中の「Rs(1)」は「左上角セル」を指しています。また「Address(False, False)」は、Addressプロパティの第一・第二引数をFalseにしていますので「行・列とも相対参照（$マーク無し）」の「C4」のような文字列になります。
最後尾の「LargeNo」は、３８行目で算出した「配列RsAry内から取り出した１番大きな値（２番目の時は、２番目の大きな値）」です。

４４～４５行目は「SUMPRODUCT」を使った数式の組立ですが、「CountIf」を使った場合は見え消しの４７～４８行目の数式になります。前述した通り、処理速度の点から今回は「SUMPRODUCT」を使用しています。

４－４－３．書式の書込み

５０～５４行目は、４４～４５行目で作成した条件付き書式の数式とのペアである「書式」を設定しています。
５０行目の「With Interior」は、セルの背景に対して処理をすることを意味しています。
５１行目の「.ThemeColor = xlThemeColorAccent4」は背景色を設定しています。ここでは「ThemeColorプロパティ」を使っているため、図４－８のような「MosThemeColorIndex列挙型」の係数・値を使う必要があります。色は「セルの書式設定」で確認・選択できるものに沿っています。
なお、「XlThemeColor列挙型」の係数・値を使うことも出来ます（マクロ記録で得られるのは、XlThemeColor列挙型のようです）。

図４－８

また「ThemeColorプロパティ」では無く、代わりに５２行目（見え消し部）のような「Colorプロパティ」を使って色を指定することも可能です。Colorプロパティには、RGB関数・カラー定数・XlRgbColor列挙型定数・カラー値が使用できますが、５２行目ではRGB関数を使い「RGB(255,192,0)」とすることで「xlThemeColorAccent4」と同色を指定しています。

５３行目の「.TintAndShade = (spCnt - 1) * 0.9 / (SPLT - 1)」は、５１行目の背景色を元にした「色の明るさ・暗さ」で「出現回数の差」を表すようにしています。「TintAndShadeプロパティ」には「－１（最も暗い）～０（ニュートラル）～＋１（最も明るい）」の値をSingle型で与えることになります。TintAndShade値の増減によって、どのような色になるかは図４－８を参照下さい。

今回は定数SPLT値（何段階にするかの値）により可変で色分けをする必要があったので、図４－９のように「最も数の多いもの」をニュートラルとし、２番目・３番目となるごとに明るくする（色としては薄くなる）方法にしました。

図４－９

しかし明るさを限界の１までしてしまうと、白になってしまい「書式を設定していないのと見分けがつかない」ので、最大は0.9になるようにし、０と0.9の間を等分して設定することとしました。そのため、５３行目のような式になっています。

最後に６０行目の「Set Rs = Nothing」で、データ範囲のRangeオブジェクトを解放し、６２行目の「Application.ScreenUpdating = True」で画面更新を再開しています。

５．最後に

長年同じようなデータを取り続けていると、その取得方法・分析方法もある程度固定化し定型化・マニュアル化されていくのは、ある程度は効率のためにも大切です。データも自動取得、グラフ化や判断までも自動なんていうシステムもあります。
しかし同じ見方ばかりしていることで、データの持っている大切なメッセージを知らない間に見逃してしまっているかもしれません。
そうならないためにも、様々な解析方法にトライすることは大切だと思います。そして、新たな発見をした時の喜びは何物にも代えがたいもののはずです。

現在のExcelの条件付き書式では、図４－６のように様々な分析方法が選択できます。そのような機能にプラスして今回のようなシステムを使うことで、もし何かの発見につながことがあれば嬉しい限りです。

なお、今回システムで作成した「条件付き書式」は作成するだけで「削除する機能はありません」。条件付き書式が不要になったら、手動で削除をお願いします。

出現回数ごとにデータを色分け（it-054.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。