Excel 2016 - الدرس 15: مراجع الخلايا النسبية والمطلقة

• Excel 2016 (الجزء 12): تنسيق الصفحات وطباعة جداول البيانات
• Excel 2016 (الجزء 13): مقدمة إلى الصيغ
• Excel 2016 (الجزء 14): إنشاء صيغ معقدة

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

يرجى الانضمام إلى LuckyTemplates في هذه المقالة مع إرشادات حول مراجع الخلايا النسبية والمطلقة في Excel 2016 !

مراجع الخلايا النسبية والمطلقة في Excel

• المراجع النسبية
  • قم بإنشاء ونسخ صيغة تستخدم مراجع نسبية
• المراجع المطلقة
  • إنشاء ونسخ صيغة حسب المرجع المطلق
• استخدم مراجع الخلايا مع أوراق عمل متعددة
  • الخلايا المرجعية في أوراق العمل

شاهد الفيديو أدناه لمعرفة المزيد حول مراجع الخلايا:

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

بشكل افتراضي، كافة مراجع الخلايا هي مراجع نسبية. عند النسخ عبر خلايا متعددة، فإنها تتغير بناءً على الموضع النسبي للصفوف والأعمدة. على سبيل المثال، إذا قمت بنسخ الصيغة =A1+B1 من الصف 1 إلى الصف 2، تصبح الصيغة =A2+B2 . تعتبر المراجع النسبية ملائمة بشكل خاص عندما تحتاج إلى تكرار نفس العملية الحسابية عبر صفوف أو أعمدة متعددة.

قم بإنشاء ونسخ صيغة تستخدم مراجع نسبية

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

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 في Sheet1 ، فسيكون مرجع الخلية الخاص بها هو Sheet1!A1 .

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

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

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

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

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

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

4. اكتب علامة التساوي (=) واسم الجدول متبوعًا بعلامة تعجب (!) وعنوان الخلية. في المثال، سنكتب ='MenuOrder'!E14 .

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

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

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

الرجوع إلى بعض المزيد من المقالات:

• Excel 2016 (الجزء 3): كيفية إنشاء جداول بيانات جديدة وفتح جداول بيانات موجودة
• Excel 2016 (الجزء 4): كيفية تخزين جداول البيانات ومشاركتها
• Excel 2016 (الجزء 5): أساسيات الخلايا والنطاقات

استمتع!