كيف يمكن العثور على القيمة الثانية من النهاية وإرجاعها في صف أو عمود معيّن في Excel؟
عند التعامل مع أوراق عمل Excel الكبيرة، غالبًا ما تحتاج إلى تحديد ليس فقط القيمة الأخيرة في صف أو عمود، بل والقيمة الثانية من النهاية أيضًا. فعلى سبيل المثال، كما يظهر في لقطة الشاشة أدناه، وافتراض أن نطاق الجدول هو A1:E16، قد ترغب في استرجاع القيمة الثانية من النهاية في الصف السادس أو في العمود B. وتظهر هذه الحاجة عادةً عند تتبع التغييرات الحديثة، أو مراقبة بيانات السلاسل الزمنية، أو تحليل الإدخالات الأحدث التي لم تعد حالية ضمن مجموعة البيانات. مقارنةً بالعثور على القيمة الأخيرة فقط، قد يكون تحديد القيمة الثانية من النهاية أقل وضوحًا—خاصةً إذا احتوت البيانات على خلايا فارغة أو كانت تخضع لتحديثات منتظمة. تقدم هذه المقالة حلولًا واضحة ومُفصَّلة خطوة بخطوة لهذه المهمة، لتسهيل سير عملك وزيادة كفاءته، ولتجنب الأخطاء الشائعة.
- العثور على القيمة الثانية من النهاية وإرجاعها في صف أو عمود معين باستخدام الصيغ
- رمز VBA – استخدام ماكرو للعثور على القيمة الثانية من النهاية في صف أو عمود محدد
- طرق Excel المدمجة الأخرى – تصفية جميع الخلايا الفارغة وتحديد القيمة الثانية من النهاية يدويًا
العثور على القيمة الثانية من النهاية وإرجاعها في صف أو عمود معين باستخدام الصيغ
كما يظهر في لقطة الشاشة أعلاه، عندما تحتاج إلى العثور على القيمة الثانية من النهاية وإرجاعها إما في الصف السادس أو العمود B ضمن النطاق A1:E16، توفّر صيغ Excel حلاً ديناميكيًا وفعالًا. ويُعد استخدام الصيغ مثاليًا في السيناريوهات التي تُحدَّث فيها بياناتك باستمرار أو قد يتغيّر موضع القيمة الثانية من النهاية نتيجة الإضافات أو الحذف. وعلى عكس الطرق اليدوية، تضمن الصيغ تحديث النتيجة تلقائيًا لتتماشى مع أحدث إدخالات البيانات.
العثور على القيمة الثانية من النهاية في العمود B وإرجاعها
1. حدد الخلية الفارغة التي تريد عرض القيمة الثانية من النهاية فيها. أدخل صيغة المصفوفة التالية في شريط الصيغة، ثم اضغط علىCtrl + Shift + Enter (لإصدارات Excel القديمة) لتأكيدها كصيغة مصفوفة. في Excel 365 أو Excel 2021، يكفي الضغط على Enter وحده، لأن صيغ المصفوفة تُعالج تلقائيًا.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
ملاحظة: في هذه الصيغة، يشيرB:B إلى العمود B. إذا كنت بحاجة إلى القيمة الثانية من النهاية في عمود مختلف، فما عليك سوى تعديلB:Bإلى مرجع العمود المطلوب (على سبيل المثال، غيّره إلى)C:C للعمود C). تعمل هذه الطريقة مع النطاقات التي تحتوي أو لا تحتوي على خلايا فارغة، ولكن إذا كانت هناك قيم مخفية أو بيانات مستبعدة بواسطة التصفية، فتأكد من أخذ الاختلافات المحتملة بعين الاعتبار. تحقق دائمًا من نطاق البيانات الخاص بك إذا لم تظهر القيمة المتوقعة.
العثور على القيمة الثانية من النهاية في الصف 6 وإرجاعها
حدد خلية فارغة لعرض نتيجة الصف السادس، ثم أدخل الصيغة التالية في شريط الصيغة واضغط علىEnter للتأكيد.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
ملاحظة: في الصيغة أعلاه،$A$6 هي الخلية الأولى في الصف 6، ويشير6:6إلى الصف بأكمله 6. قم بتعديل هذه المراجع لاستهداف صفوف مختلفة حسب الحاجة. تقوم الصيغة باحتساب عدد الخلايا غير الفارغة في الصف 6 ديناميكيًا، ثم تعوّض الخلية الابتدائية لتحديد الخلية المملوءة الثانية من النهاية. إذا كان صفّك يحتوي على صيغة تُرجع سلاسل فارغة ()"")، فقد لا يزال COUNTA يحتسبها كخلايا غير فارغة، مما قد يؤثر على النتيجة. وللتأكد من الدقة مع النطاقات التي تحتوي على مزيج من القيم الثابتة والصيغ، راجع النتائج بعناية لتجنب الأخطاء.
نصائح لاستخدام الصيغ:
- عند التعامل مع مجموعات البيانات الكبيرة، قد يؤثر استخدام مراجع الأعمدة الكاملة (مثل)
B:B) سلبًا على الأداء. لذا، إن أمكن، قم بتقليص النطاق ليشمل فقط المنطقة التي تحتاجها (مثلB1:B100). - إذا كانت بياناتك تحتوي على صفوف مخفية أو كانت مُرشَّحة، فستستمر الصيغ في احتساب الخلايا المخفية أو المُستبعدة بواسطة التصفية. بالنسبة إلى البيانات المُرشَّحة، يُفضَّل استخدام دوال المجموع الفرعي الخاصة أو أعمدة مساعدة.
- إذا كانت جميع القيم في الصف أو العمود المستهدف فارغة، فقد تُرجع هذه الصيغ خطأً أو نتيجة غير متوقعة؛ لذا تأكد من أن بياناتك تتضمّن قيمتين غير فارغتين على الأقل.
رمز VBA – استخدام ماكرو للعثور على القيمة الثانية من النهاية في صف أو عمود محدد
للمستخدمين الذين يضطرون غالبًا إلى العثور على القيمة الثانية من النهاية في نطاقات متغيرة أو كبيرة، يُعد أتمتة هذه العملية عبر ماكرو VBA وسيلة فعّالة لتوفير الوقت، خصوصًا عند التعامل مع جداول ديناميكية أو معقدة. تبرز فائدة الماكرو عندما يتغير حجم مجموعة البيانات وتريد تجنب تحديث الصيغ يدويًّا بشكل متكرر، أو عندما تحتاج إلى استخراج قيم من مواضع مختلفة باستخدام نفس الأسلوب. فيما يلي حلٌّ مبني على رمز VBA يطلب منك تحديد الصف أو العمود المستهدف، ثم يُرجع القيمة الثانية من النهاية تلقائيًّا—مما يلغي الحاجة إلى العد أو التعديل اليدوي.
1. انتقل إلىأدوات المطورفي شريط Excel، ثم انقر فوقVisual Basic لفتح محرر VBA. في المحرر، انقر فوقإدراج > وحدة نمطية. بعد ذلك، الصق رمز VBA التالي في الوحدة النمطية الجديدة:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. بعد إدخال الرمز، عُد إلى Excel وشغّل الماكرو باستخدام زر
تشغيلأو بالضغط علىAlt + F8 واختيارGetSecondToLastValue من القائمة. سيطلب منك مربّع حوار تحديد نطاق يحتوي على صف واحد أو عمود واحد (مثل B1:B16 للعمود، أو A6:E6 للصف). وبعد تأكيد تحديدك، سيعرض الماكرو القيمة الثانية من النهاية في مربّع حوار.
ملاحظات:
- يقوم هذا الماكرو باحتساب الخلايا غير الفارغة فقط، ويتجاوز تلقائيًا أي خلايا فارغة ضمن الصف أو العمود المحدد.
- إذا كان عدد القيم غير الفارغة في تحديك أقل من قيمتين، فستتلقى رسالة تحذير ولن تُرجع أي نتيجة.
- هذا الماكرو مصمم خصيصًا لاختيار صف واحد أو عمود واحد فقط. إذا قمت بتحديد عدة صفوف وأعمدة معًا، فسيُطلب منك تعديل تحديدك.
- لأتمتة العملية، يمكنك تعديل الرمز لنسخ النتيجة مباشرةً إلى خلية محددة في ورقة العمل بدلاً من عرضها في مربع رسالة.
يُعد حل VBA هذا مثاليًا خصوصًا للمستخدمين الذين يعملون مع الجداول الديناميكية أو مجموعات البيانات المتغيرة باستمرار، إذ يقلل من الأخطاء اليدوية ويختصر المهام المتكررة.
طرق Excel المدمجة الأخرى – تصفية جميع الخلايا الفارغة وتحديد القيمة الثانية من النهاية يدويًا
بينما توفّر الصيغ والماكرو طرقًا آلية للعثور على القيمة الثانية من النهاية، قد تفضّل أحيانًا اعتماد نهج بصري سريع—خاصةً مع البيانات غير المنتظمة أو غير المتصلة، أو عندما تحتاج فقط إلى التحقق العرضي من قيمة ما. وتتيح لك أدوات التصفية المدمجة في Excel إخفاء الخلايا الفارغة أو غير ذات الصلة مؤقتًا، مما يسهّل تحديد الإدخال المطلوب دون الحاجة إلى تعديل البيانات أو كتابة صيغ.
طريقة التصفية:
- حدد نطاق البيانات الخاص بعمودك أو صفك: بالنسبة للأعمدة، حدد الخلايا من B1 إلى B16؛ أما بالنسبة للصفوف، فحدد الخلايا من A6 إلى E6 في ورقة عملك.
- في شريط Excel، انقر فوقبيانات > تصفيةلتمكين أسهم التصفية. ثم في رأس العمود أو بجانب نطاق التحديد الخاص بك، انقر فوق قائمة التصفية المنسدلة.
- قم بإلغاء تحديد(خلايا فارغة)لإخفاء الخلايا الفارغة. وسيعرض جدول البيانات الآن القيم غير الفارغة فقط.
- بالنسبة إلى العمود، قم بالتمرير لأسفل حتى نهاية القائمة المُرشَّحة، ثم اعرض القيمة الثانية من النهاية. أما بالنسبة إلى الصف (بعد التصفية، إن أمكن)، فعد من اليمين لتحديد الإدخال الثاني من النهاية غير الفارغ.
ملاحظات ونصائح:
- تُعد التصفية الأسرع خيارًا مثاليًا في القوائم الصغيرة إلى المتوسطة الحجم، أو عندما تحتاج إلى التأكد بصريًا من موضع القيم.
- إذا كانت مجموعة بياناتك كبيرة جدًا، فقد تؤدي التصفية إلى إبطاء الأداء، كما أنها ليست الخيار الأمثل للأتمتة أو الاستخدام المتكرر.
- عند تطبيق المرشحات، تأكد من أنك تنظر إلى السياق الصحيح، إذ قد تتخطى القوائم المُرشَّحة الصفوف أو الأعمدة المخفية.
- لإزالة المرشحات، انقر فوقمسحفي تبويب البيانات.
هذه الطريقة أقل ملاءمة للتحليل الديناميكي المستمر أو مجموعات البيانات الكبيرة، لكنها توفر تحققًا يدويًّا شفافًا من الإدخال الثاني من النهاية غير الفارغ، وهو ما قد يُسهّل استكشاف الأخطاء أو التحقق منها بسرعة.
مقالات ذات صلة:
- كيف يمكن العثور على موضع أول رقم أو آخر رقم في سلسلة نصية في Excel؟
- كيف يمكن العثور على أول جمعة أو آخر جمعة من كل شهر في Excel؟
- كيف تستخدم دالة VLOOKUP للعثور على أول قيمة مطابقة أو ثانيها أو القيمة المطابقة n في Excel؟
- كيف تجد القيمة الأكثر تكرارًا في نطاق معين في Excel؟
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل