Excel المرجع المطلق (كيفية إنشاء واستخدام)
عند الإشارة إلى خلية في صيغة في Excel، يكون نوع المرجع الافتراضي هو المرجع النسبي. ستتغير هذه المراجع عندما يتم نسخ الصيغة إلى خلايا أخرى بناءً على أعمدتها وصفوفها النسبية. إذا كنت ترغب في الحفاظ على مرجع ثابت بغض النظر عن مكان نسخ الصيغة، تحتاج إلى استخدام المرجع المطلق.
- ما هو المرجع المطلق؟
- كيفية إنشاء المراجع المطلقة
- استخدام المرجع المطلق مع أمثلة
- حساب نسبة مئوية من الإجمالي
- البحث عن قيمة وإرجاع القيمة المطابقة المقابلة
- نقرتان لتحويل جميع المراجع الخلوية إلى مراجع مطلقة باستخدام Kutools
- المراجع النسبية والمراجع المختلطة
- أشياء يجب تذكرها
فيديو: المرجع المطلق
ما هو المرجع المطلق؟
المرجع المطلق هو نوع من مراجع الخلايا في Excel.
مقارنة بالمرجع النسبي الذي يتغير بناءً على موقعه النسبي عند نسخ الصيغة إلى خلايا أخرى، فإن المرجع المطلق يبقى ثابتًا بغض النظر عن مكان نسخ أو نقل الصيغة.
يتم إنشاء المرجع المطلق بإضافة علامة الدولار ($) قبل مراجع العمود والصف في الصيغة. على سبيل المثال، لإنشاء مرجع مطلق للخلية A1، يجب تمثيلها كـ $A$1.
المراجع المطلقة مفيدة عندما تريد الإشارة إلى خلية أو نطاق ثابت في صيغة سيتم نسخها إلى عدة خلايا، ولكنك لا تريد أن تتغير المرجع.
على سبيل المثال، يحتوي النطاق A4:C7 على أسعار المنتجات، وتريد الحصول على الضريبة المستحقة لكل منتج بناءً على معدل الضريبة في الخلية B2.
إذا استخدمت مرجعًا نسبيًا في الصيغة مثل "=B5*B2"، فسيتم إرجاع نتائج غير صحيحة عند سحب مقبض التعبئة التلقائي لتطبيق هذه الصيغة. حيث سيتغير المرجع للخلية B2 بالنسبة إلى الخلايا في الصيغة. الآن، الصيغة في الخلية C6 هي "=B6*B3"، والصيغة في الخلية C7 هي "=B7*B4".
ولكن إذا استخدمت مرجعًا مطلقًا للخلية B2 مع الصيغة "=B5*$B$2"، فسيتم ضمان أن معدل الضريبة يبقى كما هو لجميع الخلايا عند سحب الصيغة باستخدام مقبض التعبئة التلقائي، وستكون النتائج صحيحة.
استخدام المرجع النسبي | استخدام المرجع المطلق | |
![]() | ![]() |
كيفية إنشاء المراجع المطلقة
لإنشاء مرجع مطلق في Excel، أضف علامات الدولار ($) قبل مراجع العمود والصف في الصيغة. هناك طريقتان لإنشاء المرجع المطلق:
إضافة علامات الدولار يدويًا إلى المرجع الخلوي
يمكنك إضافة علامات الدولار ($) يدويًا قبل مراجع العمود والصف التي تريد جعلها مطلقة أثناء كتابة الصيغة في خلية.
على سبيل المثال، إذا كنت تريد إضافة الأرقام في الخلية A1 وB1 وجعل كل منهما مطلقًا، فقط اكتب الصيغة كالتالي: "=$A$1+$B$1". هذا سيضمن أن مراجع الخلايا تبقى ثابتة عند نسخ أو نقل الصيغة إلى خلايا أخرى.
بدلاً من ذلك، إذا كنت تريد تغيير المراجع في صيغة موجودة إلى مراجع مطلقة، يمكنك تحديد الخلية، ثم الانتقال إلى شريط الصيغة لإضافة علامات الدولار ($).
استخدام اختصار F4 لتحويل المرجع النسبي إلى مطلق
- انقر نقرًا مزدوجًا على الخلية التي تحتوي على الصيغة لدخول وضع التحرير;
- ضع المؤشر عند المرجع الخلوي الذي تريد جعله مطلقًا;
- اضغط على مفتاح "F4" على لوحة المفاتيح للتبديل بين أنواع المراجع حتى يتم إضافة علامات الدولار قبل مراجع العمود والصف;
- اضغط على مفتاح "Enter" للخروج من وضع التحرير وتطبيق التغييرات.
مفتاح F4 يقوم بالتبديل بين المراجع النسبية والمطلقة والمختلطة.
A1 → $A$1 → A$1 → $A1 → A1
إذا كنت تريد جعل جميع المراجع مطلقة في صيغة، حدد الصيغة بأكملها في شريط الصيغة، واضغط على مفتاح "F4" للتبديل بين أنواع المراجع حتى يتم إضافة علامات الدولار قبل مراجع العمود والصف.
A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1
استخدام المرجع المطلق مع أمثلة
هذا الجزء يقدم مثالين لتوضيح متى وكيفية استخدام المراجع المطلقة في صيغة Excel.
المثال الأول: حساب نسبة مئوية من الإجمالي
لنفترض أن لديك نطاق بيانات (A3:B7) يحتوي على مبيعات كل فاكهة، والخلية B8 تحتوي على إجمالي مبيعات هذه الفواكه، الآن تريد حساب نسبة مبيعات كل فاكهة من الإجمالي.
الصيغة العامة لحساب نسبة مئوية من الإجمالي:
Percentage = Sale/Amount
استخدم المرجع النسبي في الصيغة للحصول على نسبة أول فاكهة كالتالي:
=B4/B8
عند سحب مقبض التعبئة التلقائي لأسفل لحساب نسبة الفواكه الأخرى، سيتم إرجاع أخطاء #DIV/0!.
بما أنه عند سحب مقبض التعبئة التلقائي لنسخ الصيغة إلى الخلايا أدناه، يتم تعديل المرجع النسبي B8 تلقائيًا إلى مراجع خلايا أخرى (B9، B10، B11) بناءً على مواقعها النسبية. والخلايا B9، B10 وB11 فارغة (أصفار)، عندما يكون القاسم صفرًا، تعيد الصيغة خطأ.
لإصلاح الأخطاء، في هذه الحالة، تحتاج إلى جعل مرجع الخلية B8 مطلقًا ($B$8) في الصيغة للحفاظ عليه من التغيير عند نقل أو نسخ الصيغة إلى أي مكان. الآن تم تحديث الصيغة إلى:
=B4/$B$8
ثم اسحب مقبض التعبئة التلقائي لأسفل لحساب نسبة الفواكه الأخرى.
المثال الثاني: البحث عن قيمة وإرجاع القيمة المطابقة المقابلة
لنفترض أنك تريد البحث عن قائمة الأسماء في D4:D5 وإرجاع رواتبهم المقابلة بناءً على أسماء الموظفين والرواتب السنوية المقدمة في النطاق (A4:B8).
الصيغة العامة للبحث هي:
=VLOOKUP(lookup_value, table_range, column_index, logical)
إذا استخدمت المرجع النسبي في الصيغة للبحث عن قيمة وإرجاع القيمة المطابقة المقابلة كالتالي:
=VLOOKUP(D4,A4:B8,2,FALSE)
ثم اسحب مقبض التعبئة التلقائي لأسفل للبحث عن القيمة أدناه، سيتم إرجاع خطأ.
عندما تسحب مقبض التعبئة لأسفل لنسخ الصيغة إلى الخلية أدناه، يتم تعديل المراجع في الصيغة تلقائيًا لأسفل بمقدار صف واحد. نتيجة لذلك، يصبح المرجع إلى نطاق الجدول، A4:B8، إلى A5:B9. بما أن Lisa لا يمكن العثور عليها في النطاق A5:B9، تعيد الصيغة خطأ.
لتجنب الأخطاء، استخدم المرجع المطلق $A$4:$B$8 بدلاً من المرجع النسبي A4:B8 في الصيغة:
=VLOOKUP(D4,$A$4:$B$8,2,FALSE)
ثم اسحب مقبض التعبئة التلقائي لأسفل للحصول على راتب Lisa.
نقرتان لتحويل جميع المراجع الخلوية إلى مراجع مطلقة باستخدام Kutools
سواء اخترت الكتابة يدويًا أو استخدام اختصار F4، يمكنك فقط تغيير صيغة واحدة في كل مرة في Excel. إذا كنت تريد جعل المراجع الخلوية في مئات الصيغ مطلقة في Excel، يمكن أن تساعدك أداة "تحويل المراجع" من Kutools لـ Excel في التعامل مع المهمة بنقرتين.
لجعل المراجع الخلوية مطلقة في صيغ متعددة، حدد خلايا الصيغ وانقر على "Kutools" > "المزيد" > "تحويل المراجع". ثم اختر الخيار "إلى مطلق" واضغط على "موافق" أو "تطبيق". الآن تم تحويل جميع المراجع الخلوية للصيغ المحددة إلى مراجع مطلقة.
خاصية "تحويل المراجع" ستغير جميع المراجع الخلوية في الصيغة.
لاستخدام خاصية "تحويل المراجع"، يجب عليك تثبيت Kutools لـ Excel. انقر لتنزيل Kutools لـ Excel الآن.
المراجع النسبية والمراجع المختلطة
بالإضافة إلى المرجع المطلق، هناك نوعان آخران من أنواع المراجع: المرجع النسبي والمرجع المختلط.
المرجع النسبي هو نوع المرجع الافتراضي في Excel، وهو بدون علامات الدولار ($) قبل مراجع الصفوف والأعمدة. وعندما يتم نسخ أو نقل صيغة تحتوي على مراجع نسبية إلى خلايا أخرى، ستتغير المراجع تلقائيًا بناءً على مواقعها النسبية.
على سبيل المثال، عندما تقوم بكتابة صيغة في خلية مثل "=A1+1"، ثم تسحب مقبض التعبئة التلقائي لأسفل لتعبئة هذه الصيغة إلى الخلية التالية، ستتغير الصيغة تلقائيًا إلى "=A2+1".
المرجع المختلط يتكون من كل من المرجع المطلق والمرجع النسبي. بمعنى آخر، يستخدم المرجع المختلط علامة الدولار ($) لإصلاح إما الصف أو العمود عند نسخ أو تعبئة الصيغة.
خذ جدول الضرب كمثال، الصفوف والأعمدة تعرض الأرقام من 1 إلى 9، والتي ستحتاج إلى ضربها ببعضها البعض.
للبدء، يمكنك استخدام الصيغة "=B3*C2" في الخلية C3 لضرب الرقم 1 في الخلية B3 بالرقم (1) في العمود الأول. ومع ذلك، عند سحب مقبض التعبئة التلقائي لليمين لتعبئة الخلايا الأخرى، ستلاحظ أن جميع النتائج غير صحيحة باستثناء الأولى.
هذا لأن الموقع الصفري لن يتغير عند نسخ الصيغة إلى اليمين، لكن الموقع العمودي يتغير من B3 إلى C3، D3، وما إلى ذلك. نتيجة لذلك، تتحول الصيغ في الخلايا اليمنى (D3، E3، وما إلى ذلك) إلى "=C3*D2"، "=D3*E2"، وما إلى ذلك، بينما ترغب في أن تكون "=B3*D2"، "=B3*E2"، وما إلى ذلك.
في هذه الحالة، تحتاج إلى إضافة علامة الدولار ($) لتأمين مرجع العمود "B3". استخدم الصيغة التالية:
=$B3*C2
الآن عند سحب الصيغة إلى اليمين، تكون النتائج صحيحة.
ثم تحتاج إلى ضرب الرقم 1 في الخلية C2 بالأرقام الموجودة في الصفوف أدناه.
عند نسخ الصيغة لأسفل، لن يتغير الموقع العمودي للخلية C2، لكن الموقع الصفري يتغير من C2 إلى C3، C4، وما إلى ذلك. نتيجة لذلك، تتحول الصيغ في الخلايا أدناه إلى "=$B4C3"، "=$B5C4"، وما إلى ذلك، مما سيؤدي إلى نتائج غير صحيحة.
لحل هذه المشكلة، قم بتغيير “C2” إلى “C$2” للحفاظ على المرجع الصفري ثابتًا عند سحب مقبض التعبئة التلقائي لأسفل لتعبئة الصيغ.
=$B3*C$2
الآن يمكنك سحب مقبض التعبئة التلقائي لليمين أو لأسفل للحصول على جميع النتائج.
أشياء يجب تذكرها
ملخص المراجع الخلوية
النوع المثال الملخص المرجع المطلق $A$1 لا يتغير أبدًا عند نسخ الصيغة إلى خلايا أخرى المرجع النسبي A1 يتغير كل من مرجع الصف والعمود بناءً على الموقع النسبي عند نسخ الصيغة إلى خلايا أخرى المرجع المختلط $A1/A$1
يتغير مرجع الصف عند نسخ الصيغة إلى خلايا أخرى ولكن مرجع العمود ثابت / يتغير مرجع العمود عند نسخ الصيغة إلى خلايا أخرى ولكن مرجع الصف ثابت; بشكل عام، لا تتغير المراجع المطلقة أبدًا عند نقل الصيغة. ومع ذلك، ستقوم المراجع المطلقة بالتعديل تلقائيًا عند إضافة أو إزالة صف أو عمود من أعلى أو يسار ورقة العمل. على سبيل المثال، في صيغة "=$A$1+1"، عند إدراج صف في أعلى الورقة، ستتغير الصيغة تلقائيًا إلى "=$A$2+1".
مفتاح "F4" يمكنه التبديل بين المراجع النسبية والمطلقة والمختلطة.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel مع Kutools لـ Excel، واختبر الكفاءة كما لم يحدث من قبل. Kutools لـ Excel يقدم أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يقدم واجهة التبويب لـ Office، ويجعل عملك أسهل بكثير
- تمكين تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint.
- افتح وأنشئ عدة مستندات في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ منفصلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات بالماوس يومياً!
جدول المحتويات
- فيديو: المرجع المطلق
- ما هو المرجع المطلق
- كيفية إنشاء المراجع المطلقة
- استخدام المرجع المطلق مع أمثلة
- حساب نسبة مئوية من الإجمالي
- البحث عن قيمة وإرجاع القيمة المطابقة المقابلة
- نقرتان لتحويل جميع المراجع الخلوية إلى مراجع مطلقة باستخدام Kutools
- المراجع النسبية والمراجع المختلطة
- أشياء يجب تذكرها
- مقالات ذات صلة
- أفضل أدوات إنتاجية المكتب
- تعليقات