آموزش اکسل؛ ۵ روش تشخیص خطاهای پنهان و فرمول‌های اشتباه

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

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

بررسی پیوندهای خارجی در اکسل و جلوگیری از محاسبات اشتباه

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

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

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

برای بررسی نام‌های تعریف‌شده نیز مسیر زیر را باز کنید:

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

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

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

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

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

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

برای بررسی دقیق‌تر کل فایل، از مسیر زیر استفاده کنید:

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

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

به عنوان مثال، برای جمع‌زدن مقادیر ستون H از ردیف ۲ تا ۴۱ با نادیده گرفتن ردیف‌های فیلترشده و مخفی‌شده به‌صورت دستی، فرمول زیر را وارد کنید:

عدد ۱۰۹ در این فرمول باعث می‌شود جمع مقادیر قابل مشاهده محاسبه شود و ردیف‌های مخفی‌شده به‌صورت دستی نیز کنار گذاشته شوند. این روش برای گزارش‌هایی که از فیلتر استفاده می‌کنند کاربرد زیادی دارد؛ بااین‌حال، برای اطمینان از صحت گزارش، بهتر است علت مخفی بودن ردیف‌ها را هم بررسی کنید.

تبدیل اعداد متنی به عدد واقعی در اکسل

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

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

به عنوان مثال، ممکن است جمع یک ستون شامل ۴۰ سلول به‌جای مقدار مورد انتظار، فقط ۳۵ مقدار عددی را محاسبه کند؛ زیرا پنج سلول دیگر به‌صورت متن ذخیره شده‌اند. در چنین شرایطی، حتی اگر سایر فرمول‌های جدول درست به نظر برسند، جمع نهایی می‌تواند اشتباه باشد.

برای تشخیص سریع، سلول‌های ستون موردنظر را انتخاب کنید و مقادیر Count و Numerical Count را در نوار وضعیت پایین پنجره اکسل بررسی کنید. اختلاف این دو مقدار می‌تواند نشانه‌ای از وجود داده‌های غیرعددی باشد؛ البته سلول‌های خالی و داده‌های متنی دیگر نیز باید در نظر گرفته شوند.

اگر اکسل مثلث سبزرنگ و هشدار ذخیره‌شدن عدد به‌صورت متن را نشان می‌دهد، سلول‌های موردنظر را انتخاب کنید و از منوی هشدار گزینه Convert to Number را انتخاب کنید.

اگر این گزینه نمایش داده نمی‌شود، می‌توانید از روش‌های دیگری مانند تبدیل داده‌ها با ابزار Text to Columns یا استفاده از فرمول‌های تبدیل متن به عدد کمک بگیرید. در فایل‌هایی که جداکننده اعشار یا هزارگان متفاوت دارند، تنظیمات منطقه‌ای اکسل نیز اهمیت دارد.

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

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

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

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

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

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

در جدول‌های بزرگ‌تر، ابزار Go To Special نیز مفید است. برای استفاده از آن مسیر زیر را دنبال کنید:

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

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

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

پیدا کردن فرمول‌های اشتباه و ناهماهنگ در سلول‌های اکسل

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

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

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

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

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

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

برای بررسی دقیق‌تر ارتباط سلول‌ها، قابلیت Trace Precedents در تب Formulas نشان می‌دهد که فرمول از کدام سلول‌ها اطلاعات دریافت می‌کند. این ابزار به‌ویژه برای فرمول‌های پیچیده و گزارش‌هایی با چندین کاربرگ کاربرد دارد.

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

چطور فایل اکسل را پیش از ارسال یا استفاده نهایی بررسی کنیم؟

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

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

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

سیاره‌ی آی‌تی