Excel-函数公式-自定义规则排序表格

这种需求现在比较少有，因为多数采用软件报告模板自动生成材料表。

以前PDS时代，不怎么使用综合材料表，更倾向于复核并修正轴测图后，从轴测图更新材料表中提取数据并汇总，然后按自定义规则进行排序。现在也有一些人工复核材料表的场景，可能会用到这个操作。

示例表如下，当然实际类型非常多。

注：本文内容适用于最新版WPS或Office 365，低版本请先更新到新版本，或者阅读下文，解决最新版函数公式无法应用于旧版本的问题。

软件分享-Excel低版本支持高版本函数插件

不想学习函数公式只想直接取用的朋友，可以直接看文末的方法2。

自定义排序的规则实质上就是取短描述的第一个逗号前面的文本，以这个文本作为子类型的关键词，再定义实际所需的顺序，作为排序依据。所以先获取该列表，并去重排序。

在J2单元格输入以下公式，提取第一个逗号前面的文本：

=LEFT(List!I2, FIND(",",List!I2)-1)

回车后，点击单元格右下角的绿色方点，当鼠标变成黑色十字时双击，该操作可以将连续含值行的对应列全部填充公式【示例为J列】，遇到左列【示例为I列】单元格为空值时会终止。

在K2单元格输入以下公式去重并简单排序，这里的排序无实际意义，仅作为参考：

=SORT(UNIQUE(J2:J9999))

第二个数字9999是随便写的，只要大于实际表格正文含值总行数即可，之所以不用J:J，因为 .xls 格式默认最大行数为65,536行， .xlsx 及其他等格式默认最大行数为1,048,576行，设计院中低端配置电脑的小身板扛不住 .xlsx 这种计算量，多次计算会呈指数级，尽量避免。

这样就获得了类似如下这个列表，

实际上管道专业的综合材料表，除去特殊件、管架和绝热涂漆等辅材之外，一般是按照这样的顺序依次显示：“管道”-“管件”-“法兰”-“垫片”-“阀门”-“紧固件”。

针对这个顺序，也就是前缀为“Pipe”的排在第一位【也可以认为是包含，与下述关键词统一格式】；如果包含“Flange”，就排在第三位；如果包含“Gasket”，就排在第四位；如果包含“Valve”，就排在第五位；如果包含“Nut”，就排在第六位；剩余的都排在第二位。由于手动输入数据普遍不严谨的实际现状，这些关键词必须都不区分大小写。

这里再增加一个附属规则，提升复杂程度，要求各种材料的“PE”类型【平端 Plain End】，必须排在该类材料的“BE”类型【坡口端 Beveled End】之前。

当然我也见过更混乱的写法，比如单词有缩写却不是全都取前缀，而是随机抽取部分字母，导致以上逻辑的关键词更加复杂。这种也需要事先提取出所有可能的关键词，再统一处理。

现在开始讲述自定义排序的函数公式。

首先，必然选用SORTBY函数，而不是SORT函数，它的语法结构是：

第一位array表示需要排序的范围，也就是标题表头之下的内容，本文示例对应A2:I9999；

第二位by_array1表示排序依照的范围，也就是自定义排序的规则，本文示例对应的原始值为I2:I9999的短描述，而实际值就是本文核心内容；

第三位sort_order1表示排序的方式，1为升序且可缺省，-1为降序。

其次，需要配合使用MATCH函数。

第一位lookup_value表示需要匹配的范围，本文示例对应所有I2:I9999短描述第一个逗号之前的文本，也就是辅助列的J2:J9999；

第二位lookup_array表示自定义规则，也就是辅助列去重排序后的K2:K99【本文示例不到一百种前缀类型】，但并非简单的默认排序；

第三位match_type表示排序的方式，1为小于且可缺省，0为精确匹配，-1为大于。

也可以选择更高级的XMATCH函数，当前需求其实用不上，它主要是多了两种匹配模式和一种搜索模式。

其他都是一些通用函数，比如FIND和SEARCH函数的区别为是否区分大小写，所以本文示例采用SEARCH函数。

1、不推荐的方法：

依据辅助列去重后的K2:K99列表，人工调整顺序，将其写入成品函数公式。

在单元格L2输入以下公式，将列表按照MATCH函数要求的数组结构合并：

="{"""&TEXTJOIN(""";""",,List!K2:K99)&"""}"

注：函数公式中引用显示的引号，需要输入两次，才能正常表达为一个。

人工排序【以下截图仅供参考，实际上来自于方法二的自动排序】：

将其代入MATCH函数的第二参数，生成一个序号数组，就能使SORTBY的嵌套函数依据这个顺序，将原表格正文内容进行排序。对应函数公式为：

=SORTBY(List!A2:I9999,MATCH(List!J2:J9999,{"Pipe";"45 Degree Elbow";"45 Elbow (R=1.5D)";"45 Elbow (R=1D)";"90 Degree Elbow";"90 Elbow (R=1.5D)";"90 Elbow (R=1D)";"BW Latrolet";"Cap";"Concentric Reducer";"Concentric Swage";"Eccentric Reducer";"Eccentric Swage";"Equal 45 Lateral Tee";"Equal Tee";"Nipple";"Paddle Blank";"Paddle Spacer";"Redu 45 Lateral Tee";"Reducing Tee";"Reinforcing Pad";"Spectacle Blind";"Weldolet";"Blind Flange";"Reducing WN Flange";"Socket weld Flange";"Weld neck Flange";"Spiral Wound Gasket";"Ball valve";"Butterfly valve";"Check valve";"Gate valve";"Globe valve";"Needle valve";"Hex Nuts"},0),1)&""

最后加了个 &“”，是为了将结果全局设置为文本格式，防止末尾行出现0，或者其他显示问题。另外因为SORTBY第三参数如果为1则可缺省，所以也可以写为：

=SORTBY(List!A2:I9999,MATCH(List!J2:J9999,{"Pipe";"45 Degree Elbow";"45 Elbow (R=1.5D)";"45 Elbow (R=1D)";"90 Degree Elbow";"90 Elbow (R=1.5D)";"90 Elbow (R=1D)";"BW Latrolet";"Cap";"Concentric Reducer";"Concentric Swage";"Eccentric Reducer";"Eccentric Swage";"Equal 45 Lateral Tee";"Equal Tee";"Nipple";"Paddle Blank";"Paddle Spacer";"Redu 45 Lateral Tee";"Reducing Tee";"Reinforcing Pad";"Spectacle Blind";"Weldolet";"Blind Flange";"Reducing WN Flange";"Socket weld Flange";"Weld neck Flange";"Spiral Wound Gasket";"Ball valve";"Butterfly valve";"Check valve";"Gate valve";"Globe valve";"Needle valve";"Hex Nuts"},0))&""

实际上这里还差了一步，就是之前提到的附属规则，正常情况下“PE”类型按照自然排序规则，是会排在该类材料的“BE”类型之后的。所以需要针对MATCH函数的数组结果二次处理。

=SORTBY(List!A2:I9999,MATCH(List!J2:J9999,{"Pipe";"45 Degree Elbow";"45 Elbow (R=1.5D)";"45 Elbow (R=1D)";"90 Degree Elbow";"90 Elbow (R=1.5D)";"90 Elbow (R=1D)";"BW Latrolet";"Cap";"Concentric Reducer";"Concentric Swage";"Eccentric Reducer";"Eccentric Swage";"Equal 45 Lateral Tee";"Equal Tee";"Nipple";"Paddle Blank";"Paddle Spacer";"Redu 45 Lateral Tee";"Reducing Tee";"Reinforcing Pad";"Spectacle Blind";"Weldolet";"Blind Flange";"Reducing WN Flange";"Socket weld Flange";"Weld neck Flange";"Spiral Wound Gasket";"Ball valve";"Butterfly valve";"Check valve";"Gate valve";"Globe valve";"Needle valve";"Hex Nuts"},0)-ISNUMBER(SEARCH(", PE, ", List!I2:I9999))/10000)&""

SEARCH函数查找 “, PE, “ ，第三参数缺省的情况下，其结果为查找关键词第一次出现的第一个字符在全部字符串中所处的位数，也可以简写成 ” PE” ，但不可以写成 “PE” ，因为 “PE” 会与 “Pipe” 混淆结果，一般写的越完整就越稳妥。然后嵌套ISNUMBER函数，如果结果为数值则返回TRUE，反之无论什么结果都会返回FALSE，总体来说这个嵌套函数就相当于确认字符串是否包含关键词。而函数公式中，默认将TRUE默认为1，将FALSE默认为0，可以直接参与数学运算。至于末尾的 /10000 则是考虑到表格含值范围的总行数，然后取整选用的，就和前文的9999一样，并非定值，但一定要大于等于数据处理范围的任意子类型的总行数。它可以生成一组小于1的自然排序数组，加入复合运算后，只会影响局部排序，不会影响整体排序。

注：{A;B;C;…;Z}结构表达了数组，其实就相当于人工排序后的J2:J9999，这个写法必须将全部内容直接写入数组函数公式，不可以通过单元格间接调用，因为调用后就只是一段文本了。

之所以不推荐这种方法，就是因为一次性这么干还好，如果类型频繁变动，属实是在难为自己了。

2、推荐的方法：

这里着重介绍一下LET函数，它可以用于所有复杂的函数公式，尤其是涉及到外部链接时，避免同一个过程变量多次出现导致函数公式的字段过长。

截图这个语法结构可能不太直观，实际上可以理解为：

变量1名称,变量1内容,变量2名称,变量2内容,变量3名称,变量3内容,...,最终运算

那么成品的函数公式可以有结构更加清晰，理解更加透彻的写法，就像写代码一样，先定义变量，再使用变量，获得最终结果。

严格按照上述需求编写函数公式，并且一步到位，无需前文的辅助列：

=LET(    AllForm,List!A2:I9999,    Description,List!I2:I9999,    AllList,LEFT(Description,FIND(",",Description)-1),    OrderList,SORT(UNIQUE(AllList)),    SimpleOrder,MATCH(AllList,OrderList,0),    TagPipe,ISNUMBER(SEARCH("pipe",Description)),    TagFlange,ISNUMBER(SEARCH("flange",Description)),    TagGasket,ISNUMBER(SEARCH("gasket",Description)),    TagValve,ISNUMBER(SEARCH("valve",Description)),    TagNut,ISNUMBER(SEARCH("nut",Description)),    TagPE,ISNUMBER(SEARCH(", PE,",Description)),    TagComponent,TagPipe+TagFlange+TagGasket+TagValve+TagNut=0,    Weight,10000,    MultiOrder,SimpleOrder+1*Weight*TagPipe+2*Weight*TagComponent+3*Weight*TagFlange+4*Weight*TagGasket+5*Weight*TagValve+6*Weight*TagNut-TagPE/Weight,    SORTBY(AllForm,MultiOrder,1))&""

TagComponent这行使用了

TagPipe+TagFlange+TagGasket+TagValve+TagNut=0

实际上它全写应为

if(TagPipe+TagFlange+TagGasket+TagValve+TagNut=0,TRUE,FALSE)

或者

if(TagPipe+TagFlange+TagGasket+TagValve+TagNut=0,1,0)

但是巧了这些函数公式写法的结果一致，所以简写了，其实还可以写成

NOT(TagPipe+TagFlange+TagGasket+TagValve+TagNut<>0)

MultiOrder这行使用了

SimpleOrder+1*Weight*TagPipe+2*Weight*TagComponent+3*Weight*TagFlange+4*Weight*TagGasket+5*Weight*TagValve+6*Weight*TagNut-TagPE/Weight

数学逻辑就是利用 Weight 这个权重系数，按顺序倍数进行运算，保证需要的子类型序列必然在前一个子类型序列之后和后一个子类型序列之前，再结合 TagPE 这个参数进行复合运算，得到最终排序结果。

这个公式看起来就非常规整了，而且对于不懂函数公式的人也很友好，除了可能需要定义各个子类型的规则以外【多数设计院估计不需要修改】，实际必须适配调整的只有 AllForm 、 Description 、 Weight 这三个参数，相信主动点进本文的朋友很少有不会改的吧。

如果还需要加入特殊件、管架和绝热涂漆等辅材的材料排序，其实也不困难，只要理解逻辑，LET的变量参数可以继续添加。

最后，编写不易，四连随你。

本公众号会尽力确保发布内容的准确性和完整性，但无法保障所有信息的及时性或者可靠性。分享的各种信息只是提供参考，不能当作专业的技术指导，是否有效还请自行判断。

本公众号会不定期分享一些资源链接，只是为了提供工作或生活的便利，各位读者使用这些资源的时候请自行承担风险，建议搭配沙盒软件或虚拟系统运行。如果因此导致各位读者的数据和隐私安全损害，本公众号无力负责。

本公众号发布的文字、图片、音乐、视频等素材，部分来源于互联网，在此尊重所有原创作者的合理权益，默认会鸣谢作者或提供原始链接，如有疏漏存在侵权请联系我删除。

本公众号保留随时对发布内容进行修改、删除或者更新的权利。

感谢各位读者的阅读、支持与理解，如果符合您的需求或爱好，烦请关注并「设为星标」。