الرئيسية / الدوال والمعادلات / المراجع المختلطة – أنواع المراجع في اكسل – ج3/3
أنواع المراجع في اكسل
أنواع المراجع في اكسل

المراجع المختلطة – أنواع المراجع في اكسل – ج3/3

السلام عليكم, تعتبر المراجع المختلطة Mixed References نوعاً مكملاً ومتمماً للنوعي المراجع النسبية Relative References والمطلقة Absolute References الذين قمنا باستعراضهما في الجزئين الأول والثاني من هذه المقالة كونها لاتقدم اي جديد وانما هي خليط مابين هذين النوعين كما سنرى لاحقاً.

 

ماهي المراجع المختلطة ومتى نحتاجها:

اصبحنا نعلم بأن المراجع النسبية Relative References تُستخدم عندما نحتاج ان يقوم اكسل Excel بنسخ معادلة ما مع الحفاظ على العلاقة المكانية للخلايا المُشار إليها من خلال تلك المعادلة. اما المراجع المطلقة Absolute References فتستخدم لتثبيت عناوين الخلايا المُشار إليها في المعادلة في حال نسخها. لكن السؤال الذي يطرح نفسه, كيف سنتصرف في حال أردنا كتابة معادلة ما تقوم بتطبيق اسلوب المراجع النسبية Relative References  في حال تم النسخ الى خلية من نفس السطر الأصلي في حين انها تقوم بتطبيق اسلوب المراجع المطلقة Absolute References في حال تم النسخ إلى خلية من غير سطر؟!! حل هكذا معضلة يكون عادةً باتباع اسلوب المراجع المختلطة والذي هو محور هذه المقالة.

المثال الأول: يوجد لدينا في العمود B مجموعة من المصاريف المتغيرة Variable Expenses خلال السنوات الخمس المقبلة (ارقام السنوات متاحة في العمود A), في حين أن السطر 8 يحوي مجموعة من المصاريف الثابتة Fixed Expenses لكل مشروع Project من المشاريع الثلاثة لدينا. الفكرة كالتالي, نريد احتساب مجموع المصاريف الكلية (الثابتة والمتغيرة) لكل مشروع خلال كل سنة من سنوات المشروع الخمسة.

مثال توضيحي عن المراجع المختلطة - 1
مثال توضيحي عن المراجع المختلطة – 1

 

لحل هذا السؤال, سنكتب في الخلية C10 معادلة مجموع التكاليف للمشروع الأول في سنته الأولى قبل تطبيقها على باقي المشاريع والسنوات كمايلي:

  • مبلغ التكاليف الثابتة للمشروع الأول: طالما أن مبلغ التكاليف الثابتة لجميع المشاريع خلال السنوات الخمس متساوية, لذلك قمنا باتباع اسلوب المراجع المطلقة للاشارة إليها في الخلية C8 لذلك نكتب عنوان هذه الخلية محاطاً بعلامتي دولار 8$C$ (قد تتسائل لماذا C8 وليس D8, السبب ان هاتين الخليتين مدموجتين في المثال اعلاه, لذلك قد يبدو لك ان مبلغ التكاليف موجود في الخلية D8 لكنه فعلياً مخزن في الخلية C8. يمكنك تحميل ملف المثال من اسفل المقال لمزيد من التوضيح). وبالتالي وبسبب اتباعنا لاسلوب المراجع المطلقة عند الاشارة للخلية C8, فإن هذه الخلية ستتكرر معنا لاحقاً عند نسخ هذه المعادلة الى باقي المشاريع والسنوات وهو تماماً مانبحث عنه.
  • مبلغ التكاليف المتغيرة للمشروع الأول, السنة الأولى: لاحظ أن التكاليف المتغيرة للسنة الأولى هي ذاتها للمشاريع الثلاثة. أي أننا وفي حالة نسخ المعادلة افقيّاً ضمن السطر رقم 10, نحتاج من اكسل Excel أن يقوم بالابقاء (تثبيت) عنوان التكاليف المتغيرة للسنة الأولى (الخلية B10). لكن ومن ناحية اخرى, مبلغ التكاليف السنوية متغير من سنة إلى أخرى. أي أننا وفي حالة نسخ المعادلة عموديّاً ضمن العمود C فإن مبلغ التكاليف المتغيرة سيتغير حسب السنة. لاحظ المشكلة التي نريد حلها هنا, اتباع اسلوب المراجع المطلقة سيحل معضلة الحركة افقيّاً لكنه لن يفيدنا اثناء الحركة عمودياً (اقصد بالحركة هنا نسخ المعادلة), والعكس ينطبق على اتباع اسلوب المراجع النسبية والتي ستفيد في حل مشكلة الحركة العمودية ولكن ليس الأفقية. إذاً ما الحل؟
    الحل يكون باتباع اسلوب المراجع المختلطة والذي سيسمح لنا بدمج كِلا الاسلوبين السابقين بما يناسب حاجتنا. نحن نريد من اكسل Excel ان يسمح بتغير عنوان التكاليف المتغيرة عند الحركة عامودياً وفي ذات الوقت, نحتاج من اكسل Excel أن يثبّت عنوان التكاليف المتغيرة عند الحركة افقياً. إذاً نشير الى خلية التكاليف المتغيرة للسنة الأولى بـB10B$ والذي يعني,

    • عند الحركة افقيّاً, ثبّت الاشارة الى عمود التكاليف المتغيرة والذي هو في حالتنا هنا العمود B, اي اننا وفي حال نسخ المعادلة الى اي خلية جديدة, دائماً العمود المشار إليه سيكون العمود B.
    • عند الحركة عموديّاً, حرِّك دائماً السطر ليتناسب مع العلاقة المكانيّة مابين عنوان المعادلة الأصلية وعنوان التكاليف المتغيرة للسنة المناسبة. أي أننا وفي حال نسخنا معادلتنا الى السطر 11, فإن اكسل Excel لن يثبت عنوان السطر وانما سيقوم بتغييره من 10 الى 11 وهكذا.

من المهم التنبه أننا قمنا باضافة علامة $ قبل عنوان العمود (الجزء الحرفي من عنوان الخلية) لتثبيته, وأننا قمنا بازالة العلامة $ قبل عنوان السطر (الجزء الرقمي من عنوان الخلية) لتحريره.

المعادلة الناتجة, ستكون كالتالي,

سنقوم الآن بنسخ المعادلة افقياً خلال السطر 10,

مثال توضيحي عن المراجع المختلطة - 1
مثال توضيحي عن المراجع المختلطة – 1

 

لاحظ أن اكسل Excel قام بتثبيت الاشارة الى الخلية B10 رقم اننا قمنا بتحريك (نسخ)المعادلة افقياً والسبب اننا ثبتنا هذه الخلية من جهة العمود. لذلك نرى ان العمود B ثابت ولم يتغير. اما السطر فنحن لم نغيره وبالتالي اكسل Excel كذلك لم يقم بتغييره. (لايهمني في النقاش الحالي جزئية التكاليف الثابتة لانها كُتبت باتباع اسلوب المراجع المطلقة وانما تركزي على جزئية التكاليف المتغيرة كونها كتبت باتباع اسلوب المراجع المختلطة)

الآن, لننسخ هذا السطر (تكاليف اول سنة) للأسطر الأخرى (السنوات الأخرى). النتيجة ستكون كالتالي,

مثال توضيحي عن المراجع المختلطة - 1
مثال توضيحي عن المراجع المختلطة – 1

 

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

 

المثال الثاني: في المثال التالي, سنعكس الآية, بمعنى اننا سنثبت التكاليف المتغيرة وسنجعل قيم التكاليف الثابتة غير متساوية بين المشاريع الثلاثة.

مثال توضيحي عن المراجع المختلطة - 2
مثال توضيحي عن المراجع المختلطة – 2

 

سنقوم الآن بكتابة معادلة مجموع التكاليف كالتالي,

  • مبلغ التكاليف الثابتة للمشروع الأول: طالما أن مبلغ التكاليف الثابتة متغيرة لكل مشروع, اذاً عند التحريك (النسخ) الأفقي, يجب علينا تغيير عنوان مبلغ التكلفة لان المشروع نفسه سيتغير اما عند التحريك (النسخ) عمودياً, فإن مبلغ التكلفة الثابتة سيبقى كما هو دون تغيير لأن مبلغ التكلفة ثابت خلال السنوات المختلفة للمشروع الواحد. لذلك يجب كتابة جزئية التكاليف الثابتة كالتالي,
    C$20 والذي يعني,

    • عند الحركة افقيّاً, حرِّك (غيّر) دائماً عنوان العمود ليتناسب مع العلاقة المكانيّة مابين عنوان المعادلة الأصلية وعنوان التكاليف الثابتة المُشار إليها.
    • عند الحركة عموديّاً, ثبّت دائماً السطر رقم 20 وبالتالي وفي حال نسخ المعادلة الى سطر جديد, دائماً ستبق الاشارة الى السطر رقم 20.
  • مبلغ التكاليف المتغيرة للمشروع الأول, السنة الأولى: طالما ان التكاليف المتغيرة هي ثابتة خلال السنوات لجميع المشاريع لدينا, إذاً يمكننا مجازاً الاشارة إليها باتباع اسلوب المراجع المطلقة.

من المهم التنبه أننا قمنا باضافة علامة $ قبل عنوان السطر (الجزء الرقمي من عنوان الخلية) لتثبيته, وأننا قمنا بازالة العلامة $ قبل عنوان العمود (الجزء الحرفي من عنوان الخلية) لتحريره.

المعادلة الناتجة ستكون,

سنقوم الآن بنسخ المعادلة افقياً خلال السطر 20,

مثال توضيحي عن المراجع المختلطة - 2
مثال توضيحي عن المراجع المختلطة – 2

 

لاحظ أن اكسل Excel لم يقم بتثبيت الاشارة الى الخلية C20 عند التحريك (النسخ) افقياً كوننا ازلنا العلامة $ قبل عنوان العمود C. (لايهمني في النقاش الحالي جزئية التكاليف المتغيرة لانها كُتبت باتباع اسلوب المراجع المطلقة وانما تركزي على جزئية التكاليف الثابتة كونها كُتبت باتباع اسلوب المراجع المختلطة)

الآن, لننسخ هذا السطر (تكاليف اول سنة) للأسطر الأخرى (السنوات الأخرى). النتيجة ستكون كالتالي,

مثال توضيحي عن المراجع المختلطة - 2
مثال توضيحي عن المراجع المختلطة – 2

 

لاحظ الآن التأثير الكامل لاسلوب المراجع المختلطة. لاحظ أن جميع المعادلات تشير الى السطر 20 لانه كان مثبّتاً في المعادلة الأصلية (مسبوقاً بعلامة $) اما العمود فقد تغيّر وكأنه مكتوب باتباع اسلوب المراجع النسبية.

 

المثال الثالث: سنقوم في هذا المثال بدمج المثالين الأول والثاني, اني اننا سنجعل كل المصاريف (القابتة والمتغيرة) ذات قيم متغيرة وبالتالي يجب علينا استخدام اسلوب المراجع المختلطة للاشارة للمصاريف الثابتة والمتغيرة معاً كلٌ حسب مايناسب موقعه من المعادلة واتجاه النسخ المتوقع.

مثال توضيحي عن المراجع المختلطة - 2
مثال توضيحي عن المراجع المختلطة – 3

 

لنبدأ,

  • التكاليف المتغيرة Variable Expenses: سنشير إليها باتباع اسلوب المراجع المختلطة تماماً كما مرّ معنا خلال مناقشتنا للمثال الأول. أي أننا سنقوم بتثبيت العمود B مع تحرير السطر لذلك سنكتب هذه الجزئية كالتالي,
     
  • التكاليف الثابتة Fixed Expenses: سنشير إليها باتباع اسلوب المراجع المختلطة تماماً كما مرّ معنا خلال مناقشتنا للمثال الثاني. أي أننا سنقوم بتثبيت السطر 31 مع تحرير العمود لذلك سنكتب هذه الجزئية كالتالي,
     

المعادلة النهائية ستكون,

لننسخ المعادلة الآن خلال السطر الأول من الجدول (للسنة الأولى),

مثال توضيحي عن المراجع المختلطة - 3
مثال توضيحي عن المراجع المختلطة – 3

 

والآن سنقوم بالنسخ لباقي الأسطر (باقي  السنوات),

مثال توضيحي عن المراجع المختلطة - 3
مثال توضيحي عن المراجع المختلطة – 3

 

هذا المثال يوضّح بشكل متكامل اهميّة وميزة اسلوب المراجع المختلطة كونه يمنحنا امكانية الدمج بين الإسلوبين الرئيسيين (النسبي والمطلق) كما مرّ معنا سابقاً.

 

المُلخص النّهائي,

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

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

 

أما إذا أردت تثبيت فقط السطر مع تحرير العمود, إذاً ضع علامة الـ $ قبل عنوان السطر, (أو يمكنك بعد تعيين الخلية التي تريد الاشارة إليها, اضغط على الزر F4 مرّتين.)

المراجع المختلطة - تثبيت السطر فقط
المراجع المختلطة – تثبيت السطر فقط

 

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

المراجع المختلطة - تثبيت العمود فقط
المراجع المختلطة – تثبيت العمود فقط

 

أما اذا أردت عدم تثبيت اي جزء من عنوان الخلية (او الخلايا) في معادلتك, يمكنك كتابة العناوين بدون استخدام العلامة $ فيها (الضغط على الزر F4 أربع مرّات يزيل التثبيت كليّاً.)

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

 

أخيراً, يمكنك تحميل ملف المثال المستخدم في هذه المقالة من هنا.

عن Hussein.B

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

4 تعليقات

  1. جزاء الله كل خير … ومجهود عظيم

أضف تعليقاً

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