يعد "المرجع الدائري" أحد أكثر تحذيرات الأخطاء شيوعًا التي يواجهها المستخدمون في Excel. يعاني آلاف المستخدمين من نفس المشكلة ، وتحدث عندما تشير الصيغة مرة أخرى إلى خليتها الخاصة بشكل مباشر أو غير مباشر ، مما يتسبب في حلقة لا نهائية من العمليات الحسابية.
على سبيل المثال ، لديك قيمتان في الخليتين B1 و B2. عندما يتم إدخال الصيغة = B1 + B2 في B2 ، فإنها تنشئ مرجعًا دائريًا ؛ تعيد الصيغة في B2 حساب نفسها بشكل متكرر لأنه في كل مرة يتم فيها الحساب ، تتغير قيمة B2.
معظم المراجع الدائرية هي أخطاء غير مقصودة ؛ سوف يحذرك Excel من هذه. ومع ذلك ، هناك أيضًا مراجع دائرية مقصودة ، تُستخدم لإجراء حسابات تكرارية. قد تتسبب المراجع الدائرية غير المقصودة في ورقة العمل الخاصة بك في حساب الصيغة بشكل غير صحيح.
لذلك ، في هذه المقالة ، سنشرح كل ما تحتاج لمعرفته حول المراجع الدائرية ، وكذلك كيفية البحث عن المراجع الدائرية وإصلاحها وإزالتها واستخدامها في Excel.
كيفية البحث عن المرجع الدائري والتعامل معه في Excel
عند العمل مع Excel ، نواجه أحيانًا أخطاء مرجعية دائرية تحدث عندما تقوم بإدخال صيغة تتضمن الخلية التي توجد بها الصيغة الخاصة بك. في الأساس ، يحدث ذلك عندما تحاول الصيغة حساب نفسها.
على سبيل المثال ، لديك عمود من الأرقام في الخلية A1: A4 وأنت تستخدم الدالة SUM (= SUM (A1: A5)) في الخلية A5. تشير الخلية A5 مباشرة إلى الخلية الخاصة بها ، وهذا خطأ. ومن ثم ، سوف تحصل على التحذير المرجعي الدائري التالي:
بمجرد حصولك على رسالة التحذير أعلاه ، يمكنك النقر فوق الزر "مساعدة" لمعرفة المزيد عن الخطأ ، أو إغلاق نافذة رسالة الخطأ بالنقر فوق الزر "موافق" أو "X" والحصول على "0" كنتيجة لذلك.
في بعض الأحيان ، قد تتسبب حلقات المرجع الدائرية في تعطل الحساب أو إبطاء أداء ورقة العمل. يمكن أن يؤدي المرجع الدائري أيضًا إلى عدد من المشكلات الأخرى ، والتي لن تتضح على الفور. لذلك ، من الأفضل تجنب ذلك.
مراجع دائرية مباشرة وغير مباشرة
يمكن تصنيف المراجع الدائرية إلى نوعين: مراجع دائرية مباشرة ومراجع دائرية غير مباشرة.
المرجع المباشر
المرجع الدائري المباشر بسيط جدًا. تنبثق رسالة تحذير المرجع الدائري المباشر عندما تشير الصيغة إلى خليتها مباشرة.
في المثال أدناه ، تشير الصيغة الموجودة في الخلية A2 مباشرةً إلى الخلية الخاصة بها (A2).
بمجرد ظهور رسالة التحذير ، يمكنك النقر فوق "موافق" ، ولكنها ستؤدي فقط إلى "0".
مرجع دائري غير مباشر
يحدث المرجع الدائري غير المباشر في Excel عندما تشير قيمة في صيغة ما إلى الخلية الخاصة بها ، ولكن ليس بشكل مباشر. بمعنى آخر ، يمكن تكوين المرجع الدائري من خلال خليتين تشير كل منهما إلى الأخرى.
دعونا نشرح بهذا المثال البسيط.
الآن تبدأ القيمة من A1 والتي لها القيمة 20.
بعد ذلك ، تشير الخلية C3 إلى الخلية A1.
ثم تشير الخلية A5 إلى الخلية C3.
الآن استبدل القيمة 20 في الخلية A1 بالصيغة الموضحة أدناه. تعتمد كل خلية أخرى على الخلية A1. عند استخدام مرجع لأي خلية صيغة سابقة أخرى في A1 ، فسوف يتسبب ذلك في تحذير مرجعي دائري. لأن الصيغة في A1 تشير إلى الخلية A5 ، التي تشير إلى C3 ، والخلية C3 تشير مرة أخرى إلى A1 ، ومن هنا جاء المرجع الدائري.
عند النقر فوق "موافق" ، ينتج عنه قيمة 0 في الخلية A1 ويقوم Excel بإنشاء سطر مرتبط يعرض تتبع السابقات وتتبع التابعين كما هو موضح أدناه. يمكننا استخدام هذه الميزة للعثور على المراجع الدائرية وإصلاحها بسهولة.
كيفية تمكين / تعطيل المراجع الدائرية في Excel
بشكل افتراضي ، يتم إيقاف تشغيل العمليات الحسابية التكرارية (معطلة) في Excel. الحسابات المتكررة هي حسابات متكررة حتى تحقق شرطًا معينًا. عندما يتم تعطيله ، يعرض Excel رسالة مرجع دائري ويعيد 0 كنتيجة.
ومع ذلك ، في بعض الأحيان تكون هناك حاجة إلى المراجع الدائرية لحساب حلقة. لاستخدام المرجع الدائري ، يجب تمكين العمليات الحسابية التكرارية في برنامج Excel الخاص بك وسيسمح لك بإجراء العمليات الحسابية الخاصة بك. الآن ، دعنا نوضح لك كيف يمكنك تمكين أو تعطيل العمليات الحسابية التكرارية.
في Excel 2010 و Excel 2013 و Excel 2016 و Excel 2019 و Microsoft 365 ، انتقل إلى علامة التبويب "ملف" في الزاوية العلوية اليسرى من Excel ، ثم انقر فوق "خيارات" في الجزء الأيمن.
في نافذة خيارات Excel ، انتقل إلى علامة التبويب "الصيغة" وحدد خانة الاختيار "تمكين الحساب التكراري" ضمن قسم "خيارات الحساب". ثم انقر فوق "موافق" لحفظ التغييرات.
سيمكن هذا الحساب التكراري وبالتالي السماح بالمرجع الدائري.
لتحقيق ذلك في الإصدارات السابقة من Excel ، اتبع الخطوات التالية:
- في Excel 2007 ، انقر فوق زر Office> خيارات Excel> الصيغ> منطقة التكرار.
- في Excel 2003 والإصدارات السابقة ، تحتاج إلى الانتقال إلى القائمة> الأدوات> الخيارات> علامة التبويب الحساب.
الحد الأقصى من التكرارات ومعلمات التغيير القصوى
بمجرد تمكين الحسابات التكرارية ، يمكنك التحكم في الحسابات التكرارية ، من خلال تحديد خيارين متاحين ضمن قسم تمكين الحساب التكراري كما هو موضح في لقطة الشاشة أدناه.
- الحد الأقصى من التكرارات - يحدد هذا الرقم عدد المرات التي يجب أن تعيد الصيغة حسابها قبل إعطائك النتيجة النهائية. القيمة الافتراضية هي 100. إذا قمت بتغييرها إلى "50" ، فسيقوم Excel بتكرار العمليات الحسابية 50 مرة قبل إعطائك النتيجة النهائية. تذكر أنه كلما زاد عدد التكرارات ، زادت الموارد والوقت المستغرق في الحساب.
- أقصى قدر من التغيير - يحدد الحد الأقصى للتغيير بين نتائج الحساب. تحدد هذه القيمة دقة النتيجة. كلما كان الرقم أصغر ، كانت النتيجة أكثر دقة وطالت المدة التي تستغرقها لحساب ورقة العمل.
إذا تم تمكين خيار الحسابات التكرارية ، فلن تتلقى أي تحذير عندما يكون هناك مرجع معاد في ورقة العمل الخاصة بك. قم بتمكين الحساب التفاعلي فقط عندما يكون ذلك ضروريًا للغاية.
ابحث عن مرجع معاد في Excel
لنفترض أن لديك مجموعة بيانات كبيرة وحصلت على تحذير مرجعي دائري ، فستظل بحاجة إلى معرفة مكان حدوث الخطأ (في أي خلية) لإصلاحه. للعثور على مراجع دائرية في Excel ، اتبع الخطوات التالية:
استخدام أداة فحص الأخطاء
أولاً ، افتح ورقة العمل حيث حدث المرجع الدائري. انتقل إلى علامة التبويب "الصيغة" ، وانقر على السهم بجوار أداة "التحقق من الأخطاء". ثم مرر المؤشر فوق خيار "المراجع الدائرية" ، وسيعرض لك Excel قائمة بجميع الخلايا المشتركة في المرجع الدائري كما هو موضح أدناه.
انقر فوق أي عنوان خلية تريده في القائمة وسيأخذك إلى عنوان الخلية هذا لحل المشكلة.
باستخدام شريط الحالة
يمكنك أيضًا العثور على المرجع الدائري في شريط الحالة. في شريط الحالة في Excel ، سيعرض لك أحدث عنوان خلية مع مرجع دائري ، مثل "المراجع الدائرية: B6" (انظر أدناه لقطة الشاشة).
هناك أشياء معينة يجب أن تعرفها عند التعامل مع المرجع الدائري:
- لن يُظهر شريط الحالة عنوان الخلية المرجعي الدائري عند تمكين خيار الحساب التكراري ، لذلك تحتاج إلى تعطيله قبل البدء في البحث في المصنف عن المراجع الدائرية.
- في حالة عدم العثور على مرجع معاد في الورقة النشطة ، يعرض شريط الحالة فقط "مراجع دائرية" بدون عنوان خلية.
- ستحصل على مطالبة مرجعية دائرية مرة واحدة فقط وبعد النقر فوق "موافق" ، لن تظهر المطالبة مرة أخرى في المرة القادمة.
- إذا كان المصنف الخاص بك يحتوي على مراجع دائرية ، فسوف يعرض لك المطالبة في كل مرة تفتحه حتى تحل المرجع الدائري أو حتى تقوم بتشغيل الحساب التكراري.
إزالة مرجع معاد في Excel
يعد العثور على مراجع دائرية أمرًا سهلاً ولكن إصلاحه ليس بهذه البساطة. لسوء الحظ ، لا يوجد خيار في Excel يتيح لك إزالة جميع المراجع الدائرية مرة واحدة.
لإصلاح المراجع الدائرية ، عليك البحث عن كل مرجع معاد على حدة ومحاولة تعديله أو إزالة الصيغة الدائرية تمامًا أو استبدالها بمرجع آخر.
في بعض الأحيان ، في الصيغ البسيطة ، كل ما عليك فعله هو إعادة ضبط معلمات الصيغة بحيث لا تشير إلى نفسها. على سبيل المثال ، قم بتغيير الصيغة في B6 إلى = SUM (B1: B5) * A5 (تغيير B6 إلى B5).
سيعيد نتيجة الحساب كـ "756".
في الحالات التي يصعب فيها العثور على مرجع دائري في Excel ، يمكنك استخدام ميزتي تتبع السابقات وتتبع التابعين لتتبعها مرة أخرى إلى المصدر وحلها واحدًا تلو الآخر. يوضح السهم الخلايا التي تأثرت بالخلية النشطة.
هناك طريقتان للتتبع يمكن أن تساعدك على حذف المراجع الدائرية من خلال إظهار العلاقات بين الصيغ والخلايا.
للوصول إلى طرق التتبع ، انتقل إلى علامة التبويب "الصيغ" ، ثم انقر فوق "تتبع السابقات" أو "تتبع التابعين" في مجموعة تدقيق الصيغة.
تتبع السوابق
عند تحديد هذا الخيار ، فإنه يتتبع مرة أخرى الخلايا التي تؤثر على قيمة الخلية النشطة. يرسم خطًا أزرق يشير إلى الخلايا التي تؤثر على الخلية الحالية. مفتاح الاختصار لاستخدام سوابق التتبع هو Alt + T U T..
في المثال أدناه ، يوضح السهم الأزرق أن الخلايا التي تؤثر على قيمة B6 هي B1: B6 و A5. كما ترى أدناه ، تعد الخلية B6 أيضًا جزءًا من الصيغة ، مما يجعلها مرجعًا دائريًا وتتسبب في إرجاع الصيغة "0" كنتيجة.
يمكن إصلاح ذلك بسهولة عن طريق استبدال B6 بـ B5 في وسيطة SUM: = SUM (B1: B5).
تتبع المعالين
تتعقب ميزة العناصر التابعة للتتبع الخلايا التي تعتمد على الخلية المحددة. ترسم هذه الميزة خطًا أزرق يشير إلى الخلايا المتأثرة بالخلية المحددة. وهذا يعني أنه يعرض الخلايا التي تحتوي على الصيغ التي تشير إلى الخلية النشطة. مفتاح الاختصار لاستخدام التابعين هو Alt + T U D.
في المثال التالي ، تتأثر الخلية D3 بـ B4. يعتمد على B4 لقيمته لتحقيق النتائج. ومن ثم ، فإن تابع التتبع يرسم خطًا أزرق من B4 إلى D3 ، مما يشير إلى أن D3 يعتمد على B4.
تعمد استخدام المراجع الدائرية في Excel
لا يُنصح باستخدام المراجع المعممة بشكل متعمد ، ولكن قد تكون هناك بعض الحالات النادرة التي تحتاج فيها إلى مرجع معاد حتى تتمكن من الحصول على الإخراج الذي تريده.
دعونا نوضح ذلك باستخدام مثال.
للبدء ، قم بتمكين "الحساب التكراري" في مصنف Excel الخاص بك. بمجرد تمكين الحساب التكراري ، يمكنك البدء في استخدام المراجع الدائرية لصالحك.
لنفترض أنك تشتري منزلًا وتريد إعطاء عمولة 2٪ على التكلفة الإجمالية للمنزل لوكيلك. سيتم حساب التكلفة الإجمالية في الخلية B6 ويتم احتساب نسبة العمولة (رسوم الوكيل) في B4. يتم احتساب العمولة من التكلفة الإجمالية وتشمل التكلفة الإجمالية العمولة. نظرًا لأن الخلايا B4 و B6 تعتمدان على بعضهما البعض ، فإنها تنشئ مرجعًا دائريًا.
أدخل الصيغة لحساب التكلفة الإجمالية في الخلية B6:
= SUM (B1: B4)
نظرًا لأن التكلفة الإجمالية تشمل رسوم الوكيل ، فقد قمنا بتضمين B4 في الصيغة أعلاه.
لحساب رسوم الوكيل 2٪ ، أدخل هذه الصيغة في B4:
= B6 * 2٪تعتمد الصيغة في الخلية B4 الآن على قيمة B6 لحساب 2٪ من إجمالي الرسوم وتعتمد الصيغة في B6 على B4 لحساب التكلفة الإجمالية (بما في ذلك رسوم الوكيل) ، ومن هنا جاء المرجع الدائري.
إذا تم تمكين الحساب التكراري ، فلن يمنحك Excel تحذيرًا أو 0 في النتيجة. بدلاً من ذلك ، سيتم حساب نتيجة الخليتين B6 و B4 كما هو موضح أعلاه.
عادةً ما يتم تعطيل خيار الحسابات التكرارية افتراضيًا. إذا لم تقم بتشغيله وعند إدخال الصيغة في B4 ، فسيتم إنشاء مرجع دائري. سيصدر Excel التحذير وعند النقر فوق "موافق" ، سيظهر سهم التتبع.
هذا هو. كان هذا كل ما تحتاج لمعرفته حول المراجع الدائرية في Excel.