كيف تُرتِّب الأرقام بسهولة في Excel مع تخطي الخلايا الفارغة؟

عند العمل مع البيانات في Excel، من الشائع مواجهة قوائم تحتوي على خلايا فارغة. إذا استخدمت دوال الترتيب القياسية في Excel مثلRANK أوRANK.EQ على مثل هذه القوائم، فإن الخلايا الفارغة عادةً ما تُظهر أخطاءً أو نتائج غير مرغوبة في مخرجات الترتيب. وهذا يجعل تفسير بياناتك أكثر صعوبة، خاصةً إذا كنت تفضل ترك الخلايا الفارغة فارغة بدلاً من عرض قيم خطأ أو رتب عشوائية. إن ترتيب الأرقام بكفاءة مع الحفاظ على الخلايا الفارغة فارغة تلقائيًا يعزز وضوح نتائجك وسهولة استخدامها، مما يجعل جدولك أكثر احترافية وسلاسة في القراءة.

في هذه المقالة، ستجد تعليماتٍ خطوة بخطوة لاستخدام الصيغ وماكروهات VBA لإتمام هذه المهمة بسلاسة. وتشمل الحلول الموسَّعة أيضًا شروحاتٍ واضحة للمعاملات، إلى جانب نصائح عملية واقتراحات فعّالة لاستكشاف الأخطاء وإصلاحها، مما يساعدك على تجنُّب المشكلات الشائعة بكل يسر.

• رتب القيم مع تخطي الخلايا الفارغة بترتيب تصاعدي باستخدام الصيغ
• رتب القيم مع تخطي الخلايا الفارغة بترتيب تنازلي باستخدام صيغة
• رتب القيم مع تخطي الخلايا الفارغة باستخدام VBA

رتب القيم مع تخطي الخلايا الفارغة بترتيب تصاعدي باستخدام الصيغ

عندما تحتاج إلى ترتيب الرتب تصاعديًّا مع تجاهل الخلايا الفارغة، يُعد استخدام أعمدة مساعدة متعددة ومنطق الصيغ نهجًا شائعًا لضمان استبعاد القيم الفارغة أثناء الترتيب.

السيناريو المناسب:استخدم هذه الطريقة عندما تريد إنشاء رتب تصاعدية (من الأدنى إلى الأعلى) مع الحفاظ على موضع الخلايا الفارغة، خاصةً مع البيانات ذات النطاق المستمر التي لا ينبغي أن تؤثر فيها القيم المفقودة على ترقيم الرتب.

لتنفيذ ترتيب تصاعدي مع تخطي الخلايا الفارغة، اتبع الخطوات التالية، والتي تتضمن عمودَي مساعدة لبناء النتيجة:

1. حدد خلية فارغة مجاورة لقيمك — على سبيل المثال، الخليةB2 إذا كانت قائمتك تبدأ منA2 — وأدخل الصيغة التالية:

=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))

تُعيد الصيغة خلية فارغة إذا كانت A2 فارغة؛ وإلا فإنها تُولِّد أرقامًا عشرية بناءً على قيمة A2، بإضافة ‎.0، .1، .2، .3، وهكذا دواليك، كلما سحبت مقبض التعبئة لأسفل لنسخ الصيغة إلى جميع الصفوف التي تحتوي على بيانات.

شروحات المعاملات والنصائح:

• $A2: الخلية الأولى التي سيتم ترتيبها. غيّرها إذا كانت قائمتك تبدأ من صف مختلف.
• $B$2: الخلية التي تُدخل فيها هذه الصيغة. انتبه إلى المراجع المطلقة (مثل $A2 و$B$2) لضمان عمل الصيغة بشكل صحيح عند سحبها لأسفل.

2. في العمود التالي، على سبيل المثالC2، أدخل هذه الصيغة لإنشاء قائمة مرتبة لقيم المساعد:

=SMALL($B$2:$B$8,ROW()-ROW($C$1))

تستخرج هذه الصيغة، كلما نسختها لأسفل، أصغر القيم منB2:B8 بالتتابع، ثم القيمة الأصغر التالية، فالأصغر بعد ذلك (ملاحظة: غيّر النطاق إذا كانت بياناتك تمتد لأبعد من ذلك).

شروحات المعاملات:

• $B$2:$B$8: النطاق الذي تُطبَّق عليه الصيغة السابقة (الأولى).
• $C$1: الخلية الموجودة مباشرةً فوق المكان الذي تُدخل فيه الصيغة؛ ويتحكم هذا الإزاحة في ترتيب الفرز.

3. في الخليةD2، أدخل الصيغة التالية لتعيين الرتب مع ترك الخلايا الفارغة دون تغيير:

=IFERROR(MATCH($B2,$C$2:$C$8,0),"")

تقارن هذه الصيغة القيمة الموجودة فيB2 مع النتائج المرتبة فيC2:C8. إذا كانت متطابقة، تُخرِج الرتبة؛ وإذا لم تكن كذلك (مثلًا في حالة الخلايا الفارغة)، فلا تُخرِج شيئًا، مما يحافظ على مظهر الخلية فارغة. اسحب مقبض التعبئة لأسفل لتطبيقها على جميع الصفوف ذات الصلة.

المعاملات:

• $B2: الخلية التي تحتوي على قيمة المساعد المستخدمة في الترتيب.
• $C$2:$C$8: نطاق قيم المساعد المرتبة.

احتياطات:إذا قمت بإضافة بيانات أو حذفها، تذكّر تحديث جميع النطاقات في كل صيغة لتتناسب مع حجم بياناتك الجديد. وللقوائم الكبيرة جدًّا، فكّر في استخدام نطاقات ديناميكية أو جداول Excel لتقليل الحاجة إلى التعديلات اليدوية للنطاقات.

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

رتب القيم مع تخطي الخلايا الفارغة بترتيب تنازلي باستخدام صيغة

عندما تريد تعيين الرتب بترتيب تنازلي — بحيث تحصل القيمة الأعلى على الرتبة 1 — هناك نهج أسرع يعتمد على صيغة واحدة فقط. وهو مثالي خصوصًا لدرجات الاختبارات وأهداف المبيعات ومجموعات البيانات المشابهة، حيث تمثّل الخلايا الفارغة بيانات مفقودة أو غير متاحة، ولا ترغب في أن تشغّل مواقع رتب أو تُظهِر أخطاءً.

حدد خلية في نفس صف أول إدخال بيانات، حيث تريد أن تظهر النتيجة المستخرجة، وأدخل:

=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))

بعد إدخال الصيغة، استخدم مقبض التعبئة لنسخها لأسفل بجانب بياناتك. تتحقّق هذه الصيغة مما إذا كانت دالةRANKتُرجع خطأً (مثلًا عندما تكون الخلية)A2فارغة)؛ وفي حال حدوث ذلك، تترك النتيجة فارغة بدلًا من عرض «#N/A». أما إذا احتوت الخلية على قيمة صالحة، فتعرض الرتبة المناسبة.

المعاملات:

• A2: الخلية التي سيتم ترتيبها (عدّلها وفقًا لنطاق البيانات الخاص بك).
• A$2:A$8: النطاق الكامل لبياناتك (استخدم مرجعًا مطلقًا عند النسخ).

تذكيرات بالأخطاء:إذا كنت لا تزال ترى أخطاءً من نوع «#N/A»، فتأكد من أن مراجع الصيغة تتطابق مع نطاق البيانات المطلوب، وأن الخلايا التي يتم ترتيبها لا تحتوي على قيم غير رقمية.

رتب القيم مع تخطي الخلايا الفارغة باستخدام VBA

للمستخدمين المتمرسين في استخدام الماكروهات والراغبين في أتمتة الترتيب عبر نطاق يحتوي خلايا فارغة—سواءً كان الترتيب تصاعديًا أو تنازليًا—يمكن لماكرو VBA مخصص أن يبسّط العملية بشكل كبير، إذ يلغي الحاجة إلى أعمدة مساعدة متعددة وصيانة الصيغ المستمرة.

كيفية الاستخدام:

1. انتقل إلى تبويبالمطور، ثم انقر علىVisual Basic لفتح محررMicrosoft Visual Basic for Applications. إذا لم يكن تبويب المطور ظاهرًا، راجع هذا الدليل: إظهار تبويب المطور في Excel.

2. في نافذةMicrosoft Visual Basic for Applications الجديدة، انقر علىإدراج > وحدة نمطية، ثم الصق إحدى الشيفرتين التاليتين في نافذة الوحدة النمطية:

• لتنفيذ ترتيب تصاعدي مع تخطي الخلايا الفارغة:

  Sub RankSkipBlank_Ascending()
      Dim WorkRng As Range
      Dim Cell As Range
      Dim NumArr() As Double
      Dim Ws As Worksheet
      Dim OutputCell As Range
      Dim i As Long, j As Long
      
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
      Set Ws = WorkRng.Worksheet
      
      Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8)
      If OutputCell Is Nothing Then Exit Sub
      
      j = 0
      ReDim NumArr(1 To WorkRng.Rows.Count)
      
      For Each Cell In WorkRng
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              j = j + 1
              NumArr(j) = Cell.Value
          End If
      Next Cell
      
      Dim temp As Double
      Dim k As Long
      
      For i = 1 To j - 1
          For k = i + 1 To j
              If NumArr(i) > NumArr(k) Then    ' ← CHANGE HERE
                  temp = NumArr(i)
                  NumArr(i) = NumArr(k)
                  NumArr(k) = temp
              End If
          Next k
      Next i
      
      Dim RankArr() As Double
      ReDim RankArr(1 To j)
      For i = 1 To j
          RankArr(i) = NumArr(i)
      Next i
      
      Dim RankValue As Long
      Dim r As Long: r = 0
      
      For Each Cell In WorkRng
          r = r + 1
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              RankValue = 0
              For k = 1 To j
                  If Cell.Value = RankArr(k) Then
                      RankValue = k        ' 1 = smallest
                      Exit For
                  End If
              Next k
              OutputCell.Offset(r - 1, 0).Value = RankValue
          Else
              OutputCell.Offset(r - 1, 0).Value = ""
          End If
      Next Cell
  
  End Sub

• لتنفيذ ترتيب تنازلي مع تخطي الخلايا الفارغة:

  Sub RankSkipBlank_Descending()
      Dim WorkRng As Range
      Dim Cell As Range
      Dim NumArr() As Double
      Dim Ws As Worksheet
      Dim OutputCell As Range
      Dim i As Long, j As Long
      
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8)
      Set Ws = WorkRng.Worksheet
      
      Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8)
      If OutputCell Is Nothing Then Exit Sub
      
      j = 0
      ReDim NumArr(1 To WorkRng.Rows.Count)
      
      For Each Cell In WorkRng
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              j = j + 1
              NumArr(j) = Cell.Value
          End If
      Next Cell
      
      Dim temp As Double
      Dim k As Long
      
      For i = 1 To j - 1
          For k = i + 1 To j
              If NumArr(i) < NumArr(k) Then
                  temp = NumArr(i)
                  NumArr(i) = NumArr(k)
                  NumArr(k) = temp
              End If
          Next k
      Next i
      
      Dim RankArr() As Double
      ReDim RankArr(1 To j)
      For i = 1 To j
          RankArr(i) = NumArr(i)
      Next i
      
      Dim RankValue As Long
      Dim r As Long: r = 0
  
      For Each Cell In WorkRng
          r = r + 1
          If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then
              RankValue = 0
              For k = 1 To j
                  If Cell.Value = RankArr(k) Then
                      RankValue = k
                      Exit For
                  End If
              Next k
              OutputCell.Offset(r - 1, 0).Value = RankValue
          Else
              OutputCell.Offset(r - 1, 0).Value = ""
          End If
      Next Cell
  
  End Sub

3. اضغط علىF5 لتشغيل الماكرو. سيطلب منك مربع حوار تحديد النطاق الذي تريد ترتيبه، ثم يظهر مربع حوار آخر لاختيار الخلية الأولى التي ستُوضع فيها نتائج الترتيب. بعد ذلك، سيُخرِج الماكرو الرتب بدءًا من الخليّة التي حددتها، مع ترك أي خلايا فارغة في نطاق المصدر كما هي.

نصائح:

• إذا لم يحدث شيء، فتأكد من أن الماكروهات مفعّلة ولديك الصلاحيات اللازمة لتشغيل الكود في مصنفك.
• لا يمكن التراجع عن تشغيل ماكرو VBA، لذا تأكد من نسخ بياناتك أو إنشاء نسخة احتياطية منها قبل تنفيذه.

أفضل أدوات الإنتاجية لمكتبتك

🤖	KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
	الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
	بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
	قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
	مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
	ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
	أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   12 أدواتالتحويل(تحويل إلى كلمات،تحويل العملة، ...)|   7 أدواتدمج وتقسيم(دمج متقدم للصفوف،تقسيم الخلايا، ...)|... وأكثر من ذلك

استخدم Kutools باللغة التي تفضلها – يدعم الإنجليزية والإسبانية والألمانية والفرنسية والصينية و40+ لغات أخرى!

عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...

مزيد من التفاصيل حول Kutools لـ Excel...تنزيل مجاني...

---

يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير

• تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
• افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
• يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!

مزيد من التفاصيل حول Office Tab...تنزيل مجاني...

---

جميع الإضافات من Kutools في برنامج تثبيت واحد!

Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.

• حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
• برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
• يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
• تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
• أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل

مزيد من التفاصيل حول Kutools for Office...تنزيل مجاني...