欠番の抽出について

エクセル等のオフィスで欠番の抽出を行いたいのですが、数が多すぎて処理がおいついておりません。

1-999999までの数字で商品コードを登録しております。
その中で約58,000件は使用済みです。
未使用分のコードを一括抽出できるように関数を用いて処理しようと実行しましたが、件数が多すぎて
再計算処理にかなりの時間がとられてしまいます。

件数が多い場合の欠番の検索、抽出はどのようにされていますか？

御回答よろしくお願い致します。

No.5 ベストアンサー 回答者： d-q-t-p 回答日時： 2018/01/22 16:15

データベースクエリで差分クエリを作ってもいいですけど PowerBIを導入する

のはダメなんでしょうか。

Googleスプレッドシートなら
A列商品コード
B列使用済みコード
として

=QUERY(QUERY({UNIQUE(A1:A);UNIQUE(B1:B)},"select Col1, Count(Col1) group by Col1"),"select Col1 where Col2=1 label Col1 '差分'")

で Excelで数式使ってやるよりは速くなるかと思います。

この回答へのお礼

ありがとうございました。参考にさせて頂きます。

お礼日時：2018/01/22 16:30

No.4 回答者： LHS07 回答日時： 2018/01/22 15:48

LHS10続きです。

登録されている商品コード58000からする無視すればもっと早く実行できます。

No.3 回答者： tom04 回答日時： 2018/01/22 15:29

こんにちは！

No.1さんの
＞最大値を求めておけば999.999までではなく最大値までのループで済みます。
に大賛成です。

VBAでの一例です。
元データはSheet1のA列2行目以降にあるとし、Sheet2に欠番を表示させる方法にしてみました。
標準モジュールにしてください。

Sub Sample1()
Dim myDic As Object, myDic2 As Object
Dim i As Long, lastRow As Long, myMax As Long
Dim myKey, myItem, myR
Dim wS As Worksheet

Set myDic = CreateObject("Scripting.Dictionary")
Set myDic2 = CreateObject("Scripting.Dictionary")
Set wS = Worksheets("Sheet2")
wS.Range("A:A").ClearContents
wS.Range("A1") = "欠番"
With Worksheets("Sheet1")
myMax = WorksheetFunction.Max(.Range("A:A"))
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
myR = Range(.Cells(2, "A"), .Cells(lastRow, "A"))
For i = 1 To UBound(myR, 1)
If myR(i, 1) <> "" Then
myDic.Add myR(i, 1), ""
End If
Next i
For i = 1 To myMax
If Not myDic.exists(i) Then
myDic2.Add i, ""
End If
Next i
End With
myKey = myDic2.keys
myR = Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, "A"))
For i = 0 To UBound(myKey)
myR(i + 1, 1) = myKey(i)
Next i
Range(wS.Cells(2, "A"), wS.Cells(UBound(myKey) + 2, "A")) = myR
Set myDic = Nothing
Set myDic2 = Nothing
wS.Range("B1") = "登録最大ナンバー"
wS.Range("B2") = myMax
wS.Activate
MsgBox "完了"
End Sub

※　コードは長いですが、最大登録番号が極端に飛んでいなければすぐに表示されると思います。

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。m(_ _)m

この回答へのお礼

御回答ありがとうございました。参考にさせていただきます。

お礼日時：2018/01/22 16:28

No.2 回答者： LHS07 回答日時： 2018/01/22 15:25

商品コードがB2に書いてあって

この下に商品コードが羅列してある場合
K3から羅列されます。

Sub Macro1()
'
' Macro1 Macro
'

'
Rem 変数定義
Dim n, i, e, y, m(1000000) As Integer



Rem 変数初期化
e = 20
For i = 1 To e
m(i) = 0
Next i

For i = 3 To e + 3
n = Cells(i, 2).Value
m(n) = 1
Next i

y = 3
For i = 1 To e
If m(i) = 0 Then Cells(y, 11) = i: Debug.Print m(i); " ";: y = y + 1
Next i

End Sub

表示　
　マクロ
　　マクロの表示
　　　Macro1
Microsoft Visual Basic for Applicationsが立ち上がる

中にコピペ

右三角で実行

この回答へのお礼

御回答ありがとうございました。

お礼日時：2018/01/22 16:28

No.1 回答者： zircon3 回答日時： 2018/01/22 14:25

1から999,999まで1ずつカウントアップしながらfindで対象範囲を検索するループを作ればよいように思います。

処理を恥じる前に検索対象を昇順ソートし、最大値を求めておけば999.999までではなく最大値までのループで済みます。

参考まで。

この回答へのお礼

ありがとうございました。皆さんの意見を参考にさせて頂きます。

お礼日時：2018/01/22 16:27