الروابط في Excel - مطلقة ونسبية ومختلطة. أخطاء عند العمل مع الارتباطات النسبية في Excel

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

1. الروابط النسبية. تستخدم لعمليات حسابية بسيطة. نسخ صيغة يستلزم تغيير الإحداثيات.
2. روابط مطلقة. إذا كنت بحاجة إلى إجراء حسابات أكثر تعقيدًا ، فإن هذا الخيار مناسب. لإصلاح استخدام الرمز “$”. مثال: $ A $ 1.
3. روابط مختلطة. يستخدم هذا النوع من العنونة في العمليات الحسابية عندما يكون من الضروري إصلاح عمود أو سطر بشكل منفصل. مثال: $ A1 أو A $ 1.

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

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

هذه مجموعة من الأحرف التي تحدد موقع الخلية. تتم كتابة الروابط الموجودة في البرنامج تلقائيًا مع العنونة النسبية. على سبيل المثال: A1، A2، B1، B2. يؤدي الانتقال إلى صف أو عمود مختلف إلى تغيير الأحرف في الصيغة. على سبيل المثال ، وضع البداية A1. يؤدي التحرك أفقيًا إلى تغيير الحرف إلى B1 و C1 و D1 وما إلى ذلك. وبنفس الطريقة ، تحدث التغييرات عند التحرك على طول خط عمودي ، وفي هذه الحالة فقط يتغير الرقم - A2 ، A3 ، A4 ، إلخ. إذا كان من الضروري التكرار حساب من نفس النوع في خلية مجاورة ، يتم إجراء عملية حسابية باستخدام مرجع نسبي. لاستخدام هذه الميزة ، اتبع بعض الخطوات:

1. بمجرد إدخال البيانات في الخلية ، حرك المؤشر وانقر بالماوس. يشير التمييز بمستطيل أخضر إلى تنشيط الخلية والاستعداد لمزيد من العمل.
2. بالضغط على مجموعة المفاتيح Ctrl + C ، نقوم بنسخ المحتويات إلى الحافظة.
3. نقوم بتنشيط الخلية التي تريد نقل البيانات إليها أو صيغة مكتوبة مسبقًا.
4. بالضغط على المجموعة Ctrl + V نقوم بنقل البيانات المحفوظة في حافظة النظام.

نصيحة إختصاصية! لإجراء نفس النوع من العمليات الحسابية في الجدول ، استخدم الاختراق الحياتي. حدد الخلية التي تحتوي على الصيغة التي تم إدخالها مسبقًا. قم بتمرير المؤشر فوق مربع صغير يظهر في الزاوية اليمنى السفلية ، مع الاستمرار في الضغط على زر الماوس الأيسر ، واسحب إلى الصف السفلي أو العمود الأقصى ، اعتمادًا على الإجراء الذي تم تنفيذه. من خلال تحرير زر الماوس ، سيتم إجراء الحساب تلقائيًا. هذه الأداة تسمى علامة التعبئة التلقائية.

مثال الارتباط النسبي

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

ترتيب الإجراءات:

1. يوضح المثال أنه تم استخدام العمودين B و C لملء كمية البضائع المباعة وسعرها. وفقًا لذلك ، لكتابة الصيغة والحصول على الإجابة ، حدد العمود D. تبدو الصيغة كما يلي: = ب2 *C

انتبه! لتسهيل عملية كتابة الصيغة ، استخدم القليل من الحيلة. ضع علامة "=" ، وانقر على كمية البضائع المباعة ، واضبط علامة "*" وانقر على سعر المنتج. ستتم كتابة الصيغة بعد علامة التساوي تلقائيًا.

2. للإجابة النهائية ، اضغط على "أدخل". بعد ذلك ، تحتاج إلى حساب المبلغ الإجمالي للربح المستلم من أنواع المنتجات الأخرى. حسنًا ، إذا لم يكن عدد الخطوط كبيرًا ، فيمكن إجراء جميع التلاعبات يدويًا. لملء عدد كبير من الصفوف في نفس الوقت في Excel ، هناك وظيفة واحدة مفيدة تجعل من الممكن نقل الصيغة إلى خلايا أخرى.
3. حرك المؤشر فوق الزاوية اليمنى السفلية للمستطيل باستخدام الصيغة أو النتيجة النهائية. يعمل ظهور التقاطع الأسود كإشارة إلى إمكانية سحب المؤشر لأسفل. وبالتالي ، يتم إجراء حساب تلقائي للربح المستلم لكل منتج على حدة.
4. من خلال تحرير زر الماوس المضغوط ، نحصل على النتائج الصحيحة في جميع الأسطر.

من خلال النقر فوق الخلية D3 ، يمكنك ملاحظة أنه تم تغيير إحداثيات الخلية تلقائيًا ، وتبدو الآن كما يلي: =ب3 *C3. ويترتب على ذلك أن الروابط كانت نسبية.

الأخطاء المحتملة عند العمل مع الروابط ذات الصلة

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

1. قم بإنشاء جدول واملأ: أ - اسم المنتج ؛ ب - الكمية المباعة ؛ ج - التكلفة D هو المبلغ المستلم. افترض أن هناك 11 عنصرًا فقط في التشكيلة. لذلك ، مع الأخذ في الاعتبار وصف الأعمدة ، يتم ملء 12 سطراً والمبلغ الإجمالي للربح هو D
2. انقر فوق الخلية E2 وأدخل =D2 /D13.
3. بعد الضغط على زر "Enter" ، يظهر معامل الحصة النسبية لمبيعات العنصر الأول.
4. قم بتمديد العمود لأسفل وانتظر النتيجة. ومع ذلك ، يعطي النظام الخطأ "# DIV / 0!"

سبب الخطأ هو استخدام مرجع نسبي للحسابات. نتيجة لنسخ الصيغة ، تتغير الإحداثيات. أي ، بالنسبة لـ E3 ، ستبدو الصيغة هكذا =D3 /D13. نظرًا لعدم ملء الخلية D13 ونظريًا لها قيمة صفرية ، سيعطي البرنامج خطأً بالمعلومات التي تفيد بأن القسمة على الصفر أمر مستحيل.

المهم! لتصحيح الخطأ ، من الضروري كتابة الصيغة بطريقة يتم فيها إصلاح إحداثيات D13. العنونة النسبية ليس لها مثل هذه الوظيفة. للقيام بذلك ، هناك نوع آخر من الروابط - مطلق. 

كيف تصنع رابطًا مطلقًا في Excel

بفضل استخدام الرمز $ ، أصبح من الممكن إصلاح إحداثيات الخلية. كيف يعمل هذا ، سننظر إلى أبعد من ذلك. نظرًا لأن البرنامج يستخدم العنونة النسبية افتراضيًا ، وفقًا لذلك ، لجعلها مطلقة ، ستحتاج إلى تنفيذ عدد من الإجراءات. دعنا نحلل حل الخطأ "كيفية إيجاد المعامل من بيع عدة أصناف من البضائع" ، وإجراء الحساب باستخدام العنونة المطلقة:

1. انقر فوق E2 وأدخل إحداثيات الرابط =D2 /D13. نظرًا لأن الارتباط نسبي ، يجب تعيين رمز لإصلاح البيانات.
2. إصلاح إحداثيات الخلية D لتنفيذ هذا الإجراء ، اسبق الحرف الذي يشير إلى العمود ورقم الصف بوضع علامة "$".

نصيحة إختصاصية! لتسهيل مهمة الإدخال ، يكفي تنشيط الخلية لتحرير الصيغة والضغط على المفتاح F4 عدة مرات. حتى تحصل على قيم مرضية. الصيغة الصحيحة هي كما يلي: = D2 / $ D $ 13.

3. اضغط على زر "دخول". نتيجة للإجراءات التي تم تنفيذها ، يجب أن تظهر النتيجة الصحيحة.
4. اسحب العلامة إلى الخلاصة لنسخ الصيغة.

نظرًا لاستخدام العنونة المطلقة في العمليات الحسابية ، ستكون النتائج النهائية في الصفوف المتبقية صحيحة.

كيفية وضع رابط مختلط في Excel

بالنسبة لحسابات المعادلات ، لا يتم استخدام المراجع النسبية والمطلقة فحسب ، بل يتم استخدام المراجع المختلطة أيضًا. السمة المميزة لها هي أنها تصلح أحد الإحداثيات.

• على سبيل المثال ، لتغيير موضع سطر ما ، يجب عليك كتابة علامة $ أمام تعيين الحرف.
• على العكس من ذلك ، إذا تمت كتابة علامة الدولار بعد تعيين الحرف ، فإن المؤشرات الموجودة في السطر ستظل دون تغيير.

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

1. للحصول على حسابات دقيقة ، أدخل = D1 / $ D $ 3 واضغط على "دخول". يعطي البرنامج الإجابة الدقيقة.
2. لنقل الصيغة إلى الخلايا التالية أسفل العمود والحصول على نتائج دقيقة ، اسحب المقبض إلى الخلية السفلية.
3. نتيجة لذلك ، سيعطي البرنامج الحسابات الصحيحة.

انتباه! إذا قمت بتعيين علامة $ أمام الحرف ، فسيقوم Excel بإعطاء الخطأ "# DIV / 0!" ، مما يعني أنه لا يمكن تنفيذ هذه العملية.

عنونة "SuperAbsolute"

في النهاية ، دعنا نلقي نظرة على مثال آخر للرابط المطلق - معالجة "SuperAbsolute". ما هي مميزاته واختلافاته. خذ الرقم التقريبي 30 وأدخله في الخلية B2. سيكون هذا الرقم هو الرقم الرئيسي ، فمن الضروري القيام بسلسلة من الإجراءات معه ، على سبيل المثال ، رفعه إلى قوة.

1. للتنفيذ الصحيح لجميع الإجراءات ، أدخل الصيغة التالية في العمود C: = $ B $ 2 ^ $ D2. في العمود D نقوم بإدخال قيمة الدرجات.

2. بعد الضغط على زر "Enter" وتفعيل الصيغة ، نقوم بتمديد العلامة لأسفل العمود.
3. نحصل على النتائج الصحيحة.

خلاصة القول هي أن جميع الإجراءات التي تم تنفيذها تشير إلى خلية واحدة ثابتة B2 ، وبالتالي:

• لن يؤدي نسخ صيغة من الخلية C3 إلى الخلية E3 أو F3 أو H3 إلى تغيير النتيجة. سيبقى دون تغيير - 900.
• إذا كنت بحاجة إلى إدراج عمود جديد ، فستتغير إحداثيات الخلية التي تحتوي على الصيغة ، ولكن النتيجة ستظل دون تغيير أيضًا.

هذه هي خصوصية رابط "SuperAbsolute": إذا كنت بحاجة إلى نقل ، فلن تتغير النتيجة. ومع ذلك ، هناك حالات يتم فيها إدراج البيانات من مصادر خارجية. وبالتالي ، يتم إزاحة الأعمدة إلى الجانب ، ويتم تعيين البيانات بالطريقة القديمة في العمود B2. ما يحدث في هذه الحالة؟ عند الخلط ، تتغير الصيغة وفقًا للإجراء المنفذ ، أي أنها لن تشير إلى B2 بعد الآن ، بل إلى C2. ولكن منذ أن تم الإدخال في B2 ، فإن النتيجة النهائية ستكون خاطئة.

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

هذا يطرح السؤال: هل من الممكن تعديل الصيغة من الخلية C2 بحيث يتم جمع الرقم الأصلي من الخلية B ، على الرغم من إدراج أعمدة البيانات الجديدة؟ للتأكد من أن التغييرات في الجدول لا تؤثر على تحديد الإجمالي عند تثبيت البيانات من مصادر خارجية ، يجب عليك تنفيذ الخطوات التالية:

1. بدلاً من إحداثيات الخلية B2 ، أدخل المؤشرات التالية: = غير مباشر ("B2"). نتيجة لذلك ، بعد تحريك تركيبة الصياغة ستبدو كما يلي: = INDIRECT (“B2”) ^ $ E2.
2. بفضل هذه الوظيفة ، يشير الارتباط دائمًا إلى المربع الذي يحتوي على الإحداثيات B2 ، بغض النظر عما إذا كانت الأعمدة قد تمت إضافتها أو إزالتها من الجدول.

يجب أن يكون مفهوماً أن الخلية التي لا تحتوي على أي بيانات تعرض دائمًا القيمة "0".

وفي الختام

بفضل استخدام ثلاثة أنواع من الارتباطات الموصوفة ، تظهر الكثير من الفرص التي تسهل التعامل مع العمليات الحسابية في Excel. لذلك ، قبل البدء في العمل مع الصيغ ، اقرأ أولاً الروابط وقواعد تثبيتها.