|
|
我们在工作中，经常会遇到合并单元格的问题，特别是新手，容易自己制造出这样的场景，却茫茫然无从着手。今天从一则案例说起，提供一个解决套路，不足之处，请指正。
图1
一、解决此类问题，对于小白来讲有两个关键点需要提前搞清楚：
1、合并单元格中的内容，本质上是合并区域内最上方单元格的内容。我们把合并后的单元格打散，就可以发现。
图2
2、合并单元格公式的填充，通过拖拽不能解决问题。在第一个合并单元格内输入公式后，全选要填写公式的列，通过ctrl+enter的方式填写公式。
图3
二，解决问题的套路
1、用=MATCH("",A2:A8,0)找出合并单元格的高度。公式中的”*“是通配符。
图4
通过与图3对比，我们发现，match的区域变化了，也就是填充过公式的单元格，会自动变化区域，得到的结果是本合并单元有内容的位置序号。但这不是我们所想要的，我们想要的是下一个合并单元格的位置，于是我们要修改一下match的区域。
图5：第二个合并单元格的公式变成=MATCH("*",A7:A12,0)
我们发现，得出的数字4，恰好是他所在合并单元格的高度；至于第二个合并单元格出现#na错误，是因为B下面没有内容了。我们给出一个足够大的数字即可。我们通过在此公式外套一个ifna来实现。
图6
2、有了高度，就可以用offset来动态引用区域了
图7
公式：=OFFSET(B2,,,IFNA(MATCH("*",A3:$A$8,0),9))第一参数不锁定，便于填充下面合并单元格时动态引用参考点；第二、三参数省略，行、列不偏移，只用第四参数控制引用的高度。
在编辑栏里按F9可以看到引用的数据正确。
图8
3、那么接下来就没有什么难点了。我就直接写结果了。
图9
图10
4、当你外面嵌套了sum之后，你会发现合并单元格不让输入数组公式，也就是不能按三键回车得到正确结果，那么，我们只好改用sumproduct这个来替代。
图11
三、优化公式。我们会发现上面的公式比较长且有类似的部分，能不能优化一下呢？（下面的问题不能理解的，就掌握上面的方法就好）
图12
图13
原题目要求是求差，是不是第一列为正，第二列变成负数，再求和就可以呢？因此，我们再构造一个一维横向数组来与他相乘。
图14
图15此图是图14的结果，通过F9显示出来
我们再用sumproduct就得出正确结果了。
图15 |
|
发表于 2023-3-22 19:00:11