تحويل مراجع الخلايا إلى مراجع مطلقة حسب العمود أو الصف في Excel

أحيانًا في Excel، تحتاج الصيغة إلى تثبيت جزءٍ فقط من مرجع الخلية عند نسخها إلى صف أو عمود آخر. على سبيل المثال،$A1 يُثبّت العمود ويسمح بتغيير الصف، وA$1 يُثبّت الصف ويسمح بتغيير العمود. تُعدّ هذه المراجع المختلطة مثالية عندما تريد ملء الصيغ بشكلٍ دقيق عبر نطاقٍ دون جعل المرجع بأكمله مطلقًا.

في هذا البرنامج التعليمي، سنستعرض عدة طرق لتحويل المراجع النسبية إلى مراجع ذات أعمدة مطلقة أو صفوف مطلقة. يمكنك استخدام مفتاح F4 لإجراء تغييرات سريعة، أو إدخال علامة الدولار ($) يدويًّا لمزيد من التحكُّم، أو تجربة ميزة البحث والاستبدال في الحالات البسيطة، أو استخدام Kutools for Excel للتحويل الدُّفعي، أو اللجوء إلى VBA إذا كنت تفضِّل عدم تثبيت إضافات خارجية.

• الطريقة 1: استخدام مفتاح F4 للتبديل بين أنواع المراجع
• الطريقة 2: إضافة علامة الدولار ($) يدويًّا
• الطريقة 3: استخدام البحث والاستبدال لأنماط المراجع البسيطة
• الطريقة 4: تحويل المراجع دفعةً واحدة باستخدام Kutools لـ Excel
• الطريقة 5: تغيير المراجع في الصيغ المحددة باستخدام VBA
• أي طريقة تناسبك أكثر؟

استخدم مفتاح F4 للتبديل بين أنواع المراجع

هذه هي أسرع طريقة مدمجة لتغيير مرجع واحد في كل مرة داخل صيغة! يتيح لك Excel التنقّل بسهولة بين المراجع النسبية والمطلقة والمختلطة بمجرد الضغط علىF4.

1. حدد الخلية التي تحتوي على الصيغة، ثم انقر نقرًا مزدوجًا عليها أو اضغطF2 للدخول في وضع التحرير.
2. انقر على مرجع الخلية الذي ترغب في تعديله ضمن الصيغة.
3. اضغطF4مرارًا وتكرارًا حتى يصبح المرجع أحد ما يلي:
   • $A$1- مرجع مطلق
   • A$1- مرجع صف مطلق ✅️
   • $A1- مرجع عمود مطلق ✅️
   • A1- مرجع نسبي
4. اضغطEnter لتأكيد الصيغة.

أضف علامة الدولار ($) يدويًّا

إذا كنت بحاجة فقط إلى تعديل بعض المراجع، يمكنك إدخال علامة الدولار مباشرةً في الصيغة.

1. حدد خلية الصيغة، ثم انقر في شريط الصيغة أو اضغطF2.
2. عدّل المرجع يدويًّا:
   • غيّرA1 إلى$A1 لتثبيت العمود فقط.
   • غيّرA1 إلىA$1 لتثبيت الصف فقط.
3. اضغطEnter لتطبيق التغيير.

استخدم البحث والاستبدال لتغيير المراجع

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

1. حدد النطاق الذي يحتوي على الصيغ التي ترغب في تحديثها.
2. اضغطCtrl + H لفتح مربع حوارالبحث والاستبدال.
3. في مربعبحث عن، أدخل نمط المرجع الذي تريد تغييره، مثل العمود «F».
4. في مربعاستبدال بـ، أدخل نمط المرجع المحدّث مع إضافة علامة الدولار، مثل «$F».
   • على سبيل المثال، استبدلF بـ$F لجعل العمود مطلقًا.
   • أو استبدل2بـ$2لجعل الصف مطلقًا.

     

5. انقراستبدال الكل.

نتيجةً لذلك، يتم تغيير جميع مراجعF في الصيغ المحددة إلى$F، مما يُثبّت العمود F.

تحويل المراجع دفعةً واحدة باستخدام Kutools لـ Excel

للحصول على حل دفعي أكثر موثوقية، توفرKutools لـ Excelميزةتحويل مراجع الخلية، التي تتيح لك تحويل الصيغ المحددة بسرعة إلى مراجع ذات أعمدة مطلقة مثل$A1 أو مراجع ذات صفوف مطلقة مثلA$1.

1. حدد النطاق الذي يحتوي على الصيغ التي ترغب في تحويلها.
2. انقرKutools > المزيد > تحويل مراجع الخلية.
3. في مربع حوارتحويل مراجع الخلية، اختر أحد الخيارات التالية:
   • لجعل العمود مطلقًا، غيّر المراجع إلى$A1.
   • لجعل الصف مطلقًا، غيّر المراجع إلىA$1.
4. انقرموافقأوتطبيق. سيتم تحويل جميع الصيغ المحددة دفعةً واحدة.

   

استخدم VBA لتغيير المراجع

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

تحويل مراجع الصيغ المحددة إلى إلى مطلق العمود

1. حدد الخلايا التي تحتوي على الصيغ التي ترغب في تحويلها.
2. اضغطAlt + F11 لفتح محرر VBA.
3. انقرإدراج > وحدة نمطية.
4. الصق الكود التالي في الوحدة النمطية:

   Sub ConvertToColumnAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=2)
           End If
       Next cell
   End Sub

5. اضغطF5 لتشغيل الماكرو وتحويل المراجع إلى نمط$A1.

تحويل مراجع الصيغ المحددة إلى إلى مطلق الصف

1. حدد الخلايا التي تحتوي على الصيغ التي ترغب في تحويلها.
2. اضغطAlt + F11 لفتح محرر VBA.
3. انقرإدراج > وحدة نمطية.
4. الصق الكود التالي في الوحدة النمطية:

   Sub ConvertToRowAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=3)
           End If
       Next cell
   End Sub

5. حدد خلايا الصيغ التي تريد تحديثها.
6. اضغطF5 لتشغيل الماكرو وتحويل المراجع إلى نمطA$1.

أي طريقة تناسبك أكثر؟

الخاتمة

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

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

عندما تحتاج إلى حل جماعي أكثر عملية، يُسهّلKutools لـ Excelالمهمة بشكل كبير من خلال تحويل المراجع المحددة بنقرات قليلة فقط. وإذا كنت تفضّل عدم استخدام إضافة خارجية وكنت مرتاحًا لاستخدام ماكرو VBA، فإنVBA يُعدّ بديلًا مرنًا لأتمتة هذه المهمة.

آمل أن تكون قد وجدت هذا البرنامج التعليمي مفيدًا! إذا رغبت في استكشاف المزيد من نصائح Excel والحلول العملية،فمن فضلك انقر هنالتصفح مجموعتنا الكاملة من دروس Excel.