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

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

السلام عليكم

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

 

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

ارقام مفقودة.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

تم تعديل بالامس في 18:43 بواسطه محمد هشام.

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

منذ ساعه, محمد هشام. 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

[محمد هشام.]

 

 

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

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

10 ساعات مضت, محمد هشام. said:

 

 

ارقام مفقودة 2.xlsb 66.92 kB · 3 downloads

وين كانت المشكلة

[محمد هشام.]

منذ ساعه, زياد الحسناوي said:

وين كانت المشكلة

ببساطة أخي @زياد الحسناوي بعد إضافة الأرقام الجديدة لم تقم بسحب المعادلات للأسفل كما تمت الإشارة إليه في المشاركة السابقة

وذلك لأنني قمت بوضع المعادلة على الملف المرفق بقدر البيانات الموجودة سابقا فقط

 هناك كدالك نقطة مهمة يجب الإنتباه إليها في المعادلة المقترحة 

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

 وظيفتها إظهار الأرقام المفقودة من تسلسل يبدأ من 1 حتى أكبر رقم موجود في العمود A  وتعرض النتيجة في العمود D أو B حسب وضعها كما جاء في طلبك لكن هذه الصيغة تفترض أن الأرقام تبدأ من 1 وتتزايد بواحد  

 مثال 
عندما تكون الأرقام بهذا الشكل  مثلا 

فالصيغة أعلاه لن تعمل كما يجب لأنها تبدأ بالبحث من الرقم 1 بينما الأرقام الفعلية تبدأ من 15 لحل هذا الإشكال

نقترح استخدام الصيغة التالية التي تعتمد على أصغر وأكبر رقم موجودين فعليا في العمود A

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

المعادلة تبحث عن جميع الأرقام بين MIN و MAX وتستبعد الأرقام الموجودة فعليا في العمود A أي ترجع فقط الأرقام المفقودة في تسلسل منتظم وتعرض النتائج بشكل ديناميكي في العمود D بدءا من D2 

 

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

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

12 دقائق مضت, محمد هشام. said:

ببساطة أخي @زياد الحسناوي بعد إضافة الأرقام الجديدة لم تقم بسحب المعادلات للأسفل كما تمت الإشارة إليه في المشاركة السابقة

وذلك لأنني قمت بوضع المعادلة على الملف المرفق بقدر البيانات الموجودة سابقا فقط

 هناك كدالك نقطة مهمة يجب الإنتباه إليها في المعادلة المقترحة 

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

 وظيفتها إظهار الأرقام المفقودة من تسلسل يبدأ من 1 حتى أكبر رقم موجود في العمود A  وتعرض النتيجة في العمود D أو B حسب وضعها كما جاء في طلبك لكن هذه الصيغة تفترض أن الأرقام تبدأ من 1 وتتزايد بواحد  

 مثال 
عندما تكون الأرقام بهذا الشكل  مثلا 

فالصيغة أعلاه لن تعمل كما يجب لأنها تبدأ بالبحث من الرقم 1 بينما الأرقام الفعلية تبدأ من 15 لحل هذا الإشكال

نقترح استخدام الصيغة التالية التي تعتمد على أصغر وأكبر رقم موجودين فعليا في العمود A

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

المعادلة تبحث عن جميع الأرقام بين MIN و MAX وتستبعد الأرقام الموجودة فعليا في العمود A أي ترجع فقط الأرقام المفقودة في تسلسل منتظم وتعرض النتائج بشكل ديناميكي في العمود D بدءا من D2 

 

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

عاشت ايدك استاذ حاليا استطيع اضافة الى غاية فرضا ٨٠٠٠ في العمود A وبمجرد سحب المعادلات تظهر النتائج