فرمول‌های اکسل برای متره و برآورد ساختمان؛ SUMIFS، VLOOKUP و خطاها
فرمول‌های اکسل برای متره و برآورد ساختمان: ساختار درست شیت، SUMIFS، VLOOKUP و XLOOKUP، گرد کردن احجام و خطاهای رایج دفتر فنی.
بیشتر دفاتر فنی پروژه‌های کوچک و متوسط، متره و برآورد را با اکسل انجام می‌دهند؛ نه به این دلیل که نرم‌افزار تخصصی وجود ندارد، بلکه چون اکسل انعطاف‌پذیر است و خروجی آن را کارفرما هم می‌تواند باز کند. مشکل جای دیگری است: بیشتر شیت‌های متره‌ای که در کارگاه‌ها دست‌به‌دست می‌شوند، از نظر ساختار داده اشتباه ساخته شده‌اند و همین باعث می‌شود جمع نهایی با ریزمتره نخواند، فرمول‌ها با اضافه‌شدن یک ردیف بشکنند، یا فایل روی رایانه‌ی دیگری خطا بدهد. این مقاله به‌جای آموزش عمومی توابع، روی همان تصمیم‌های ساختاری و رفتارهای مستندشده‌ی اکسل تمرکز می‌کند که مستقیماً بر دقت برآورد اثر می‌گذارند.
پاسخ کوتاه. شیت متره را به‌صورت یک جدول داده‌ی تخت با ستون‌های ثابت (کد آیتم، محل اجرا، طول، عرض، ارتفاع، تعداد، حجم) بسازید و جمع‌بندی را نه با انتخاب دستی سلول‌ها بلکه با SUMIFS انجام دهید. اتصال به فهرست‌بها را با VLOOKUP در حالت تطابق دقیق یا XLOOKUP برقرار کنید و احجام را با ROUND کنترل کنید.
شیت متره باید پایگاه داده باشد، نه جدول کاغذی
هر سطر، یک اندازه‌گیری. رایج‌ترین اشتباه این است که شیت متره را شبیه فرم کاغذی می‌سازند: سرگروه‌ها را با سلول‌های ادغام‌شده جدا می‌کنند و زیر هر گروه یک SUM دستی می‌گذارند. این ساختار با هر تغییر نقشه می‌شکند.
ستون‌های طبقه‌بندی را صریح بنویسید. به‌جای اینکه «طبقه دوم» عنوان یک بلوک باشد، آن را در یک ستون مستقل کنار تک‌تک سطرها تکرار کنید. همین تکرار ظاهراً اضافی است که بعداً اجازه می‌دهد بدون بازسازی شیت، برآورد را به تفکیک طبقه، بلوک، پیمانکار جزء یا صورت‌وضعیت ماهانه بیرون بکشید.
محدودیت ابعاد شیت عملاً مسئله نیست. هر کاربرگ اکسل از نسخه‌ی ۲۰۰۷ به بعد تا ۱٬۰۴۸٬۵۷۶ سطر و ۱۶٬۳۸۴ ستون (تا ستون XFD) را پشتیبانی می‌کند. یعنی حتی ریزمتره‌ی یک پروژه‌ی بزرگ هم به سقف سطر نمی‌رسد و هیچ دلیلی برای فشرده‌کردن داده در سلول‌های ادغام‌شده وجود ندارد.
SUMIFS؛ ستون فقرات جمع‌بندی احجام
جمع شرطی به‌جای انتخاب دستی. وقتی ستون‌های طبقه‌بندی را دارید، حجم کل بتن‌ریزی فونداسیون بلوک A با یک فرمول به دست می‌آید و با اضافه‌شدن ردیف‌های جدید خودش به‌روز می‌شود؛ برخلاف SUM دستی که باید هر بار محدوده‌اش را اصلاح کنید.
سقف شرط‌ها ۱۲۷ جفت است. تابع SUMIFS تا ۱۲۷ جفت «محدوده‌ی شرط و شرط» را می‌پذیرد و عبور از این سقف به خطای #VALUE! منجر می‌شود. در عمل هیچ شیت متره‌ای به این عدد نزدیک نمی‌شود؛ اگر شما به بیش از چهار پنج شرط رسیدید، نشانه‌ی آن است که طبقه‌بندی ستون‌ها بد طراحی شده، نه اینکه به شرط بیشتری نیاز دارید.
ارجاع به فایل بسته کار نمی‌کند. این نکته دفاتر فنی را زیاد غافلگیر می‌کند: طبق مستندات مایکروسافت، اگر SUMIF یا SUMIFS به سلولی در یک فایل بسته ارجاع بدهد، نتیجه #VALUE! می‌شود و این رفتار عمدی است. اگر شیت برآورد شما به فایل جداگانه‌ی فهرست‌بها یا آنالیز بها وصل است، یا باید فایل مرجع باز باشد یا فرمول را با SUMPRODUCT بازنویسی کنید که با فایل بسته هم محاسبه می‌شود.
VLOOKUP و XLOOKUP برای اتصال به فهرست‌بها
آرگومان چهارم را هرگز خالی نگذارید. در VLOOKUP اگر آرگومان range_lookup را ننویسید، مقدار پیش‌فرض TRUE یعنی تطابق تقریبی است و اکسل به‌جای پیدانکردن کد، نزدیک‌ترین مقدار کوچک‌تر را برمی‌گرداند. در جدول فهرست‌بها نتیجه فاجعه است: قیمت یک ردیف دیگر روی آیتم شما می‌نشیند و هیچ خطایی هم نمایش داده نمی‌شود. برای کد آیتم همیشه FALSE بنویسید.
تطابق تقریبی فقط با جدول مرتب‌شده معنا دارد. حالت TRUE فرض می‌کند ستون اول جدول به‌ترتیب صعودی مرتب شده است؛ اگر نباشد، خروجی می‌تواند بی‌سروصدا غلط باشد. تنها جای موجه استفاده از آن، جدول‌های پلکانی مثل ضرایب طبقات یا بازه‌های ارتفاعی است، نه جست‌وجوی کد.
پیش از استفاده از XLOOKUP، نسخه‌ی مقصد را بسنجید. تابع XLOOKUP فقط در اکسل ۲۰۲۱ و مایکروسافت ۳۶۵ در دسترس است و در اکسل ۲۰۱۶ و ۲۰۱۹ وجود ندارد؛ فایلی که با آن ساخته‌اید، روی رایانه‌ی کارفرما یا ناظر با خطای #NAME? باز می‌شود. اگر فایل قرار است بین چند سازمان بچرخد، VLOOKUP با تطابق دقیق انتخاب امن‌تری است.
گرد کردن و دقت عددی؛ جایی که اختلاف‌های ریز متولد می‌شوند
تفاوت نمایش و مقدار واقعی. کم‌کردن اعشار از نوار ابزار فقط نمایش را عوض می‌کند و مقدار ذخیره‌شده دست‌نخورده می‌ماند. اگر ستون حجم را با دو رقم اعشار نشان بدهید ولی گرد نکنید، جمع ستون با جمع اعداد نمایش‌داده‌شده اختلاف پیدا می‌کند و همین اختلاف در جلسه‌ی تأیید صورت‌وضعیت به بحث تبدیل می‌شود. برای احجامی که مبنای پرداخت‌اند، ROUND را داخل خود فرمول بگذارید.
سقف دقت اکسل ۱۵ رقم بامعنا است. اکسل اعداد را بر پایه‌ی استاندارد ممیز شناور IEEE 754 نگه می‌دارد و حدود ۱۵ رقم بامعنا دقت دارد؛ ارقام بعدی صفر می‌شوند. در متره این محدودیت روی احجام اثری ندارد، اما هنگام درج شماره‌های طولانی مثل کد ملی پیمانکار یا شماره‌ی حساب، عدد بی‌سروصدا تغییر می‌کند و راه‌حل، قالب‌بندی سلول به‌صورت متن پیش از ورود داده است.
به تودرتویی IF تکیه نکنید. اکسل از نسخه‌ی ۲۰۰۷ اجازه‌ی ۶۴ سطح IF تودرتو را می‌دهد (این سقف در نسخه‌ی ۲۰۰۳ فقط ۷ سطح بود)، ولی فرمولی با ده سطح شرط عملاً غیرقابل بازبینی است. منطق ضرایب و شرط‌های آنالیز بها را در یک جدول کمکی بنویسید و با جست‌وجو به آن وصل شوید.
جدول مرجع: رفتارها و محدودیت‌های کلیدی اکسل در شیت متره
| موضوع | مقدار یا رفتار مستند | اثر در متره و برآورد |
|---|---|---|
| ابعاد کاربرگ (اکسل ۲۰۰۷ به بعد) | ۱٬۰۴۸٬۵۷۶ سطر × ۱۶٬۳۸۴ ستون (تا XFD) | ریزمتره را می‌توان کاملاً تخت و سطر به سطر نوشت |
| حداکثر شرط در SUMIFS | ۱۲۷ جفت محدوده و شرط؛ فراتر از آن خطای #VALUE! | عملاً محدودیتی ایجاد نمی‌کند |
| SUMIF/SUMIFS با فایل بسته | خطای #VALUE! (رفتار عمدی) | برای اتصال به فایل فهرست‌بها از SUMPRODUCT استفاده کنید |
| آرگومان چهارم VLOOKUP | در صورت حذف، پیش‌فرض TRUE یعنی تطابق تقریبی | خطر تخصیص قیمت اشتباه به کد آیتم، بدون پیام خطا |
| در دسترس بودن XLOOKUP | اکسل ۲۰۲۱ و مایکروسافت ۳۶۵؛ در ۲۰۱۶ و ۲۰۱۹ نیست | خطای #NAME? هنگام باز کردن فایل روی نسخه‌های قدیمی‌تر |
| دقت عددی | حدود ۱۵ رقم بامعنا بر پایه IEEE 754 | شماره‌های طولانی را به‌صورت متن وارد کنید |
| تودرتویی تابع IF | ۶۴ سطح از اکسل ۲۰۰۷ (در ۲۰۰۳ فقط ۷ سطح) | منطق ضرایب را به جدول کمکی منتقل کنید |
سه خطای ساختاری که بیشترین هزینه را می‌سازند
سلول ادغام‌شده در محدوده‌ی فرمول. ادغام سلول‌ها باعث می‌شود مرتب‌سازی، فیلتر و توابع شرطی رفتار غیرمنتظره پیدا کنند. اگر صرفاً به‌دنبال ظاهر بهتر هستید، از قالب‌بندی مرکز در سراسر انتخاب استفاده کنید، نه ادغام.
عدد و متن در یک ستون. نوشتن «۱۲ متر» در ستون طول، آن سلول را متنی می‌کند و از جمع کنار می‌گذارد. واحد باید در عنوان ستون بیاید، نه داخل داده.
محدوده‌ی فرمول با ردیف‌های جدید هم‌گام نمی‌شود. اگر محدوده را به‌صورت جدول رسمی اکسل تعریف کنید، ردیف‌های جدید خودبه‌خود وارد محاسبه می‌شوند و دیگر لازم نیست بعد از هر بازدید کارگاهی محدوده‌ها را دستی اصلاح کنید. برای مرور اصول اندازه‌گیری پیش از ساخت شیت، راهنمای کامل متره و برآورد ساختمان نقطه‌ی شروع خوبی است، و برای دیدن اینکه این ساختار در عمل چطور به صورت‌وضعیت تبدیل می‌شود، راهنمای صورت‌وضعیت پروژه‌های عمرانی مسیر را کامل می‌کند. برای محاسبات سریع مصالح هم می‌توانید از ابزارهای آنلاین مهندسی آرنوکس به‌عنوان مرجع کنترل متقابل نتایج شیت استفاده کنید.
پرسش‌های متداول
چرا SUMIFS من خطای #VALUE! می‌دهد؟ شایع‌ترین علت، ارجاع فرمول به فایل اکسل بسته است؛ مایکروسافت این رفتار را عمدی اعلام کرده. فایل مرجع را باز کنید یا فرمول را با SUMPRODUCT بازنویسی کنید.
حداکثر چند شرط می‌توان در SUMIFS گذاشت؟ تا ۱۲۷ جفت محدوده و شرط. فراتر از آن خطای #VALUE! برمی‌گردد، اما در شیت متره عملاً به این سقف نمی‌رسید.
آیا XLOOKUP در اکسل ۲۰۱۹ کار می‌کند؟ خیر. این تابع فقط در اکسل ۲۰۲۱ و مایکروسافت ۳۶۵ وجود دارد و در نسخه‌های ۲۰۱۶ و ۲۰۱۹ خطای #NAME? می‌دهد.
چرا VLOOKUP قیمت اشتباه برمی‌گرداند؟ چون آرگومان چهارم نوشته نشده و اکسل تطابق تقریبی را پیش‌فرض گرفته است. برای کد آیتم همیشه FALSE بگذارید تا فقط تطابق دقیق پذیرفته شود.
اکسل ابزار ضعیفی برای متره نیست؛ چیزی که شیت‌های متره را غیرقابل اتکا می‌کند، ساختار داده‌ی بد و ناآگاهی از چند رفتار مستند نرم‌افزار است. اگر داده را تخت و ستون‌بندی‌شده نگه دارید، جمع‌بندی را به SUMIFS بسپارید، جست‌وجوی کد را همیشه دقیق تعریف کنید و احجام مبنای پرداخت را صریح گرد کنید، بخش بزرگی از اختلاف‌های همیشگی بین ریزمتره و خلاصه‌ی برآورد از بین می‌رود.