خطای Circular Reference و چگونگی برطرف ساختن آن

خطای Circular Reference یک خطای پنهان

همونطور که قبلا گفتیم یکی از اصول فرمول نویسی آشنایی با انواع خطاها و نحوه مدیریت کردن آن است. اما غیر از خطاهایی که قبلا توضیح داده شد، یک خطا وجود داره به نام خطای Circular Reference. تمایز این خطا با خطاهای دیگه، اینه که بصورت Error در سلول نمایش داده نمیشه و خطا رو بصورت یک پیام اخطار نمایش میده (شکل ۱)

شکل ۱- نمایش اخطار مربوز به این خطا

علت بروز این خطا چی هست؟

در فرمول نویسی (بسته به نوع فرمول) یک یا چند سلول به عنوان مرجع اختصاص داده میشن. حالا اگه بیایم توی سلول های مرجع یک فرمول، فرمولی بنویسیم که ارتباط برقرار بشه با سلولی که فرمول اولیه توش بوده، این خطا ظاهر میشه. به شکل ۲ دقت کنید. همونطور که میبینید در سلول D3 جمع سلولهای A2:D2 محاسبه شده. بعد در سلول D2 که یکی از رفرنس های فرمول Sum(A2:D2) هست به خود سلول D3 که شامل فرمول هست ارجاع داده شده. پس بطور خلاصه، این خطا زمانی اتفاق میفته که بین سلولهای مرجع یک فرمول و سلولی که فرمول در آن نوشته شده، ارتباط برقرار بشه.

شکل ۲- خطای Circular Reference

وقتی این خطا اتفاق بیفته، پیامی مشابه شکل ۱ ظاهر میشه و بعد از زدن OK خطور آبی مطابق شکل ۲ بر روی سلول ها ظاهر میشه که ارتباط سلولها رو نشون میده.

وقتی فایلی حاوی این خطا باشه هر بار که فایل رو باز میکنیم خطای مشابه شکل ۱ ظاهر میشه. پس در این صورت باید ببینیم کدوم سلول ها شامل این خطا هستند. مطابق مسیر زیر (شکل ۳) سلول های حاوی خطای Circular Reference رو می بینید.

Formulas/ Formula Auditing/ Error Checking/ Circular References

شکل ۳- نمایش مکان سلول هایی که خطای ارجاع بازگشتی دارند

مدیریت خطای Circular Reference

برای مدیریت خطای Circular Reference دو حالت وجود داره:

1. خطا بصورت عمدی ایجاد شده که باید تنظیمات مربوطه رو مشخص کنیم.

شکل4- تنظیمات محاسبات خطای ارجاع بازگشتی

در این قسمت تنظیم میکنیم که لوپ های این خطا رو تا چند بار محاسبه کنه. در شکل ۴ میزان Maximum Iterations رو گذاشتیم ۱. این موضوع کاربردهای خیلی خاصی داره و در حالت عادی مورد استفاده قرار نمیگیره.

2. خطا سهوی ایجاد شده که باید مراجع رو در فرمول ها اصلاح کنیم تا ارتباطی بین سلول حاوی فرمول و سلولهای مرجع فرمول وجود نداشته باشه.