سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

خطای Circular Reference
۴/۵ - (۸ امتیاز)

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

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

نمایش اخطار مربوز به خطای Circular Reference

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

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

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

خطای حلقه ای در اکسل

شکل ۲- خطای Circular Reference

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

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

Formulas/ Formula Auditing/ Error Checking/ Circular References

نمایش مکان سلول هایی که خطای Circular Reference دارند

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

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

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

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

تنظیمات محاسبات خطای Circular Reference

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

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

  1. خطا سهوی ایجاد شده که باید مراجع رو در فرمول ها اصلاح کنیم تا ارتباطی بین سلول حاوی فرمول و سلولهای مرجع فرمول وجود نداشته باشه.
کلیدواژه : مقدماتی
آواتار
144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • امید ۲۷ بهمن ۱۴۰۱ / ۴:۵۹ ب٫ظ

    باسلام و خدا قوت به شما اساتید محترم
    یک سوال داشتم درخصوص یک پروژه
    من ده فایل اکسل که در یک درایو مشترک در یک سرور برای استفاده ده نفر برای تنظیم برنامه کاری روزانه و ماهانه و سالانه و گرفتن مغایرت های مکانی و زمانی و خروجی های دگیر تهیه کردم که این فایل ها اطلاعات زمان جلسات ، مکان، و شرایط و نتایج آن را به یکدیگر و بخصوص فایل مادر ارسال و دریافت می کنند
    مشکلی که وجود دارد این است که در بعضی از لینک های بوجود امده خطای رفرنس داده می شود و هنگام بررسی فرمول ان سلول اسم شیت مورد نظر بصورت REF# مشاهده می شود و پس از تغییر نام شیت های کلیه فایل ها از نام فارسی به نام انگلیسی، تغییر نام پوشه حاوی فایل های اکسل از نام فارسی به نام انگلیسی باز هم با این مشکل مواجه می شوم
    لطفا راهنمایی بفرمایید
    باتشکر

    • آواتار
      حسنا خاکزاد ۲۳ اردیبهشت ۱۴۰۲ / ۲:۱۲ ب٫ظ

      درود
      احتمالا فایل ها جابجا شدن
      برای این قبیل کارها گوگل شیت یا اکسل آنلاین رو پیشنهاد میکنم

      • امیر ۲۴ مهر ۱۴۰۲ / ۳:۴۹ ب٫ظ

        با سلام و احترام
        فایل من حاوی این پیغام می باشد و فایل که باز می شود در قسمت فرمول گزینه ها غیر فعال می باشد خواهشمند است راهنمایی بفرمایین برای حل مشکل

        • آواتار
          حسنا خاکزاد ۲۴ مهر ۱۴۰۲ / ۷:۱۳ ب٫ظ

          درود بر شما
          چک کنید protect نباشه
          چون راه تشخیصش از همون تب formula هست

          • امیر ۲۵ مهر ۱۴۰۲ / ۹:۴۰ ق٫ظ

            با سلام و احترام
            با سلام و احترام
            در قسمت فرمول سلول خطا نمایش داده می شود ولی متاسفانه فایل باز نمی شود که خطا رفع شود راه حل دیگری دارد

          • سامان چراغی ۱۲ فروردین ۱۴۰۳ / ۱۰:۴۵ ق٫ظ

            درود
            باید سلولی که این خطا داره رو باز کنید و بررسی کنید که کجای فرمول اشتباه هست

  • شقایق ۸ شهریور ۱۴۰۱ / ۱۰:۴۷ ق٫ظ

    خیلی ممنونم من توی محل کار گیر کرده بودم مشکلم حل شد ممنون از راهنماییتون

  • صادق ۲۴ فروردین ۱۴۰۱ / ۱۲:۳۷ ب٫ظ

    ممنون از راهنماییتون

  • ساناز ۵ تیر ۱۴۰۰ / ۲:۵۲ ب٫ظ

    ممنونم از راهنماییتون

  • حامد ۲۱ اسفند ۱۳۹۹ / ۴:۱۴ ب٫ظ

    با سلام
    من یک فایل اکسل دارم با چندین شیت که وقتی حتی کل فایل رو از سیستم خودم جابجا میکنم و داخل یه سیستم دیگه میریزم فرمولهای if تو در تو دیگه کار نمیکنه و صفر نمایش میده حتی محاسبات هر دو سیستم هم بصورت خودکار هستش.روی چندید سیستم کار کردم ولی جوابی نگرفتم.یعنی عملا یک فرمول ساده رو اجرا میکنه ولی سلولهایی که فرمولهای طولانی تری دارند جواب نمیده.
    لطفا راهنمایی بفرمائید.
    تشکر فراوان

    • سامان چراغی ۲۳ اسفند ۱۳۹۹ / ۸:۴۲ ق٫ظ

      سلام
      باید فایلتون بررسی بشه، سوالتون رو به همراه فایل (با داده های غیر واقعی) در گروه تلگرامی اکسل پدیا ارسال کنید.

  • بامداد مهیمنی ۲ بهمن ۱۳۹۹ / ۸:۳۹ ق٫ظ

    هرچی از خدا میخوای بهت بده
    خیلی کمک کردی

  • مهدی ۲۵ مرداد ۱۳۹۹ / ۱۲:۵۵ ب٫ظ

    سلام . خدا پدر مادرتو ببخشه و بیامرزه

  • مجتبی ۱۱ اسفند ۱۳۹۸ / ۱۰:۴۰ ق٫ظ

    با سلام یک سوال داشتم ازتون
    من یه سلول دارم که داخلش تاریخ شمسی درج میشه. این سلول که تاریخ شمسی داخلش هستش باید مثلا۷ روز بهش اضافه بشه و دوباره داخل همون سبول قبلی قرار بگیره. یعنی مقدار جدید داخل سلولی که مقدار قبلی داشت. چطوری اینکار رو انجام بدم. و میخوام یه دکمه یا سلول باشه که وقتی مثلا پر شد یا تیک خورد. بغد از پر شدنش یا تیک خوردنش اتوماتیک مثلا هفت روز اضافه کنه به تاریخ قبلی و تاریخ جدید جای تاریخ قبلی قرار بگیره و همین امر تکرار بشه

    اگه توضیح بیشتری خواستین ایمیل بدین تا من فایل بفرستم

    • آواتار
      حسنا خاکزاد ۱۱ اسفند ۱۳۹۸ / ۱۱:۰۶ ق٫ظ

      درود
      میتونید یک چک باکس بذارید که وقتی تیک خورد، هفت روی به سلول مورد نظر اضافه بشه.
      داخل سلول مورد نظر هم اینو بنویسید:

      در این فرمول، A1 سل لینک چک باکس مورد نظر هست
      ضمن اینکه فرمت سلول رو هم روی تاریخ شمسی تنظیم کنید

      اگر حالت ها بیش از اینه و هر بار یک اتفاق باید بیفته، احتمالا باید برید سمت کد VBA

  • امین ۱۴ بهمن ۱۳۹۸ / ۴:۵۰ ق٫ظ

    با سلام
    در قسمت error checking قسمت circular قابل دسترسی نیست (گزیم هشفیده و نمیشه روش کلیک کرد)
    چجوری میشه منشا خطا رو فهمید ؟
    پ ن :در برنامه هایی که قبلا نوشته بودم واین خطا رو نمیداد هم جدیدا این خطا ظاهر میشه در صورتی که تغییری داخلشون ندادم
    پ ن ۲ : بعضی از سلولها به رنگ قرمز درمیان در صورتی که فقط کپی شدن
    با تشکر

    • آواتار
      حسنا خاکزاد ۱۴ بهمن ۱۳۹۸ / ۱۱:۳۷ ق٫ظ

      درود بر شما
      وقتی قابل کلیک نیست دو حالت رو میشه متصور شد که خطای circular نداریم (که ظاهرا شما پیغام خطا رو دریافت میکنید و این گزینه ممکن نست)
      یک احتمال دیگه هم وجود داره که انقدر این سلول های حاوی خطا تعدادشون زیاده، که در اون قسمت نمایش داده نمیشه.
      اگر اینطور باشه باید فرمول هایی که مشکوک هستید رو خودتون چک کنید

      ضمن اینکه اگر محدوده نامگذاری دارید اونم چک کنید ممکنه اونجا هم همچین خطایی وجود داشته باشه

      پ.ن ۲ ر و متوجه نشدم!

    • مجتبی ۱۱ اسفند ۱۳۹۸ / ۴:۴۴ ب٫ظ

      بسیار سپاسگزارم از راهنمای تون
      الان خروجی این فرمول که داخل این سلول هستش اتوماتیک جای تاریخ قبلی میشینه. کار من برای یه بار نیس مثلا برای هفته بعد نباید تیک داشته باشه. اینو با یه دکمه حل میشه که وقتی روش زدم ۷ روز اضافه کنه
      مشکل من اینه که میخوام تاریخ جدید دقیقا جایگزین تاریخ قبلی بشه. تاریخی که توی سلول بوده نه اینکه با تابع وارد کنم مثلا
      ۱۳۹۸/۰۵/۰۱ توی سلول A1 نوشته شده و توی A2 نوشتم عدد هفت. حالا میخوام مثلا با یه دکمه یا تیک یا هرچیزی دیگه ای(فرقی نداره) هر وقت روش زدم تاریخ سلول A1 رو بگیره و با A2 جمع کنه و “مجددا توی سلول A1 بزاره”. نکته جمله آخر مجددا تاریخ رو توی همون سلول قبلی جایگزین کنه

      • آواتار
        حسنا خاکزاد ۱۹ فروردین ۱۳۹۹ / ۷:۵۵ ب٫ظ

        باید کدنویسی کنید که با زدن دکمه یا هر چیز دیگه این کارر رو انجام بده
        کد ساده جمع زدن رو به یک ئکمه assign کنید

  • اسدی ۸ بهمن ۱۳۹۸ / ۱۱:۰۸ ق٫ظ

    سلام خسته نباشید
    من دستوری که فرموده بودید را اجرا کردم اما هیچ تغییری ایجاد نشد!

    • آواتار
      حسنا خاکزاد ۹ بهمن ۱۳۹۸ / ۶:۵۲ ب٫ظ

      درود
      چه دستوری؟!

  • تقی غفاری ۲۶ دی ۱۳۹۸ / ۱۰:۰۱ ق٫ظ

    ممنون از راهنماییتون خیلی بدردم خورد واقعا ممنون

  • علیرضا ۲۴ دی ۱۳۹۸ / ۱:۱۲ ب٫ظ

    آخرش نوشتین این روش توصیه نمی شود . میشه توضیح بدین یعنی منظورتون اینه روش دوم رو بکار نبریم؟

    • آواتار
      حسنا خاکزاد ۲۹ دی ۱۳۹۸ / ۹:۱۲ ب٫ظ

      کدوم روش دوم؟
      حالت های ارائه شده، روش نیست، نحوه مدیریت خطا بسته به شرایط موضوع هست.
      ضمن اینکه نگفتیم توصیه نمیشه. با توجه به اینکه خطای لوپ رو محاسبه میکنه، باید دقت داشته باشید به فمرول نویس یدقیق چون دیگه این خطا رو نخواهید دید.
      ضمن اینکه کاربردها خاص هستن و باید مسائل مختلفش رو ببینید

  • مهدی ۲۲ تیر ۱۳۹۸ / ۵:۱۸ ب٫ظ

    باسلام و خیلی ممنونم مشکل حل شد

  • صادقی ۱۷ بهمن ۱۳۹۶ / ۱۱:۱۱ ق٫ظ

    مدت ها بود این خطا اذیتم میکرد و ذهنمو به خودش مشغول کرده بود
    مرسی
    تو این مدت کوتاه تا الان چند تا نکته کاربردی یاد گرفتم ازتون
    سپاس فراوان

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان