ارجاع‌های چرخشی (Circular-refrence)

آخرین به‌روزرسانی: 27 دی 1401

در فصل گذشته به بررسی دیگر توابع مالی اکسل پرداختیم, در این فصل به بررسی ارجاع‌های چرخشی (Circular-refrence) می پردازیم.

سؤال‌های این فصل

• اغلب پیام ارجاع چرخشی از اکسل دریافت می‌کنم. آیا این بدان معناست که اشتباه کرده‌ام؟

• چگونه می‌توانم ارجاع‌های چرخشی را اصلاح کنم؟

وقتی مایکروسافت اکسل 2019 پیامی به این مضمون که فایل شما حاوی ارجاعی چرخشی است ارائه می‌دهد، بدان معناست که در کاربرگ نوعی چرخه (حلقه) یا وابستگی میان دو یا چند سلول وجود دارد. مثلاً اگر مقدار موجود در سلولA1 بر مقدار موجود در سلولD3 تأثیر بگذارد مقدار سلولD3‌بر مقدار سلول F6 تأثیر می‌گذارد و در نهایت مقدار سلول F6 بر مقدار سلول A1 تأثیر می‌گذارد( تصویر1-11 طرح یک ارجاع چرخشی را نشان می‌دهد)

تصویر 1-11  یک چرخه که باعث به‌وجودآمدن ارجاع چرخشی شده است.

همان‌طور که به‌زودی خواهید دید، می‌توانید ارجاع چرخشی را با انتخاب گزینهenable iterative calculations )فعال نمودن محاسبه تکراری( را در بخشcalculation options )انتخاب‌های مربوط به محاسبات( در قسمتformulas (فرمول‌ها) واقع در کادر محاوره‌ای   Options(گزینه‌های) اکسل فعال نمایید.

پاسخ به سؤال های این فصل

اغلب پیام ارجاع چرخشی از اکسل دریافت می‌کنم. آیا این بدان معناست که اشتباه کرده‌ام؟

ارجاع چرخشی معمولاً در کاربرگی که منطقاً دارای ثبات است رخ می‌دهد و در آن چند سلول رابطه‌ای چرخشی شبیه به چیزی که در تصویر11-2نشان داده شده، دارند. بیایید نگاهی به مثال ساده‌ای از مسئله‌ای بیندازیم که بدون ایجاد یک ارجاع چرخشی در اکسل نمی‌توان آن را حل کرد.

شرکت کوچکی 1500 دلار در آمد کسب می‌کند و متحمل 1000 دلار هزینه می‌شود. آنها می‌خواهند ده درصد از سود پس از کسر مالیات خود را به سازمان خیریه‌ای ببخشند. نرخ مالیات 40 درصد است. چقدر پول می‌بایست به خیریه بدهند؟ راه‌حل این مسئله در کاربرگSheet1 در فایلی بنام Circular.Xlsx در تصویر2-11 نشان داده شده است.

تصویر 2-11 ارجاع چرخشی می‌تواند وقتی میزان مالیات را محاسبه کنید رخ دهد.

این کار با نام‌گذاری سلول‌های D3:D8 با نام‌های مرتبط سلول‌هایC3:C8 آغاز می‌شود. پس از آن درآمد، نرخ مالیات و هزینه ها در سلول‌های D3:D5 وارد می شوند.برای محاسبه مقدار هدیه شده به خیریه بعنوان ده درصد از سود پس از کسر مالیات، در سلول D6 فرمول =0.1*after_tax_profit را وارد می کنیم. سود پس از از مالیات را در سلول D8  با فرمول =(1-tax_rate)*before-tax-profit محاسبه می کنیم.

اکسل ارجاع چرخشی‌ را در سلولD8 مشخص می‌کند( به گوشه انتهای سمت چپ فایل Circular.xlsx نگاه کنید) چه اتفاقی رخ داده است؟

مقدار مبلغ هدیه شده به خیریه (در سلولD6) بر سود پیش از مالیات(سلول D7) تأثیر می‌گذارد.

سود پیش از مالیات (سلول D7) بر سود پس از کسر مالیات (سلولD8) تأثیر می‌گذارد.

سود پس از کسر مالیات (سلولD8) بر مقدار مبلغ هدیه شده به خیریه تأثیر می‌گذارد.

بنابراین در اینجا در میان سلولهایD6,D7,D8 چرخشی شکل گرفته (در تصویر 2-11 مشخص شده) که باعث به‌وجودآمدن پیام ارجاع چرخشی شده است. کاربرگ منطقا اشکالی ندارد، کار اشتباهی انجام نشده است . با این حال شما می‌توانید از تصویر 2-11 متوجه شوید که اکسل جواب نادرستی برای مسئله اهدای پول به خیریه محاسبه کرده است.

چگونه می‌توانم ارجاع‌های چرخشی را اصلاح کنم؟

ازبین‌بردن یک ارجاع چرخشی کار ساده‌ای است. می‌توان به‌سادگی روی سربرگ file در انتهای سمت چپ ریبون کلیک کرده و بعد گزینه Options را انتخاب کرده تا کادر محاوره‌ای Options یا گزینه‌های انتخابی اکسل باز شود. آنگاه از نوار سمت چپ گزینه formulas را انتخاب کرده و در کادر سمت راست در قسمت  calculation optionsگزینه enable iterative calculations  یا فعال‌کردن محاسبات تکراری را همان‌طور که در تصویر 3-11 نشان‌داده‌شده انتخاب کرده و بعد روی Ok‌کلیک می کنیم.

تصویر 3-11 از گزینه enable iterative calculation  برای حل کردن مشکل ارجاع چرخشی استفاده کنید.

وقتی‌که گزینه Enable Itretive Calculation را انتخاب می‌کنید، اکسل تشخیص می‌دهد که ارجاع چرخشی سیستم زیر را با سه معادله و سه مجهول ایجاد کرده است.

مقدارپول خیریه=0.1*(سود پس از مالیات)

سود پیش از مالیات =درآمد – مقدار پول خیریه – هزینه‌ها

سود پس از مالیات=(1- نرخ مالیات)*(سود قبل از مالیات)

Charity=0.1*(AfterTax Profit)
BeforeTax Profit=Revenue–Charity–Costs
AfterTax Profit= (1–Tax rate)*(BeforeTax Profit)

این سه مجهول مقدارپول خیریه، سود پیش از مالیات و سود پس از مالیات می‌باشند. وقتی‌که گزینه Enable Itretive Calculation‌ را فعال می کنید، اکسل برای جستجوی راه‌حلی برای تمامی معادله های ایجاد شده توسط ارجاع چرخشی محاسبه را تکرار می‌کند ( بر اساس تجربیات خودم در موردارجاع چرخشی می‌بایست از یکصد تکرار را مورد استفاده قرار داد) ارزش هریک از گزینه‌های مجهول معادله با هر تکرار به دیگری توسط فرآیند پیچیده ریاضی ای تغییر می کنند(تکرار گاوس-سیدل) اکسل چنانچه حد اکثر تغییر در هریک از سلول‌های کاربرگ از یک تغییر به تغییر دیگری کوچکتر از حد اکثر ارزش تغییر( به شکل از پیش تعیین شده 0.001) باشد تغییر را متوقف می‌کند. شما می‌توانید حد اکثر تغییر را به عدد کوچکتری مثلاً 0.000001 کاهش دهید. اگر حد اکثر تغییرات را کاهش ندهید احتمالا در می یابید که اکسل یک مقدار خاص مثلاً 5.001 را به سلولی که می‌بایست برابر با 5 با شد تخصیص داده است و این صحصح نیست. همچنین ممکن است برخی کاربرگ ها نیاز به بیش از 100 تکرار داشته باشند تا بالاخره راه‌حلی برای ارجاع چرخشی شان پیدا شود هرچند در این مثال ارجاع چرخشی بلافاصله حل شده و می‌توانید آن را در تصویر 4-11 مشاهده کنید.

تصویر 4-11 اکسل محاسبات را آن‌قدر ادامه می‌دهد تا بتواند مورد ارجاع چرخشی را حل کند

توجه کنید که میزان 28.30 دلار خیریه حالا دقیقاً ده در صد از سود پس از مالیات 283.01 دلار می‌باشد. تمامی سلول‌های دیگر در این کاربرگ اکنون به طرز صحیح مورد محاسبه قرار گرفته‌اند.

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

حالا مثالی دیگر از ارجاع چرخشی. در هر فرمول اکسل می‌توان به ستون یا ردیفی با نام خودش ارجاعی داد. مثلاً فرمول: AVERAGE(B:B) میانگین تمامی سلول‌های ستون B را محاسبه می‌کند. فرمول =AVERAGE(1:1) میانگین تمام سلول‌های ردیف یک را محاسبه می‌کند. این راه میان‌بر برای زمانی که به طور مدام داده‌های جدیدی را (مثل فروش ماهیانه) در ستون یا ردیفی وارد می‌کنید بسیار مفید و کاربردی است. مشکل اینجاست که اگر این فرمول را در ستون یا ردیفی که به آن ارجاع شده وارد کنید آن‌وقت ارجاعی چرخشی ایجاد کرده‌اید؛ بنابراین با فعال‌کردن گزینه Enable Itrative Calculation ارجاع‌های چرخشی‌ ازاین‌دست به‌راحتی حل می‌شوند.

مسئله‌های این فصل

شرکتی پیش از پرداخت پاداش و مالیات ایالتی و مالیات فدرال کارمندان، سودی 60000 دلاری کسب می‌کند. این شرکت به کارمندان پاداشی برابر با 5 درصد سود پس از کسر مالیات می‌پردازد. مالیات ایالتی 5 درصد سود (بعد از اینکه پاداش‌ها پرداخت شدند) می‌باشد. مالیات فدرال 40 درصد سود (پس از اینکه پاداش‌ها و مالیات ایالتی پرداخت شدند) می‌باشد. مبلغ پرداخت شده پاداش، مالیات ایالتی و مالیات فدرال را مشخص کنید.

در یکم ژانویه سال 2002 ، 500 دلار داشتم. در پایان هر ماه دو درصد سود به آن تعلق می گیرد ، سود هر ماه بر اساس میانگین مانده ابتدای ماه و انتهای آن محاسبه می‌شود. پس از دوازده ماه چقدر پول خواهم داشت؟

مسیر پرواز هواپیمای من به شرح زیر است: هیوستون – لس‌آنجلس – سیاتل – مینیاپولیس – هیوستون. در هر مرحله از سفر میزان سوخت مصرفی (که به مقیاس مایل بر گالن حساب می‌شود) برابر با

(میانگین استفاده از سوخت در مسیر)40-0.02* می‌باشد.

متوسط مصرف سوخت درهر مسیر برابراست با (سوخت اولین مسیر+سوخت نهایی مسیر) 0.5*.

هواپیمای من سفرش را از هیوستون با 1000 گالن بنزین آغاز می‌کند. مقصد پرواز در هریک از مراحل سفر به شرح زیر است.

هنگامی‌که به هیوستون برگردم چند گالن سوخت باقی‌مانده است؟

یک روش معمول برای تخصیص هزینه‌ها برای پشتیبانی از بخش‌های مختلف، روش تخصیص هزینه متقابل نام دارد. این روش را به‌آسانی می‌توان با استفاده از ارجاع چرخشی اجرا نمود. برای تصور کردن این موضوع فرض کنید که شرکت ویجدکو برای پشتیبانی دو بخش دارد. بخش حسابداری و بخش مشاوره. ویجدکو دو بخش تولیدی هم دارد: شعبه 1 و شعبه 2. شرکت ویجدکو تصمیم گرفته که 600000 دلار هزینه عملیاتی بخش حسابداری و 116000 هزینه عملیاتی بخش مشاوره را به دو بخش دیگر تخصیص دهد. درصد زمان بخش حسابداری و مشاوره که توسط هریک از بخش‌های شرکت مورداستفاده گرفته عبارت است از:

چه مقدار از هزینه‌های حسابداری و مشاوره باید به سایر بخش‌های کارخانه تخصیص داده شود؟ برای حل این مسئله می‌بایست دو مقدار کل هزینه اختصاص‌داده‌شده به بخش حسابداری و کل هزینه اختصاص‌داده‌شده به بخش مشاوره را مشخص نمایید: کل هزینه اختصاص‌داده‌شده به بخش حسابداری برابر است با (کل هزینه تخصیص‌داده‌شده به بخش مشاوره) 600000+.1*دلار چراکه ده درصد همه کارهای مشاوره برای بخش حسابداری انجام شده است. مسئله مشابهی را می‌توان برای کل هزینه اختصاص‌داده‌شده به بخش مشاوره نیز نوشت. اکنون دیگر می‌بایست قادر باشید تا مقدار صحیح هزینه‌های تخصیص‌داده‌شده از بخش حسابداری و مشاوره به دو بخش دیگر کارخانه را حساب کنید.

سالی را با 200 دلار پس‌انداز شروع و در طول سال 100 دلار دریافت می‌کنیم. همچنین در انتهای سال ده درصد سود دریافت خواهیم کرد که این سود بر اساس موجودی اولیه حساب شده است. در این مورد در پایان سال 320 دلار خواهیم داشت. چنانچه سود بر اساس میانگین موجودی ابتداً و انتهای سال به ما تعلق گیرد، موجودی پایان سال را محاسبه کنید.