یکی از همکاران گزارش فروش سه‌ماهه را می‌فرستد؛ جمع‌ها مرتب و بی‌نقص به نظر می‌رسند و مهلت تحویل هم وسوسه‌ام می‌کند آنها را یکراست در اسلایدهای خودم بچسبانم. اما این کار را نمی‌کنم. دفتر کاری که شخص دیگری ساخته، اغلب مشکلات خاموشی دارد که هرگز به‌شکل خطا نمایان نمی‌شوند؛ بنابراین ممکن است اعداد روی صفحه نادرست باشند، در حالی که تک‌تک سلول‌ها کاملاً سالم به نظر می‌رسند.
پیش از استفاده دوباره از هر عدد، حدود پنج دقیقه برای انجام پنج بررسی وقت می‌گذارم. هیچ‌کدام به افزونه نیاز ندارند، زیرا بیشترشان بر ابزارهایی مانند اشکال‌زدای فرمول تکیه دارند که در بخش حسابرسی فرمول‌های اکسل پنهان شده است. این بررسی‌ها را به همان ترتیبی انجام می‌دهم که هنگام باز کردن فایل پیش می‌آیند.
پیش از هر کاری، پیوندهای خارجی را بررسی کنید
فایلی که پنهانی به فایل دیگری وابسته است
نخستین چیزی که در یک دفتر کار دریافتی بررسی می‌کنم، نوار زردرنگ زیر نوار ابزار است. در صفحه‌گسترده من، پیام «به‌روزرسانی خودکار پیوندها غیرفعال شده است» دیده می‌شد؛ یعنی بعضی سلول‌ها مقدارشان را از فایل دیگری می‌گیرند. اگر آن فایل روی لپ‌تاپ یکی از همکاران باشد، این اعداد در همان وضعی باقی می‌مانند که هنگام آخرین ذخیره‌سازی داشته‌اند.
برای دیدن پیوندها، به مسیر «داده‌ها > پیوندهای دفتر کار» بروید. در این بخش، فایلی با نام CommissionRates.xlsx دیده می‌شد و با کلیک روی هر سلول کمیسیون، مسیر کامل آن فایل درون یک فرمول VLOOKUP نمایش داده می‌شد. این بخش همچنین هشدار داد که پیوند در یک نام تعریف‌شده و یک ردیف پنهان وجود دارد؛ دو جایی که هرگز به‌صورت دستی بررسی‌شان نمی‌کردم.
مسیر «فرمول‌ها > مدیر نام» نیز این موضوع را تأیید کرد. نامی با عنوان RegionTargets به دفتر کاری بیرونی اشاره داشت، با اینکه هیچ سلولی از آن استفاده نمی‌کرد. اگر به فایل مبدأ دسترسی دارید، پیوند را تازه‌سازی کنید. در غیر این صورت، گزینه «قطع همه پیوندها» هر فرمول پیوندخورده را به مقدار کنونی آن تبدیل می‌کند تا گزارش دیگر به فایلی که هرگز نخواهید دید وابسته نباشد.
قطع پیوندها بازگشت‌پذیر نیست. ابتدا نسخه‌ای از دفتر کار ذخیره کنید تا همچنان بتوانید ببینید هر فرمول به کجا اشاره داشته است.
پیش از اعتماد به جمع‌ها، همه ردیف‌ها را آشکار کنید
پرش شماره ردیف‌ها راز را برملا می‌کند
ردیف‌های پنهان به‌آسانی از چشم دور می‌مانند، زیرا اکسل وجودشان را اعلام نمی‌کند. تنها نشانه، شماره ردیف‌هاست. در برگه فروش، ردیف ۱۴ درست بالای ردیف ۱۸ قرار داشت و شماره‌ها از ردیف ۳۰ به ۳۲ می‌پریدند. چهار سفارش پنهان شده بود که هر چهار مورد لغوشده بودند.
نکته دردسرساز این است که SUM به نمایان یا پنهان بودن ردیف‌ها توجهی ندارد. جمع مبلغ فروش ۱۳۰٬۲۱۰٫۷۴ دلار بود که ۲۵٬۸۱۱٫۶۴ دلار سفارش لغوشده را نیز در بر می‌گرفت. وارد کردن =SUBTOTAL(109, H2:H41) عدد ۱۰۴٬۳۹۹٫۱۰ دلار را برگرداند که فقط مربوط به ردیف‌های نمایان بود؛ نمونه خوبی از اینکه چرا SUBTOTAL اغلب انتخاب مطمئن‌تری از SUM است. این رقم تا آخرین سنت با برگه خلاصه مطابقت داشت.
برای بررسی کامل، به مسیر «فایل > اطلاعات > بررسی مشکلات > بازرسی سند» بروید. این ابزار وجود چهار ردیف پنهان را گزارش کرد و برگه‌های پنهان را نیز بررسی می‌کند. برای آشکار کردن ردیف‌ها، ردیف‌های دو سوی فاصله را برگزینید، روی سرآیند یکی از آنها راست‌کلیک کنید و گزینه «آشکار کردن» را بزنید.
البته گاهی پنهان کردن ردیف‌ها عمدی است؛ برای نمونه، داده‌های قدیمی را کنار می‌گذارند تا گزارش خوانا بماند. اگر این موضوع مشخص شده باشد، ایرادی ندارد. بااین‌حال ترجیح می‌دهم در زبانه «داده‌ها» از قابلیت «گروه‌بندی» استفاده شود که دکمه مثبت را در دیدرس نگه می‌دارد؛ آن هم در کنار SUBTOTAL تا جمع نهایی با آنچه روی صفحه دیده می‌شود هم‌خوان باشد.
اعدادی را پیدا کنید که اکسل متن می‌پندارد
رقم‌های چپ‌چینی که SUM نادیده می‌گیرد
عددهایی که از خروجی CSV یا یک صفحه وب چسبانده می‌شوند، اغلب به‌شکل متن وارد سلول می‌شوند. ظاهرشان عددی است، اما اکسل آنها را مانند واژه‌ها نگه می‌دارد. نشانه‌های این مشکل، چپ‌چین بودن و وجود مثلث سبز کوچکی در گوشه سلول است. در برگه من، پنج سلول از ستون «تعداد فروخته‌شده» هر دو نشانه را داشتند.
آسیب اصلی در جمع نهایی آشکار می‌شود. مجموع ستون «تعداد فروخته‌شده» ۱٬۰۲۵ بود، در حالی که مقدار واقعی ۱٬۱۲۶ بود؛ زیرا SUM تعداد ۱۰۱ واحد را نادیده گرفته بود. عجیب اینکه مبلغ فروش همان ردیف‌ها درست محاسبه شده بود، چون عمل ضرب متن را همان لحظه به عدد تبدیل می‌کند. فقط جمع‌ها اشتباه بودند و به همین دلیل این مشکل اغلب از چشم کاربران دور می‌ماند.
سریع‌ترین راه آزمایش، استفاده از نوار وضعیت است. ستون را برگزینید و مقدار «شمارش» را با «شمارش عددی» مقایسه کنید. برای من این دو مقدار به‌ترتیب ۴۰ و ۳۵ بودند؛ پس پنج سلول عدد به شمار نمی‌آمدند. برای اصلاحشان، سلول‌های علامت‌خورده را برگزینید، روی نماد هشدار کلیک کنید و گزینه «تبدیل به عدد» را بزنید. تنظیم قالب‌های عددی مناسب که از به‌هم‌ریختن برگه جلوگیری کنند، احتمال تکرار این مشکل را نیز کاهش می‌دهد.
جمع‌هایی را آشکار کنید که دستی وارد شده‌اند
جمعی که هرگز به‌روز نمی‌شود
جمع کلِ سخت‌کدنویسی‌شده، عددی است که به‌جای فرمول به‌صورت دستی وارد شده است. ظاهرش هیچ تفاوتی با نتیجهٔ محاسبه‌شده ندارد و تا زمانی درست می‌ماند که کسی داده‌ها را ویرایش کند. در برگهٔ خلاصه، برای مناطق شمال، جنوب و شرق از SUMIFS استفاده شده بود، اما جمع منطقهٔ غرب عددِ دستیِ ۴۲۷۹۴.۹۱ بود.
برای روشن‌کردن «نمایش فرمول‌ها»، کلیدهای Ctrl + ` را فشار دهید؛ ` همان نشانهٔ تکیهٔ سنگین و کلید سمت چپ ۱ است. با این کار، متن همهٔ فرمول‌ها نمایان می‌شود و عددی ساده در میان ستونی از فرمول‌ها بی‌درنگ به چشم می‌آید. در برگه‌های بزرگ‌تر، سلول‌های جمع کل را انتخاب می‌کنم، به «خانه > یافتن و انتخاب > رفتن به مورد ویژه» می‌روم، «ثابت‌ها» را برمی‌گزینم و فقط گزینهٔ «اعداد» را علامت‌خورده نگه می‌دارم. Excel تنها B7 را انتخاب کرد و بس.
راه‌حل این است که مقدار دستی را با همان فرمولی جایگزین کنید که سلول‌های کناری به کار می‌برند. به این ترتیب، اگر کسی بعداً یکی از سفارش‌های منطقهٔ غرب را اصلاح کند، جمع کل نیز خودکار به‌روز می‌شود.
فرمول ناسازگار با الگو را پیدا کنید
یک سلول با ارجاعی متفاوت
این پنهان‌کارترینِ آن پنج خطاست. هر سلولِ «مبلغ فروش»، تعداد واحدهای فروخته‌شده را در قیمت واحدِ همان ردیف ضرب می‌کرد؛ به‌جز H24 که فرمولش =F24*G23 بود. این فرمول قیمت را از ردیف بالایی می‌گرفت و در نتیجه، سفارشی به ارزش $۶,۰۰۳.۸۱ با مبلغ $۷۷۸.۸۱ نمایش داده می‌شد. چنین لغزش ساده‌ای هنگام کپی و جای‌گذاری ممکن است برای هر کسی پیش بیاید.
خوشبختانه Excel این خطا را تشخیص می‌دهد. مثلثی سبز در گوشهٔ سلول پدیدار می‌شود و با نگه‌داشتن نشانگر روی نماد هشدار، پیام «فرمول ناسازگار» همراه با توضیحی دیده می‌شود که می‌گوید فرمول این سلول با فرمول‌های پیرامونش تفاوت دارد. برای بررسی سراسر یک کارپوشه، به «فرمول‌ها > بررسی خطا» بروید تا Excel سلول‌های علامت‌گذاری‌شده را یکی‌یکی بررسی کند.
برای اصلاح خطا، گزینهٔ «کپی‌کردن فرمول از بالا» را برگزینید. اگر این تفاوت عمدی است، مثلاً برای تخفیفی استثنایی، روی «نادیده‌گرفتن خطا» کلیک کنید و در سلول یادداشتی بگذارید تا نفر بعدی ناچار به حدس‌زدن نباشد. بررسی خطا در پس‌زمینه به‌طور پیش‌فرض روشن است، اما بهتر است وضعیت آن را در «پرونده > گزینه‌ها > فرمول‌ها» نیز بررسی کنید.
خطایی پیدا کرده‌اید؟ آن را به info@www.makeuseof.com گزارش دهید تا اصلاح شود.
Make the next person's audit shorterتغییراتی که پیش از فرستادن فایل‌ها انجام می‌دهم
بررسی فایل‌های دیگران بر شیوهٔ ساخت فایل‌های خودم اثر گذاشته است. پیش از اشتراک‌گذاری کارپوشه، «بازرسی سند» را اجرا می‌کنم، ورودی‌ها را در بخشی مشخص و برچسب‌خورده نگه می‌دارم و داده‌ها را به‌شکل جدول درمی‌آورم تا ردیف‌های تازه همان فرمول را خودکار دریافت کنند. این کار فقط چند دقیقه زمان می‌برد و طرف مقابل را از پنج دقیقه بدگمانی و وارسی بی‌نیاز می‌کند.
اگر فایلی که به دستتان رسیده حتی در یکی از این بررسی‌ها رد شد، آن را از نو نسازید. همان مشکل را برطرف کنید، تغییراتتان را یادداشت کنید و پیش از آنکه اعداد در جای مهمی به کار روند، باقی برگهٔ آشفته را نیز سامان دهید.
منبع: این مطلب ترجمه و بومی‌سازی مقاله‌ای از MakeUseOf به قلم Yasir Mahmood است. مشاهده مقاله اصلی