Excelで大量のデータを扱う際、どの値がどれくらい出現しているのかを視覚的に把握したい場面は少なくありません。
例えば、名簿データの中で同じ名前が何回登場しているのか、商品コードの出現頻度がどの程度か、などです。
こうした場合、Excelの「条件付き書式」を使って出現回数に応じてセルの色を自動で変えることで、データの傾向を一目で理解できるようになります。
本記事では、出現回数に応じて色を変える方法を、関数・条件付き書式・VBAの3つの観点から詳しく解説します。
業務効率や分析力を高めるテクニックとして、ぜひマスターしてください。
目次
出現回数によって色を変えるとは？
まず「出現回数で色を変える」とは、同じ値が登場する回数に応じてセルの塗りつぶし色や文字色を変える処理です。
たとえば以下のようなデータがあるとします：
| 氏名 |
|---|
| 山田 |
| 佐藤 |
| 山田 |
| 鈴木 |
| 山田 |
| 佐藤 |
この中で、
「山田」は3回出現 → 赤色
「佐藤」は2回出現 → 黄色
「鈴木」は1回出現 → 緑色
のように、登場回数ごとに異なる色でハイライトすると、視覚的に非常にわかりやすくなります。
【Excel】重複データを完全に削除して一切残さない方法｜関数・フィルター・VBAによる実践的アプローチ
✅ COUNTIF関数 × 条件付き書式で実現する方法（3段階に分ける例）
最も汎用的かつ簡単にできる方法が、COUNTIF関数と条件付き書式の組み合わせです。
手順：
色分けしたい範囲（例：A2:A100）を選択
「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して…」を選択
以下の式を1つずつ入力し、それぞれに異なる色を設定
3回以上出現：
=COUNTIF($A$2:$A$100, A2) >= 3
→ 塗りつぶし色：赤
2回出現：
=COUNTIF($A$2:$A$100, A2) = 2
→ 塗りつぶし色：黄色
1回のみ出現：
=COUNTIF($A$2:$A$100, A2) = 1
→ 塗りつぶし色：緑
ポイント：
$A$2:$A$100の範囲は、実際のデータ量に応じて調整してくださいA2の部分は、行番号だけが変動するように設定します（相対参照）
【Excel】重複の削除を“行単位”で行う方法｜1行全体の重複チェックと削除手順を徹底解説
✅ アイコンセットで出現回数を可視化する方法（簡易的な色分け）
「条件付き書式」の中にある「アイコンセット」を使うことで、出現回数に応じたアイコン表示も可能です。
手順：
A列に名前、B列に以下のCOUNTIF関数を入力：
=COUNTIF($A$2:$A$100, A2)
B列の値に対して、「条件付き書式」→「アイコンセット」を設定
出現回数が多いほど赤い丸、少ないほど緑色などの視覚的な表現が可能
注意点：
アイコンはセルの値に基づいて表示されるため、数値を別列で算出しておく必要があります
細かい色分けは難しいため、ざっくり傾向を見る場合に適している方法です
【Excel】重複データをまとめる方法｜関数・ピボット・Power Queryを使った実践テクニック
✅ VBAで出現回数に応じた色付けを自動化する
複数の色分けを手作業で条件付き書式に設定するのが手間に感じる場合は、VBAを使って自動で色を変えるマクロを作成するのもおすすめです。
サンプルコード：
Sub 出現回数で色分け()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim dict As Object
Dim name As String
Dim rng As Range
Set ws = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' カウント処理
For i = 2 To lastRow
name = ws.Cells(i, 1).Value
If name <> "" Then
If dict.exists(name) Then
dict(name) = dict(name) + 1
Else
dict.Add name, 1
End If
End If
Next i
' 色付け処理
For i = 2 To lastRow
name = ws.Cells(i, 1).Value
If dict.exists(name) Then
Select Case dict(name)
Case Is >= 3
ws.Cells(i, 1).Interior.Color = RGB(255, 150, 150) ' 赤
Case 2
ws.Cells(i, 1).Interior.Color = RGB(255, 255, 150) ' 黄
Case 1
ws.Cells(i, 1).Interior.Color = RGB(200, 255, 200) ' 緑
End Select
End If
Next i
MsgBox "色分けが完了しました。"
End Sub
特徴：
出現回数に応じて自動でセルの背景色を変更
条件付き書式を使わずに自由な色指定が可能
データ更新のたびにマクロ実行すればOK
・実務での活用例とおすすめの色分け基準
| シーン | 色分け基準 | 利用目的 |
|---|---|---|
| 名簿の重複登録チェック | 同姓同名が2件以上 → 赤 | 入力ミスや重複登録を目視で即確認 |
| 商品販売数ランキング | 出現回数が多い順に赤→黄→緑 | 売れ筋商品の可視化 |
| アンケート集計 | 回答数が多い項目 →濃い色 | 人気意見の可視化 |
| ログデータ（IPやユーザーID） | 出現回数が多いログ →警告色 | 異常アクセスの検知やセキュリティ監視に活用 |
注意点と対策
| トラブル | 原因 | 対処方法 |
|---|---|---|
| 同じ見た目でも色が付かない場合 | 全角/半角・空白の違いで別データとして判定されている | TRIM, CLEAN, UPPERなどで前処理を行う |
| データを増やすと色分けされない | 条件付き書式の適用範囲が固定されている | 範囲を広く設定するか、テーブル形式に変換する |
| 並び替えやフィルターで色がおかしくなる | 書式は値ではなく位置に依存するため | VBAで再処理を行う or 条件付き書式の再設定 |
・まとめ：出現回数で色を変えるだけでデータの可視化と分析が加速する
出現回数を視覚化するだけで、Excelでのデータ分析・誤入力防止・意思決定のスピードが劇的に向上します。
| 方法 | 特徴 |
|---|---|
| 条件付き書式 | 最も手軽で初心者向け。3段階程度の色分けに最適 |
| アイコンセット | 回数の傾向を簡易に可視化したいときに便利 |
| VBAマクロ | 自動化・高度な色分け・動的対応に強い |
出現回数に応じた色分けは、日々のデータ処理だけでなく、報告資料やダッシュボード作成にも応用できるExcelの基礎かつ応用力のあるスキルです。
ぜひ活用して、あなたの業務をより「見える化」しましょう！