دورة اكسل من الصفر حتى الاحتراف 2021 المحاضرة الخامسة مراجع الخلية المرجع النسبي و المرجع المطلق
دورة اكسل من الصفر حتى الاحتراف 2021
المحاضرة الخامسة
فهم مراجع الخلية
بينما يمكنك إنشاء صيغ بسيطة في Excelباستخدام الأرقام فغالبًا ما ستستخدم عناوين الخلايا لإنشاء صيغة. هذا هو المعروف باسم جعل الخلية المرجعية.سيضمن استخدام مراجع الخلية أن تكون الصيغ الخاصة بك دقيقة دائما لأنه يمكنك تغيير قيمة الخلايا المرجعية بدون الحاجة إلى إعادة كتابة الصيغة.
يضيف الخلية A3 قيم الخلايا A1و A2 عن طريق إنشاء مراجع الخلية: صيغة A في Excel باستخدام مراجع الخلية عند الضغط على Enter ،تقوم الصيغة بحساب وعرض الإجابة في الخلية :A3 تقوم الصيغة تلقائيا بحساب النتيجة. إذا تغيرت القيم في الخلايا المرجعية ،فستتم إعادة حساب الصيغة تلقائيًا: من خلال الجمع بين عامل التشغيل الحسابى ومراجع الخلايا ،يمكنك إنشاء مجموعة متنوعة من الصيغ البسيطة في .Excelيمكن أن تتضمن الصيغ أيضا مجموعة من المراجع والخلايا
انشاء صيغة:
سنحدد الخلية .D12
✓ اكتب علامة يساوي =. الحظ كيف يظهر في كل من الخلية وشريط الصيغة.
✓ اكتب عنوان خلية الخلية التي تريد الإشارة إليها أولا الصيغة الخلية D10في مثالنا. سيظهر حد أزرق حول الخلية المرجعية.
✓ إدخال مرجع الخلية D10
في مثالنا ، سنقوم بكتابة علامة
الجمع+.
✓ اضغط على Enterعلى لوحة المفاتيح. سيتم حساب الصيغة ، وسيتم عرض القيمة في الخلية. إذا قمت بتحديد الخلية مرة أخرى ، لاحظ أن الخلية تعرض النتيجة ، بينما يعرض شريط الصيغة الصيغة.
✓ اضغط على Enterلإكمال الصيغة وعرض النتيجة
تعديل القيم بمراجع الخلايا
الميزة الحقيقية لمراجع الخلايا هي أنها تسمح لك بتحديث البيانات في ورقة العمل الخاصة بك دون الحاجة إلى إعادة كتابة الصيغ. في المثال أدناه ، عّدلنا قيمة الخلية B1من 1200 دولار إلى 1800 دولار. ستقوم الصيغة في B3تلقائياً بإعادة حساب وعرض القيمة الجديدة في الخلية .B3
نسخ الصيغ باستخدام مقبض التعبئة
✓ يمكن أيضا نسخ الصيغ إلى الخلايا المجاورة باستخدام مقبض التعبئة،مما يوفر الكثير
من الوقت والجهد إذا كنت بحاجة إلى إجراء نفس الحساب عدة مرات في ورقة العمل. مقبض التعبئة هو المربع الصغير في الزاوية السفلية اليسرى من الخلية المحددة. .
✓ حدد الخلية التي تحتوي على الصيغة التي تريد نسخها.انقر واسحب مقبض التعبئة عبر الخلايا التي تريد تعبئتها.
بعد تحرير الماوس ، سيتم نسخ الصيغة إلى الخلايا المحددة.
تحرير صيغة
✓ في بعض الأحيان قد ترغب في تعديل صيغة موجودة.
✓ في المثال أدناه ، أدخلنا عنوان خلية غير صحيح في صيغتنا ،لذلك سنحتاج إلى تصحيحه.
✓ حدد الخلية التي تحتوي على الصيغة التي تريد تحريرها Editفي مثالنا ، سنحدد الخلية D12 12 To Edit A انقر فوق شريط الصيغة لتحرير الصيغة. يمكنك أيضا النقر نقرا مزدوجا فوق الخلية لعرض الصيغة وتحريرها مباشرة داخل الخلية.
سيظهر حد حول أي خلايا مرجعية. في مثالنا ، سنقوم بتغيير الجزء الأول من الصيغة إلى مرجع الخلية D10بدلا من الخلية صيغة تحرير عند الانتهاء ، اضغط على Enterعلى لوحة المفاتيح أو حدد الأمر Enterفي شريط الصيغة.
سيتم تحديث الصيغة ،وسيتم عرض القيمة الجديدة في الخلية.
إذا غيرت رأيك ، فيمكنك الضغط على مفتاح Escعلى لوحة المفاتيح أو النقرعلى أمر
"إلغاء الأمر"في شريط الصيغة لتجنب إجراء تغييرات على الصيغة عن طريق الخطأ
المرجع النسبي Reference Relative
عند الحديث عن المرجع النسبي Reference Relative فى Excel لابد أن نفهم أولا طبيعة العمليات الحسابية فى Excel فعندما نقوم بإجراء عملية حسابية معينة ولتكن جمع قيمة خليتين ونريد تطبيق الناتج على باقى الخلايا . نلاحظ أن Excel يقوم بإجراء العملية الحسابية على الخليتين المحددتين وإظهار ناتج العملية الحسابية وعند نسخ المعادلة نلاحظ أن Excel يقوم بالعملية الحسابية بنفس الإجراء السابق وبنفس ترتيب الخلايا التى تم فى المعادلة الأولى فإذا كان الرقمين لخليتين متجاورتين فإنه يقوم بجمع باقى الخلايا كل خليتين متجاورتين يقوم بجمعهم وإذا كانت الخلايا التى دخلت فى المعادلة الأولى خليتين عموديتين فإنه يقوم بجمع باقى الخلايا كل خليتين عموديتين وإظهار الناتج
المرجع النسبي Reference Relative
مثال: في المثال الخاص بعلامات الحللاب المطلوب تعميم الصيغة التي تمت كتابتها في الخلية D2على باقي الخلايا في العمود Dللوصول إلى ھذه النتيجة نتحرك بمؤشر الماوس فوق مقبض التعبئة )وھو المربع الصفير في أسفل يمين الخلية( حتى يتغير شكل الماوس إلى إشارة )+( كما بالشكل. نضغط بزر الماوس الأيسر ونستمر بالضغط ثم نسحب للأسفل بالقدر الذي نريد فنحصل على النتيجة
ھنا نرى كيف أن استخدام مراجع الخلايا النسبية في كتابة المعادلات قد سهل علينا بشكل كبير جدأ ادخال المعادلات في ورقة العمل؛ فعوضاً عن ادخال المعادلة في كل خلية في العمود D تم ادخال المعادلة مرة واحدة في الخلية الأولى ثم تعميمها على باقي الخلايا في العمود D
✓ مرجع الخلية النسبي ھو نوع المرجع الافتراضي في الاكسيل.
✓ عند نقل المعادلة )اجراء عملية قص ولصق( فإن المعادلة تنتقل كما ھي بدون ان تتغير مراجع الخلايا النسبية.
نسخ المعادالت بدون تعديل المرجع النسبي إذا كنت تريد نسخ معادلة الى خلية أخرى بدون أن تتغير مراجع الخلايا النسبية فاتبع الخطوات التالية:
1 .حدد الخلية التي تحتوي على المعادلة التي تريد نسخها
2 .اضغط بزر الماوس بداخل شريط الصيغة لتفعيله
3 .استخدم الماوس لتحديد المعادلة بداخل شريط الصيغة
4 .انسخ المعادلة المظللة
5 .اضغط على Esc .
6 .ألصق المعادلة في المكان الذي تريد
المرجع المطلق Reference Absolute
في مرجع الخلية المطلق يتم تثبيت موقع الخلية عن طريق تثبيت رقم الصف واسم العمود. يتم ھذا األمرعن طريق وضع إشارة )$( قبل اسم العمود لتثبيت العمود و)$( قبل رقم الصف لتثبيت الصف. وفيهذه الحالة ال يتغير مرجع الخلية عند نسخ المعادلة من مكان آلخر. يمكن كتابة اشارتي )$( بشكل يدوي أو عن طريق تحديد المرجع المطلوب )أو وضع مؤشر قبألو بعد المرجع( ومن ثم الضغط مرة واحدة على زر F4 7
مثال: في ھذا المثال لدينا جدول بأسماء
الموظفين الجديدة في العمود C بعد تطبيق زيادة على الراتب بمقدار النسبة الموجودة في الخلية B3 8
لحل ھذا المثال نبدأ من عند الموظف الأول في القائمة أحمد محمد محمود الخلية :C6 فندخل المعادلة التالية في (B6*(l+ B3= فنحصل على الراتب الخاص بالموظف المذكور بعد الزيادة.
لو عممنا المعادلة على باقي الخلايا في
العمود Cفسوف نحصل على النتيجة كما بالشكل لاحظ ھنا أن المعادلة المكتوبة في الخلية C6ھي معادلة سليمة آما باقي المعادالت في النطاق C13:C7 فهي خطأ؛ وذلك لأن مرجع الخلية المستخدم في المعادلة والذي يحتوي على نسبة العلاوة (B3 )ھو عبارة عن مرجع نسبي وبالتالي فعند تعميم المعادلة للأسفل تغير ھذا المرجع، بينما نحن نريد في ھذا المثال أن يبقى مرجع الخلية المحتوية على نسبة العلاوة ثابتآ عند تعميم المعادلة على باقي الخلايا.
لحل ھذه المشكلة نحول مرجع الخلية ( B3 )من مرجع نسبي إلى مطلق عن طريق التالي:
1 .نفتح المعادلة في الخلية C6للتعديل عن طريق النقر بزر الماوس الأيسر نقراً مزدوجآ عليها.
2 .نحدد مرجع الخلية المطلوب )B3 )أو نضع مؤشر قبله أو بعده ثم نضغط F4 مرة واحدة فنحصل على المعادلة التالية: (B6*(1+$B$3=
3-نعمم الصيغة على باقي الخلايا فنحصل على النتيجة السليمة كما بالشكل الحظ ھنا أن مرجع الخلية 3$B$بقي ثابتآ عند تعميم الصيغة للأسفل
تعليقات
إرسال تعليق