做为一名水利水电施工企业经营管理人员,经常要处理大量工程数据。特别是对上结算报表的编制,要有本月、本年、自开工等等累计数据，处理起来很麻烦，因此想编一个结合实际、适用性比较的 EXCEL 模板，各位同行有什么好的建议和经验欢迎在此留言，共同编制，以提高业务水平！！ 同时欢迎加入水利水电工程施工造价群，群号：1953174以下链接是本人编制的某引水式发电站工程对上结算报表，利用函数编制。
做为一名水利水电施工企业经营管理人员,经常要处理大量工程数据。特别是对上结算报表的编制,要有本月、本年、自开工等等累计数据，处理起来很麻烦，因此想编一个结合实际、适用性比较的 EXCEL 模板，各位同行有什么好的建议和经验欢迎在此留言，共同编制，以提高业务水平！！
同时欢迎加入水利水电工程施工造价群，群号：1953174
以下链接是本人编制的某引水式发电站工程对上结算报表，利用函数编制。
http://co.163.com/forum/content/212_928531_1.htm
打开密码：1953174
以下附件是利用自定义函数编制的水利水电工程施工企业对上结算报表--Excel系统,主要是方便计算自开工、和自年初累计的计算，欢迎有共同爱好者探讨和学习！！
[
本帖最后由 wxvtang 于 2008-9-13 10:46 编辑 ]
2楼
第一篇 编制参数表
为了计算方便,首先需要先建一个参数表。数据主要包括结算的年份和月份数，为求累计数时利用公式的便利，工程不管是某年的那个月开工，都从开工当年的1月起建立年份和月份数。同时指定一个计算合价的保留位数，并将其名称定义为位数。同时建立一个组合框，数据源是刚才建好的年份和月份数，链接的单元格是报表的顺序号，并将其名称定义为序号，这个数字很关键，以后的计算都要用到。
[
本帖最后由 wxvtang 于 2008-9-13 10:40 编辑 ]
回复
3楼
定义保留位数
回复
4楼
定义报表的顺序号
回复
5楼
第二篇 编制工程量表
（待续）
回复
6楼
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
'＊ ＊
'＊ 计算自开工至上月末累计完成累计数据的自定义函数 ＊
'＊ ＊
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Public Function Kglj(Mou As Double, c As Range)
Dim a As Double
Dim b As Double
a = c.Row
b = c.Column
Kglj = WorksheetFunction.Sum(Range(Cells(a, b), Cells(a, b - 1 + Mou)))
End Function
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
'＊ ＊
'＊ 计算自年初累计数据的自定义函数 ＊
'＊ ＊
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Public Function Nclj(Mou As Double, c As Range)
Dim a As Double
Dim b As Double
a = c.Row
b = c.Column
Select Case Mou
Case 1 To 12
Nclj = WorksheetFunction.Sum(Range(Cells(a, b), Cells(a, b - 1 + Mou)))
Case 13 To 24
Nclj = WorksheetFunction.Sum(Range(Cells(a, b + 12), Cells(a, b - 1 + Mou)))
Case 25 To 36
Nclj = WorksheetFunction.Sum(Range(Cells(a, b + 24), Cells(a, b - 1 + Mou)))
Case 37 To 48
Nclj = WorksheetFunction.Sum(Range(Cells(a, b + 36), Cells(a, b - 1 + Mou)))
Case 49 To 60
Nclj = WorksheetFunction.Sum(Range(Cells(a, b + 48), Cells(a, b - 1 + Mou)))
End Select
End Function
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
'＊ ＊
'＊ 计算本月数据的自定义函数 ＊
'＊ ＊
'＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Public Function Byue(Mou As Double, c As Range)
Dim a As Double
Dim b As Double
a = c.Row
b = c.Column
Byue = Cells(a, b - 1 + Mou).Value
End Function
[
本帖最后由 wxvtang 于 2008-9-13 10:52 编辑 ]
回复
7楼
新来报道 看到这个怎么下载啊
回复
