چطور خطا‌های رایج فرمول‌ها در برنامه اکسل را برطرف کنیم؟

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

هنگامی که فرمولی را در یک سلول وارد می‌کنید و Enter یا Return را فشار می‌دهید، ممکن است به جای نتیجه مورد انتظار یکی از خطاهای زیر را در سلول مشاهده کنید. همچنین ممکن است خطاهایی فراتر از موارد ذکر شده در اینجا مشاهده کنید، اما این‌ها برخی از رایج‌ترین خطاهای فرمول در اکسل هستند.

خطای: #####

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

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

خطای: #DIV/0!

اگر #DIV/0 را دیدید در حال تقسیم بر صفر یا یک سلول خالی هستید. اگر قبل از ایجاد فرمول، تمام داده‌های خود را وارد نکرده باشید، می‌تواند به راحتی اتفاق بیفتد.

در مثال ما سلول B2 را بر سلول C2 تقسیم می‌کنیم و C2 خالی است.

رفع: مطمئن شوید که سلول شما حاوی مقداری است که صفر یا خالی نباشد. همچنین می‌توانید از تابع IFERROR استفاده کنید تا در صورت بروز خطا، نتیجه‌ای را که شما مشخص کرده‌اید به جای #DIV/0 برگرداند!

خطای: #N/A

هنگامی که از توابع جستجو مانند XLOOKUP، HLOOKUP، VLOOKUP و MATCH استفاده می‌کنید، زمانی که فرمول نتواند مطابقت را پیدا کند، این خطا را مشاهده خواهید کرد.

در زیر فرمولی برای تابع XLOOKUP داریم که در آن مقدار جستجو (20735) در مجموعه داده ما وجود ندارد.

رفع: داده‌های خود را بررسی کنید تا مطمئن شوید مقداری که جستجو می‌کنید وجود دارد یا مطمئن شوید که مقدار صحیح را در فرمول وارد کرده‌اید.

خطای: #NAME?

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

شما این خطا‌ها را خواهید دید اگر فرمولتان:

• حاوی یک تابع با غلط املایی است
• حاوی یک نام تعریف نشده است
• دارای اشتباه تایپی در نام تعریف شده
• برای متن علامت نقل قول وجود ندارد
• یک کولون بین محدوده سلولی آن وجود ندارد

در اینجا، یک فرمول با یک تابع با غلط املایی و دیگری با یک دونقطه خالی داریم.

رفع: به دنبال غلط املایی، عملگرهای گم شده و محدوده‌های نامگذاری شده در فرمول باشید.

خطای: #NULL!

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

به عنوان مثال، در اینجا تابع SUM برای اضافه کردن دو محدوده سلولی داریم. فرمول حاوی یک فاصله به جای کاما است که جداکننده اتحاد است.

بار دیگری که ممکن است #NULL را ببینید، این است که هنگام استفاده از عملگر تقاطع برای محدوده‌هایی استفاده می‌کنید که همدیگر را قطع نمی‌کنند، خطا به وجود می‌آید.

رفع: محدوده یا عملگر اتحاد را در فرمول خود تصحیح کنید یا محدوده سلولی را به آن‌هایی که متقاطع هستند تغییر دهید.

خطای: #REF!

اگر به یک سلول نامعتبر در فرمول ارجاع می‌دهید، #REF را خواهید دید! اگر ستون یا ردیف حاوی مرجع را حذف کنید، از یک تابع جستجو مانند VLOOKUP در جایی که مرجع وجود ندارد، یا از INDIRECT برای یک کتاب کار بسته استفاده می‌کنید، این خطا ممکن است رخ دهد.

در فرمول زیر، از SUM برای کم کردن مقدار سلول B2 از سلول C2 استفاده می‌کنیم. سپس ستون C را حذف می‌کنیم که خطا را ایجاد می‌کند.

رفع: ستون یا سطر را جایگزین کنید، فرمول را دوباره بسازید، یا کتاب کار مرجع را دوباره باز کنید.

خطای: #VALUE!

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

در اینجا، ما از SUM برای تفریق مقادیر در دو سلول استفاده می‌کنیم که یکی حاوی متن است، نه عدد.

رفع: قالب‌های داده‌ای را که استفاده می‌کنید مانند متن به جای عدد تصحیح کنید. در مثال، ما به سادگی مراجع سلولی را اشتباه وارد کردیم. به مراجع سلول برای فضاهای پنهان نگاه کنید، مانند فضاهای پیشرو یا انتهایی، و آن‌ها را حذف کنید.

ابزار یافتن خطا‌های بیشتر در اکسل

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

فعال کردن بررسی خطای پس‌زمینه: به File > Options > Formulas بروید. کادر زیر بررسی خطا را علامت بزنید و سپس از کادرهای موجود در بخش قوانین بررسی خطا برای آن‌هایی که می‌خواهید ببینید استفاده کنید.

از ابزار Evaluate Formula استفاده کنید: برای باز کردن ارزیابی گام به گام فرمول خود، به Formulas > Evaluate Formula بروید.

ردیابی خطا: اگر از سلولی که در فرمول خود مشکل دارد مطمئن نیستید، می‌توانید از ابزار ردیابی خطا استفاده کنید. به Formulas > Error Checking بروید و “Trace Error” را انتخاب کنید. سپس خطوط آبی از فرمول خود تا سلول‌های آرگومان‌ها را خواهید دید. برای حذف ردیابی، فرمول را ثابت کنید.

دریافت راهنمایی در مورد خطا: وقتی سلولی با خطا دارید و نماد خطا را مشاهده می‌کنید، برای نمایش گزینه‌های اضافی کلیک کنید. “Help on This Error” را انتخاب کنید تا نوار کناری با جزئیات مربوط به مشکل باز شود.

سوالات خود در مورد خطای فرمول اکسل را از ما و دوستانتان در آی‌تی‌رسان بپرسید.