عند الإشارة إلى خلية في صيغة في 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 لتحويل المرجع النسبي إلى مطلق

1. انقر نقرًا مزدوجًا فوق الخلية التي تحتوي على الصيغة للدخول إلى وضع التحرير ؛
2. ضع المؤشر على مرجع الخلية الذي تريد جعله مطلقًا ؛
3. صحافة F4 المفتاح على لوحة المفاتيح لتبديل أنواع المراجع حتى يتم إضافة علامات الدولار قبل مراجع العمود والصف ؛
4. صحافة أدخل مفتاح للخروج من وضع التحرير وتطبيق التغييرات.

يمكن للمفتاح 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. نظرًا لأن "Lisa: لا يمكن العثور عليها في النطاق A5: B9 ، تُرجع الصيغة خطأ.

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

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

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

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

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

حدد خلايا الصيغة التي تريد جعل مراجع الخلايا مطلقة ، انقر فوق كوتولس > المزيد (FX) > تحويل المراجع. ثم اختر ملف إلى المطلق الخيار وانقر Ok or التقديم. الآن تم تحويل جميع مراجع الخلايا للصيغ المحددة إلى مطلقة.

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

بصرف النظر عن المرجع المطلق ، هناك نوعان آخران من أنواع المراجع: المرجع النسبي والمرجع المختلط.

مرجع نسبي هو نوع المرجع الافتراضي في 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

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

أشياء للذكرى

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

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

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

  

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