آشنایی با خطاهای فرمول‌نویسی در اکسل

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

#### (خطای پهنای ستون)

این خطا زمانی رخ می‌دهد كه پهنای عدد یا نتیجه‌ی محاسبه‌شده برای فرمول‌ها در پهنای ستون جا نشود؛ به عبارت دیگر به اندازه‌ی كافی عریض نیست. در صورتی که ستون مورد نظر را کمی بزرگ‌تر کنیم خطای #### رفع می‌شود.

⇓

#CALC! (خطای محاسبات)

با معرفی آرایه‌های پویا در اکسل (Dynamic Arrays)، فرمول‌ها بیشتر بر روی آرایه‌ها تکیه می‌کنند. خطای #CALC! زمانی رخ می‌دهد که یک فرمول هنگام کار با یک آرایه با خطای محاسباتی مواجه شود. خطای #CALC! یک خطای «جدید» در اکسل است که با آرایه‌های پویا معرفی شده‌است و در نسخه‌های قدیمی اکسل ظاهر نمی‌شود.

یک آرایه‌ی خالی می‌تواند باعث ایجاد خطای #CALC! شود، و این رایج‌ترین دلیلی است که ممکن است خطای #CALC! را در یک ورک‌شیت ببینید، به ویژه هنگام استفاده از تابع FILTER. زیرا FILTER وقتی هیچ مقداری با معیارها مطابقت ندارد، خطای #CALC! را برمی‌گرداند – به عبارت دیگر، FILTER یک آرایه‌ی خالی را برمی‌گرداند.

به عنوان مثال، در تصویر زیر، تابع FILTER برای فیلتر کردن داده‌های منبع در B5: D11 تنظیم شده‌است. با این حال، فرمول تمام داده‌های گروه “x” را درخواست می‌کند، که وجود ندارد. نتیجه یک خطای #CALC! است:

#DIV/0! (خطای تقسیم بر صفر)

تقسیم بر صفر عملیاتی است که پاسخی برای آن وجود ندارد، بنابراین مجاز نیست. هر زمان که بخواهید یک عدد را بر 0 یا یک سلول خالی در اکسل تقسیم کنید، خطای تقسیم بر صفر (#DIV/0!) دریافت خواهید کرد. در تصویر زیر در سلول C1 فرمول A1/B1 (A1 تقسیم بر B1) نوشته شده‌است، لذا خطای #DIV/0! ظاهر شده‌است.

مانع شدن از #DIV/0! با IFERROR

ساده‌ترین روش برای مدیریت خطای #DIV/0! در اکسل این است که فرمول تقسیم خود را در تابع IFERROR مانند زیر قرار دهید:

=IFERROR(A2/B2, “”)

فرمول، نتیجه‌ی تقسیم را بررسی می‌کند، و اگر خطایی را ارزیابی کند، یک رشته‌ی خالی (“”) را برمی‌گرداند، در غیر این صورت نتیجه‌ی تقسیم را برمی‌گرداند.

لطفاً نگاهی به دو ورک‌شیت زیر بیندازید. کدام یک از نظر زیباشناسی لذت‌بخش‌تر است؟

توجه: تابع IFERROR اکسل علاوه بر #DIV/0!، همه‌ی انواع خطاها دیگر را هم مانند #N/A، #NAME?، #REF!، #VALUE! و غیره پنهان می‌کند. اگر می‌خواهید به طور خاص تنها مانع خطاهای #DIV/0! شوید، از فرمول IF استفاده کنید همان‌طور که در مثال بعدی نشان داده شده‌است.

برخورد با خطای #DIV/0! با IF

برای پوشاندن خطاهای #DIV/0! در اکسل، از یک فرمول IF استفاده کنید که مساوی بودن (یا مساوی نبودن) مقسوم علیه با صفر را بررسی کند.

مثلاً:

=IF(B2=0,””,A2/B2)

یا

=IF(B2<>0,A2/B2,””)

اگر مقسوم علیه عددی غیر از صفر باشد، فرمول‌ها سلول A2 را بر B2 تقسیم می‌کنند. اگر B2 صفر یا خالی باشد، فرمول‌ها چیزی (رشته‌ی خالی) برنمی‌گردانند.

به جای سلول خالی، هم‌چنین می‌توانید یک پیام سفارشی مانند این نمایش دهید:

=IF(B2<>0, A2/B2, “Error in calculation”)

#N/A (Not Available)

N/A مخفف Not Available است و زمانی رخ می‌دهد که داده‌ی مرتبط با فرمول مورد نظر، موجود نباشد.
مثلاً در فرمول LOOKUP اگر آرگومان اول (بخش اول) که باید مقدار یا ارزش قابل جستجو قرار گیرد، خالی باشد اکسل خطای #N/A می‌دهد، یعنی سلول مورد نظر خالی است و چیزی موجود نیست.

#NAME? (خطای NAME)

این خطا زمانی رخ می‌دهد که متن فرمول نوشته‌شده غلط باشد. معمولاً جهت نوشتن توابع اکسل یا آدرس‌دهی به سلول‌های دیگر ابتدا باید یک مساوی (=) بنویسیم سپس شروع به نوشتن تابع مورد نظر می‌کنیم. در صورتی که متن بعد از مساوی برای اکسل ناشناخته باشد، این خطا ظاهر می‌شود. مثلاً در سلول A1 اگر بنویسیم =Sales خطای #NAME? ظاهر می‌شود چون نام این متن (Sales) برای اکسل تعریف نشده‌است.

#NULL! (خطای تهی)

اکسل هنگام اشتراک‌گیری از دو ناحیه که هیچ اشتراکی ندارند، این خطا را نشان می دهد. اپراتور اشتراک در اکسل کاراکتر فاصله است که ارجاع‌ها را در یک فرمول جدا می کند. از آن‎جا که کاراکتر فاصله‌ نشان‌دهنده‌ی اشتراک‌گیری است، اگر به جای کاما، بین بازه‌های مورد استفاده در آرگومان‌های تابع فاصله را وارد کنید، این خطا رخ می‌دهد. =SUM(A1:A4 B1:B4) خطای #NULL! را برمی‌گرداند زیرا دو بازه با یکدیگر هیچ اشتراکی ندارند.

#NUM! (خطای عدد)

این خطا مربوط به اعداد است. مثلاً همان‌طور که می‌دانید ریشه‌ی دوم عدد منفی معنایی ندارد بنابراین در تابع SQRT که ریشه‌ی دوم اعداد را محاسبه می‌کند در صورت استفاده از اعداد منفی این خطا ظاهر می‌شود.

#REF! (خطای مرجع)

این خطا مربوط به مرجع تابع است و در صورتی که مرجع یک تابع پاک شود این خطا ظاهر می‌شود. مثلاً در فرمول SUM(A1:A3) اگر ستون A حذف شود، خطای #REF ظاهر می‌شود چون محدوده‌ی جمع‌زده‌شده حذف شده‌است.

#SPILL! (خطای ریختگی)

با معرفی آرایه‌های پویا (Dynamic Arrays) در اکسل 365، فرمول‌هایی که چندین مقدار را برمی‌گرداند، به طور خودکار نتایج خود را در سلول‌های مجاور به هم می‌ریزند (Spill). مستطیلی که این مقادیر را در برمی‌گیرد «محدوده ریختگی» (spill range) نامیده می‌شود؛ و اگر چیزی در ورک‌شیت مانع پر شدن آن محدوده شود، خطای #SPILL رخ می‌دهد.

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

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

#VALUE! (خطای ارزش یا داده)

این خطا برای زمانی است که داده‌ی نامناسب استفاده شود. مثلاً در سلول C1، خطای #VALUE! به دلیل ناهمخوانی داده‌های سلول A1 و B1 ظاهر شده‌است.