كورس إكسيل Excel 2019 شرح كامل،
للمبتدئين من البداية للاحتراف
المرحلة
الثالثة: الصيغ والدوال في إكسيل Excel 2019
الدرس رقم [15]: مراجع الخلايا النسبية والمطلقة في إكسيل Excel 2019
هناك نوعان من مراجع الخلايا:
النسبية والمطلقة. تتصرف المراجع النسبية والمطلقة بشكل مختلف عند نسخها وتعبئتها
للخلايا الأخرى. تتغير المراجع النسبية عندما يتم نسخ صيغة إلى خلية أخرى. من
ناحية أخرى، تظل المراجع المطلقة ثابتة بغض النظر عن مكان نسخها.
المراجع
النسبية:
بشكل افتراضي، تعتبر جميع مراجع
الخلايا مراجع نسبية. عند نسخها عبر خلايا متعددة، فإنها تتغير بناءً على الموضع
النسبي للصفوف والأعمدة. على سبيل المثال، إذا قمت بنسخ الصيغة =A1+B1 من الصف 1 إلى الصف 2، فستصبح الصيغة =A2+B2. تكون المراجع النسبية ملائمة بشكل خاص
عندما تحتاج إلى تكرار نفس الحساب عبر صفوف أو أعمدة متعددة.
إنشاء ونسخ صيغة باستخدام مراجع نسبية:
في المثال التالي، نريد إنشاء صيغة
تضرب سعر كل صنف في الكمية. بدلاً من إنشاء صيغة جديدة لكل صف، يمكننا إنشاء صيغة
واحدة في الخلية D4 ثم
نسخها إلى الصفوف الأخرى. سنستخدم المراجع النسبية حتى تحسب الصيغة الإجمالي لكل صنف
بشكل صحيح.
(1) حدد الخلية التي ستحتوي على
الصيغة. في مثالنا، سنحدد الخلية D4.
(2) أدخل الصيغة لحساب القيمة
المطلوبة. في مثالنا، سنكتب =B4*C4.
(3) اضغط على Enter على لوحة المفاتيح. سيتم حساب الصيغة، وسيتم عرض النتيجة في
الخلية.
(4) حدد موقع مقبض التعبئة في
الزاوية اليمنى السفلية من الخلية المطلوبة. في مثالنا، سنقوم بتحديد مقبض التعبئة
للخلية D4.
(5) انقر واسحب مقبض التعبئة فوق
الخلايا التي تريد تعبئتها. في مثالنا، سنحدد الخلايا D5:D13.
(6) حرر الماوس. سيتم نسخ الصيغة إلى
الخلايا المحددة بمراجع نسبية، وتعرض النتيجة في كل خلية.
** يمكنك النقر نقرًا مزدوجًا فوق
الخلايا المعبأة للتحقق من دقة الصيغ الخاصة بها. يجب أن تكون مراجع الخلايا
النسبية مختلفة لكل خلية، اعتمادًا على صفوفها.
المراجع المطلقة:
قد يكون هناك وقت لا تريد تغيير مرجع
الخلية عند نسخه إلى خلايا أخرى. على عكس المراجع النسبية، لا تتغير المراجع
المطلقة عند نسخها أو تعبئتها. يمكنك استخدام مرجع مطلق للحفاظ على ثبات الصف و/
أو العمود.
يتم تعيين مرجع مطلق في صيغة بإضافة
علامة الدولار ($). يمكن أن يسبق مرجع العمود أو مرجع الصف أو كليهما.
** ستستخدم بشكل عام تنسيق $A$2 عند إنشاء الصيغ التي تحتوي على مراجع
مطلقة. يتم استخدام التنسيقين الآخرين بشكل أقل تكرارًا.
عند كتابة صيغة، يمكنك الضغط على
مفتاح F4 على لوحة المفاتيح للتبديل
بين مراجع الخلايا النسبية والمطلقة. هذه طريقة سهلة لإدراج مرجع مطلق بسرعة،
لإنشاء صيغة ونسخها باستخدام مراجع مطلقة:
في المثال أدناه، سنستخدم الخلية E2 (التي تحتوي على معدل الضريبة بنسبة 7.5٪)
لحساب ضريبة المبيعات لكل عنصر في العمود D. للتأكد من أن المرجع إلى معدل الضريبة يظل ثابتًا - حتى عندما
تكون الصيغة تم نسخها وتعبئتها إلى خلايا أخرى - سنحتاج إلى جعل الخلية $E$2 مرجعًا مطلقًا.
(1) حدد الخلية التي ستحتوي على
الصيغة. في مثالنا، سنحدد الخلية D4.
(2) أدخل الصيغة لحساب القيمة
المطلوبة. في مثالنا، سنكتب =B4*C4)*$E$2)،
جاعلًا $E$2 مرجعًا مطلقًا.
(3) اضغط على Enter على لوحة المفاتيح. سيتم حساب الصيغة، وستظهر النتيجة في الخلية.
(4) حدد موقع مقبض التعبئة في
الزاوية اليمنى السفلية من الخلية المطلوبة. في مثالنا، سنقوم بتحديد مقبض التعبئة
للخلية D4.
(5) انقر واسحب مقبض التعبئة فوق
الخلايا التي تريد ملؤها (الخلايا D5:D13 في مثالنا).
(6) حرر الماوس. سيتم نسخ الصيغة إلى
الخلايا المحددة بمرجع مطلق، وسيتم حساب القيم في كل خلية.
** يمكنك النقر نقرًا مزدوجًا فوق
الخلايا المعبأة للتحقق من دقة الصيغ الخاصة بها. يجب أن يكون المرجع المطلق هو
نفسه لكل خلية، بينما تكون المراجع الأخرى ذات صلة بصف الخلية.
** تأكد من تضمين علامة الدولار ($)
كلما كنت تقوم بعمل مرجع مطلق عبر خلايا متعددة. تم حذف علامات الدولار في المثال
أدناه. أدى ذلك إلى قيام Excel
بتفسيره كمرجع نسبي، مما ينتج عنه نتيجة غير صحيحة عند نسخه إلى خلايا أخرى.
استخدام
مراجع الخلايا مع أوراق عمل متعددة:
يسمح لك Excel بالإشارة إلى أي خلية في أي ورقة عمل، والتي يمكن أن تكون مفيدة
بشكل خاص إذا كنت تريد الرجوع إلى قيمة معينة من ورقة عمل إلى أخرى. للقيام بذلك،
ستحتاج ببساطة إلى بدء مرجع الخلية باسم ورقة العمل متبوعة بعلامة تعجب (!). على
سبيل المثال، إذا أردت الإشارة إلى الخلية A1 على الورقة 1، فسيكون مرجع الخلية الخاص بها هو (Sheet1!A1)
أي الورقة 1!A1.
** لاحظ أنه إذا كان اسم ورقة العمل
يحتوي على مسافة، فستحتاج إلى تضمين علامات اقتباس مفردة (' ') حول الاسم. على
سبيل المثال، إذا أردت الإشارة إلى الخلية A1 في ورقة عمل باسم "ميزانية يوليو"، فسيكون مرجع الخلية
هو "ميزانية يوليو"!A1.
الإشارة إلى الخلايا عبر أوراق العمل:
في المثال أدناه، سنشير إلى خلية ذات
قيمة محسوبة بين ورقتي عمل. سيسمح لنا هذا باستخدام نفس القيمة بالضبط في ورقتي
عمل مختلفتين دون إعادة كتابة الصيغة أو نسخ البيانات.
(1) حدد موقع الخلية التي تريد
الرجوع إليها، ولاحظ ورقة العمل الخاصة بها. في مثالنا، نريد الإشارة إلى الخلية E14 في ورقة عمل ترتيب القائمة.
(2) انتقل إلى ورقة العمل المطلوبة.
في مثالنا، سنحدد ورقة عمل فاتورة خدمات المطاعم.
(3) حدد موقع الخلية التي تريد أن
تظهر القيمة بها وحددها. في مثالنا، سنحدد الخلية C4.
(4) اكتب علامة يساوي (=)، واسم
الورقة متبوعة بعلامة تعجب (!)، وعنوان الخلية. في مثالنا، سنكتب ='Menu
Order'!E14
(5) اضغط على Enter على لوحة المفاتيح. ستظهر قيمة الخلية المرجعية. الآن، إذا تغيرت
قيمة الخلية E14 في ورقة عمل طلب القائمة،
فسيتم تحديثها تلقائيًا في ورقة عمل فاتورة تقديم الطعام.
** إذا قمت بإعادة تسمية ورقة العمل
الخاصة بك في وقت لاحق، فسيتم تحديث مرجع الخلية تلقائيًا ليعكس اسم ورقة العمل
الجديدة.
** إذا أدخلت اسم ورقة عمل
بشكل غير صحيح، #REF!
سيظهر خطأ في الخلية. في المثال أدناه، أخطأنا في كتابة اسم ورقة العمل. لتحرير
الخطأ أو تجاهله أو التحقيق فيه، انقر فوق الزر خطأ بجانب الخلية وحدد خيارًا من
القائمة.
العودة إلى صفحة الكورس:
(كورس إكسيل Excel 2019 شرح كامل، للمبتدئين من البداية للاحتراف) من هنا
الانتقال للدرس التالي:
(الدوال في إكسيل Excel 2019) من هنا
تعليقات
إرسال تعليق