今天，给大家分享几个关于合并单元格操作的小技巧。
如下图，图1 中实现了各部门的业绩总额，部门人员数、季度人员平均业绩、最好业绩、最差业绩等功能。
图1 效果图
这里是如何做的呢？
下面，EK就和大家一起来看看是如何实现的。
1、我们给E2单元格做一个数据下拉列表，分别为：销售1部，销售2部，销售3部。如下图所示：
图2 插入数据下拉列表
2、在F2单元格中输入公式：=SUM((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*C2:C17) 这里要注意的是：因为该公式为数组公式，在输完公式以后，需要同时按【SHIFT】 【CTRL】 【ENTER】三个键，然后会在F2单元格中显示成。{=SUM((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*C2:C17)}。花括号不是我们输入的，是按下前面三个键以后的效果。
图3 数组公式效果1
图4 数组公式效果图2
这里，EK给大家简单说明一下。如何理解。
IF(A2:A17<>"",ROW(A2:A17)，表示，当A2：A17单元格区域不为空，返回的是当前单元格行号，即返回数组A为{2,false,false,false..,8,,,,13...}。
LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17)，在数组A中查找单元格行号对应的值。因为LOOKUP是模糊查找，ROW（A2：A7）中的行号分别为2,3,4,5,6,7.在数组A中查找的数字为2.因为数组A中只有2,8,13（分别对应A2：A17中非空单元格的行号）。查找到的数字只能是2，同理A8：A12 中只能查找到8、A13：A17 查找的是13、这里LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17)返回的数组为{销售1部,销售1部,销售1部,销售1部,销售1部,销售1部,销售2部,销售2部,销售2部,销售2部,销售2部,销售3部,销售3部,销售3部,销售3部,销售3部,销售3部}.
E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17)这部分，当E2代表的是销售1部，返回的数组B为{true,true,true,true,true,true,false,false,false,false,false,false,false....},
EXCEL中true=1，false= 0，则SUM((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*C2:C17) 看做数组B与C2：C17对应的数组元素相乘的和。转换成数组C{true *1536,true *2272,true *1615,true *2055,true *2253,true *2308,false *2004,false *2154,....}同样可以转换成{1536,2272,1615,2055}的求和
3、G2的公式为=SUM((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*1)同样，该公式也是数组公式。因为该公式与F2中公式类似。这里就不分析了。没看明白的小伙伴可以在看一遍步骤2 中的分析。不同点是把A2:A17转换成了1.制作图：
图5 部门人员总数
4、H2中公式=F2/G2 这公式相信小伙伴们都能理解。
5、I2中公式 =MAX((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*C2:C17)。同样，该公式是数组公式。这里提示一下。我们前面的是SUM求和。这里是MAX对数组C求最大值，其实原理是一样的。（当E2=销售1部时，数组C的最大值为2308.）
图6 最大业绩
6、J2中公式=LARGE(((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17)),A2:A17))*C2:C17),G2) 同样。也是数组公式。
LARGE(数组或单元格区域，k）表示的是在数组或者单元格区域中，最大值中排名为k的数据
分析，((E2=LOOKUP(ROW(A2:A17),IF(A2:A17<>"",ROW(A2:A17))为数组C{1536，2272,1615,2055,2253,2308,0,0,0，..}当E2=销售1组时，求最低业绩，也就求在数组C中销售1部总人数的排名。LARGE函数刚好可以满足我们的需求。
图7 最低业绩
到这里我们的需求就全部完成了。
评论 (0)