1、问题描述编辑
在我们制作报表过程中,当在同一个维度进行纵向对比的时候,所在的部门总会想知道在对比项中谁是最优的,谁是最差的。而各个部门的负责人总会想知道自己与最优的差距有多大,最差的与前一位差距有多大。如何进行报表的设置及排除其中有影响的数据,下边讲为小伙伴做一个步骤详解。效果图如下图所示:
2、方法示例编辑
标杆数据即我们从扩展出数据里选择出来最优的,可以使用max()/min()函数或依据算法获取。
I 单元格 内公式为: IF(LEN(F2) = 0, "", max(MAPARRAY(GREPARRAY(H2, LEN(trim(item)) > 0 && item > 0), ABS(item - F2))))
逐层解释为:
LEN(trim(item)) > 0 && item > 0) 对当前单元格进行判空及数值为0 的数据进行排除
GREPARRAY(H2, LEN(trim(item)) > 0 && item > 0), ABS(item - F2)) : 将数组中为0的为空的排除后,形成一个新数组
MAPARRAY(GREPARRAY(H2, LEN(trim(item)) > 0 && item > 0), ABS(item - F2))) : 将数组中进行一次转换
max()选取该数组中最大值
J 单元格内公式: : IF(LEN(F2) = 0, "", min(MAPARRAY(GREPARRAY(H2, LEN(trim(item)) > 0 && item > 0), ABS(item - F2))))
同最大值公式类似
通过条件属性,设置公式 满足 ABS($$$ - G2) = I2 当前单元格值与 最优值相等时候,即进行背景色变换。
至此,我们已经完成整个高亮显示最优值,最差值表单显示的所有步骤。
此操作的难点在于数组公式的逻辑,在操作时候可先理清楚逻辑即可完成功能实现。