プロダクトマネージャーから受け取ったスプレッドシートを開き、「ちょっと重複をチェックしてくれない？」と頼まれた経験がある方なら、あの感覚をご存知でしょう。一見簡単そうに聞こえますが、いざファイルを開くと、そこには1万行のデータ、複数の列、そして「この文脈で『重複』とは具体的に何を指すのか？」という明確な定義の欠如が待ち受けています。

重複検出は、Excel パワーユーザーとバックエンド開発者の狭間に位置するタスクです。手動で扱うには手間がかかりすぎる一方で、.xlsx ファイルに強く依存しているため、普段使っているデータツールにうまく適合しません。

このガイドでは、ニーズの規模に応じてスケールするアプローチを、簡単な UI のテクニックから自動化、開発者向けのソリューションまで順を追って説明します。

Excel における「重複」とは何か？

解決策に入る前に、まず「重複」が実際に何を意味するのかを明確にする価値があります。シナリオによって定義が異なる可能性があるからです。

重複の種類

実際のケースでは、重複は主に以下の 3 つのカテゴリに分類されます。

単一列の重複: 同じ値が 1 つの列に複数回出現する場合（例：メールアドレスの重複）。
完全行の重複: 2 つ以上の行で、すべてのセルが完全に同一である場合。
複合重複: 複数の列の組み合わせが重複キーを形成する場合（個々の列だけでは重複していなくても）。例えば、トランザクションログに同じ (user_id, date) のペアが 2 回出現する場合などです。

これらの区別は重要です。なぜなら、Excel の組み込みツールはこれらのケースを常に同じ方法で処理するとは限らないからです。

よくある落とし穴

ロジックが単純に見える場合でも、いくつかの微妙な問題が誤解を招く結果につながることがあります。

例えば、Excel はデフォルトで大文字と小文字を区別しないため、Apple と apple は同じ値として扱われます。これは多くの場合便利ですが、不整合を見逃す原因にもなります。

目に見えない文字もよくある原因です。"123" と "123 " は、末尾にスペースがあるだけで、視覚的には見つけにくいものの、異なる値として扱われます。

また、データ型の不一致にも遭遇します。"123" (テキスト) と 123 (数値) は異なる扱いを受けます。そしてもちろん、Excel も浮動小数点演算の癖と無縁ではありません。0.1 + 0.2 のような計算は、常に正確に期待どおりに動作するとは限りません。

これらのエッジケースを考慮すると、「重複を見つける」ことが見かけよりもはるかに微妙な問題である理由が理解できるでしょう。

方法 1：条件付き書式（標準 UI）

重複を強調表示する最速の方法で、数式もコードも不要です。問題を素早く確認したいだけの、一度限りのチェックに最適です。

手順:

チェックしたい列または範囲を選択し、次のパスをたどります。

ホーム → 条件付き書式 → セルの強調表示ルール → 重複する値

ダイアログが表示されるので、強調表示の色を選択します。OK をクリックすると、選択範囲内で複数回出現する値を持つすべてのセルがすぐに色付けされます。

知っておくべき動作:

複数の列を一度に選択した場合、Excel は各行ではなく、各セルを個別にチェックします。したがって、列Aに alice が2回、列Bに bob が 2 回ある場合、両方とも強調表示されます。しかし、(alice, bob) という行が2回出現した場合、両方のセルが独立して繰り返されている場合にのみ検出されます。行レベルまたは複合重複の検出が必要な場合は、方法 2 に進んでください。

書式のカスタマイズ:

デフォルトは薄い赤の塗りつぶしですが、任意の塗りつぶし色、フォント色、罫線スタイルを設定できます。同じダイアログで、書式のドロップダウンを開き、「ユーザー設定の書式」を選択すると、セルの書式設定に関するすべてのオプションにアクセスできます。

制限事項:

大文字と小文字の区別を制御できない — Alice と alice は同じものとして扱われます。
動的な範囲は自動的に追跡されない — 後で行を追加した場合、手動でルールを再適用または拡張する必要があります。
ルールは積み重なる — 同じ範囲に複数の条件付き書式ルールがある場合、順序が重要になります。「ホーム → 条件付き書式 → ルールの管理」で管理します。

それでも、簡単な一度限りのチェックには、これが最も効率的なオプションであることが多いです。

方法 2：COUNTIF 関数を用いた条件付き書式

組み込みの UI では柔軟性が足りない場合（大文字と小文字の区別、複合キー、どの出現をフラグするかについてのより正確な制御が必要な場合）、COUNTIF を使用すると、Excel から離れることなく、その制御を実現できます。

考え方は簡単です。何を重複と見なすかを Excel に任せる代わりに、各セルに対して TRUE または FALSE を返す数式を記述し、条件付き書式がそのロジックに基づいて強調表示を適用します。

基本的な設定:

データ範囲（例：B2:B12）を選択し、新しい条件付き書式ルールを作成します。

ホーム → 条件付き書式 → 新しいルール → 「数式を使用して、書式設定するセルを決定」

次の数式を入力します。

=COUNTIF($B$2:$B$12, B2) > 1

これは、B2 の値が範囲全体で何回出現するかをカウントします。複数回出現する場合、セルが強調表示されます。$ 記号は範囲を固定し、ルールが各セルに適用される際に行参照をシフトさせます。

SQL に慣れている方なら、これは以下のスプレッドシート版と言えます。

SELECT value
FROM table
GROUP BY value
HAVING COUNT(*) > 1

2回目以降の出現のみを強調表示する:

基本的な数式は、最初の出現を含むすべての出現をフラグします。元の値をそのままにして、重複のみをマークしたい場合は、代わりにこれを使用します。

=COUNTIF($B$2:B2, B2) > 1

主な違いは $B$2:B2 です。範囲の開始点は固定されていますが、ルールが各行を下に移動するにつれて終了点が拡大します。Excel が 5 行目を評価するまでに、範囲は $B$2:B5 となるため、現在の行までの出現のみをカウントします。最初の出現は決してフラグされず、繰り返しのみが対象となります。

複合重複の検出:

複数の列の組み合わせ（例：(email, signup_date)）が重複を形成するかどうかを確認するには、& を使用して値を単一のキーに連結します。

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) > 1

または、より多くの列にわたる文字列連結では：

=COUNTIF($A$2:$A$100 & $B$2:$B$100, A2 & B2) > 1

これは、ネイティブ Excel 内で複合主キーチェックに最も近い方法です。

大文字と小文字を区別する重複検出:

COUNTIF はデフォルトで大文字と小文字を区別しません。大文字と小文字を区別したマッチングには、SUMPRODUCT でラップされたEXACTを使用します。

=SUMPRODUCT((EXACT($A$2:$A$100, A2)) * 1) > 1

これは範囲内のすべての値を反復処理し、大文字と小文字を含めた完全一致をチェックしてカウントを返します。大規模なデータセットでは負荷が高くなりますが、機能します。

方法 3：VBA マクロ — クライアントサイド自動化

上記の 3 つの方法はすべて Excel の UI 内で動作します。対話的な使用には適していますが、プログラムによるトリガーが難しく、バージョン管理が困難であり、Excel を手動で開かずに実行することは不可能です。ファイルを開いたとき、ボタンをクリックしたとき、または再現可能なワークフローの一部として、重複の強調表示を自動的に行う必要がある場合、VBA が次の自然なステップとなります。

基本的な実装:

核となるロジックは単純です。列を反復処理し、Dictionary を使用して各値が出現した回数を追跡し、複数回出現した値を持つセルに戻って色を付けます。

Sub HighlightDuplicates()

    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim dict As Object
    Dim highlightColor As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A2:A1000")
    highlightColor = RGB(255, 199, 206)

    Set dict = CreateObject("Scripting.Dictionary")

    For Each cell In rng
        If cell.Value <> "" Then
            Dim key As String
            key = Trim(CStr(cell.Value))
            If dict.exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell

    For Each cell In rng
        If cell.Value <> "" Then
            If dict(CStr(cell.Value)) > 1 Then
                cell.Interior.Color = highlightColor
            Else
                cell.Interior.ColorIndex = xlNone
            End If
        End If
    Next cell

End Sub

最初のループで頻度マップを構築します。2 番目のループでカウントに基づいて強調表示を適用または削除します。2 つのパスに分割することでロジックが明確になり、再適用前に「すべての強調表示をクリア」するステップを簡単に追加できます。

再利用可能にする:

シート名と範囲をハードコーディングすることは、単発のスクリプトでは問題ありませんが、複数のファイルで再利用する予定がある場合は、キーとなる変数をパラメータ化します。

Sub HighlightDuplicates(sheetName As String, columnRange As String, highlightColor As Long)
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = ThisWorkbook.Sheets(sheetName)
    Set rng = ws.Range(columnRange)
    ' ... 上記と同じロジック
End Sub

プロジェクト内の他の場所から呼び出します。

Call HighlightDuplicates("Sheet1", "A2:A1000", RGB(255, 199, 206))

ファイルを開いたときに自動的にトリガーする:

ブックが開かれるたびにマクロを実行するには、ThisWorkbook の Workbook_Open イベントに追加します。

Private Sub Workbook_Open()
    Call HighlightDuplicates("Sheet1", "A2:A1000", RGB(255, 199, 206))
End Sub

これで、誰かがファイルを開くたびに、重複が自動的にフラグ付けされます。手動の手順は不要です。

大規模なデータセットでのパフォーマンス:

VBA のループは、Excel が変更のたびに再計算と再描画を行うため、デフォルトでは低速です。数千行を超えるデータセットの場合は、実行前にこれらを無効にし、実行後に再度有効にします。

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' ... ここにループ ...

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

5 万行のデータセットでは、これだけで実行時間を 30 秒以上から 2～3 秒に短縮できます。

VBA の限界:

VBA は Excel 内で実行されるため、Excelがインストールされ、開いている必要があります。CI パイプライン、バックエンドサービス、サーバー上のスケジュールされたタスクからトリガーすることはできません。デスクトップ環境なしで Excel ファイルを処理する必要があるユースケースの場合は、次の方法が必要です。

方法 4：Power Queryと動的配列

これまでの方法は Excel 自体の中でうまく機能しますが、データパイプラインに統合したり、クリーンで構造化された出力を生成したりするのは困難です。重複排除されたテーブルを出力する、結果を別のプロセスに渡す、データが更新されたときに分析を自動的に更新することが目標の場合、Power Query と動的配列はより優れたツールです。

動的配列 (Excel 365 のみ):

Microsoft 365 を使用している場合、UNIQUE() 関数と FILTER() 関数を使用すると、重複する値を新しい範囲に直接抽出できます。条件付き書式は不要です。

複数回出現する値のリストを取得するには：

=FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1)

COUNTIF(A2:A100, A2:A100) は、行ごとに 1 つのカウントの配列を返します。FILTER() は、そのカウントが1を超える行のみを保持します。結果は隣接するセルに自動的にスピルします。

注意: 非常に大規模なデータセットでは、COUNTIF が各要素に対して範囲全体で評価されるため、この方法は遅くなる可能性があります。

重複した値の中で、一意の値だけを（繰り返しなしで）取得するには：

=UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1))

これはクリーンで読みやすく、ソースデータの変更に応じてライブで更新されます。主な制約は、Excel 365 が必要であり、Excel 2016 や 2019 では動作しないことです。

Power Query:

Power QueryはExcel 2016 以降で動作し、より大規模なデータセットやより複雑な変換に適しています。アプローチは、注目する列で行をグループ化し、出現回数をカウントしてから、複数の行を持つグループをフィルタリングすることです。

Power Query を開くには：

データ → データの取得 → テーブル/範囲から

データ範囲を選択して OK をクリックします。Power Query エディターで：

重複をチェックする列を選択します。
ホーム → グループ化 に移動します。
対象の列でグループ化し、行数のカウント を使用してカウント列を追加します。
OK をクリックします。これで、各値とその出現回数のサマリーテーブルが作成されます。
カウント列をフィルタリングして、カウント > 1 の行のみを表示します。

閉じて読み込む をクリックして、結果を新しいシートに出力します。クエリを更新するたびに（データ → すべて更新）、最新のデータに対して再実行されます。

複合重複検出の場合は、グループ化する前に複数の列を選択します。Power Query はその組み合わせを単一のキーとして扱います。

👉 SQL に慣れている方なら、これは GROUP BY + HAVING COUNT > 1 のワークフローをビジュアルインターフェースで表現したものと考えることができます。

それぞれの使い分け:

動的配列は、自動的に更新されるライブな数式が必要で、データが単一のワークシートに収まる場合に最適です。Power Query は、データセットが大きい場合、変換ロジックが複雑な場合、または技術者以外のユーザーが「更新」を1回クリックするだけでトリガーできる再現可能なプロセスが必要な場合に、より適した選択肢です。

どちらの方法もコードを必要としませんが、機能面では大幅な向上があり、どちらもセルを塗るだけでなく、クリーンなデータを出力します。

方法 5：C# を使用したサーバーサイドでの重複強調表示

ここまでは、すべてのアプローチが実行環境として Excel に依存しています。これは手動ワークフローでは機能しますが、バックエンドサービス、CI/CD パイプライン、または Microsoft Office が利用できない環境でファイルを処理する必要がある場合には不十分です。

このようなシナリオでは、スタンドアロンの .NET ライブラリを使用することが最も実用的なアプローチになります。Excel 自体に依存する代わりに、コード内でファイル形式を直接操作するため、ソリューションの自動化、デプロイ、スケールが容易になります。

ここでの選択肢の一つが Spire.XLS です。これは Office に依存せずに Excel ファイルの読み書きを行う .NET ライブラリです。COM 相互運用機能も、バックグラウンドでの Excel プロセスも、デスクトップ環境への依存もありません。NuGet 経由でインストールし、ファイルを指定して、コードベース内の他のオブジェクトと同様に操作します。

セットアップ:

NuGet 経由でパッケージをインストールします。

dotnet add package Spire.XLS

または、Visual Studio の NuGet パッケージマネージャーを使用します。

Install-Package Spire.XLS

これ以上の設定は不要です。ライブラリは .xlsx および .xls 形式をそのまま処理します。

コアオブジェクトモデル:

実装に入る前に、全体で使用する 3 つのオブジェクトを理解しておくと役立ちます。

Workbook — Excel ファイル全体を表します。
Worksheet — ワークブック内の単一のシート。
CellRange — セルまたはセル範囲を表し、値、スタイル、書式設定のプロパティを持ちます。

基本的な実装:

ロジックは VBA アプローチを反映しています。頻度マップを構築し、再度反復処理して強調表示を適用します。違いは、これがスタンドアロンのC#プログラムとして実行されることです。Excel は不要です。

using Spire.Xls;
using System.Collections.Generic;
using System.Drawing;

class Program
{
    static void Main(string[] args)
    {
        Workbook workbook = new Workbook();
        workbook.LoadFromFile("data.xlsx");

        Worksheet sheet = workbook.Worksheets[0];
        int startRow = 2;
        int endRow = sheet.LastRow;
        int targetColumn = 2;

        // 頻度マップの構築
        Dictionary<string, List<int>> valueMap = new Dictionary<string, List<int>>();

        for (int row = startRow; row <= endRow; row++)
        {
            CellRange cell = sheet.Range[row, targetColumn];
            string value = cell.Value?.Trim();

            if (string.IsNullOrEmpty(value)) continue;

            if (!valueMap.ContainsKey(value))
                valueMap[value] = new List<int>();

            valueMap[value].Add(row);
        }

        // 重複行に強調表示を適用
        Color highlightColor = Color.FromArgb(255, 199, 206);

        foreach (var entry in valueMap)
        {
            if (entry.Value.Count > 1)
            {
                foreach (int row in entry.Value)
                {
                    CellRange cell = sheet.Range[row, targetColumn];
                    cell.Style.Color = highlightColor;
                }
            }
        }

        workbook.SaveToFile("data_highlighted.xlsx", ExcelVersion.Version2013);
        System.Console.WriteLine("完了。重複が強調表示されました。");
    }
}

Dictionary<string, List<int>> は、各一意の値と、それが出現する行番号のリストを格納します。最初のパスの後、リストに複数の行番号を持つキーは重複です。2 番目のパスでそれらの行番号を反復処理し、塗りつぶし色を適用します。

複合重複検出:

複数の列（例：列 1 と列 2 の組み合わせ）にわたる重複をチェックするには、セルの値を複合キーに連結します。

string value = $"{sheet.Range[row, 1].Value?.Trim()}|{sheet.Range[row, 2].Value?.Trim()}";

区切り文字 | は、異なる2つのペアを連結した場合に同じ文字列が生成される誤一致を防ぎます。例えば、区切り文字がない場合、("ab", "c") と ("a", "bc") はどちらも "abc" を生成しますが、"ab|c" と "a|bc" は区別されます。区切り文字が実際のデータに出現しないことを確認するか、より堅牢な区切り文字を選択してください。

ヘッダー行のスキップ:

上記の実装の startRow = 2 は、すでにこれに対応しています。ファイルにあるヘッダー行の数に合わせて値を調整してください。

強調表示色のカスタマイズ:

Color.FromArgb(r, g, b) は任意のRGB値を受け付けます。デフォルトの Excel の重複強調表示に合わせるには：

Color highlightColor = Color.FromArgb(255, 199, 206); // 薄い赤の塗りつぶし
Color fontColor = Color.FromArgb(156, 0, 6);          // 濃い赤のテキスト

cell.Style.Color = highlightColor;
cell.Style.Font.Color = fontColor;

.NETサービスへの統合:

これは純粋なC#であるため、あらゆる .NET アプリケーションに自然に組み込めます。アップロードされたファイルを処理する ASP.NET エンドポイント、スケジュールに従ってレポートを検証するバックグラウンドワーカー、CIパイプラインから呼び出されるコンソールツールなどです。

# GitHub Actionsのステップ例
- name: Excelレポートの検証
  run: dotnet run --project ./ExcelValidator -- --file reports/output.xlsx

重複が見つかった場合、プロセスはゼロ以外のコードで終了し、パイプラインステップが失敗します。これにより、CIゲートとして自動重複検出が実現します。

Spire.XLS vs. VBA — それぞれの使い分け:

VBA は、作業がデスクトップに留まり、ファイルが対話的に開かれ、実行する人が Excel を持っている場合に適切なツールです。Spire.XLS は、ファイルがサーバーで処理される場合、環境に Office がインストールされていない場合、またはロジックがテスト可能で、バージョン管理され、より大きなコードベースに統合される必要がある場合に適切なツールです。

手法の早見比較

5 つの方法すべてを覚えておくのは大変です。ここでは、状況に応じて適切なものを選ぶための要約を示します。

方法	コーディング要否	Office 要否	自動化	最適な用途
条件付き書式	不要	必要	手動	簡単な一度限りの目視チェック
COUNTIF 数式	不要(数式)	必要	半自動	柔軟なロジック、Excel 内で完結
Power Query / 動的配列	不要	必要(365)	自動更新	定期的なレポート、クリーンな出力
VBA マクロ	必要	必要	全自動	デスクトップ自動化、イベント駆動
C# Spire.XLS	必要	不要	全自動	サーバーサイド、CI/CD、バッチ処理

迅速な意思決定のための簡単な経験則は以下の通りです。

誰かから送られてきたファイルのスポットチェック → 条件付き書式
ロジックは複雑だが、Excel 内で作業している → COUNTIF 数式
更新可能でクリーンな出力テーブルが必要 → Power Query
ファイルを開いたときやボタンクリックでトリガーするデスクトップ自動化 → VBA
サーバーサイドで、Office 不要、CI/CDパイプライン → C# Spire.XLS

まとめ

Excel で重複を強調表示することは小さなタスクのように思えるかもしれませんが、データがどのように構造化され、検証されているかという、より深い問題を明らかにすることがよくあります。簡単な目視チェックとして始まったものが、すぐに一貫性、エッジケース、信頼性に関する疑問へと発展する可能性があります。ワークフローに応じて、適切なアプローチは、単純な条件付き書式から、数式、Power Query、またはコードを使用した、より制御された再現可能なソリューションまで多岐にわたります。データが成長し、プロセスがより自動化されるにつれて、重複検出は単なる Excel のテクニックではなくなり、より広範なデータ品質戦略の一部となります。適切な方法を選択することは、ツール自体の問題というよりも、ワークフローの進化に伴ってデータロジックが自然にどこに属するかという問題です。