الدرس رقم 6: مرجع الخلية النسبي والمطلق في الاكسيل
سوف نركز في الدرس السادس من دروس الإكسيل على موضوع هام وهو كيف يمكن ان نتحكم في مرجع الخلية التي تحتوي على بيانات بأن تكون مطلقة أاو ثابتة، علماً بأن مرجع الخلية هو العنوان الذي يشير إلى الخلية والمكون من حرف لاتيني يشير إلى عنوان العمود ورقم يشير إلى عنوان الصف وتجد مرجع الخلية على اقصي اليسار بجانب شريط الصيغ كما في الشكل.
لاحظ ان مرجع الخلية يشير إلى الخلية B16 وهي الخلية المحددة والتي سيقوم الاكسيل بادخال البيانات عليها وهذا المرجع يمكن تحويله من مرجع نسبي الى مرجع مطلق كما يمكن ان نطلق عليه اسم نتعامل معه بدلاً من التعامل مع الرموز.
وللتعرف على اهمية هذا الموضوع دعنا نفترض أنك تدير شركة واردت ان تحسب رواتب الموظفين العاملين لديك حسب ساعات العمل مع العلم بان قيمة ساعة العمل لموظفين واحدة فهنا سوف تقوم بكتابة اسماء الموظفين العاملين في الشركة في ورقة عمل اكسيل ثم سوف تدون عدد الساعات التي عملها كل موظف وستطلب من الاكسيل ان يقوم بحساب قيمة الراتب المستحق لكل عامل.
الموضوع سهل ستطبق معادلة رياضية لحساب الراتب لكل موظف والتي تساوي عدد الساعات في تكلفة الساعة ثم تطبقها على كل الموظفين ولكن فرضاً انك تريد تعديل تكلفة الساعة، وهنا تكمن المشكلة وخصوصاً لو كان عدد الموظفين لديك كبير ستضطر الى تعديل كل الخلايا وربما حدث خطأ غير مقصود، لذا من الافضل ان نخبر الاكسيل ان قيمة سعر الساعة هو 10 دينار مثلاً في خلية محددة في الجدول ونطلب منه ان يعتبرها من الثوابت وندخلها في المعادلة وعند تغير قيمة سعر الساعة ما عليك سوى تعديل خلية واحدة التي تحتوي على قيمة سعر الساعة من 10 دينار إلى 12 دينار مثلاً ولعمل ذلك يجب ان نعرف الفرق بين مفهوم مرجع الخلية النسبي ومرجع الخلية المطلق.
مرجع الخلية النسبي
مرجع الخلية هو مجموعة من الإحداثيات التي تعرف موقع خلية أو خلايا على ورقة العمل. يؤدي مرجع الخلية وظيفته في برنامج Excel من خلال توجيه البرنامج إلى تنفيذ المهام على الخلايا المعنية من خلال الصيغ الرياضية أو التحديد.
باستخدام مرجع الخلية يمكن استخدام البيانات من أية منطقة على ورقة العمل، كذلك استخدام بيانات الخلية الواحدة في أكثر من صيغة رياضية. كما أن استخدام مرجع الخلية بدلاً من التعامل مع قيمة الخلية في الصيغة يجعل الصيغة تحدث ناتجها بتحديث قيمة الخلية أتوماتيكياً.
هنالك نوعان من مرجع الخلية هما مرجع الخلية النسبي relative reference ومرجع الخلية المطلق absolute reference.
في الفصول السابقة تعاملنا مع مرجع الخلية النسبي، حيث أن المرجع النسبي للخلية يدل Excel أين يجد خلية أخرى بالنسبة للخلية التي تحتوي على الصيغة الرياضية. ولهذا السبب كان مرجع خلية الصيغة يتغير عند السحب للتعبئة التلقائية لتنفيذ الصيغة على عدة خلايا كما هو موضح في الشكل التالي.
مرجع الخلية المطلق
مرجع الخلية المطلق يدل Excel أين يجد الخلية في موقع محدد على ورقة العمل. في هذه الحالة فإن مرجع الخلية المطلق سيبقى ثابتاً خلال عملية السحب للتعبئة التلقائية لتكرار تنفيذ معادلة رياضية على عدة خلايا.
للتمييز بين مرجع الخلية النسبي ومرجع الخلية المطلق نستخدم إشارة الدولار ($) في مرجع الخلية المطلق قبل رمز العمود وكذلك قبل رقم الصف.
يمكن لبرنامج Excel تحويل مرجع الخلية النسبي إلى مطلق بالضغط على مفتاح F4 عندما يكون مؤشر الإدراج في شريط الصيغة فعالاً.
مثال
(1) قم بطباعة البيانات في الجدول التالي:
(2) احسب راتب العامل الأول محمد في الخلية C4 وذلك من خلال المعادلة التالية:
الراتب = ساعات العمل * أجرة الساعة
أجرة الساعة هي ثابت لا يتغير لكل العمال ولذلك تم إدخالها في خلية واحدة B1. لذلك قبل تنفيذ المعادلة يجب تغيير مرجع الخلية B1 المطلق إلى نسبي لتصبح $B$1، باستخدام المفتاح F4 كما تعلمنا في الخطوة السابقة.
(3) اضغط على مفتاح Enter لتنفيذ المعادلة كالمعتاد.
باستخدام خاصية التعبئة التلقائية، اسحب مربع التعبئة لتنفيذ المعادلة على بقية الأشخاص لتحديد رواتبهم عن عدد الساعات التي أنجزوها في العمل. ستحصل على النتيجة الموضحة في الشكل أدناه. 
لاحظ أن الصيغة في كل خلية لحساب الراتب استخدمت القيمة الثابتة نفسها بدون أن تتغير مع عملية التعبئة التلقائية، وستكون النتيجة خاطئة إذا لم يتم تعريف الخلية B1 في الصيغة بأنها ذات مرجع مطلق.
في المثال السابق قم بتعديل أجرة ساعة العمل لتكون 15 أو 20 ولاحظ تأثير ذلك على البيانات في خلايا الراتب.
تسمية الخلايا
تسمية الخلية باسم ذو دلالة بحيث يكون سهل التذكر، للإشارة إلى خلية أو مجموعة من الخلايا أو قيمة أو صيغة.
مزايا وفوائد استخدام تسمية الخلايا:
(1) الصيغ التي تستخدم الأسماء يسهل قراءتها وتذكرها أكثر من الصيغ التي تستعمل مراجع الخلايا.
(2) إذا قمت بتغيير بنية ورقة العمل، فبإمكانك تحديث المراجع في مكان واحد، فيتم تحديث جميع الصيغ التي تستعمل نفس الاسم تلقائياً.
(3) عندما تعرف أسماء لبعض الخلايا فإنه بإمكانك استخدامها على كافة أوراق العمل للمستند.
(4) يمكنك أن تجعل Excel ينشئ تلقائياً أسماء للخلايا مستنداً على عناوين الصفوف أو الأعمدة، أو أن تقوم بنفسك بإدخال الأسماء للخلايا أو الصيغ.
خطوات تسمية الخلايا
الطريقة الأولى:
(1) حدد الخلية أو مجموعة الخلايا التي ترغب في تسميتها باسم معين.
(2) اضغط على صندوق الاسم في شريط الصيغ.
(3) اطبع الاسم ثم اضغط على المفتاح Enter.
الطريقة الثانية:
(1) حدد الخلية أو مجموعة الخلايا التي ترغب في تسميتها باسم معين.
(2) اختر من قائمة “إدراج” الأمر “اسم” “Name” ثم الأمر “تعريف” “Define”.
(3) اطبع الاسم في مربع الحوار المخصص ثم اضغط على المفتاح إضافة.
(4) اضغط على المفتاح موافق لإنهاء مربع الحوار والعودة إلى ورقة العمل.
مثال
سنستخدم المثال السابق لتوضيح خطوات استخدام تسمية الخلايا لإيجاد المطلوب دون استخدام طريقة مرجع الخلية المطلق.
(1) سنقوم بتسمية الخلية B1 باسم أجرة الساعة والخلايا B4:B11 باسم ساعات العمل.
باتباع الطريقة الأولى للتسمية:
(1) حدد الخلية B4.
(2) اضغط على صندوق الاسم في شريط الصيغ واطبع أجرة_الساعة.
(3) اضغط على المفتاح Enter.
(5) حدد الخلية C4 واطبع الصيغة باستخدام الأسماء (=أجرة_الساعة*ساعات_العمل) كما في الخطوات التالية:
اطبع =
اختر من قائمة “إدراج” الأمر “اسم” ثم الأمر “لصق”.
اختر الاسم أجرة_الساعة من مربع الحوار ثم اضغط على مفتاح موافق.
اطبع *
اختر من قائمة “إدراج” الأمر “اسم” ثم الأمر “لصق”.
اختر الاسم ساعات_العمل من مربع الحوار ثم اضغط على مفتاح موافق.
اضغط على المفتاح Enter.
(6) باستخدام التعبئة التلقائية يمكن السحب لتطبيق الصيغة على باقي الخلايا.
تمرين
ادخل البيانات الموضحة في الجدول التالي
(1) احسب قيمة الضريبة وصافي الدخل لكل موظف.
(2) ماذا يحدث إذا تغيرت الضريبة لتصبح 40%؟
تعلمنا في هذا الدرس المواضيع التالية:
(1) الفرق بين مصطلحي مرجع الخلية المطلق ومرجع الخلية النسبي.
(2) كتابة الصيغ الرياضية التي تحتوي على مراجع خلايا مطلقة.
(3) كيفية تسمية الخلايا ومزايا عملية التسمية.
