Excelの重複の削除 色付けで確かめてから消す手順と表記の揺れの直し方

2 つの部署の名簿を 1 枚にまとめたら、同じ会社が 3 行も 4 行も並んでいた。宛名ラベルを印刷する前に重複を消したいけれど、消してはいけない行まで消えてしまいそうで、手が止まる。総務でも営業事務でも人事でも、よくある場面です。

Excel には、重複に色を付けて見つける機能と、重複を消す機能が別々にあります。色で見て、数えて確かめてから消す順番を守れば、取り返しがつかなくなることはまずありません。見た目は同じ会社なのに「株式会社」と「(株)」の違いで見つからない、という見落としの直し方までまとめました。

消す前に、元の表を別のシートに残す

重複の削除は、データを完全に削除します。Microsoft のページは、誤って情報が失われないよう、削除の前に元のデータを別のワークシートに移動またはコピーしておくことを勧めています。削除の直後なら Ctrl + Z で戻せますが、保存して閉じたあとは戻せません。

シートのコピーは、シート見出しを右クリックして行います。

1. 名簿のシート見出しを右クリックし、[移動] または [コピー] を選ぶ。
2. [コピーを作成する] にチェックを入れる。
3. [挿入先] でコピーを置く場所を選び、[OK] を選ぶ。

あとはコピーのほうで作業します。元のシートには触りません。

もう 1 つ、先に決めておくことがあります。「何が同じなら重複とみなすか」です。取引先リストを 1 社 1 行にしたいのか、同じ会社でも担当者ごとに 1 行ずつ残したいのかで、基準にする列が変わります。この記事の例では、会社名と担当者の組み合わせが同じなら重複とします。

次の表が、この記事で使う例です。8 行のうち、同じ人が 4 回出てくる行が混ざっています。

人の目で見ると、山田 太郎さんが 2・4・6・7 行目の 4 回、佐藤 花子さんが 3・8 行目の 2 回です。最終的に残したいのは、2・3・5・9 行目の 4 行です。

重複に色を付けて確かめる

最初は、消さずに見つけるだけです。会社名の列(A 列)のデータ部分を選び、次の順に進みます。

1. [ホーム] タブの [条件付き書式] を選ぶ。
2. [セルの強調表示ルール] から [重複する値] を選ぶ。
3. [値] の横のボックスで色などの書式を選び、[OK] を選ぶ。

例の表では、「株式会社青葉商事」の 2・7・9 行目に色が付きます。次の 2 点も押さえておきます。

• 一般的には、1 回目のセルにも色が付く。どの行を消すかは、色だけでは決められない。
• 9 行目は、同じ会社の別の担当者。会社名だけを見たので、色が付いている。

つまり、色の付いたセルがすべて消してよい行とは限りません。反対に、4 行目の「(株)青葉商事」と 6 行目の「青葉商事株式会社」には色が付いていません。文字列が完全に同じではないためで、見落としの原因になります。

なお、ピボットテーブル レポートの値の領域では、重複を強調表示できません。

何回目に出たかを COUNTIF で数える

色だけでは「1 回目」と「2 回目以降」を分けられません。そこで、作業用の列に COUNTIF 関数を入れ、何回目に出てきた値かを数えます。COUNTIF は、範囲の中で検索条件に合うセルの数を返す関数で、書式は COUNTIF(範囲, 検索条件) です。

電話番号(D 列)の右の E 列に、見出し「何回目」を付けます。2 行目に次の式を入れて、最終行までコピーします。

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

範囲の始点だけを $ で固定しているので、下の行にコピーすると A2:A3、A2:A4 と、範囲が 1 行ずつ伸びます。「自分の行までに、同じ値が何回出たか」が分かり、1 なら初出、2 以上なら 2 回目以降です。

2 回目以降だけを見るには、フィルターを使います。

1. 見出し行のセルを選び、[データ] タブの [フィルター] を選ぶ。
2. 「何回目」の列の矢印を選び、[(すべて選択)] を外して、1 以外にチェックを入れる。
3. [OK] を選ぶ。

絞り込んだ行は、消す前の候補です。内容を目で見て、本当に不要な行かを確かめます。Microsoft のページによると、フィルターは行を一時的に非表示にするだけで、データは消えません。

COUNTIF には、知っておきたい動作があります。

重複の削除で消す 基準にする列で結果が変わる

確認が済んだら、フィルターをクリアして全行を表示し、重複の削除を使います。[データ] タブの [データ ツール] にある [重複の削除] です。

1. 名簿の範囲を選ぶ(表の中のセルを 1 つ選んでも構いません)。
2. [データ] タブで [重複の削除] を選ぶ。
3. ダイアログの [列] で、同じかどうかを見る列にチェックを入れる。
4. [OK] を選ぶと、削除された重複値の数を知らせるメッセージが出る。

ポイントは 3 番目です。Microsoft のページには、選ばなかった列のデータもあわせて消えると説明があります。たとえば Column1 と Column2 だけを選ぶと、2 つの列の値が両方とも同じ行が重複とされ、重複が見つかった行は、選ばなかった列のデータも含めて行全体が削除されます。

例の表で、基準を変えると、次のように結果が変わります(完全に同じ文字列の行だけが対象で、表記の揺れはまだ直していません)。

会社名だけを選ぶと、「株式会社青葉商事」の 2 行目が残り、7 行目と 9 行目が消えます。9 行目は、別の担当者である田中さんです。名簿の目的が担当者ごとの連絡先なら、これは誤った削除です。

そのほかの決まりを、表にまとめます。

Microsoft のページによると、Excel for the web でも重複する値を削除できます。

見落とす原因は、表記の揺れにある

ここまでの方法は、文字列が完全に同じ値だけを重複とみなします。例の表では、山田 太郎さんが 4 回出ているのに、会社名と担当者を基準にした重複の削除では 1 行しか消えません。残りは、見た目が似ているだけで、Excel には別の文字列だからです。

名簿でよく起きる揺れと、直し方は次の 6 つです。

全角と半角(ASC と JIS)

ASC 関数は、全角の文字を半角に変えます。JIS 関数はその反対です。Microsoft のページには、ASC は Microsoft 365、Excel 2024、2021、2019、2016 が対象と書かれています。JIS のページの適用先は、Mac 版が中心の書き方になっているので、使う前に手元の Excel で動くかを試してください。

前後の空白(TRIM と SUBSTITUTE)

TRIM 関数は、単語の間の空白を 1 つ残し、不要な空白をすべて削除します。他のアプリケーションから読み込んだ文字列に向いています。ただし Microsoft のページには、TRIM は半角のスペース(文字コード 32)用に作られていて、Web ページで使われる改行しないスペース(コード 160)は消せないと書かれています。

全角の空白は、SUBSTITUTE 関数で消します。たとえば =SUBSTITUTE(B2,"　","") は、B2 の全角の空白をなくします。姓と名の間の空白も一緒に消すと、「山田 太郎」「山田　太郎」「山田太郎」が同じ文字列になります。

株式会社と(株)、前株と後株

「株式会社青葉商事」「(株)青葉商事」「青葉商事株式会社」は、人にとっては同じ会社でも、Excel には 3 つの別の文字列です。照合用の列で「株式会社」と「(株)」を取り除くと、前株でも後株でも、残るのは「青葉商事」です。かっこが全角か半角かの違いもあるので、両方を取り除く式にします。

取り除くのは照合用の列だけにします。元の会社名の列まで変えると、「株式会社A」と「有限会社A」のような別の会社が、区別できなくなるおそれがあります。

ふりがなの有無

2 つの名簿を合わせると、片方にしかふりがなの列が無いことがあります。ふりがなの列を基準に含めると、ふりがなが空の行は別の行とみなされるので、基準からはずします。Microsoft のページに書かれているとおり、選んだ列だけが比較に使われます。

PHONETIC 関数は、文字列からふりがなを取り出します。地域設定が日本語などの極東言語である必要があります。「渡辺」と「渡邊」のような漢字の違いを見つける手がかりになりますが、同姓同名の別の人も拾います。候補を探すためだけに使い、消すかどうかは目で決めてください。

電話番号のハイフン

「03-5550-0101」と「0355500101」は、別の値です。SUBSTITUTE 関数でハイフンを取り除くと、同じ値になります。ハイフンには半角の「-」、全角の「－」、長音の「ー」などがあるので、必要なものを順に取り除きます。先頭の 0 が消える問題は、CSVをExcelで開くと文字化け・0が消えるときの直し方 取り込みと保存の手順で説明しています。

置換(Ctrl+H)で直す

照合用の列を作らず、データそのものを直す方法が置換です。Ctrl + H で [検索と置換] を開き、[検索する文字列] と [置換後の文字列] を入れて、[すべて置換] を選びます。[セルの内容全体を一致させる] のオプションと、検索する範囲(シートかブックか)も選べます。

置換は、元のデータを書き換えます。必ず、コピーしたシートで行ってください。

照合用の列を作って、そろえてから数える

揺れの直し方がいくつもあっても、元の列を直接いじるのは危険です。そこで、会社名と担当者をつなげて表記をそろえた「照合用」の列を作り、その列で重複を調べます。元の列は、そのまま残ります。

F 列に見出し「照合用」を付け、2 行目に次の式を入れます。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A2&B2)," ",""),"　",""),"株式会社",""),"(株)",""),"（株）","")

式は内側から順に働きます。会社名と担当者をつなげて ASC で半角にそろえ、半角の空白、全角の空白、「株式会社」、半角の「(株)」、全角の「（株）」の順に取り除きます。ASC は、全角のカタカナも半角にします。照合用の列に半角のカタカナが並んで見えますが、比べるためだけの列なので、そのままで構いません。

例の表に入れると、次の結果になります。E 列の「何回目」は、前の章の式の参照先を =COUNTIF($F$2:F2,F2) に替えたものです。

「何回目」が 2 以上の 4・6・7・8 行目が、消す候補です。会社名と担当者が同じ人が、表記の違いを越えて見つかりました。9 行目の田中さんは、1 のままなので残ります。

最後に、[重複の削除] を開き、[列] は「照合用」の列だけにチェックを入れます。最初に出てきた 2・3・5・9 行目の 4 行が残り、重複の削除は 4 行分を削除したと知らせます。照合用と何回目の列は、役目を終えたら削除してください。

UNIQUE 関数との違い

UNIQUE 関数は、範囲に含まれる値から重複を除いた一覧を、別の場所に作ります。元の表は変わりません。重複の削除は元の表から行を消す機能なので、使い分けは次のようになります。

UNIQUE にも、表記の揺れを見分ける力はありません。照合用の列をそろえてから使う点は同じです。UNIQUE の書式と使いどころは、事務で使うExcelのショートカットと関数 集計と日付の計算が速くなる基本に載せています。

個人情報の名簿を扱うときの注意

氏名や電話番号が入った名簿は、個人情報です。作業用にコピーしたシートや、保存し直したファイルを、そのまま置いておかないようにします。一般的な注意として、次の 3 点を守ると安心です。

• 作業が終わって結果を確かめたら、コピーのシートと照合用の列を消す。
• 作業中のファイルを、共有フォルダや他の人に送るメールにそのまま入れない。
• 見せる範囲は、必要な人だけに絞る。

個人情報の保管と共有の考え方は、情報システムの担当者に相談すると確実です。

コピーのシートで試す段取り

実際の名簿でいきなり始めず、次の順番で進めます。

1. 名簿のシートをコピーする([移動] または [コピー] で [コピーを作成する])。
2. 基準を決める。会社単位か、会社と担当者の組み合わせか。
3. 名簿のコピーで、基準の列に [重複する値] のルールで色を付け、見た目で確かめる。
4. 表記の揺れが見つかったら、照合用の列を作る(ASC・SUBSTITUTE・TRIM)。
5. 照合用の列に COUNTIF の式を入れて、2 回目以降の行をフィルターで絞り、目で確かめる。
6. フィルターをクリアして、[重複の削除] で照合用の列だけを選ぶ。
7. 残った行数が、予想と合っているかを確かめる。

今後、揺れを増やさないための入力時の工夫もあります。部署名や区分は、入力欄をプルダウンにすると表記がそろいます。手順は、Excelのプルダウンの作り方 入力規則で選択肢を作り入力ミスを減らす手順に書きました。