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

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

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

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

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

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

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

 توابع 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 برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار می گیرد،