Excel 2019 (الجزء 14): مراجع الخلايا النسبية والمطلقة

في Excel ، هناك نوعان من مراجع الخلايا: نسبية ومطلقة. تتصرف المراجع النسبية والمطلقة بشكل مختلف عند نسخها وتعبئتها في خلايا أخرى. تتغير المراجع النسبية عند نسخ صيغة إلى خلية أخرى. ومن ناحية أخرى، تظل المراجع المطلقة دون تغيير بغض النظر عن مكان نسخها.

مرجع نسبي

بشكل افتراضي، كافة مراجع الخلايا هي مراجع نسبية. عند النسخ عبر خلايا متعددة، فإنها ستتغير بناءً على الموضع النسبي للصفوف والأعمدة. على سبيل المثال، إذا قمت بنسخ الصيغة =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 ، فسيكون مرجع الخلية الخاص بها هو الورقة1!A1.

لاحظ أنه إذا كان اسم الورقة يحتوي على مسافات، فسوف تحتاج إلى تضمين علامات الاقتباس ( '' ) حول الاسم. على سبيل المثال، إذا كنت تريد الإشارة إلى الخلية A1 في ورقة عمل باسم " ميزانية يوليو" ، فسيكون مرجع الخلية الخاص بها هو "ميزانية يوليو"!A1.

كيفية الرجوع إلى الخلايا في أوراق العمل

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

1. حدد موقع الخلية التي تريد الرجوع إليها ولاحظ صفحتها. على سبيل المثال، نريد الإشارة إلى الخلية E14 في ورقة عمل ترتيب القائمة .

2. انتقل إلى ورقة العمل المطلوبة. على سبيل المثال، سوف نختار جدول بيانات فاتورة تقديم الطعام .

3. حدد موقع الخلية التي تريد أن تظهر فيها القيمة وحددها. على سبيل المثال، سوف نختار الخلية C4.

4. أدخل علامة التساوي ( = )، واسم ورقة العمل متبوعًا بعلامة تعجب (!)، وعنوان الخلية. على سبيل المثال، أدخل ='ترتيب القائمة'!E14.

5. اضغط على Enter بلوحة المفاتيح. تظهر قيمة الخلية المشار إليها. الآن، إذا تغيرت قيمة الخلية E14 في ورقة عمل ترتيب القائمة ، فسيتم تحديثها تلقائيًا في ورقة عمل فاتورة تقديم الطعام.

إذا قمت بإعادة تسمية ورقة العمل الخاصة بك لاحقًا، فسيتم تحديث مرجع الخلية تلقائيًا ليعكس اسم ورقة العمل الجديدة.

إذا قمت بإدخال اسم الورقة بشكل خاطئ، فسيظهر الخطأ #REF! سيظهر خطأ في الخلية. في المثال أدناه، تم كتابة اسم الورقة بشكل خاطئ. لتحرير الأخطاء أو تجاهلها أو التحقق منها، انقر فوق الزر "خطأ" بجوار الخلية واختر خيارًا من القائمة.