合并单元格公式集锦

流浪铁匠

序言:                                               
学习Excel函数这么久，合并单元格在Excel的反人类设计里面绝对能排在前三名……                                               
但是，无数的新手还是喜欢使用合并单元格,这也是这篇笔记的原因                                               
在看这篇笔记前，建议新手先看下小妖的"改变十大旧习惯"相关视频                                               
妖姐已经列举了合并单元格的种种罪状,合并单元格的罪行可谓罄竹难书!                                               
当然，学习涉及合并单元格的函数公式对于开拓函数思路很有帮助                                               
在工作中，数据列表一定不要出现合并单元格，否则对数据的统计会造成很大困难，下面的很多就是案例

流浪铁匠

合并单元格的公式，主要分2类
1,在合并单元格中录入公式
2,对数据源存在合并单元格结构的数据进行统计

以下对2类公式均有涉及

1，涉及合并单元格的简单统计/合并单元格中录入公式的方式
1.1，序号类
1.2，求和
1.3，计数(合并单元格对应内容数)
1.4，计行数(合并单元格对应行数，若存在空单元格时与1.3计数结果有差别)
1.5，求平均值

以下公式除1.5的解法3外，均需选取整个区域后编写公式并使用ctrl+enter批量填充公式完成                                                                                       
1.1：序号        =MAX(D$18:D18)+1                        补1.1 b序号:     =IF(A19="",K18+1,1)                                       
或=COUNTA(D$18:D18)或
=(A19="")*N(K18)+1                                       
1.2：求和=SUM(C19:C$33)-SUM(E20:E$33)                                                                        
1.3：计数=COUNTA(B19:B$33)-SUM(F20:F$33)                                                                        
1.4：计区域行数=ROWS(B19:B$33)-SUM(F20:F$33)  
当(年份)字段有空值时，1.3和1.4的结果是有差异的                                       
=FREQUENCY(-ROW(19:33),-ROW(19:33)*(A19:A33<>""))  frequency对各合并单元格的区域行数的内存数组思路,缺点是由于fre特性,内存数组区域会多1行                                       
1.5：平均值        合并单元格的求平均值是相对麻烦的公式                                                                        
解法1        =AVERAGE(OFFSET(C19,,,MATCH("*",A20:A33,0)))  缺点a34需要输入一个任意文本避免最后一个公式报错                                
解法2        =AVERAGE(OFFSET(C19,,,MATCH(FALSE,IF(ROW($A20:$XFC$34)=ROW(I$34),FALSE,ISBLANK(I20:I$34)),)))                                            
红色部分为定义名称(quyu)，因合并单元格不能直接输入数组公式,且这个公式的思路涉及合并单元格结构(公式只在每个合并单元格区域的第1行)         解法3        =IF(A19<>"",AVERAGE(OFFSET(C19,,,MATCH(FALSE,IF(ROW(20:$34)=ROW(I$34),FALSE,LEN(J20:J$34)=0),))),"")                                     解法3是在解法2基础上自己修改的，不需要定义名称，但需要先取消合并后输入数组公式下拉后用格式刷假合并(请参考合并单元格技巧篇)，后来知道了match的思路后有很简洁的思路                                                                        
解法4       =MMULT(N(LOOKUP(COLUMN(A:O),ROW($1:$15)/(A$19:A$33>0),A$19:A$33)=A19),--C$19:C$33)/SUMPRODUCT((B$19:B$33<>"")*((LOOKUP(ROW($1:$15),ROW($1:$15)/(A$19:A$33>""),A$19:A$33)=A19)))             解法4是支持数组运算的函数集合下的一个极端写法，可以学习思路并在一些情况下衍生用法                                                                
解法5 =AVERAGE(OFFSET(C19,,,IFERROR(MATCH("*",A20:A$33,),ROW(A19)-ROWS(A19:A$33)-1)))                                             
match错位法，在合并单元格本身结果下很实用的思路        

流浪铁匠

综述上面的公式
对合并单元格本身结构的统计公式
一般有:
错位相减
match错位法
支持数组运算的函数(lookup/mmult/sumproduct等)配合使用的直接统计法
等等
错位法有个隐患
如果由于合并单元格报表下方有其他数据，最后一行需要锁定时
合并单元格结构的最下方为只有1个单元格时，会出现循环引用错误

流浪铁匠

2，涉及合并单元格的查找与统计公式/数据源存在合并单元格时的公式思路



以下问题数据源均为真合并，部分内容实际工作中为大幅简化函数公式强烈建议使用格式刷假合并(请参考技巧篇)

这类问题的核心是由于合并单元格数据结构的特殊性(每个合并区域只有第1个单元格有数据)
所以关键就是在于产生填充合并单元格内容的内存数组
这个目前常见的就是使用lookup和多维的方式


最早学会的填充合并单元格套路为
=LOOKUP(ROW(A84:A95),IF(A84:A95<>"",ROW(A84:A95)),A84:A95)
这个思路有很多变种，因为lookup可以忽略错误值
=LOOKUP(ROW(1:12),ROW(1:12)/(A84:A95>""),A84:A95)
=LOOKUP(ROW(84:95)-83,MATCH(A84:A95,A84:A95,),A84:A95)
=LOOKUP(ROW(84:95),MATCH(A84:A95,A84:A95,)^0*ROW(84:95),A84:A95)

=SUBTOTAL(3,OFFSET(A84,,,ROW(84:95)-83))
=COUNTIF(OFFSET(A84,,,ROW(84:95)-83),"<>")

lookup的几种思路均是产生填充原内容的内存数组
后面2个是产生递增序号的内存数组，一般与权重法结合使用
当合并单元格内的内容字符数一致时，填充内容的内存数组还可以使用phonetic+offset等取巧解决(请参考2.3.3)

流浪铁匠

综合以上
对应各种合并单元格问题
就可以借助多维/lookup填充内存数组等等方式来实现了

譬如极值问题
规则数据直接使用subtotal的多维获取每个区域的最大值即可
不规则时也可以考虑权重排序思路

流浪铁匠

2.3   涉及合并单元格的查找与统计
数据源无重复时
借助lookup特性+match/offset定位区域即可实现
实现的方式比较多


但数据存在不规则重复时,上述方法可能出错,稳妥的方式就是填充合并单元格内存数组后进行多条件查找

流浪铁匠

2.4 合并单元格排名公式
合并单元格的汇总排名
这个的难度和合并单元格区域的求和/计数不是一个级别的
希望例举的几个例子和公式对大家有参考性

流浪铁匠

3，aggregate函数在合并单元格统计中的一点发现
首先要介绍下这个函数,aggregate是2010新增的一个很有意思的函数
在探索合并单元格相关问题时我发现了这个函数的一个特性：
在1参为14-19时这个函数的3参是支持数组的，这个不少同学应该知道
但是，此时在类似lookup的数组写法时，aggregate这个函数也是可以不用三键的但支持内存数组运算(仅限1参为14-19)而且可以忽略错误值的(2参用6)


此时对合并单元格相关问题的统计，又有了新的出路

上面这个问题只是列举aggregate的作用，无其他限制条件下，取各合并区域最小值使用match错位思路会更简洁，但若有其他限制时aggregate或许可以新增一条出路

流浪铁匠

写到这里
这篇文章想分享的内容已经差不多了
另外说一个对新手可能会遭遇的问题“
如何在合并单元格中使用数组三键(数组公式):
1,使用本身支持数组运算的函数来避免使用三键;
2,借助定义名称;
3,取消合并三键录入公式再设置合并;
4,取消合并后录入公式后再用格式刷假合并
第1种是需要熟悉各个不需要三键，本身就支持数组运算的函数的，8楼分享的aggregate就是在练习合并单元格时无意发现的
而这部分的发现，才有了另外这篇笔记http://www.excelpx.com/thread-430215-1-1.html

Excel函数博大精深,今天对合并单元格相关公式的介绍,或许只是一点皮毛,希望对各位同学能有些参考和使用价值,谢谢

心随你动

好东东，收藏先。