روز بخیر دوستان!

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

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

خوب، در واقع چه اتفاقی می افتد خطا در فرمول هابرتری داشتن:

1. خطای #####.این یکی از رایج ترین و ساده ترین است خطا در فرمول های اکسل... این فقط یک چیز است، اینکه عرض ستون به اندازه کافی گسترده نیست تا اطلاعات شما را به طور کامل نمایش دهد. درمان این مشکل بسیار ساده است، نشانگر ماوس را روی مرز ستون ببرید و در حالی که دکمه سمت چپ را نگه داشته اید، سلول را افزایش دهید تا داده ها شروع به نمایش کنند یا دوبار کلیک کنیددر حاشیه ستون، عریض ترین سلول در ستون وجود دارد.
2. خطای #NAME؟... این خطا (#NAME؟)در فرمول های اکسل تنها زمانی رخ می دهد که ویرایشگر نتواند متن موجود در فرمول را تشخیص دهد (به عنوان مثال، یک خطا در نام تابع به دلیل اشتباه تایپی = SUM (A1: A4). خطا در فرمول هابرتری داشتن، باید آن را با دقت بخوانید و خطا را اصلاح کنید (A1: A4).
3. خطای #VALUE!... این خطا (#VALUE!)ممکن است موردی داشته باشید که فرمول حاوی آرگومان باشد که نوع آن برای محاسبات شما مناسب نیست. به عنوان مثال، در ریاضیات یا فرمول خود، مقدار متن= A1 + B1 + C1، که در آن C1 متن است. درمان مشکل ساده است، از فرمولی استفاده کنید که سلول‌هایی را که در آن متن وجود دارد نادیده می‌گیرد یا به سادگی حذف کنید ارزش داده شدهاز سلول C1
4. خطای # BUSINESS / 0... همانطور که از خطای فرمول می بینید، شما به سادگی آرگومان خود را در عدد 0 ضرب کردید و این کار بر اساس قوانین ریاضی... برای رفع این خطا باید عدد را طوری تغییر دهید که با 0 برابر نباشد یا فرمول را مثلا یک منطقی تغییر دهید تا خطا پیش نیاید. = IF (A2 = 0؛ ””؛ A1 / A2)
5. خطای # پیوند!... این یکی از رایج ترین و گیج کننده ترین خطاها در توابع اکسل است. وقتی این خطا را می بینید به این معنی است که فرمول به سلولی اشاره دارد که دیگر وجود ندارد. این امر به ویژه هنگامی که با حجم زیادی از داده ها در و کار می کنید مشکل ساز است مقدار زیاد... وقتی جداول خود را به این شکل ویرایش می کنید خطا در فرمول هابرتری داشتنشما نباید بترسید، تعمیر آنها بسیار آسان است، فقط لازم است و همه چیز به جای خود باز می گردد، یا در صورت لزوم، فرمول را به صورت دستی بازنویسی کنید و استدلال اشتباه را از آن حذف کنید.

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

شما را در مقالات جدید می بینیم!

"چرا دنیا آنقدر مرتب شده است که افرادی که می دانند چگونه برای لذت خود زندگی کنند هرگز پول ندارند و آنها که پول دارند نمی دانند "سوزاندن یک زندگی" به چه معناست؟
D.B. نمایش دهید

من یک فرمول وارد می کنم، اما به جای نتیجه، متن # N / A یا ##### را می بینم. چه مفهومی داره؟

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

خطای #####

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

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

خطای # N / A

این خطا در صورتی رخ می دهد که یک تابع یا فرمول حاوی ارجاع به یک مقدار (یا خود مقدار) باشد که وجود ندارد. به عبارت دیگر، یک فیلد الزامی در یک تابع یا فرمول پر نمی شود، یا اشاره ای به یک سلول خالی وجود دارد در حالی که یک مقدار باید در سلول وجود داشته باشد. شایع ترین دلایل عبارتند از:

• یک یا چند آرگومان مورد نیاز در تابع وجود ندارد.
• توابع VLOOKUP، HLOOKUP، LOOKUP یا SEARCH نشان می دهد مقدار نادرستاز آرگومان lookup_value.
• VLOOKUP، HLOOKUP یا MATCH برای جستجوی جدول مرتب نشده استفاده می شود.
• در جدول اصلی، مقدار # N / A یا ND () به جای داده های از دست رفته وارد شده است.

# خطای DIV / 0!

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

• در فرمول، تقسیم بر صفر به صراحت مشخص شده است (به عنوان مثال، = 10/0).
• در یک فرمول یا تابع، مقسوم‌کننده اشاره‌ای به یک سلول خالی یا سلولی است که حاوی صفر است.

خطای # خالی!

این خطا زمانی رخ می دهد که دو محدوده همپوشانی در شرایط تابع مشخص شده باشند، اما در واقع با هم همپوشانی ندارند.

توجه: در برخی از کارها، کاربر باید اقداماتی را فقط با سلول هایی انجام دهد که به طور همزمان در دو محدوده وجود دارند. برای انجام این کار، محدوده سلول های تابع نه با یک نقطه ویرگول، بلکه با یک فاصله (که عملگر تقاطع نامیده می شود) از هم جدا می شوند.

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

خطای #NUM!

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

• تابعی که آرگومان عددی می گیرد متن یا مرجع سلولی را با مقدار غیر عددی مشخص می کند.
• تابعی که تکرار می‌شود (محاسبه‌ای که تکرار می‌شود تا زمانی که یک شرط معین برآورده شود، به عنوان مثال، IRR یا BET) نمی‌تواند نتیجه را محاسبه کند (مثلاً یک شرط معین برآورده نشده است).
• نتیجه محاسبه عددی است که برای استفاده در اکسل خیلی بزرگ یا کوچک است. این را به شما یادآوری می کنیم حداکثر تعداددر اکسل، نمی تواند از 10307 تجاوز کند و کمتر از منهای 10307 باشد.

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

خطای #VALUE!

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

• به جای یک عدد یا یک مقدار منطقی (TRUE یا FALSE)، متن در فرمول وارد شده است.
• پس از وارد کردن یا ویرایش فرمول آرایه، به جای CTRL + SHIFT + ENTER، ENTER را فشار دهید.
• یک مرجع، فرمول یا تابع به عنوان یک آرایه مشخص می شود.
• یک محدوده به عنوان یک شرایط تابعی مشخص می شود که به یک مقدار واحد نیاز دارد.

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

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

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

من خطای #NAME را دریافت کردم؟ و من نمی توانم دلیل آن را پیدا کنم.

• املای نام تابع (عنوان) را بررسی کنید. برای انجام این کار، می توانید Function Wizard (گروه Function Library در تب Formulas) را فراخوانی کرده و انتخاب کنید. عملکرد مورد نظراز لیست پیشنهادی
• متنی که در فرمول محصور نشده بود در یک فرمول وارد شد. نقل قول های دوگانه... بیایید بگوییم که نتیجه فرمول این بود که یک متن با یک عدد باشد - به عنوان مثال، Total 200 r. اگر وقتی متن Total را در یک فرمول وارد می‌کنید، متن در دو گیومه قرار نمی‌گیرد، اکسل داده‌ها را به عنوان یک نام تفسیر می‌کند که خطا ایجاد می‌کند.

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

• فرمول یا تابع از نامی استفاده می کند که تعریف نشده است. آن را بررسی کنید نام داده شدهوجود دارد. در تب Formulas، در گروه Named Cells، Name Manager را انتخاب کنید و ببینید آیا نام داده شده در لیست است یا خیر. اگر این نام وجود ندارد، می توانید با انتخاب ایجاد نام، آن را اضافه کنید.
• در املای نام اشتباه بود. برای بررسی صحیح بودن نام ها در نوار فرمول، F3 را فشار دهید، نام مورد نظر خود را کلیک کنید و سپس روی OK کلیک کنید.
• فرمول یا تابع از ارجاع به برگه دیگری استفاده می کند که در گیومه های تکی محصور نشده است. اگر نام برگه یا کتاب دیگری حاوی نویسه‌های غیر الفبایی یا فاصله باشد، این نام باید با حروف (") قید شود.

من دارم پایگاه عظیمداده های مشتری - خریداران. هر روز یک ستون به پایگاه داده اضافه می شود که میزان درآمد هر مشتری را نشان می دهد. اگر خریدار چیزی نخریده باشد، # N / A در سلول قرار می گیرد. اگر چنین داده هایی وجود داشته باشد چگونه می توانم یک ستون را جمع کنم؟

ساده ترین راه برای حل این مشکل تغییر اصل پر کردن پایگاه داده و پر نکردن سلول های مشتریانی است که خرید نکرده اند.

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

فرض کنید جدول اصلی به شکل زیر است:

با استفاده از تابع BDSUMM، می توانیم سلول هایی را که حاوی مقدار # N / A نیستند را برای جمع بندی انتخاب کنیم. تابع BDSUMM دارای نحو زیر است:

BDSUMM (پایگاه_داده، فیلد، شرایط)، که در آن

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

یعنی در تابع باید جمع اولین ستون جدول خود را تنظیم کنیم به شرطی که مقدار سلول این ستون برابر با # N / A نباشد. برای انجام این کار، ابتدا یک شرط ایجاد کنید - در هر مکان مناسب در کاربرگ، شرط # N / A را بنویسید و این سلول را به همان شکلی که ستون پایگاه داده مورد نیاز ما است برچسب گذاری کنید.

BDSUMM (C2: C18؛ 1؛ E2: E3) که در آن

• C2: C18 جدول ما است،
• E2: E3 شرایط ما است.

لطفاً توجه داشته باشید که محدوده های پایگاه داده و شرایط باید شامل سرصفحه های ستون باشد! در این صورت سرفصل شرط باید با سرفصل ستونی که محاسبات روی آن انجام می شود مطابقت داشته باشد!

نتیجه ما در زیر جدول ذکر شده است:

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

از این روش می توان برای توابع COUNT، BCOUNT، DMAX، DMIN، BDPROIZVED و موارد مشابه نیز استفاده کرد.

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

در زیر توضیحی از فرمول های نشان داده شده در تصویر با اطلاعات دقیقبرای هر اشتباهی

1. #مورد!- "تقسیم بر 0"، اغلب هنگام تلاش برای تقسیم بر صفر اتفاق می افتد. یعنی فرمول تعبیه شده در سلول، که عملکرد تقسیم را انجام می دهد، به سلولی برخورد می کند که با مقدار صفریا "خالی" است. برای رفع مشکل، تمام سلول های درگیر در محاسبات را بررسی کنید و مقادیر نامعتبر را تصحیح کنید. دومین اقدام منجر به # DIV / O! - این ورودی مقادیر نادرست به برخی از توابع است، مانند = AVERAGE ()، اگر محاسبه در محدوده مقادیر 0 باشد. همین نتیجه سلول های خالی را تحریک می کند، که با فرمولی که نیاز به دسترسی دارد داده های خاص برای محاسبه
2. # N / A- "هیچ داده ای وجود ندارد". اینگونه است که اکسل مقادیری را که فرمول (تابع) نمی تواند درک کند، علامت گذاری می کند. با وارد کردن اعداد نامناسب در تابع، قطعا این خطا را پرتاب خواهید کرد. هنگامی که ظاهر می شود، مطمئن شوید که تمام سلول های ورودی به درستی پر شده اند، به خصوص در مواردی که همان نوشته روشن است. در هنگام استفاده رایج است
3. #نام? - "نام نامعتبر"، نشانگر نام نادرست یک فرمول یا بخشی از آن. اگر همه نام‌ها و نام‌های همراه با الگوریتم محاسبه را بررسی و تصحیح کنید، مشکل از بین می‌رود.
4. # خالی!- "مقدار خالی در محدوده"، سیگنالی است که در جایی از محاسبه مناطق غیر متقاطع نوشته شده است یا بین محدوده های مشخص شده فاصله ایجاد می شود. یک اشتباه کاملا نادر یک ورودی اشتباه ممکن است به شکل زیر باشد:

SUM (G10: G12 I8: J8)

اکسل چنین دستوراتی را نمی شناسد.
5. #عدد!- خطا به دلیل فرمولی حاوی عددی است که با محدوده مشخص شده مطابقت ندارد.
6. #ارتباط دادن!- هشدار می دهد که سلول های مرتبط با این فرمول ناپدید شده اند. بررسی کنید که آیا سلول های نشان داده شده در فرمول حذف شده اند یا خیر.
7. #مقدار!- نوع آرگومان برای تابع اشتباه انتخاب شده است.

8. پاداش، اشتباه ##### - عرض سلول برای نمایش عدد کامل کافی نیست

علاوه بر این، اکسل در مورد فرمول نادرست هشدار می دهد. برنامه سعی خواهد کرد به شما بگوید دقیقاً چگونه علائم نگارشی را ترتیب دهید (مثلاً پرانتز). اگر گزینه پیشنهادی با شرایط شما مطابقت دارد، روی "بله" کلیک کنید. اگر درخواست نیاز به تنظیم دستی دارد. سپس «نه» را انتخاب کنید و خودتان پرانتزها را مرتب کنید.

خطاها در اکسل با استفاده از تابع ISERROR () برای Excel 2003

این تابع به حذف خطاها در اکسل کمک می کند. با یافتن خطاها در سلول ها کار می کند، اگر در فرمول خطایی پیدا کند، TRUE را برمی گرداند و بالعکس. در ترکیب با = IF ()، در صورت یافتن خطایی، مقدار را جایگزین می کند.

فرمول کار: = IF (ERROR (عبارت)؛ خطا؛ عبارت).

IF (ISERROR (A1 / A2)؛ ””؛ A1 / A2)

توضیح: اگر هنگام اجرای A1 / A2 خطایی پیدا شود، یک خالی ("") برگردانده می شود. اگر همه چیز درست پیش رفت (یعنی ISERROR (A1 / A2) = FALSE)، A1 / A2 محاسبه می شود.

خطاها در اکسلاستفاده از IFERROR () برای Excel 2007 و بالاتر

یکی از دلایلی که به سرعت به Excel 2007 تغییر دادم IFERROR () (بیشترین آن بود دلیل اصلی- آی تی )

تابع خطای if دارای قابلیت های ISERROR () و IF () است، اما در نسخه های جدیدتر Excel موجود است که بسیار راحت است.

ابزار به صورت زیر فعال می شود: = IFERROR (مقدار؛ مقدار روی خطا). به جای "مقدار"، یک عبارت محاسبه شده / مرجع سلول قرار داده می شود، و به جای "مقدار روی خطا" - در صورت ظاهر شدن نادرستی، چه چیزی باید برگردانده شود، به عنوان مثال، اگر در حین محاسبه A1 / A2 #مورد!سپس فرمول به صورت زیر خواهد بود:

ESLIOSHIBKA (A1 / A2؛ "")

»از اینجا دانلود کنید

لیست ها و محدوده ها (5)
ماکروها (روش های VBA) (63)
متفرقه (39)
اشکالات و اشکالات اکسل (3)

نحوه نشان دادن 0 به جای خطا در یک سلول با فرمول

شرایطی وجود دارد که در کتاب کارفرمول های زیادی روی برگه ها ایجاد شده است که وظایف مختلفی را انجام می دهد. در همان زمان، فرمول ها مدت ها پیش ایجاد شده اند، شاید حتی بر روی شما. و فرمول ها خطاها را برمی گرداند. به عنوان مثال # DIV / 0! (# DIV / 0!). اگر تقسیم بر صفر در فرمول = A1 / B1 رخ دهد، که در آن B1 حاوی صفر یا خالی باشد، این خطا رخ می دهد. اما ممکن است خطاهای دیگری نیز وجود داشته باشد (# N / A، #VALUE! و غیره). می توانید با اضافه کردن یک بررسی خطا، فرمول را تغییر دهید:

= IF (ISERR (A1 / B1)، 0، A1 / B1)
استدلال ها:
= IF (EOSH (1 آرگومان)، 2 آرگومان، 1 آرگومان)
این فرمول ها در هر نسخه اکسل کار می کنند. درست است، عملکرد EOS خطای # N / A (# N / A) را کنترل نمی کند. برای پردازش # N / A، باید از تابع ISERROR استفاده کنید:
= IF (ISERROR (A1 / B1)، 0، A1 / B1)
= IF (ISERROR (A1 / B1)، 0، A1 / B1)
با این حال، در ادامه متن از EOS استفاده خواهم کرد (چون کوتاهتر است) و علاوه بر این، همیشه لازم نیست که خطای # N / A "نبینم".
اما برای نسخه های اکسل 2007 و بالاتر، یک تابع کمی بهینه تر را می توان اعمال کرد IFERROR:
= IFERROR (A1 / B1,0)
= IFERROR (A1 / B1,0)
استدلال ها:
= IFERROR (1 آرگومان، 2 آرگومان)

1 آرگومان: عبارت برای ارزیابی
آرگومان 2: مقدار یا عبارتی که در صورت بروز خطا در آرگومان اول به سلول بازگردانده می شود.

چرا IFERROR بهتر است و من آن را بهینه تر می نامم؟ بیایید نگاهی دقیق تر به فرمول اول بیندازیم:
= IF (EOSH (A1 / B1)؛ 0؛ A1 / B1)
اگر مرحله به مرحله محاسبه کنیم، خواهیم دید که ابتدا عبارت A1 / B1 محاسبه می شود (یعنی تقسیم). و اگر نتیجه آن یک خطا باشد، EOSH TRUE را برمی گرداند که به IF ارسال می شود. و سپس تابع IF مقدار آرگومان دوم 0 را برمی گرداند.
اما اگر نتیجه اشتباه نباشد و ISERR FALSE را برگرداند، تابع عبارت محاسبه شده قبلی را دوباره محاسبه می کند: A1 / B1
با فرمول فوق، این نقش خاصی ندارد. اما اگر از فرمولی مانند VLOOKUP با نگاهی به چندین هزار خط استفاده شود، محاسبه دو برابری می تواند زمان محاسبه مجدد فرمول ها را به میزان قابل توجهی افزایش دهد.
تابع IFERROR عبارت را یک بار ارزیابی می کند، نتیجه آن را به خاطر می آورد و اگر اشتباه باشد، آنچه را که به عنوان آرگومان دوم نوشته شده است برمی گرداند. اگر خطایی وجود نداشته باشد، نتیجه ذخیره شده ارزیابی عبارت را از اولین آرگومان برمی گرداند. آن ها محاسبه در واقع یک بار اتفاق می افتد، که عملا بر سرعت محاسبه مجدد کلی فرمول ها تأثیر نمی گذارد.
بنابراین، اگر اکسل 2007 به بالا دارید و فایل برای موارد بیشتر استفاده نمی شود نسخه های اولیه- پس منطقی است که از IFERROR استفاده کنید.

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

بنابراین، چنین فرمول هایی در برگه وجود دارد که خطاهای آنها باید پردازش شود. اگر یک یا دو فرمول مشابه برای اصلاح وجود داشته باشد (بله، حتی 10-15)، پس تقریباً هیچ مشکلی برای تعویض دستی وجود ندارد. اما اگر چندین ده یا حتی صدها فرمول از این دست وجود داشته باشد، مشکل در مقیاس تقریباً جهانی می شود :-). با این حال، فرآیند را می توان با نوشتن نسبتاً ساده کرد کد ساده ویژوال بیسیکبرای برنامه
برای تمام نسخه های اکسل:

Sub IfIsErrNull () Const sToReturnVal As String = "0", vbInformation, "www.site" Exit Sub End If For Her rc در rr If rc.HasFormula سپس s = rc.Formula s = Mid (s, 2) ss = " = "&" IF (ISERR ("& s &")، "& sToReturnVal &"، "& s &") "اگر چپ (s، 9)<>"IF (ISERR (" سپس اگر rc.HasArray سپس rc.FormulaArray = ss دیگری rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc. آدرس rc. خروج را برای پایان انتخاب کنید اگر پایان اگر پایان اگر بعد rc اگر خطا .Number سپس MsgBox "فرمول های پردازش شده"

Sub IfIsErrNull () Const sToReturnVal As String = "0" "اگر لازم باشد به جای صفر خالی برگردانده شود" Const sToReturnVal As String = "" "" "" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست"، vbInformation، "www..HasFormula سپس s = rc.Formula s = Mid (s, 2) ss =" = "&" IF (ISERR ("& s &")، "& sToReturnVal &"، "& s &") "اگر چپ (s، 9)<>"IF (ISERR (" سپس اگر rc.HasArray سپس rc.FormulaArray = ss دیگری rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc. آدرس rc. خروج را برای پایان انتخاب کنید اگر پایان اگر پایان اگر بعد rc اگر خطا .Number سپس MsgBox "Unable to transform formula in cell:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

برای نسخه های 2007 و بالاتر

Sub IfErrorNull () Const sToReturnVal As String = "0" «اگر لازم است به جای صفر، خالی برگردیم "Const sToReturnVal As String = """ "" " Dim rr به عنوان محدوده، rc به عنوان محدوده Dim s به عنوان رشته، ss به عنوان رشته در خطا از سرگیری مجموعه بعدی rr = متقاطع (انتخاب، ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست", vbInformation, "www.site" خروج Sub End If برای هر rc در rr اگر rc.HasFormula سپس s = rc.Formula s = Mid (s, 2) ss = "=" & "IFERROR (" & s & ", "& sToReturnVal &") "اگر چپ (s، 8)<>"IFERROR (" سپس اگر rc.HasArray سپس rc.FormulaArray = ss در غیر این صورت rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc. آدرس rc. خروج را انتخاب کنید برای پایان اگر پایان اگر پایان اگر بعد rc اگر عدد Err. سپس MsgBox "نمی توان فرمول را در سلول تبدیل کرد:"& ss & vbNewLine & _ Err.Description, vbInformation, "www.site" Else MsgBox "فرمول های پردازش شده"، vbInformation، "www.site" End If End Sub

Sub IfErrorNull () Const sToReturnVal As String = "0" "اگر لازم باشد به جای صفر خالی برگردانده شود" Const sToReturnVal As String = "" "" "" Dim rr As Range, rc As Range Dim s As String, ss As String On Error Resume Next Set rr = Intersect (Selection, ActiveSheet.UsedRange) اگر rr چیزی نیست پس MsgBox "محدوده انتخاب شده حاوی داده ای نیست"، vbInformation، "www..HasFormula سپس s = rc.Formula s = Mid (s, 2) ss =" = "&" IFERROR ("& s &"، "& sToReturnVal &") "اگر چپ (s، 8)<>"IFERROR (" سپس اگر rc.HasArray سپس rc.FormulaArray = ss در غیر این صورت rc.Formula = ss پایان اگر اگر Err.Number سپس ss = rc. آدرس rc. خروج را انتخاب کنید برای پایان اگر پایان اگر پایان اگر بعد rc اگر عدد Err. سپس MsgBox "Unable to transform formula in cell:" & ss & vbNewLine & _ Err.Description, vbInformation, "www..site" End If End Sub

چگونه کار می کند
اگر با ماکروها آشنایی ندارید، ابتدا بهتر است نحوه ایجاد و فراخوانی آنها را بخوانید: ماکرو چیست و از کجا می توانم آن را پیدا کنم؟ از آنجا که ممکن است همه چیز را درست انجام دهید، اما فراموش کرده اید که ماکروها را فعال کنید و هیچ چیز کار نخواهد کرد.

کد بالا را کپی کنید، به ویرایشگر VBA بروید ( جایگزین+F11، یک ماژول استاندارد ایجاد کنید ( درج کنید -مدول) و فقط این کد را در آن قرار دهید. به سمت راست بروید کتاب کار اکسلو تمام سلول ها را انتخاب کنید، فرمول هایی که در آنها باید تبدیل شوند تا در صورت بروز خطا به صفر برگردند. مطبوعات جایگزین+F8، کد را انتخاب کنید IfIsErrNull(یا IfErrorNullبسته به اینکه کدام یک را کپی کرده اید) و فشار دهید اجرا کردن.
یک تابع رسیدگی به خطا به تمام فرمول های سلول های انتخاب شده اضافه می شود. کدهای داده شده همچنین موارد زیر را در نظر می گیرند:
- اگر فرمول قبلاً تابع IFERROR یا IF (EOSH) را اعمال کرده باشد، چنین فرمولی پردازش نمی شود.
-کد توابع آرایه را نیز به درستی پردازش می کند.
- می توانید سلول های غیر مجاور (از طریق Ctrl) را انتخاب کنید.
چه ضرری دارد:فرمول‌های آرایه‌ای پیچیده و طولانی به دلیل خاص بودن این فرمول‌ها و پردازش آن‌ها از VBA می‌توانند باعث خطای کد شوند. در این صورت کد در مورد عدم امکان ادامه کار می نویسد و سلول مشکل را برجسته می کند. بنابراین، من به شدت توصیه می‌کنم کپی‌های فایل‌ها را جایگزین کنید.
اگر مقدار خطا باید با خالی و نه صفر جایگزین شود، به خط نیاز دارید

"Const sToReturnVal As String = """ "" "

حذف آپستروف ( " )

شما همچنین می توانید کد داده شدهبا فشار دادن یک دکمه (چگونه یک دکمه برای فراخوانی ماکرو در یک برگه ایجاد کنیم) یا آن را در یک افزونه (چگونه افزونه خود را بسازیم؟) قرار دهید تا بتوانید آن را از هر فایلی فراخوانی کنید.

و کمی اضافه: سعی کنید کد را با دقت اعمال کنید. همیشه مانعی برای بازگرداندن خطا نمی شود. به عنوان مثال، برای با استفاده از VLOOKUPگاهی اوقات دیدن اینکه کدام مقادیر پیدا نشدند مفید است.
من همچنین می خواهم توجه داشته باشم که باید در فرمول های واقعاً کارآمد اعمال شود. زیرا اگر فرمول #NAME را برگرداند (# NAME!) به این معنی است که برخی از آرگومان ها به اشتباه در فرمول نوشته شده است و این خطا در نوشتن فرمول است و خطا در نتیجه محاسبه نیست. بهتر است برای جلوگیری از خطاهای منطقی محاسبات روی برگه، چنین فرمول هایی را تجزیه و تحلیل کنید و خطا را پیدا کنید.

آیا مقاله کمکی کرد؟ لینک را با دوستان خود به اشتراک بگذارید دروس تصویری

("نوار پایین" :( "textstyle": "static"، "textpositionstatic": "bottom"، "textautohide": true، "textpositionmarginstatic": 0، "textpositiondynamic": "bottomleft"، "textpositionmarginleft": 24، " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" اسلاید "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" چپ "," texteffectslided : 30، "texteffectdelay": 500، "texteffectseparate": نادرست، "texteffect1": "slide"، "texteffectslidedirection1": "راست"، "texteffectslidedistance1": 120، "Texteffecteasing1": "Ceffectubic"،10extut، "easeOtut" , "texteffectdelay1": 1000، "texteffect2": "slide"، "texteffectslidedirection2": "راست"، "texteffectslidedistance2": 120، "texteffecteasing2": "easeOutCubic"، "texteffectslidedirection2": "texteffectslidedirection2": "texteffectslidedirection2": :" نمایش: بلوک؛ بالشتک: 12 پیکسل؛ تراز متن: چپ؛ "," textbgcss ":" نمایش: بلوک؛ موقعیت: مطلق؛ بالا: 0 پیکسل؛ سمت چپ: 0 پیکسل؛ عرض: 100٪؛ ارتفاع: 100٪؛ پس زمینه -color: # 333333؛ opacity: 0.6؛ filter: a lpha (تاری = 60)؛ "," titlecss ":" نمایش: بلوک; موقعیت: نسبی; فونت: پررنگ 14 پیکسل \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; رنگ: #fff؛ "," descriptioncss ":" نمایش: بلوک; موقعیت: نسبی; فونت: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; رنگ: #fff; margin-top: 8px؛ "," buttoncss ":" نمایش: block; موقعیت: نسبی; margin-top: 8px؛ "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" اندازه قلم: 12px؛ "," descriptioncssresponsive ":" نمایش: هیچکدام! مهم؛ "," buttoncssresponsive ": "، "addgooglefonts": false، "googlefonts": ""، "textleftrightpercentforstatic": 40))

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

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

V این درسمقادیر خطای فرمول هایی را که سلول ها می توانند داشته باشند را شرح می دهد. با دانستن معنی هر کد (به عنوان مثال: #VALUE !، # DIV / 0 !، #NUMBER !، # N / A !، #NAME !، #NOT !، #REF!)، به راحتی می توانید بفهمید که چگونه یک خطا در فرمول پیدا کنید و او را از بین ببرید.

نحوه حذف # DIV / 0 در اکسل

همانطور که هنگام تقسیم بر یک سلول با مقدار خالیبرنامه آن را به عنوان تقسیم بر 0 درک می کند. در نتیجه مقدار: # DIV / 0 را خروجی می کند! شما می توانید این را با کمک یک راهنمایی تأیید کنید.

در دیگران محاسبات حسابی(ضرب، جمع، تفریق) سلول خالینیز پوچ است.



نتیجه یک محاسبه اشتباه #NUM است!

شماره نامعتبر: # NUMBER! خطای ناتوانی در انجام محاسبات در فرمول است.

چند مثال کاربردی:

خطا: #NUM! زمانی رخ می دهد که مقدار عددیخیلی بزرگ یا خیلی کوچک همون روش خطا داده شدهممکن است هنگام تلاش برای گرفتن ریشه از یک عدد منفی رخ دهد. به عنوان مثال، = ROOT (-25).

در سلول A1 - نیز عدد بزرگ(10 ^ 1000). اکسل نمی تواند با چنین چیزی کار کند اعداد بزرگ.

سلول A2 با اعداد زیاد نیز همین مشکل را دارد. به ظاهر 1000 عدد کوچک، اما بازگرداندن فاکتوریل آن منجر به یک مقدار عددی بسیار بزرگ می شود که اکسل نمی تواند آن را مدیریت کند.

در سلول A3 - ریشه دومنمی تواند با یک عدد منفی باشد، اما برنامه نمایش داده می شود نتیجه داده شدههمان خطا

نحوه حذف ND در اکسل

مقدار موجود نیست: # N / A! - به این معنی است که مقدار برای فرمول موجود نیست:

فرمول نوشته شده در B1: = SEARCH ("Maxim"؛ A1: A4) محتوای متن "Maxim" را در محدوده سلول های A1: A4 جستجو می کند. محتوا در سلول دوم A2 یافت شد. بنابراین، تابع نتیجه 2 را برمی گرداند. فرمول دوم محتوای متن "Andrey" را جستجو می کند، سپس محدوده A1: A4 حاوی چنین مقادیری نیست. بنابراین، تابع خطای # N / A (بدون داده) را برمی گرداند.

خطای #NAME! در اکسل

در دسته خطاهای نوشتن توابع قرار می گیرند. نام نامعتبر: #NAME! - به این معنی است که اکسل متن نوشته شده در فرمول را تشخیص نمی دهد (نام تابع = SUM () برای او ناشناخته است، با خطا نوشته شده است). این نتیجه یک خطای نحوی هنگام نوشتن نام تابع است. برای مثال:

خطای # خالی! در اکسل

مجموعه خالی: # EMPTY! - اینها خطاهای عملگر تقاطع مجموعه ها هستند. در اکسل چیزی به نام تقاطع مجموعه ها وجود دارد. اعمال می شود رسید سریعداده های جداول بزرگ به درخواست نقطه تقاطع محدوده عمودی و افقی سلول ها. اگر محدوده ها همپوشانی نداشته باشند، برنامه یک مقدار اشتباه را نشان می دهد - # NULL! عملگر مجموعه تقاطع یک فضای واحد است. آنها محدوده های عمودی و افقی مشخص شده در آرگومان های تابع را از هم جدا می کنند.

در این حالت محل تقاطع محدوده ها سلول C3 است و تابع مقدار آن را نمایش می دهد.

آرگومان های داده شده در تابع: = SUM (B4: D4 B2: B3) - یک تقاطع تشکیل نمی دهند. بنابراین، تابع یک مقدار با یک خطا می دهد - # NULL!

#ارتباط دادن! - خطای مراجع سلول اکسل

V این مثالاین خطا زمانی رخ داد که فرمول به اشتباه کپی شد. ما 3 محدوده سلول داریم: A1: A3، B1: B4، C1: C2.

زیر اولین محدوده در سلول A4، فرمول جمع را وارد کنید: = SUM (A1: A3). و سپس همان فرمول را در محدوده دوم، در سلول B5 کپی کنید. فرمول، مانند قبل، تنها 3 سلول B2: B4 را جمع می کند و مقدار B1 اول را دور می زند.

هنگامی که همان فرمول در محدوده سوم کپی شد، تابع خطای #REFERENCE! را به سلول C3 برگرداند. از آنجایی که فقط 2 سلول در بالای سلول C3 وجود دارد و نه 3 (طبق فرمول اصلی).

توجه داشته باشید. V در این موردراحت ترین فشار دادن ترکیبی از داغ است کلیدهای ALT+ = سپس تابع جمع درج می شود و به طور خودکار تعداد سلول های جمع را تعیین می کند.

نحوه رفع VALUE در اکسل

#مقدار! - خطا در ارزش اگر بخواهیم یک عدد و یک کلمه در اکسل اضافه کنیم، نتیجه یک #VALUE خواهد بود! یک واقعیت جالب این است که اگر بخواهیم دو خانه اضافه کنیم که مقدار اولی یک عدد است و دومی یک متن با استفاده از تابع = SUM () باشد، خطایی رخ نمی دهد و متن دریافت می کند. مقدار 0 در حین محاسبه برای مثال:

شبکه ها در یک سلول اکسل

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

بنابراین هش (######) به جای مقادیر سلول زمانی که تاریخ منفی است قابل مشاهده است. به عنوان مثال، ما در حال تلاش برای حذف از تاریخ قدیمی تاریخ جدید... و در نتیجه محاسبه، قالب سلول ها "تاریخ" است (نه "عمومی").

یک قالب سلول نادرست همچنین می تواند یک سری کاراکتر هش (######) را به جای مقادیر نمایش دهد.