چرا جمع اعداد در اکسل صفر می‌شود؟ شرح خطای Circular References اکسل و یافتن آن

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

توجه ۱) در این مقاله Circular References را به اختصار CR می‌نویسیم.

توجه ۲) CR‌ را، مراجع دورانی ترجمه کرده‌اند.

علائم ظاهری این خطا در اکسل و حاصل صفر محاسبات روی اعداد در اکسل

مهمترین مشکلی که CR ایجاد می‌کند این است که جمع و هر محاسبه دیگری بر روی محدوده‌ای که در یکی از سلول‌های آن CR‌ باشد، صفر خواهد شد و به همین دلیل حتما باید CR‌ را بیابید و مرتفع کنید.

• هر بار که اکسل را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
• یک فلش آبی رنگ روی صفحه اکسل می‌آید و اصلا انتخاب و پاک نمی‌شود.
• نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که من کلی عدد در محدوده Sum دارم.

بله، شما به یکی از اشکال فوق سوالتان را می‌پرسید و جواب همه آنها در اکسل رخ دادن خطای Circular Reference است که باید آنرا پاک یا کنترل نمایید.

توضیح Circular Reference در اکسل

برای توضیح این خطا یک مثال ساده آورده می‌شود تا دقیقا بفهمید که چه بر سر اکسل  می‌آید. فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید " نامه شما ابتدا باید به  تایید آقای جیم برسید " و وقتی که به اتاق آقای "جیم" می‌روید، می‌گوید "این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود".
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک "دور باطل" یا به قول ما کامپیوتری‌ها Loop خواهد بود که در اکسل به این حالت Circular Reference می‌گویند.

Circular Reference در اکسل چگونه ایجاد می‌شود

مانند شکل زیر در خانه B2 فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی ، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم اکسل بلافاصله پیغام زیر را به شما نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار اکسل باز شده و Circular Reference را توضیح می‌دهد.

این خطا در نسخه‌های جدیدتر اکسل به شکل زیر است:

البته حالت‌های دیگری نیز می‌توان مثال زد، مثلا شما در سلول B2 فرمولی مانند زیر را بنویسید. چون خود سلول در محدوده تابع SUM است در نتیجه Circular Reference ایجاد خواهد شد.

نمایش فلش آبی رنگ در اکسل

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference در اکسل است.

خطای هنگام باز شدن این فایل

این فایل را ذخیره کنید و ببنید و سپس باز نمایید، مشاهده می‌کنید که در هر بار بازشدن فایل پیغام خطای Circular Reference نمایش داده می‌شود.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar شما می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول ، آدرس سلولهای بعدی را نشان می‌دهد.

روش پیدا کردن همه Circular Reference

از ریبون (به نوار ابزار اکسل 2007 ریبون می‌گویند) گزینه Formula و سپس مانند شکل زیر اقدام نمایید:

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

در مثال زیر سلول C5 دچار CR شده است (فرمول آن در سلول کناری برای راهنمایی به صورت متنی کمرنگ نوشته شده) و همانطور که می‌بینید نتیجه سلول C9 که جمع سلولهای بالایی است عدد صفر شده است و این به دلیل خطای CR است.

تا اینجا با مفهوم Circular Reference در اکسل آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطای Circular References‌ در اکسل

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در اکسل Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به اکسل می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه بده) و سپس کار را تمام کن.

برای فعال کردن وضعیت Iteration در اکسل Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و ... و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع اکسل و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد.