نحوه رفع خطاهای رایج فرمول نویسی در اکسل
فرمول ها می توانند روش های قدرتمندی برای دستکاری، ارزیابی، یافتن و تجزیه و تحلیل داده ها باشند. اما با فرمول ها ممکن است مشکلاتی به وجود بیاید. در این مقاله ما چندتا از خطاهای رایج فرمول نویسی در اکسل ، نحوه تصحیح آنها و ابزارهایی برای کمک به شما را معرفی کرده ایم. هنگامی که فرمولی را در یک سلول وارد می کنید و Enter یا Return را فشار می دهید، ممکن است به جای نتیجه مورد انتظار یکی از خطاهای زیر را در سلول مشاهده کنید. همچنین ممکن است خطاهایی فراتر از موارد ذکر شده در اینجا مشاهده کنید، اما اینها برخی از رایج ترین خطاهای فرمول در اکسل هستند.
خطای #### در اکسل
این احتمالا یکی از ساده ترین خطاهایی است که در اکسل تصحیح می شود. اگر دادهای در یک سلول دارید و ستون برای نمایش آن دادهها به اندازه کافی گسترده نیست، ##### را مشاهده خواهید کرد.
راه حل : ستون را با کشیدن سرصفحه به سمت راست پهن کنید. از طرف دیگر، به سادگی روی فلش دو طرفه در سمت راست ستون دوبار کلیک کنید. این کار عرض را طوری تنظیم می کند که طولانی ترین رشته داده در آن ستون را در خود جای دهد.
رفع خطای #div/0 در اکسل
اگر در حال تقسیم بر صفر یا یک سلول خالی هستید، این خطا را خواهید دید. همچنین اگر قبل از ایجاد فرمول، تمام داده های خود را وارد نکرده باشید، می تواند به راحتی اتفاق بیفتد.
در مثال ما سلول B2 را بر سلول C2 تقسیم می کنیم و C2 خالی است.
راه حل : تأیید کنید که سلول شما حاوی مقداری است که صفر یا خالی نباشد. همچنین می توانید از تابع IFERROR استفاده کنید تا در صورت بروز خطا، نتیجه ای را که شما مشخص کرده اید به جای #DIV/0 برگرداند!
خطای #N/A
هنگامی که از توابع جستجو مانند XLOOKUP، HLOOKUP، VLOOKUP و MATCH استفاده می کنید، زمانی که فرمول نتواند مطابقت را پیدا کند، این خطا را مشاهده خواهید کرد.
در زیر فرمولی برای تابع XLOOKUP داریم که در آن مقدار جستجو (۲۰۷۳۵) در مجموعه داده ما وجود ندارد.
راه حل : دادههای خود را بررسی کنید تا مطمئن شوید مقداری که جستجو میکنید وجود دارد یا تأیید کنید که مقدار صحیح را در فرمول وارد کردهاید.
خطای ؟NAME# در اکسل
این ارور یکی دیگر از خطاهای رایج فرمول است که در اکسل مشاهده خواهید کرد. می تواند به دلایل مختلفی رخ دهد.
اگر فرمول شامل یکی از موارد زیر باشد، این خطا را خواهید دید:
- حاوی یک تابع غلط املایی است
- حاوی یک نام تعریف نشده است
- دارای اشتباه تایپی در نام تعریف شده
- برای متن علامت نقل قول وجود ندارد
- یک کولون بین محدوده سلولی وجود ندارد
در اینجا، یک فرمول با یک تابع غلط املایی و دیگری با یک دونقطه خالی داریم.
راه حل : به دنبال غلط املایی، اپراتورهای گم شده و محدوده های نامگذاری شده در فرمول خود باشید.
خطای !NULL#
در چند مورد این خطا را خواهید دید. متداول ترین حالت این است که شما یک عملگر محدوده نادرست در فرمول خود داشته باشید .
به عنوان مثال، در اینجا تابع SUM برای اضافه کردن دو محدوده سلولی داریم. فرمول حاوی یک فاصله به جای کاما است که باعث خطا است.
یک بار دیگر #NULL را خواهید دید! اگر از عملگر تقاطع برای محدوده هایی استفاده می کنید که همدیگر را قطع نمی کنند، خطا وجود دارد.
راه حل : محدوده یا عملگر اتحادیه را در فرمول خود تصحیح کنید یا محدوده سلولی را به آنهایی که متقاطع هستند تغییر دهید.
خطای !REF#
اگر به یک سلول نامعتبر در فرمول ارجاع می دهید، #REF را خواهید دید! اگر ستون یا ردیف حاوی مرجع را حذف کنید، اگر از یک تابع جستجو مانند VLOOKUP استفاده میکنید که مرجع وجود ندارد، یا از INDIRECT برای یک کتاب کار بسته استفاده میکنید، این اتفاق میافتد.
- مطالب مرتبط: نحوه استفاده از VLOOKUP در اکسل
در فرمول زیر، از SUM برای کم کردن مقدار سلول B2 از سلول C2 استفاده می کنیم. سپس ستون C را حذف می کنیم که خطا را ایجاد می کند.
راه حل : ستون یا سطر را جایگزین کنید، فرمول را دوباره بسازید، یا کتاب کار ارجاع شده را دوباره باز کنید.
خطا !VALUE#
اگر این خطا را مشاهده کردید، به این معنی است که اشکالی در نحوه نگارش فرمول یا سلول هایی که به آن ارجاع می دهید وجود دارد. می توانید از یک تابع ریاضی برای داده های غیر عددی یا سلول هایی که حاوی فضاهای پنهان هستند استفاده کنید.
در اینجا، ما از SUM برای تفریق مقادیر در دو سلول استفاده میکنیم که یکی حاوی متن است، نه عدد.
راه حل : قالبهای دادهای را که استفاده میکنید مانند متن به جای عدد تصحیح کنید. در این مثال، ما به سادگی مراجع سلولی را اشتباه وارد کردیم. یا به مراجع سلول برای فضاهای پنهان نگاه کنید، مانند فضاهای پیشرو یا انتهایی ، و آنها را حذف کنید.
- مطالب مرتبط : نحوه تصحیح خطای فرمول IF اکسل
ابزارهایی برای کمک به رفع خطاها در اکسل
خطاها می توانند ناراحت کننده باشند، اما می توانند ما را از اشتباه کردن و استفاده از داده های نادرست باز دارند. برای کمک بیشتر در پی بردن به خطاها در اکسل ، این ابزارها را امتحان کنید.
فعال کردن بررسی خطای پسزمینه : به File > Options > Formulas بروید. کادر زیر Error Checking Rules را علامت بزنید و سپس از کادرهای موجود در بخش قوانین بررسی خطا برای کسانی که می خواهید ببینید استفاده کنید.
از ابزار ارزیابی فرمول استفاده کنید : برای باز کردن یک ارزیابی گام به گام از فرمول خود، به Formulas > Evaluate Formula بروید .
ردیابی خطا : اگر مطمئن نیستید سلولی که در فرمول خود مشکل دارد، می توانید از ابزار ردیابی خطا استفاده کنید. به Formulas > Error Checking بروید و “Trace Error” را انتخاب کنید. سپس خطوط آبی از فرمول خود تا سلول های آرگومان ها را خواهید دید. برای حذف ردیابی، فرمول را ثابت کنید.
دریافت راهنمایی در مورد خطا : وقتی سلولی با خطا دارید و نماد خطا را مشاهده می کنید، برای نمایش گزینه های اضافی کلیک کنید. “Help on This Error” را انتخاب کنید تا نوار کناری با جزئیات مربوط به مشکل باز شود.
دانستن اینکه چه زمانی این خطاهای رایج فرمول رخ می دهد می تواند به شما در جلوگیری از آنها در آینده کمک کند. امیدواریم این مقاله برای شما مفید قرار گرفته شده باشد.
سلام و نور وخدا قوت
. چجور میشه که به اکسل بگم هر آدرسی که تو سلول b2 هست رو به همون آدرس برو و ماکرو از قبل نوشته شده رو اونجا اجرا کنه؟
مثلا یک جدول نمرات کلاسی دارم که این رو تو شیت ورود ، نمره ها رو نوشتم. حالا میخام از لیست کشویی تاریخی که مد نظرم هست رو انتخاب کنم و سپس ماکرو محترم بره اون تاریخ رو تو دیتا من بگرده و جا خالی رو با این جدول پر کنه .یعنی جدول رو کپی کنه ببره تو همون سلول و ستون مربوط به اون تاریخ که تو شیت دیتا از قبل آماده کرده بودم.
به عبارت دیگه: برای نمایش لیست و دیتا ام با آف ست تونستم نمایش بگذارم و با انتخاب تاریخ و نام دانش آموز نمرات اون روز یا هفته اش رو بیارم . ولی میخام وقتی تاریخ رو میزنم، اطلاعات و نمرات رو ثبت کنه و ورودی باشه.و گزینه ای باشه که بره تو لیست اصیلی ثبت کنه. این آف ست فقط نمایش میده.
یعنی وابستگی ماکرو به سلول اکسل.
ماکرو کپی اش رو نوشتم. و اتومات میره تو آخرین سلول هم پیست میکنه. ولی میخام تاریخی که دستی بهش میگم رو بره اونجا بریزه.
Range(“Range(“b2″).Value”).Select”
بعدش Selection رو پیست کنه مثلا
“سلام و خداقوت.
یکسری سلول داریم میانگین شون رو میخام حساب کنم. یعنی معدلی که مد نظرم هست. ولی با اینشرایط :
۱. مقادیر سلول ها نمره کمی و هم نمره کیفی توش هست. یعنی هم عدد هم حروف
۲. برخی سلول ها ممکنه خالی بگذاریم و خب تو میانگین نباس حساب بشن
۳. آیتم ها برای میانگین گیری مون دارای ضریب هستن
۴. علاوه بر معدل ، برای امتیاز دادن هم یکسری مشکلاتی هست. متلا هر رنج نمره ای امتیاز خودش رو داده. ۵ محدوده عدد از ۲۰ نمره
۵. مشکل برای محاسبه خط تیره یا خالی گذاشتن سلول هست و همچنین فرمول if فکرکنم اشتباه حساب میکنه و گیج میزنه یا قوانین نا نوشته شون رو نمیفهمم. مثلا تو فرمول SUBTOTAL یا AGGREGATE مثلا کلمه حضور رو صفر حساب میکنه و با ضریب اش مثلا حساب میکنه و امتیاز ها رو خراب نشون میده.تو معدل نمرات هم همینه
بنظرتون چه کنیم بهتره؟ ممنون از راهنمایی تون”