ارزیابی فرمول ها در اکسل: راهنمای کامل ابزار Formula Auditing در اکسل

گاهی اوقات پیش می آید که یک فایل اکسل با فرمول های پیچیده دریافت می کنیم، قطعا در نگاه اول، درک نحوه محاسبات و فرمول های به کار رفته در آن چالش برانگیز است. در این مواقع، ابزارهای ارزیابی و عیب یابی فرمول (Formula Auditing) در اکسل بسیار مفید هستند.

برای درک ارتباط بین فرمول ها و سلول های اکسل، یا می توانید راه طولانی را انتخاب کنید و سلول ها را یک به یک بررسی کنید، یا از ابزارهای Formula Auditing در اکسل برای بررسی فرمول ها استفاده کنید تا سریع متوجه روابط بین سلول ها و فرمول ها شوید.

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

• ردیابی سلول های موثر بر فرمول (Precedents)
• ردیابی سلول های وابسته به فرمول (Dependents)
• نمایش فرمول های موجود در صفحه کاری
• ارزیابی فرمول ها (Evaluate Formula)
• حذف فلش های نشان دهنده روابط بین سلول ها

در مطلب آموزش فرمول نویسی در اکسل، نکات کاربردی بیشتری درباره این ویژگی های ابزار Formula Auditing خواهید یافت.

ابزارهای عیب یابی فرمول در اکسل مجموعه ای از قابلیت ها هستند که به کاربران کمک می کنند فرمول های موجود در صفحات اکسل خود را بررسی و تحلیل کنند. این ابزارها در تب Formulas در نوار ابزار اکسل و در گروه Formula Auditing قرار دارند. با استفاده از این ابزارها، کاربران می توانند خطاهای فرمول ها را سریع شناسایی و برطرف کنند و از بروز اشتباهات جلوگیری نمایند.

اکسل هفت ابزار عیب یابی فرمول دارد که در این مقاله آنها را بررسی خواهیم کرد. این ابزارها از این دست هستند:

1. Trace Precedents: مشخص می کند که کدام سلول ها در یک فرمول استفاده شدند.
2. Trace Dependents: نشان می دهد کدام سلول ها به فرمول وابسته هستند.
3. Remove Arrows: حذف فلش های نمایش دهنده ارتباط بین سلول ها.
4. Show Formulas: نمایش تمامی فرمول های موجود در شیت اکسل.
5. Error Checking: بررسی خودکار فرمول ها برای تشخیص خطاها و ارائه پیشنهادات اصلاحی.
6. Evaluate Formula: نمایش نحوه ارزیابی مرحله به مرحله فرمول ها توسط اکسل.
7. Watch Window: مشاهده تغییرات لحظه ای در فرمول ها بدون نیاز به تغییر موقعیت شیت

دستور Trace Precedents در اکسل، مشخص می کند چه سلول هایی در یک فرمول استفاده شدند. این سلول ها به عنوان سلول های مقدم (Precedent Cells) شناخته می شوند. اگر فرمولی در شیت شما خطایی نشان می دهد، اولین کاری که برای عیب یابی آن باید انجام دهید بررسی سلول هایی است که در آن فرمول استفاده شدند.

بگذارید این مسئله را با یک مثال ساده روشن کنیم. فرض کنید درآمد خالص یک کارمند ۳۱ میلیون تومان در ماه است. برای اینکه ببینیم کدام سلول ها در محاسبه این مقدار نقش دارند، کافیست مراحل زیر را دنبال کنید:

روی سلول B13 که مقدار درآمد خالص را نشان می دهد، کلیک کنید. سپس به تب Formulas بروید و در گروه Formula Auditing گزینه Trace Precedents را انتخاب کنید.

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

اگر رنگ فلش آبی باشد، یعنی هیچ خطایی در فرمول وجود ندارد. اما اگر رنگ فلش قرمز باشد، نشان دهنده خطا در فرمول است.

این فلش های ردیابی نشان می دهند کدام سلول ها برای محاسبه مقدار سلول (B13) استفاده شدند. در این مثال، سلول های B8 و B11 در محاسبه B13 نقش دارند. پس این سلول‌ها، Precedent Cells برای B13 محسوب می شوند و رابطه آنها از طریق ابزار Trace Precedents مشخص می شود.

دستور Trace Dependents در اکسل، سلول هایی که به یک سلول خاص وابسته هستند را مشخص می کند. پس برای مشاهده سلول هایی که در فرمول خود از مقدار یک سلول خاص استفاده می کنند، باید از این دستور استفاده کنید. برای مثال حقوق ناخالص کارمند را در سلول B8 داریم و می خواهیم ببینیم چه سلول هایی در محاسبات خود از حقوق ناخالص استفاده کردند. برای این کار مراحل زیر را انجام دهید:

روی سلول B8 که حقوق ناخالص را نشان می دهد، کلیک کنید.
به تب Formulas بروید و گزینه Trace Dependents را از گروه Formula Auditing انتخاب کنید.

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

فلش ردیابی نشان می دهد که سلول هایی وجود دارند که در محاسبات خود به مقدار سلول B8 وابسته هستند. در این مثال، سلول‌های B11 و B13 که به ترتیب مالیات حقوق قابل پرداخت و درآمد خالص هستند، به سلول B8 وابسته هستند. رابطه بین این سه سلول هم از طریق ابزار Trace Dependents مشخص می شود.

پس از بررسی روابط بین سلول ها، برای حذف فلش های ردیابی، کافیست از تب Formulas روی گزینه Remove Arrows کلیک کنید. برای حذف فلش های ردیابی سه گزینه خواهید داشت:

• Remove Arrows: تمام فلش های ردیابی که توسط Trace Precedents و Trace Dependents ایجاد شدند، حذف می شوند.
• Remove Precedent Arrows: فقط فلش های ردیابی Trace Precedents حذف می شوند.
• Remove Dependent Arrows: فقط فلش های ردیابی Trace Dependents حذف می شوند.

به طور پیش فرض، توابع و فرمول های اکسل نتیجه محاسبات خود را درون سلول های مربوطه نمایش می دهند. به جای اینکه هر بار جداگانه روی هر سلول کلیک کنید تا فرمول آن را در نوار فرمول ببینید، می توانید از گزینه Show Formulas در بخش Formula Auditing استفاده کنید. با انتخاب این گزینه، تمام سلول های دارای فرمول در شیت اکسل، به جای اینکه نتیجه محاسبات را نمایش دهند، خود فرمول را نشان خواهند داد.

گاهی اوقات خطاهایی در فرمول ها وجود دارد و اکسل در سلول ها به جای نتیجه، خطاهای #DIV/0! یا #VALUE! را نمایش می دهد. دستور Error Checking در گروه Formula Auditing چند حالت مختلف برای بررسی جزئیات خطاها در اختیار شما قرار می دهد.

برای اینکه عملکرد این ویژگی را ببینید، یک مقدار تصادفی در سلول B10 وارد کنید. با انجام این کار، دو سلول وابسته B11 و B13 مقدار #VALUE! را نمایش خواهند داد.

حالا برای بررسی خطاهای این سلول ها به این ترتیب عمل کنید:

روی سلول B11 کلیک کنید.
به تب Formulas بروید و از گروه Formula Auditing گزینه Error Checking را انتخاب کنید.
پنجره Error Checking مانند تصویر زیر برای شما باز خواهد شد.

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

• Help on this Error: صفحه ای از وبسایت مایکروسافت را باز می کند که توضیحات و پیشنهاداتی برای رفع خطا ارائه می دهد.
• Show Calculation Steps: این گزینه شما را مرحله به مرحله در فرمول راهنمایی می کند تا مشخص شود خطا مربوط به کدام قسمت است. در فرمول های پیچیده تر، می‌توانید روی Evaluate کلیک کنید تا سلول خطادار مشخص شود.
• Ignore Error: اکسل این خطا را نادیده می گیرد و دیگر آن را به صورت یک خطا علامت گذاری نمی کند.
• Edit in Formula Bar: این گزینه امکان ویرایش مستقیم فرمول در Formula Bar را فراهم می کند.

گاهی اوقات، یک مقدار یا نتیجه در اکسل از چندین محاسبه و فرمول میانجی حاصل می شود. برای درک نحوه‌ محاسبه‌ نتیجه‌ی نهایی، می‌توان از قابلیت Evaluate Formula در اکسل استفاده کرد که هر بخش از فرمول را به صورت جداگانه ارزیابی می کند.

فرض کنید که می خواهید فرمول درآمد خالص را ارزیابی کنید. برای این کار مراحل زیر را انجام دهید:

روی سلول B13 کلیک کنید.
در تب Formulas از گروه Formula Auditing گزینه Evaluate Formula را انتخاب کنید.

پنجره Evaluate Formula باز خواهد شد و نشان می دهد که مقدار سلول B13 از تفریق مقدار سلول B11 از B8 به دست آمده است.

روی Evaluate کلیک کنید تا مقدار سلول B8 را که در این مثال ۳۶ میلیون و ۵۰۰ تومان است، ببینید.

با کلیک بر روی Step In، می توانید مقادیر سلول های قبلی که در محاسبه B8 نقش دارند را مشاهده کنید. در اینجا با دو بار زدن روی Step in، خواهید دید که مقدار B8 مجموع مقادیر موجود در سلول های B2 تا B6 است.

برای اینکه با ویژگی Evaluate Formula در اکسل بیشتر آشنا شوید، پیشنهاد می کنیم مقاله عیب یابی فرمول در اکسل را مطالعه کنید.

ابزار Watch Window در اکسل مستقیما به ارزیابی و عیب یابی فرمول ها نمی پردازد، اما ابزاری مفید و کاربردی، به ویژه برای شیت های سنگین، است. این ابزار به شما امکان می دهد که مقدار برخی از سلول های خاص را بدون نیاز به اسکرول کردن و یافتن آن سلول ها، به‌صورت مداوم زیر نظر داشته باشید. برای این کار کافیست از تب Formulas روی Watch Window کلیک کنید. سپس، درست مانند تصویر زیر، سلول های مورد نظر خود را انتخاب و روی Add کلیک کنید.

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

برای آشنایی کامل با این ابزار فوق العاده اکسل و کاربردهای آن، پیشنهاد می کنیم مطلب ابزار Watch Window در اکسل را حتما مطالعه کنید.

حال که با مزایای ابزارهای Formula Auditing اکسل برای بررسی روابط بین فرمول ها و سلول ها آشنا شدیم، می توانیم به جای استفاده از ماوس، از کلیدهای میانبر برای اجرای این دستورات استفاده کنیم.

استفاده از میانبرهای صفحه کلید، سرعت کار را افزایش می دهد و در نتیجه زمان انجام وظایف کوتاه تر می شود.

• کلیدهای ] + Ctrl برای ردیابی سلول های مقدم
• کلیدهای [ + Ctrl برای ردیابی سلول های وابسته
• کلیدهای ~ + Ctrl برای نمایش فرمول ها

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