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

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

السلام عليكم

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

 

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

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

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

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

منذ ساعه, محمد هشام. 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 وبمجرد سحب المعادلات تظهر النتائج

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

قمت باضافة ارقام جديدة ولكن ظهرت مشكلة في الاعمدة
ارقام ليست متسلسلة وتوجد خلايا فارغة 

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

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

وجربت ايضا الملف هذا 

فقط هذه الدالة 

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

ولكن توجد فارغات بين رقم و رقم اخر 

 

اما باقي الدوال لم تعمل 

المصنف1.xlsx

[محمد هشام.]

أخي الكريم  @زياد الحسناوي  تحية طيبة

 هناك إستفسار بسيط فقط للتوضيح:
هل المشكلة التي واجهتها تظهر فقط عند نقل المعادلة إلى ملفك الأصلي؟
أم أن الخطأ موجود أيضا في الملفات التي تم تحميلها من المنتدى؟

الهدف من هذا السؤال هو التحقق ما إذا كانت المشكلة ناتجة عن طريقة النقل أو التعديل على الملف الأصلي

قبل الخوض في آخر توضيح لي ضمن هذا الموضوع  أود أن أعتذر عن الخطأ غير المقصود في نسخ المعادلة الأخيرة داخل الرد في المداخلة السابقة رغم أنها كانت مكتوبة بشكل صحيح تماما داخل الملف المرفق

في 20‏/6‏/2025 at 18:05, محمد هشام. 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)), "")

المعادلة تعمل على البحث عن الأرقام المفقودة ضمن تسلسل يبدأ من أصغر رقم MIN(A:A) إلى أكبر رقم MAX(A:A) وترجع فقط الأرقام غير الموجودة فعليا في العمود A

المعادلة الصحيحة

=IFERROR(SMALL(
    IF(ISNA(MATCH(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))), A:A, 0)),
    ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))
), ROWS(C$1:C1)), "")

أو 

=IFERROR(SMALL(IF(COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))))
=0,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))),ROWS(D$1:D1)),"")

أو بطريقة متقدمة نوعا ما 

=FILTER(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A)), 
ISNA(MATCH(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A)), A:A, 0)))

 

أما بخصوص الحلول المقترحة تم تزويدك بعدة حلول مختلفة لتختار ما يناسب أسلوب عملك في الملف لأنك لم تحدد في سؤالك

هل ترغب في عرض القيم المفقودة بشكل متسلسل دون فراغات؟

أم ترغب في عرضها بنفس مواقع الصفوف الأصلية (مع فراغات)؟

لذلك تم عرض كلا الإحتمالين ومن بينها المعادلة التي أشرت إليها تعرض القيم المفقودة مع وجود فراغات

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

مع تزويدك أيضا بمعادلة تعرض القيم المفقودة بشكل متسلسل دون فراغات 

=IFERROR(
  SMALL(
    IF(COUNTIF(A:A, ROW(INDIRECT("1:1900"))) = 0,
      ROW(INDIRECT("1:1900"))
    ),
    ROWS(J$1:J1)
  ),
"")

إذا كنت تريد إستخراج القيم المفقودة بناء على الحد الأدنى والحد الأعلى في العمود A بدون تحديد 1900 يدويا فقد تم التنبيه لدالك سابقا يمكنك تعديل

( 1:1900 )  حسب النطاق الذي تعمل عليه  مثلا  "1:"&MAX(A:A)  ليتغير تلقائيا حسب البيانات

=IFERROR(
  SMALL(
    IF(COUNTIF(A:A, ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))) = 0,
      ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))
    ),
    ROWS(J$1:J1)
  ),
"")

وبهذا ستكون بين يديك عدة اختيارات يمكنك تحديد ما يناسبك منها وفق الإصدار المستخدم لديك وشكل النتائج المتوقعة

وهذه صورة توضح بياناتك بعد تصحيح المعادلات في الملف وتؤكد أنها تعمل كما هو متوقع

 

ملاحظة أخيرة:
يرجى التأكد من نسخ المعادلات كما هي دون تعديل أو حذف جزء منها مع مراعات تعديل عناوين الخلايا في حالة نسخها إلا عمود مختلف لأن أي خطأ بسيط في الصيغة قد يؤدي إلى عدم عملها بالشكل الصحيح وهذا ما تم ملاحظته في الملف الأخير المرفق من طرفك 

وأخيرا إدا كنت تستخدم إصدار حديث من الأوفيس فأفضل حل بالنسبة لك هو 

=FILTER(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A)), 
ISNA(MATCH(SEQUENCE(MAX(A:A)-MIN(A:A)+1,1,MIN(A:A)), A:A, 0)))

بالتوفيق..........

 

المصنف V2.xlsx

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