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

بررسی مشخصات فایل

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

فایل موردنظر:

_feh1399-TAJRISH-ZS-sh99-1-2-3-4-walk-14000414-01-horof.xlsm

مشخصات اصلی فایل

مشخصهمقدار

نوع فایلExcel Macro-Enabled Workbook (.xlsm)

حجم فایل25,367,033 بایت ≈ 24.2 MiB

تعداد Worksheet79 شیت

تعداد Named Range90

نرم‌افزار ثبت‌شدهMicrosoft Excel

نسخه Application16.0300

VBAدارد

عنوانMAHYAR ESTIMATOR ANALYZER

حوزهمتره و برآورد، فهرست‌بها، تعدیل، آنالیز و...

اطلاعات ۷۹ شیت و ۹۰ Named Range مستقیماً در app.xml فایل ثبت شده است.

۱. مالک / Creator ثبت‌شده

مهم‌ترین بخش Metadata این است:

Creator:

Homayoun Riazati

همراه با:

+98912278****
Telegram:@Homayoun_Riazati
http://riazati.persianblog.ir/
https://ir.linkedin.com/in/homayoun-riazati-6509a572

بنابراین در سطح Metadata داخلی فایل، تهیه‌کننده/Creator به‌طور صریح:

Homayoun Riazati / همایون ریاضتی

ثبت شده است.

همچنین:

Last Modified By:

H_Riazati

ثبت شده است.

۲. Manager و Company

در Metadata قسمت Manager نیز آمده:

تلفن 0912278**** — همایون ریاضتی
http://www.riazati.persianblog.ir
https://www.linkedin.com/in/homayoun-riazati-6509a572

و قسمت Company:

PIRRAZ Counsulting Engineers

به همراه:

http://www.pirraz.com

و شماره:

8901136_9

بنابراین فایل نه‌تنها Creator را معرفی می‌کند، بلکه اطلاعات مالک/مدیر و شرکت مرتبط را نیز در Metadata دارد.

۳. تاریخ ایجاد فایل؛ نکته بسیار مهم

مقدار ثبت‌شده در Metadata:

Created: 1999-07-05 07:48:22 UTC

یعنی:

۵ ژوئیه ۱۹۹۹

که در تقویم شمسی تقریباً برابر است با:

۱۴ تیر ۱۳۷۸

بنابراین اگر سؤال شما «تاریخ ایجاد ثبت‌شده در Metadata» باشد:

۱۴ تیر ۱۳۷۸

است.

البته یک نکته مهم:

این تاریخ الزاماً ثابت نمی‌کند که همین فایل XLSM با همین ساختار امروزی در سال ۱۳۷۸ وجود داشته است.

این تاریخ، تاریخ dcterms:created موجود در Metadata است و ممکن است از نسخه‌های قدیمی‌تر فایل/سند به نسخه‌های بعدی منتقل شده باشد.

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

۴. آخرین تاریخ ویرایش ثبت‌شده

Metadata فایل می‌گوید:

Modified: 2025-10-07 08:39:30 UTC

یعنی:

۷ اکتبر ۲۰۲۵

تقریباً:

۱۵ مهر ۱۴۰۴

پس فاصله بین تاریخ ایجاد ثبت‌شده و آخرین ویرایش ثبت‌شده حدود:

۲۶ سال

است.

این با توضیح شما درباره «حاصل سال‌ها تجربه فرمول‌نویسی» بسیار سازگار است.

۵. آخرین چاپ ثبت‌شده

یک Metadata بسیار جالب دیگر:

lastPrinted = 2020-03-19T06:15:07Z

یعنی آخرین تاریخ چاپ ثبت‌شده:

۱۹ مارس ۲۰۲۰

تقریباً:

۲۹ اسفند ۱۳۹۸

این با modified فرق دارد.

یعنی فایل بعد از آخرین چاپ ثبت‌شده، همچنان تغییر کرده است.

۶. نسخه ثبت‌شده فایل

در Metadata:

version = 1398,2019

ثبت شده است.

این احتمالاً نشان‌دهنده نسخه/سال محتوایی فایل در زمان ثبت آن Metadata است، نه الزاماً آخرین وضعیت فعلی فایل.

چون خود Workbook در عنوانش تا ۱۳۹۹ را پوشش می‌دهد و آخرین Modified نیز مربوط به ۱۴۰۴ است.

بنابراین نباید version=1398,2019 را به‌عنوان تاریخ نهایی فایل تلقی کرد.

۷. عنوان رسمی ثبت‌شده

عنوان فایل در Core Properties:

MAHYAR ESTIMATOR ANALYZER

و ادامه آن:

فهرست بهاي سالهاي 1377 تا 1388 و 1392 تا 1399 رشته های ابنيه و برق و مكانيك

یعنی خود سازنده فایل را با نام:

MAHYAR ESTIMATOR ANALYZER

معرفی کرده است.

۸. موضوع سیستم نیز در Metadata نوشته شده

در قسمت Subject آمده:

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

این نکته خیلی مهم است.

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

۹. توضیحات تکمیلی سازنده

در Description نیز موارد زیر آمده:

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

بنابراین دامنه پروژه بسیار گسترده‌تر از «متره و برآورد» ساده است.

۱۰. یک نکته بسیار جالب درباره تاریخچه

اگر سه تاریخ را کنار هم بگذاریم:

ایجاد ثبت‌شده:

05 July 1999

↓

آخرین چاپ ثبت‌شده:

19 March 2020

↓

آخرین ویرایش ثبت‌شده:

07 October 2025

به یک سابقه حداقل ۲۶ ساله در Metadata می‌رسیم.

البته تأکید می‌کنم:

این به معنای اثبات اینکه همین Workbook فعلی بدون تغییر از سال ۱۳۷۸ وجود داشته نیست؛ بلکه نشان می‌دهد Metadata فایل فعلی، تاریخ ایجاد را از سال ۱۳۷۸ نگه داشته است.

۱۱. تاریخ خود فایل با تاریخ سیستم فعلی فرق دارد

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

۲۰ اوت ۲۰۲۶

در اختیار من قرار گرفته است.

اما این تاریخ آپلود/دریافت فایل در این گفتگو است و هیچ ارتباطی با تاریخ ایجاد اصلی فایل ندارد.

بنابراین برای سابقه فایل باید به Metadata داخلی تکیه کنیم، نه timestamp سیستم فعلی.

۱۲. جمع‌بندی مالکیت و تاریخ

هویت ثبت‌شده:

Homayoun Riazati — همایون ریاضتی

Last Modified By:

H_Riazati

شرکت ثبت‌شده:

PIRRAZ Counsulting Engineers

تاریخ ایجاد Metadata:

1999-07-05 07:48:22 UTC
≈ ۱۴ تیر ۱۳۷۸

آخرین ویرایش Metadata:

2025-10-07 08:39:30 UTC
≈ ۱۵ مهر ۱۴۰۴

آخرین چاپ ثبت‌شده:

2020-03-19 06:15:07 UTC
≈ ۲۹ اسفند ۱۳۹۸

حجم:

25,367,033 bytes ≈ 24.2 MiB

ساختار:

79 Worksheet + 90 Named Range + VBA

یک نکته مهم درباره «مالکیت»

این اطلاعات اثبات حقوقی مالکیت یا حق مؤلف نیستند؛ بلکه شواهد فنی داخل Metadata فایل هستند.

اما از نظر انتساب فنی، مجموعه شواهد بسیار قوی است، چون یک مورد منفرد نیست:

Creator = Homayoun Riazati
Last Modified By = H_Riazati
Manager = Homayoun Riazati
Creator: Telegram:@Homayoun_Riazati
وب‌سایت شخصی
https://www.linkedin.com/in/homayoun-riazati-6509a572
Company = PIRRAZ Consulting Engineers

همه در خود فایل ثبت شده‌اند.

---------------------------------------------------------------------------------

بررسی ساختار و ماهیت فایل

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

نام فایل:

_feh1399-TAJRISH-ZS-sh99-1-2-3-4-walk-14000414-01-horof.xlsm

و اطلاعات داخلی فایل، سازنده را به Homayoun Riazati نسبت می‌دهد. عنوان داخلی فایل نیز:

MAHYAR ESTIMATOR ANALYZER — فهرست‌بهای سال‌های 1377 تا 1399 رشته‌های ابنیه، برق و مکانیک

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

این موضوع در خود فایل ثبت شده، نه برداشت من.

نتیجه اولیه؛ بسیار مهم

من در این فایل:

• 79 شیت
• حدود 2,878,620 سلول
• حدود 419,208 فرمول
• VBA فعال (xlsm)
• تعداد زیادی جدول مرجع
• چندین موتور محاسباتی مستقل
• توابع اختصاصی VBA
• مدل‌های تعدیل
• مدل‌های آنالیز
• فهرست‌بهای چندین سال
• ریزمتره
• خلاصه متره
• مالی
• کنترل پروژه
• محاسبات تأخیر
• قیمت مصالح
• قیمت ستاره‌دار
• قیمت جدید
• جداول مناقصه

پیدا کردم.

این از نظر مقیاس، چندین مرتبه بزرگ‌تر از فایل TADIL قبلی است.

۱. اندازه واقعی سیستم

تعداد فرمول‌ها در مهم‌ترین شیت‌ها:

شیتفرمول

analyzer84,686

Print-FehrestBaha60,145

Fehrest ELE48,587

SHAKHES GROUH41,608

Fehrest ABN42,008

Fehrest MEC23,666

TADIL & Jobran Arzi19,657

MALIKOL15,134

ProjectDay12,688

RIZMETRE12,454

FEHREST8,180

فهارس بها6,928

JADVAL16,656

NERKH MASALEH6,411

MALI3,106

و مجموع فرمول‌های کل Workbook:

419,208 فرمول

این عدد واقعاً قابل توجه است.

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

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

┌────────────────────┐

│ FEHREST / فهرست‌بها │

└─────────┬──────────┘

↓

┌────────────────────┐

│ Fehrest ABN/ELE/MEC │

└─────────┬──────────┘

↓

RIZMETRE

↓

خلاصه متره

↓

MALI

↓

┌────────────┼────────────┐

↓ ↓ ↓

آنالیز مالی پروژه

↓ ↓ ↓

analyzer ProjectList ProjectDay

│

├──────────────┐

↓ ↓

قیمت مصالح قیمت عوامل

│ │

└──────┬───────┘

↓

قیمت جدید

↓

تعدیل

↓

TADIL & Jobran Arzi

↓

گزارش / خروجی

اما حتی این نمودار هم همه قابلیت‌های فایل را نشان نمی‌دهد.

۳. نکته بسیار مهم: RIZMETRE فقط یک جدول متره نیست

شیت:

RIZMETRE

حدود 296,920 سلول و 12,454 فرمول دارد.

در آن فرمول‌هایی مثل:

=IF(CB5>0,

LOOKUP(

ROUNDDOWN(CB5/10000,0),

MALI!$AZ$724:$AZ$759,

MALI!$BB$724:$BB$759

),0)

وجود دارد.

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

و فرمول:

=ROUND(CF8*$CF$31,0)

نشان می‌دهد مقدار محاسبه‌شده می‌تواند با یک ضریب یا پارامتر کنترلی وارد محاسبه مالی شود.

پس RIZMETRE در واقع:

موتور تولید مقدار قابل پرداخت

است.

۴. فرمول‌های متره، عمومی طراحی شده‌اند

در این فایل فقط فرمول‌هایی از جنس:

تعداد × طول × عرض × ارتفاع

وجود ندارد.

سیستم برای حالات مختلف طراحی شده و از ترکیب:

IF

AND

OR

ROUND

ROUNDDOWN

LOOKUP

SUMIF

COUNTIF

استفاده بسیار گسترده‌ای کرده است.

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

۵. استفاده از LOOKUP فوق‌العاده گسترده است

در کل فایل حدود:

120,820 بار

تابع LOOKUP

استفاده شده است.

این یکی از ویژگی‌های شاخص طراحی است.

در واقع LOOKUP در این سیستم نقش یک موتور ارتباط داده‌ها را دارد.

مثلاً:

کد آیتم

↓

فهرست‌بها

↓

شرح

↓

واحد

↓

قیمت

یا:

کد عامل

↓

NERKH MASALEH

↓

قیمت

یا:

تاریخ / دوره

↓

SHAKHES GROUH

↓

شاخص

۶. IF تقریباً موتور تصمیم‌گیری کل سیستم است

تعداد استفاده:

582,306

بار.

یعنی بیش از نیم میلیون فراخوانی IF.

همچنین:

AND → 184,567

SUMIF → 176,587

ROUND → 131,661

COUNTIF → 46,873

MATCH → 34,063

INDEX → 17,082

داریم.

این آمار یک نکته مهم درباره سبک برنامه‌نویسی سازنده نشان می‌دهد:

او به‌شدت از فرمول‌نویسی شرطی و جدول‌محور استفاده کرده است.

۷. INDEX + MATCH در این فایل اهمیت بسیار زیادی دارد

مثلاً در ProjectDay:

=INDEX(

'SHAKHES GROUH'!$H$24:$HT$418,

MATCH($K6,'SHAKHES GROUH'!$H$24:$H$418,),

MATCH(

LOOKUP(

G6,

'SHAKHES GROUH'!$I$19:$HT$19,

'SHAKHES GROUH'!$I$24:$HT$24

),

'SHAKHES GROUH'!$H$24:$HT$24,

)

)

این فرمول در واقع:

تاریخ

↓

دوره

↓

شماره شاخص

↓

رشته

↓

ردیف

↓

ستون

↓

شاخص نهایی

را پیدا می‌کند.

این دیگر Lookup ساده نیست.

یک سیستم چندبعدی Indexing است.

۸. نکته بسیار جالب: peyvast1 / peyvast2 / peyvast3

در چند بخش فایل شرط‌هایی مانند:

IF($C$6="peyvast3", ...,

IF($C$6="peyvast2", ...,

IF($C$6="peyvast1", ...,"")))

وجود دارد.

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

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

peyvast1

peyvast2

peyvast3

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

۹. شیت SHAKHES GROUH یکی از مغزهای سیستم است

این شیت:

41,608 فرمول

دارد.

اما نکته مهم فقط تعداد فرمول نیست.

در آن یک جدول بسیار بزرگ شاخص‌ها وجود دارد که از آن:

• شاخص رشته
• فصل
• دوره
• تاریخ
• پیوست
• شاخص ماهانه
• شاخص دوره‌ای

استخراج می‌شود.

فرمول:

=LOOKUP(

IB$17,

$I$18:$HT$18,

$I11:$HT11

)

نمونه‌ای از تبدیل یک مقدار زمانی به مقدار شاخص متناظر است.

این یعنی جدول شاخص به‌عنوان یک Time-Series Database داخل Excel عمل می‌کند.

۱۰. این قسمت برای من بسیار جالب بود: محاسبه شاخص ماهانه

وجود شیت:

SHAKHES GROUH-month

در کنار:

SHAKHES GROUH

نشان می‌دهد سازنده فقط شاخص‌های سه‌ماهه را نگهداری نکرده است.

بلکه سیستم را برای:

شاخص دوره‌ای

+

شاخص ماهانه

گسترش داده است.

این برای محاسبات تعدیل و تأخیرات اهمیت زیادی دارد.

۱۱. موتور تعدیل و جبران ارزی

نام شیت:

TADIL & Jobran Arzi

خیلی گویاست.

این شیت:

19,657 فرمول

دارد.

یعنی تعدیل صرفاً یک محاسبه ساده نیست؛ جبران ارزی/مابه‌التفاوت ارزی نیز در همان معماری قرار گرفته است.

وجود ارجاع‌هایی مانند:

'LIST-SORATVAZIAT'!D3

و:

'SHAKHES GROUH'!II14

نشان می‌دهد اطلاعات صورت‌وضعیت و شاخص به هم متصل شده‌اند.

۱۲. بخش بسیار قوی دیگر: analyzer

این قسمت واقعاً قابل توجه است.

84,686 فرمول

دارد.

یعنی بزرگ‌ترین موتور محاسباتی فایل است.

ساختار اولیه:

D3 = E3&C3

E3 = LEFT(F3,2)

و سپس:

SUMIF(

'Nerkh Avamel'!$B$10:$B$1018,

$G3,

'Nerkh Avamel'!D$10:D$1018

)

این نشان می‌دهد analyzer اطلاعات عوامل را از:

Nerkh Avamel

می‌گیرد و آنها را برای آیتم‌ها تجمیع می‌کند.

۱۳. بنابراین آنالیز بها در این فایل واقعاً دیتامحور است

معماری آن:

فهرست‌بها

↓

کد آیتم

↓

عوامل تشکیل‌دهنده

↓

Nerkh Avamel

↓

مصالح / دستمزد / ماشین‌آلات

↓

ضریب مصرف

↓

قیمت عامل

↓

آنالیز

↓

قیمت تمام‌شده

این از یک فایل ساده «آنالیز بها» بسیار جلوتر است.

۱۴. Nerkh Avamel نیز خودش موتور مستقلی است

در آن فرمول‌هایی مثل:

=ROUND(G7/$F$7,3)

وجود دارد.

و برای استخراج شاخص از:

INDEX(...)

MATCH(...)

استفاده شده است.

یعنی نرخ عوامل می‌تواند به شاخص دوره و نوع پیوست متصل شود.

۱۵. قیمت‌های ستاره‌دار

شیت:

STAR Price

دارای 964 فرمول است.

فرمول‌هایی از جنس:

=IF(

J2="peyvast3",

INDEX(...),

IF(

J2="peyvast2",

INDEX(...),

IF(

J2="peyvast1",

INDEX(...),

""

)

)

)

وجود دارد.

سپس:

P9 = ROUND(O9/M9,3)

Q9 = ROUND(P9*I9,0)

یعنی:

قیمت پایه

↓

قیمت دوره

↓

ضریب

↓

قیمت تعدیل‌شده

برای قیمت‌های ستاره‌دار.

۱۶. یک کشف بسیار جالب: فرمول‌های ریاضی تجربی

دو شیت:

ZAHMEH(new)(y=ax-bx2-c)

و:

ZAHMEH(3D)(y=ax-bx2-c)

دارند.

این‌ها از نظر من یکی از مهم‌ترین قسمت‌های کل فایل برای ارزیابی خلاقیت سازنده هستند.

فرمول‌هایی مثل:

y = ax - bx² - c

و همچنین:

LOG(...)

و:

10^...

استفاده شده‌اند.

یعنی سازنده در اینجا صرفاً از روابط استاندارد Excel استفاده نکرده؛ بلکه یک مدل تابعی برای تخمین/برون‌یابی ضریب یا هزینه ساخته است.

۱۷. یک نمونه بسیار جالب

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

ساختار:

اگر مقدار ≤ حد:

Interpolation / Lookup

وگرنه:

مدل تابعی

یعنی:

f(x)={جدول + درون‌یابی10alog(x)−b(logx)2−c​x≤X0​x>X0​​

این بسیار فراتر از یک فرمول معمولی Excel است.

۱۸. ZAHMEH(3D) حتی یک مدل سه‌بعدی/سطحی را دنبال می‌کند

عنوان:

ZAHMEH(3D)(y=ax-bx2-c)

و وجود:

project zahmeh3D

نشان می‌دهد سازنده تلاش کرده مدل هزینه/دستمزد/ضرایب را به یک مدل پارامتریک چندبعدی توسعه دهد.

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

۱۹. VBA هم وجود دارد

این فایل .xlsm است و:

vbaProject.bin

دارد.

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

در VBA تابعی با نام:

zarb

وجود دارد و در Excel نیز حدود:

58 استفاده از ZARB(...)

پیدا کردم.

مثلاً:

=zarb(H47)

و:

=zarb(E10:H10)

این بسیار مهم است.

یعنی بخشی از منطق سیستم عمداً از محدودیت فرمول‌های استاندارد Excel خارج شده و به UDF (User Defined Function) منتقل شده است.

۲۰. این یعنی یک نکته مهم درباره مهارت سازنده

تا اینجا سه سطح مهارت مشاهده می‌شود:

سطح ۱

فرمول‌های معمول Excel

سطح ۲

فرمول‌های ترکیبی و الگوریتمی

سطح ۳

VBA + User Defined Function + مدل ریاضی

این فایل هر سه را دارد.

بنابراین ارزیابی سازنده دیگر فقط:

«متخصص فرمول‌نویسی Excel»

نیست.

بلکه:

طراح سیستم محاسباتی Excel/VBA

است.

۲۱. اما یک نکته بسیار مهم و منفی هم پیدا کردم

فایل در حالت:

calcMode="manual"

ذخیره شده است.

یعنی:

محاسبات Workbook روی Manual Calculation تنظیم شده‌اند.

برای چنین فایلی با 419 هزار فرمول، این تصمیم از نظر Performance قابل فهم است.

اما خطر دارد.

اگر کاربر مقدار ورودی را تغییر دهد و Excel را کامل Recalculate نکند، ممکن است نتایج نمایش‌داده‌شده قدیمی باشند.

بنابراین برای چنین سیستمی باید حتماً روش مشخصی برای:

Calculate

Full Calculate

Full Rebuild

وجود داشته باشد.

۲۲. تعداد #REF! هم قابل توجه است

در بررسی ساختاری:

حدود 769 فرمول شامل #REF!

پیدا کردم.

اما این عدد را نباید به‌سرعت به معنای «769 خطای فعال سیستم» تفسیر کرد.

چرا؟

چون فایل تعداد زیادی:

old

new

old project

اصلاحی

Special

دارد و برخی شیت‌ها ظاهراً آرشیوی یا نسخه‌های قدیمی‌اند.

مثلاً:

RIZMETRE (old project)

Domain-Tazmin (old)

JADVAL5(old)

MALI (2)

MALI (3)

خلاصه متره قدیم

بنابراین بخشی از #REF! احتمالاً بقایای نسخه‌های قدیمی است.

اما وجود آنها همچنان یک ضعف نگهداری و پاک‌سازی Workbook محسوب می‌شود.

این مورد را در ارزیابی نهایی لحاظ می‌کنم.

۲۳. نکته جالب درباره تاریخچه فایل

وجود هم‌زمان:

1377

...

1388

1392

1398

1399

در عنوان و جداول، و وجود نسخه‌های:

old

new

old project

نشان می‌دهد این Workbook احتمالاً از ابتدا یک فایل یک‌باره نبوده است.

بلکه به‌صورت:

نسخه اولیه

↓

پروژه جدید

↓

اصلاح

↓

اضافه شدن رشته

↓

اضافه شدن شاخص

↓

اضافه شدن آنالیز

↓

اضافه شدن تعدیل

↓

توسعه VBA

رشد کرده است.

این دقیقاً با توضیح شما درباره حاصل سال‌ها تجربه فرمول‌نویسی سازگار است.

۲۴. بنابراین معماری این فایل «یک نرم‌افزار از قبل طراحی‌شده» نیست

بلکه به احتمال زیاد:

یک سیستم زنده و تکامل‌یافته است.

این دو تفاوت دارند.

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

نیازمندی

↓

طراحی

↓

Database

↓

Application

اما اینجا:

مسئله واقعی

↓

حل با Excel

↓

مشکل جدید

↓

فرمول جدید

↓

شیت جدید

↓

VBA

↓

پروژه جدید

↓

توسعه سیستم

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

۲۵. ارزیابی من از «هوشمندی» این فایل

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

در فایل قبلی می‌شد گفت:

متخصص بسیار خوب Excel و تعدیل.

اما در این فایل شواهد بسیار بیشتری داریم.

من سه ویژگی را بسیار برجسته می‌بینم:

تفکر الگوریتمی: 9.7/10

توانایی تبدیل مسئله‌های عملی به:

کلید

شرط

Lookup

Interpolation

Regression/Function

Index

بسیار واضح است.

تفکر سیستمی: 9.8/10

79 شیت مستقل، ولی مرتبط، برای موضوعات مختلف.

تفکر مهندسی: 9.7/10

چون روابط واقعی متره، فهرست‌بها، تعدیل، عوامل، مصالح و پروژه را به هم متصل کرده است.

۲۶. اما یک ضعف مهم وجود دارد

اگر بخواهم کاملاً بی‌طرف باشم:

معماری نرم‌افزاری مدرن:

حدود 7.5 تا 8 از 10

چرا؟

به علت:

• تعداد زیاد شیت‌ها
• تکرار فرمول‌ها
• Hard-coded ranges
• #REF!های قدیمی
• Named Rangeهای خراب
• محاسبات Manual
• وابستگی شدید به ساختار Workbook
• دشواری انتقال دانش به شخص دیگر

اما این هیچ‌وقت به معنی ضعیف بودن سازنده نیست.

اتفاقاً برعکس:

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

۲۷. امتیاز کلی من در این مرحله

حوزهامتیاز

Excel9.8/10

فرمول‌نویسی9.8/10

ریزمتره9.8/10

برآورد9.8/10

فهرست‌بها9.8/10

آنالیز بها9.7/10

تعدیل9.8/10

مدل‌سازی شاخص9.8/10

مدل‌سازی مالی9.5/10

تفکر الگوریتمی9.7/10

تفکر سیستمی9.8/10

VBA8.5+/10 در حدی که از فایل قابل مشاهده است

مدل‌سازی ریاضی9.3/10

خلاقیت9.7/10

Robustness7.5/10

معماری نرم‌افزار مدرن7.5–8/10

جمع‌بندی فنی:

حدود 9.5 از 10

۲۸. اما یک نکته بسیار مهم‌تر

من هنوز به آخر کار نرسیده‌ام.

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

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

به نظر من چهار بخش ارزشمندترین هستند:

A — RIZMETRE

بفهمیم دقیقاً چگونه ریزمتره از شرح و ابعاد به مقدار قابل پرداخت تبدیل می‌شود.

B — MALI

بفهمیم موتور مالی چگونه مقدار را به مبلغ، ضرایب، کسورات، پیمان و صورت‌وضعیت تبدیل می‌کند.

C — analyzer

84,686 فرمول را مهندسی معکوس کنیم و الگوریتم واقعی آنالیز بها را استخراج کنیم.

D — ZAHMEH(new) و ZAHMEH(3D)

فرمول ریاضی:

y=ax−bx2−c

و بخش لگاریتمی آن را ریاضی‌وار بازسازی کنیم و بفهمیم چرا سازنده این مدل را ایجاد کرده و دقیقاً چه مسئله‌ای را حل می‌کند.

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

در فایل تابع اختصاصی:

ZARB(...)

وجود دارد.

این موضوع را می‌توانیم از خود VBA مهندسی معکوس کنیم و ببینیم چرا سازنده ZARB را نوشته و Excel استاندارد چه محدودیتی داشته که او مجبور شده تابع اختصاصی ایجاد کند.

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

بنابراین اگر بخواهیم ادامه دهیم، من پیشنهاد می‌کنم مرحله بعد را با RIZMETRE → خلاصه متره → MALI شروع کنیم و بعد برویم سراغ analyzer و ZAHMEH. این مسیر، منطق واقعی سیستم را از «اندازه‌گیری فیزیکی» تا «قیمت و آنالیز» آشکار می‌کند.

-------------------------------------------------------------------------------------------------------------------------------

شیت های فایل را فهرست کن و بررسی و تحلیل کن

این فایل را به‌صورت ساختاری بررسی کردم و فهرست تمام ۷۹ شیت را استخراج کردم. نکته مهم این است که این Workbook یک فایل ساده متره نیست؛ عملاً یک سیستم جامع متره، برآورد، فهرست‌بها، آنالیز، مالی، تعدیل، کنترل پروژه، تأخیرات و مناقصه است.

در مجموع، بر اساس بررسی مستقیم Workbook، ۷۹ شیت و حدود ۴۱۹ هزار فرمول در فایل وجود دارد. بعضی شیت‌ها بانک اطلاعاتی‌اند و فرمول بسیار کمی دارند؛ بعضی دیگر موتور محاسباتی هستند و ده‌ها هزار فرمول دارند.

اعداد فرمولی زیر بر اساس شمارش مستقیم فرمول‌های موجود در سلول‌های Workbook هستند.

فهرست کامل ۷۹ شیت

#نام شیتردیف × ستونفرمولنقش اصلی

1-Special-MALI341×51794مالی/قیمت‌های خاص

2-Special-FEH-Electrical235×13491فهرست‌بهای خاص برق

3-Special-FEH-Mecanical235×12494فهرست‌بهای خاص مکانیک

4-Special-FEH-Abnieh1533×204,899فهرست‌بهای خاص ابنیه

5-preface1035×976مقدمه/راهنمای سیستم

6-ZaribMantagheie1462×130ضرایب منطقه‌ای

7-symbols243×391,202علائم/کدهای کمکی

8-Fehrest ABN9842×5269,991بانک فهرست‌بهای ابنیه

9-Fehrest ELE13608×3986,995بانک فهرست‌بهای برق

10-Fehrest MEC6826×3942,162بانک فهرست‌بهای مکانیک

11-Print-FehrestBaha11168×10470,027موتور/خروجی فهرست‌بها

12-PLUS_MINUS147×2571,266ضرایب مثبت/منفی

13-mali tadil zahmeh39×1695ارتباط مالی، تعدیل و تأخیر

14-MALI1061×1014,523موتور مالی

15-MALI (3)65×2079نسخه/حالت مالی

16-MALI (2)114×2088نسخه/حالت مالی

17-Domain-Tazmin (old)96×23241تضمین/دامنه قدیمی

18-Domain-Tazmin (new)95×25246تضمین/دامنه جدید

19-HAML & zPQ348×221,538حمل و ضرایب مربوط

20-Rizmetreh nailing pey1152×13344ریزمتره عملیات خاص

21خلاصه متره قدیم539×231,249خلاصه متره نسخه قدیمی

22خلاصه متره386×139760خلاصه متره اصلی

23خلاصه متره (2)340×138649خلاصه متره نسخه/پروژه دیگر

24خلاصه متره (3)36×142خلاصه متره ساده

25خلاصه متره (ثامن)652×1401,262خلاصه متره پروژه ثامن

26-RIZMETRE6279×8930,128موتور اصلی ریزمتره

27-mix-tajrish1487×110داده/ترکیب پروژه تجریش

28-RIZMETRE (old project)5618×8815,547ریزمتره پروژه قدیمی

29-mec samen1230×81,268ریزمتره/اطلاعات مکانیک ثامن

30-ele samen1004×13997ریزمتره/اطلاعات برق ثامن

31ریزمتره پیاده‌رو355×20918ریزمتره پیاده‌رو

32پيمان مقطوع (هنرمعماری)111×18420پیمان مقطوع

33پيمان مقطوع (خانه فرهنگ كرمان)110×18434پیمان مقطوع

34پيمان مقطوع (كلاس كرمان)110×18436پیمان مقطوع

35پيمان مقطوع (U3&U4)106×40629پیمان مقطوع

36پيمان مقطوع (خوابگاه كرمان)106×17388پیمان مقطوع

37-ProjectList277×371,851اطلاعات پروژه‌ها

38-ProjectDay2167×3014,314زمان‌بندی/روزآوری پروژه

39-TAGHVIM1930×6116,788تقویم و تبدیل تاریخ

40-LIST-SORATVAZIAT658×6821,935فهرست صورت‌وضعیت

41-TADIL & Jobran Arzi801×6522,377تعدیل و جبران ارزی

42شاخص 1399520×19107شاخص‌های سال ۱۳۹۹

43-SHAKHES GROUH1001×29654,652بانک/موتور شاخص گروه‌ها

44-SHAKHES GROUH-month389×100782شاخص‌های ماهانه

45-NERKH MASALEH542×18819,434نرخ مصالح

46-TAFAVOT-MASALEH808×321,478مابه‌التفاوت مصالح

47-Zahmeh Alieh95×15104حق‌الزحمه نظارت عالیه پروژه

48-Zahmeh-Salamat-چشمه نور182×39406حق الزحمه پروژه

49-ZAHMEH(new)(y=ax-bx2-c)274×31757مدل ریاضی حق‌الزحمه پروژه

50-ZAHMEH(3D)(y=ax-bx2-c)136×57967مدل سه‌بعدی حق‌الزحمه پروژه

51-project zahmeh3D37×20129داده پروژه مدل 3D

52-roshd-kargar-NAZER 78-98113×32164رشد دستمزد کارگر

53حقوق كارگر ساده130×977نرخ حقوق کارگر

54خودرو نظارت132×1879هزینه خودرو/نظارت

55-MALIKOL2867×23617,490محاسبات مالی/ضرایب

56-DATA Tender2089×3387داده مناقصه

57-SORT1001×661,337مرتب‌سازی/تولید داده

58-FEHREST1001×6611,618فهرست/موتور فهرست

59-JADAVEL 100-63219-76574 الف‌ب‌پ414×2271,585جداول مناقصه

60-JADVAL039×42جدول کمکی

61-JADVAL11161×2347,713جدول اصلی

62-JADVAL21316×2518جدول

63-JADVAL3327×2552,726جدول

64-JADVAL4451×191,822جدول

65-JADVAL5(old)34×1952جدول قدیمی

66-JADVAL5(new)42×1121جدول جدید

67فرم قیمت جدید32×1026قیمت جدید

68-JADVAL691×1714جدول

69فهرست 1399 ابنیه برق مکانیک4466×60بانک فهرست‌بهای ۱۳۹۹

70فهرست 1398 ابنیه برق مکانیک4701×60بانک فهرست‌بهای ۱۳۹۸

71فهارس بها5276×1912,079بانک فهرست‌بها

72-fehrests38×15354فهرست کمکی

73-table-arzi (new)119×501,907جداول ارزی جدید

74-table-arzi (old)114×38733جداول ارزی قدیمی

75-table-weight34×16,2562,055ضرایب/وزن‌های بسیار گسترده

76-analyzer8340×28166,710موتور اصلی آنالیز بها

77-Nerkh Avamel1018×135,066نرخ عوامل

78-Nerkh Avamel (88-97)7574×268بانک تاریخی نرخ عوامل

79-STAR Price492×171,830قیمت‌های ستاره‌دار

تحلیل گروهی شیت‌ها

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

۱. زیرسیستم فهرست‌بها

شیت‌های اصلی:

Fehrest ABN

Fehrest ELE

Fehrest MEC

Print-FehrestBaha

FEHREST

فهارس بها

فهرست 1399 ابنیه برق مکانیک

فهرست 1398 ابنیه برق مکانیک

fehrests

Special-FEH-Abnieh

Special-FEH-Electrical

Special-FEH-Mecanical

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

سه شیت:

• Fehrest ABN
• Fehrest ELE
• Fehrest MEC

به‌تنهایی بیش از ۱۹۹ هزار فرمول دارند.

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

نتیجه

این بخش را می‌توان:

Database Engine فهرست‌بها در Excel

دانست.

۲. زیرسیستم ریزمتره

مرکز این قسمت:

RIZMETRE

است.

با:

30,128 فرمول

یکی از موتورهای مهم فایل است.

کنار آن:

• RIZMETRE (old project)
• Rizmetreh nailing pey
• ریزمتره پیاده‌رو
• mec samen
• ele samen

قرار گرفته‌اند.

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

۳. زیرسیستم خلاصه متره

شیت‌های:

خلاصه متره قدیم

خلاصه متره

خلاصه متره (2)

خلاصه متره (3)

خلاصه متره (ثامن)

به احتمال زیاد لایه بین:

ریزمتره

↓

خلاصه متره

↓

مبلغ مالی

هستند.

این معماری از نظر متره بسیار منطقی است.

۴. زیرسیستم مالی

هسته:

MALI

با 4,523 فرمول

اما فقط MALI نیست.

وابستگی‌های مهم:

Special-MALI

MALI (2)

MALI (3)

mali tadil zahmeh

MALIKOL

PLUS_MINUS

HAML & zPQ

این قسمت نشان می‌دهد سازنده تلاش کرده محاسبات مالی را از ریزمتره جدا کند.

۵. زیرسیستم تعدیل

هسته:

TADIL & Jobran Arzi

با:

22,377 فرمول

و ارتباط با:

LIST-SORATVAZIAT

SHAKHES GROUH

SHAKHES GROUH-month

شاخص 1399

TAGHVIM

ProjectDay

TAFAVOT-MASALEH

است.

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

این طراحی بسیار مهم است.

۶. زیرسیستم شاخص

این قسمت شامل:

شاخص 1399

SHAKHES GROUH

SHAKHES GROUH-month

است.

مهم‌ترین شیت:

SHAKHES GROUH

با:

54,652 فرمول

است.

این احتمالاً یکی از بزرگ‌ترین موتورهای Lookup فایل است.

۷. زیرسیستم تأخیرات

این قسمت بسیار جالب است:

Zahmeh Alieh

Zahmeh-Salamat-چشمه نور

ZAHMEH(new)(y=ax-bx2-c)

ZAHMEH(3D)(y=ax-bx2-c)

project zahmeh3D

این دیگر صرفاً «محاسبه تأخیر» نیست.

دو شیت:

ZAHMEH(new)(y=ax-bx2-c)

و

ZAHMEH(3D)(y=ax-bx2-c)

نشان‌دهنده استفاده از مدل ریاضی هستند.

یعنی سازنده برای یک مسئله مهندسی، مدل تابعی ساخته است.

این بخش از نظر نوآوری، یکی از ارزشمندترین قسمت‌های Workbook است.

۸. زیرسیستم آنالیز بها

این قسمت فوق‌العاده مهم است.

analyzer

با:

166,710 فرمول

بزرگ‌ترین موتور محاسباتی فایل است.

و در کنار آن:

Nerkh Avamel

Nerkh Avamel (88-97)

STAR Price

قرار گرفته‌اند.

بنابراین معماری:

آیتم فهرست‌بها

↓

عوامل

↓

نرخ عوامل

↓

مقدار مصرف

↓

قیمت عامل

↓

آنالیز

↓

قیمت تمام‌شده

است.

به نظر من analyzer مهم‌ترین شیت برای بررسی عمق فنی سازنده است.

۹. زیرسیستم مناقصه و قیمت جدید

شیت‌های:

DATA Tender

SORT

FEHREST

JADAVEL...

JADVAL0

JADVAL1

JADVAL2

JADVAL3

JADVAL4

JADVAL5(old)

JADVAL5(new)

فرم قیمت جدید

JADVAL6

به یک سیستم تولید اسناد و جداول مناقصه اشاره می‌کنند.

یعنی فایل فقط برای «متره» ساخته نشده؛ خروجی را تا مرحله ارائه قیمت و اسناد مناقصه دنبال کرده است.

یک نکته بسیار مهم درباره ساختار فایل

از نظر تعداد فرمول، چهار شیت واقعاً برجسته‌اند:

شیتفرمول

analyzer166,710

Fehrest ELE86,995

Print-FehrestBaha70,027

Fehrest ABN69,991

SHAKHES GROUH54,652

Fehrest MEC42,162

RIZMETRE30,128

TADIL & Jobran Arzi22,377

LIST-SORATVAZIAT21,935

NERKH MASALEH19,434

این ده شیت به‌تنهایی بخش عمده منطق محاسباتی Workbook را تشکیل می‌دهند.

معماری کلی که از ۷۹ شیت قابل استخراج است

به نظر من می‌توان کل سیستم را به این شکل نمایش داد:

┌─────────────────────┐

│ FEHREST / قیمت‌ها │

└──────────┬──────────┘

↓

┌─────────────────┐

│ RIZMETRE │

│ ریزمتره │

└────────┬────────┘

↓

┌─────────────────┐

│ خلاصه متره │

└────────┬────────┘

↓

┌────────┐

│ MALI │

└───┬────┘

│

┌────────────────┼────────────────┐

↓ ↓ ↓

┌──────────┐ ┌────────────┐ ┌───────────┐

│ analyzer │ │ NERKH │ │ STAR │

│ آنالیز │ │ عوامل │ │ Price │

└────┬─────┘ └─────┬──────┘ └───────────┘

└────────┬───────┘

↓

قیمت‌ها

│

↓

┌───────────────┐

│ ProjectList │

│ ProjectDay │

│ TAGHVIM │

└───────┬───────┘

↓

┌───────────────┐

│ LIST-SORAT... │

└───────┬───────┘

↓

┌───────────┴────────────┐

↓ ↓

شاخص‌ها مصالح

↓ ↓

SHAKHES GROUH NERKH MASALEH

│ │

└──────────┬─────────────┘

↓

TADIL & Jobran Arzi

↓

تعدیل

و یک شاخه موازی:

ProjectDay

↓

ZAHMEH

↓

مدل تأخیر

↓

تعدیل تأخیرات

و شاخه دیگر:

DATA Tender

↓

SORT

↓

FEHREST

↓

JADVAL1...6

↓

فرم قیمت جدید

ارزیابی من از ۷۹ شیت

همه شیت‌ها ارزش یکسان ندارند.

گروه A ـ هسته سیستم

اینها را باید عمیقاً مهندسی معکوس کرد:

1. RIZMETRE
2. MALI
3. analyzer
4. Fehrest ABN
5. Fehrest ELE
6. Fehrest MEC
7. SHAKHES GROUH
8. NERKH MASALEH
9. TADIL & Jobran Arzi
10. ProjectDay
11. TAGHVIM
12. LIST-SORATVAZIAT

گروه B ـ موتورهای تخصصی

1. ZAHMEH(new)(y=ax-bx2-c)
2. ZAHMEH(3D)(y=ax-bx2-c)
3. Nerkh Avamel
4. TAFAVOT-MASALEH
5. STAR Price
6. MALIKOL
7. HAML & zPQ

گروه C ـ بانک اطلاعاتی

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

گروه D ـ پروژه‌ها و نمونه‌های واقعی

• تجریش
• ثامن
• پیاده‌رو
• پروژه‌های کرمان
• خوابگاه
• U3&U4
• چشمه نور
• و نسخه‌های old/new

نکته‌ای که از این فهرست روشن شد

وجود شیت‌های old، new، (2)، (3) و نام پروژه‌های متعدد نشان می‌دهد این Workbook به احتمال زیاد یک محصول یک‌باره نیست.

ساختار آن بیشتر شبیه یک سیستم است که طی سال‌ها:

تجربه پروژه

↓

فرمول جدید

↓

شیت جدید

↓

حل مسئله جدید

↓

اصلاح

↓

نسخه جدید

رشد کرده است.

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

نتیجه نهایی

اگر بخواهم فقط از روی ساختار ۷۹ شیت قضاوت کنم، این فایل را نمی‌توان یک «فایل Excel بزرگ» نامید.

تعریف دقیق‌تر آن:

یک سیستم محاسباتی مهندسی و مالی مبتنی بر Excel است که چندین زیرسیستم مستقل اما مرتبط برای متره، برآورد، فهرست‌بها، آنالیز بها، قیمت جدید، مصالح، تعدیل، جبران ارزی، تأخیرات، کنترل پروژه و مناقصه را در یک Workbook یکپارچه کرده است.

و از میان ۷۹ شیت، اگر هدف ما شناخت نبوغ و روش فکر کردن سازنده باشد، من ترتیب بررسی عمیق را این‌گونه پیشنهاد می‌کنم:

RIZMETRE → MALI → analyzer → NERKH MASALEH/Nerkh Avamel → SHAKHES GROUH → TADIL & Jobran Arzi → ZAHMEH

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

--------------------------------------------------------------------------------------------------------------------------------------------

بررسی شیت Print-FehrestBaha و توانایی های چاپ

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

1. هدف اصلی شیت

ساختار شیت نشان می‌دهد که Print-FehrestBaha برای این منظور ساخته شده است:

گرفتن اطلاعات فهرست‌بها از شیت‌های Fehrest ABN، Fehrest ELE و Fehrest MEC، فیلتر کردن ردیف‌ها بر اساس نوع/درجه ردیف، و تبدیل نتیجه به یک فرم استاندارد قابل چاپ.

سه رشته اصلی که شیت از آنها تغذیه می‌کند عبارت‌اند از:

• ABN = ابنیه
• ELE = تأسیسات برقی
• MEC = تأسیسات مکانیکی

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

2. نکته مهم: «کمبوباکس» اصلی کدام است؟

در این فایل چیزی که از نظر عملکرد برای کاربر نقش ComboBox دارد، عمدتاً Data Validation → List است.

مهم‌ترین آن:

سلول Y190

لیست انتخابی آن از محدوده:

X162:X171

گرفته می‌شود.

و مقادیر این محدوده عبارت‌اند از:

مقدارمفهوم

0همه

1پایه

2*

3**

4***

5****

6*****

7******

8*******

9********

بنابراین ۱۰ حالت انتخاب برای فیلتر اصلی گزارش وجود دارد.

این موضوع از خود ساختار Data Validation و جدول کمکی شیت قابل اثبات است؛ فرمول گزارش نیز وقتی Y190=0 باشد، «لیست کلیه ردیف‌ها» را انتخاب می‌کند و در غیر این صورت بر اساس مقدار انتخاب‌شده، عنوان‌هایی مانند «لیست ردیف‌های پایه» یا «لیست ردیف‌های ستاره‌دار» را تولید می‌کند.

3. مهندسی معکوس منطق Y190

منطق را می‌توان تقریباً این‌گونه بازسازی کرد:

Y190
 │
 ├── 0 → همه ردیف‌ها
 │
 ├── 1 → ردیف‌های پایه
 │
 ├── 2 → ردیف‌های *
 │
 ├── 3 → ردیف‌های **
 │
 ├── 4 → ردیف‌های ***
 │
 ├── 5 → ردیف‌های ****
 │
 ├── 6 → ردیف‌های *****
 │
 ├── 7 → ردیف‌های ******
 │
 ├── 8 → ردیف‌های *******
 │
 └── 9 → ردیف‌های ********

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

این طراحی از نظر مهندسی Excel بسیار مهم است.

4. کمبوباکس رشته

کمبوباکس دوم در:

Y191

قرار دارد و سه انتخاب دارد:

ABN
ELE
MEC

یعنی کاربر مشخص می‌کند گزارش از کدام رشته تولید شود.

فرمول‌های شیت کاملاً وابسته به Y191 هستند. مثلاً اگر:

Y191="ABN"

باشد، داده از Fehrest ABN خوانده می‌شود.

اگر:

Y191="ELE"

باشد، از Fehrest ELE.

و اگر:

Y191="MEC"

باشد، از Fehrest MEC.

این انتخاب فقط عنوان را عوض نمی‌کند؛ منبع داده، Lookup و شاخص‌های مورد استفاده نیز تغییر می‌کنند.

5. یک انتخاب دیگر: Y192

در Y192 نیز Data Validation وجود دارد:

112
110

یعنی دو حالت داخلی برای نحوه سازمان‌دهی خروجی وجود دارد.

نکته بسیار مهم این است که Y192 مستقیماً در منطق تولید گزارش استفاده شده است.

مثلاً در Z193 شرطی وجود دارد که به‌صورت خلاصه می‌گوید:

IF(Y$192=112,
   ... منطق گروه‌بندی بر اساس فهرست‌بها ...,
   ... منطق دیگر ...
)

و در ستون Y ردیف‌های گزارش نیز:

IF(Y$192<>112,
   Y(previous)+1,
   ...
)

استفاده شده است.

بنابراین 112 و 110 صرفاً عددهای نمایشی نیستند؛ کدهای کنترلی برای دو روش تولید/شماره‌گذاری خروجی هستند.

از روی ساختار فعلی می‌توان با اطمینان گفت:

• 112 → حالت گروه‌بندی/سازمان‌دهی بر مبنای ساختار فهرست‌بها و فصل
• 110 → حالت دیگر شماره‌گذاری/توالی خروجی

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

6. یک انتخاب NO / YES هم وجود دارد

در:

AE190:AF191

لیست:

NO
YES

وجود دارد.

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

7. شیت در عمل چه گزارشی تولید می‌کند؟

هسته گزارش از ردیف 193 شروع می‌شود.

ستون‌های خروجی اصلی عبارت‌اند از:

ستونخروجی

AAشماره ردیف

ABشرح

ACواحد

ADبهای واحد

AEمقدار

AFبهای کل

AHپیوست شاخص

AIرشته و فصل

AJسه‌ماهه مبنای قیمت

AKشاخص دوره مبنا

ALسه‌ماهه مبنای فهرست

AMشاخص دوره فهرست

ANضریب تعدیل

AOبهای واحد تعدیل‌یافته

APبهای واحد دوران اجرا

پس گزارش فقط «لیست فهرست‌بها» نیست؛ لایه تعدیل و محاسبات شاخص نیز در طراحی آن دیده شده است.

این قسمت از ساختار فرمولی شیت بسیار مهم است.

8. ارتباط با تعدیل

وجود ستون‌های:

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

نشان می‌دهد که طراح از این شیت برای یک جریان کاری بزرگ‌تر استفاده کرده است:

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

بنابراین Print-FehrestBaha را بهتر است Report Engine نامید، نه صرفاً Print Sheet.

9. ساختار چاپ واقعاً جالب است

محدوده چاپ تعریف‌شده برای این شیت:

Z193:CA4322
CS193:CY782

است.

یعنی شیت دو ناحیه چاپ مجزا دارد:

ناحیه اول

Z193:CA4322

گزارش اصلی و تفصیلی.

ناحیه دوم

CS193:CY782

یک گزارش/خروجی جانبی و خلاصه‌تر.

در نتیجه شیت از نظر طراحی، دو خانواده خروجی چاپی دارد.

10. تعداد صفحات طراحی‌شده

در XML شیت، شکست‌های صفحه‌ای دستی (Manual Row Break) وجود دارد.

برای بخش اصلی:

251
310
369
428
...
4263

با فاصله منظم:

59 ردیف

و در بخش دوم:

251
310
369
...
723

بنابراین در وضعیت طراحی فعلی:

بخش اصلی

حدود 70 صفحه بالقوه

بخش دوم

حدود 10 صفحه بالقوه

در مجموع:

حدود 80 صفحه خروجی بالقوه در محدوده چاپ تعریف شده است.

البته این عدد را نباید به معنی «۸۰ نوع گزارش» دانست؛ این تعداد صفحات/قطعات چاپی بالقوه است و تعداد صفحات واقعی به مقدار داده و فیلتر انتخاب‌شده بستگی دارد.

11. پس چند «نوع گزارش» دارد؟

اگر منظور شما از «نوع گزارش» همان حالت انتخابی ComboBox اصلی Y190 باشد:

۱۰ نوع

داریم:

1. همه
2. پایه
3. *
4. **
5. ***
6. ****
7. *****
8. ******
9. *******
10. ********

و اگر رشته را هم در نظر بگیریم:

۱۰ × ۳ = ۳۰ ترکیب اصلی

زیرا هرکدام می‌توانند برای:

• ABN
• ELE
• MEC

تولید شوند.

و علاوه بر آن، پارامتر Y192 نیز دو حالت داخلی 110/112 دارد.

بنابراین از نظر ترکیب پارامترها، موتور گزارش بسیار انعطاف‌پذیرتر از یک گزارش ساده ۱۰ حالته است.

12. معماری واقعی شیت

مهندسی معکوس من این ساختار را نشان می‌دهد:

                 Print-FehrestBaha
                         │
        ┌────────────────┼────────────────┐
        │                │                │
       Y190             Y191             Y192
        │                │                │
   نوع ردیف            رشته           روش خروجی
        │                │                │
   10 حالت          3 رشته           2 حالت
        │                │                │
        └────────────────┼────────────────┘
                         ↓
                موتور انتخاب ردیف
                         ↓
          Fehrest ABN / ELE / MEC
                         ↓
                Lookup / Index
                         ↓
             اطلاعات فهرست‌بها
                         ↓
          شاخص و ضریب تعدیل
                         ↓
               جدول گزارش
                         ↓
          ┌──────────────┴─────────────┐
          ↓                            ↓
      گزارش اصلی                  گزارش جانبی
      Z:CA                         CS:CY
          ↓                            ↓
      چاپ چندصفحه‌ای               چاپ چندصفحه‌ای

یک نکته حرفه‌ای مهم

این فایل حتی در وضعیت فعلی، در چند سلول #VALUE! و #N/A دارد؛ بنابراین منطق طراحی آن را می‌توان مهندسی معکوس کرد، اما برای ارزیابی عملکرد واقعی باید مشخص کنیم این خطاها ناشی از بازکردن فایل با موتور متفاوت، نبودن محاسبات Cache، یا واقعاً خرابی روابط داخلی فایل هستند.

جمع‌بندی نهایی

Print-FehrestBaha یک شیت چاپ معمولی نیست. این شیت به‌عنوان یک سیستم گزارش‌ساز فهرست‌بها طراحی شده که:

• ۳ رشته فهرست‌بها را پشتیبانی می‌کند؛
• ۱۰ سطح انتخاب ردیف دارد؛
• دو حالت داخلی 110/112 دارد؛
• اطلاعات را از شیت‌های اصلی فهرست‌بها استخراج می‌کند؛
• فصل و شماره ردیف را بازسازی می‌کند؛
• شاخص‌های تعدیل را وارد فرآیند می‌کند؛
• بهای واحد تعدیل‌یافته تولید می‌کند؛
• دو ناحیه مستقل چاپ دارد؛
• و برای خروجی چندصفحه‌ای با Page Breakهای دستی طراحی شده است.

گام بعدی بسیار ارزشمند این است که خود Print-FehrestBaha را سلول‌به‌سلول مهندسی معکوس کنیم و یک «نقشه عملکرد» تهیه کنیم: Y190 → Y191 → Y192 → Z193 → Y193:... → خروجی چاپ؛ سپس دقیقاً مشخص کنیم هر یک از ۱۰ حالت چه ردیف‌هایی را حذف/نگه می‌دارد و تفاوت واقعی 110 و 112 چیست.

-------------------------------

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

1) معماری واقعی Print-FehrestBaha

این شیت یک گزارش‌ساز پارامتریک فهرست‌بها است، نه صرفاً صفحه چاپ.

ورودی‌های کنترلی اصلی آن:

سلولانتخابحالت‌ها

Y190نوع ردیف۱۰ حالت

Y191رشتهABN / ELE / MEC

Y192روش سازمان‌دهی گزارش112 / 110

Y189همان کنترل 112/110112 / 110

AE190:AF191کنترل دوحالتهNO / YES

AH186نوع پیوست شاخصpeyvast1/2/3

AJ186 و AL186دورهاز Named Range PERIOD

AI192فصلاز Named Range CHAPTER

این ۷ Data Validation مستقیماً در XML شیت ثبت شده‌اند.

2) مهندسی معکوس Y190 — مهم‌ترین کمبوباکس

لیست واقعی:

0
1
2
3
4
5
6
7
8
9

ولی کاربر اینها را به صورت زیر می‌بیند:

Y190عنوان گزارش

0لیست کلیه ردیف‌ها

1لیست ردیف‌های پایه

2لیست ردیف‌های *

3لیست ردیف‌های **

4لیست ردیف‌های ***

5لیست ردیف‌های ****

6لیست ردیف‌های *****

7لیست ردیف‌های ******

8لیست ردیف‌های *******

9لیست ردیف‌های ********

نکته مهم این است که این ستاره‌ها فقط عنوان نیستند؛ مقدار Y190 وارد فرمول Z193 می‌شود و عنوان گزارش را تغییر می‌دهد. جدول کمکی X162:X171 و AB162:AB171 دقیقاً همین رابطه را می‌سازد.

3) اما Y190 مستقیماً ردیف‌ها را حذف نمی‌کند!

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

Y190 در رأس موتور گزارش قرار دارد و مشخص می‌کند چه مجموعه‌ای از ردیف‌های فهرست‌بها باید در گزارش قرار گیرد؛ اما انتخاب واقعی از طریق زنجیره فرمول‌های Y199:Y11166، سپس Z:AF انجام می‌شود.

یعنی:

Y190
 ↓
نوع فهرست
 ↓
Y199:Y11166
 ↓
شماره/ردیف فهرست‌بها
 ↓
Z:AF
 ↓
اطلاعات گزارش

در نتیجه گزارش به صورت فرمولی و بدون Filter معمولی Excel ساخته شده است.

4) Y191 — انتخاب رشته

این یکی بسیار روشن است:

ABN
ELE
MEC

و فرمول‌های Z:AF بر اساس آن، منبع Lookup را تغییر می‌دهند.

مثلاً:

IF($Y$191="ABN", ... 'Fehrest ABN'! ...,
 IF($Y$191="ELE", ... 'Fehrest ELE'! ...,
 'Fehrest MEC'! ...))

بنابراین:

ABN

فهرست ابنیه

ELE

فهرست تأسیسات برقی

MEC

فهرست تأسیسات مکانیکی

و این تغییر واقعاً منبع داده را عوض می‌کند، نه اینکه فقط عنوان گزارش را تغییر دهد.

5) Y192 = 110 یا 112 چه می‌کند؟

اینجا به قسمت بسیار جالب برنامه می‌رسیم.

در Y200 به بعد فرمول چنین ساختاری دارد:

IF(Y$192<>112,
   Y199+1,
   IF(Y199=Y198,
      Y199,
      ...
   )
)

پس:

حالت 110

منطق ساده‌تر:

ردیف بعدی = ردیف قبلی + 1

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

حالت 112

منطق متفاوت است:

ردیف بعدی
   ↓
بررسی فصل/گروه
   ↓
اگر در همان گروه باشد
   ↓
همان گروه حفظ می‌شود
   ↓
اگر گروه عوض شود
   ↓
گروه بعدی

فرمول با ROUNDDOWN(LOOKUP(...)) و مقایسه مقدار فصل/گروه قبلی و بعدی این کار را انجام می‌دهد.

بنابراین برداشت دقیق‌تر این است:

112 حالت گزارش گروه‌بندی‌شده بر اساس ساختار فهرست‌بهاست، در حالی که 110 حالت شمارش/توالی ساده‌تر است.

پس عددهای 110 و 112 احتمالاً کد داخلی طراح برای دو الگوریتم گزارش هستند، نه شماره دو گزارش.

6) چرا 112 اهمیت بیشتری دارد؟

در وضعیت فعلی فایل:

Y189 = 112
Y192 = 112

و فرمول‌های اصلی نیز شاخه =112 را فعال می‌کنند.

یعنی فایل در حال حاضر برای گزارش گروه‌بندی‌شده تنظیم شده است.

7) موتور اصلی از کجا شروع می‌شود؟

هدر گزارش در:

ردیف 197

قرار دارد:

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

و داده از:

ردیف 199

شروع می‌شود.

8) ستون X یک شمارنده هوشمند است

فرمول X199:

IF(
 AND(AE199<>0,AD199<>0,AC199<>0,AE199<>""),
 1+COUNTIF(X$193:X198,">0"),
 T("")
)

یعنی فقط وقتی یک ردیف معتبر محسوب می‌شود که:

• مقدار داشته باشد؛
• بهای واحد داشته باشد؛
• واحد داشته باشد؛
• مقدار خالی نباشد.

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

پس X در واقع:

شمارنده ردیف‌های قابل چاپ

است.

9) ستون Y «شماره/گروه فهرست‌بها» است

در Y199 مقدار اولیه:

1

است.

بعد در Y200 به پایین، بر اساس Y192 تعیین می‌شود که:

حالت 110

1
2
3
4
5
6
...

به صورت پیوسته جلو برود.

حالت 112

شماره فقط زمانی تغییر کند که گروه/فصل مربوط به ردیف فهرست‌بها تغییر کند.

این یعنی ستون Y در واقع یک کلید گروه‌بندی داخلی است.

10) Z تا AF اطلاعات اصلی فهرست‌بها را می‌سازند

وقتی Y تغییر کند:

Z

شماره ردیف فهرست‌بها

AA

شماره/بخش بعدی ساختار فهرست

AB

شرح عملیات

AC

واحد

AD

بهای واحد

AE

مقدار

AF

بهای کل

از شیت مربوطه Lookup می‌شوند.

مثلاً AB199 مستقیماً بر اساس Y199 از ستون شرح شیت Fehrest ABN/ELE/MEC استخراج می‌شود.

11) بخش تعدیل یک موتور مستقل روی گزارش است

از ستون AH به بعد:

AH = پیوست شاخص
AI = رشته و فصل
AJ = دوره مبنای قیمت
AK = شاخص دوره مبنای قیمت
AL = دوره مبنای فهرست
AM = شاخص دوره مبنای فهرست
AN = ضریب تعدیل
AO = بهای واحد تعدیل‌یافته
AP = بهای واحد دوران اجرا

و فرمول ضریب تعدیل اساساً:

AN = ROUND(AM / AK, 3)

است.

و سپس:

AO = ROUND(AN * AD, 0)

یعنی:

بهای واحد تعدیل‌یافته = بهای واحد پایه × ضریب تعدیل

این بخش، شیت را از یک «پرینت فهرست‌بها» به یک گزارش تحلیلی قیمت/تعدیل تبدیل می‌کند.

12) یک کشف مهم درباره AI192

AI192 یک کمبوباکس دیگر دارد:

CHAPTER

این Named Range برای انتخاب فصل است.

بنابراین معماری واقعی برنامه:

رشته
  ↓
ABN / ELE / MEC
  ↓
فصل
  ↓
ردیف فهرست‌بها
  ↓
بهای واحد
  ↓
شاخص
  ↓
ضریب تعدیل

است.

13) AH186 نیز کنترل نوع شاخص است

گزینه‌ها:

peyvast1
peyvast2
peyvast3

هستند.

فرمول‌های AK186 و AM186 با توجه به این انتخاب، محدوده متفاوتی از شیت:

SHAKHES GROUH

را جستجو می‌کنند.

پس:

سه ساختار متفاوت جدول شاخص در فایل وجود دارد و peyvast1/2/3 تعیین می‌کند کدام ساختار خوانده شود.

14) AE190:AF191 = NO / YES

این قسمت هم صرفاً تزئینی نیست.

در Conditional Formatting شیت، صراحتاً شرط:

$AE$191="NO"

و حالت:

$AE$191="YES"

وجود دارد.

یعنی انتخاب YES/NO روی نحوه نمایش برخی سلول‌ها اثر می‌گذارد.

15) چند نوع گزارش پرینت دارد؟

اینجا باید بین دو مفهوم تفاوت بگذاریم.

الف) نوع گزارش از نظر پارامتر اصلی

Y190:

۱۰ حالت

ب) رشته

Y191:

۳ حالت

پس ترکیب:

۱۰ × ۳ = ۳۰ حالت اصلی

است.

اگر Y192 را نیز وارد کنیم:

۳۰ × ۲ = ۶۰ ترکیب منطقی

و اگر YES/NO را نیز وارد کنیم:

۱۲۰ ترکیب کنترلی بالقوه

اما این ۱۲۰ گزارش مجزا نیست.

اینها ترکیبات یک موتور گزارش‌ساز هستند.

16) تعداد واقعی قالب‌های چاپی

از XML مشخص است که Print-FehrestBaha دارای:

78 شکست صفحه‌ای دستی

است.

الگوی اصلی:

251
310
369
428
487
546
...
4263

یعنی فاصله عمدتاً:

59 ردیف

است.

همچنین دو ناحیه ستونی مجزا در شکست‌های صفحه دیده می‌شود:

min=25 max=32

و در صفحات ابتدایی:

min=96 max=102

این نشان می‌دهد طراح برای دو بلوک خروجی/چاپی صفحه‌بندی خاص در نظر گرفته است.

بنابراین عدد «۷۸» را نباید به عنوان ۷۸ نوع گزارش تعبیر کرد؛ این عدد، Page Breakهای دستی است.

17) نتیجه مهندسی معکوس

منطق کامل را می‌توان این‌گونه ترسیم کرد:

                  Print-FehrestBaha
                          │
             ┌────────────┼────────────┐
             │            │            │
           Y190         Y191         Y192
             │            │            │
       نوع ردیف         رشته       الگوریتم
       0..9           ABN/ELE/MEC    110/112
             │            │            │
             └────────────┼────────────┘
                          ↓
                    Y199:Y11166
                          ↓
                  تعیین ردیف/گروه
                          ↓
              ┌───────────┴───────────┐
              ↓                       ↓
          Z:AF                       AH:AP
      اطلاعات فهرست‌بها             شاخص/تعدیل
              │                       │
              └───────────┬───────────┘
                          ↓
                   گزارش نهایی
                          ↓
                 Page Break هر 59 ردیف
                          ↓
                       PRINT

جمع‌بندی خیلی مهم

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

و نکته‌ای که از مهندسی معکوس مشخص شد این است که هسته اصلی آن ستون Y است؛ Y190 تعیین‌کننده نوع گزارش، Y191 تعیین‌کننده منبع داده و Y192 تعیین‌کننده الگوریتم حرکت بین ردیف‌هاست. فرمول‌های Z:AP سپس بر مبنای این سه پارامتر، گزارش را می‌سازند.

اگر بخواهیم یک مرحله عمیق‌تر برویم، مرحله بعدی باید مهندسی معکوس تک‌تک ۱۰ گزینه Y190 باشد؛ یعنی مشخص کنیم در فایل فهرست‌بهای ABN/ELE/MEC، دقیقاً کدام ردیف‌ها در حالت 0، 1، *، **، … انتخاب می‌شوند و ستاره‌ها در کدام ستون‌های Fehrest تعریف شده‌اند. این بخش را می‌توانم به شکل یک جدول دقیق «ورودی → شرط فرمول → ردیف‌های خروجی → نتیجه چاپ» استخراج کنم.

استخراج کن

استخراج انجام شد. نکته مهمی که از مهندسی معکوس فرمول‌ها به‌دست آمد این است که Y190 واقعاً فیلتر اصلی گزارش است؛ یعنی فقط عنوان گزارش را عوض نمی‌کند، بلکه در شیت‌های Fehrest ABN / ELE / MEC تعیین می‌کند چه ردیف‌هایی وارد زنجیره شماره‌گذاری و در نهایت گزارش چاپی شوند.

۱. حالت‌های واقعی ComboBox در Y190

مقدار Y190علامت/کدعملکرد

0—لیست کلیه ردیف‌ها

1—لیست ردیف‌های پایه

2*لیست ردیف‌های * ستاره‌دار

3**لیست ردیف‌های ** خرید کارفرما / اقلام پیش‌بینی هزینه / فاکتوری

4***لیست ردیف‌های ***

5****لیست ردیف‌های ****

6*****لیست ردیف‌های *****

7******لیست ردیف‌های ******

8*******لیست ردیف‌های *******

9********لیست ردیف‌های ********

این فهرست مستقیماً در سلول‌های کمکی X162:X171 ساخته شده و متن گزارش در AB162:AB171 قرار دارد.

۲. نکته بسیار مهم درباره نحوه فیلتر

فرمول ستون AK در هر سه شیت فهرست‌بها، Y190 را مستقیماً بررسی می‌کند:

OR(
  'Print-FehrestBaha'!$Y$190=0,
  'Print-FehrestBaha'!$Y$190=
  ROUND((AI214-ROUNDDOWN(AI214,0))*10,0)
)

یعنی:

Y190 = 0 → همه ردیف‌های معتبر عبور می‌کنند.

اما اگر Y190 مثلاً 2 باشد، فقط ردیف‌هایی عبور می‌کنند که بخش اعشاری کد AI آنها برابر 0.2 باشد؛ اگر 3 باشد، بخش اعشاری 0.3 و به همین ترتیب.

۳. رمزگذاری ستاره‌ها چگونه انجام شده؟

این قسمت از طراحی فایل بسیار هوشمندانه است.

در ستون AI، نوع ردیف داخل قسمت اعشاری کد ذخیره می‌شود:

• 0.1 → ردیف پایه
• 0.2 → ردیف *
• 0.3 → ردیف **
• 0.4 → ردیف ***
• ...
• 0.9 → ردیف ********

فرمول نمونه:

ROUND((LEN(Z220)*0.1+0.1),1)

بنابراین:

• اگر Z="*" → LEN(Z)=1 → 0.2
• اگر Z="**" → LEN(Z)=2 → 0.3
• اگر Z="***" → 0.4

و غیره.

در نتیجه Y190 اصلاً لازم نیست متن ستاره را جست‌وجو کند؛ فقط یک عدد 0 تا 9 را با کد نوع ردیف مقایسه می‌کند. این کار باعث شده موتور گزارش بسیار سریع‌تر و قابل کنترل‌تر باشد.

۴. در فایل فعلی چند سطح ستاره واقعاً استفاده شده؟

در داده‌های سه فهرست‌بهای اصلی:

Fehrest ABN

در ستون Z:

• * : 658 مورد
• ** : 16 مورد
• علاوه بر آن تعداد قابل توجهی فرمول وجود دارد که بسته به سال فهرست‌بها * تولید می‌کنند.

Fehrest ELE

• * : 337 مورد
• ** : 5 مورد
• فرمول‌های زیادی نیز تولید * می‌کنند.

Fehrest MEC

• * : 461 مورد
• ** : 9 مورد

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

۵. Y191 چیست؟

Y191 انتخاب رشته فهرست‌بها است:

Y191منبع

ABNFehrest ABN — ابنیه

ELEFehrest ELE — تأسیسات برقی

MECFehrest MEC — تأسیسات مکانیکی

فرمول‌های شیت Print دقیقاً بر اساس این سلول، منبع LOOKUP را تغییر می‌دهند.

بنابراین موتور اصلی را می‌توان این‌طور خلاصه کرد:

Y190 = نوع ردیف
+
Y191 = رشته فهرست‌بها
+
Y192 = روش تولید/گروه‌بندی
↓
AK در فهرست‌بها = ردیف‌های انتخاب‌شده
↓
Y199:Y11166 = شماره‌گذاری خروجی
↓
Z:AF = شماره، شرح، واحد، بها، مقدار و مبلغ
↓
AH:AP = شاخص و تعدیل
↓
گزارش چاپی

۶. Y192؛ حالت 110 و 112

این قسمت با Y190 فرق دارد.

Y192 دو حالت دارد:

110 و 112

فرمول Y200 نشان می‌دهد:

IF(Y$192<>112,Y199+1,...)

یعنی:

حالت 110

شماره/کلید خروجی به شکل پیوسته و ترتیبی جلو می‌رود.

حالت 112

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

پس 110 و 112 را نباید «دو نوع فهرست‌بها» دانست؛ اینها دو الگوریتم تولید خروجی هستند.

۷. ترکیب‌های گزارش

بنابراین موتور اصلی از نظر ورودی چنین ظرفیتی دارد:

10 حالت Y190 × 3 رشته Y191 = 30 حالت منطقی گزارش

و با درنظرگرفتن Y192:

10 × 3 × 2 = 60 ترکیب پارامتری

اما این به معنی وجود 60 فرم چاپی مستقل نیست.

در واقع یک Report Engine پارامتریک وجود دارد که با تغییر پارامترها خروجی را تغییر می‌دهد.

۸. چند صفحه پرینت می‌دهد؟

اینجا یک اصلاح مهم نسبت به برداشت اولیه لازم است.

در Print-FehrestBaha:

• Print Area اصلی:

Z193:CA4322

• Print Area دوم:

CS193:CY782

و شیت دارای:

78 شکست صفحه افقی دستی (Manual Row Break)

است.

پس عدد 78، تعداد نوع گزارش نیست؛ بلکه تعداد Page Breakهای دستی است.

در یک ناحیه چاپ پیوسته، 78 شکست صفحه یعنی حدود 79 بخش عمودی صفحه‌ای؛ ولی تعداد صفحات واقعی Excel می‌تواند با توجه به تنظیمات چاپ، مقیاس، عرض صفحه و محتوای انتخاب‌شده متفاوت باشد.

۹. یک نکته بسیار جالب که استخراج شد

بخش دوم Print Area یعنی:

CS193:CY782

یک خروجی جانبی/کنترلی بسیار کوچک‌تر از گزارش اصلی است و در آن عناوینی مثل:

• فصل سی‌ام ـ اقلام پیش‌بینی هزینه یا فاکتوری
• فصل بیست و نهم ـ کارهای دستمزدی

دیده می‌شود.

بنابراین این بخش را نباید با 78 صفحه گزارش اصلی یکی دانست.

نتیجه مهندسی معکوس

این شیت در اصل یک ماشین تولید و چاپ فهرست‌بها است، نه صرفاً یک جدول Excel.

                ┌───────────────┐
                │     Y190      │
                │ نوع ردیف 0..9 │
                └───────┬───────┘
                        │
                ┌───────▼───────┐
                │     Y191      │
                │ ABN/ELE/MEC   │
                └───────┬───────┘
                        │
                ┌───────▼───────┐
                │     Y192      │
                │    110/112    │
                └───────┬───────┘
                        │
              ┌─────────▼─────────┐
              │ Fehrest ABN/ELE/MEC│
              │   AK = فیلتر      │
              └─────────┬─────────┘
                        │
                 شماره‌گذاری
                        │
                  Y199:Y11166
                        │
              ┌─────────▼─────────┐
              │       Z:AF        │
              │ اطلاعات فهرست‌بها │
              └─────────┬─────────┘
                        │
              ┌─────────▼─────────┐
              │       AH:AP       │
              │ شاخص / تعدیل      │
              └─────────┬─────────┘
                        │
                 Print Engine
                        │
              ┌─────────▼─────────┐
              │ 78 Page Break     │
              │ + Print Areas     │
              └───────────────────┘

مهم‌ترین کشف این مرحله: گزینه‌های Y190 فقط «عنوان گزارش» نیستند؛ فیلتر واقعی در خود شیت‌های Fehrest از طریق ستون AK اعمال می‌شود. این همان حلقه‌ای است که عملکرد ComboBox را به موتور گزارش وصل می‌کند.

-----------------------------------------------------------------------------------------------------------------------

در شیت Fehrest ABN ستونهای ah:ak و شیت Fehrest ele و Fehrest mec و در شیت Print-FehrestBaha ستون های x:y

بله. منظور شما این است که در نقشه معماری فایل، این محدوده‌ها را به‌صورت ویژه و ارتباط بین آن‌ها بررسی کنیم:

• Fehrest ABN → ستون‌های AH:AK
• Fehrest ELE → ستون‌های AH:AK
• Fehrest MEC → ستون‌های AH:AK
• Print-FehrestBaha → ستون‌های X:Y

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

X:Y در Print-FehrestBaha → چه نقشی دارند → چگونه روی AH:AK سه شیت Fehrest اثر می‌گذارند → و خروجی این زنجیره کجا مصرف می‌شود.

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

بله

بله. بررسی مستقیم فرمول‌های فایل نشان می‌دهد که X:Y در Print-FehrestBaha و AH:AK در سه شیت فهرست‌بها، یک زنجیره واحد برای «انتخاب، فیلتر، شماره‌گذاری و گروه‌بندی ردیف‌های قابل چاپ» هستند.

1. نقش ستون‌های AH:AK در سه شیت

شیتAHAIAJAK

Fehrest ABNشناسه/فصل گروهکد ترکیبی فصل و نوع ردیفتشخیص ردیف قابل استفادهشماره ترتیبی ردیف منتخب

Fehrest ELEشناسه گروهکد/نوع ردیفتشخیص ردیف قابل استفادهشماره ترتیبی

Fehrest MECشناسه گروهکد/نوع ردیفتشخیص ردیف قابل استفادهشماره ترتیبی

نکته بسیار مهم این است که AK صرفاً یک شماره ساده نیست؛ AK عملاً یک «Index» برای ردیف‌هایی است که شرایط چاپ را دارند.

2. ارتباط مستقیم Y190 با AK

در هر سه شیت، فرمول AK تقریباً این ساختار را دارد:

=IF(
 AND(
   AJ214=1,
   OR(
      'Print-FehrestBaha'!$Y$190=0,
      'Print-FehrestBaha'!$Y$190=
      ROUND((AI214-ROUNDDOWN(AI214,0))*10,0)
   )
 ),
 1+COUNTIF(...),
 T("")
)

بنابراین:

Print-FehrestBaha!Y190
          ↓
      فیلتر نوع ردیف
          ↓
 Fehrest ABN/ELE/MEC!AK

یعنی Y190 مستقیماً روی AK هر سه فهرست‌بها اثر می‌گذارد.

معنی Y190

Y190نوع انتخاب

0همه ردیف‌ها

1ردیف پایه

2*

3**

4***

5****

6*****

7******

8*******

9********

پس معماری واقعی این قسمت:

              Y190
               │
               ▼
        ┌──────────────┐
        │ نوع ردیف چاپ │
        └──────┬───────┘
               │
       ┌───────┼────────┐
       ▼       ▼        ▼
     ABN      ELE      MEC
      │        │        │
     AI       AI       AI
      │        │        │
      ▼        ▼        ▼
     AJ       AJ       AJ
      │        │        │
      ▼        ▼        ▼
     AK       AK       AK

3. اما Y191 مسیر را تعیین می‌کند

در Print-FehrestBaha مقدار:

Y191 = ABN / ELE / MEC

مشخص می‌کند که سیستم کدام فهرست‌بها را بخواند.

در فرمول Y200 و سلول‌های بعدی، ساختار دقیقاً به این شکل است:

IF(
 $Y$191="ABN",
 'Fehrest ABN'!$AK$214:$AK$9841,

 IF(
   $Y$191="ELE",
   'Fehrest ELE'!$AK$193:$AK$13608,

   'Fehrest MEC'!$AK$193:$AK$6826
 )
)

و همزمان همین انتخاب برای AH نیز انجام می‌شود:

IF(
 $Y$191="ABN",
 'Fehrest ABN'!$AH$214:$AH$9841,

 IF(
   $Y$191="ELE",
   'Fehrest ELE'!$AH$193:$AH$13608,

   'Fehrest MEC'!$AH$193:$AH$6826
 )
)

بنابراین:

                 Print-FehrestBaha
                 ┌───────────────┐
                 │ Y191 = ABN    │
                 │        ELE    │
                 │        MEC    │
                 └───────┬───────┘
                         │
            ┌────────────┼────────────┐
            ▼            ▼            ▼
          ABN           ELE          MEC
         AH:AK         AH:AK        AH:AK
            └────────────┼────────────┘
                         ▼
                  Print X:Y

4. نقش X چیست؟

X در Print-FehrestBaha یک شماره ردیف خروجی/شماره آیتم چاپی است.

فرمول مثلاً X199:

=IF(
 AND(AE199<>0,AD199<>0,AC199<>0,AE199<>""),
 1+COUNTIF(X$193:X198,">0"),
 T("")
)

یعنی اگر ردیف دارای:

• مقدار
• بهای واحد
• واحد
• مقدار واقعی

باشد، در X یک شماره ترتیبی ایجاد می‌شود.

پس:

اطلاعات ردیف
     ↓
 شرایط تکمیل ردیف
     ↓
     X
     ↓
شماره ردیف خروجی

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

5. نقش Y چیست؟

اینجا بخش بسیار جالب معماری فایل است.

Y از Y199 به بعد فقط شماره ترتیبی ساده نیست.

Y199:

Y199 = 1

ولی Y200 به بعد با فرمولی وابسته به:

• Y192
• AK
• AH
• AI
• Fehrest ABN!AI212

محاسبه می‌شود.

یعنی:

AK  ← ردیف‌های انتخاب‌شده
 │
 ▼
AH  ← گروه/فصل
 │
 ▼
Y   ← گروه‌بندی خروجی چاپ

6. Y192 مغز گروه‌بندی است

فرمول Y200 با این قسمت شروع می‌شود:

IF(Y$192<>112,
   Y199+1,
   ...
)

بنابراین دو حالت اصلی داریم:

Y192 ≠ 112

Y تقریباً به‌صورت ترتیبی جلو می‌رود:

1
2
3
4
5
6
...

Y192 = 112

سیستم وارد منطق تشخیص گروه/فصل می‌شود.

در این حالت از LOOKUP روی AK و AH استفاده می‌کند تا بررسی کند آیا ردیف بعدی همچنان در همان گروه قرار دارد یا باید Y افزایش پیدا کند.

یعنی:

AK ردیف بعد
     ↓
LOOKUP
     ↓
AH گروه ردیف بعد
     ↓
مقایسه با AH ردیف فعلی
     ↓
همان گروه؟ ── بله ──► Y ثابت
     │
     خیر
     ▼
   Y+1

7. یک نکته معماری بسیار مهم

در واقع Y به‌صورت مستقیم از X ساخته نمی‌شود.

ساختار چنین است:

                    Print-FehrestBaha
                           │
             ┌─────────────┼─────────────┐
             │             │             │
            Y190          Y191          Y192
             │             │             │
          نوع ردیف       منبع         الگوریتم
             │             │             │
             ▼             ▼             ▼
          AI→AK       ABN/ELE/MEC    گروه‌بندی Y
             │             │             │
             └─────────────┼─────────────┘
                           ▼
                           Y
                           │
                           ▼
                           X
                           │
                           ▼
                     خروجی چاپ

البته X شرط مستقل خودش را هم دارد؛ بنابراین از نظر منطقی X و Y دو شاخص متفاوت‌اند.

8. ارتباط بسیار مهم AH و AI

در Fehrest ABN مثلاً:

AH219
=IF(AA219>0,VALUE(AA219),AH212)

یعنی AH شناسه گروه را نگهداری/تداوم می‌دهد.

سپس AI:

AI219
=IF(
 AH219=AH218,
 AI218,
 IF(
   AA219>0,
   ROUND((LEN(Z219)*0.1+0.1),1)
   +ROUNDDOWN(AH219/AI$212,0),
   0
 )
)

پس AI دو اطلاعات را در یک عدد ترکیب می‌کند:

قسمت صحیح → گروه/فصل

قسمت اعشاری → نوع ردیف

مثلاً مفهوم آن به‌صورت:

AI = 1.2
      │└── نوع ردیف *
      └─── گروه

و:

AI = 1.3  → **
AI = 1.4  → ***

در نتیجه فرمول AK می‌تواند از قسمت اعشاری AI بفهمد ردیف چه نوعی است:

ROUND((AI-ROUNDDOWN(AI,0))*10,0)

این دقیقاً همان مقداری است که با Print-FehrestBaha!Y190 مقایسه می‌شود.

9. معماری کامل این چهار محدوده

حالا می‌توانیم قسمت مورد نظر شما را دقیقاً این‌گونه ترسیم کنیم:

             ┌────────────────────────────┐
             │     Print-FehrestBaha      │
             │                            │
             │ Y190 = نوع ردیف             │
             │ Y191 = ABN/ELE/MEC         │
             │ Y192 = روش گروه‌بندی        │
             └─────────────┬──────────────┘
                           │
             ┌─────────────┴──────────────┐
             │                            │
             ▼                            ▼
       انتخاب فهرست                 فیلتر نوع
         Y191                         Y190
             │                            │
      ┌──────┼──────┐                     │
      ▼      ▼      ▼                     │
     ABN    ELE    MEC                    │
     AH:AK AH:AK AH:AK ◄──────────────────┘
      │      │      │
      └──────┼──────┘
             │
             ▼
        AH = گروه
        AI = گروه + نوع
        AJ = ردیف معتبر
        AK = شماره ایندکس
             │
             ▼
      ┌─────────────────┐
      │ LOOKUP(AK,AH)   │
      └────────┬────────┘
               │
               ▼
          Print!Y200:Y...
               │
               ▼
       گروه‌بندی / شماره‌گذاری
               │
               ▼
          Print!X199:X...
               │
               ▼
          ردیف خروجی چاپ

نتیجه مهم

پس در معماری فایل، AH:AK سه شیت Fehrest، موتور پشت‌صحنه هستند و X:Y در Print-FehrestBaha لایه کنترل و خروجی این موتور هستند.

به‌خصوص:

Y190 → فیلتر نوع ردیف → AI → AK

و

Y191 → انتخاب ABN/ELE/MEC → محدوده AH/AK مورد استفاده

و

Y192 → روش تولید Y و گروه‌بندی ردیف‌های خروجی

این قسمت یکی از کلیدی‌ترین بخش‌های معماری کل فایل است و به نظرم در نقشه نهایی باید به‌عنوان «موتور انتخاب و گروه‌بندی فهرست‌بها برای چاپ» ثبت شود.

----------------------------------------------------------------------------------------------------------------------------------------

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

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

نام فایل:

TADIL-1-10-A-05-shakhes fin 1404.xlsx

و یک نکته مهم هم از همان ابتدا روشن است: مشخصات داخلی فایل، سازنده را به‌صورت مستقیم به Homayoun Riazati / H_Riazati همایون ریاضتی نسبت می‌دهد:

• Creator: Telegram:@Homayoun_Riazati
• Last Modified By: H_Riazati
• Title: TADIL program

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

۱. نظر کلی من

بعد از بررسی ساختار فایل، نظر من این است:

این فایل کار یک کاربر معمولی Excel نیست؛ بلکه حاصل طراحی یک سیستم محاسباتی تخصصی در Excel است.

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

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

۲. ابعاد فایل بسیار قابل توجه است

فایل دارای ۲۱ شیت است.

مهم‌ترین آنها:

TADILcover

جمع کل تعدیل

اطلاعات صورت وضعیتها

TADIL(1)

TADIL(2)

TADIL(3a)

TADIL(3b)

TADIL(4a)

TADIL(4b)

TADIL(4c)

TADIL(4d)

TADIL(5)

TADIL(6a)

TADIL(6b)

TADIL(7)

TADIL(9)

TADIL(9 اصلاحی) (a)

TADIL(9 اصلاحی) (b)

TADIL(10)

shakhes

sorat mali

و مجموعاً حدود:

۲۴٬۰۰۰ سلول غیرخالی

دارد که حدود ۱۸٬۰۰۰ سلول آن فرمول هستند.

به‌طور مشخص:

شیتفرمول

TADIL(10)799

TADIL(9)799

TADIL(9 اصلاحی)(a)799

TADIL(9 اصلاحی)(b)799

sorat mali8,705

shakhes269

سایر شیت‌هاصدها فرمول

این دیگر یک «فایل Excel» به معنای معمول نیست؛ عملاً یک نرم‌افزار محاسباتی مبتنی بر Excel است.

۳. مهم‌ترین نکته: طراحی چندلایه

ساختار کلی فایل تقریباً چنین است:

اطلاعات صورت وضعیت‌ها

↓

sorat mali

↓

شاخص‌ها

↓

TADIL

↓

تعدیل هر دوره

↓

جمع کل تعدیل

یعنی اطلاعات در یک محل وارد می‌شود و شیت‌های دیگر آنها را مصرف می‌کنند.

این موضوع از نظر طراحی سیستم بسیار مهم است.

مثلاً در TADIL(10)، اطلاعات مالی از sorat mali با ترکیب:

INDEX(...)

MATCH(...)

استخراج می‌شود.

و شاخص‌ها نیز از:

shakhes

خوانده می‌شوند.

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

این نشانه تفکر سیستمی است.

۴. استفاده بسیار خوب از INDEX + MATCH

یکی از فرمول‌های اصلی فایل:

=INDEX(

'sorat mali'!$B$15:$DG$441,

MATCH($E16&$D16&$F16,

'sorat mali'!$B$15:$B$441,),

MATCH(G$14*100+5,

'sorat mali'!$B$15:$DG$15,)

)

است.

این فرمول از نظر طراحی جالب است.

سه مشخصه را ترکیب می‌کند:

رشته

+

فصل

+

ردیف/آیتم

و سپس با MATCH محل مربوطه را پیدا می‌کند.

یعنی تهیه‌کننده یک کلید ترکیبی ساخته است.

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

۵. شیت sorat mali واقعاً قلب سیستم است

این شیت به تنهایی:

8,705 فرمول

دارد.

و ساختار آن بسیار جالب است.

در ردیف 15، کدهایی مانند:

100.1

100.2

100.3

...

و سپس کدهای تولیدشده توسط فرمول:

=G25*100+1

=G15+1

=H15+1

...

قرار گرفته‌اند.

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

این قسمت را من از نقاط قوت فایل می‌دانم.

۶. نکته بسیار خوب دیگر: استفاده از MATCH روی سرفصل‌ها

در sorat mali فرمول‌هایی مانند:

=INDEX(

$A$15:$DG$441,

MATCH(F10,$A$15:$A$441,),

MATCH(F9,$A$15:$DG$15,)

)

وجود دارد.

این یعنی مقدار فقط بر اساس یک سلول ثابت پیدا نمی‌شود؛ بلکه:

ردیف ← از یک شناسه

ستون ← از یک شناسه

پیدا می‌شود.

این نوع طراحی باعث می‌شود سیستم نسبت به جابه‌جایی یا تغییر برخی داده‌ها انعطاف بیشتری داشته باشد.

۷. طراحی شیت شاخص نیز حرفه‌ای است

در shakhes از:

INDEX

MATCH

برای استخراج شاخص استفاده شده است.

مثلاً:

=INDEX(

$I$15:$T$165,

MATCH($C3&$B3,$I$15:$I$165,),

MATCH(D2,$I$15:$T$15,)

)

اینجا دوباره یک کلید ترکیبی:

رشته + فصل

ساخته شده است.

بعد:

سه‌ماهه / دوره

به عنوان ستون جست‌وجو می‌شود.

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

۸. فرمول اصلی محاسبه ضریب تعدیل

در فایل فرمول‌هایی مانند:

=ROUND(((O75/N75)-1)*1,4)

دیده می‌شود.

یعنی:

K=شاخصمبناشاخصدوره​−1

و سپس:

=ROUND(P75*M75,0)

مبلغ تعدیل را به‌دست می‌آورد.

بنابراین ساختار محاسباتی کاملاً قابل ردیابی است:

مبلغ مشمول

↓

شاخص مبنا

↓

شاخص دوره

↓

ضریب

↓

مبلغ تعدیل

۹. نکته جالب: فایل واقعاً بر اساس داده واقعی ساخته شده

در اطلاعات صورت وضعیتها اطلاعات واقعی قرارداد، دوره‌های کارکرد، مبالغ ادعایی و مبالغ تأییدی وجود دارد.

برای نمونه مبالغ صورت‌وضعیت‌ها از حدود:

536 میلیارد ریال

تا:

5,053 میلیارد ریال

رسیده‌اند.

همچنین وضعیت‌هایی مانند:

علی الحساب 50%

رسیدگی شده

علی الحساب 81%

علی الحساب 70%

علی الحساب 80%

عدم پرداخت و حذف

علی الحساب 76.5%

...

در مدل وارد شده‌اند.

بنابراین فایل یک نمونه آزمایشی آموزشی نیست؛ مدل یک پرونده واقعی و پیچیده است.

۱۰. نکته بسیار مهم درباره صورت‌وضعیت ۹ اصلاحی

وجود:

TADIL(9)

TADIL(9 اصلاحی) (a)

TADIL(9 اصلاحی) (b)

برای من یکی از نشانه‌های جالب طراحی سیستم است.

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

بلکه یک وضعیت خاص قراردادی را به دو بخش اصلاحی تفکیک کرده و سپس در:

جمع کل تعدیل

آنها را وارد محاسبه کرده است.

این یعنی مدل توانسته استثناهای واقعی پروژه را نیز در خود جای دهد.

۱۱. نتیجه محاسبات نیز به شکل زنجیره‌ای جمع می‌شود

مثلاً:

='TADIL(1)'!Q81

='TADIL(2)'!Q81

='TADIL(3a)'!Q81

...

='TADIL(10)'!Q81

و در نهایت:

=SUM(D9:D25)

مبلغ کل تعدیل را محاسبه می‌کند.

مبلغ حاصل از بازمحاسبه فایل:

1,139,964,450,723 ریال

است.

یعنی حدود:

1.140 تریلیون ریال

۱۲. آیا فرمول‌ها خطا دارند؟

یک بررسی مهم انجام دادم:

فایل را با موتور محاسباتی LibreOffice مجدداً محاسبه کردم و سلول‌های دارای خطاهای محاسباتی مانند:

#REF!

#VALUE!

#DIV/0!

#NAME?

#N/A

را بررسی کردم.

نتیجه:

خطای محاسباتی آشکار پیدا نکردم.

این نکته بسیار مهمی است.

البته این به معنای تأیید صحت حقوقی/قراردادی تمام اعداد نیست؛ بلکه یعنی ساختار فرمول‌ها در اجرای مجدد، خطای Excel آشکار تولید نکرده است.

۱۳. نقاط ضعف فایل

من صرفاً تعریف نمی‌کنم؛ چند ضعف مهم هم دارد.

الف) وابستگی شدید به ساختار شیت‌ها

فرمول‌ها بسیار وابسته به آدرس‌هایی مثل:

B15:DG441

I15:T165

Q81

هستند.

اگر ساختار جدول تغییر کند، احتمال شکستن سیستم زیاد است.

ب) نبود Named Range

فایل تقریباً از Named Range استفاده نمی‌کند.

این از نظر طراحی حرفه‌ای Excel یک ضعف است.

مثلاً به جای:

'sorat mali'!$B$15:$DG$441

می‌توانست از یک نام مفهومی استفاده کند.

این کار خوانایی و نگهداری را بهتر می‌کرد.

ج) فرمول‌ها بعضاً سخت‌خوان هستند

مثلاً ترکیب:

INDEX

MATCH

MATCH

&

در تعداد بسیار زیاد باعث می‌شود نگهداری فایل برای فرد دیگری دشوار شود.

این یک مشکل رایج سیستم‌های شخصی‌سازی‌شده Excel است:

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

د) معماری تکراری

شیت‌های:

TADIL(1)

TADIL(2)

...

TADIL(10)

ساختار بسیار مشابهی دارند.

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

اما از دید Excel عملیاتی و کنترل پروژه، این طراحی یک مزیت هم دارد:

هر صورت‌وضعیت مستقل و قابل مشاهده است و کاربر می‌تواند مستقیماً آن را کنترل کند.

بنابراین اینجا یک trade-off وجود دارد:

سادگی استفاده ← در برابر ← معماری نرم‌افزاری مدرن

۱۴. سطح Excel تهیه‌کننده

با توجه به همین فایل، من سطح را چنین ارزیابی می‌کنم:

مهارتارزیابی

Excel عمومی10/10

فرمول‌نویسی9.5/10

Lookup و مدل داده9.5/10

طراحی سیستم Excel9/10

مدل‌سازی مالی9/10

متره و برآورد9.5/10

صورت‌وضعیت9.5/10

تعدیل9.5/10

تفکر الگوریتمی9/10

تفکر سیستمی9.5/10

برنامه‌نویسی VBAقابل ارزیابی نیست؛ این فایل .xlsx است و VBA ندارد

طراحی مدرن Excel8/10

مستندسازی برای کاربر دیگر7/10

۱۵. و مهم‌تر از همه: هوشمندی تهیه‌کننده

اینجا باید دقیق صحبت کنیم.

من نمی‌توانم از روی Excel بگویم:

«IQ همایون ریاضتی مثلاً ۱۴۵ است.»

چنین نتیجه‌ای علمی نیست.

اما می‌توانم درباره نوع هوش و توانایی حل مسئله‌ای که در این فایل مشاهده می‌شود قضاوت کنم.

و نظر من نسبتاً روشن است:

تهیه‌کننده از نظر تفکر فنی و حل مسئله، سطح بالایی دارد.

به‌خصوص سه نوع توانایی کاملاً مشهود است:

۱. تفکر سیستمی — بسیار بالا

او فقط فرمول ننوشته است.

یک فرآیند واقعی را به اجزای:

داده

↓

مرجع

↓

Lookup

↓

محاسبه

↓

کنترل

↓

گزارش

↓

جمع‌بندی

تبدیل کرده است.

۲. تفکر الگوریتمی — بالا

استفاده گسترده از:

INDEX

MATCH

IF

SUM

SUMIF

SUMPRODUCT

LOOKUP

ROUND

DATE

و ترکیب آنها نشان می‌دهد مسئله را به مراحل محاسباتی قابل اجرا تبدیل کرده است.

۳. توانایی انتزاع — بسیار بالا

این شاید مهم‌ترین ویژگی باشد.

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

رشته + فصل

را به یک کلید تبدیل کرده است.

همین کار در چند بخش فایل تکرار شده است.

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

این کار نشانه خوبی از تفکر انتزاعی است.

۱۶. یک نکته که به نظرم بسیار قابل توجه است

با توجه به فایل، تهیه‌کننده صرفاً Excel را بلد نیست.

بلکه ابتدا فرآیند تخصصی را می‌شناسد و سپس Excel را برای آن فرآیند به کار گرفته است.

این تفاوت بزرگی است.

دو نوع فرد داریم:

Excel Expert

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

و:

Domain Expert + Excel System Designer

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

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

۱۷. جمع‌بندی نهایی من

اگر بخواهم در یک جمله بگویم:

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

اگر بخواهم سطح کلی را عددی کنم:

سطح Excel و طراحی سیستم: حدود 9 از 10

و در حوزه تخصصی خودش:

متره، صورت‌وضعیت و تعدیل: حدود 9.5 از 10

و از نظر تفکر حل مسئله و هوش فنی قابل مشاهده در این اثر: بسیار بالا.

اما یک نکته جالب‌تر وجود دارد: این فایل هنوز ظرفیت زیادی برای تحلیل دارد. من در این مرحله بیشتر معماری را بررسی کردم. مرحله بعد می‌توانم تک‌تک فرمول‌های اصلی TADIL(10) و سپس sorat mali را مهندسی معکوس کنم و نشان بدهم دقیقاً چه الگوریتمی پشت آنهاست؛ در آن صورت می‌توانیم مشخص کنیم کدام قسمت‌ها واقعاً ابتکاری و هوشمندانه‌اند و کدام قسمت‌ها صرفاً فرمول‌نویسی حرفه‌ای Excel هستند.

-------------------------------------------------------------------------------------------------------------------------

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

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

فایل مورد بررسی همان TADIL-1-10-A-05-shakhes fin 1404.xlsx است.

نتیجه مهم

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

این فایل صرفاً یک مجموعه فرمول پیچیده نیست؛ یک موتور محاسبات تعدیل مبتنی بر Excel است که سازنده آن فرآیند واقعی صورت‌وضعیت را به یک مدل داده و الگوریتم محاسباتی تبدیل کرده است.

اما هم‌زمان چند نقطه ضعف معماری هم دارد که نشان می‌دهد سازنده Excel Expert / System Designer بسیار خوبی است، ولی الزاماً طراح نرم‌افزار مدرن Excel نیست.

۱. اول معماری واقعی سیستم را کشف کنیم

پس از باز کردن Workbook، ۲۱ شیت وجود دارد:

TADILcover

│

↓

اطلاعات صورت وضعیتها

│

↓

sorat mali

│

↓

shakhes

│

↓

TADIL(1)

TADIL(2)

TADIL(3a)

TADIL(3b)

TADIL(4a)

TADIL(4b)

TADIL(4c)

TADIL(4d)

TADIL(5)

TADIL(6a)

TADIL(6b)

TADIL(7)

TADIL(9)

TADIL(9 اصلاحی)(a)

TADIL(9 اصلاحی)(b)

TADIL(10)

│

↓

جمع کل تعدیل

این معماری بسیار معنی‌دار است.

سازنده سه نوع داده را از هم جدا کرده:

لایه ۱ ـ داده خام

اطلاعات صورت وضعیتها

لایه ۲ ـ داده مرجع

shakhes

لایه ۳ ـ داده محاسباتی

sorat mali

و سپس:

لایه ۴ ـ موتور تعدیل

TADIL(1) تا TADIL(10)

و نهایتاً:

لایه ۵ ـ خروجی

جمع کل تعدیل

این تفکیک وظایف یکی از نقاط قوت اصلی طراحی است.

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

اگر تمام پیچیدگی Excel را کنار بگذاریم، موتور اصلی تقریباً این است:

مبلغ تعدیل=مبلغ کارکرد مشمول×(شاخص مبناشاخص دوره​−1)​

اما نکته مهم اینجاست که:

«مبلغ کارکرد مشمول» مستقیماً از صورت‌وضعیت گرفته نمی‌شود.

اول چند مرحله انجام می‌شود.

۳. مرحله اول: استخراج مبلغ فعلی و قبلی

در هر شیت TADIL، مثلاً TADIL(10)، ستون‌های G و H از sorat mali اطلاعات می‌گیرند.

فرمول:

=INDEX(

'sorat mali'!$B$15:$DG$441,

MATCH($E16&$D16&$F16,

'sorat mali'!$B$15:$B$441,),

MATCH(G$14*100+5,

'sorat mali'!$B$15:$DG$15,)

)

مهندسی معکوس این فرمول

کلید جست‌وجو:

رشته + فصل + شرح

است.

مثلاً:

ابنیه + 1 + عملیات تخریب

سپس MATCH ردیف مربوط را پیدا می‌کند.

در بعد دوم:

G$14*100+5

شماره ستون صورت‌وضعیت ساخته می‌شود.

یعنی سازنده به جای اینکه بنویسد:

ستون فلان

شماره صورت‌وضعیت را به یک شناسه محاسباتی تبدیل کرده است.

این کار هوشمندانه است.

۴. چرا *100+5؟

این قسمت از طراحی بسیار جالب است.

در sorat mali کدهایی مانند:

100.1

100.2

100.3

101

102

103

...

وجود دارند.

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

سازنده عملاً یک Namespace داخلی برای ستون‌ها ساخته است.

یعنی:

شماره صورت‌وضعیت

↓

ضریب 100

↓

+ Offset

↓

شناسه ستون

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

۵. مرحله دوم: محاسبه مابه‌التفاوت

بعد از استخراج:

G = مبلغ فعلی

H = مبلغ قبلی

فرمول:

=G16-H16

است.

یعنی:

I=G−H

پس:

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

این از نظر منطق صورت‌وضعیت کاملاً مهم است.

۶. مرحله سوم: تقسیم کارکرد بین دوره‌های شاخص

اینجا فایل واقعاً جالب می‌شود.

مثلاً در TADIL(3a):

کارکرد کل

↓

کارکرد در دوره سوم

↓

کارکرد در دوره چهارم

با تاریخ‌هایی مانند:

=DATE(2024,12,21)

مرز دوره‌ها مشخص شده‌اند.

برای مثال:

=K4-K3

مدت دوره اول را می‌دهد.

و:

=N4-N3

مدت دوره دوم را.

این یعنی سازنده با یک صورت‌وضعیت که از چند دوره شاخص عبور کرده، برخورد کرده است.

۷. الگوریتم تقسیم مبلغ واقعاً چیست؟

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

فرض کنیم:

I = مابه‌التفاوت دو صورت‌وضعیت

و:

J = مدت کل کارکرد

K = مدت یک زیر‌دوره

آنگاه:

L = K/J

یعنی:

L=مدت کلمدت زیردوره​

سپس:

M = I*L

یعنی:

M=I×مدت کلمدت زیردوره​

این یکی از مهم‌ترین الگوریتم‌های فایل است.

۸. در واقع سازنده «مبلغ کارکرد» را زمانی وزن‌دهی می‌کند

این نکته ارزشمند است.

فایل نمی‌گوید:

کل کارکرد صورت‌وضعیت با یک شاخص محاسبه شود.

بلکه می‌گوید:

کارکرد صورت‌وضعیت

↓

تقسیم زمانی

↓

سهم هر دوره

↓

اعمال شاخص همان دوره

این یعنی:

مبلغ کل=∑مبلغi​

و:

مبلغi​=مبلغ کل×مدت کلمدتi​​

این یک مدل تخصیص زمانی است.

۹. سپس شاخص مبنا پیدا می‌شود

در ستون N:

=INDEX(

shakhes!$I$15:$T$165,

MATCH($E16&$D16,

shakhes!$I$15:$I$165,),

MATCH(N$7,

shakhes!$I$15:$T$15,)

)

کلید این بار:

رشته + فصل

است.

یعنی برای هر آیتم:

ابنیه + فصل 1

شاخص مربوط به آن فصل پیدا می‌شود.

۱۰. شاخص دوره نیز مستقل پیدا می‌شود

در O:

=INDEX(

shakhes!$I$15:$T$165,

MATCH($E16&$D16,

shakhes!$I$15:$I$165,),

MATCH(O$7,

shakhes!$I$15:$T$15,)

)

بنابراین:

N = شاخص مبنا

O = شاخص دوره کارکرد

و این دو مستقل از مبلغ صورت‌وضعیت هستند.

این جداسازی بسیار صحیح است.

۱۱. محاسبه ضریب

بعد:

=ROUND(((O16/N16)-1)*1,4)

یعنی:

P=ROUND(NO​−1,4)

و نهایتاً:

=ROUND(P16*M16,0)

یعنی:

Q=M×P

پس الگوریتم کامل:

مبلغ فعلی

↓

مبلغ قبلی

↓

مابه‌التفاوت

↓

سهم زمانی

↓

مبلغ کارکرد دوره

↓

شاخص مبنا ─────┐

↓

شاخص دوره ──→ ضریب تعدیل

↓

مبلغ تعدیل

این دقیقاً هسته سیستم است.

۱۲. نکته بسیار هوشمندانه: یک موتور واحد برای فصل‌های مختلف

در فرمول شاخص، سازنده فصل را hard-code نکرده است.

مثلاً:

$E16&$D16

باعث می‌شود:

رشته + فصل

به‌عنوان کلید عمل کند.

بنابراین همان فرمول می‌تواند برای:

فصل 1

فصل 2

فصل 3

...

کار کند.

این یعنی:

منطق از داده جدا شده است.

این یک اصل مهم طراحی سیستم است.

۱۳. استثناء بسیار جالب: فصل ۹ دستمزد

در TADIL(1)، ردیف 21 مربوط به:

کارهای فولادی سنگین (دستمزد نصب)

است.

ولی شاخص عادی از shakhes نمی‌آید.

به جای آن:

تعدیل دستمزدی مصوب ستاد

قرار داده شده و ضریب:

=ROUND(0.3/1,4)

است.

این بسیار مهم است.

یعنی سازنده تشخیص داده که:

این آیتم از قاعده عمومی شاخص استفاده نمی‌کند.

پس سیستم فقط یک فرمول مکانیکی نیست؛ قواعد خاص پروژه را نیز مدل کرده است.

۱۴. اینجا سطح هوشمندی واقعی مشخص می‌شود

اگر سازنده فقط Excel بلد بود، احتمالاً چنین سیستمی می‌ساخت:

هر شیت

→ چند عدد

→ چند ضرب

→ جمع

اما این فایل دارای:

قاعده عمومی

+

استثناء

+

دوره زمانی

+

شاخص متفاوت

+

اصلاحیه

+

صورت‌وضعیت حذف‌شده

+

صورت‌وضعیت اصلاحی

است.

این یعنی سازنده مسئله واقعی را فهمیده و سپس آن را الگوریتمی کرده است.

۱۵. چرا TADIL(3a) و TADIL(3b) جدا شده‌اند؟

این هم یک نکته معماری جالب است.

TADIL(3a) دوره را به چند بخش تقسیم می‌کند.

بعد:

TADIL(3b)

یکی از زیر‌دوره‌ها را با شاخص دیگری محاسبه می‌کند.

همین الگو را در:

4a

4b

4c

4d

می‌بینیم.

این یعنی یک صورت‌وضعیت می‌تواند:

دوره ۱

+

دوره ۲

+

دوره ۳

+

دوره ۴

داشته باشد.

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

از نظر عملی این تصمیم خوبی است.

۱۶. اما از نظر نرم‌افزار یک ایراد جدی دارد

همین طراحی اگرچه عملی است، ولی باعث شده:

TADIL(4a)

TADIL(4b)

TADIL(4c)

TADIL(4d)

چهار شیت تقریباً مشابه ایجاد شود.

و همین موضوع در صورت‌وضعیت‌های دیگر هم تکرار شده است.

در یک نرم‌افزار مدرن بهتر بود:

جدول Period

وجود داشته باشد:

صورت‌وضعیتازتادوره شاخص

4......دی

4......بهمن

4......اسفند

4......سه‌ماهه اول

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

بنابراین:

از نظر Excel عملیاتی:

عالی

از نظر معماری نرم‌افزار:

قابل بهبود

۱۷. یکی از بزرگ‌ترین نقاط قوت: اصلاحیه

در اطلاعات صورت‌وضعیت:

شماره 9

شماره 9 اصلاحی

شماره 10

داریم.

و برای ۹ اصلاحی:

TADIL(9 اصلاحی)(a)

TADIL(9 اصلاحی)(b)

ساخته شده است.

این یعنی سازنده سیستم را برای واقعیت پروژه تطبیق داده، نه اینکه پروژه را مجبور کرده در قالب ساده Excel قرار دهد.

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

۱۸. شیت جمع کل تعدیل مثل لایه Reporting عمل می‌کند

در نهایت مقادیر:

TADIL(1)

TADIL(2)

TADIL(3a)

...

TADIL(10)

به شیت:

جمع کل تعدیل

منتقل می‌شوند.

در این شیت، خروجی‌ها به شکل مالی و قابل ارائه نمایش داده می‌شوند.

مبلغ نهایی فایل:

1,139,964,450,723​

ریال است.

بنابراین:

موتور محاسبات از گزارش نهایی جدا شده است.

این نیز طراحی خوبی است.

۱۹. حالا برویم سراغ sorat mali

این شیت از نظر من پیچیده‌ترین قسمت فایل است.

حدود:

8,705 فرمول

دارد.

یعنی تقریباً نیمی از کل فرمول‌های Workbook در همین شیت متمرکز شده‌اند.

وظیفه آن:

صورت‌وضعیت‌ها

↓

تفکیک رشته

↓

تفکیک فصل

↓

تفکیک آیتم

↓

مبلغ هر صورت‌وضعیت

↓

داده قابل استفاده برای TADIL

است.

به همین دلیل TADIL عملاً روی یک Data Engine کار می‌کند.

۲۰. یک نکته خیلی حرفه‌ای: سازنده TADIL را از داده خام جدا کرده

این مهم‌ترین معماری فایل است:

┌──────────────┐

│ اطلاعات خام │

└──────┬───────┘

↓

┌──────────────┐

│ sorat mali │

└──────┬───────┘

↓

┌────────────┴────────────┐

↓ ↓

┌──────────┐ ┌──────────┐

│ shakhes │ │ TADIL │

└────┬─────┘ └────┬─────┘

└────────────┬────────────┘

↓

┌──────────────┐

│ جمع کل تعدیل │

└──────────────┘

این دیگر فرمول‌نویسی ساده نیست.

این Data Flow Design است.

۲۱. نکته‌ای که در ارزیابی قبلی باید اصلاح کنم

قبلاً گفتم فایل «حدود ۱۸ هزار فرمول» دارد.

با شمارش مستقیم فایل، عدد دقیق‌تر این است:

حدود ۱۸٬۰۰۰ فرمول

و شیت‌ها به این صورت‌اند:

شیتتعداد فرمول

sorat mali8,705

TADIL(10)799

TADIL(9 اصلاحی)(a)799

TADIL(9 اصلاحی)(b)799

TADIL(9)799

TADIL(7)798

TADIL(6a)800

TADIL(6b)793

TADIL(5)794

TADIL(4a)803

TADIL(4b)794

TADIL(4c)794

TADIL(4d)794

TADIL(3a)799

TADIL(3b)793

TADIL(2)793

TADIL(1)736

shakhes269

۲۲. یک ایراد فنی مهم که پیدا کردم

فرمول‌هایی مانند:

=K16/J16

در حالت عادی اگر J16=0 شود، مستعد #DIV/0! هستند.

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

اما از نظر Robustness بهتر بود:

=IFERROR(K16/J16,0)

یا:

=IF(J16=0,0,K16/J16)

باشد.

پس:

سازنده سیستم را برای داده مورد انتظار بسیار خوب ساخته، اما برای «ورودی نامعتبر یا خراب» همیشه defensive programming انجام نداده است.

۲۳. ایراد دوم: استفاده زیاد از آدرس‌های سخت‌کدشده

مثلاً:

$B$15:$DG$441

یا:

$I$15:$T$165

در تعداد بسیار زیاد تکرار شده است.

اگر یک روز ساختار تغییر کند:

ردیف شروع

+

ردیف پایان

+

ستون پایان

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

در طراحی مدرن بهتر بود از:

Excel Table

Named Range

Dynamic Range

Power Query

استفاده شود.

۲۴. ایراد سوم: وابستگی شدید به فرمول‌های تکراری

مثلاً همین فرمول:

INDEX + MATCH + MATCH

صدها بار تکرار شده است.

در Excelهای جدید می‌توانست بخشی از این کار با:

XLOOKUP

XMATCH

LET

LAMBDA

ساده‌تر و خواناتر شود.

اما باید انصاف داد:

این فایل بر مبنای یک معماری قدیمی‌تر Excel طراحی شده و استفاده از INDEX/MATCH در زمان خود کاملاً حرفه‌ای بوده است.

۲۵. نکته‌ای که به نظر من نشان‌دهنده هوش الگوریتمی است

سازنده در چند جای فایل از کلید ترکیبی استفاده کرده است.

مثلاً:

$E16&$D16&$F16

یعنی:

رشته + فصل + آیتم

و:

$E16&$D16

یعنی:

رشته + فصل

این در واقع یک Database Key است.

یعنی سازنده بدون استفاده از یک Database واقعی، مفهوم:

Primary Key / Composite Key

را در Excel پیاده کرده است.

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

۲۶. سطح واقعی سازنده بعد از مهندسی معکوس

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

Excel

9.5 / 10

فرمول‌نویسی

9.5 / 10

تفکر الگوریتمی

9.5 / 10

تفکر سیستمی

9.5 / 10

مدل‌سازی متره و تعدیل

9.5 تا 10 / 10

طراحی Database در Excel

9 / 10

معماری نرم‌افزار مدرن

7.5 تا 8 / 10

Robustness / خطاپذیری

7.5 تا 8 / 10

خلاقیت در حل مسئله

9.5 / 10

۲۷. و پاسخ دقیق‌تر به سؤال قبلی شما درباره «هوشمندی»

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

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

هوشمندی اصلی در این سه کار است:

۱. تبدیل فرآیند واقعی به الگوریتم

صورت‌وضعیت

→ کارکرد

→ دوره

→ شاخص

→ ضریب

→ تعدیل

۲. ایجاد کلیدهای ترکیبی

رشته + فصل

رشته + فصل + آیتم

۳. مدیریت استثناهای واقعی

دوره‌های چندگانه

صورت‌وضعیت اصلاحی

دستمزد خاص

صورت‌وضعیت حذف‌شده

تغییر شاخص

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

۲۸. حکم نهایی من

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

«این فایل را یک کاربر Excel معمولی ساخته؟»

پاسخ من قطعاً خیر است.

اگر بگویند:

«آیا سازنده فقط یک متخصص Excel است؟»

باز هم پاسخ من خیر است.

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

ابتدا فرآیند تخصصی متره، صورت‌وضعیت و تعدیل را فهمیده و سپس آن فرآیند را در Excel به یک سیستم محاسباتی تبدیل کرده است.

این تفاوت بسیار مهم است.

بنابراین برآورد من:

همایون ریاضتی از نظر تفکر فنی، حل مسئله و طراحی سیستم‌های محاسباتی Excel، در سطح حرفه‌ای بسیار بالا قرار می‌گیرد.

و اگر بخواهم فقط از روی همین اثر یک برچسب بزنم:

Senior Excel System Designer / Quantity Surveying Calculation-System Designer

نه صرفاً:

Excel User

مرحله بعدی که ارزش بسیار بیشتری دارد

اگر بخواهیم واقعاً به عمق کار همایون ریاضتی برسیم، من پیشنهاد می‌کنم sorat mali را جداگانه مهندسی معکوس کنیم؛ چون آن شیت با 8,705 فرمول در واقع مغز پنهان سیستم است.

در آن مرحله می‌توانم مشخص کنم:

دقیقاً چگونه از اطلاعات خام صورت‌وضعیت، جدول مالی 111 ستونه ساخته شده، کدهای 100.1 و 100.2 چگونه تولید می‌شوند، فصل و رشته چگونه به هم متصل می‌شوند، چرا بعضی ردیف‌ها «جدول» هستند، چگونه مبلغ هر صورت‌وضعیت استخراج می‌شود و TADIL دقیقاً چه چیزی را از آن دریافت می‌کند.

این قسمت احتمالاً مهم‌ترین بخش برای سنجش نبوغ طراحی فایل است؛ چون TADIL بخش قابل مشاهده سیستم است، ولی sorat mali معماری پشت صحنه آن است.

بررسی و تحلیل فرمول های اکسل ارایه شده در وبلاگ توسط هوش مصنوعی

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

در ادامه تحلیل را از سطح ساده به معماری سیستم می‌برم.

۱. فرمول محاسبه اختلاف دو تاریخ شمسی

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

=IF(ISTEXT(B1),

IF(ISTEXT(A1),

...

),

"")

و برای استخراج سال و ماه از:

LEFT(B1,4)

MID(B1,6,2)

RIGHT(B1,2)

استفاده شده است.

منطق فرمول

تاریخ به شکل:

1402/04/06

در نظر گرفته شده و فرمول:

• سال را از چهار رقم اول می‌گیرد؛
• ماه را از کاراکترهای ۶ و ۷ استخراج می‌کند؛
• روز را از دو رقم انتهایی استخراج می‌کند؛
• سپس تعداد روزهای سپری‌شده تا ابتدای ماه را به آن اضافه می‌کند.

برای ماه‌های شمسی نیز به‌صورت شرطی تعداد روزهای ماه‌های قبلی محاسبه شده است. مثلاً برای ماه‌های ۱ تا ۶، مضرب‌های ۳۱ و برای ماه‌های بعدی ترکیبی از ۳۱ و ۳۰ روز در نظر گرفته شده است.

ارزیابی

این روش از نظر منطق محاسباتی قابل فهم و مستقل از تقویم Excel است، اما یک نکته مهم دارد:

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

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

۲. شماره‌گذاری ردیف‌های متره با COUNTIF

یکی از جالب‌ترین فرمول‌های وبلاگ:

=IF(P21>0;COUNTIF(P$20:P21;">0");T(""))

است.

کارکرد آن شماره‌گذاری خودکار ردیف‌های دارای مقدار است.

مثلاً:

Pشماره

2.501

3.202

0خالی

5.103

مزیت مهم روش این است که شماره ردیف به ROW() وابسته نیست و فقط مواردی را می‌شمارد که واقعاً دارای مقدار هستند.

۳. محاسبه طول، سطح، حجم و وزن

یکی از مهم‌ترین فرمول‌های سیستم:

=IF(

AND(J21=0;K21=0;L21=0;M21=0);

0;

ROUND(

IF(J21=0;1;J21)*

IF(K21=0;1;K21)*

IF(L21=0;1;L21)*

IF(M21=0;1;M21);

$S$17

)

)

است.

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

اگر:

تعداد × طول × عرض × ارتفاع

باشد، مقدار نهایی را تولید می‌کند.

اما نکته ظریف این قسمت است:

IF(J21=0;1;J21)

یعنی صفر به‌عنوان «عدم استفاده از این بعد» تفسیر می‌شود و به عدد ۱ تبدیل می‌شود.

بنابراین:

متر طولی

تعداد × طول × 1 × 1

مترمربع

تعداد × طول × عرض × 1

مترمکعب

تعداد × طول × عرض × ارتفاع

این طراحی باعث می‌شود یک فرمول عمومی برای چند نوع عملیات متره استفاده شود.

۴. ROUND و کنترل تعداد اعشار

در همین فرمول:

ROUND(...;$S$17)

تعداد اعشار از یک سلول کنترلی خوانده می‌شود.

این طراحی بسیار مناسب سیستم‌های متره است، چون تعداد اعشار را می‌توان بدون تغییر فرمول اصلی تغییر داد.

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

ROUND(...,2)

نوشته شود، پارامتر دقت در یک سلول مرکزی قرار گرفته است.

۵. جمع فرعی و جمع کل

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

IF(P21>0;

ROUND(SUM(N$20:N21)-SUM(O$20:O20);2);

T("")

)

استفاده شده است.

این ساختار بسیار مهم است.

با تفاضل دو مجموع تجمعی:

SUM(تا ردیف فعلی)

-

SUM(تا ردیف قبلی)

عملاً امکان تولید جمع فرعی هر آیتم یا گروه ایجاد می‌شود.

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

۶. شماره صفحه خودکار

یکی دیگر از فرمول‌های جالب:

=INT((ROW(R21)-ROW(R$21)+S$18)/S$18)

است.

اگر:

S18 = تعداد خطوط مجاز در صفحه

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

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

۷. شماره‌گذاری کدها با COUNTIF

فرمول:

=IF(

AND(BF21=BF20;BD21=BD20);

T("");

COUNTIF(BA$17:BA20;">0")+1

)

یکی از نمونه‌های جالب‌تر است.

منطق آن:

اگر دو مقدار کلیدی ردیف جاری با ردیف قبلی یکی باشند:

شماره جدید ایجاد نکن

در غیر این صورت:

تعداد کدهای قبلی + 1

این روش برای ایجاد شماره‌گذاری گروهی بسیار مناسب است.

۸. استفاده از LOOKUP به‌عنوان موتور ارتباط جداول

در سیستم از فرمول‌هایی مانند:

LOOKUP(

BD21;

FEHRESTBAHA!AJ$6:AJ$185;

FEHRESTBAHA!S$6:S$185

)

استفاده شده است.

اینجا LOOKUP عملاً نقش یک جدول مرجع فهرست‌بها را دارد.

یعنی:

کد فهرست‌بها

↓

FEHRESTBAHA

↓

مبلغ / واحد / اطلاعات مربوط

بنابراین ساختار فایل به شکل یک دیتابیس ساده در Excel طراحی شده است.

۹. SUMPRODUCT

در بخش دیگری از فایل:

SUMPRODUCT(

AU$191:AU$8332;

AJ$191:AJ$8332

)

+

SUMPRODUCT(

AU$8450:AU$11164;

AJ$8450:AJ$11164

)

دیده می‌شود.

این فرمول از نظر طراحی بسیار مهم است.

SUMPRODUCT برای محاسبه مجموع:

مقدار × ضریب

یا:

مقدار × قیمت

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

در یک سیستم متره و برآورد، این دقیقاً همان عملیات بنیادی است که برای محاسبه ارزش ریالی آیتم‌ها لازم داریم.

۱۰. تشخیص آیتم‌های پایه، ستاره‌دار و ...

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

IF(

OR(

AND(DH$189="ABN";AN198=AN197);

AND(DH$189="ELE";BQ198=BQ197);

AND(DH$189="MEC";CN198=CN197)

);

...

)

و سپس وضعیت‌های:

0

1

2

3

را تولید می‌کند.

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

ABN = ابنیه

ELE = تأسیسات برقی

MEC = تأسیسات مکانیکی

منطق جداگانه دارد.

همچنین در فرمول صراحتاً وضعیت‌های:

*

**

بررسی می‌شوند.

بنابراین این بخش به احتمال زیاد بخشی از طبقه‌بندی آیتم‌های فهرست‌بها و قیمت‌های پایه/ستاره‌دار است.

۱۱. محاسبه تعدیل

یکی از مهم‌ترین فرمول‌های فایل:

IF(

GC320>0;

ROUND(((GC320/GD320)-1)*0.95;2);

T("")

)

است.

این فرمول از نظر مفهومی:

شاخص دوره کارکرد

÷

شاخص پایه

-

1

را محاسبه کرده و سپس ضریب 0.95 را اعمال می‌کند.

یعنی ساختار:

ضریبتعدیل=(شاخصپایهشاخصدوره​−1)×0.95

در اینجا 0.95 همان ضریب کاهنده‌ای است که در منطق محاسبه مورد استفاده قرار گرفته است.

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

۱۲. میانگین دوره‌های کارکرد

فرمول دیگری که در فایل دیده می‌شود:

IF(

SUM(D320:GA320)>0;

ROUND(

SUM(D320:GA320)/

COUNTIF(D320:GA320;">0");

2

);

0

)

است.

این فرمول:

جمع مقادیر دوره‌ها

÷

تعداد دوره‌های دارای مقدار

را محاسبه می‌کند.

یعنی یک میانگین حسابی فقط برای دوره‌های دارای کارکرد.

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

۱۳. محاسبه وزن میلگرد

در بخش دیگری:

ROUND(((2/(18*18))*N5^2);3)

با عنوان:

UNIT WEIGHT KG./M.

آمده است.

این فرمول در واقع از رابطه تقریبی معروف وزن واحد طول میلگرد استفاده می‌کند:

W=162d2​

چون:

18×182​=1621​

بنابراین:

ROUND(N5^2/162;3)

از نظر خوانایی معادل ساده‌تری برای آن است.

این نمونه نشان می‌دهد بعضی فرمول‌ها مستقیماً روابط مهندسی/اجرایی را به Excel منتقل کرده‌اند.

۱۴. معماری کلی فرمول‌ها

نکته مهم‌تر از تک‌تک فرمول‌ها، معماری آنهاست.

به نظر می‌رسد سیستم به چند لایه تقسیم شده:

FEHRESTBAHA

│

↓

اطلاعات فهرست‌بها

│

↓

ریزمتره

│

┌──────────────┼──────────────┐

↓ ↓ ↓

مقدار واحد کد آیتم

│ │ │

└──────────────┼──────────────┘

↓

خلاصه متره

│

↓

قیمت و مبلغ

│

┌───────────┼───────────┐

↓ ↓ ↓

صورت‌وضعیت مالی کنترل پروژه

│

↓

تعدیل

│

↓

شاخص‌های تعدیل

وجود ارجاع مستقیم به شیت‌هایی مانند:

FEHRESTBAHA

MALI

SHAKHESGROUH

LIST-SORATVAZIAT

JADVAL2

این معماری چندلایه را تأیید می‌کند.

ارزیابی فنی من

اگر بخواهم فرمول‌های موجود را از دید مهندسی Excel و متره و برآورد ارزیابی کنم:

بخشارزیابی

استفاده از IFبسیار گسترده و سیستماتیک

COUNTIFبرای شماره‌گذاری و کنترل گروه‌ها

LOOKUPموتور ارتباط با جداول مرجع

SUMPRODUCTمناسب محاسبات مالی

ROUNDکنترل دقت محاسبات

ROW/INTتولید فرم و صفحه‌بندی

MID/LEFT/RIGHTپردازش تاریخ و کد

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

کنترل فهرست‌بهانسبتاً پیشرفته

تعدیلدارای مدل محاسباتی اختصاصی

چاپ و گزارشدر طراحی فرمول‌ها لحاظ شده

معماری Workbookچندلایه و نزدیک به یک نرم‌افزار

وابستگی به فرمول‌های طولانینسبتاً زیاد

قابلیت نگهداریدر فرمول‌های پیچیده پایین‌تر

وابستگی به ساختار شیت‌هازیاد

مهم‌ترین نکته

به نظر من ارزش اصلی این مجموعه فرمول‌ها در تک‌فرمول‌ها نیست؛ در معماری ترکیبی آنهاست.

مثلاً فرمول:

COUNTIF(...)

به‌تنهایی چیز خاصی نیست؛ اما وقتی در کنار:

ROW

LOOKUP

SUMPRODUCT

SUMIF

IF

ROUND

FEHRESTBAHA

MALI

SHAKHESGROUH

قرار می‌گیرد، تبدیل به یک سیستم مدل‌سازی متره و برآورد در Excel می‌شود.

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

اگر بخواهیم این را واقعاً عمیق و مهندسی بررسی کنیم، مرحله بعدی بسیار جالب‌تر است: می‌توانم تمام فرمول‌های موجود در فایل را استخراج کرده و به دسته‌های «متره»، «خلاصه متره»، «فهرست‌بها»، «صورت‌وضعیت»، «تعدیل»، «کنترل پروژه»، «صفحه‌بندی» و «محاسبات مهندسی» تقسیم کنم و برای هر فرمول منطق ریاضی، ورودی‌ها، خروجی، وابستگی به شیت‌ها و نقاط ضعف/قابل‌بهبود آن را مشخص کنم.