كيفية إضافة $ بسرعة إلى صيغ Excel؟

في برنامج Excel، تكون مراجع الخلايا في الصيغ نسبية بشكل افتراضي، مثل "A1". هذا يعني أنه عند نسخ أو سحب صيغة إلى موقع آخر، ستقوم المراجع بالتحديث تلقائيًا لتعكس مواقعها الجديدة. على سبيل المثال، إذا أدخلت الصيغة =A1+B1 في الخلية C1 ثم نسختها إلى الخلية H12، ستقوم الصيغة بالتعديل التلقائي لتصبح =F12+G12. هذا السلوك مفيد في العديد من الحالات ولكنه قد يؤدي إلى أخطاء عندما تحتاج دائمًا إلى الإشارة إلى خلايا معينة بغض النظر عن مكان وضع الصيغة. لمنع هذا التعديل التلقائي، تحتاج إلى تغيير مراجع الخلايا إلى مراجع مطلقة بإضافة علامة الدولار ($)، مثل $A$1. تبقى المراجع المطلقة ثابتة عند نسخها أو سحبها، مما يجعلها ضرورية للحسابات باستخدام القيم أو النطاقات الثابتة.

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

إضافة $ إلى الصيغة باستخدام مفاتيح الاختصار

إضافة $ إلى الصيغة باستخدام Kutools لـ Excel

استخدام ماكرو VBA لتحويل مراجع الخلايا دفعة واحدة إلى مراجع مطلقة

إضافة $ إلى الصيغة باستخدام مفاتيح الاختصار

يمكنك التبديل بسهولة بين أنواع مختلفة من مراجع الخلايا – النسبية، المطلقة والمختلطة – باستخدام مفاتيح الاختصار. لتغيير مرجع الخلية، قم ببساطة باختيار الخلية المستهدفة، انقر داخل شريط الصيغة ووضع المؤشر داخل أو بجانب مرجع الخلية الذي ترغب في تعديله. بعد ذلك، اضغط على F4 للتغيير بين أنواع المراجع المتاحة. كل ضغطة على F4 تقوم بتحديث المرجع بهذا الترتيب: نسبي (A1), مطلق ($A$1), مختلط (عمود ثابت) (A$1), و مختلط (صف ثابت) ($A1).

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

إضافة $ إلى الصيغة باستخدام Kutools لـ Excel

اختصار F4 يمكنه فقط التعامل مع مرجع خلية واحد في كل مرة، مما يجعله غير فعال عندما تحتاج إلى تحديث عدة خلايا أو نطاق كامل من الصيغ. إذا كنت تتعامل مع صيغ متعددة أو تحتاج إلى توحيد أنواع المراجع عبر ورقة العمل، فإن Kutools لـ Excel يقدم حلاً فعالاً من خلال أداة Convert Refers.

بعد التثبيت المجاني Kutools لـ Excel، يمكنك اتباع الخطوات التالية:

1. حدد جميع خلايا الصيغ التي ترغب في إضافة علامات $ إليها (جعل المراجع مطلقة). ثم انتقل إلى Kutools > المزيد > تحويل المراجع. انظر لقطة الشاشة:

2. في النافذة المنبثقة التي تظهر، اختر نوع المرجع الذي تحتاج إليه. لجعل جميع مراجع الخلايا مطلقة، حدد الخيار “To absolute”.

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

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

استخدم ماكرو VBA لتحويل مراجع الخلايا دفعة واحدة إلى مراجع مطلقة

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

1. على شريط أدوات Excel، اذهب إلى Developer Tools > Visual Basic. في نافذة Microsoft Visual Basic for Applications، انقر Insert > Module، ثم انسخ الكود التالي في الوحدة الجديدة:

Sub ConvertToAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formulaStr As String
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to convert formulas to absolute references", xTitleId, rng.Address, Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.HasFormula Then
            formulaStr = cell.Formula
            For i = 1 To 10 ' Protects from infinite looping
                formulaStr = Replace(formulaStr, "([A-Za-z]+)([0-9]+)", "$$1$$2") ' Basic pattern replacement
            Next i
            cell.Formula = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

2. وبعد ذلك، انقر Run لتشغيل الكود. ستظهر نافذة منبثقة تطلب منك تحديد النطاق الذي ترغب في معالجته. بعد التأكيد، سيتم تحويل جميع مراجع الخلايا الصالحة داخل النطاق تلقائيًا إلى النمط المرجعي المطلق (مع علامات $).

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

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

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