Excel拥有强大的数学计算、逻辑运算功能,在现代办公工作中运用广泛。下面简要介绍Excel在设计工作中的运用案例,供大家借鉴。

一、数据查询

常用函数:LOOKUP、VLOOKUP、HLOOKUP、INDEX、MATCH

举例1:根据城市名称查询当地气象条件、地震设计资料。原始数据为按省、市名列出的各地气象条件、按省列出的各省地震设计资料。

excel开发工具导入功能(Excel在设计工作中的应用)(1)

首先,根据城市名称查询当地气象条件,这个很简单,原始数据已按市名列出的各地气象条件,用VLOOKUP函数查询即可。

如何根据城市名查出地震设计资料呢?地震设计资料原始数据是按省列出,我们先要根据城市名确定是哪个省的。先用MATCH函数查出城市名所在的行数,再用INDEX函数查得省所有在列那一行是哪个省。由省名再次使用MATCH函数确定该省地震设计资料所在的行号,在要显示地震设计资料的单元格使用INDIRECT(ADDRESS(行号,列号))即可。

以上是基本的查询过程。要设计一个好的工具尚需解决使用过程可能遇到的各种情况,如:表中的有的地名需要模糊查询才能找到(比如这个地名:乌拉特中旗海流图);直接输入省名,通过列表选择城市名查询;在查出的地震资料中突出显示要查的地名。这些问题均可通过一定的方法解决,在此不再赘述。

经过优化后最终使用效果如下:

excel开发工具导入功能(Excel在设计工作中的应用)(2)

excel开发工具导入功能(Excel在设计工作中的应用)(3)

二、查询图表

excel开发工具导入功能(Excel在设计工作中的应用)(4)

附图为压力容器计算中根据D0/δ0和L/D0两个参数查表求A值。人工查图比较繁琐,还需插值计算,效率很低。下面介绍用Excel根据Do/δe和L/D0两个参数自动算出A值的方法:

1. 根据Do/δe和L/D0典型数值查图列出表格。如下图(局部)

excel开发工具导入功能(Excel在设计工作中的应用)(5)

2. 根据Do/δe,利用HLOOKUP函数在表中查出比Do/δe小的(Do/δe)1值,再用INDIRECT和MATCH函数查出比Do/δe大的(Do/δe)2值。

3. 同理,根据L/D0,利用VLOOKUP函数在表中查出比L/D0小的(L/D0)1值,再用INDIRECT和MATCH函数查出比L/D0大的(L/D0)2值。

4. 用VLOOKUP函数查询(L/D0)1值对应的(Do/δe)1和(Do/δe)2的两个A值,并用TREND函数插值计算,得出(L/D0)1和Do/δe对应的A值。

5. 同理,用VLOOKUP函数查询(L/D0)2值对应的(Do/δe)1和(Do/δe)2的两个A值,并用TREND函数插值计算,得出(L/D0)1和Do/δe对应的A值。

6. 由(L/D0)1的A值和(L/D0)2的A值,插值计算(L/D0)的A值。

7. 以上过程编制成Excel计算表后,只要输入(L/D0)和(Do/δe),即可得出A值。

再举一例:压力容器圆筒局部受力的验算(如不带刚性环的耳式支座处),验算过程需要查十几个图表,按上述方法编制成Excel计算工具,只要输入已知条件,即可得出验算结果。

excel开发工具导入功能(Excel在设计工作中的应用)(6)

三、CAD明细表工具

CAD明细表格式如下:

excel开发工具导入功能(Excel在设计工作中的应用)(7)

目前二维的CAD软件均不提供材料重量计算功能,需设计人员人工计算。能否发挥Excel的计算强项,自动算出材料重量?答案是肯定的。既然这个问题可以人工计算,那么我们可以用软件来模拟人工计算过程。下面简要介绍利用VBA编程实现此功能的思路。

首先,各种板型计算公式不同,因此我们要先判断每行用的是什么材料。

不同的材料重量计算公式是不同的,根据具体的材料,编制自定义函数,以材料和尺寸作用函数参数,计算得的重量作为返回值,填入单重栏。

用循环语句对每一行重复执行以上过程,即可得出整个表格的单重。总得由单重与数量相乘即可。

以上即为计算重量的基本过程。利用Excel强大的计算功能、丰富的函数和VBA,还可实现自动编图号、输入自动填充等功能,非常方便。

在“目录”表中添加部件和数量,使用VBA的change事件,可自动新增对应的明细表。这样可以创建整套设备的明细表,以便汇总材料。汇总材料的过程见下一节。

excel开发工具导入功能(Excel在设计工作中的应用)(8)

excel开发工具导入功能(Excel在设计工作中的应用)(9)

四、材料汇总

有了整套设备的明细表,如果要将整套设备的材料汇总, 用VBA编程不难解决。主要过程如下:

1. 用VBA语句选择明细表文件并打开(此代码网上可找到)。

2. 用循环语句将各明细表复制到汇总表中。

3. 用循环语句逐行读取,无重量的作为外购件,复制到外购件表中,合并相同的外购件。

4. 有重量的作为原材料,用数据透视表汇总。

按此方法做成Excel汇总模板,材料汇总可一键完成。最终效果如下:

excel开发工具导入功能(Excel在设计工作中的应用)(10)

五、 生成图纸目录

一般情况下一套图纸或每个卷册应编制目录,注明卷册名称、张数等。人工编制目录既简单又枯燥,一点技术含量也没有。那么这个工作能否交也电脑完成呢?可以!

1. 我们需要在给图纸文件命名时采用图号 图名的格式,图号和图名之间用唯一的标识符分开(如空格,下划线等)。

2. 做好目录模板。

3. 编写VBA程序获得指定目录下全部文件名(此代码网上可找到)。

4. 用字符串处理函数分离图号、图名、后缀名。

5. 根据我们指定的图纸文件格式(后缀名),用VBA删除不需要的文件名。

6. 按文件名排序。

7. 用循环语句逐行复制图号和图名到目录模板中。

8. 图纸总张数、目录页数等均可用Excel函数自动计算。

按上述方法即可做成图纸目录工具。最终效果如下:

excel开发工具导入功能(Excel在设计工作中的应用)(11)

六、 文件批量改名

我们在工作中有时需要对大量件改名,如:在文件名中加上图号,文件名前面或后面增加序号,将BAK文件改为DWG文件等。如果用VBA编写一个改名的工具,这些工作就简单多了。

1. 编写VBA程序获得指定目录下全部文件名(此代码网上可找到)。

2. 在Excel中编辑文件名,如加序号、增加前后缀、文件替换等。

3. 编写VBA程序,使用文件对象的name属性,对原文件按Excel表中新的文件名逐个改名。

下图左侧为将原文件名,右侧为加上图号“XM-”并去掉“-Model”后的文件名。

excel开发工具导入功能(Excel在设计工作中的应用)(12)

七、 解复杂的一元方程

管道保温计算中,保温厚度的计算公式为:

excel开发工具导入功能(Excel在设计工作中的应用)(13)

式中D1为需要求解的未知数。按正常思路,我们应解此方程式,求出D1的表达式。好在Excel提供了解一元方程的工具:单变量求解。具体过程如下:

1.分别在两个单元格中列出方程左边和右边的计算式。

2.在新的单元格中计算左边减右边的值,作为单变量求解的目标值。

3. 使用单变量求解,目标值为0,D1所在的单元格为可变单元格。

4.点击确定,得出满足方程要求的D1值。

将单变量求解的过程录制为宏,并加上按钮,即可一键求解此方程。

excel开发工具导入功能(Excel在设计工作中的应用)(14)

八、 变换已知条件

设计旋梯时,有时我们需要根据旋梯设计倾斜角计算旋梯的螺旋角,有时需要螺旋角反算旋梯的倾斜角。这种计算表格如何编制?设计成两个计算表格当然简单,但是使用不方便。怎样在一个计算表中实现这个功能呢?

1. 按旋梯设计倾斜角为已知条件,旋梯的螺旋角由旋梯设计倾斜角计算得到。

2. 编写VBA程序,使用单变量求解,旋梯的螺旋角作为目标值,旋梯设计倾斜角为可变量。程序如下:

Sub RndStair() '单变量求解(解方程)

Dim GoalVal, ChangVal As Integer, GoalAdd, ChangAdd

GoalVal = InputBox("请输入旋梯螺旋角:", "根据螺旋角求倾斜角")

If GoalVal = "" Then Exit Sub

GoalAdd = Cells(ActiveCell.Row, 16).Address

ChangAdd = Cells(ActiveCell.Row, 4).Address

Range(GoalAdd).GoalSeek Goal:=GoalVal, ChangingCell:=Range(ChangAdd)

ChangVal = Cells(ActiveCell.Row, 4)

MsgBox Cells(ActiveCell.Row, 6) & "中间倾斜角为:" & ChangVal

End Sub

3. 利用工作表的SelectionChange事件,当鼠标点到旋梯的螺旋角单元格时触发事件,调用单变量求解程序,在弹出的输入框中输入螺旋角,由程序反算出旋梯倾斜角。

4. 这样,我们输入旋梯设计倾斜角即得到旋梯的螺旋角。反之,鼠标点到旋梯的螺旋角单元格即可输入螺旋角,得到旋梯倾斜角。

excel开发工具导入功能(Excel在设计工作中的应用)(15)

九、 离散数据公式化

excel开发工具导入功能(Excel在设计工作中的应用)(16)

第一张图为空气绝对温度与质量比热容的关系列表,要据此推导两者之间的关系式,以便计算任意温度下的质量比热容。

如果x和y值大致呈线性关系,可以拟合为线性方程Y=ax b的形式。式中斜率a用intercept函数计算,斜率b用slope函数计算。

如果拟合为线性方程不满足精度要求,可以拟合为二次曲线Y=m1x m2x2 b或更高次曲线Y=m1x m2x2 m3x3 b等。各系数用LINEST数组公式计算。

第二张图为分别按直线、二次曲线、三次曲线拟合得到的方程。方程次数越高、有效位数越多,误差越小。实际应用时可根据需要的精度选用。

以上介绍了在设计工作中利用Excel编写设计工具的技巧。灵活运用这些技巧,可以让我们编写的工具功能更“强大”,提高我们的工作效率。

如果我们编写的工具还想给别人使用,我们还要注意小工具的易用性,比如:

1. 区分输入区、非输入区、结果区;

2. 输入、输出区集中,适当隐藏中间过程;

3. 对关键公式进行保护,防止篡改;

4. 输入时给出适当的提示;

5. 限制输入的内容,给出警告或禁止输入非法值,避免计算出错;

6. 提供下拉列表,方便使用,避免输入非法值;

下图为矩形烟道计算工具的条件输入界面。

excel开发工具导入功能(Excel在设计工作中的应用)(17)

灰色部分为非输入区;白色部分为输入区;蓝色部分可以输入,也可接受本工具的计算结果。

雪压、风压可根据输入的地名自动查询出数值,也可不输入地名,直接输入数值。

积灰高度数值利用批注或数据有效性给出提示。

“是否离心风机出口”栏使用下拉列表提供0和1两个选项,避免输入错误。

下图为矩形烟道强度计算过程页面。

excel开发工具导入功能(Excel在设计工作中的应用)(18)

宽、高尺寸加入纠错设置,输入尺寸时既可以输入米数,也可以输入毫米数。

板厚提供下拉列表框,列出常用厚度值,也可输入其它值。

针对不同的设计方法,提供相应的按钮,简单操作。

隐藏中间计算过程,只显示输入和输出数据,界面简洁,使用方便。

,