Articles /

Excelで重複するセルや行を強調表示する方法

このチュートリアルでは、Excelで重複を表示する方法について学習します。 条件付き書式を使用して、重複するセル、行全体、または連続する重複に影を付けるさまざまな方法を詳しく見ていきます。 また、専用のツールを使用して、異なる色で重複を強調表示する方法も紹介します。

先週は、Excel で重複を識別するさまざまな方法について検討しました。 間違いなく、重複する数式は非常に便利ですが、定義された色で重複するエントリを強調表示することにより、データ分析がさらに簡単になります。

Excel で重複を検索して強調表示する最も速い方法は、条件付き書式を使用することです。 この方法の最大の利点は、既存の重複を表示するだけでなく、データの入力、編集、または上書きの際に新しい重複を検出して色を付けることです。

このチュートリアルではさらに、特定のタスクに応じて、重複するレコードを強調する方法を多数紹介します。 これらのテクニックは、Excel 2016、Excel 2013、Excel 2010およびそれ以下のすべてのバージョンで機能します。

  • Excelで1回目の出現で重複を強調表示する（組み込み
  • Excel で重複するファイルを 1 つだけ強調表示する方法
  • 範囲 (複数列) の重複を強調表示する方法
  • N 番目以降の重複を強調表示する方法
  • Excel で重複するファイルを強調表示する方法。 レコード
  • 1列の重複した値に基づいて行全体に影を付ける
  • エクセルで重複した行をハイライトする方法
  • 重複したセルを連続して表示する方法
  • Duplicate Remover – (重複排除ツール) Excel で重複をハイライトする最速の方法

How to highlight dupates in Excel using built-in rules (with 1st occurrence)

Forstars, すべてのバージョンのExcelには、重複するセルを強調表示するための定義済みルールがあります。 ワークシートでこのルールを使用するには、次の手順を実行します。

  1. 重複をチェックしたいデータを選択します。 これは、列、行、またはセルの範囲です。
  2. ホームタブの［スタイル］グループで［条件付き書式］>［セルの強調表示のルール］>重複した値…
    重複した値を強調する Excel の組み込みルールを使用
  3. ［重複した値］のウィンドウが開き、デフォルトでは、塗りが薄い赤、文字色が濃い赤の形式が選択されています。 既定の書式を適用するには、単に [OK] をクリックします。

赤の塗りつぶしとテキストの書式以外に、ドロップダウン リストで他のいくつかの定義済みの書式が利用できます。 他の色を使用して重複を強調表示するには、[カスタム書式…] (ドロップダウンの最後の項目) をクリックして、好みの塗りつぶしおよび/またはフォントの色を選択します。
デフォルトの薄赤色の塗りつぶしおよび濃い赤色のテキスト書式で重複を強調表示する

Tip. 一意の値を強調表示するには、左側のボックスで [一意] を選択します。

次のスクリーンショットに示すように、組み込みのルールを使用して、1 つの列または複数の列の重複を強調表示できます。

注意事項。 組み込みの重複ルールを 2 つ以上の列に適用する場合、Excel はそれらの列の値を比較せず、単にすべての重複するインスタンスをハイライト表示します。 2つの列の間の一致と相違を検索してハイライトしたい場合、以下のチュートリアルでいくつかの例を見ることができます。 Excel で 2 つの列を比較する方法。

重複する値を強調表示する Excel の内蔵ルールを使用する場合、次の 2 つの点に留意してください：

  • これは個々のセルに対してのみ機能します。 重複する行を強調表示するには、特定の列の値に基づいて、または複数の列の値を比較して、独自のルールを作成する必要があります。
  • 重複するセルには、その最初の出現を含めて影がつきます。 最初の出現を除くすべての重複を強調表示するには、この数式に基づいて条件付き書式設定ルールを作成します。

初出以外の重複を強調表示する方法

2回目以降の重複を強調表示するには、色を付けたいセルを選択し、次の方法で数式ベースのルールを作成します：

  1. ［ホーム］タブで［スタイル］グループから［条件付き書式］> 新しいルール > どのセルに対して書式設定するか数式を用いて決定します。
  2. ［この数式が真となる値の書式設定］ボックスに、次のような数式を入力します：
    =COUNTIF($A:$A2,$A2)>1

    A2 は選択した範囲の一番上のセルです。

  3. ［フォーマット…］ボタンをクリックして、塗りや文字の色を選択します。
  4. 最後に、保存してルールを適用するために［OK］をクリックします。

最初のインスタンスを除き、Excelで重複を強調表示する。

Excelの条件付き書式の経験があまりない場合は、次のチュートリアルで数式ベースのルールを作成する詳細な手順を参照してください。

結果として、最初のインスタンスを除く重複するセルは、選択した色で強調表示されます：
最初のインスタンスを除くすべての重複するセルが強調表示されます。

3番目、4番目以降の重複レコードを表示する方法

N番目以降の重複を表示するには、前の例のように数式に基づいて条件付き書式ルールを作成し、唯一の違いは、数式の最後の>1を必要な番号に置き換えることです。 たとえば、

3 番目以降のすべての重複するインスタンスを強調表示するには、この数式に基づいて条件付き書式設定ルールを作成します：

=COUNTIF($A:$A2,$A2)>=3

4番目以降のすべての重複するレコードを強調するには、この数式を使用します：

=COUNTIF($A:$A2,$A2)>=4

特定の事象のみを強調するには、等号演算子 (=) を使用します。 たとえば、2 番目のインスタンスのみを強調表示するには、次の数式を使用します:

=COUNTIF($A:$A2,$A2)=2

範囲（複数列）の重複を強調する方法

複数の列で重複をチェックしたい場合、列を比較するのではなく、すべての列で同じ項目のすべてのインスタンスを見つけるには、次のいずれかの解決方法を使用します。

1回目の出現を含む複数の列で重複を強調表示する

データセットに複数回出現する項目の最初のインスタンスが重複とみなされる場合、最も簡単な方法は、重複に対する Excel の組み込み規則を使用することです。

または、次の数式を使用して条件付き書式設定ルールを作成します：

COUNTIF(range, top_cell)>1

たとえば、範囲 A2 の重複を強調するために。5779>

=COUNTIF($A:$C, A2)>1

範囲 ($A$2:$C$8) には絶対セル参照を使用し、トップ セル (A2) には相対参照を使用していることに注意してください。
1 回目の出現を含む複数の列の重複を強調表示するルール

1 回目の出現を含まない複数の列の重複を強調表示する

このシナリオのソリューションは非常に厄介で、これに関するルールが Excel に組み込まれていないのも不思議はありません。)

初出を無視して複数の列で重複するエントリを強調表示するには、次の数式を使用して 2 つのルールを作成する必要があります。 最初の列に適用

ここで、1 つの列で初出がない重複を強調するために使用したのとまったく同じ式を使用します (詳細な手順は、こちらを参照してください)。

この例では、次の数式を使用して A2:A8 のルールを作成します。

=COUNTIF($A:$A2,$A2)>1

その結果、範囲の左端の列で初出のない重複したアイテムが強調されます (この例ではそのようなアイテムは 1 つだけです)。
複数列で初出のない重複をハイライトするルール1

ルール2.初出のない重複をハイライトするルール3.複数列で初出のない重複をハイライトするルール4.複数列で初出のない重複をハイライトするルール6. 残りの列 (B2:C8) の重複を強調表示するには、この式を使用します:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

上記の式では、最初の COUNTIF 関数は最初の列で与えられた項目の発生をカウントし、2 番目の COUNTIF は以降のすべての列で同じことを行います。 そして、それらの数値を足し合わせて、合計が1より大きいかどうかをチェックするのです。

その結果、1回目の出現を除くすべての重複する項目が見つかり、ハイライト表示されます。
Highlighting duplicates without 1st occurrence in multiple columns

Highlight duplicates in all columns with a single rule

別の解決策は、データセットの左に空の列を追加して、上記の数式を次のように 1 つの数式に結合します：

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

ここで B2 はターゲット範囲の 2 列目でデータがある一番上のセルです。
複数の列で初出がない重複をハイライトする別の可能なソリューション

数式をよりよく理解するために、2 つの主要部分に分解してみましょう：

  • 最初の列 (B) では IF 条件が満たされないため、2 番目の COUNTIF 関数のみが適用され、これはすでに 1 列で初出を含まない重複を見つけるのに使った通常の数式と同じものです。
  • 以降のすべての列 (C2:D8) では、2 つの COUNTIF 関数で絶対参照と相対参照を巧みに使っていることがポイントになります。 わかりやすくするために、G列にコピーして、他のセルに適用したときに式がどのように変わるかを確認できるようにしています。
    範囲内に1回も出現しない重複を強調する式

最初の列以外ではIF条件が常に真であるので（列数が1以上）、このようにして式が進みます。

  • 与えられた列の左側にあるすべての列で、与えられた項目（上のスクリーンショットではD5）の出現回数を数えます。 COUNTIF(B:$C,D5)
  • 指定された項目の列の中で、その項目のセルまでの出現回数を数えます。 COUNTIF(D:D5,D5)
  • 最後に、数式は両方の COUNTIF 関数の結果を加算します。

1 つの列の重複する値に基づいて行全体を強調表示する

テーブルに複数の列がある場合、特定の列の重複するレコードに基づいて行全体を強調表示したい場合があります。 しかし、カスタム数式ベースのルールは、行を網掛けにすることに何の問題もありません。 重要なポイントは、行全体を選択し、次のいずれかの数式を使用してルールを作成することです:

  • To highlight duplicates rows excluding 1st occurrence:
    =COUNTIF($A:$A2, $A2)>1
  • To highlight duplicates rows including 1st occurrence:
    =COUNTIF($A:$A, $A2)>1

ここで A2 は最初のセル、A15 は最後に使用したセルで、重複を確認したい列に含まれるもの。 次のスクリーン ショットは、両方のルールの動作を示しています。
キー列の重複値に基づいて行全体を強調表示する

How to highlight duplicate rows in Excel

前の例では、特定の列の重複値に基づいて行全体を着色する方法を示しました。 しかし、複数の列で同じ値を持つ行を表示したい場合はどうすればよいのでしょうか。 または、すべての列で完全に同じ値を持つ絶対重複行を強調表示するにはどうしたらよいでしょうか。

これには、複数の基準でセルを比較できる COUNTIFS 関数を使用します。 たとえば、A 列と B 列に同じ値を持つ重複行を強調表示するには、次の式のいずれかを使用します。

  • 初出以外の重複行を強調表示するには：
    =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
  • 初出で重複行を強調するには：
    =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

次のスクリーンショットは結果を示しています。

ご理解のように、上記の例はデモンストレーションのためだけのものです。 実際のシートで重複した行を強調表示する場合、当然ながら2列の値のみを比較することに限定されず、COUNTIFS 関数は最大 127 の範囲/基準ペアを処理できます。

Highlighting consecutive duplicate cells in Excel

場合によっては、列内のすべての重複を強調する必要はなく、連続した重複セル、すなわち互いに隣接するもののみを表示したいことがあります。 これを行うには、データのあるセル（列ヘッダーを含まない）を選択し、次の式のいずれかを使用して条件付き書式設定ルールを作成します：

  • 1回目の出現がない連続した重複を強調表示する場合。
    =$A1=$A2
  • 1 回目の出現がある連続した重複を強調表示する：
    =OR($A1=$A2, $A2=$A3)

次のスクリーンショットは、連続した重複するテキストを強調表示していますが、これらのルールは、数字と日付が連続して重複している場合も機能します。
Highlighting consecutive duplicate cells in Excel

Excel シートに空の行がある場合があり、連続した空白セルを強調表示したくない場合は、数式に次の改良を加えてください。

  • 1 回目の出現がない連続した重複セルを強調表示し、空白セルを無視するには：
    =AND($A2<>"", $A1=$A2)
  • 1 回目の出現がある連続した重複セルを強調表示して空白セルを無視するには：
    =AND($A2<>"", OR($A1=$A2, $A2=$A3))

ご覧のとおり、条件付書式を使って Excel で重複するものを強調することはたいしたことではありません。 しかし、もっと手軽で簡単な方法もあるのです。 それを知るには、このチュートリアルの次のセクションを読んでください。

Duplicate Remover を使用して Excel で重複を強調する方法

Duplicate Remover アドインは、Excel で重複したレコードを処理するオールインワンのソリューションです。 その名前に反して、このアドインは、重複したセルまたは重複した行全体を検索、ハイライト、選択、コピーまたは移動することができます。

Duplicate Remover は、あなたの Excel リボンに 3 つの新しい機能を追加します:

  • Dedupe Table – 1 つのテーブルですぐに重複を検索してハイライトすることができます。
  • Duplicate Remover – 1 つのテーブルで重複またはユニークな値を識別しハイライトするための高度なオプションを備えたステップバイステップのウィザード。
  • Compare 2 Tables – 2 つの列または 2 つの別々のテーブルを比較して重複を検出しハイライトします。

Ultimate Suite for Excel をインストールすると、Ablebits データタブの「Dedupe」グループでこれらのツールを確認できます。
Duplicate Remover for Excel

Highlight duplicates in Excel in couple of clicks

この例では、数百行の次の表を作成しました。 そして、目的は、3 つの列すべてで同じ値を持つ重複した行を強調表示することです。
重複した行を強調表示する必要がある Excel テーブルです。

信じられないかもしれませんが、マウスを 2 回クリックするだけで望ましい結果を得ることができます 🙂

  1. 表の任意のセルを選択し、[表の分離] ボタンをクリックすると、賢いアドインは表全体を取り込みます。
  2. [Dedupe Table] ウィンドウは、すべての列が自動的に選択されて開き、デフォルトでは [重複を色付け] オプションが選択されています。 ですから、あなたがしなければならないことは、［OK］をクリックすることだけです 🙂 完了！
Tip. 1 つまたは複数の列によって重複する行を検出したい場合は、無関係な列のチェックをすべて外し、キー列 (s) のみを選択したままにします。
選択した列 (s) に基づいて Excel で重複する行をすばやく強調表示することができます。

そして、結果は次のようになります。
最初に出現していない重複した行がハイライトされます。

上の画像にあるように、重複テーブル ツールは最初のインスタンスのない重複した行をハイライトしています。

最初の出現を含む重複を強調表示したい場合、重複ではなく一意のレコードに色を付けたい場合、またはデフォルトの赤色が気に入らない場合は、これらのすべての機能とさらに多くの機能を持つ Duplicate Remover ウィザードを使用します。

Highlight duplicates in Excel using an advanced step-by-step wizard

Dedupe Table ツールと比較して、Duplicate Remover ウィザードはより多くのクリックが必要ですが、多くの追加オプションでこれを補っています。

  1. 複製を強調表示したいテーブル内のセルを選択し、リボン上の [重複の削除] ボタンをクリックします。 ウィザードが実行され、表全体が選択されます。 このアドインは、念のため、表のバックアップコピーを作成することも提案します。 必要ない場合は、そのチェックボックスをオフにします。

    テーブルが正しく選択されたことを確認し、[次へ]をクリックします。

    Duplicate Remover ウィザードを実行します。

  2. 見つけたい次のデータ タイプのいずれかを選択します：
    • Duplicates except 1st occurrence
    • Duplicates with 1st occurrence
    • Unique values
    • Unique values and 1st duplicate occurrence

    この例のためにDuplicates + 1st occurrenceを見つけてあげましょう。
    Finding duplicates with 1st occurrence

  3. さて、重複をチェックする列を選択します。 完全な重複行を強調したいので、3 つの列をすべて選択しました。
    重複をチェックしたい列を選択します。

    さらに、アドインでは、テーブルにヘッダーがあるかどうか、空のセルをスキップするかどうかを指定することができます。

  4. 最後に、重複に対して実行するアクションを選択します。 選択、削除、コピー、移動、または重複を識別するためのステータス列の追加など、多くのオプションがあります。

今日は Excel で重複を強調するさまざまな方法を検討しているので、選択は明白です :)。 そこで、[色で塗りつぶす] を選択して、標準のテーマ色の 1 つを選択するか、または [その他の色] をクリックして、任意のカスタム RGB または HSL カラーを選択します。
異なる色で Excel で重複を強調表示する

完了ボタンをクリックして結果を楽しむ 🙂
重複行が選択した色で強調されます

Duplicate Removerアドインを使って Excel で重複したファイルを強調する方法はこのようなものでした。 ご自分のワークシートでこのツールを試してみたい方は、Excel 用のすべての時間節約ツールを含む究極のスイートの全機能を備えた試用版をぜひダウンロードしてください。 そして、コメントであなたのフィードバックを大いに歓迎します！

Duplicate Removerのパフォーマンスに満足している場合、私たちはこの非常に特別な機会を提供することをうれしく思います。

Ultimate Suiteのプロモーションコードを入手 – ブログ読者限定オファー！

  • Excelで重複を識別する方法（1テーブル内）
  • 2列またはテーブルを比較して一致と相違を調べる方法
  • Excelで重複した行を削除する方法

にも興味があるかもしれませんね。

Leave a Reply