Excel固有の番号（既存最大値＋１）を自動で

質問

Excel2007を使っています。
データベース的に使いたいのですが、
ある顧客データがあります。
作成する際に、連番の最大値で、番号を振りたいと思います。
（ただ削除する可能性もあります。つまり番号が抜けたりする）
自動でふる方法、何かありますでしょうか？
（別の表で、vlookupなどで、この番号から引っ張って、この行データを使うことになります）
(単にrow関数 だと、番号がずれて変わってしまうようで)

回答No.6

kkkkkm · ベストアンサー

> vlookupでの参照先も、データ不要なので、消しちゃいます。

参照先は一過性のものなのですね。
そのあたりは深く考えないとして、たとえば、すごく簡単な例です。
一番最初だけ現状の最大値をX1に入れておきます。
ボタンを押したら過去の最大値+1を選択されている行のA列に(未入力なら)書き込み、X1のデータを+1します。

ボタンを作成しそのクリックに(ボタンの名前がボタン1なら)
Sub ボタン1_Click()
With Sheets("Sheet1")'←Sheet1は実際のシート名に
If .Cells(ActiveCell.Row, "A").Value = "" Then
.Cells(ActiveCell.Row, "A").Value = .Range("X1").Value + 1
.Range("X1").Value = .Range("X1").Value + 1
End If
End With
End Sub

お礼

ありがとうございます！

回答No.8

kkkkkm

あと自動でという形をとるのでしたら
B列に入力するもしくは変更したときに同行のA列が未入力なら連番を振る
(現状の最大値+1の場合)
下記を対象シートのコードに記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = Range("B:B").Column And Cells(Target.Row, "A").Value = "" Then
Cells(Target.Row, "A").Value = WorksheetFunction.Max(Range("A:A")) + 1
End If
Application.EnableEvents = True
End Sub

(過去の最大値+1を振る場合X1に最終データを入れておいて)
下記を対象シートのコードに記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = Range("B:B").Column And Cells(Target.Row, "A").Value = "" Then
Cells(Target.Row, "A").Value = Range("X1").Value + 1
Range("X1").Value = Range("X1").Value + 1
End If
Application.EnableEvents = True
End Sub

回答No.7

kkkkkm

No6の追加です。
参照先も消すという事でしたら、過去の番号と重複してもいいわけですよね。
単純に現在の最大値+1にしてしまえば値を保存する必要もなくなるので

Sub ボタン1_Click()
With Sheets("Sheet1") '←Sheet1は実際のシート名に
If .Cells(ActiveCell.Row, "A").Value = "" Then
.Cells(ActiveCell.Row, "A").Value = WorksheetFunction.Max(.Range("A:A")) + 1
End If
End With
End Sub

回答No.5

kon555

>>途中の行で削除が発生する
>>行追加が、行途中で発生する
うーん、お望みの動作そのものはVBAで可能ですが、運用も見直された方がいいのではないかと思います。
行削除はともかく、行追加を途中に行うというのは管理上あまりいい事ではありません。
ともあれ作業者にはどうしようもないケースがあるでしょうし、お役に立ちそうな簡単なマクロを組みました。よければお使い下さい。

Sub 連番()
Dim i As Long
i = Range("A1").Value + 1
If i = 1 Then
MsgBox "記録セルが空白、または0です。適切な数値に修正して下さい。"
Exit Sub
End If
If TypeName(Selection) = "Range" And Selection.Value = "" Then
Selection.Value = i
Range("A1").Value = i
End If
End Sub

このマクロは、実行時に選択していたものが空白セルの時のみ、記録していたナンバーに1を足した数値を書き込み、その書き込んだ数値を記録します。
データを追加した後でナンバー用のセルを選択してマクロを実行する、という使い方を想定しました。
現状既に連番は振られているでしょうから、その最大値を記録セルに書き込んでおいて下さい。0や空白の場合はマクロは停止します。

分かりやすいようにA1セルを記録用にしましたが、必要に応じてコードのRange("A1")の"A1"を好きなセルに書き換えてください。そこが記録セルになります。

回答No.4

oboroxx

普通はExcelではデータの整合性が破たんするため、マスターデータからデータを削除しないと思います。（データベースソフトだと参照整合性のチェックが簡単にできるため必ずしもそうではないですが）
次の本を参考に勉強してみてください。

https://www.amazon.co.jp/%E3%82%A2%E3%83%97%E3%83%AA%E4%BD%9C%E6%88%90%E3%81%A7%E5%AD%A6%E3%81%B6ExcelVBA%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0-%E3%82%B3%E3%83%B3%E3%83%88%E3%83%AD%E3%83%BC%E3%83%AB-%E6%A8%AA%E5%B1%B1-%E9%81%94%E5%A4%A7/dp/4798032956/ref=sr_1_3?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&keywords=excel+vba+%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0&qid=1567333330&s=gateway&sr=8-3

お礼

ありがとうございます

回答No.3

kkkkkm

> そのとおりです。そこをどうするかがポイントかと。

関数ではデータを他のセルに入力する(参照はできますが)というのは無理ですのでマクロを使ってデータを保存していくしかないと思います。

ただ、データを削除した場合Vlookupで参照しているセルはエラーになると思いますが…。

また、新規データを途中に追加して連番を振っていたら番号順に並ばないので管理が面倒になると思いますよ。

利用方法を一度見直してみてはいかがですか。

お礼

ありがとうございます

順番は結構ならびかえたりする予定です
vlookupでの参照先も、データ不要なので、消しちゃいます。
もしくは、非表示にして、どちらものこしておくかです。

回答No.2

kkkkkm

> 連番の最大値で、番号を振りたい

データの削除があるのでしたら、連番の最大値のデータが削除された場合、顧客データが追加されたときに連番に現在の連番最大値+1を振ると駄目ですよね。

データ追加のたびに最大値をどこかに保存していかないと適切な連番が振れないと思います。

お礼

そのとおりです。そこをどうするかがポイントかと。

回答No.1

kon555

書かれている内容を考えると、シンプルにオートフィル機能でいい気がするのですがどうでしょうか。
https://www.sony.jp/support/vaio/guide/excel/002.html

また自動で振る場合であれば、例えばAセルがナンバーだとして「=A1 + 1」で足す方法もあります。
この場合データ削除時にナンバーまで削除すると既存番号の変化が起きてしまうので、若干の工夫が必要です。
(ナンバーは削除しないか、値貼り付け等で確定させるか）
またVBAでも極めて簡単にナンバーの自動割付は可能です。

ただし、やはり最初に挙げたオートフィルが一番簡単なように思えます。

お礼

ありがとうございます。オートフィルはわかりますが、
途中の行で削除が発生すること、がポイントです。

かりに削除しないという方針にしても、
どうしても行追加が、行途中で発生する（類似行をコピーしたいため）ので、途中行にありながら、最大値プラス１を表示させたいです。