重複データに連番を振りたいけど、やり方がわからない…
大量のデータを処理すると時間がかかりすぎて困る…
そんなお悩みを解決します！
【本記事の内容】
・重複データの整理や連番付けを簡単な手順で実現
・大量データでも軽快に動作する効率的な方法を紹介
・エラーなく処理できるテクニックを解説
本記事を書いた私は、管理職を10年以上経験し、エクセルのショートカットキーや関数、マクロを駆使して仕事の効率化を実現してきました。爆速すぎて楽しく仕事できています。
実際、本記事で紹介する方法を使えば、数万件のデータでも数分で整理・連番付けが完了します。エクセルが苦手な方でも、手順に従えば簡単に実践できるでしょう。
重複データの整理や連番付けで悩んでいる方は、ぜひ最後までお読みください。
あなたの作業効率が劇的に改善するはずです！
重複データへの連番付け実践ガイド
重複データごとに連番を振る方法
重複データごとに連番を振るには、COUNTIF関数を使います。この方法では、データの出現順に1から順番に番号が振られます。
例えば、A列にデータがある場合、B1セルに次の関数を入力します。
=COUNTIF($A$1:A1,A1)この数式をB列全体にコピーすると、同じデータの出現順に応じて番号が振られます。
特定の内容の重複回数を確認したい時などに便利ですね。
一意の識別番号をつける方法
「重複データごとの連番だけだと管理に使えない！」という時は、「データ内容-出現順」形式の一意IDを作成しましょう。
使う関数はコレ↓
=A1 & "-" & COUNTIF($A$1:A1, A1)
先ほどと違って「完全に同じ内容」がないため、個別で識別したい時にすごく便利！
活用する場面の例を紹介しておきます。
顧客や商品ごとの取引管理
利用シーン: 顧客や商品のデータが複数行にわたって存在し、同じ名前の顧客や商品が複数回登場する場合。
活用方法: 顧客名-出現順や商品名-出現順のようにIDを付けることで、同じ顧客や商品が再度現れても、その順序がわかるため、特定の顧客や商品の取引履歴を順番通りに追跡しやすくなります。
例: 顧客「山田太郎」が3回登場した場合、それぞれ「山田太郎-1」「山田太郎-2」「山田太郎-3」となり、各取引を一意に識別可能。
プロジェクトのタスク管理
利用シーン: 同じプロジェクト名やタスク名が異なる行に並ぶことがあるが、それぞれを一意に管理する必要がある場合。
活用方法: プロジェクト名-タスク名-出現順のように番号を付け、各タスクを順番通りに管理できます。これにより、同じタスク名が登場しても、異なるIDで追跡可能。
例: プロジェクト「A」のタスク「デザイン」が2回登場する場合、「A-デザイン-1」と「A-デザイン-2」とすることで、どのタイミングでのタスクかを区別可能。
在庫管理や商品登録
利用シーン: 同じ商品名であっても、入庫のたびに新しいエントリーとして登録する必要がある場合（例：ロット管理）。
活用方法: 商品名と出現順に基づいた番号（例：商品名-ロット番号）で管理することで、どの入庫がどのロットかを識別できる。これにより、在庫のトレースや追跡が可能。
例: 「商品A」が3回入庫した場合、「商品A-1」「商品A-2」「商品A-3」のようにし、ロット管理の一環として一意に識別。
データ入力時のエラー確認や重複検出
利用シーン: 同じデータが複数回入力されることがあるが、何度も同じデータを入力する際に一意の識別番号が必要な場合。
活用方法: データ内容-出現順で番号を付けることで、データが意図的に重複しているか、誤って複数回入力されているかを簡単に確認できる。特に、大規模データのクリーニング作業で役立つ。
例: 「製品コード123」が複数行に入力されている場合、「123-1」「123-2」のように表示することで、重複の発生タイミングがわかる。
アンケートやフォームの回答データ管理
利用シーン: 同じ回答が複数の人から得られる場合や、同じ項目が複数回回答される可能性がある場合。
活用方法: 回答の内容と出現順に基づく一意のIDを付けて管理することで、誰の回答がどの順序で得られたかを区別できます。これにより、同じ回答内容であっても異なるエントリーとして管理可能。
例: 回答「A」が5人から同じ内容で寄せられた場合、「A-1」「A-2」「A-3」…とすることで、どの順序で得られた回答かが識別可能。
同じイベントの履歴管理
利用シーン: 同じイベントが複数回発生するケース（例：定期的な点検やチェックリストの履行記録など）。
活用方法: イベント名-発生順のような形式で識別番号を付けて、いつどの順序でイベントが発生したかを管理。これにより、履歴管理が容易になり、定期点検や繰り返しタスクの進捗を確認しやすくなります。
例: 「月次点検」が毎月行われている場合、「月次点検-1」「月次点検-2」…とすることで、何回目の点検かがわかり、進捗の把握がしやすくなります。
重複データのチェックと可視化
これから紹介するのは「重複データの確認」をする方法です。
重複を削除したいだけであれば「データ」タブの「重複の削除」機能を使えば一発なので、ここでは触れません。
COUNTIF関数で重複を瞬時に確認する方法
COUNTIF関数を使えば、データの重複をすぐに見つけられます。
使い方は「=COUNTIF(範囲,検索値)>1」です。
=COUNTIF(A:A,A1)>1結果がTRUEかFALSEで表示されるので、一目で判断可能です。大量のデータでも素早く結果が出るので、とても便利。
条件付き書式を使った重複の視覚化手順
条件付き書式を使うと、重複しているデータを色で分けて見やすくできます。
①チェックしたい範囲を選択
②「ホーム」タブ→「条件付き書式」を選択
③「セルの強調表示ルール」→「重複する値」を選択
④文字色や背景色などを好きに設定する
コレは多用してます！
やっぱり、見て瞬時に判断できるほうがストレス少ないですよね。
複数列の重複をチェックするテクニック
複数の列にまたがる重複をチェックしたい時は、COUNTIFS関数を使いましょう。
=COUNTIFS(A:A,A1,B:B,B1)>1A列とB列の値の両方が一致している時は「TRUE」と表示されるため、簡単に重複を発見できます。
複数列の重複をチェックするテクニック+α
重複の確認はできたけど…
データが「どの場所で」重複しているか知りたい。
大量のデータから目視で探すのは大変すぎて困る。
そんな時はINDEXとMATCH関数の組み合わせが便利です！
この表では、商品名と仕入日の組み合わせに基づいて該当する番号を取得し、重複をチェックしています。重複が番号で表示されるため容易に発見可能。
使用している関数は「=INDEX(連番配列,MATCH(検索値,検索範囲,0))」で、この方法は大量のデータを処理する際に特に有効です。
具体的な設定手順
画像の構造を再現したいのであれば、必要なのは
・「番号列」
・「商品名+仕入日」のような、検索したい内容の結合列
・関数入力列
の三つです。
①まずは番号列を作りましょう。ROW関数を使うと簡単に作成できるためおススメ
=ROW()-2②次に、検索したい内容の結合列を作成します。「＝A1&B1」のようなシンプルなもので良いのですが、今回は日付を連結したいため、シリアル値にならないようにTEXT関数を使用します
= B3 & TEXT(C3, "YYYY/MM/DD")③最後にINDEXとMATCHの複合関数を入力します
=INDEX($A$3:$A$27, MATCH(B3 & TEXT(C3, "YYYY/MM/DD"), $D$3:$D$27, 0))重複の確認だけが目的ならCOUNTIFS関数で十分ですが、重複箇所の特定、データの抽出やクレンジングが必要な場合には、INDEXとMATCH関数の方が便利です。
INDEXとMATCH関数の組み合わせは、以下のような場面で役に立ちますよ！
在庫管理と重複チェック
同じ商品が同じ日に仕入れられた場合、重複データとして扱うことができます。
例えば、仕入れデータの入力ミスや、在庫が正確に反映されているかを確認する際に、同じ商品が重複していないかをすばやく確認できます。
返品・交換や棚卸のチェック
売れ残りや返品・交換が発生した際に、どの仕入れ分の在庫が対象なのかを迅速に特定することができます。
同じ商品が異なる仕入日で存在する場合、その仕入日情報を利用して具体的なロットを絞り込むことが可能です。
データの正規化やクレンジング
データ分析やレポートを行う前に、重複データを検出・排除するためのクレンジングが必要です。
統合された列でデータの重複を検索すれば、手間がかからず、ミスが少なくなります。例えば、ピボットテーブルを使って重複の有無を確認するなど、データ整理に役立ちます。
レポートや分析の精度向上
売れ筋や仕入れ頻度の分析の際、重複データがあると正確な傾向がつかめません。
特定の商品がどのタイミングで、どの頻度で仕入れられているかを確認するために、重複を避けたデータで分析することが大切です。この表形式であれば、重複を除いた分析が簡単に行えます。
最新のMAP/LAMBDA関数を使用した効率的な方法
Excel 365では、MAP/LAMBDA関数を使った新しい連番付け方法が可能です。
これらの関数を使うと、より少ない数式でスマートな連番付けが可能に。
例えば、A1:A10に重複を含むデータがある場合、B1セルに
=LET(data,A1:A10,MAP(data,LAMBDA(x,COUNTIF(data,x))))と入力すると、同じデータに同じ番号が振られます。
この方法は、従来の方法と比べてファイルサイズを抑えられ、処理速度も向上します。
【MAP/LAMBDA関数の利点】
・コードの可読性が高く、メンテナンスが容易
・大規模データセットでも快適に動作する
・ファイルサイズを抑えられる
・複雑な処理を1つの数式で表現できる
重複データの集計方法
重複データを集計する際は、ピボットテーブルや集計関数を使いましょう。
例えば、COUNTIF関数を使って各データの出現回数を数えてから、重複が多い順に並べ替えれば、どのデータが最も重複しているかがわかります。
また、グラフ機能を使えば重複の傾向を視覚的に理解しやすくなります。
こうした分析結果はデータの整理や改善に役立ちますし、定期的に分析レポートを作れば、データの品質を継続的に高められます。
連番付けの最速テクニック
大量データでも処理が軽く、最速で連番を付ける方法
結論＝SEQUENCE関数を使います。
大量のデータを扱った時のエクセルの動作遅延を防ぎ、最も楽に連番を作れて、途中のセルを消しても自動で補填してくれる神関数。それがSEQUENCEです！
使い方はいたってシンプルで、対象セルに
=SEQUENCE(100)と入力するだけ。（）内の引数は作成したい連番の数量なので、調整して使ってください。
そんな神関数なSEQUENCEですが、実はデメリットもあるんです…
旧バージョンのエクセルでは非対応
SEQUENCE関数はExcel 365やExcel 2019以降で利用可能な関数であり、古いバージョンでは使用できません。
古いバージョンと互換性を持たせる場合には他の方法（例えばROW関数を使った連番生成）を考える必要があります。
動的配列によるセルの上書きリスク
SEQUENCE関数は、動的配列として範囲に出力します。もしSEQUENCE関数が出力した範囲の一部を手動で上書きした場合、エラーが発生することがあります。
そのため、関数で生成した範囲を編集しないように注意が必要です。
セルのリファレンスが難しい
SEQUENCE関数は連続した範囲に配列を返すため、他のセルやシートから特定の値を参照するのが難しい場合があります。
通常の数式で1つずつのセルを指定するのと違い、範囲全体を返すため、リファレンスや結合操作には工夫が必要です。
複雑な数式の中で使うと計算負荷が増える可能性
SEQUENCE関数自体は軽量ですが、他の関数（SUMPRODUCTやFILTERなど）と組み合わせて複雑な計算をする際には、動的配列の範囲が大きくなるとExcelの処理が重くなる可能性があります。
特に大規模なデータ処理を繰り返す場合は注意が必要です。
トラブルシューティングとベストプラクティス
連番付け時の一般的なエラーと解決方法
作業中にエラーが発生したり、希望と違う動作になると「あー…」ってなりますよね。原因がパッとわかれば良いのですが、疲れてる時なんかはウンザリしちゃいます。
そこで、代表的なエラーや問題点だけでもサクッと解決できるように紹介しておきます！
・「#REF!エラー」→セルの参照範囲を確認し、必要に応じて修正する
・「#VALUE!エラー」→セルの書式を統一し、数値と文字列が混在していないか確認する
・「数式をコピーした時に参照がずれる」→$A$1のように、変更したくない部分に$マークを付ける
・「重複判定がうまくいかない」→TRIM関数を使って不要なスペースを削除する
・「大量のデータを処理する時にフリーズする」→SUMIFS関数やVLOOKUP関数の代わりに、より高速なINDEX-MATCH関数を使用する
大量データ処理時の注意点と対策
大量のデータを扱う時は、システムの負荷を減らすことが大切です。
まず、不要な書式設定や条件付き書式を削除してファイルサイズを小さくしましょう。意外と重くなるため、これだけでも変化があります。
また、計算をする時はできるだけテーブル機能を使いましょう。重複計算を避けつつ動的に範囲を管理し、無駄を省いて処理を高速化できるため動作が安定します。
「名前の定義」これも便利な機能なのですが、ファイルサイズ肥大化の原因になるためデータ量が多いときは使わないほうが無難ですね。
大量のデータを扱う時に大事なのは「シンプルイズベスト」という考え方。できるだけ装飾を減らし、余計な機能を使わず、エクセルに計算だけ行わせるイメージでいましょう。
膨大なデータが含まれるシートなんて客先に見せない場合がほとんどですので、見た目の華やかさなんて不要ですよ。
データ形式による処理の違いと対応方法
データの形式によって処理方法を変えると、エラー防止につながります。
・「文字列データの統一」UPPER関数やLOWER関数を使用して大文字小文字を揃える
・「数値データの調整」ROUND関数で小数点以下の桁数を統一する
・「日付データの形式統一」TEXT関数を使用して日付の表示形式を指定する
・「データ型の変換」VALUE関数で文字列を数値に、TEXT関数で数値を文字列に変換する
・「特殊文字の処理」CLEAN関数で印刷できない文字を削除する
まとめ
今回は、重複データの連番付けと管理に関する方法を8つ紹介しました。
1.COUNTIFで重複データに同じ番号を付与
2.ROW関数で出現順に連番を自動生成
3.INDEX・MATCHで複雑な条件にも対応
4.SEQUENCE関数で最速の連番付け
5.条件付き書式で重複を視覚的に確認
6.MAP/LAMBDAで効率的な処理を実現
7.大量データでも軽快に動作する手法
8.エラーを防ぐトラブルシューティング
これらの方法をマスターすることで、数万件のデータでも数分で整理できるようになります。重複データの管理が格段に楽になり、作業効率が劇的に向上するでしょう。
特に大規模なデータを扱う現場では、作業時間を大幅に削減できます。エクセルの基本機能を最大限に活用し、より正確で効率的なデータ管理を実現しましょう。
今回の内容を実践することで、あなたのエクセルスキルは確実に一歩先へ進むはずです！
