تکنیک های اشکال زدایی فرمول ها

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

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

معرفی تکنیک های اشکال زدایی فرمول ها در اکسل

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

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

پس از مقدمه ی فوق، در شکل زیر تمام تکنیک های اشکال زدایی فرمول ها در اکسل نشان داده شده است، البته لازم به ذکر است در اکسل ابزارهای زیادی وجود دارد که میتوان نام آنها را در این لیست گنجاند، ولی در اینجا تنها به ذکر همین ابزار به عنوان متداول ترین ها بسنده میکنیم:

توابع IS و توابع ترکیبی IF در اشکال زدایی فرمول ها

در مقاله ی توابع اطلاعات در اکسل (قسمت دوم) آموختیم توابع بررسی محتوا در اکسل شامل سه تابع ISODD، ISEVEN و ISFORMULA و توابع IS هستند. توابع IS در اکسل شامل توابع ISBLANK، ISERR، ISERROR، ISLOGICAL، ISNA، ISNONTEXT، ISNUMBER، ISREF و ISTEXT میباشند. تمامی این توابع ساختار یکسانی دارند و خروجی آنها Boolean میباشد، توابع IS در اکسل تنها یک ورودی میگیرند و آن را برای شرط خاصی بررسی میکنند و خروجی تمام این توابع تنها TRUE یا FALSE میباشد.

توصیه میگردد حتماً مقاله ی مربوط به معرفی توابع IS را مطالعه نمایید.

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

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

با استفاده از تابع TYPE میتوانیم نوع داده های وارد شده به سلول و یا خروجی فرمول ها را بررسی نماییم و در صورت مغایرت با نوع مورد نظر خود اقدام مناسب را صورت دهیم.

فرض کنید قرار است کاربر در سلول A1 تعداد کالای مورد درخواست را وارد کند و این عدد در فرمول های دیگر برای محاسبات پارامترهای مورد نیاز استفاده شود، حال اگر کاربر A1 را خالی بگذارد و یا سهواً داده ای غیر عددی در آن وارد نماید مسلماً فرمول های ما با خطا مواجه خواهند شد، برای این کار میتوانیم در سلول A2 فرمول مشابه فرمول زیر بنویسیم:

=IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data, Number Only please”; “Let’s Do Next Step”)

در ساختار فرمول بالا از تابع OR (توابع منطقی در اکسل) و توابع TYPE و ISBLANK استفاده شده، به این ترتیب در صورتی که سلول A1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده میشود. اگر قرار بود عدد وارد شده در A1 به عنوان ورودی تابع POWER (توابع ریاضی) باشد، مسلماً غیر عددی بودن A1 باعث بروز خطا در فرمول میگردید، در چنین موردی ساختاری مانند ساختار زیر را میتوان استفاده کرد:

=IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data in A1″;POWER(A1;2))

در فرمول بالا در صورتی که A1 داده ای غیر عددی باشد پیام خطا نوشته میشود و در غیر اینصورت روال عادی فرمول نویسی طی میشود.

همانطور که از مثال های بالا متوجه شدیم از ترکیب تابع IF و توابع IS و دیگر توابع اطلاعات در اکسل میتوان ساختارهای بسیار مفید در جلوگیری از ورود خطاها و داده های اشتباه در فرمول ها تشکیل داد. در این میان دو تابع ISERR و ISERROR از اهمیت ویژه ای برخوردارند، زیرا این دو تابع در نهایت میتوانند از نشان داده شدن پیام های خطا در فایل اکسل شما جلوگیری کنند و به عبارتی خطاها را به دام اندازند. ساختارهای زیر کمک میکنند که با استفاده از این توابع و تابع IF از نشان داده شدن پیام خطا در فایل های اکسل خود جلوگیری نمایید:

=IF(ISERROR(A1);”This Formula Contains Error”; “Acceptable Outcome”)

حتی در فرمول نویسی حرفه ای تر میتوان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای N/A# در خروجی تابع VLOOKUPحتماً میتوان این نتیجه را گرفت که عبارت مورد نظر در جدول مورد جستجو وجود نداشته، این پیام را میتوان از ساختاری مشابه ساختار زیر به کاربر داد:

=IF(ERROR.TYPE(A1)=7;”The Required Item is not in Table”;””)

در فرمول بالا در صورت بروز خطای خطای N/A# پیامی برای کاربر نمایش داده میشود و او را در جریان قرار میدهد، حتی میتوان ساختار زیر را مستقیماً در سلول A1 استفاده کرد:

=IF(ERROR.TYPE(VLOOKUP(“Excelpro”;A2:C13;2;FALSE))=7;”The Required Item is not in Table”;VLOOKUP(“Excelpro”;A2:C13;2;FALSE))

در ساختار بالا عبارت Excelpro در محدوده A2:C13 جستجو میشود و در صورت پیدا نشدن پیامی متناسب به کاربر داده میشود.

استفاده از گروه Formula Auditing در اشکال زدایی فرمول ها

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