ملف اكسل يحتوي على مجموعة من الارقام المتسلسلة

[زياد الحسناوي]

السلام عليكم

ملف اكسل يحتوي على مجموعة من الارقام المتسلسلة 

 

لكن توجد ارقام مفقودة هل يمكن التعرف على الارقام الغير موجودة ؟؟؟ 

ارقام مفقودة.xlsx

[محمد هشام.]

وعليكم السلام ورحمة الله تعالى وبركاته 

 

الطريقة 1 : ضع المعادلة مثلا  في B2 واسحب للأسفل:

=IF(COUNTIF(A:A, ROW())=0, ROW(), "")

الطريقة 2 : ضع المعادلة التالية  مع استبدال  الرقم 100 حسب الحد الأقصى في بياناتك ثم اسحب للأسفل:

=IFERROR(SMALL(IF(COUNTIF(A:A,ROW(INDIRECT("1:100")))=0,ROW(INDIRECT("1:100"))),ROWS(B$1:B1)),"")

أو بشكل ديناميكي 

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:" & MAX(A:A))), A:A, 0)), ROW(INDIRECT("1:" & MAX(A:A)))), ROWS(B$2:B2)), "")

ادا كنت تستخدم نسخة حديثة من الأوفيس 

=LET( maxVal, MAX(A:A),fullSet, SEQUENCE(maxVal),missing, FILTER(fullSet, ISNA(MATCH(fullSet, A:A, 0))),
  IF(ROWS(B$2:B2)<=ROWS(missing), INDEX(missing, ROWS(B$2:B2)), ""))

او بإستخدام الأكواد :   يمكنك تعديل Max لتحديد الحد الأقصى الذي تبحث فيه عن الأرقام 

Option Explicit

Sub RechercherNum()
    Dim lastRow As Long, i As Long, Max As Long
    Dim dict As Object, tmp As Long, col As String, a As Variant
    Dim WS As Worksheet: Set WS = Sheets("ورقة1")
    Set dict = CreateObject("Scripting.Dictionary")
    
    col = "G"       ' عمود وضع القيم المفقودة
    Max = 100       ' الحد الأقصى المتوقع
    
     With Application
     .ScreenUpdating = False: .Calculation = xlCalculationManual: .EnableEvents = False

    WS.Range(col & "2:" & col & WS.Rows.Count).ClearContents
    lastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        a = WS.Cells(i, 1).Value
        If IsNumeric(a) Then dict(CLng(a)) = True
    Next i
    tmp = 2
    For i = 1 To Max
        If Not dict.exists(i) Then
            WS.Cells(tmp, col).Value = i
            tmp = tmp + 1
        End If
    Next i
    .ScreenUpdating = True: .Calculation = xlCalculationAutomatic: .EnableEvents = True
 End With
End Sub

 

 

ارقام مفقودة.xlsb

تم تعديل منذ 5 ساعات بواسطه محمد هشام.

[زياد الحسناوي]

منذ ساعه, محمد هشام. said:

وعليكم السلام ورحمة الله تعالى وبركاته 

 

الطريقة 1 : ضع المعادلة مثلا  في B2 واسحب للأسفل:

=IF(COUNTIF(A:A, ROW())=0, ROW(), "")

الطريقة 2 : ضع المعادلة التالية  مع استبدال  الرقم 100 حسب الحد الأقصى في بياناتك ثم اسحب للأسفل:

=IFERROR(SMALL(IF(COUNTIF(A:A,ROW(INDIRECT("1:100")))=0,ROW(INDIRECT("1:100"))),ROWS(B$1:B1)),"")

أو بشكل ديناميكي 

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:" & MAX(A:A))), A:A, 0)), ROW(INDIRECT("1:" & MAX(A:A)))), ROWS(B$2:B2)), "")

ادا كنت تستخدم نسخة حديثة من الأوفيس 

=LET( maxVal, MAX(A:A),fullSet, SEQUENCE(maxVal),missing, FILTER(fullSet, ISNA(MATCH(fullSet, A:A, 0))),
  IF(ROWS(B$2:B2)<=ROWS(missing), INDEX(missing, ROWS(B$2:B2)), ""))

او بإستخدام الأكواد :   يمكنك تعديل Max لتحديد الحد الأقصى الذي تبحث فيه عن الأرقام 

Option Explicit

Sub RechercherNum()
    Dim lastRow As Long, i As Long, Max As Long
    Dim dict As Object, tmp As Long, col As String, a As Variant
    Dim WS As Worksheet: Set WS = Sheets("ورقة1")
    Set dict = CreateObject("Scripting.Dictionary")
    
    col = "G"       ' عمود وضع القيم المفقودة
    Max = 100       ' الحد الأقصى المتوقع
    
     With Application
     .ScreenUpdating = False: .Calculation = xlCalculationManual: .EnableEvents = False

    WS.Range(col & "2:" & col & WS.Rows.Count).ClearContents
    lastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow
        a = WS.Cells(i, 1).Value
        If IsNumeric(a) Then dict(CLng(a)) = True
    Next i
    tmp = 2
    For i = 1 To Max
        If Not dict.exists(i) Then
            WS.Cells(tmp, col).Value = i
            tmp = tmp + 1
        End If
    Next i
    .ScreenUpdating = True: .Calculation = xlCalculationAutomatic: .EnableEvents = True
 End With
End Sub

 

 

ارقام مفقودة.xlsb 24.11 kB · 0 downloads

جربت ارقام جديدة ولكن توجد مشكلة 

ارقام مفقودة.xlsb