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

هل رأيت خطأ المرجع الدائري على ورقة Excel وتساءلت عما إذا كانت ورقة العمل معطلة؟ ثق بي! أنت لست أول من يواجه خطأ Excel هذا. يواجه مئات وآلاف من مستخدمي Excel أخطاء مرجعية دائرية كل يوم. لا تقلق! إليك كيفية العثور على مراجع دائرية في Excel وحلها.

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

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

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

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

ما هو المرجع الدائري في Excel؟

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

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

أنت تحاول ضرب 10 من الخلية B1 ووضع النتيجة في الخلية B1 نفسها.

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

ما هو المرجع الدائري في Excel

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

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

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

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

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

متى ترى خطأ المرجع الدائري في Excel؟

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

لتجنب أي أضرار ناتجة عن المراجع الدائرية في صيغ Excel ، يجب أن تعرف متى تظهر ومتى لا تظهر. تجد أدناه الحالات:

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

متى ترى خطأ المرجع الدائري في Excel

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

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

أنواع المراجع الدائرية في Excel

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

مثال على استخدام المرجع الدائري

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

مرجع معاد غير مقصود في Excel

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

= SUM (A2: A10)

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

مثال على مرجع دائري غير مباشر

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

الآن بعد أن فهمت أساسيات المراجع الدائرية في Excel ، دعنا نتعرف على كيفية العثور على مراجع دائرية في Excel أدناه:

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

بشرط أن يتم إلغاء تنشيط ميزة الحساب التكراري ، يمكنك مشاهدة الإعلامات التالية حول المراجع الدائرية على شريط معلومات Excel:

• المراجع الدائرية: C3  (أو أي مرجع خلية آخر) عندما تكون في ورقة عمل تحتوي على صيغة دائرية في المصنف.

إظهار المراجع الدائرية في شريط معلومات Excel

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

كيفية البحث عن مراجع دائرية في Excel: استخدام تدقيق الأخطاء

إنها أفضل طريقة للتحقق من وجود مراجع دائرية في مصنف أو ورقة عمل. فيما يلي الخطوات التي يجب أن تعرفها:

• افتح ورقة عمل تشك في احتوائها على صيغ دائرية.
• انقر فوق قائمة الصيغ في شريط Excel.

استخدم التحقق من الأخطاء لـ How to Find Circular References في Excel

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

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

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

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

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

• يمكنك أيضًا النقر فوق تتبع التابعين لاكتشاف الخلايا المتأثرة بقيمة مرجع الخلية المحدد.
• إذا لم يُظهر Excel أي أخطاء بعد النقر فوق الأوامر أعلاه ، فلا توجد مراجع دائرية في ورقة العمل الخاصة بك.
• كرر الخطوات في جميع أوراق العمل للتحقق من المصنف بأكمله.

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

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

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

كيفية إلغاء تنشيط الحساب التكراري في Excel

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

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

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

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

هناك طريقة أخرى سهلة وهي إزالة الصيغة من الخلية الحالية ووضعها في مكان آخر داخل نفس ورقة العمل.

كيفية البحث عن المراجع الدائرية في أوراق Google (طريقة المكافأة)

مثل Excel ، لا تسمح جداول بيانات Google بأي مراجع دائرية في صيغة واحدة أو أكثر إذا لم تقم بتنشيط ميزة الحساب التكراري. ومن ثم ، ستعرض "جداول بيانات Google" على الفور الخطأ #REF! خطأ في الخلية التي تحتوي على صيغة دائرية.

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

لذلك ، يمكنك ببساطة تحديد جميع الخلايا التي تحتوي على #REF! تحذير للعثور على المراجع المعممة في جداول بيانات Google وحلها.

مراجع Excel المعممة: الكلمات النهائية

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

بعد ذلك ،  أفضل قوالب تقويم Excel لأي عام  وأفضل  قوالب ميزانية Excel .