خطای 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 دو حالت وجود داره:
- خطا بصورت عمدی ایجاد شده که باید تنظیمات مربوطه رو مشخص کنیم.
شکل۴- تنظیمات محاسبات خطای ارجاع بازگشتی
در این قسمت تنظیم میکنیم که لوپ های این خطا رو تا چند بار محاسبه کنه. در شکل ۴ میزان Maximum Iterations رو گذاشتیم ۱. این موضوع کاربردهای خیلی خاصی داره و در حالت عادی مورد استفاده قرار نمیگیره.
- خطا سهوی ایجاد شده که باید مراجع رو در فرمول ها اصلاح کنیم تا ارتباطی بین سلول حاوی فرمول و سلولهای مرجع فرمول وجود نداشته باشه.
باسلام و خدا قوت به شما اساتید محترم
یک سوال داشتم درخصوص یک پروژه
من ده فایل اکسل که در یک درایو مشترک در یک سرور برای استفاده ده نفر برای تنظیم برنامه کاری روزانه و ماهانه و سالانه و گرفتن مغایرت های مکانی و زمانی و خروجی های دگیر تهیه کردم که این فایل ها اطلاعات زمان جلسات ، مکان، و شرایط و نتایج آن را به یکدیگر و بخصوص فایل مادر ارسال و دریافت می کنند
مشکلی که وجود دارد این است که در بعضی از لینک های بوجود امده خطای رفرنس داده می شود و هنگام بررسی فرمول ان سلول اسم شیت مورد نظر بصورت REF# مشاهده می شود و پس از تغییر نام شیت های کلیه فایل ها از نام فارسی به نام انگلیسی، تغییر نام پوشه حاوی فایل های اکسل از نام فارسی به نام انگلیسی باز هم با این مشکل مواجه می شوم
لطفا راهنمایی بفرمایید
باتشکر
درود
احتمالا فایل ها جابجا شدن
برای این قبیل کارها گوگل شیت یا اکسل آنلاین رو پیشنهاد میکنم
با سلام و احترام
فایل من حاوی این پیغام می باشد و فایل که باز می شود در قسمت فرمول گزینه ها غیر فعال می باشد خواهشمند است راهنمایی بفرمایین برای حل مشکل
درود بر شما
چک کنید protect نباشه
چون راه تشخیصش از همون تب formula هست
با سلام و احترام
با سلام و احترام
در قسمت فرمول سلول خطا نمایش داده می شود ولی متاسفانه فایل باز نمی شود که خطا رفع شود راه حل دیگری دارد
درود
باید سلولی که این خطا داره رو باز کنید و بررسی کنید که کجای فرمول اشتباه هست
خیلی ممنونم من توی محل کار گیر کرده بودم مشکلم حل شد ممنون از راهنماییتون
ممنون از راهنماییتون
ممنونم از راهنماییتون
با سلام
من یک فایل اکسل دارم با چندین شیت که وقتی حتی کل فایل رو از سیستم خودم جابجا میکنم و داخل یه سیستم دیگه میریزم فرمولهای if تو در تو دیگه کار نمیکنه و صفر نمایش میده حتی محاسبات هر دو سیستم هم بصورت خودکار هستش.روی چندید سیستم کار کردم ولی جوابی نگرفتم.یعنی عملا یک فرمول ساده رو اجرا میکنه ولی سلولهایی که فرمولهای طولانی تری دارند جواب نمیده.
لطفا راهنمایی بفرمائید.
تشکر فراوان
سلام
باید فایلتون بررسی بشه، سوالتون رو به همراه فایل (با داده های غیر واقعی) در گروه تلگرامی اکسل پدیا ارسال کنید.
هرچی از خدا میخوای بهت بده
خیلی کمک کردی
سلام . خدا پدر مادرتو ببخشه و بیامرزه
با سلام یک سوال داشتم ازتون
من یه سلول دارم که داخلش تاریخ شمسی درج میشه. این سلول که تاریخ شمسی داخلش هستش باید مثلا۷ روز بهش اضافه بشه و دوباره داخل همون سبول قبلی قرار بگیره. یعنی مقدار جدید داخل سلولی که مقدار قبلی داشت. چطوری اینکار رو انجام بدم. و میخوام یه دکمه یا سلول باشه که وقتی مثلا پر شد یا تیک خورد. بغد از پر شدنش یا تیک خوردنش اتوماتیک مثلا هفت روز اضافه کنه به تاریخ قبلی و تاریخ جدید جای تاریخ قبلی قرار بگیره و همین امر تکرار بشه
اگه توضیح بیشتری خواستین ایمیل بدین تا من فایل بفرستم
درود
میتونید یک چک باکس بذارید که وقتی تیک خورد، هفت روی به سلول مورد نظر اضافه بشه.
داخل سلول مورد نظر هم اینو بنویسید:
در این فرمول، A1 سل لینک چک باکس مورد نظر هست
ضمن اینکه فرمت سلول رو هم روی تاریخ شمسی تنظیم کنید
اگر حالت ها بیش از اینه و هر بار یک اتفاق باید بیفته، احتمالا باید برید سمت کد VBA
با سلام
در قسمت error checking قسمت circular قابل دسترسی نیست (گزیم هشفیده و نمیشه روش کلیک کرد)
چجوری میشه منشا خطا رو فهمید ؟
پ ن :در برنامه هایی که قبلا نوشته بودم واین خطا رو نمیداد هم جدیدا این خطا ظاهر میشه در صورتی که تغییری داخلشون ندادم
پ ن ۲ : بعضی از سلولها به رنگ قرمز درمیان در صورتی که فقط کپی شدن
با تشکر
درود بر شما
وقتی قابل کلیک نیست دو حالت رو میشه متصور شد که خطای circular نداریم (که ظاهرا شما پیغام خطا رو دریافت میکنید و این گزینه ممکن نست)
یک احتمال دیگه هم وجود داره که انقدر این سلول های حاوی خطا تعدادشون زیاده، که در اون قسمت نمایش داده نمیشه.
اگر اینطور باشه باید فرمول هایی که مشکوک هستید رو خودتون چک کنید
ضمن اینکه اگر محدوده نامگذاری دارید اونم چک کنید ممکنه اونجا هم همچین خطایی وجود داشته باشه
پ.ن ۲ ر و متوجه نشدم!
بسیار سپاسگزارم از راهنمای تون
الان خروجی این فرمول که داخل این سلول هستش اتوماتیک جای تاریخ قبلی میشینه. کار من برای یه بار نیس مثلا برای هفته بعد نباید تیک داشته باشه. اینو با یه دکمه حل میشه که وقتی روش زدم ۷ روز اضافه کنه
مشکل من اینه که میخوام تاریخ جدید دقیقا جایگزین تاریخ قبلی بشه. تاریخی که توی سلول بوده نه اینکه با تابع وارد کنم مثلا
۱۳۹۸/۰۵/۰۱ توی سلول A1 نوشته شده و توی A2 نوشتم عدد هفت. حالا میخوام مثلا با یه دکمه یا تیک یا هرچیزی دیگه ای(فرقی نداره) هر وقت روش زدم تاریخ سلول A1 رو بگیره و با A2 جمع کنه و “مجددا توی سلول A1 بزاره”. نکته جمله آخر مجددا تاریخ رو توی همون سلول قبلی جایگزین کنه
باید کدنویسی کنید که با زدن دکمه یا هر چیز دیگه این کارر رو انجام بده
کد ساده جمع زدن رو به یک ئکمه assign کنید
سلام خسته نباشید
من دستوری که فرموده بودید را اجرا کردم اما هیچ تغییری ایجاد نشد!
درود
چه دستوری؟!
ممنون از راهنماییتون خیلی بدردم خورد واقعا ممنون
آخرش نوشتین این روش توصیه نمی شود . میشه توضیح بدین یعنی منظورتون اینه روش دوم رو بکار نبریم؟
کدوم روش دوم؟
حالت های ارائه شده، روش نیست، نحوه مدیریت خطا بسته به شرایط موضوع هست.
ضمن اینکه نگفتیم توصیه نمیشه. با توجه به اینکه خطای لوپ رو محاسبه میکنه، باید دقت داشته باشید به فمرول نویس یدقیق چون دیگه این خطا رو نخواهید دید.
ضمن اینکه کاربردها خاص هستن و باید مسائل مختلفش رو ببینید
باسلام و خیلی ممنونم مشکل حل شد
مدت ها بود این خطا اذیتم میکرد و ذهنمو به خودش مشغول کرده بود
مرسی
تو این مدت کوتاه تا الان چند تا نکته کاربردی یاد گرفتم ازتون
سپاس فراوان