بهترین روش های حل خطای Formula Parse Errors در گوگل شیت

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

برای دریافت آموزش های گوگل کلیک کنید.

خطای Formula Parse Errors در گوگل شیت چیست؟

قبل از بررسی انواع مختلف این خطاها، ممکن است این سؤال برای شما پیش بیاید که “خطای Formula Parse Error در گوگل شیت به چه معناست؟”

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

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

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

بررسی و رفع خطاهای Formula Parse Errors در گوگل شیت

اگر هنگام وارد کردن یک فرمول در Google Sheets با خطا مواجه شده‌اید، نگران نباشید! این راهنما به شما کمک می‌کند تا علت خطای خود را شناسایی کرده و آن را برطرف کنید. در این مقاله، انواع Formula Parse Errors را بررسی کرده و روش‌های رفع آن‌ها را توضیح خواهیم داد.

• بیشتر بخوانید: 4 روش برای ایمپورت کردن داده های وب سایت به گوگل شیت

خطای Popup: زمانی که نمی‌توان فرمول را وارد کرد

گاهی اوقات، پس از وارد کردن یک فرمول و زدن Enter، با پیغام خطایی مشابه “Houston, we have a problem” مواجه می‌شوید. این مشکل معمولاً به دلیل وجود یک خطای اساسی در ساختار فرمول رخ می‌دهد.

علت خطا

• وجود کاراکترهای اضافی یا ناخواسته (مانند “” که ممکن است تصادفاً هنگام فشردن Enter وارد شده باشد).
• حذف بخشی از فرمول یا وجود یک مرجع سلولی ناقص.

چگونه این خطا را برطرف کنیم؟

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

بررسی و رفع خطای #N/A در گوگل شیت

خطای #N/A در گوگل شیت نشان می‌دهد که مقدار مورد نظر در داده‌های شما وجود ندارد یا قابل دسترسی نیست. این خطا معمولاً در هنگام استفاده از توابع جستجو مانند VLOOKUP، HLOOKUP یا MATCH رخ می‌دهد.

چرا خطای #N/A اتفاق می‌افتد؟

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

=VLOOKUP(“A-051”, A2:C10, 2, FALSE)

اگر مقدار “A-051” در محدوده A2:C10 وجود نداشته باشد، تابع VLOOKUP مقدار #N/A را برمی‌گرداند. این خطا به این معنا نیست که فرمول اشتباه نوشته شده است، بلکه به این دلیل است که مقدار مورد نظر در جدول یافت نشده است.

چگونه خطای #N/A را برطرف کنیم؟

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

استفاده از تابع IFERROR برای مدیریت خطای #N/A

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

=IFERROR(VLOOKUP(“A-051”, A2:C10, 2, FALSE), “نتیجه‌ای یافت نشد”)

عملکرد این فرمول: اگر مقدار A-051 در جدول پیدا شود، مقدار مورد نظر را نمایش می‌دهد، در غیر این صورت، به‌جای خطای #N/A، پیام “نتیجه‌ای یافت نشد” نمایش داده می‌شود.

سایر روش‌های جلوگیری از خطای #N/A

• بررسی محدوده داده‌ها: مطمئن شوید که داده‌های شما کامل و بدون خطا هستند.
• کنترل دقیق مقدار جستجو: گاهی اوقات یک فاصله اضافی در مقدار جستجو شده، باعث خطای #N/A می‌شود. می‌توانید از TRIM() برای حذف فضاهای اضافی استفاده کنید:

=VLOOKUP(TRIM(A1), A2:C10, 2, FALSE)

• استفاده از تابع IFNA: اگر فقط می‌خواهید خطای #N/A را مدیریت کنید (و نه سایر خطاها)، می‌توانید از IFNA() استفاده کنید:

=IFNA(VLOOKUP(“A-051”, A2:C10, 2, FALSE), “مقدار یافت نشد”)

این روش فقط #N/A را کنترل می‌کند و سایر خطاهای فرمول را نشان خواهد داد.

خطای #N/A در گوگل شیت همیشه به معنای اشتباه بودن فرمول نیست، بلکه نشان‌دهنده عدم یافتن مقدار جستجو شده است. به جای نمایش این خطا، می‌توان با استفاده از IFERROR یا IFNA یک پیام کاربرپسند نمایش داد و تجربه بهتری برای کاربران ایجاد کرد. با رعایت این نکات، می‌توانید از مشکلات غیرضروری جلوگیری کرده و بهینه‌تر با داده‌های خود کار کنید

چگونه خطای #DIV/0! را برطرف کنیم؟

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

به زبان ساده، معنای این است که ما سعی می کنیم چیزی شبیه به این را محاسبه کنیم:

= A/0

که معنی ندارد زیرا نمی توانید بر 0 تقسیم کنید.

بررسی مقدار مخرج (Denominator)

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

بررسی سلول‌های خالی در مخرج فرمول

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

اگر مقدار عددی انتظار دارید، آن را به‌صورت دستی وارد کنید یا از یک محدوده معتبر استفاده کنید.

 استفاده از تابع IFERROR برای مدیریت خطا

به جای نمایش پیام خطا، می‌توان از IFERROR برای نمایش یک مقدار جایگزین استفاده کرد:

= IFERROR(A1 / B1, “تقسیم بر صفر غیرممکن است”)

• اگر B1 صفر باشد، پیام “تقسیم بر صفر غیرممکن است” نمایش داده می‌شود.
• اگر مقدار مخرج معتبر باشد، فرمول به‌طور عادی اجرا می‌شود.

 استفاده از IF برای جلوگیری از خطا

اگر می‌خواهید دقیق‌تر مشکل را بررسی کنید، می‌توان از IF برای بررسی مقدار مخرج قبل از انجام تقسیم استفاده کرد:

= IF(B1=0, “خطا: مخرج صفر است!”, A1/B1)

• اگر B1 صفر باشد، پیام هشدار نمایش داده می‌شود.
• در غیر این صورت، نتیجه محاسبه به‌درستی نمایش داده می‌شود.

 بررسی و رفع خطای #VALUE! در گوگل شیت

خطای #VALUE! در گوگل شیت زمانی رخ می‌دهد که یک فرمول انتظار یک نوع داده مشخص را دارد اما مقدار نامناسبی دریافت می‌کند. این اتفاق معمولاً در موارد زیر رخ می‌دهد:

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

چرا خطای #VALUE! اتفاق می‌افتد؟

استفاده از مقدار متنی در محاسبات عددی

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

 مثال:

= A1 + B1

اگر مقدار B1 شامل یک متن باشد، گوگل شیت نمی‌تواند عملیات جمع را انجام دهد و خطای #VALUE! نمایش داده می‌شود.

 راه‌حل:

بررسی کنید که تمام ورودی‌های فرمول عددی هستند.

در صورت نیاز، از تابع VALUE() برای تبدیل متن به عدد استفاده کنید:

= A1 + VALUE(B1)

وجود فاصله‌های اضافی در سلول‌ها

اگر یک سلول به ظاهر خالی باشد اما شامل فاصله (Space) باشد، ممکن است باعث این خطا شود.

 مثال:

در سلول B1، ممکن است یک فاصله اضافی وجود داشته باشد که دیده نمی‌شود:

= A1 * B1

اگر مقدار B1 فقط یک فاصله باشد، گوگل شیت این مقدار را به‌عنوان متن در نظر گرفته و عملیات ریاضی ممکن نخواهد بود.

 راه‌حل:

برای حذف فضاهای اضافی از تابع TRIM() استفاده کنید:

= A1 * TRIM(B1)

• بیشتر بخوانید: نحوه قرار دادن Smart Chips در گوگل شیت

مشکل در فرمت تاریخ (اختلاف بین MM/DD/YYYY و DD/MM/YYYY)

گوگل شیت تاریخ‌ها را به‌عنوان عدد ذخیره می‌کند، اما اگر تاریخ در قالب نامناسبی وارد شود، ممکن است آن را به‌عنوان متن تشخیص دهد و این خطا رخ دهد.

مثال:

= DATEVALUE(“31/12/2024”) – DATEVALUE(“01/01/2024”)

اگر تنظیمات منطقه‌ای شیت شما به‌صورت آمریکایی باشد (MM/DD/YYYY)، تاریخ 31/12/2024 به‌عنوان متن شناخته شده و باعث #VALUE! می‌شود.

 راه‌حل:

تاریخ‌ها را با فرمت صحیح منطقه‌ای وارد کنید.

از تابع DATEVALUE() برای تبدیل متن به تاریخ معتبر استفاده کنید.

چگونه خطای #VALUE! را پیدا و رفع کنیم؟

 بررسی نوع داده‌ها:

• مطمئن شوید که همه اعداد به‌عنوان مقدار عددی وارد شده‌اند و متنی نیستند.
• اگر نیاز به تبدیل متن به عدد دارید، از VALUE() استفاده کنید.

حذف فاصله‌های اضافی:

• سلول‌های به‌ظاهر خالی را بررسی کنید که فاصله نامرئی نداشته باشند.
• برای حذف فاصله‌های اضافی، از TRIM() استفاده کنید.

تبدیل داده‌های تاریخ:

• تاریخ‌ها را در فرمت صحیح و متناسب با تنظیمات منطقه‌ای وارد کنید.
• در صورت نیاز، از DATEVALUE() برای تبدیل متن به تاریخ استفاده کنید.

استفاده از تابع IFERROR برای جلوگیری از نمایش خطا:

اگر نمی‌خواهید این خطا نمایش داده شود، می‌توانید از IFERROR() استفاده کنید تا یک مقدار جایگزین مشخص کنید:

= IFERROR(A1 + B1, “مقدار نامعتبر!”)

• اگر فرمول به درستی اجرا شود، مقدار صحیح نمایش داده می‌شود.
• در غیر این صورت، به‌جای #VALUE! پیام “مقدار نامعتبر!” نمایش داده خواهد شد.

بررسی و رفع خطای #REF! در گوگل شیت

خطای #REF! در Google Sheets هنگامی ظاهر می‌شود که فرمول شما به یک مرجع نامعتبر اشاره کند. این خطا اغلب به دلیل حذف سلول‌ها، استفاده نادرست از توابع جستجو و یا ایجاد یک حلقه وابستگی دایره‌ای رخ می‌دهد. در ادامه، دلایل اصلی این خطا و روش‌های رفع آن را بررسی خواهیم کرد.

دلایل اصلی بروز خطای #REF!

 ارجاع به سلول حذف‌شده (Missing Reference)

اگر فرمول شما به سلولی اشاره کند که کاملاً حذف شده است (نه فقط مقدار درون آن)، گوگل شیت نمی‌تواند مقدار مورد نظر را پیدا کند و در نتیجه، خطای #REF! را نمایش می‌دهد.

 مثال:

فرض کنید فرمول زیر را در یک سلول داریم:

= A1 * B1

اما اگر ستون A را حذف کنیم، فرمول دیگر به A1 دسترسی نخواهد داشت و به این شکل در می‌آید:

= #REF! * B1

راه‌حل:

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

کپی کردن فرمول با محدوده نامعتبر (Out-of-Bounds Reference)

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

 مثال:

= SUM(A1:A3)

اگر این فرمول را به سلولی منتقل کنیم که کمتر از ۳ ردیف بالای آن وجود دارد، گوگل شیت نمی‌تواند مقادیر مورد نیاز را پیدا کند و خطای #REF! نمایش داده خواهد شد.

راه‌حل:

• قبل از کپی کردن فرمول‌ها، محدوده‌های مورد نظر را بررسی کنید.
• در صورت نیاز، از محدوده‌های مطلق ($A$1:$A$3) استفاده کنید تا هنگام کپی‌برداری تغییر نکنند.

خطا در توابع جستجو (Lookup Out of Bounds)

زمانی که در یک تابع جستجو مانند VLOOKUP، مقدار مورد نظر را خارج از محدوده داده‌ها جستجو کنیم، این خطا رخ می‌دهد.

 مثال اشتباه:

= VLOOKUP(1001, A1:B10, 3, FALSE)

محدوده A1:B10 فقط ۲ ستون دارد، اما ما از ستون سوم مقدار درخواست کرده‌ایم که باعث بروز خطای #REF! می‌شود.

راه‌حل:

• مطمئن شوید که مقدار Index در تابع VLOOKUP بزرگ‌تر از تعداد ستون‌های محدوده داده‌ها نیست.
• اگر نیاز به جستجو در ستون‌های بیشتر دارید، محدوده جستجو را افزایش دهید.

وابستگی دایره‌ای (Circular Dependency)

اگر یک فرمول به خودش ارجاع دهد، به طوری که خروجی آن وابسته به مقدار خودش باشد، گوگل شیت دچار حلقه بی‌پایان (Loop) شده و خطای #REF! را نمایش می‌دهد.

مثال اشتباه:

= SUM(A1:A4)

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

راه‌حل:

• بررسی کنید که فرمول به سلول خود ارجاع نداده باشد.
• در صورت لزوم، محدوده فرمول را اصلاح کنید تا شامل سلول خود فرمول نشود.

چگونه خطای #REF! را برطرف کنیم؟

خواندن پیام خطا

هنگامی که خطای #REF! ظاهر می‌شود، گوگل شیت معمولاً پیامی همراه با آن نمایش می‌دهد که می‌تواند به شما کمک کند نوع خطا را مشخص کنید.

 بررسی فرمول و اصلاح مراجع حذف‌شده

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

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

• بیشتر بخوانید: 4 روش برای ایمپورت کردن داده های وب سایت به گوگل شیت

اصلاح خطای توابع جستجو

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

در صورت نیاز، محدوده داده‌ها را گسترش دهید.

 بررسی وابستگی دایره‌ای و اصلاح آن

از ارجاع به سلول خود در فرمول جلوگیری کنید.

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

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

اگر نمی‌خواهید خطای #REF! در جدول شما نمایش داده شود، می‌توانید از تابع IFERROR() برای نمایش یک مقدار جایگزین استفاده کنید:

= IFERROR(VLOOKUP(1001, A1:B10, 3, FALSE), “خطای مرجع!”)

• اگر فرمول درست عمل کند، مقدار واقعی نمایش داده می‌شود.
• اما اگر خطای #REF! رخ دهد، پیام “خطای مرجع!” نمایش داده می‌شود.

بررسی و رفع خطای #NAME? در گوگل شیت

خطای #NAME? در Google Sheets نشان‌دهنده وجود مشکل در نحوه نگارش (Syntax) فرمول شما است. این خطا معمولاً به دلیل اشتباه تایپی در نام تابع، ارجاع به محدوده نام‌گذاری نشده، یا فراموش کردن علامت نقل قول در متون رخ می‌دهد.

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

دلایل اصلی بروز خطای #NAME?

اشتباه تایپی در نام تابع (Misspelled Function Name)

اگر هنگام تایپ یک تابع در گوگل شیت، نام آن را اشتباه تایپ کنید، گوگل شیت نمی‌تواند آن را شناسایی کند و در نتیجه خطای #NAME? نمایش داده می‌شود.

 مثال اشتباه:

=SUMM(A1:A10)

در این مثال، تابع SUM به‌اشتباه SUMM نوشته شده است که در گوگل شیت وجود ندارد.

 راه‌حل:

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

ارجاع به محدوده نام‌گذاری نشده (Undefined Named Range)

اگر از یک نام متغیر (Named Range) استفاده کنید که قبلاً تعریف نشده باشد یا نام آن را اشتباه تایپ کنید، خطای #NAME? ایجاد می‌شود.

مثال اشتباه:

=SUM(profit)

در این مثال، گوگل شیت به دنبال یک محدوده profit می‌گردد، اما اگر چنین محدوده‌ای وجود نداشته باشد، خطای #NAME? نمایش داده می‌شود.

 راه‌حل:

از منوی Data → Named ranges بررسی کنید که محدوده موردنظر تعریف شده است.

مطمئن شوید که نام محدوده را درست تایپ کرده‌اید.

 فراموش کردن علامت نقل قول در متن‌ها (Missing Quotation Marks)

اگر در فرمول خود یک مقدار متنی استفاده کنید اما آن را داخل علامت نقل قول (” “) قرار ندهید، گوگل شیت آن را به‌عنوان یک مقدار ناشناس در نظر می‌گیرد و خطای #NAME? نمایش داده می‌شود.

 مثال اشتباه:

=CONCAT(“First”, Second)

در این مثال، عبارت Second بدون علامت نقل قول نوشته شده است که باعث بروز خطا می‌شود.

 راه‌حل:

تمام متون را داخل علامت نقل قول (” “) قرار دهید.

تصحیح فرمول:

=CONCAT(“First”, “Second”)

 فراموش کردن دو‌نقطه (:) در ارجاع به محدوده‌ها

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

مثال اشتباه:

=SUM(A1A10)

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

 راه‌حل:

تصحیح فرمول:

=SUM(A1:A10)

چگونه خطای #NAME? را برطرف کنیم؟

بررسی نام تابع

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

بررسی محدوده‌های نام‌گذاری شده

• در منوی Named Ranges بررسی کنید که محدوده موردنظر شما وجود دارد.
• اگر محدوده‌ای را با نام خاصی تعریف کرده‌اید، نام آن را به‌درستی در فرمول بنویسید.

قرار دادن نقل قول برای متون

• اگر در فرمول خود یک مقدار متنی استفاده می‌کنید، حتماً آن را داخل علامت نقل قول (” “) قرار دهید.

بررسی ارجاع به محدوده‌ها

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

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

اگر می‌خواهید خطای #NAME? را کنترل کنید، می‌توانید از تابع IFERROR() استفاده کنید تا در صورت بروز خطا، پیام مناسبی نمایش داده شود.

 مثال:

=IFERROR(SUM(A1:A10), “خطا در فرمول!”)

اگر فرمول درست کار کند، مقدار موردنظر نمایش داده می‌شود. اما اگر خطای #NAME? رخ دهد، پیام “خطا در فرمول!” نمایش داده می‌شود.

بررسی و رفع خطای #NUM! در گوگل شیت

خطای #NUM! در Google Sheets زمانی ظاهر می‌شود که مقدار عددی در فرمول نامعتبر باشد. این مشکل اغلب در محاسبات ریاضی، جستجوهای عددی یا توابعی که به ورودی‌های عددی وابسته‌اند، رخ می‌دهد.

دلایل بروز خطای #NUM!

محاسبات نامعتبر، مانند جذر یک عدد منفی

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

=SQRT(-9)

این فرمول نامعتبر است، زیرا گوگل شیت از اعداد موهومی (Complex Numbers) پشتیبانی نمی‌کند.

استفاده نادرست از توابع SMALL و LARGE

توابع SMALL و LARGE برای یافتن کوچک‌ترین یا بزرگ‌ترین مقدار nام در یک محدوده استفاده می‌شوند. اما اگر مقدار n از تعداد مقادیر موجود در داده‌ها بزرگ‌تر باشد، خطای #NUM! ایجاد می‌شود.

مثال:

=SMALL(A1:A5, 10)

اگر محدوده A1:A5 فقط 5 مقدار داشته باشد اما مقدار n=10 وارد شود، این فرمول خطای #NUM! خواهد داد.

ارزش‌های بیش از حد بزرگ یا کوچک

برخی محاسبات، مانند بهره مرکب یا تابع POWER، می‌توانند اعداد بسیار بزرگ تولید کنند که خارج از محدوده‌ی پشتیبانی‌شده‌ی گوگل شیت باشند. این امر می‌تواند به خطای #NUM! منجر شود.

عدم همخوانی داده‌های عددی

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

چگونه خطای #NUM! را برطرف کنیم؟

بررسی اعداد ورودی

اگر در حال انجام محاسبات ریاضی هستید، مقدار ورودی را بررسی کنید.

برای رفع مشکل جذر عدد منفی، از تابع IMAGINARY استفاده کنید تا عدد موهومی تولید شود.

محدوده مقادیر را بررسی کنید

اگر از توابع SMALL یا LARGE استفاده می‌کنید، مطمئن شوید که مقدار n کمتر یا برابر با تعداد کل مقادیر در داده‌های شما باشد.

مقدار ورودی را محدود کنید

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

استفاده از IFERROR برای جلوگیری از خطا

می‌توانید با استفاده از تابع IFERROR، خطای #NUM! را با یک پیام سفارشی جایگزین کنید.

مثال:

=IFERROR(SQRT(A1), “عدد معتبر وارد کنید”)

این فرمول در صورت ورود عدد منفی، به‌جای نمایش خطای #NUM!، پیام “عدد معتبر وارد کنید” را نمایش می‌دهد.

بررسی و رفع خطای #ERROR! در گوگل شیت

خطای #ERROR! در Google Sheets یک پیام خطای خاص است که در Microsoft Excel معادل مستقیمی ندارد. این خطا زمانی رخ می‌دهد که گوگل شیت نتواند فرمول شما را پردازش (Parse) کند، یعنی نمی‌داند چگونه آن را اجرا کند. این معمولاً به دلیل اشتباهات دستوری (Syntax Errors) در فرمول شما اتفاق می‌افتد.

دلایل بروز خطای #ERROR!

استفاده‌ی اشتباه از نماد $ در مقدار پولی

اگر $ را به‌عنوان بخشی از یک عدد وارد کنید، گوگل شیت ممکن است آن را به‌عنوان یک مرجع مطلق سلول (Absolute Reference) در نظر بگیرد و این باعث ایجاد خطا شود.

مثال اشتباه:

= $100 + A1

در اینجا، $100 به‌عنوان مقدار عددی در نظر گرفته نشده، بلکه گوگل شیت آن را به‌عنوان مرجع نادرست سلولی تفسیر کرده است.

 روش صحیح:

= 100 + A1

سپس می‌توان فرمت سلول را به ارز (Currency) تغییر داد.

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

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

مثال اشتباه:

= “جمع کل” SUM(A1:A3)

در اینجا، تابع SUM مستقیماً بعد از متن نوشته شده و این باعث خطا شده است.

 روش صحیح:

= “جمع کل: ” & SUM(A1:A3)

در این روش، از & برای ترکیب متن و مقدار عددی استفاده شده است.

 بستن نادرست پرانتزها

اگر فرمول شما دارای تعداد نامتعادل پرانتزها (Parentheses) باشد، گوگل شیت نمی‌تواند آن را پردازش کند و خطای #ERROR! را نمایش می‌دهد.

 مثال اشتباه:

= (A1 + A2 * (B1 – B2)

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

 روش صحیح:

= (A1 + A2) * (B1 – B2)

همیشه تعداد پرانتزهای باز و بسته را بررسی کنید تا مطمئن شوید که متعادل هستند.

چگونه خطای #ERROR! را برطرف کنیم؟

بررسی دقیق فرمول برای رفع اشتباهات دستوری

تعداد پرانتزهای باز و بسته را چک کنید.

علامت‌های & را برای ترکیب متن و اعداد بررسی کنید.

استفاده از تنظیمات فرمت سلول برای نمایش ارز و درصد

به‌جای تایپ دستی $ یا %، مقدار را به‌صورت ساده وارد کنید و از تنظیمات Format > Number برای تبدیل آن به ارز یا درصد استفاده کنید.

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

اگر می‌خواهید در صورت وقوع خطا مقدار مشخصی نمایش داده شود، می‌توانید از IFERROR استفاده کنید:

=IFERROR(“جمع کل: ” & SUM(A1:A3), “فرمول نادرست است”)

در صورت وجود خطا، این فرمول “فرمول نادرست است” را نمایش خواهد داد.

• بیشتر بخوانید: نحوه استفاده از تابع SumIf در گوگل شیت

استراتژی‌های دیگر برای رفع خطاهای فرمول در Google Sheets

خطاهای فرمول در Google Sheets می‌توانند به دلایل مختلفی رخ دهند. در اینجا، چند روش موثر و پیشرفته برای شناسایی و رفع این خطاها ارائه شده است:

بررسی هایلایت‌های قرمز در فرمول

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

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

تکنیک “لایه‌برداری از پیاز” (Peeling Back the Onion) برای خطایابی فرمول‌های پیچیده

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

 مراحل:

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

به تدریج توابع را اضافه کنید – هر تابع را یکی‌یکی اضافه کنید و مشاهده کنید که در کدام مرحله خطا ظاهر می‌شود.

مشخص کنید که کدام قسمت مشکل دارد – پس از شناسایی بخش مشکل‌دار، آن را اصلاح کنید.

 مثال:

فرمول زیر دارای خطا است:

=IF(A1>10, VLOOKUP(A1, Sheet2!A:B, 2, FALSE) + SUM(A2:A10))

گام ۱: ابتدا VLOOKUP را حذف کنید و ببینید آیا خطا برطرف می‌شود.

گام ۲: سپس SUM(A2:A10) را بررسی کنید.

گام ۳: در نهایت، مقدار IF(A1>10, …) را تست کنید.

تفاوت‌های نحوی در کشورهای مختلف

در برخی کشورها (مانند کشورهای اروپایی)، نمادهای جداکننده‌ی مقادیر در فرمول‌ها متفاوت هستند.

مقایسه:

🇺🇸 فرمول استاندارد (آمریکا و بسیاری از کشورها):

=ArrayFormula(VLOOKUP(A1, Sheet2!A:I, {2,3,4,5,6,7,8}, FALSE))

🇩🇪 فرمول در برخی کشورهای اروپایی:

=ArrayFormula(VLOOKUP(A1; Sheet2!A:I; {2\3\4\5\6\7\8}; FALSE))

راه‌حل: اگر خطای فرمولی دریافت کردید، مطمئن شوید که از جداکننده‌ی مناسب (, یا ; یا \) مطابق با تنظیمات زبان و منطقه (Locale) استفاده می‌کنید.

استفاده از آپاستروف (‘) برای غیرفعال کردن فرمول

اگر می‌خواهید یک فرمول اجرا نشود ولی همچنان آن را در صفحه مشاهده کنید، می‌توانید در ابتدای آن یک آپاستروف (‘) اضافه کنید. این کار باعث می‌شود که فرمول به‌عنوان متن در نظر گرفته شود.

 مثال:

‘=SUM(A1:A10)

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

توابع کاربردی برای مدیریت خطاهای فرمول در Google Sheets

Google Sheets چندین تابع مفید برای شناسایی و مدیریت خطاهای فرمول دارد. در اینجا به معرفی این توابع و نحوه استفاده از آن‌ها می‌پردازیم.

تولید خطای #N/A با تابع NA()

تابع NA() به‌طور خاص برای ایجاد خطای #N/A طراحی شده است.

 کاربرد:

این تابع می‌تواند در اعتبارسنجی داده‌ها (Data Validation) در فرمول‌های پیچیده مفید باشد.

مثال:

=NA()

خروجی این فرمول #N/A خواهد بود.

شناسایی نوع خطا با ERROR.TYPE(value)

تابع ERROR.TYPE(value) عددی را بازمی‌گرداند که نشان‌دهنده‌ی نوع خطای رخ‌داده در یک مقدار است.

کدهای خطا در ERROR.TYPE

عدد خروجی	نوع خطا
1	#NULL!
2	#DIV/0!
3	#VALUE!
4	#REF!
5	#NAME?
6	#NUM!
7	#N/A
8	سایر خطاها

مثال:

=ERROR.TYPE(A1)

اگر سلول A1 دارای خطای #REF! باشد، مقدار ۴ بازگردانده می‌شود.

بررسی خطای #N/A با ISNA(value)

تابع ISNA(value) بررسی می‌کند که مقدار موردنظر #N/A است یا خیر.

 مثال:

=ISNA(A1)

اگر مقدار A1 برابر #N/A باشد، مقدار TRUE برمی‌گردد، در غیر این صورت مقدار FALSE خواهد بود.

 بررسی همه‌ی خطاها به‌جز #N/A با ISERR(value)

تابع ISERR(value) بررسی می‌کند که مقدار موردنظر خطاست، اما #N/A نباشد.

 مثال:

=ISERR(A1)

اگر مقدار A1 یکی از خطاهای #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! باشد، مقدار TRUE برمی‌گردد.

اما اگر مقدار A1 برابر #N/A باشد، مقدار FALSE خواهد بود.

 بررسی تمامی خطاها با ISERROR(value)

تابع ISERROR(value) بررسی می‌کند که آیا مقدار موردنظر دارای هر نوع خطایی هست یا نه.

 مثال:

=ISERROR(A1)

اگر مقدار A1 دارای هر نوع خطایی باشد، مقدار TRUE برمی‌گردد.

در غیر این صورت مقدار FALSE خواهد بود.

جدول خلاصه توابع مدیریت خطا در Google Sheets

تابع	عملکرد	خروجی (TRUE/FALSE یا عدد)
NA()	تولید خطای #N/A	#N/A
ERROR.TYPE(value)	بازگرداندن کد عددی خطا	1 تا 8
ISNA(value)	بررسی #N/A بودن مقدار	TRUE/FALSE
ISERR(value)	بررسی همه خطاها به‌جز #N/A	TRUE/FALSE
ISERROR(value)	بررسی همه‌ی خطاها	TRUE/FALSE

جمع بندی

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

#N/A: مقدار یافت نشد (معمولاً در VLOOKUP). بررسی کنید که مقدار موردنظر در محدوده جستجو وجود داشته باشد.

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

#VALUE!: ناسازگاری نوع داده (مثلاً متن به‌جای عدد). نوع ورودی‌ها را تصحیح کنید.

#REF!: ارجاع به سلول حذف‌شده. فرمول را اصلاح کنید.

#NUM!: مقادیر عددی نامعتبر (مثل جذر عدد منفی). ورودی‌ها را بررسی کنید.

#ERROR!: فرمول نامفهوم برای Google Sheets. سینتکس را بازبینی کنید.

برای رفع خطاها، به پیام خطا، فرمول و مقادیر ورودی دقت کنید. همچنین، استفاده از IFERROR و توابعی مانند ISERROR می‌تواند به مدیریت بهتر خطاها کمک کند.