Excel-表格的合并单元格排序

现有工艺专业给出的管道特性表，局部示例如下图，行列都包含合并单元格，需要按照默认顺序进行排序，但是表格的排序功能根本无法使用。

大概描述一下表格的复杂程度：

1、没有序号列。

2、A列“管段号”为空或者标注“(冲洗工况)”，表示默认与上一个单元格的管段号相同，这种空单元格，可能有一个，也可能有连续两个。

3、OPQ列“防腐材料”是行列皆为合并单元格。

4、W列“管道级别及压力管道级别”是唯一未被合并单元格的列，但是内容顺序不统一，包含了两种：压力管道分级和石化管道分级。

5、中间有随机分布的空行。

6、可能存在整行总体重复的内容。

7、A列“管段号”存在部分数据并未合并单元格。

8、同时存在“(冲洗工况)”和“（冲洗工况）”两种写法。

9、S列“试验压力MPaG”的数值，是公式计算值，通过单元格格式定义的显示为保留小数点后两位，而非彻底保留小数点后两位。

10、最奇葩的，合并单元格的空白格竟然也有数据，由一种特殊操作实现，钻了漏洞增加麻烦。

一、插件处理

推荐插件：易用宝&方方格子。自行百度访问官网，下载安装其一皆可，也有其他插件，功能暂时不如这俩。

注意：对于禁网系统，比如部分云桌面，只能选择方方格子，因为易用宝免费版需要在线激活，而方方格子不用激活，但方方格子总会跳出更新提示。

如果表格内容和格式规整的话，可以安装选用，操作方便。

但对于当前要处理的这个表格，不可直接使用，需要预先处理好相关数据，否则结果一塌糊涂。

二、公式处理

格式比较复杂，需要逐级处理。

1、虽然公式也能解决同时存在“(冲洗工况)”和“（冲洗工况）”两种写法的问题，但不如先批量替换方便，这里按统一为“（冲洗工况）”。

2、X列加入每两行合并单元格专用的辅助列序号。【可选】

该公式对应的是内容首行为第七行，下拉该公式可用于表达合并单元格的序号：

=(ROW(A7)-1)/2-2

=(ROW()-1)/2-2

=COUNTA(A$7:A7)

4、Z列加入辅助列，用于将A列“管段号”的缺失内容补全。

=IFS(AND(ISBLANK(H7:K8)),"",ISNUMBER(FIND("-",A7)),A7,ISBLANK(A7),LOOKUP(2,1/($A$7:A7<>""),$A$7:A7),A7="（冲洗工况）",A5&A7,TRUE,"")

以上公式涉及到六种情况，依次为：

如果整行空值，输出空值，我这里写”H7:K8“其实和”A7:W8“等效；

查找一个正常的“管段号”有而“（冲洗工况）”没有的字符，如果有字符则判定为“管段号”，直接输出“管段号”；

如果“管段号”这列单元格为空值，输出上一个有值单元格的内容，这个公式因为考虑到存在多个连续空值单元格，所以比较复杂，如果确定最多只有一个空值单元格，那使用“COUNTA(A$7:A7)”的变形公式也可以；

如果是“（冲洗工况）”，则输出上一个有值单元格的内容&“（冲洗工况）”的合并内容，因为确定“（冲洗工况）”不会连续出现，所以公式比较简单；

最后的“TURE”表示任意未被考虑到的情况的合集，输出空值。

下图为XYZ三列。

5、新建空白辅助表，我命名为“奇数单行”，以普通表格奇数单行的型式，将合并单元格所有内容列出。

这里可选两种公式，首先是全兼容的旧版本：

=IF(MOD(ROW(A7),2),'源表'!A7:W7,"")&""

也可以使用新版本的数组函数，放大到1000行，对我够用：

=FILTER(源表!A$7:W$1000,MOD(ROW(源表!A$7:A$1000),2))&""

由于W列“管道级别及压力管道级别”涉及到两行内容都要显示出来，所以在这个新建辅助表的X列增加公式：

=FILTER(源表!W$7:W$1000,NOT(MOD(ROW(源表!A$7:A$1000),2)))&""

意思是将源表中从W7开始的所有偶数行内容依次列出来，正好与本表中左侧A~W列一一对应。

为了处理方便，也将源表中的Z列，列出到本表的Y列。

=FILTER(源表!Z$7:Z$1000,MOD(ROW(源表!A$7:A$1000),2))&""

意思是将源表中从Z7开始的所有奇数行内容依次列出来，也是一一对应。

下图为A~Y列。

6、再新建空白辅助表，我命名为“排序”，之所以没在上一个辅助表同时实现筛选和排序，就是因为W列的特殊情况，不得不如此。

因为需要按照“奇数单行”辅助表的Y列内容进行排序，所以这里选择Sortby而非Sort函数。

=SORTBY(FILTER(奇数单行!A$1:Y$1000&"",奇数单行!Y$1:Y$1000<>""),FILTER(奇数单行!Y$1:Y$1000,奇数单行!Y$1:Y$1000<>""))

下图为A~Y列。

7、最后新建空白辅助表，需要先将源表的格式复制到本表，保留标题行和所有合并单元格的格式，我命名为“成品”。

全选本表的A~V列所有正文内容范围，也就是从A7单元格开始，全选到V列最后一行，然后在顶部输入栏输入公式，并键盘上按Ctrl+Enter：

=INDIRECT("'排序'!"&CHAR(COLUMN()+64)&((ROW($A7)-1)/2-2))&""

如此才能将所有单元格中（合并单元格算为一个独立的单元格）填充入同样的公式内容。

这个公式已经考虑到自动读取实际所在的行和列。

这里不能使用公式拖拽延伸，是因为包含不同格式的合并单元格。

唯一特殊的是S列，需要将公式修改为：

=IFERROR(ROUNDUP(INDIRECT("'排序'!"&CHAR(COLUMN()+64)&((ROW($A7)-1)/2-2)),2),"")

这是为了增加一个数值保留两位小数的额外处理。ROUNDUP表示向上保留，ROUNDDOWN表示向下保留，ROUND表示四舍五入，这里选择ROUNDUP。

继续在W7输入公式并下拉：

=INDIRECT("'排序'!"&IF(ISNUMBER(FIND("SH",INDIRECT("'排序'!"&CHAR(COLUMN()+65)&ROUNDUP(ROW(A7)/2-3,0)))),CHAR(COLUMN()+IF(MOD(ROW(),2)=0,64,65)),CHAR(COLUMN()+IF(MOD(ROW(),2)=0,65,64)))&ROUNDUP(ROW(A7)/2-3,0))&""

该公式将会按照SH分级在奇数行、GC分级在偶数行的原则，将对应的分级内容一一对应列出。

下图为A~W列。

对于最终成品文件，一般建议全选所有正文内容，再鼠标原地右击，选择以值粘贴，消除所有公式。也可以使用VBA，以后再讲。

多数工艺同事都默认不填的单元格就是不用填，而不是使用“-”或者“\”等符号填充，让下游专业还得猜测到底是不填还是漏填。

所以这里再分享一个技巧，消除公式后，全选所有正文内容，键盘按Ctrl+G，调出“定位”，点击“定位条件”，选择“空值”，然后以上文提到的输入栏输入内容并Ctrl+Enter的方式，给所有空值填充指定符号。

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

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

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

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

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