چرا جمع فایل اکسل متره با ریزمتره دستی فرق می‌کند؟
اختلاف جمع فایل اکسل متره با ریزمتره دستی معمولاً خطای کاربر نیست؛ ریشه آن در گرد کردن نمایشی و دقت ۱۵ رقمی اکسل است. علت و راه کنترل آن را ببینید.
در دفتر فنی، یکی از آزاردهنده‌ترین لحظه‌ها جایی است که جمع ستون «مقدار» در فایل اکسل متره، چند صدم یا چند دهم با ریزمتره دستی اختلاف دارد. اختلاف آن‌قدر کوچک است که به نظر اشتباه محاسباتی نمی‌آید، و آن‌قدر مشخص است که ناظر یا کارفرما رویش دست می‌گذارد. نکته این است که در بیشتر موارد این اختلاف خطای شما نیست؛ رفتار عادی و مستندشده اکسل است.
پاسخ کوتاه. اکسل عددی را که نمایش می‌دهد جمع نمی‌زند؛ عدد کامل ذخیره‌شده در سلول را جمع می‌زند. وقتی اعشار را با قالب‌بندی کم می‌کنید فقط نمایش تغییر می‌کند و رقم‌های پنهان همچنان در محاسبه شرکت دارند. جمع این اختلاف‌های کوچک در یک فایل متره چندصد سطری، خودش را به شکل اختلاف نهایی نشان می‌دهد.
ریشه ماجرا: عددی که می‌بینید با عددی که جمع می‌شود یکی نیست
قالب‌بندی، مقدار را تغییر نمی‌دهد. اگر طول دیوار را ۳٫۲۳۴۹ متر وارد کرده باشید و سلول را روی دو رقم اعشار تنظیم کنید، اکسل ۳٫۲۳ نشان می‌دهد ولی همان ۳٫۲۳۴۹ را در محاسبات به کار می‌برد.
خطا انباشته می‌شود. در یک سطر، اختلاف در حد چند ده‌هزارم است و کسی متوجه نمی‌شود. در یک برگه متره با چند صد سطر، همین اختلاف‌ها روی هم جمع می‌شوند و جمع فصل را از عددی که خودتان روی کاغذ حساب کرده‌اید جدا می‌کنند.
چرا اول از همه در متره دیده می‌شود. چون متره پر از ضرب‌های پیاپی است: طول در عرض، در ارتفاع، در تعداد. هر ضرب، رقم‌های اعشاری بیشتری تولید می‌کند و فاصله بین عدد نمایشی و عدد واقعی را بازتر می‌کند. اگر با ساختار فرمول‌نویسی متره آشنا نیستید، مرور فرمول‌های کاربردی اکسل در متره و برآورد نقطه شروع خوبی است.
دقت ۱۵ رقمی اکسل؛ محدودیتی که قابل دور زدن نیست
اکسل با ممیز شناور کار می‌کند. اکسل اعداد را در قالب ممیز شناور دودویی ۶۴ بیتی بر پایه استاندارد IEEE 754 ذخیره می‌کند و تنها ۱۵ رقم بامعنا را نگه می‌دارد؛ رقم‌های بعد از پانزدهمین رقم به صفر تبدیل می‌شوند.
بسیاری از کسرهای اعشاری دقیق ذخیره نمی‌شوند. عددی مثل ۰٫۱ در مبنای دو نمایش دقیق ندارد، بنابراین اکسل نزدیک‌ترین مقدار ممکن را ذخیره می‌کند. این اختلاف بی‌نهایت کوچک در یک محاسبه بی‌اهمیت است، اما در جمع‌های طولانی خودش را نشان می‌دهد.
نتیجه عملی برای مترور. انتظار نداشته باشید فایل اکسل عدد را «دقیقاً» مثل ماشین‌حساب دستی بدهد. به جای آن باید تعیین کنید کجا و با چه دقتی گرد می‌کنید.
تفاوت Decrease Decimal با تابع ROUND
دکمه کاهش اعشار فقط ظاهر را عوض می‌کند. این دکمه هیچ تغییری در مقدار ذخیره‌شده ایجاد نمی‌کند و به همین دلیل مشکل اختلاف جمع را حل نمی‌کند.
تابع ROUND مقدار را واقعاً گرد می‌کند. نوشتن ROUND(A2*B2,2) باعث می‌شود عددی که در سلول می‌نشیند همان عددی باشد که می‌بینید. از آن پس، جمع ستون با جمع دستی یکی خواهد بود.
جای درست ROUND مهم است. گرد کردن نتیجه نهایی با ROUND(SUM(...),2) با گرد کردن تک‌تک سطرها و بعد جمع زدن، دو عدد متفاوت می‌دهد. در متره و صورت‌وضعیت، معمولاً گرد کردن در سطح هر ردیف منطقی‌تر است، چون همان عدد ردیف است که در جدول صورت‌وضعیت درج و امضا می‌شود.
کجای فایل متره باید گرد کنید
| محل | کار درست | نتیجه |
|---|---|---|
| ابعاد برداشت‌شده از نقشه | ثبت عدد کامل، بدون گرد کردن | امکان ردیابی و بازبینی ریزمتره حفظ می‌شود |
| ستون مقدار هر ردیف ریزمتره | اعمال ROUND با دقت توافق‌شده در قرارداد | جمع ستون با عدد نمایش‌داده‌شده یکی می‌شود |
| جمع هر فصل یا صورت‌وضعیت | ROUND روی نتیجه جمع ردیف‌های گردشده | عددی که در جلسه قابل دفاع است |
| تبدیل واحد (مثلاً سانتی‌متر به متر) | گرد کردن پس از تبدیل، نه پیش از آن | خطای تبدیل روی هم انباشته نمی‌شود |
دقت گرد کردن را در قرارداد بنویسید. اینکه مقادیر تا دو رقم اعشار گرد شوند یا سه رقم، یک توافق است نه یک قاعده طبیعی. اگر پیش از شروع کار مشخص شود، بحث بر سر اختلاف چند صدمی در جلسه تأیید صورت‌وضعیت اصلاً پیش نمی‌آید.
گزینه‌ای که نباید بدون فکر روشن کنید
Set precision as displayed چه می‌کند. این گزینه در مسیر File ← Options ← Advanced و بخش «When calculating this workbook» قرار دارد و مقدار ذخیره‌شده همه سلول‌های کاربرگ را با قالب نمایشی‌شان یکسان می‌کند.
چرا خطرناک است. مایکروسافت هنگام فعال کردن این گزینه هشدار می‌دهد که داده‌های کاربرگ برای همیشه دقت خود را از دست می‌دهند. رقم‌های حذف‌شده بازگردانده نمی‌شوند و این تغییر قابل بازگشت نیست. برای فایل متره‌ای که ابعاد خام برداشت‌شده از نقشه در آن ثبت شده، این یعنی از دست رفتن مبنای بازبینی.
جایگزین امن. به جای این گزینه، ROUND را دقیقاً در همان ستون‌هایی به کار ببرید که خروجی رسمی محسوب می‌شوند و ستون‌های ورودی را دست‌نخورده نگه دارید.
سقف‌های فنی اکسل که در فایل‌های متره بزرگ به آن‌ها می‌خورید
این اعداد از مستندات رسمی مایکروسافت برای نسخه‌های دسکتاپ اکسل (نسخه ۲۰۰۷ به بعد) گرفته شده‌اند. در نسخه ۲۰۰۳ و پیش از آن، سقف سطرهای یک کاربرگ ۶۵٬۵۳۶ بود و همین موضوع هنوز هنگام باز کردن فایل‌های قدیمی با پسوند xls دردسر می‌سازد.
| محدودیت | مقدار | چرا در فایل متره مهم است |
|---|---|---|
| حداکثر ابعاد یک کاربرگ | ۱٬۰۴۸٬۵۷۶ سطر × ۱۶٬۳۸۴ ستون | سقف واقعی ریزمتره در یک شیت؛ پروژه‌های بزرگ باید به چند شیت یا بانک اطلاعاتی تقسیم شوند |
| حداکثر کاراکتر یک سلول | ۳۲٬۷۶۷ کاراکتر | شرح‌های طولانی ردیف و توضیحات محل اجرا در همین سقف جا می‌شوند |
| حداکثر آرگومان یک تابع | ۲۵۵ | زنجیره‌های طولانی SUMIFS و شرط‌های چندگانه به این سقف می‌خورند |
| حداکثر طول محتوای یک فرمول | ۸٬۱۹۲ کاراکتر | فرمول‌های شرطی تودرتو که برای تفکیک آیتم‌های فهرست‌بها نوشته می‌شوند |
| دقت عددی | ۱۵ رقم بامعنا | سرچشمه اصلی همان اختلاف جمعی که بالاتر توضیح داده شد |
وقتی فایل به این سقف‌ها نزدیک می‌شود، مسئله دیگر فرمول‌نویسی نیست. فایلی که به مرز آرگومان‌ها یا طول فرمول رسیده، عملاً نگهداری‌پذیر نیست و هر بازبینی‌اش ریسک خطای جدید دارد. در این مرحله بهتر است محاسبه از فایل بیرون بیاید. برای محاسبات پرتکرار مثل برآورد مصالح یا تبدیل واحد، ابزارهای آنلاین متره و محاسبه مصالح آرنوکس همان منطق را بدون وابستگی به سلامت یک فایل اکسل اجرا می‌کنند و خروجی‌شان بین افراد تیم یکسان است.
چند عادت که اختلاف را از ابتدا حذف می‌کند
ورودی و خروجی را جدا کنید. ستون‌های ابعاد خام را دست نزنید و ستون‌های گردشده را جداگانه بسازید. این کار بازبینی متره را برای ناظر هم ساده می‌کند.
واحدها را یکدست کنید. ترکیب سانتی‌متر و متر در یک برگه، منبع خطایی جدی‌تر از گرد کردن است. همین اصل در محاسبات وزنی هم صدق می‌کند؛ نمونه‌اش را در محاسبه وزن میلگرد فونداسیون از روی نقشه می‌بینید.
یک سطر کنترل بگذارید. یک سلول اختلاف بین جمع ستون گردشده و جمع ستون خام بسازید. اگر این عدد از آستانه تعیین‌شده بیشتر شد، یعنی جایی از منطق فایل ایراد دارد.
پرسش‌های متداول
چرا جمع اکسل من دقیقاً یک صدم با جمع دستی فرق دارد؟ چون سلول‌ها فقط از نظر نمایشی گرد شده‌اند و مقادیر واقعی پشت آن‌ها کمی متفاوت است. جمع مقادیر واقعی پس از گرد شدن نمایشی، یک صدم بالاتر یا پایین‌تر از جمع اعداد دیده‌شده در می‌آید.
آیا کم کردن اعشار با دکمه Decrease Decimal عدد را گرد می‌کند؟ خیر. این دکمه فقط قالب نمایش را عوض می‌کند و مقدار ذخیره‌شده دست‌نخورده باقی می‌ماند. برای گرد کردن واقعی باید از تابع ROUND استفاده کنید.
گزینه Set precision as displayed را روشن کنم؟ برای فایل متره توصیه نمی‌شود، چون مقادیر خام را برای همیشه از بین می‌برد و قابل بازگشت نیست. اگر ناچار شدید، حتماً روی یک نسخه پشتیبان اجرایش کنید.
فایل متره من به سقف سطرهای اکسل خورده، چه کنم؟ سقف یک کاربرگ ۱٬۰۴۸٬۵۷۶ سطر است و افزایش‌پذیر نیست. راه‌حل، تفکیک متره به چند کاربرگ بر اساس بلوک یا فصل، یا انتقال داده به یک بانک اطلاعاتی و گرفتن گزارش از آن است.
جمع‌بندی
اختلاف جمع در فایل متره تقریباً همیشه ریشه فنی دارد، نه ریشه انسانی: اکسل عدد کامل را جمع می‌زند و شما عدد گردشده را می‌بینید. اگر دقت گرد کردن را از ابتدا تعیین کنید، ROUND را در جای درست به کار ببرید و ورودی خام را از خروجی رسمی جدا نگه دارید، این اختلاف عملاً حذف می‌شود. برای پروژه‌هایی که حجم متره و صورت‌وضعیت‌شان از ظرفیت یک فایل اکسل قابل‌اتکا بیرون زده، سپردن متره و صورت‌وضعیت به خدمات دفتر فنی آرنوکس یکی از گزینه‌هایی است که می‌توانید بررسی کنید؛ اما پیش از هر تصمیمی، همین سه اصل بالا بیشتر اختلاف‌های رایج را برطرف می‌کند.