Excelで「別の列のデータをもとに自動的に番号を振りたい」というご要望を耳にすることがあります。私自身も商品管理や顧客リストなどで同じ困りごとを抱えていました。そこで、列Eに入力した値から重複なく連番を効率よく生成するテクニックをまとめます。これを覚えておくと、日々の作業がぐっとスムーズになっていくはずです。
Excelで別の列を参照しながら自動連番を振る魅力
Excelには多彩な関数がそろっており、ひと工夫加えることで管理の手間を大幅に削減できます。特に在庫管理や顧客リスト、タスク管理などで「同じデータが重複していたら同じ番号を再利用しつつ、新しいデータには新しい連番を振りたい」といった要望はよくあります。私が初めて同様の仕組みを作ろうとしたときは関数の組み合わせに苦戦しましたが、慣れるととても便利で、むしろ作業効率が向上した実感があります。
利用シーンの多さ
企業で共用しているExcelファイルや、個人で使う小規模な在庫リストなど、多岐にわたる管理業務でこの仕組みが役立ちます。例えば列Eには商品ID、列Aには商品IDに対応する連番を振りたい場合や、顧客メールアドレスを参照して顧客ごとに番号を割り振っておきたい場面などです。こうした場面では、単純に数式をコピーするだけでミスを減らしながら集計や検索ができるようになります。
自動連番のメリット
準備と前提条件
私はまず作業シートを作るとき、どの列に何を入力するのかをあらかじめ決めています。例えば、列Eを「基準となるデータ(商品IDや顧客のメールアドレスなど)」、列Aを「自動連番」と設定します。下記のようなイメージです。
列 | 内容 | セル範囲 | データ例 |
---|---|---|---|
A列 | 自動番号 | A5~A34 | 1, 2, 2, 3… |
E列 | 基準となる入力データ | E5~E34 | 商品ID、名前、メールアドレスなど |
使用するExcelのバージョン
Excel 2019以降をお使いであればXLOOKUP関数が標準で利用可能です。もしXLOOKUPが使えない場合でもVLOOKUPやINDEX/MATCHなどで代用できます。ただし、この記事ではXLOOKUPの書き方が主体となります。
セルのデータの状態
列Eにユーザー自身が直接数字や文字を入力する場合と、別のセルから数式で値を引っ張ってきており、空文字列が入っている場合とで振る舞いがやや変わります。空白や0の扱いに注意が必要です。
基本的な自動番号付けの方法
数式全体の考え方
まず列Aのセルに入力する数式の概念を理解しましょう。手動で一つひとつ重複の有無を確かめるのはとても大変です。関数を利用して「既に入力されている値なら同じ番号を再利用し、新しい値なら直前までの最大番号に1を足す」という流れを自動化します。
重複確認
重複確認にはCOUNTIF関数が便利です。あるセルに入った値がそれまでのセル範囲内で何回登場しているかを数えることができます。COUNTIF($E$4:$E4, $E5)が0より大きければ重複、0であれば新規の値になります。
XLOOKUPによる既存番号の参照
既に割り当てられている番号を探すには、COUNTIFで重複が確認できたタイミングでXLOOKUPを使い、同じ値が最初に出現したときの番号を参照します。XLOOKUP($E5, $E$4:$E4, $A$4:$A4)で、列Eの同値と対応する列Aの番号を取得します。
MAX関数による新規番号の採番
新しい値である場合は、A列の既存番号の最大値を取り、それに+1することで新たな番号を付与します。MAX($A$4:$A4)+1と書くと、A5セルからA4セルの最大値が参照されます。
実際の数式例
それでは、列Aの5行目(A5セル)に数式を入力し、下方向へオートフィルする例を紹介します。
=IF($E5="","",
IF(COUNTIF($E$4:$E4,$E5)>0,
XLOOKUP($E5,$E$4:$E4,$A$4:$A4),
MAX($A$4:$A4)+1
)
)
こうすることで、E5セルが空白でなければ重複チェックと新規番号付与を行い、空白であればそのまま空白を表示します。私が実際にこの数式を初めて設定したときは、あまりにもあっさり動いたので「もっと複雑だと思っていたのに」と拍子抜けした記憶があります。
列Eが0の場合も空白にしたいとき
0を無視する数式例
もし列Eが0のときにも番号を振りたくない場合は、下記のようにIF($E5=0,””,…)の条件を加えます。
=IF($E5=0,"",
IF(COUNTIF($E$4:$E4,$E5)>0,
XLOOKUP($E5,$E$4:$E4,$A$4:$A4),
MAX($A$4:$A4)+1
)
)
こうすることで、E列が0だった場合はA列を空白にしておき、実際にデータが入力されているときだけ番号を割り振ります。
空文字列も一括で判定する方法
列Eに数式が入っており、見た目上は空白でも実際には””(空文字列)が入っている場合があります。そこでOR関数を用いて複数条件をまとめると、より確実に空白や0を無視できます。
=IF(OR($E5={"",0}),"",
IF(COUNTIF($E$4:$E4,$E5)>0,
XLOOKUP($E5,$E$4:$E4,$A$4:$A4),
MAX($A$4:$A4)+1
)
)
私は以前、別シートから参照した値が実は空文字列だったケースに気づかず、なぜか自動番号が振られてしまうバグに悩まされました。こうしたORでまとめておくと誤動作を防ぎやすくなります。
実務での活用事例
顧客リスト管理での活用
私が携わったケースで、顧客メールアドレスに基づき連番を振る必要がありました。既存の顧客であれば同じ番号を割り当て、新規の顧客であれば新しい番号を加算していきます。この方法だと、営業担当がメールアドレスを入力するだけで自動的に番号管理ができるようになり、重複入力による二重登録を防げました。
実際の作業フロー
1. 営業担当が新規顧客のメールアドレスを列Eに入力
2. A列に既に対応する番号があれば重複していると判断し同じ番号を表示
3. まったく新しいメールアドレスであればA列でMAX+1した番号を割り振り
4. 0や空白のセルに対しては自動的に番号を付与しない
こうした仕組みを導入してから、顧客情報を元にした売上分析やDM送付リストなどの作業効率が飛躍的に向上しました。

私の知人の会社でも似たようなExcel管理をしていたのですが、メールアドレスが少し異なるだけで重複とみなされないなどのトラブルがあったようです。その点、今回の方法を使うと同じ値かどうかをきちんと検知できるため安心です。
在庫管理への応用
店舗を構えている方が在庫管理をExcelでやっているケースも多いですが、例えば同じ商品IDであれば同じ番号を再利用し、そうでなければ新しい番号を割り振る、といった使い方ができます。データが増えても最大番号+1をするだけなので、簡単に在庫リストを拡張できます。
在庫管理の注意点
データの精度を保つため、在庫管理表では空白や0を入れる場合は何かしらの意図があるケースが多いです。たとえば在庫ゼロを意味するのか、あるいは単純に未入力なのか、判断が難しいことがあります。こうした場合は先に空白と0の使い方をルール化しておくのがおすすめです。
エラーや混乱を防ぐための対策
数式の範囲設定をこまめにチェックする
MAX($A$4:$A4)やCOUNTIF($E$4:$E4,$E5)で指定する範囲がずれていると、正しく計算されない恐れがあります。特に挿入や削除を繰り返すと範囲がずれやすくなるため、運用途中で列を挿入したり別の場所へデータを移動したりするときには、必ず数式の参照範囲を確認しておきましょう。
シート保護や入力規則を活用する
A列のセルには利用者が直接入力する必要がありません。誤ってA列に手動で番号を入力されると数式が上書きされ、予期せぬ結果を生むことがあります。そこで、A列は保護しておき、列Eのみ編集を許可するようにしておくと安心です。
入力規則設定の活用
列Eに入力すべき内容が決まっているなら、入力規則で不正な値が入らないようにする方法もあります。たとえば数字以外受け付けない、必ず文字列にする、など事前の制限をかけるだけでも、後々のトラブルが防ぎやすくなります。
よくある質問と対処法
Q1. XLOOKUPが使えない場合はどうすればいいのか
XLOOKUPが使えないExcelのバージョンをお使いの場合は、INDEX/MATCH関数やVLOOKUP関数を活用して同等の処理を実装できます。例えばCOUNTIFで重複を確認した上で、VLOOKUP($E5, $E$4:$A$4, 2, FALSE)のように列の位置を調整して参照するとよいでしょう。INDEX/MATCHの場合は行列参照を分けて書く必要があります。
Q2. 列の途中に空白行がある場合はどうなるのか
空白行があると、そこに対しても数式が判定を行います。しかしIF($E5=””,””,…)としていれば、その行のE列が空白の場合はA列が空白のままになるため、大きな問題は生じません。ただし、途中で空白行を挟んだままデータの並び替えを行うときには注意が必要です。並び替えをするときはA列とE列を一緒に範囲選択して整列するようにしましょう。
Q3. 半角スペースなど見えない文字を空白と判定できるのか
Excelのセル内に半角スペースや制御文字が含まれている場合、単純にE5=””という条件だけでは判定できないことがあります。TRIM関数やCLEAN関数を組み合わせて、見た目上の空白を取り除いてからIF判定をする方法も検討するとさらに堅牢な運用が可能です。
拡張テクニック
番号のリセットをしたい場合
年ごとや月ごとに番号をリセットしたいときは、データの区切り条件を追加します。たとえば年が変わったら連番を初期化するなどの処理は、COUNTIFS関数で複数条件を設定し、既存の年とE列の値がセットになっているかどうかを判定すると実現できます。
集計と組み合わせる場合
ピボットテーブルやSUMIFで集計を行う場合も、自動連番列があると後々の分析で役立ちます。商品ごとに同じ番号が振られていれば、番号の小さい順に並べ替えて製品群をチェックしたり、特定の番号だけ抽出してまとめたりなどがやりやすくなるため、業務効率がさらに高まるはずです。
実践的な応用例
たとえば下記のようなリストがあるとします。
A列(自動番号) | E列(商品ID) | F列(商品名) | G列(在庫数) |
---|---|---|---|
1 | ABC001 | タブレット | 10 |
1 | ABC001 | タブレット | 5 |
2 | XYZ100 | ノートPC | 3 |
同じ商品IDには同じ番号が割り振られています。こうしておくと、ピボットテーブルでA列かE列を軸にした集計を行えば、商品ごとの在庫数合計などを簡単に出せるようになるわけです。
運用を円滑にするためのポイント
シートの保護と権限設定
A列に入力禁止ルールを設定し、保護をかけておくと、他の人が誤って数式を上書きするリスクが下がります。大人数で共有する場合ほどこうした設定が効果的です。
定期的なバックアップ
数式を間違えて削除してしまったり、別ファイルにコピペしている最中に不具合が起きたりすることもありえます。定期的にバックアップをとり、万が一に備えておくと安心です。
複数バージョンの保存
ファイル名に日付を入れてバージョン管理しておくか、クラウドで履歴管理を活用する方法もあります。自動連番の仕組み自体は堅牢ですが、人が意図せず操作ミスをすることは十分にありえます。トラブルを未然に防ぐうえでも、履歴をたどれる仕組みは非常に大切です。
まとめと今後の展望
Excelで別の列のデータをもとに自動的に番号を振るテクニックは、シンプルなIFやCOUNTIF、XLOOKUPの組み合わせで実現できます。同じ値には同じ番号を振り、空白や0、空文字列には番号を割り振らないという処理も、IFとORを使ってスマートに制御できます。一度セットアップすると後々の管理が格段にやりやすくなり、特に重複データが混在しやすいリスト管理で重宝されます。私も初めて使ったころはその手軽さに驚き、以来さまざまなシーンで応用し続けています。
今後さらにデータが増え、大規模な管理が必要になる場合は、Accessなどのデータベースやクラウドツールへの移行を視野に入れることもあるでしょう。しかし、Excelで手軽に試行錯誤できるメリットは大きく、社内の少人数だけで運用する場合や手軽な管理帳票を作りたい場合などはこの方法が最適です。運用する中で「もう少しこうしたい」というニーズが出てきたら、COUNTIFやXLOOKUPを別の関数に置き換えたり条件を工夫したりしながら、より使いやすいシステムに育てていけるはずです。



私も過去にこの方法を使ってから「Excelでここまでできるんだ」と驚きました。慣れると本当に便利なので、まずは小さなリストで試してみるといいかもしれません。
コメント