الرئيسية / متفرقات / المراجع النسبية والمراجع المطلقة في اكسل:
المراجع (العناوين) النسبية والمطلقة
Relative & Absolute Reference

المراجع النسبية والمراجع المطلقة في اكسل:

السلام عليكم, من المهم جداً لكل مستخدم لبرنامج اكسل ان يعرف الفرق بين المراجع النسبية والمراجع المطلقة Relative Reference & Absolute Reference (او كما يسميها البعض, العناوين النسبية والعناوين المطلقة) وكيفية استخدام كل منها اثناء كتابة المعادلات والدوال المختلفة حفظاً لوقته ولحماية عمله من اي خطأ غير مقصود قد ينتج عن نسخ معادلة بشكل خاطئ دون الانتباه الى نوع المرجع المستخدم فيها. لنأخذ نظرة اكثر قرباً على كلٍّ من هذين النوعين لمعرفة الفرق بينهما,

المراجع النسبية Relative References:

لنفترض أن لديك ورقة عمل فيها ارقام في العمودين A و B وتريد ان تحسب مجموعهما في العمود C. في الخلية C2 سنقوم بكتابة معادلة الجمع (sum(A2,B2 أو A2+B2 (طبعاً مع كتابة اشارة =).

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

 

لتطبيق المعادلة الموجودة في الخلية C2 على باقي الخلايا في نفس العمود, نقوم بالضغط على المربع الأخضر الصغير في اسفل ويمين الخلية C2 ومن ثم السحب للاسفل حتى الخلية C9,

تجربة المراجع النسبية
تجربة المراجع النسبية

دعونا ننظر لنرى كيف استجاب اكسل لما قمنا به,

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

 

لاحظ ان اكسل قد فهم علينا وطبق ما أردنا منه تطبيقه. قمنا بكتابة أول معادلة بالاشارة الى الخلايا A2 و B2 ومن ثم قام اكسل بتطبيقها على باقي الخلايا في الأسفل. طبقها على A3 مع B3 والنتيجة في C3 كما طبقها على A4 مع B4 والنتيجة في C4 …

هذا هو جوهر المراجع (العناوين) النسبية, إذ أن اكسل يحفظ (بالنيابة عنا) العلاقة المكانية “اذا جاز لي التعبير” للخلايا المشمولة (المذكورة) في المعادلة أو الدالة (في المثال السابق الخلايا A2 و B2) بالنسبة للخلية الفعالة (الخلية التي كتبت فيها المعادلة – في مثالنا السابق, الخلية C2) ومن ثم يقوم بتطبيق ذات العلاقة المكانية في حال نسخ او سحب الخلية الفعالة الى موقع جديد (خلية اخرى). من مثالنا السابق, عندما كتبنا في الخلية C2 معادلة الجمع A2+B2, اكسل فهم المعادلة على الشكل التالي,

اجمع محتوى الخلية الثانية الى يسار الخلية الفعالة مع محتوى الخلية الاولى الى يسار الخلية الفعالة

 

لاحظ أن اكسل لم يستخدم عناوين الخلايا المذكورة في الجمع وإنما استخدم مكانهم المكاني (النسبي) من الخلية الفعالة (أو الخلية التي كُتبت فيها المعادلة). وبالتالي, عندما نسخنا معادلتنا من الخلية C2 الى الخلايا C3 الى C9, قام اكسل بتطبيق نفس العلاقة المكانية بغض النظر عن عناوين الخلايا.

يجب ملاحظة أن نسخ خلية ما تحوي معادلة ما سيؤدي باكسل الى تطبيق اسلوب المراجع النسبية لكن في حال قص (نقل) خلية تحوي معادلة ما, فإن اكسل سيقوم باستخدام اسلوب المراجع المطلقة.

 

المراجع المطلقة Absolute References:

في احيان كثيرة قد لاترغب ان يقوم اكسل باتباع المراجع النسبية كما في المثال التالي, لديك مجموعة قيم في العمود A وتريد ان تجمع في العمود C حاصل جمع العمود A مع القيمة الموجودة في الخلية B1,

تجربة المراجع المطلقة
تجربة المراجع المطلقة

 

لاحظ أن كتابة معادلة الجمع كما في مثالنا الأول ومن ثم سحبها وتطبيقها على الخلايا في الأسفل سيُنتج قيماً خاطئة لأن اكسل وبتطبيقه اسلوب المراجع النسبية, قام بتطبيق معادلة الجمع الأصلية باستخدام العلاقة المكانية للخليتين A2 و B1 بالنسبة للخلية الفعالة C2 (لاحظ أن نتيجة الخلية C3 هي 2, حاصل جمع الخلايا A2 و B2 بدل B1).

الحل البديل الأول يتمثل بكتابة الخلية يدوياً في جميع الأسطر كالتالي,

تجربة المراجع المطلقة
تجربة المراجع المطلقة

 

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

الحل الصحيح هو باستخدام المراجع المطلقة Absolute Reference حيث أن اكسل وبموجب هذه الطريقة يقوم بحفظ المعادلة /الدالة بالاعتماد على عنوان الخلايا المشمولة (المذكورة) في المعادلة وليس على العلاقة المكانية بالنسبة للخلية الفعالة كما رأينا في اسلوب المراجع النسبية. بكلام آخر وباعتماد مثالنا الحالي, لاحظ أننا وعند نسخنا المعادلة من الخلية C2, فإننا نحتاج من اكسل أن يشير باستمرار الى الخلية B1 بغض النظر عن المكان الجديد للمعادلة. يمكن لاكسل تنفيذ هكذا أمنية عن طريق اتباع اسلوب المراجع المطلقة.

 

نظرة أكثر قرباً على المراجع المطلقة:

عند نسخ أو تطبيق معادلة ما إلى موقع جديد, اكسل سيتوقع (غالباً) بأنك تعتمد اسلوب المراجع النسبية إلا إذا انت اخبرته صراحةً بأنك تستخدم اسلوب المراجع المطلقة وذلك عن طريق ادراج رمز التثبيت الخاص باسلوب المراجع المطلقة أو لندعوه اختصاراً رمز التثبيت $ (تحصل عليه بالضغط على المفتاح Shift مع رقم 4 في لوحة المفاتيح القياسية)

رمز التثبيت لاستخدام اسلوب المراجع المطلقة
رمز التثبيت لاستخدام اسلوب المراجع المطلقة

 

وذلك بكتابته قبل عنوان العمود و السطر للخلية المراد تثبيتها وفق اسلوب المراجع المطلقة. ففي مثالنا السابق, يجب كتابة الخلية B1 كالتالي 1$B$ ليقوم اكسل بتثبيتها حين نسخ معادلة الجمع من الخلية C2. يمكنك كذلك ادراج رمز التثبيت بشكل تلقائي عند كتابة عنوان الخلية المراد تثبيتها ومن ثم الضغط لمرة واحدة على المفتاح F4 من لوحة المفاتيح مباشرةً بعد كتابة عنوان الخلية المراد تثبيتها (أي اكتب B1 ثم قم بالضغط مرة واحدة على المفتاح F4 ليتم التثبيت).

استخدام المراجع المطلقة
استخدام المراجع المطلقة

 

 

يجب استخدام رمز التثبيت مع كل خلية مذكورة داخل المعادلة /الدالة المنوي نسخها لتحديد طريقة تثبيتها. الخلية (الخلايا) التي يتم اغفالها ضمن المعادلة ستتم معاملتها وفق اسلوب المراجع النسبية.

 

المراجع المختلطة Mixed-Reference Format:

 المميز في هذا الإسلوب من المراجع أنه يتيح لك تثبيت الخلية المراد تثبيتها تثبيتاً جزءياً, بمعنى أنه يمكنك تثبيت عنوان العمود فقط أو عنوان السطر فقط. مثلاً ليكن لديك جدول فيه قيم مبيعات مختلفة ويُراد منك احتساب قيمة الخصم لهذه المبيعات عند معدلات خصم مختلفة.

مثال لاسلوب المراجع المختلطة
مثال لاسلوب المراجع المختلطة

 

لنحلل المطلوب منا بعيداً عن اكسل, يجب علينا في العمود C, أن نحتسب قيمة الخصم لجميع قيم المبيعات المذكورة في العمود B وذلك وفق قيمة الخصم المذكورة في الخلية C1 أي أنه علينا أن نشير دوماً إلى الخلية C1 عند كتابة معادلات العمود C, في حين يجب أن نشير في العمود D إلى الخلية D1, وفي العمود E يجب أن نشير دوماً إلى الخلية E1 وهكذا حتى العمود G. لاحظ أن العامل المشترك في الخلايا المثبتة هو رقم السطر في حين أن العمود متغير (C1, D1, E1…. G1), أي أنه ولضمان احتساب قيم معدلات خصم صحيحة, علينا أن نثبت خلية معدل الخصم من جهة السطر فقط (C$1).

في حين أنه يجب علينا في السطر 2, أن نحتسب قيم الخصم المختلفة باختلاف معدلات الخصم المذكورة في السطر 1 وذلك بالنسبة لقيمة المبيعات المذكورة في الخلية B2, أي أنه علينا أن نشير دوماً الى الخلية B2 عند كتابة معادلات السطر 2, في حين يجب علينا أن نشير الى الخلية B3 عند كتابة معادلات السطر 3 وهكذا حتى السطر 6. لاحظ أن العامل المشترك في الخلايا المثبتة هو رمز العمود في حين أن السطر متغير (B2, B3, B4…. B6), أي أنه ولضمان احتساب قيم المبيعات الصحيحة, علينا أن نثبت خلية قيمة المبيعات من جهة العمود فقط (B.($B2

 اعتقد أن الحل اصبح مفهوماً من حيث الفكرة. لنأتي الى التطبيق, نكتب المعادلة التالية في الخلية C2,

تطبيق المراجع المختلطة
تطبيق المراجع المختلطة

 

نقوم الآن بنسخ المعادلة خلال السطر 2,

تطبيق المراجع المختلطة
تطبيق المراجع المختلطة

 

ومن ثم نقوم بالسحب لكامل السطر للأسفل حتى السطر 6,

تطبيق المراجع المختلطة
تطبيق المراجع المختلطة

 

لينتج لدينا جدول النتائج بدون اي خطأ حسابي. لاحظ أن كل خلية في الجدول تحوي معادلة صحيحة لها فقط وكأنك قد كتبتها يدوياً. هذه هي ميزة المراجع المختلطة في اكسل.

Mixed-References 4
تطبيق المراجع المختلطة

 

فهم طريقة كتابة المراجع المختلطة:

يمكن تثبيت اي خلية عن طريق كتابة الرمز $ قبل الجهة المُراد تثبيتها, أي ضعها قبل رقم السطر لتثبيت السطر, أو قبل رمز العمود لتثبيته أو قبلهما كلاهما لتثبيت الخلية بشكل مطلق أو أخيراً لاتذكر هذا الرمز كلياً كي تستخدم اسلوب المراجع النسبية.

المراجع في اكسل
المراجع في اكسل

 

وكذلك يمكنك التنقل بين انواع التثبيت المذكورة اعلاه اثناء كتابة المعادلة وذلك بمجرد ان تكتب خلية تريد تثبيتها, اضغط على المفتاح F4 لتتنقل بين الأنواع الاربعة للمراجع. في حال اردت التعديل على معادلة مكتوبة بأن تغير اسلوب المرجع المستخدم في جزء منها, فقط فعل وضع الكتابة / التعديل للمعادلة وضع مؤشر الكتابة عن الخلية المراد تغيير مرجعيتها ومن ثم قم بالتعديل يدوياً أو عن طريق الضغط المتعدد على المفتاح F4.

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

عن Hussein.B

مؤسس ومدير موقع viaexcel.com, اعمل في مجال الإئتمان, مهتم بمواضيع التدريب وتطوير الذات بالإضافة لحبي الشديد لبرنامج اكسل.

9 تعليقات

  1. جزاك الله خيرا

  2. شرح مستفيض بارك الله فيكم

  3. اسلام البسيونى

    شرح جميل استفدت كثيرا بعد معاناه مع فهم كيفية تطبيق المراجع المطلقه مشكور

  4. شكراً لكم، موقع أكثر من رائع!

أضف تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *