本帖最后由 佛山小老鼠 于 2016-10-19 17:53 编辑
今天和大家分享的是——让人头疼的合并单元格条件求和
具体要求见下面的图
大家先不要急着往下面看，动动脑筋2分钟，看下你有什么方面实现，再往下面看
方法1：倒数加法 首先大家要明白一个道理，合并单元格里的数据会出现那一个单元格？答案是：合并单元格中的数据会出现在合并单元格左上角第一个单元格 我们选择A列点一下格式刷，然后选中C列，目的也让C列出现合并单元格
思路：B列所有的总和=sum(B1:$B$12)，B12要用绝对引用减掉C2:$C$12,C12要用绝对引用，我们把公式写在C1，为什么要从C2开始，不从C1开始，如果从C1开始就会造成循环引用，且我们的数据都是在合并单元格左上解第1个单元格，所以影响我们计算，这样就得到了我们想的结果=SUM(B1:$B$12)-SUM(C2:$C$12) 把公式输好之后，打钩，然后选中区域C1:C12，把光标点到编辑栏里，按快捷键Ctrl+Enter,得到下图的结果
方法2：Offset+Match+Indirect实现
老鼠老师你这个倒数加法很好，可是我不想合并C列，那样太麻烦了，我想直接在单元格里实现，晕，没有办法，别人要求要这样，我只好动脑，使用自己的洪荒之力，终于想出一种解法，我相信还有简单的解法，我也有只这个能力了，公式长了一点
offset共5个参数
第1参数：参照位置
第2参数：偏移行
第3参数：偏移列
第4参数：行高
第5参数：列宽
先我们来求出offset函数的第2参数，偏移多少行？查找值是张三，到A列找，精确查找MATCH(E1,A:A,)找到张三在第1个位置，还要减掉1因为张三在A1单元格，不要偏移MATCH(E1,A:A,)-1
现在来看第4参数行高，这个相对来说，是这个题目重点，也是难点，请大家耐心听我解释
查找值是张三，到A列里找，精确查找，公式是=MATCH(E1,A:A,)，这里的第3参数本为是0，我简写了，这样找到张三在第1个位置，本来张三也是在A1单元格，那么我们等下要引用要从B1开始，所以行不偏移，要加1， 得到公式=MATCH(E1,A:A,)+1，也就是说我们区域是从张三下一个单元格开始，A2开始，然后找到第一个不等于空的值，="A"&(MATCH(E1,A:A,)+1)，返这个公式抹黑,F9,到A2，这样我们就得到了一个根据查找值来确定单元格区域，="A"&(MATCH(E1,A:A,)+1)&":A13",把它抹黑，F9,得到A2:A13,同理当查找值是李四时，="A"&(MATCH(E1,A:A,)+1)&":A13"把公式抹黑得到单元格区域A5:A13，其实我们现在得到只是文本，还不是单元格区域，摇身一变，还要外面嵌套一个indirect函数，这样返回的才是单元格区域，=indirect("A"&(MATCH(E2,A:A,)+1)&":A13")，从A2开始，我们要找到第一个不等于空的位置，也就是李四出现的位置，本来我们应该要减1，但是我们查找时回避张三本身，所以就不用减1了，
=MATCH(1=1,INDIRECT("A"&(MATCH(E1,A:A,)+1)&":A13")<>"",)
刚好张三合并了三个单元格
解释一下1=1，F9，返回的是True,也就是说match的查找值是True
我们现在基本上要完成公式了，下面我们把我们前面那个match合并起来，作为offset的第2参数，现在的这个match作为offset的第4参数
最后合起来得到下面的公式
=OFFSET($B$1,MATCH(E1,A:A,)-1,0,MATCH(1=1,INDIRECT("A"&(MATCH(E1,A:A,)+1)&":A13")<>"",),1)
解释offset函数第1参数从B1单元格开始，第2参数偏移行就是第1个match得到的，第3参数列不偏移就是0，第4参数行高就是第2个match得到，第5参数列宽为1
我们把这个公式=OFFSET($B$1,MATCH(E1,A:A,)-1,0,MATCH(1=1,INDIRECT("A"&(MATCH(E1,A:A,)+1)&":A13")<>"",),1)抹黑，F9得到{100;200;300}，最后我们要求张三的和，还要在最外面嵌套一个sum函数得到这个的公式=SUM(OFFSET($B$1,MATCH(E1,A:A,)-1,0,MATCH(1=1,INDIRECT("A"&(MATCH(E1,A:A,)+1)&":A13")<>"",),1))，最后大家把这个公式输在F1单元格，把光标移到编辑里，三键一齐下Ctrl+Shift+Enter，数组公式，大功告成
备注：有的朋友说，老师我按你操作，不行，注意，这里我动了手脚，我在A13单元格按了一个空格，也就是说A13单元格一定要有东西存在，我们才可以找到王五的分隔点，因为要保证王五后面也有不等于空
|
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2016-10-19 17:51
发表于 2016-10-19 22:49
