گاهی اوقات حین فرمول‌نویسی در سلول‌های اکسل، اروری با عبارت Circular Reference ظاهر می‌شود که به معنی ارجاع بازگشتی یا حلقه‌ی ارجاع است. زمانی که در فرمول به سلول دیگری ارجاع بدهید و مقدار آن سلول نیز به سلول فعلی بستگی داشته باشد، یک چرخه‌ی بازگشتی ایجاد می‌شود و محاسبه‌ی نتیجه‌ی فرمول امکان‌پذیر نیست.
در ادامه با ارور Circular Reference در نرم‌افزار Excel آشنا می‌شویم و روش رفع ارور را بررسی می‌کنیم.
ارور Circular Reference چیست؟
همان‌طور که در مقدمه اشاره کردیم، اکسل نمی‌تواند حاصل فرمولی که مرتباً به خودش برمی‌گردد را محاسبه کند و در واقع ارور Circular Reference زمانی ظاهر می‌شود که اکسل با یک حلقه‌ی محاسباتی روبرو شده باشد، حلقه‌ای که تا بی‌نهایت تکرار می‌شود.
ارجاع گاهی مستقیم است و گاهی غیرمستقیم است. اگر در فرمول مستقیماً به خود سلول ارجاع داده شده باشد، ارجاع مستقیم است و اگر در فرمولی به سلول‌های دیگری ارجاع شود و در آن سلول‌ها به سلول فعلی ارجاع داده شده باشد، ارجاع از نوع غیرمستقیم است. در هر دو حالت حلقه‌ی محاسباتی تا بی‌نهایت ادامه پیدا می‌کند و لذا Excel محاسبه را متوقف کرده و ارور Circular Reference را نمایش می‌دهد.
گاهی اوقات فایل ساده و جدول کوچک است، در نتیجه به سرعت متوجه می‌شوید که چرا ارور Circular Reference ظاهر شده است اما گاهی در یک فرمول از چند سلول استفاده شده و یکی از آن سلول‌ها به سلول فعلی وابسته است و در نتیجه تشخیص ارجاع به سلول فعلی سخت‌تر است.
مثال‌هایی از ارجاع مستقیم و غیرمستقیم منجر به ارور Circular Reference
درک ارجاع مستقیم بسیار ساده است و با یک مثال ساده موضوع روشن می‌شود. فرض کنید که صفحه‌ای به صورت زیر دارید که در آن در سلول A3 فرمول زیر تایپ شده است:
=A1+A2+A3
فرض کنید که سلول‌های A1 و A2 مستقل و حاوی یک عدد هستند. در این حالت برای محاسبه کردن مقدار سلول A3 می‌بایست از خود A3 استفاده شود و در نتیجه حلقه‌ی محاسباتی تا بی‌نهایت تکرار می‌شود. در این حالت ارجاع مستقیم به خود سلول صورت گرفته که به لحاظ منطقی اشتباه است.
با زدن کلید Enter، اروری به صورت زیر نمایان می‌شود که در آن از عبارت Circular references استفاده شده است. اگر روی OK کلیک کنید، اکسل عدد ۰ را به عنوان خروجی چاپ می‌کند و می‌بایست برای رفع ارور اقدام کنید.
دقت کنید که اگر ارور را رفع نکنید، حاصل فرمول اشتباه خواهد بود. در این مثال ساده، حاصل سلول A3 به لحاظ منطقی نامشخص است چرا که هر بار ۱۵ عدد به خروجی فرمول اضافه می‌شود و این کار تا بی‌نهایت ادامه دارد. مقدار اولیه نیز مشخص نیست. به همین علت اکسل حاصل محاسبه را صفر فرض می‌کند که البته صحیح نیست و می‌بایست ارور را رفع کرد.
اما ارجاع غیرمستقیم: فرض کنید که در سلول F4 یک فرمول ساده نوشته‌ایم که مقدار سلول D2 در ۲ ضرب شود:
=D2*2
با زدن کلید Enter، نتیجه در سلول F4 نمایش داده می‌شود. فرضاً اگر D2 عدد ۵ باشد، F4 عدد ۱۰ خواهد بود.
در ادامه در سلول B4 فرمول ساده‌ای نوشته‌ایم که مقدار F4 را در ۲ ضرب می‌کند و طبعاً حاصل کار عدد ۲۰ خواهد بود.
اما در نهایت به سلول D2 برمی‌گردیم و به جای مقدار اولیه که عدد ۵ بود، یک فرمول ساده می‌نویسیم که عدد موجود در سلول B4 را در ۲ ضرب کند:
=B4*2
به این ترتیب حلقه‌ی محاسباتی ایجاد می‌شود چرا که B4 خود به D2 بستگی دارد و D2 نیز به B4 بستگی دارد!
باز زدن کلید Enter، اکسل متوجه این حلقه‌ی محاسباتی شده و ارور ارجاع بازگشتی ظاهر می‌شود. نتیجه نمایش عدد 0 به عنوان خروجی فرمول تایپ شده در سلول D2 است که البته خروجی صحیح نیست و باید ارور را رفع کرد.
روش پیدا کردن حلقه‌های ارجاع در Excel
در اکسل دو روش دستی و خودکار برای پیدا کردن ارجاع‌های بازگشتی وجود دارد. روش دستی در فایل‌های ساده و جداول کوچک خوب است اما برای صفحات بزرگ حاوی داده‌ها و فرمول‌های پیچیده، این روش مناسب نیست. هر دو روش را با مرور کردن مثال‌های فوق بررسی می‌کنیم.
روش دستی
فایل و Sheet موردنظر را باز کنید.
در نوار ابزار بالای صفحه روی تب Formulas کلیک کنید.
روی Show Formulas کلیک کنید تا فرمول‌هایی که در سلول‌های مختلف نوشته شده، نمایش داده شود.
اکنون سلول‌هایی که حلقه‌ی ارجاع دارند را شناسایی کنید.
فرمول‌ها را تصحیح کنید تا ارور رفع شود.
البته شناسایی کردن ارجاع بازگشتی در فرمول‌های پیچیده، نیاز به دقت و تجربه و حوصله دارد، به خصوص فرمول‌های پیچیده که ارجاع‌های متعددی به سلول‌ها دارند.
روش خودکار
مراحل زیر را دنبال کنید:
فایل و صفحه‌ی موردنظر را باز کنید.
در بالای صفحه روی تب Formulas کلیک کنید.
در این تب روی فلش کنار گزینه‌ی Error checking کلیک کنید.
در منوی کرکره‌ای روی گزینه‌ی Circular References کلیک کنید.
پس از تصحیح کردن اولین ارور، یک بار دیگر مراحل را تکرار کنید تا ارور ارجاع بازگشتی بعدی شناسایی شود. دقت کنید که در سمت پایین و چپ صفحه نیز سلول‌هایی که ارجاع بازگشتی دارند، نمایش داده می‌شود.
به همین سادگی اکسل سلول‌هایی که ارور حلقه‌ی ارجاع دارند را شناسایی می‌کند و می‌توانید فرمول‌ها را تصحیح کنید.
نحوه غیرفعال کردن ارجاع بازگشتی در اکسل
به صورت پیش‌فرض قابلیت محاسبات تکرارشونده یا iterative calculation در اکسل غیرفعال است اما اگر این قابلیت را فعال کنید، برخی از ارورهای ارجاع بازگشتی رفع می‌شود چرا که اکسل با تکرار کردن محاسبه، به نتیجه می‌رسد. برای فعال کردن این قابلیت به صورت زیر عمل کنید:
در بالای صفحه روی منوی File کلیک کنید.
در ستون کناری روی Options کلیک کنید.
در پنجره‌ی باز شده که Excel Options نام دارد، روی تب Formula در ستون کناری کلیک کنید.
حال در سمت راست تیک گزینه‌ی Enable Iterative Calculation را بزنید.
روی OK کلیک کنید تا تنظیمات ذخیره شود.
در نسخه‌های قدیمی اکسل روش کار اندکی متفاوت است:
ابتدا روی آیکون Office در گوشه‌ی بالای پنجره کلیک کنید.
سپس روی Excel options کلیک کنید.
در پنجره‌ی باز شده روی تب Formula کلیک کنید.
در سمت راست تیک Iterative Calculations را بزنید.
روی OK کلیک کنید.
پس از فعال کردن قابلیت محاسبات تکرارشونده، می‌توانید در خصوص تعداد تکرار محاسبات نیز تصمیم‌گیری کنید و یکی از گزینه‌های زیر را انتخاب کنید:
- Maximum Iterations یا حداکثر تکرار محاسبه که در این حالت می‌توانید حداکثر تعداد تکرار محاسبات را در کادر مربوطه وارد کنید که به صورت پیش‌فرض ۱۰۰ تکرار است.
- Maximum Change یا بیشترین تغییر که در این حالت محاسبات تا زمانی تکرار می‌شود که اختلاف بین آخرین نتیجه با نتیجه‌ی قبلی، به کمتر از عدد مشخص شده برسد که به صورت پیش‌فرض عدد 0.001 است و می‌توانید عدد دلخواه را وارد کنید.
makeuseofسیاره‌ی آی‌تی
آقا من دوتا راه حل رو رفتم اما باز لوک آپ ک مینویسم بازم ارور N/A میده ممنون میشم راهنماییم کنید دمت گرم سالار