به طور کلی پذیرفته شده است که پیوندهای حلقوی در اکسل یک بیان اشتباه است. در واقع ، اغلب این واقعیت دارد ، اما هنوز هم نه همیشه. بعضی مواقع کاملاً عمداً اعمال می شوند. بیایید دریابیم که پیوندهای حلقوی چیست ، چگونه آنها را ایجاد کنیم ، چگونه پیوندهای موجود را در یک سند پیدا کنیم ، چگونه با آنها کار کنیم یا در صورت لزوم چگونه آنها را حذف کنیم.
با استفاده از منابع مدور
اول از همه ، بیایید دریابیم که یک حلقه دایره چیست. در حقیقت ، این عبارتی است که از طریق فرمول موجود در سلولهای دیگر ، به خود اشاره دارد. همچنین می تواند پیوندی باشد که در عنصر ورق واقع شده است.
لازم به ذکر است که به طور پیش فرض ، نسخه های مدرن اکسل بطور خودکار روند انجام یک عمل حلقوی را مسدود می کنند. این به این دلیل است که چنین عباراتی بسیار غلط هستند و حلقه زدن یک روند ثابت بازگو و محاسبه را ایجاد می کند و این باعث ایجاد بار اضافی بر روی سیستم می شود.
پیوند مدور ایجاد کنید
حال بیایید ببینیم چگونه می توان یک عبارت ساده چرخه ای را ایجاد کرد. این پیوندی است که در همان سلول قرار دارد که به آن مراجعه می کند.
- یک برگه را انتخاب کنید A1 و عبارت زیر را در آن بنویسید:
= A1بعد روی دکمه کلیک کنید وارد شوید روی صفحه کلید
- پس از آن ، کادر گفتگوی هشدار بیان چرخه ظاهر می شود. روی دکمه موجود در آن کلیک کنید. "خوب".
- بنابراین ، ما یک عمل حلقوی بر روی یک ورق دریافت کردیم که در آن سلول به خود مراجعه می کند.
بیایید کمی کار را پیچیده کنیم و از چندین سلول یک عبارت حلقوی ایجاد کنیم.
- در هر عنصر ورق ، یک عدد بنویسید. بگذار سلول باشد A1، و شماره 5.
- به سلول دیگر (B1) عبارت را بنویسید:
= C1 - در عنصر بعدی (C1) ما چنین فرمولی را می نویسیم:
= A1 - پس از آن به سلول باز می گردیم A1که در آن شماره تنظیم شده است 5. ما به عنصر موجود در آن اشاره می کنیم. B1:
= B1روی دکمه کلیک کنید وارد شوید.
- بنابراین ، حلقه بسته شد و ما یک مرجع دایره ای کلاسیک گرفتیم. بعد از بسته شدن پنجره هشدار ، می بینیم که این برنامه پیوند حلقوی را با فلش های آبی روی صفحه مشخص کرده است ، که به آنها پیکان ردیابی گفته می شود.
حالا بیایید با استفاده از یک جدول مثال ، به ایجاد یک عبارت چرخه ای برویم. ما یک جدول فروش مواد غذایی داریم. از چهار ستون تشکیل شده است که در آن نام کالا ، تعداد کالاهای فروخته شده ، قیمت و میزان درآمد حاصل از فروش کل حجم ذکر شده است. جدول ستون آخر قبلاً فرمول دارد. آنها درآمد را با ضرب مقدار با قیمت محاسبه می کنند.
- برای حلقه فرمول در خط اول ، عنصر برگه را با مقدار مورد اول در حساب انتخاب کنید (B2) به جای مقدار استاتیک (6) فرمول را در آنجا وارد می کنیم که با تقسیم مقدار کل ، مقدار کالا را در نظر می گیریم (D2) با قیمت (C2):
= D2 / C2روی دکمه کلیک کنید وارد شوید.
- ما اولین حلقه حلقوی را بدست آوردیم ، رابطه ای که معمولاً با پیکان ردیابی نشان داده می شود. اما همانطور که مشاهده می کنید ، نتیجه نادرست و برابر با صفر است ، همانطور که قبلاً نیز گفته شد ، اکسل اجرای عملیات چرخه ای را مسدود می کند.
- عبارات را در مورد سایر سلولهای ستون با تعداد محصولات کپی کنید. برای انجام این کار ، مکان نما را در گوشه سمت راست پایین عنصری که از قبل حاوی فرمول است قرار دهید. مکان نما به صلیب تبدیل می شود که معمولاً مارکر پر کننده نامیده می شود. دکمه سمت چپ ماوس را نگه دارید و این صلیب را تا انتهای جدول بکشید.
- همانطور که مشاهده می کنید ، این عبارت در کلیه عناصر ستون کپی شد. اما ، تنها یک رابطه با یک پیکان ردیابی مشخص شده است. این را برای آینده توجه کنید.
پیوندهای دایره ای را جستجو کنید
همانطور که در بالا دیدیم ، در همه موارد برنامه رابطه مرجع دایره ای با اشیاء را نشان نمی دهد ، حتی اگر روی صفحه باشد. با توجه به اینکه اکثریت قریب به اتفاق عملیات چرخه ای مضر هستند ، باید از بین بروند. اما برای این کار ابتدا باید یافت. چگونه این عبارات را انجام دهیم اگر عبارات با خط با فلش مشخص نشده باشند؟ بیایید با این مشکل کنار بیاییم.
- بنابراین ، اگر هنگام راه اندازی پرونده اکسل ، یک پنجره اطلاعاتی باز می شود که حاوی یک لینک دایره ای است ، توصیه می شود آن را پیدا کنید. برای انجام این کار ، به برگه بروید فرمول ها. روی روبان روی مثلث که در سمت راست دکمه قرار دارد ، کلیک کنید "بررسی خطاها"واقع در بلوک ابزار وابستگی فرمول. منویی باز می شود که در آن شما باید روی مورد معلق بگیرید "پیوندهای حلقوی". پس از آن ، لیست آدرس عناصر ورق که در آن برنامه عبارات حلقوی را شناسایی کرده است ، در فهرست بعدی باز می شود.
- هنگامی که روی یک آدرس خاص کلیک می کنید ، سلول مربوطه روی برگه انتخاب می شود.
روش دیگری برای فهمیدن پیوند دایره ای وجود دارد. پیام مربوط به این مشکل و آدرس عنصر حاوی این عبارت در سمت چپ نوار وضعیت قرار دارد که در پایین پنجره اکسل قرار دارد. درست است ، بر خلاف نسخه قبلی ، اگر تعداد زیادی وجود داشته باشد ، نوار وضعیت آدرس همه عناصر حاوی پیوندهای دایره ای را نشان نمی دهد ، اما فقط یکی از آنها ظاهر شده است.
علاوه بر این ، اگر شما در کتابی هستید که دارای یک عبارت چرخه ای باشد ، نه روی برگه ای که در آن قرار دارد ، بلکه از طرف دیگر ، در این حالت فقط پیام مربوط به وجود خطا بدون آدرس در نوار وضعیت نمایش داده می شود.
درس: نحوه یافتن پیوندهای دایره ای در اکسل
پیوندهای حلقوی را برطرف کنید
همانطور که گفته شد ، در اکثریت قریب به اتفاق موارد ، عملیات چرخه ای شر است که باید از آنها دفع شود. بنابراین منطقی است که پس از یافتن اتصال حلقوی ، اصلاح آن لازم باشد تا فرمول به فرم عادی برسد.
برای برطرف کردن وابستگی چرخه ای ، لازم است که کل اتصال سلول ها ردیابی شود. حتی اگر چک یک سلول خاص را نشان دهد ، خطا ممکن است در خودش نباشد ، بلکه در عنصر دیگری از زنجیره وابستگی باشد.
- در مورد ما ، با وجود این واقعیت که برنامه به درستی به یکی از سلولهای حلقه اشاره کرده است (D6) ، خطای واقعی در سلول دیگری نهفته است. یک عنصر را انتخاب کنید D6برای پیدا کردن اینکه کدام سلول مقدار را از آن خارج می کند. ما در نوار فرمول به بیان نگاه می کنیم. همانطور که مشاهده می کنید ، مقدار در این عنصر ورق با ضرب محتوای سلول ها تشکیل می شود B6 و C6.
- برو به سلول C6. آن را انتخاب کرده و به خط فرمول نگاه کنید. همانطور که مشاهده می کنید ، این مقدار استاتیک معمولی است (1000) ، که محصولی از محاسبه فرمول نیست. بنابراین ، با اطمینان می توان گفت که عنصر مشخص شده حاوی خطایی نیست که باعث ایجاد عملیات چرخه ای شود.
- به سلول بعدی بروید (B6) پس از برجسته سازی در نوار فرمول ، می بینیم که حاوی یک عبارت محاسبه شده است (= D6 / C6) ، که داده ها را از دیگر عناصر جدول ، به ویژه ، از سلول می کشد D6. بنابراین سلول D6 به داده های مورد اشاره دارد B6 و برعکس ، که باعث حلقه شدن می شود.
در اینجا ما رابطه را خیلی سریع محاسبه کردیم ، اما در واقعیت مواردی وجود دارد که تعداد زیادی سلول در فرآیند محاسبه دخیل هستند و نه سه عنصر ، مانند ما. سپس جستجو می تواند زمان زیادی را ببرد ، زیرا شما باید هر یک از عناصر چرخه را مطالعه کنید.
- اکنون باید بفهمیم که در کدام سلول (B6 یا D6) حاوی یک خطا است. اگرچه ، به طور رسمی ، این حتی یک اشتباه نیست ، بلکه صرفاً استفاده بیش از حد از لینک ها ، که منجر به حلقه می شود. در طی فرایند تصمیم گیری که سلول باید ویرایش شود ، باید منطق اعمال شود. هیچ الگوریتم روشنی وجود ندارد. در هر حالت ، این منطق متفاوت خواهد بود.
به عنوان مثال ، اگر در جدول ما مقدار کل باید با ضرب مقدار کالاهای فروخته شده واقعاً بر اساس قیمت آن محاسبه شود ، می توان گفت که پیوندی که مقدار کل مبلغ فروش را محاسبه می کند ، به وضوح اضافی است. بنابراین ، ما آن را حذف کرده و آن را با یک مقدار استاتیک جایگزین می کنیم.
- اگر روی صفحه باشد ، یک عمل مشابه را در مورد سایر عبارات حلقوی انجام می دهیم. پس از حذف کامل همه منابع دایره ای از کتاب ، پیام در مورد وجود این مشکل باید از نوار وضعیت ناپدید شود.
علاوه بر این ، آیا عبارات حلقوی کاملاً حذف شده است ، می توانید با استفاده از ابزار بررسی خطا متوجه شوید. برو به برگه فرمول ها و روی مثلثی که قبلاً برای ما آشنا است در سمت راست دکمه کلیک کنید "بررسی خطاها" در گروه ابزار وابستگی فرمول. اگر در منویی که باز می شود ، "پیوندهای حلقوی" فعال نخواهد بود ، این بدان معنی است که ما همه اشیاء از سند را حذف کرده ایم. در غیر این صورت ، لازم است روش حذف در مورد عناصر موجود در لیست به همان روشی که قبلاً در نظر گرفته شده بود ، اعمال شود.
اجازه حلقه برگشت
در قسمت قبلی درس ، ما عمدتاً در مورد چگونگی برخورد با پیوندهای دایره ای یا نحوه یافتن آنها صحبت کردیم. اما ، در ابتدا این گفتگو نیز در مورد این واقعیت بود که در برخی موارد ، برعکس ، می توانند توسط کاربر مفید و آگاهانه مورد استفاده قرار گیرند. به عنوان مثال ، اغلب این روش برای محاسبات تکراری در ساخت مدل های اقتصادی استفاده می شود. اما مشکل این است که ، صرف نظر از اینکه شما از یک عبارت دایره ای آگاهانه یا ناخودآگاه استفاده می کنید ، اکسل به طور پیش فرض باز هم عملکرد را روی آنها مسدود می کند ، تا منجر به اضافه بار بیش از حد سیستم نشود. در این حالت ، مسئله غیرفعال کردن چنین قفلی موضوعیت پیدا می کند. بیایید ببینیم چگونه این کار را انجام دهیم.
- اول از همه ، به برگه بروید پرونده برنامه های اکسل
- بعد روی مورد کلیک کنید "گزینه ها"واقع در سمت چپ پنجره ای که باز می شود.
- پنجره گزینه های Excel شروع می شود. باید برویم به برگه فرمول ها.
- در پنجره ای که باز می شود امکان اجرای عملیات حلقوی امکان پذیر است. به بلوک سمت راست این پنجره می رویم ، جایی که خود تنظیمات اکسل در آن قرار دارد. ما با بلوک تنظیمات کار خواهیم کرد پارامترهای محاسبهکه در قسمت بالا واقع شده است
برای فعال کردن استفاده از عبارات حلقوی ، کادر کنار پارامتر را علامت بزنید محاسبه Iterative را فعال کنید. علاوه بر این ، تعداد مجاز تکرارها و خطای نسبی را می توان در همان بلوک تنظیم کرد. به طور پیش فرض ، مقادیر آنها به ترتیب 100 و 0.001 است. در بیشتر موارد ، این پارامترها نیازی به تغییر ندارند ، اگرچه در صورت لزوم یا در صورت تمایل ، می توانید در این قسمت ها تغییراتی ایجاد کنید. اما در اینجا باید در نظر گرفته شود که تکرارهای بیش از حد زیادی می توانند منجر به بارگیری جدی در برنامه و سیستم به طور کلی شوند ، به خصوص اگر با پرونده ای کار می کنید که حاوی بسیاری از عبارات چرخه ای است.
بنابراین کادر کنار پارامتر را علامت بزنید محاسبه Iterative را فعال کنید، و سپس برای اجرای تنظیمات جدید ، روی دکمه کلیک کنید "خوب"واقع در پایین پنجره گزینه های Excel.
- پس از آن ، ما به طور خودکار به برگه کتاب فعلی می رویم. همانطور که مشاهده می کنید ، در سلول هایی که فرمول های چرخه ای در آن قرار دارند ، اکنون مقادیر به درستی محاسبه می شوند. این برنامه محاسبات موجود در آنها را مسدود نمی کند.
با این وجود شایان ذکر است که از شمول عملیات چرخه ای نباید سوءاستفاده کرد. از این ویژگی فقط در مواردی استفاده کنید که کاربر کاملاً از ضرورت خود اطمینان داشته باشد. گنجاندن نامعقول عملیات چرخه ای نه تنها می تواند منجر به بار بیش از حد سیستم شود و محاسبات را هنگام کار با یک سند کند کند بلکه کاربر می تواند سهواً یک عبارت چرخه ای اشتباه را وارد کند که به طور پیش فرض بلافاصله توسط برنامه مسدود می شود.
همانطور که می بینیم ، در اکثریت قریب به اتفاق موارد ، مراجع دایره ای پدیده ای هستند که باید به آن توجه شود. برای این ، قبل از هر چیز ، لازم است که رابطه حلقوی خود را تشخیص داده ، سپس سلول را در جایی که خطا وجود دارد محاسبه کرده و در آخر با انجام تنظیمات مناسب آن را از بین ببرید. اما در برخی موارد ، عملیات چرخه ای می تواند در محاسبات مفید باشد و توسط کاربر آگاهانه انجام شود. اما حتی در این صورت ، ارزش دارد که با احتیاط به استفاده از آنها نزدیک شوید ، به درستی تنظیم Excel و دانستن اندازه گیری در اضافه کردن چنین لینک هایی ، که در صورت استفاده به صورت انبوه می توانند باعث کندی سیستم شوند.
