自项目详细设计伊始确定管道等级，至项目末期施工单线图管道属性填写，管道表贯穿整个项目周期，且需要工艺专业和管道专业的不断修改完善。

一、计算方法简述

注：本文所述计算均以此表为例，每列所表示的条件与样表一致，文末附下载链接。

工艺专业给定条件：

管道编号、介质名称、管道起止点、公称直径、操作条件（操作温度、操作压力）、隔热类别、蒸汽吹扫、PID图号、备注条件（最高操作压力、最高操作温度、硫化氢含量、氢气含量）

管道专业需完善条件：

管道等级、设计条件（设计温度、设计压力）、试验压力（水压试验压力、气密试验压力）、隔热厚度、管道检验级别

1、设计温度T

最高工作温度（Tw）℃	设计温度(T) ℃
-20<Tw≤15	T=Tw-5(最低取-20）
15<Tw≤350	T=Tw+20
Tw>350	T=Tw+(5～15）

特殊说明：
当管道最高操作温度大于计算设计温度时，设计温度取管道最高操作温度。

2、设计压力P

工作压力(Pw) MPa	设计压力(P) MPa
Pw≤1.8	P=PW+0.18
1.8<Pw≤4.0	P=1.1Pw
4.0<Pw≤8.0	P=Pw+0.4
Pw>8.0	P=1.05Pw

特殊说明：
a.当管道最高操作压力大于计算设计压力时，设计压力取管道最高操作压力；
b.真空系统管道设计压力取0.1MPa；
c.氮气、净化风、非净化风、循环冷水、新鲜水管道，设计压力等于操作压力。

3、水压试验压力Ph

Ph=1.5*P*[σ]／[σ]t

[σ]／[σ]t   -管道设计温度高于试验温度(20度)时，试验温度下与设计温度下材料的许用应力比值

许用应力系数确定，根据管道等级及管径确定材料和壁厚，将管道温度填入下表，得到相应的许用应力系数，再返回填入管道表的公式计算中。

4、气密试验压力Pg

Pg=P

5、隔热厚度

根据管径、设计温度由下表查得。

6、管道检验级别

根据介质、设计温度、设计压力由下表查得。

二、常规做法存在问题

1.设计温度计算：公式为=I7+20；

遇Tw>350，手动修改公式=I7+10；

遇最大操作温度大于计算设计温度，手动修改。

2.设计压力计算： 公式=IF(J7<=1.8,J7+0.18,IF(AND(J7>1.8,J7<=4), J7*1.1,IF(AND(J7>4,J7<=8),J7+0.4,IF(8<J7,J7*1.05))))；

遇最大操作压力大于计算设计压力，手动修改；

遇真空系统，手动修改；

遇设计压力等于操作压力的管道，手动修改。

3.水压试验压力计算：公式=L7*1.5；

为简化计算，温度大于100度时，查表得许用应力系数，手动修改公式=L7*1.5*系数。

4.隔热厚度及管道检验级别均需手动查表确定。

5.共性格式问题：当存在两种操作条件时，通常用“/”分隔写在同一单元格内，此时对应操作条件及水压试验压力均需手动计算输入。

6.一旦上游专业修改操作条件，以上需要手动计算输入的地方则需再次计算输入。

由此可见，工作流程存在大量重复性工作。

三、利用公式简化计算

1、设计温度

=MAX(IF(AND(I7>15,I7<=350),I7+20,IF(350<I7,I7+10)),V7)

根据项目实际情况，15℃以下暂不考虑，350℃以上，温度余量取10。

IF函数容易理解，将条件即对应返回结果逐层写入即可；并最后用MAX函数取得计算设计温度和最大操作温度的最大值，即为结果。设计压力同理。

2、设计压力

=IF(COUNT(FIND({"NG";"IA";"PA";"CW";"FW"},B7))>0,J7, MAX(IF(J7<=0,0.1,IF(AND(J7>0,J7<=1.8),J7+0.18, IF(AND(J7>1.8,J7<=4),J7*1.1,IF(AND(J7>4,J7<=8),J7+0.4, IF(8<=J7,J7*1.05))))),U7))

补充真空系统、最大操作压力、与操作压力相同情况。
COUNT函数和FIND函数在后文解释。

3、隔热厚度

=IF(OR(O7="PP",O7="ST"),30,IF(O7="H", INDEX('（保温厚度表-华北'!$C$3:$J$23, MATCH(G7,'保温厚度表-华北'!$B$3:$B$23,0),MATCH(K7,'保温厚度表-华北'!$C$2:$J$2,-1)),""))

1) 用IF函数区分三种情况：

隔热要求（条件）	公式表述（条件）	返回结果
防烫（PP）或伴热（ST）	OR(O7="PP",O7="ST")	30
保温（H）	O7="H"	INDEX公式查表
无	-	“”（空值）

将原有厚度表整理，管壳（DN≤300）和卷毯（DN> 300）两张表合二为一，温度按降序排列，使得实现查找大于等于设计温度的最大值的对应列即为目标厚度所在列。

2)INDEX查表

MATCH(G7,'保温厚度表-华北'!$B$3:$B$23,0)

-查找相等管径确定行数

MATCH(K7,'保温厚度表-华北'!$C$2:$J$2,-1)

-查找大于等于设计温度的最大值，确定列数

行数列数确定，INDEX返回对应保温厚度

注：$C$3中的$代表绝对引用，复制公式时地址不会跟着变化。

4.水压试验压力

水压试验压力计算问题关键点在于许用应力系数的确定。

许用应力系数决定因素：温度、材料、壁厚（材料、壁厚又与管道等级和管径对应）。

最初，在原有许用应力表基础上进行改进，因管道表所用温度仅精确到个位，将20～575°C温度逐一列出，完善各材料在每个温度下的许用应力值（内差法），而对材料及壁厚的选择，想以管道等级和管径为条件用IF函数实现。

但最终考虑到，管道等级众多，且各等级对应壁厚无明显规律，用IF函数实现，条件过多，嵌套层数过多，且容易存在漏项。

再次修改许用应力表，完善为等级-管径-温度-许用应力系数对照表，自此，许用应力系数表条件与管道表中给定条件一一对应，许用应力系数完全利用查表实现。

但与保温厚度查表实现不同的是，许用应力系数由三个条件确定，温度匹配确定行数，可以继续MATCH函数实现；但要同时满足等级、管径匹配两个条件确定列数，因match函数为按顺序查找仅能确定满足条件的第一个值的位置，不能查找出所有等级匹配的列再去筛选管径匹配的单元格，所以目标列的查找用MATCH函数实现是有难度的。

所以引入SUMPRODUCT函数，SUMPRODUCT函数功能强大，可实现多条件的计数求和等。以下仅说明本次用法。

=L7*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=H7)*('许用应力'!$B$3:$BU$3>=G7)*('许用应力'!$B$5:$BU$5<=G7)*('许用应力'!$A$6:$A$586=K7)*('许用应力'!$B$6:$BU$586)）

判断工作表“许用应力”中，

数组$B$2:$BU$2（管道等级集合），是否等于目标管道等级；

数组$A$6:$A$586（管道温度集合），是否等于目标管道温度；

数组$B$3:$BU$3（管径上限集合），是否大于等于管径；

数组$B$5:$BU$5（管径下限集合），是否小于等于确定管径范围下限；

是返回TRUE，否返回FALSE，判断后分别返回逻辑值组。

再与$B$6:$BU$586的值对应相乘，求和得到结果（因表中每组条件可以确定唯一的单元格，所以求和结果即为目标许用应力系数）。

注：当忽略求和区域非数字单元格时，将求和区域前的“*”改为“，”即可。

当然，用SUMPRODUCT函数也可以实现多条件的列位置返回，如下公式：

=SUMPRODUCT(('许用应力'!$B$2:$BU$2=H7)*('许用应力'!$B$3:$BU$3>=G7)*('许用应力'!$B$5:$BU$5<=G7) *COLUMN('许用应力'!$B$6:$BU$586))

但在此例中，若用MATCH函数返回行位置，SUMPRODUCT函数返回列位置，再用INDEX引用，显得多此一举，直接用SUMPRODUCT进行条件查找计数更为便捷。

5.管道检验级别

将管道检验级别对照表进行整理，按介质分类，以温度和压力为查找条件，温度、压力均按降序排列，使得每个区间仅包含范围下限。

1）工艺介质

因工艺介质繁多，且不能根据管道表确定介质，所以介质不作为查找条件。

管径小于50的管道不属于压力管道，只需返回检验等级，无需返回压力管道级别。

因此，对丙类介质而言，引用区域为'检验级别表'!$S$7:$T$10，用Match函数查找区域内对应的压力和温度，确定检验级别。

当管径小于50时，仅需用LEFT函数引用目标单元格内前四位字符即可，如下：

=IF(G7>=50,INDEX('检验级别表'!$S$7:$T$10,MATCH(L7,'检验级别表'!$A$7:$A$10,1),MATCH(K7,'检验级别表'!$S$5:$T$5,1)),LEFT(INDEX('检验级别表'!$S$7:$T$10,MATCH(L7,'检验级别表'!$A$7:$A$10,1),MATCH(K7,'检验级别表'!$S$5:$T$5,1)),4))

2）公用工程

公用工程管道种类相对固定，且介质无毒害，每种介质对应管道号中的介质代号。

引用区域即为'(管道用）检验级别表'!$U$7:$W$10。

特殊情况说明：

a.净化风（IA）管道检验级别确定为SHC4；

AND(LEFT(B17,2)="IA",G17<150),"SHC4"

AND(LEFT(B17,2)="IA",G17>=150),"SHC4/GC3"

b.温度低于标准沸点的液体不是压力管道，即"DSW"、"DOW"、"FW"、"CW"、"HW"管道温度低于100度时；

AND(COUNT(FIND({"DSW";"DOW";"FW";"CW";"HW"},B17))>0,K17<100)

{"DSW";"DOW";"FW";"CW";"HW"}是数组形式，FIND的查找结果也是数组形式，所以用COUNT统计查找结果中数字的个数，若数字大于0，则是目标管道包含在这五种管道之中

c.设计压力小于1.6MPa，且管径小于150mm的气体管道不是压力管道，符合该条件的"NG"、"PA"、"IA"管道；

AND(COUNT(FIND({"NG";"PA"},B371))>0,G17<150,L17<1.6)

d.其他管径小于50mm的管道不是压力管道；

G17<50

情况a单独作为IF的两个条件，分别返回结果

情况b～d三者满足其中一个条件，只返回检验级别，用OR函数连接三种情况，作为IF的第三个条件，公式如下：

（AND函数表示“与”关系，OR函数表示“或”关系）

=IF(AND(LEFT(B17,2)="IA",G17<150),"SHC4", IF(AND(LEFT(B17,2)="IA",G17>=150),"SHC4/GC3", IF(OR(AND(COUNT(FIND({"NG";"PA"},B371))>0,G17<150,L17<1.6), AND(COUNT(FIND({"DSW";"DOW";"FW";"CW";"HW"},B17))>0,K17<100), G17<50),LEFT(INDEX('检验级别表'!$U$7:$W$10,MATCH(L17,'检验级别表'!$A$7:$A$10,1),MATCH(K17,'检验级别表'!$U$5:$W$5,1)),4),
INDEX('检验级别表'!$U$7:$W$10,MATCH(L17,'检验级别表'!$A$7:$A$10,1), MATCH(K17,'检验级别表'!$U$5:$W$5,1)))))

四、对不同格式数据公式的改进

那么对于一个单元格中存在两种条件的情况，思路是

数据分离 --> 分别计算结果 --> 结果写入同一单元格

假设J8单元格中数据为18.2/0.25，先要将18.2和0.25提取出来分别计算，当然可以用LEFT(J8,4)=18.2，RIGHT(J8,4)=0.25，但应用中，数据格式并非都是四位字符，那么为了通用我们依靠MID函数和FIND函数的配合使用分离数据。

“/”为是两个条件分隔的标志，那么我们首先找到“/”的位置，就可以确定第一个数据要截取到第几位，和第二个数据应该从第几位截取。

18.2 = --MID(J8,1,FIND("/",J8,1)-1)

FIND("/",J8,1) 是“/”的位置号，用MID函数从第一位截取到“/”前一位即可提取第一个数据；

0.25 = --MID(J8,FIND("/",J8,1)+1,6)

同理，从“/”后一位开始，提取6位（所用数据最多六位），得到第二个数据；

注意，直接用MID函数得到的结果是字符串形式，并非数字， “--”可将字符串转为数字格式，才可以正常参与数值的比较和计算。

将以上分离出的数据分别代入前面所述各基本公式中计算，两个数据的计算公式用&"/"&连接，即可实现两个结果用“/”分隔写进一个单元格。

针对不同格式数据，对前述公式改进举例如下：

1.设计压力

=MAX(IF(--MID(J8,1,FIND("/",J8,1)-1)<=0,0.1,IF(AND(--MID(J8,1,FIND("/",8,1)-1)>0,--MID(J8,1,FIND("/",J8,1)-1)<=1.8),--MID(J8,1,FIND("/",J8,1)-1)+0.18,IF(AND(--MID(J8,1,FIND("/",J8,1)-1)>1.8,--MID(J8,1,FIND("/",J8,1)-1)<=4),--MID(J8,1,FIND("/",J8,1)-1)*1.1,IF(AND(--MID(J8,1,FIND("/",J8,1)-1)>4,--MID(J8,1,FIND("/",J8,1)-1)<=8),--MID(J8,1,FIND("/",J8,1)-1)+0.4,IF(8<=--MID(J8,1,FIND("/",J8,1)-1),--MID(J8,1,FIND("/",J8,1)-1)*1.05))))),U8)&"/"&IF(--MID(J8,FIND("/",J8,1)+1,6)<=0,0.1,IF(AND(--MID(J8,FIND("/",J8,1)+1,6)>0,--MID(J8,FIND("/",J8,1)+1,6)<=1.8),--MID(J8,FIND("/",J8,1)+1,6)+0.18,IF(AND(--MID(J8,FIND("/",J8,1)+1,6)>1.8,--MID(J8,FIND("/",J8,1)+1,6)<=4),--MID(J8,FIND("/",J8,1)+1,6)*1.1,IF(AND(--MID(J8,FIND("/",J8,1)+1,6)>4,--MID(J8,FIND("/",J8,1)+1,6)<=8),--MID(J8,FIND("/",J8,1)+1,6)+0.4,IF(8<=--MID(J8,FIND("/",J8,1)+1,6),--MID(J8,FIND("/",J8,1)+1,6)*1.05)))))

2.水压试验压力

因水压试验压力计算结果小数位可能会很多，单元格格式设置又不能对用“／”分开的两组数据分别进行位数保留，所以在计算后再套用ROUND函数仅保留两位小数。

1）一个等级，两个压力

=ROUND(--MID(L10,1,FIND("/",L10,1)-1)*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=H10)*('许用应力'!$B$3:$BU$3>=G10)*('许用应力'!$B$5:$BU$5<=G10)*('许用应力'!$A$6:$A$586=K10)*('许用应力'!$B$6:$BU$586)),2)&"/"&ROUND(--MID(L10,FIND("/",L10,1)+1,6)*1.5 *SUMPRODUCT(('许用应力'!$B$2:$BU$2=H10)*('许用应力'!$B$3:$BU$3>=G10) *('许用应力'!$B$5:$BU$5<=G10)*('许用应力'!$A$6:$A$586=K10)*('许用应力'!$B$6:$BU$586)),2)

2)两个等级，一个压力

两个等级分别为MID(H9,1,FIND("/",H9,1)-1) 和 MID(H9,FIND("/",H9,1)+1,6)

先判断许用应力系数是否相等，如果相等，只返回一个值。

=IF(SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H9,1,FIND("/",H9,1)-1))*('许用应力'!$B$3:$BU$3>=G9)*('许用应力'!$B$5:$BU$5<=G9)*('许用应力'!$A$6:$A$586=K9)*('许用应力'!$B$6:$BU$586))=SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H9,FIND("/",H9,1)+1,6))*('许用应力'!$B$3:$BU$3>=G9)*('许用应力'!$B$5:$BU$5<=G9)*('许用应力'!$A$6:$A$586=K9)*('许用应力'!$B$6:$BU$586)),ROUND(L9*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H9,1,FIND("/",H9,1)-1))*('许用应力'!$B$3:$BU$3>=G9)*('许用应力'!$B$5:$BU$5<=G9)*('许用应力'!$A$6:$A$586=K9)*('许用应力'!$B$6:$BU$586)),2),ROUND(L9*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H9,1,FIND("/",H9,1)-1))*('许用应力'!$B$3:$BU$3>=G9)*('许用应力'!$B$5:$BU$5<=G9)*('许用应力'!$A$6:$A$586=K9)*('许用应力'!$B$6:$BU$586)),2)&"/"&ROUND(L9*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H9,FIND("/",H9,1)+1,6))*('许用应力'!$B$3:$BU$3>=G9)*('许用应力'!$B$5:$BU$5<=G9)*('许用应力'!$A$6:$A$586=K9)*('许用应力'!$B$6:$BU$586)),2))

3)两个等级，两个压力

=ROUND(--MID(L8,1,FIND("/",L8,1)-1)*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H8,1,FIND("/",H8,1)-1))*('许用应力'!$B$3:$BU$3>=G8)*('许用应力'!$B$5:$BU$5<=G8)*('许用应力'!$A$6:$A$586=K8)*('许用应力'!$B$6:$BU$586)),2)&"/"&ROUND(--MID(L8,FIND("/",L8,1)+1,6)*1.5*SUMPRODUCT(('许用应力'!$B$2:$BU$2=MID(H8,FIND("/",H8,1)+1,6))*('许用应力'!$B$3:$BU$3>=G8)*('许用应力'!$B$5:$BU$5<=G8)*('许用应力'!$A$6:$A$586=K8)*('许用应力'!$B$6:$BU$586)),2)

3.检验等级

两个压力条件时，先判断两个条件下查得的检验等级是否相同；

相同返回一个值，不同返回两个值；

返回值的设定，分别再用IF函数讨论，管径大于等于50，返回目标单元格值，否则返回目标单元格前四位。

=IF(INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,1,FIND("/",L8,1)-1),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1))=INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,FIND("/",L8,1)+1,6),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1)),IF(G8>=50,INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,1,FIND("/",L8,1)-1),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1)),LEFT(INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,1,FIND("/",L8,1)-1)),'检验级别表'!$A$7:$A$10,1),MATCH(K8,检验级别表!$S$5:$T$5,1)),4)),IF(G8>=50,INDEX(检验级别表!$S$7:$T$10,MATCH(--MID(L8,1,FIND("/",L8,1)-1),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1))&"/"&INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,FIND("/",L8,1)+1,6)'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1)),LEFT(INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,1,FIND("/",L8,1)-1),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1)),4)&"/"&LEFT(INDEX('检验级别表'!$S$7:$T$10,MATCH(--MID(L8,FIND("/",L8,1)+1,6),'检验级别表'!$A$7:$A$10,1),MATCH(K8,'检验级别表'!$S$5:$T$5,1)),4)))

五、数据筛选功能的应用

那么针对不同格式的数据，要填入不同的公式，如果用眼睛判断格式依然不够简便。

用数据筛选功能将会事半功倍，以最繁琐的水压试验压力计算为例，

筛选管道等级、设计压力两栏数据，

a.管道等级-不包含“/”，设计压力-不包含“/”；

b.管道等级-不包含“/”，设计压力-包含“/”；

c.管道等级-包含“/”，设计压力-不包含“/”；

d.管道等级-包含“/”，设计压力-包含“/”；

筛选后，填入当前格式数据对应的公式，仅需四步计算瞬间完成。

六、尾声

改进后，除工艺介质管道检验级别填写还需参照介质情况，修改公式查找范围外，其他条件均可实现自动计算；并且一旦上游专业修改给定条件，对应项可自动修改；节省工作量，比手动输入准确定更高，今后对不同项目按实际需要再适当修改即可。

对EXCEL了解甚少，这次改进不断思考简化每种查找或计算的逻辑关系，查找EXCEL功能如何实现，再尽可能的精简公式。水平有限，若有更好的方法还请多指教。

附件上传公式样表，各取所需。

https://static.ranshy.com/ranran/excel/EXAMPLE.xls