Excelの「重複の削除」を使うと、重複した行は削除され、下の行が上に詰まります。しかし、同じ取引先名が並んでいても、隣の列の日付や金額はそれぞれ別のデータ、ということがよくあります。そのような表で行を詰めると、データ自体が失われてしまいます。
この記事では、行を消さずに、ある列の2件目以降の重複だけを空白にする方法を紹介します。値は残したまま見た目だけ目立たなくする方法もあわせて扱います。行の位置と隣の列との対応は、どの方法でも変わりません。
先に決めておくこと
作業の前に、次の3つを決めます。
- どの列で重複を判定するか：取引先名の1列だけで判定するのか、「取引先名＋日付」のように複数の列の組み合わせで判定するのかを決めます。
- どれを残すか：上から見て最初の1件を残すのか、最後の1件を残すのかを決めます。
- 値を消すか、見た目だけ変えるか：それぞれの違いは次の表のとおりです。
| やり方 | 元の値 | 合計・ピボット・CSV出力 | 元に戻しやすさ |
|---|---|---|---|
| A：重複を空白にした列を新しく作る | 元の列にそのまま残る | 新しい列を使えば重複は含まれない | 元の列が残るので戻しやすい |
| B：絞り込んで値を消す | 消える | 重複は含まれない | 作業前のコピーが必要 |
| C：条件付き書式で目立たなくする | 残る | 重複も含まれる | 書式を消せば元どおり |
どの方法でも、作業前にシートかファイルのコピーを残しておいてください。
2件目以降を見分ける式
ここでは、判定する値がA2:A500に入っているものとします。空いている列（たとえばE列）のE2に次の式を入れ、最終行までコピーします。
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)
$A$2:A2 の部分は、下の行にコピーするほど範囲が広がります。つまり「1行目から自分の行までに、同じ値がいくつあるか」を数えています。2件目以降の行だけが TRUE になります。空欄の行は A2<>"" の条件で除いています。
目的に応じて、式を次のように変えます。
- 複数の列の組み合わせで判定する：
=AND(A2<>"",COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1) - 最後の1件を残す：
=AND(A2<>"",COUNTIF(A2:$A$500,A2)>1)（自分の行から下の範囲を数えるので、最後の1件以外が TRUE になります） - 重複が何件あるか数える：
=COUNTIF(E2:E500,TRUE)
COUNTIFには、次のような判定のくせがあります。
- 英字の大文字と小文字を区別しません。
- 値に
*や?が含まれていると、ワイルドカードとして扱われます。 - 「001」と「1」のような、数字に見える文字列を同じ値として数えることがあります。
- 255文字を超える文字列は正しく数えられません。
これらの区別が必要な場合は、次の式に置き換えてください。データが多いと計算が遅くなりますが、文字が完全に一致するものだけを重複として数えます。
=AND(A2<>"",SUMPRODUCT(--EXACT($A$2:A2,A2))>1)
方法A：重複を空白にした列を新しく作る
いちばん安全なのは、元の列を残したまま、重複を空白にした列を別に作る方法です。F2に次の式を入れて、最終行までコピーします。
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",A2))
外側の IF(A2="","", ...) は、元が空欄の行に「0」が表示されないようにするための条件です。元の値が日付の場合は、F列に日付の表示形式を設定してください。
集計や印刷にはF列を使います。A列はそのまま残るので、あとで「どの値が重複していたか」も確認できます。
A列そのものを置き換えたい場合は、F列をコピーしてA列に「値の貼り付け」をします。ただし、数式の "" を値として貼り付けると、見た目は空欄でも完全な空白にならないことがあります（COUNTAで数えると1件になる、など）。完全な空白が必要なら、次の方法Bを使ってください。
方法B：判定列で絞り込んで値だけを消す
A列の重複セルを、完全な空白にする方法です。
- E列に、前述の判定式を入れます。
- E列をコピーし、同じ場所に「値の貼り付け」をします。 数式のままA列を消すと、判定がやり直されて結果が変わってしまうためです。値にしておけば、どの行を消したかの記録としても残ります。
- 見出し行を選んで「データ」→「フィルター」を設定し、E列を TRUE だけに絞り込みます。
- A列の、表示されているセルの範囲だけを選びます。続けて Alt + ;（セミコロン）を押し、表示されているセルだけを選択した状態にします。
- Deleteキーで値を消します。行の削除ではなく、セルの値を消すだけです。
- フィルターを解除し、TRUE の行のA列が空白になっていること、ほかの列が元のままであることを確認します。
複数の列の組み合わせで判定した場合は、手順4と5で消す列（たとえばA列だけか、A列とB列の両方か）を先に決めてから選んでください。
方法C：消さずに目立たなくする（条件付き書式）
印刷や画面表示で重複が見えなければよく、値は残しておきたいときの方法です。
- A2:A500 を選びます（A2がアクティブセルになるようにします）。
- 「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
- 数式に
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)と入力します。 - 書式で、文字の色を薄いグレーにします。完全に見えなくしたい場合は、「表示形式」の「ユーザー定義」で
;;;を指定します。
この方法では値が残っているため、SUM、ピボットテーブル、CSV出力には重複も含まれます。見た目だけを変える方法だと理解したうえで使ってください。
なお、重複した行をフィルターで非表示にする方法もありますが、その場合は行全体が隠れ、隣の列の日付や金額も見えなくなります。ほかの列はそのまま見せたいなら、方法Cのようにセルの表示だけを変えます。
処理後に確認すること
- 行数が変わっていないか：重複を消していない列（たとえば日付の列）の件数を
=COUNTA(B2:B500)で数え、作業前と同じかを確かめます。 - 消した件数が合っているか：作業前のA列の件数から作業後の件数を引いた数が、判定列の TRUE の件数と一致するかを見ます。
- 残った値に重複がないか：Microsoft 365のExcelなら、
=COUNTA(A2:A500)と=COUNTA(UNIQUE(FILTER(A2:A500,A2:A500<>"")))の結果が一致すれば、重複は残っていません。 - 隣の列との対応がずれていないか：いくつかの行を選び、作業前のコピーと見比べて、日付・金額などが同じ行に残っているかを確認します。
