كيفية الحصول دائمًا على القيمة من الخلية أعلاها عند إدراج أو حذف صف في Excel؟
في Excel، يعتبر الرجوع إلى قيمة الخلية الموجودة مباشرة فوق الخلية مطلبًا شائعًا لإنشاء المجاميع المتراكمة، المقارنات الشهرية، أو ببساطة الحفاظ على اتساق البيانات مع تطور ورقة العمل. عادةً، يمكنك الرجوع إلى الخلية الموجودة أعلاها باستخدام صيغة مباشرة مثل =D5، لكن هذا الحل له قيوده: إذا قمت بإدراج أو حذف صفوف، قد لا تستمر الصيغة في الرجوع إلى الخلية "العليا" الجديدة كما تتوقع. كما هو موضح أدناه، فإن إدراج صف جديد أعلاها يمكن أن يكسر الاستمرارية أو يبقي المرجع مرتبطًا بالخلية الأصلية بدلاً من تعديله إلى الموقع الجديد.
لحل هذه المشكلة وضمان حصول الصيغة دائمًا على القيمة من الخلية الموجودة مباشرة أعلاها حتى بعد إدراج أو حذف الصفوف، هناك عدة طرق يمكنك اتباعها. يأتي كل أسلوب مع مجموعة مختلفة من التنازلات بناءً على تعقيد ورقة العمل، سواء كنت تريد تحديثًا تلقائيًا أو صيغة يدوية، وإذا كنت مرتاحًا لاستخدام VBA/المقابس البرمجية.
جدول المحتويات:
- الحصول دائمًا على القيمة من الخلية أعلاها عند إدراج أو حذف الصفوف باستخدام الصيغة
- تحديث قيمة الخلية تلقائيًا من الخلية أعلاها باستخدام مقابس VBA القائمة على الأحداث (دائمًا ديناميكي)
الحصول دائمًا على القيمة من الخلية أعلاها عند إدراج أو حذف الصفوف باستخدام الصيغة
لحل هذه المشكلة بطريقة مباشرة لا تتطلب مقابس أو إعدادات معقدة، يمكنك استخدام صيغة تشير بشكل ديناميكي إلى الخلية أعلاها، بغض النظر عن كيفية تغير الصفوف. تستخدم الصيغة وظائف INDIRECT و ADDRESS في Excel بحيث يستمر الرجوع إلى الخلية أعلاها دائمًا في "التتبع"، حتى لو تم نقل الصفوف بسبب الإدخالات أو الحذف. هذا يجعلها مناسبة بشكل خاص لأوراق العمل التي تقوم فيها بتعديل هيكل الصفوف بشكل متكرر، مثل إضافة بيانات جديدة في الجزء العلوي أو الأوسط من القائمة.
أدخل الصيغة التالية مباشرة في الخلية التي تريد دائمًا الحصول على القيمة منها من الخلية أعلاها (على سبيل المثال، في الخلية B6 إذا كنت تريد الرجوع إلى B5):
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))اضغط على Enter بعد كتابة الصيغة. ستعرض الخلية الحالية فورًا قيمة الخلية الموجودة مباشرة أعلاها، كما هو موضح أدناه:
الآن، إذا قمت بإدراج صف جديد في أي مكان أعلاه الخلية التي تحتوي على صيغة المرجع، ستعيد الصيغة حسابها وستعرض دائمًا القيمة من الخلية الجديدة أعلاها. بهذه الطريقة تكون صيغك دائمًا محدثة، بغض النظر عن عمليات إدراج أو حذف الصفوف. ألقِ نظرة على لقطة الشاشة التالية للرجوع إليها:
شرح المعلمات والنصائح:
- هذه الصيغة ستسحب قيمة الخلية الموجودة مباشرة أعلاها الخلية الحالية - لذلك استخدامها في B6 سيعرض دائمًا B5، حتى إذا تم إدراج أو حذف صفوف أعلاها.
- إذا استخدمت هذه الصيغة في الصف الأول من بياناتك (مثل A1)، فقد تحاول سحب البيانات من صف غير موجود وتعيد خطأ
#REF!. يمكنك تجنب ذلك بإضافة معالجة للأخطاء، على سبيل المثال باستخدام=IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN())))لعرض فارغ في الصف الأول. - ضع في اعتبارك أن INDIRECT هي دالة متقلبة، لذلك في أوراق العمل الكبيرة جدًا، قد يؤدي الاستخدام المفرط إلى إبطاء العمليات الحسابية.
- تعمل هذه الصيغة بشكل جيد عندما تريد الحفاظ على علاقة صارمة مع موقع الصف، بغض النظر عن كيفية تغيير هيكل ورقة العمل.
اقتراحات واستنتاجات لتصحيح المشاكل:
إذا لم يتم تحديث صيغتك كما هو متوقع بعد إدراج أو حذف الصفوف، تحقق مرة أخرى من أنها تم إدخالها في الخلية المقصودة. أيضًا، تأكد من أنك لا تستخدم مراجع الخلايا المطلقة (مثل $A$1)، والتي تكون ثابتة. إذا واجهت #REF! أخطاء في الصف الأول، ضع في اعتبارك استخدام صيغة شرطية كما ذكرنا سابقًا. للتشغيل الآلي المتقدم، أو إذا كنت بحاجة إلى نسخ القيمة وليس فقط الرجوع إليها، راجع حل المقابس البرمجية VBA القائمة على الأحداث أدناه لنهج ديناميكي يعتمد على التعليمات البرمجية.
تحديث قيمة الخلية تلقائيًا من الخلية أعلاها باستخدام مقابس VBA القائمة على الأحداث (دائمًا ديناميكي)
إذا كنت بحاجة إلى حل حيث يتم تحديث قيمة الخلية دائمًا تلقائيًا لتتطابق مع الخلية أعلاها بغض النظر عن إدراج أو حذف الصفوف - وتريد هذا السلوك بدون نسخ الصيغ - فإن استخدام مقابس VBA القائمة على الأحداث يكون فعالًا. هذه الطريقة مثالية للمستخدمين المريحين بتمكين المقابس البرمجية وتسمح بالتحديث الديناميكي: كلما قمت باختيار خلية أو نطاق معين، أو عندما تقوم بتغيير قيمة، يمكن للمقابس أن تضبط الخلية المستهدفة فورًا لتساوي دائمًا القيمة أعلاها، مما يجعل إدخال البيانات متسقًا بغض النظر عن كيفية تغيير ورقة العمل. هذا مفيد بشكل خاص في القوالب حيث تريد نمطًا ثابتًا أو إعداد القيم الافتراضية في كل صف جديد.
إليك كيفية إعداد هذا باستخدام حدث Worksheet_Change:
1. انقر بزر الماوس الأيمن على علامة ورقة العمل حيث تريد هذه الوظيفة، وحدد عرض الكود. ستفتح محرر Microsoft Visual Basic for Applications على وحدة ورقة العمل الصحيحة.
2. انسخ والصق الكود التالي لـ VBA في نافذة وحدة ورقة العمل:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
On Error Resume Next
' Set the range you want to monitor (for example, B2:B100)
Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
If Not WatchRange Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In WatchRange
' Avoid the first row, or adjust as needed
If cell.Row > 1 Then
cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
End If
Next cell
Application.EnableEvents = True
End If
End Subملاحظات حول المعلمات: استبدل "B2:B100" في Me.Range("B2:B100") بالنطاق الفعلي الذي ترغب في تطبيق هذا السلوك عليه (يمكنك تعيينه إلى عمود كامل، على سبيل المثال "B:B"، ولكن تضييق النطاق يحسن الأداء ويتجنب الكتابة فوق غير المقصودة).
3. أغلق محرر VBA. الآن، كلما تم تغيير خلية، أو إدراجها، أو تحديث ورقة العمل داخل النطاق المحدد، سيقوم Excel تلقائيًا بتحديث تلك الخلايا لتعكس القيمة أعلاها. على سبيل المثال، إذا قمت بإدراج صف في الصف 5، فسيقوم جميع الخلايا المراقبة في العمود B من تلك النقطة بأخذ القيمة من الخلية الموجودة مباشرة أعلاها في موقعها الجديد.
- كن حذرًا: سيؤدي هذا الكود إلى الكتابة فوق القيم المدخلة يدويًا ضمن النطاق المراقب. استخدمه بحذر إذا كانت لديك خلايا صيغ أو ترغب في الحفاظ على الإدخالات الأصلية.
- إذا كنت ترغب في الاستمرار في استخدام هذا الماكرو VBA في المصنف في المستقبل، تحتاج إلى حفظ الملف كمصنف ممكّن الماكرو (.xlsm).
- سيعمل كود الحدث فقط على وحدة ورقة العمل التي تم لصق الماكرو بها (ليس عبر جميع الأوراق إلا إذا تم إضافة الكود إلى كل وحدة ورقة عمل).
- إذا كنت ترغب في حدوث التحديث عند اختيار خلية بدلاً من عند تغيير القيمة، يمكنك استخدام Worksheet_SelectionChange ومنطق مشابه.
اقتراحات واستنتاجات لتصحيح المشاكل:
إذا لم يبدو أن البرنامج النصي VBA يعمل بعد النسخ، تأكد من تمكين المقابس البرمجية في مصنفك وأنك ألصقت الكود في وحدة الورقة الصحيحة (وليس وحدة قياسية). إذا حصلت على أخطاء أو تجمد Excel، تحقق مرتين من أن Application.EnableEvents مضبوط على False قبل تغييرات الخلية التلقائية وإعادة تعيينه إلى True بعد ذلك لتجنب التكرار اللانهائي. لسلوكيات متقدمة أخرى أو تحكم أكثر دقة، ضع في اعتبارك كتابة سكربت مخصص بناءً على هيكل بياناتك.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في 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 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد