كيفية البحث عن المراجع الدائرية في Excel

عند العمل مع Microsoft Excel ، قد تصادف أحيانًا ما يُعرف بالمرجع الدائري. هذا عندما تشير صيغة في خلية إلى نفسها ، مما يتسبب في حلقة لا نهاية لها في عملية الحساب.

للعثور على مراجع دائرية في Excel ، استخدم أداة "تدقيق الأخطاء" ضمن علامة التبويب "الصيغ" لتحديد الخلايا ذات التبعية الدائرية.

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

جدول المحتويات

• ما هي المراجع الدائرية
• نافذة رسالة خطأ المرجع الدائري
• كيفية البحث عن مراجع دائرية في Excel
• كيفية البحث عن مراجع دائرية باستخدام شريط معلومات Excel
• كيفية البحث عن المراجع الدائرية المخفية في Excel
  • كيفية استخدام أداة Trace Dependent
  • أداة تتبع السوابق
• 2 طرق كيفية إزالة المراجع الدائرية في Excel
  • 2. استخدم الصيغ البديلة
• 5 نصائح لمنع المراجع الدائرية
  • 1. كن حذرًا عند تحديد النطاقات
  • 2. تخطيط الصيغ الخاصة بك بعناية
  • 3. استخدم الصيغ البديلة
  • 4. قسّم الصيغ المعقدة إلى أجزاء أصغر
  • 5. إصلاح المشاكل كما تذهب
• كيفية تمكين الحساب التكراري ولماذا الإزعاج؟

ما هي المراجع الدائرية

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

هذا أسهل في الفهم بمثال بسيط.

افترض أن لديك رقمين في الخليتين A1 و B1. تريد استخدام صيغة في الخلية A3 تجمع الرقمين معًا. يجب أن تبدو الصيغة في الخلية A3 كما يلي:

= A1 + B1

ومع ذلك ، إذا ارتكبت خطأ وقمت بتضمين C1 في العمليات الحسابية ، فلديك الآن مرجع معاد:

= A1 + B1 + C1

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

لحسن الحظ ، تم تصميم Excel للتعامل مع مثل هذه المواقف من خلال عرض رسالة تحذير عند اكتشاف مرجع دائري.

ومع ذلك ، في بعض الحالات ، قد تواجه مرجعًا معادًا دون تلقي أي تنبيه. لذلك ، من المهم معرفة كيفية العثور على هذه المشكلات وحلها يدويًا.

نافذة رسالة خطأ المرجع الدائري

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

هذا هو النص التوضيحي:

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

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

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

في هذه الحالة ، تحتاج إلى معرفة كيفية العثور على مراجع دائرية في Excel بعد رفض مربع التحذير.

كيفية البحث عن مراجع دائرية في Excel

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

تُظهر هذه الصورة الموقع داخل مجموعة تدقيق الصيغة.

الآن أنت تعرف كيفية العثور على الأداة ، إليك الخطوات التفصيلية لاستخدامها:

1. انتقل إلى علامة التبويب الصيغ.

1. انقر فوق القائمة المنسدلة تدقيق الأخطاء في مجموعة تدقيق الصيغة.

2. حدد "مراجع دائرية" من القائمة المنسدلة.

3. انقر فوق الارتباط للانتقال بالمؤشر إلى الخلية الأولى التي بها مشكلة.

توضح الصورة أدناه أن الأداة قد عثرت على خطأ دائري في الخلية G1. الخلية المعروضة هي ارتباط تشعبي.

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

بمجرد حل المرجع الدائري الأول ، يمكنك الرجوع إلى أداة التحقق من الأخطاء للعثور على المرجع التالي على الورقة.

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

إذا لم يكن هناك المزيد من المشكلات الدائرية ، فسيظهر عنصر قائمة "المراجع الدائرية" في القائمة المنسدلة باللون الرمادي. ليس لديك المزيد من العمل للقيام به.

كيفية البحث عن مراجع دائرية باستخدام شريط معلومات Excel

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

عند وجود مشكلة واحدة أو أكثر في ورقة العمل ، يعرض شريط الحالة موقع مرجع خلية مشكلة واحدة.

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

تظهر لك هذه الصورة أين ترى الشاشة.

كيفية البحث عن المراجع الدائرية المخفية في Excel

المراجع الدائرية المخفية في Excel هي مراجع دائرية لا يتم اكتشافها على الفور بواسطة ميزات الخطأ المضمنة. بمعنى آخر ، لا يعرض Excel التحذير القياسي.

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

يحتوي Excel على أداتين تساعدك في تتبع العلاقات والتحقيق في هذه المشكلات:

1. أداة Trace Dependents

2. أداة تتبع السوابق

كيفية استخدام أداة Trace Dependent

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

الأداة متاحة في قسم تدقيق الصيغة في علامة التبويب الصيغ.

لاستخدام الأداة ، اتبع الخطوات التالية:

1. انقر فوق الخلية أو الخلايا التي تريد تتبع التابعين لها.

2. انتقل إلى علامة التبويب "الصيغ" في شريط Excel.

3. انقر فوق الزر "تتبع التابعين" في مجموعة "تدقيق الصيغة".

4. راجع سهم التتبع أو الأسهم التي تظهر التبعيات.

يعرض Excel الأسهم من قيمة الخلية النشطة إلى أي خلايا تعتمد عليها. يوضح السهم الأزرق في الصورة أدناه أن الخلية D6 لها تبعية على الخلية B2.

أداة تتبع السوابق

السوابق هي عكس المعالين. تتيح لك أداة تتبع السابقات اختيار خلية بها صيغة ورؤية جميع الخلايا التي تعتمد عليها ، أي السابقات.

الأداة متاحة في قسم تدقيق الصيغة في علامة التبويب الصيغ.

لاستخدام الأداة ، اتبع الخطوات التالية:

1. انقر فوق الخلية التي تحتوي على الصيغة التي تريد التحقيق فيها.

2. انتقل إلى علامة التبويب الصيغة في شريط Excel.

3. انقر فوق الزر "تتبع السابقات" في مجموعة "تدقيق الصيغة".

4. راجع أسهم التتبع التي يعرضها Excel.

توضح الأسهم الزرقاء في الصورة أدناه أن الخلية D6 تعتمد على نطاق الخلايا A2: B5.

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

2 طرق كيفية إزالة المراجع الدائرية في Excel

هناك طريقتان رئيسيتان لحل المراجع الدائرية:

• كسر المرجع الدائري

• باستخدام الصيغ البديلة

دعونا نشرح كلا الاتجاهين.

1. كسر المرجع الدائري

يعد كسر المرجع الدائري في Excel طريقة فعالة لحل المشكلة. يتطلب هذا تحديد الخلايا المتضمنة في المرجع الدائري وتعديل الصيغ وفقًا لذلك.

للقيام بذلك ، اتبع الخطوات الموضحة مسبقًا للعثور على عنوان الخلية.

يجب عليك بعد ذلك فحص الصيغة في شريط الصيغة. ابحث عن مرجع للخلية نفسها.

في مثالنا الأول للمرجع الدائري ، تضمنت صيغة الإضافة في الخلية C1 مرجعًا للخلية نفسها:

= A1 + B1 + C1

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

2. استخدم الصيغ البديلة

بعض الأسباب الأكثر شيوعًا للمراجع الدائرية هي عند جمع أو حساب متوسط ​​الخلايا. الخطأ هو تضمين خلية النتائج في الصيغة.

في مثالنا السابق ، استخدمت الصيغة عوامل إضافية لحساب مجموع نطاق من الخلايا. الطريقة البديلة هي استخدام وظيفة SUM Excel.

لإصلاح المثال ، يمكنك استبدال الصيغة بـ:

= SUM (A1: B1)

وبالمثل ، يمكنك متوسط ​​النطاق بصيغة مثل = (A1 + B1) / 2. ومع ذلك ، من غير المرجح أن تؤدي وظيفة AVERAGE إلى حدوث خطأ.

= متوسط ​​(A1: B1)

5 نصائح لمنع المراجع الدائرية

لمنع المراجع الدائرية في Microsoft Excel ، من الضروري أن يكون لديك فهم واضح لكيفية تفاعل الصيغ الخاصة بك مع بعضها البعض في ورقة Excel الخاصة بك.

من خلال تحديد العلاقات بين الخلايا وإدراك كيفية عمل هذه الاتصالات معًا ، يمكنك تجنب إنشاء صيغ دائرية.

فيما يلي أفضل خمس نصائح لمساعدتك في منع المراجع الدائرية.

1. كن حذرًا عند تحديد النطاقات

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

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

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

2. تخطيط الصيغ الخاصة بك بعناية

قبل إدخال الصيغ ، فكر في المنطق الكامن وراء حساباتك وكيف ينبغي أن تتدفق عبر الخلايا.

يمكنك تخطيط العمليات الحسابية مسبقًا باستخدام أدوات الرسم التخطيطي أو السبورة البيضاء.

يمكن أن يساعدك هذا في تحديد أي حلقات محتملة قد تؤدي إلى مراجع دائرية.

3. استخدم الصيغ البديلة

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

ضع في اعتبارك استخدام وظائف بديلة توفر وظائف مماثلة دون المخاطرة بالمراجع الدائرية.

4. قسّم الصيغ المعقدة إلى أجزاء أصغر

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

لا يؤدي ذلك إلى تسهيل فهم المصنف والحفاظ عليه فحسب ، بل يقلل أيضًا من فرص تقديم مرجع معاد.

5. إصلاح المشاكل كما تذهب

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

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

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

كيفية تمكين الحساب التكراري ولماذا الإزعاج؟

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

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

تعمل كل حلقة على تحسين نتائج عملية حسابية حتى يتم الوصول إلى حد معين أو هدف معين.

في هذه الحالات ، من الضروري تمكين الحسابات التكرارية في إعدادات Excel للسماح بالمراجع الدائرية المقصودة. هذا يسمح للبرنامج بتكرار العمليات الحسابية لعدد محدد من المرات ، متقاربًا إلى حل.

اتبع هذه الخطوات لعمل مرجع دائري في ملف Excel الخاص بك:

1. انقر فوق علامة التبويب ملف.

2. اشر على الخيارات.

3. اختر فئة الصيغ.

4. ضمن قسم "خيارات الحساب" ، حدد مربع الاختيار "تمكين الحساب التكراري".

5. غيّر الرقم في مربع الحد الأقصى للتكرار أو اترك الرقم الافتراضي 100.

6. استخدم مربع التغيير الأقصى لتقليل التكرارات وفقًا لمتطلباتك.