المرجع المطلق في Excel (كيفية إنشائه واستخدامه)

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

• ما هو المرجع المطلق
• كيفية إنشاء مراجع مطلقة
• استخدام المرجع المطلق مع أمثلة
• حساب نسبة المجموع الكلي
• البحث عن قيمة وإرجاع القيمة المقابلة
• 2 نقرات لجعل مراجع الخلايا مطلقة دفعةً واحدة باستخدام 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 لتحويل المرجع النسبي إلى مطلق

1. انقر مرتين على الخلية التي تحتوي على الصيغة للدخول في وضع التحرير؛
2. ضع المؤشر على مرجع الخلية الذي تريد جعله مطلقًا؛
3. اضغط مفتاح "F4" على لوحة المفاتيح للتبديل بين أنواع المراجع حتى تظهر علامات الدولار قبل مرجعي العمود والصف معًا؛
4. اضغط مفتاح «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.

المثال 1: حساب نسبة مئوية من الإجمالي

افترض أن لديك نطاق البيانات (A3:B7) الذي يحتوي على مبيعات كل نوع من الفواكه، وأن الخلية B8 تحتوي على إجمالي مبيعات هذه الفواكه، وترغب الآن في حساب النسبة المئوية لمبيعات كل فاكهة من هذا الإجمالي.

الصيغة العامة لحساب النسبة المئوية من الإجمالي:

Percentage = Sale/Amount

استخدم المرجع النسبي في الصيغة للحصول على نسبة الفاكهة الأولى كما يلي:

=B4/B8

عند سحب مقبض التعبئة التلقائي لأسفل لحساب نسبة باقي الفواكه، ستظهر أخطاء من نوع #DIV/0!.

ذلك لأن المرجع النسبي B8 يتغيّر تلقائيًا إلى مراجع خلايا أخرى (B9 وB10 وB11) وفقًا لمواقعها النسبية عند سحب مقبض التعبئة لنسخ الصيغة إلى الخلايا السفلية. وبما أن الخلايا B9 وB10 وB11 فارغة (أي إن قيمتها تساوي صفرًا)، فإن القسمة على صفر تؤدي إلى ظهور خطأ.

لإصلاح هذه الأخطاء، عليك في هذه الحالة جعل مرجع الخلية B8 مطلقًا ($B$8) في الصيغة لمنع تغيّره عند نسخ الصيغة أو نقلها إلى أي مكان. والآن، أصبحت الصيغة مُحدَّثة إلى:

=B4/$B$8

ثم اسحب مقبض التعبئة التلقائي لأسفل لحساب نسب باقي الفواكه.

المثال 2: البحث عن قيمة وإرجاع القيمة المقابلة

افترض أنك تريد البحث عن قائمة الأسماء في النطاق D4:D5 وإرجاع رواتبهم السنوية المقابلة، وذلك بناءً على أسماء الموظفين والرواتب السنوية المتوفرة في النطاق A4:B8.

الصيغة العامة للبحث:

=VLOOKUP(lookup_value, table_range, column_index, logical)

إذا استخدمت مرجعًا نسبيًا في الصيغة للبحث عن قيمة وإرجاع القيمة المقابلة كما يلي:

=VLOOKUP(D4,A4:B8,2,FALSE)

ثم عند سحب مقبض التعبئة التلقائي لأسفل للبحث عن القيمة التالية، سيظهر خطأ.

عند سحب مقبض التعبئة لأسفل لنسخ الصيغة إلى الخلية التالية، تتغيّر مراجع الصيغة تلقائيًا لتشير إلى الصف الذي يليه. ونتيجةً لذلك، يصبح نطاق الجدول A4:B8 هو A5:B9. وبما أن اسم «ليزا» غير موجود في النطاق الجديد A5:B9، تُرجع الصيغة خطأً.

لتجنب هذه الأخطاء، استخدم المرجع المطلق $A$4:$B$8 بدلاً من المرجع النسبي A4:B8 في الصيغة:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

ثم اسحب مقبض التعبئة التلقائي لأسفل للحصول على راتب ليزا.

2 نقرة لجعل مراجع الخلايا مطلقة دفعة واحدة باستخدام Kutools

سواء اخترت الكتابة يدويًا أو استخدام اختصار F4، يمكنك تعديل صيغة واحدة في كل مرة في Excel. ولكن إذا أردت جعل مراجع الخلايا في مئات الصيغ مطلقة دفعة واحدة، فإن أداة «تحويل مراجع الخلية» من «Kutools لـ Excel» ستساعدك على إنجاز المهمة بنقرتين فقط!

لجعل مراجع الخلايا مطلقة في صيغ متعددة، حدد خلايا الصيغ، ثم انقر على «Kutools» > «More» > «تحويل مراجع الخلية». بعد ذلك، اختر خيار «To absolute» وانقر على «Ok» أو «Apply». الآن، أصبحت جميع مراجع الخلايا في الصيغ المحددة مطلقة.

المرجع النسبي والمرجع المختلط

بالإضافة إلى المرجع المطلق، هناك نوعان آخران من المراجع: المرجع النسبي والمرجع المختلط.

المرجع النسبيهو المرجع الافتراضي في Excel، ولا يحتوي على علامات الدولار ($) قبل مرجعي الصف والعمود. وعند نسخ صيغة تحتوي على مراجع نسبية أو نقلها إلى خلايا أخرى، تتغيّر هذه المراجع تلقائيًا وفقًا لمواقعها النسبية.

على سبيل المثال، عند إدخال صيغة مثل «=A1+1» في خلية ثم سحب مقبض التعبئة لأسفل لنسخها إلى الخلية التالية، تتغيّر الصيغة تلقائيًا إلى «=A2+1».

المرجع المختلطهو مزيجٌ من المرجع المطلق والمرجع النسبي معًا. بعبارة أخرى، يستخدم المرجع المختلط علامة الدولار ($) لتثبيت الصف أو العمود عند نسخ الصيغة أو ملئها.

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

لبدء العملية، أدخل الصيغة «=B3*C2» في الخلية C3 لضرب القيمة 1 الموجودة في الخلية B3 بالرقم (1) الموجود في العمود الأول. ومع ذلك، عند سحب مقبض التعبئة التلقائي إلى اليمين لملء الخلايا الأخرى، ستجد أن جميع النتائج تكون غير صحيحة باستثناء النتيجة الأولى.

ذلك لأن عمود الخلية B3 يتغيّر من B3 إلى C3 وD3 وما إلى ذلك عند نسخ الصيغة إلى اليمين، بينما يبقى موقع الصف دون تغيير. ونتيجة لذلك، تتغيّر الصيغ في الخلية على اليمين (D3 وE3 وما إلى ذلك) إلى «=C3*D2» و«=D3*E2» وهكذا، بينما تريد في الواقع أن تكون «=B3*D2» و«=B3*E2» وما إلى ذلك.

في هذه الحالة، تحتاج إلى إضافة علامة الدولار ($) لتثبيت مرجع العمود في الخلية "B3". استخدم الصيغة كما يلي:

=$B3*C2

الآن، عند سحب الصيغة إلى اليمين، تظهر النتائج الصحيحة.

بعد ذلك، اضرب الرقم 1 الموجود في الخلية C2 بالأرقام الموجودة في الصفوف السفلية.

عند نسخ الصيغة لأسفل، يظل موقع عمود الخلية C2 ثابتًا، بينما يتغيّر رقم الصف من C2 إلى C3 وC4 وهكذا. ونتيجةً لذلك، تصبح الصيغ في الخلايا السفلية «=$B4*C3» و«=$B5*C4» وما إلى ذلك، مما يؤدي إلى نتائج غير صحيحة.

لحل هذه المشكلة، غيّر "C2" إلى «C$2» للحفاظ على مرجع الصف ثابتًا عند سحب مقبض التعبئة لأسفل لملء الصيغ.

=$B3*C$2

يمكنك الآن سحب مقبض التعبئة التلقائية يمينًا أو لأسفل للحصول على جميع النتائج.

نقاط يجب تذكّرها

• ملخص مراجع الخلايا

  النوع	مثال	ملخص
  مرجع مطلق	$A$1	لا يتغيّر أبدًا عند نسخ الصيغة إلى خلايا أخرى
  مرجع نسبي	A1	يتغيّر كل من مرجعي الصف والعمود وفقًا للموقع النسبي عند نسخ الصيغة إلى خلايا أخرى
  مرجع مختلط	$A1/A$1	يتغيّر مرجع الصف عند نسخ الصيغة إلى خلايا أخرى بينما يبقى مرجع العمود ثابتًا / أو يتغيّر مرجع العمود عند نسخ الصيغة إلى خلايا أخرى بينما يبقى مرجع الصف ثابتًا؛

• بشكل عام، لا تتغيّر المراجع المطلقة أبدًا عند نقل الصيغة. ومع ذلك، تُعدَّل هذه المراجع تلقائيًا عند إدراج صف أو عمود أو حذفهما من أعلى الورقة أو يسارها. فعلى سبيل المثال، إذا كانت الصيغة «= $A$1 + 1»، وقمت بإدراج صف في أعلى الورقة، فستصبح الصيغة تلقائيًا «= $A$2 + 1».

  

• يمكنك استخدام مفتاح "F4" للتبديل بسلاسة بين المراجع النسبية، والمطلقة، والمختلطة.