エクセルで欠番を見付けるには

質問

エクセルで、連番の付いたデータを処理するとき、何番が欠番かを発見して、その番号をピックアップする方法を教えていただけますか。

回答No.3

dejiji- · ベストアンサー

A列に番号があるとして、Ｃ列に欠番の無い列を作ります。（フィルで連番を作る。）
Ｄ列に=vlookup(C1,A:A,1,FALSE)とし、下へコピー
欠番は#N/Aが表示されます。

お礼

ありがとうございました。よくわかりました。D列の下への複写はＣ列と同じ長さだけすればいいのですね。

回答No.8

beer55

お晩です。

A列に連番が入力されていることして
空き列に=IF(ISERROR(ADDRESS(A1,1,1,TRUE)),"A"&ROW(),"")
ではどうですか

お礼

ありがとうございました。うまくいかなかったのですが・・・。

回答No.7

imogasi

空き列をD列とします。
番号がA列A1:A100に入っているとします。
D1に=IF(ISERROR(MATCH(ROW(D1),$A$1:$A$100,0)),ROW(D1),"")といれ、下へ複写します。
欠番は数字が現れます。
１２００－１３００までの欠番の場合はROW(D1)+NのNを調整してください。この場合はROW(D1)+1199に修正する。
D1に=IF(ISERROR(MATCH(ROW(D1)+1199,$A$1:$A$100,0)),ROW(D1)+1199,"")
その後値をD列自身に複写し、D列をソートすれば飛び飛びがまとめられる。

お礼

ありがとうございました。D列に複写するときA列よりも長く下へ複写しなければなりませんね。Ａ列が１０００くらいまでは最初の式でいいのでしょうか。

回答No.6

imogasi

＃４です。少し単純な別解。
空き列（A列とする）に問題の番号列をソートするとする。
その後
Sub test03()
i = 0
j = 0
p01:
i = i + 1
ak = Cells(i, "A")
p02:
j = j + 1
bk = j
If ak = bk Then
GoTo p01
End If
If ak > bk Then
MsgBox j & "番なし"
GoTo p02
End If
If ak < bk Then End
End Sub
ｊはスタート番号の－１の数字をセットする。
文字列の場合は、BKの文字列化のため修正が必要。

お礼

ありがとうございました。ちょっと私には複雑すぎるようです。

回答No.5

BLUEPIXY

ＶＢＡでやってみました
Public Sub missingNo(r as range)
Dim b As Range
Dim i As Integer

Set b = r.Item(1)
For i = 1 To r.Rows.Count - 1
x = ripper(b.Offset(i - 1, 0).Value)
y = ripper(b.Offset(i, 0).Value)
If Abs(x - y) > 1 Then
MsgBox b.Offset(i - 1, 0).Value & "と" & b.Offset(i, 0).Value & "の間が欠番です", vbOKOnly, "欠番"
End If
Next
End Sub
Public Function ripper(s As String) As String '数字以外を取り除く
ripper = ""
s = StrConv(s, vbNarrow)

For i = 1 To Len(s)
c = Mid(s, i, 1)
If ("0" <= c And c <= "9") Then
ripper = ripper + c
End If
Next
End Function
'
call missingNo(range("A1:A17"))
のように使います

お礼

ありがとうございました。VBAは、私にはまだ無理のようです。

回答No.4

imogasi

VBAでやって見ました。
Sub test01()
s = InputBox("最初、最後") 'カンマで区切って
t = Split(s, ",")
For i = t(0) To t(1)
Set f = Range("A1:A100").Find(What:=i, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If f Is Nothing Then
MsgBox i
End If
Next i
End Sub
（データ）
1
2
3
5
6
7
8
9
11
12
15
で４、１０，１３，１４が指摘できます。
番号が文字列である場合は
Sub test01()
s = InputBox("最初、最後") '数字だと思って指定のこと
t = Split(s, ",")
For i = t(0) To t(1)
Set f = Range("A1:A100").Find(What:=Format(i, "0000"), After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If f Is Nothing Then
MsgBox Format(i, "0000")
End If
Next i
End Sub
などのように修正が必要で、文字を含んでいるA1234などの
場合はVBAのことが判らないと修正が難しいかと。

お礼

ありがとうございました。VBAは、私にはまだ無理のようです。

回答No.2

papayuka

A列に連番が入っているとして、

空いている列の1行目（例えば セル E1）に

=MATCH(ROW(),A:A,0)

と入れるとA列で「1」がある行が表示されます。
つまり「行番号」を検索値としていますので、「#N/A」が出る行番号が欠番という事です。

65536までしか使えないけど、、、

お礼

ありがとうございました。うまくいかなかったのですが・・・。

回答No.1

marbin

どういう番号でしょう？

1
2
4
5

という感じで数字のみでしたら、
番号をソートし、右列にでも

=a2-a1
と入力し、データ分だけ貼り付けし、（オートフィル？
でもＯＫ）

更に条件付き書式で１以外は赤く表示する、
とすればいいかと思います。

マクロで検索する（Findメソッド）、という手もあり
ますが・・・。

お礼

ありがとうございました。。データ分だけ貼り付けして、Ａ列と見比べながらやるといいかもしれません。