الدرس 15: المرجع النسبي والمطلق في Excel
مقدمة
تحدثنا عن إنشاء صيغ معقدة في Excel ، وإنشاء صيغ معقدة باستخدام ترتيب العمليات في Excel ، وما إلى ذلك.
من خلال إنشاء ونسخ الصيغ ذات المراجع النسبية في Excel ، لدينا خلايا مرجعية في أوراق عمل Excel ومراجع مطلقة في Excel ، وما إلى ذلك.
في ما يلي ، سيكون لدينا تفسيرات ضرورية حول المراجع النسبية والمطلقة كذلك حول الخلايا المرجعية في أوراق عمل Excel
المرجع النسبي والمطلق في Excel
عموما هناك نوعان من مراجع الخلايا: النسبية والمطلقة
تتصرف المراجع النسبية والمطلقة بشكل مختلف عند نسخها وتعبئتها في خلايا أخرى.
عند نسخ صيغة إلى خلية أخرى ، يتغير المرجع النسبي.
من ناحية أخرى ، تظل المراجع المطلقة ثابتة بغض النظر عن مكان نسخها.
المراجع النسبية في Excel
بشكل افتراضي ، تعتبر كافة مراجع الخلايا مراجع نسبية.
عند نسخها إلى خلايا متعددة ، فإنها تتغير بناءً على الموضع النسبي للصفوف والأعمدة.
على سبيل المثال ، إذا قمت بنسخ الصيغة = a1 + b1 من الصف 1 إلى الصف 2 ، فستكون الصيغة = a2 + b2.
تعتبر المراجع النسبية ملائمة كلما احتجت إلى تكرار نفس العمليات الحسابية عبر صفوف أو أعمدة متعددة.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
أيضا في المثال التالي ، نريد إنشاء معادلة تزيد سعر كل منتج بأقل مبلغ.
بدلاً من إنشاء صيغة جديدة لكل صف ، يمكننا إنشاء صيغة واحدة في الخلية d4.
ثم انسخه إلى سطور أخرى.
نحن نستخدم المصادر النسبية ، لذلك تحسب الصيغة القيمة الإجمالية لكل عنصر بشكل صحيح.
1. حدد الخلية التي تحتوي على الصيغة
في مثالنا ، نختار الخلية d4.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
2. أدخل الصيغة لحساب القيمة المطلوبة ، في مثالنا ، يتم كتابة = b4 * c4.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
3. أدخل Enter من لوحة المفاتيح
سيتم حساب الصيغة وسيتم عرض النتيجة في الخلية.
4. ضع fill handle في الزاوية اليمنى السفلية للخلية المرغوبة.
في مثالنا ، وضعنا fill handle للخلية d4.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
5. ضع fill handle على الخلايا التي تريد تعبئتها واسحبها.
في هذا المثال نختار الخلايا d5: d13.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
6. حرر الماوس ، يتم نسخ الصيغة إلى الخلايا المحددة ذات المراجع النسبية.
يتم عرض النتيجة في كل خلية.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
كذلك يمكنك الضغط نقرًا مزدوجًا فوق الخلايا المعبأة للتحقق من دقتها في الصيغ.
يجب أن تكون المراجع النسبية مختلفة لكل خلية ، بناءً على صفها.
إنشاء ونسخ الصيغ بمرجع نسبي في Excel
المراجع المطلقة في Excel
أيضا قد تكون هناك أوقات لا تريد فيها تغيير مرجع الخلية عند نسخه إلى خلايا أخرى.
على عكس النسبية ، لا تتغير المراجع المطلقة عند نسخها أو تعبئتها.
يمكنك استخدام مرجع مطلق لجعل الصف أو العمود ثابتًا.
يتم تحديد مرجع مطلق في صيغة عن طريق إضافة علامة الدولار ($).
يمكن أن يسبق مرجع العمود أو مرجع الصف أو كليهما.
المراجع المطلقة في Excel
عادة ما تستخدم التنسيق $ A $ 2 عند إنشاء الصيغ التي تحتوي على مراجع مطلقة
يتم استخدام التنسيقين الآخرين بشكل أقل كثيرًا.
عند كتابة صيغة ، يمكنك الضغط على مفتاح f4 على لوحة المفاتيح للتبديل بين مراجع الخلايا النسبية والمطلقة.
هذه طريقة سهلة لوضع مرجع مطلق بسرعة
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
في المثال أدناه ، نستخدم الخلية e2 (التي تحتوي على معدل الضريبة 7.5?) لحساب ضريبة المبيعات لكل عنصر في العمود d .
للتأكد من أن مرجع معدل الضريبة ثابت ، حتى عند نسخ الصيغة وتعبئتها في خلايا أخرى – نحتاج إلى جعل الخلية $ E $ 2 مرجعًا مطلقًا.
1. حدد الخلية التي تحتوي على الصيغة
في مثالنا ، نختار الخلية d4.
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
2. أدخل الصيغة لحساب القيمة المطلوبة ، في مثالنا ، نكتب = (B4 * C4) * $ E $ 2 ، ونجعل $ E $ 2 مرجعًا مطلقًا.
إنشاء ونسخ الصيغة مع المرجع النسبي والمطلق في Excel
3. اضغط enter من لوحة المفاتيح
سيتم حساب الصيغة وسيتم عرض النتيجة في الخلية.
4. ضع fill handle في الزاوية اليمنى السفلية للخلية المرغوبة.
في مثالنا ، وضعنا fill handle للخلية d4.
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
5. ضع واسحب fill handle في الخلية التي تريد تعبئتها
(الخلايا d5: d13 في مثالنا).
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
6. حرر الماوس: يتم نسخ الصيغة بمرجع مطلق للخلايا المحددة ويتم حساب القيم الموجودة في كل خلية.
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
يمكنك الضغط نقرًا مزدوجًا فوق الخلايا المعبأة للتحقق من دقتها في الصيغ.
يجب أن يكون المرجع المطلق هو نفسه لكل خلية ، بينما تكون المراجع الأخرى لصف الخلية نفسه.
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
أيضا تأكد من تضمين علامة الدولار ($) كلما قمت بإنشاء مراجع مطلقة في خلايا مختلفة.
تم حذف إشارات الدولار في المثال أدناه
وقد تسبب هذا في قيام Excel بتفسيره كمرجع نسبي.
ينتج أيضًا عن نتيجة غير صحيحة عند النسخ إلى خلايا أخرى.
إنشاء ونسخ الصيغة مع المرجع المطلق في Excel
استخدام مراجع الخلايا مع أوراق عمل متعددة في Excel
أيضا يتيح لك Excel الرجوع إلى أي خلية في أي ورقة ، مما قد يكون مفيدًا بشكل خاص.
إذا كنت تريد إحالة قيمة محددة من ورقة إلى أخرى
للقيام بذلك ، تحتاج فقط إلى بدء مرجع الخلية باسم ورقة العمل متبوعًا بعلامة تعجب (!)
على سبيل المثال ، إذا كنت تريد الإشارة إلى الخلية a1 في sheet1 ، فإن مرجع الخلية هو sheet1! A1.
لاحظ أنه إذا كان اسم علامة التبويب يحتوي على مسافة.
يجب وضع علامات الاقتباس (“) حول الاسم.
على سبيل المثال ، إذا أردت الإشارة إلى الخلية a1 في ورقة تسمى july Budget ، فسيكون مرجع الخلية هو july Budget “! A1”.
الخلايا المرجعية في أوراق عمل Excel
حسنًا ، لقد وصلنا إلى الخلايا المرجعية في أوراق عمل Excel ، لذلك لدينا بعض النصائح حول الخلايا المرجعية في أوراق عمل Excel:
في المثال التالي ، سنشير إلى خلية ذات قيمة محسوبة بين ورقتين.
هذا يسمح لنا باستخدام نفس القيمة على صفحتين مختلفتين دون إعادة كتابة الصيغة أو نسخ البيانات.
1. أيضا قم بتحبدد الخلية التي تريد الرجوع إليها وأضفها إلى قائمة ورقة العمل الخاصة بك.
في مثالنا ، نريد الإشارة إلى الخلية e14 في ورقة العمل menu Order .
الخلايا المرجعية في أوراق عمل Excel
2. اذهب إلى الورقة المرغوبة ، في مثالنا ، نختار ورقة العمل catering Invoice .
الخلايا المرجعية في أوراق عمل Excel
3. حدد موقع الخلية التي تريد أن تظهر فيها القيمة وحددها
في مثالنا ، نختار الخلية c4.
الخلايا المرجعية في أوراق عمل Excel
4. أدخل علامة المساواة (=) واسم الورقة ثم علامة التعجب (!) وعنوان الخلية
في مثالنا ، ‘ll type =’ Menu Order ‘! E14
الخلايا المرجعية في أوراق عمل Excel
5. أدخل Enter من لوحة المفاتيح
سيتم عرض قيمة الخلية المرجعية
الآن ، إذا تغيرت قيمة الخلية e14 في ورقة العمل menu Order ، فسيتم تحديثها تلقائيًا في الورقة catering Invoice .
الخلايا المرجعية في أوراق عمل Excel
إذا قمت بإعادة تسمية ورقة ، فسيتم تحديث مرجع الخلية تلقائيًا ليعكس اسم الورقة الجديد.
إذا لم تدخل اسم الورقة بشكل صحيح ، #REF
يظهر الخطأ في الخلية.
في المثال أدناه ، قمنا بتغيير اسم ورقة العمل
لتحرير الخطأ أو تجاهله أو التحقق منه ، اضغط فوق الزر error بجوار الخلية وحدد خيارًا من القائمة.
الخلايا المرجعية في أوراق عمل Excel
ونهاية الخلايا المرجعية في أوراق عمل Excel
مثال تطبيقي
اضغط فوق علامة التبويب paper Goods في أسفل يسار المصنف.
في الخلية d4 ، أدخل صيغة تزيد سعر الوحدة في b4 والكمية في c4 ومعدل الضريبة في e2.
تأكد من استخدام مرجع الخلية المطلق لمعدل الضريبة ، حيث ستكون كل خلية هي نفسها.
استخدم fill handle لنسخ الصيغ التي أنشأتها للخلايا d5: d12.
قم بتغيير معدل الضريبة في الخلية e2 إلى 6.5?
لاحظ أنه سيتم تحديث جميع الخلايا الخاصة بك.
عند الانتهاء من التمرين ، يجب أن يبدو المصنف كما يلي:
مثال تطبيقي
اضغط فوق علامة التبويب invoice Catering .
احذف القيمة الموجودة في الخلية c5 واستبدلها بمصدر للتكلفة الإجمالية للبضائع ورق
ملاحظة: تكلفة paper Goods موجودة في الخلية e13 في ورقة العمل paper Goods .
استخدم الخطوات المذكورة أعلاه لتحديد حسابات ضريبة المبيعات لكل عنصر في صفحة تحديد القائمة
يجب تحديث التكلفة الإجمالية في الخلية e14
بعد ذلك ،
في الخلية c4 من ورقة العمل catering Invoice ، قم بإنشاء مرجع خلية إلى الإجمالي الذي حسبته للتو.
عند الانتهاء ، يجب أن تبدو فاتورتك catering Invoice كما يلي: d5: d12… .. قم بتغيير
معدل الضريبة في cell e2 إلى 6.5?
لاحظ أنه سيتم تحديث جميع الخلايا الخاصة بك.
أيضا عند الانتهاء ، يجب أن يبدو المصنف الخاص بك كما يلي:
خاتمة الدرس
لقد تعلمنا كيفية إنشاء ونسخ الصيغ بمرجع نسبي في Excel ، وخلايا مرجعية في أوراق عمل Excel ومرجع نسبي
بعد المراجع النسبية والمطلقة و الخلايا المرجعية في أوراق عمل Excel ، نذهب إلى الدالات.