|
小伙伴们，大家好！昨天群里一个同学问了一个问题，怎么样对筛选后的结果求不重复个数，这个问题把我难住了。不过群里的杨平老师给了一个公式，我也学习了一下。今天就来和小伙伴们分享一下。 先来看下数据源和计算后的结果。A列是数据源，有生产部，销售部，财务部3个部门，但是有重复。所以未筛选前不重复的个数是3个。然后把财务部筛选出去，剩下生产部和销售部，所以筛选后不重复的个数是2。 咱们先来说说未筛选前不重复个数的计算方法，其实之前说过很多次了。就是match=row的套路。公式为=SUMPRODUCT(N(MATCH(A2:A13,A2:A13,)=ROW(1:12)))。 MATCH(A2:A13,A2:A13,)这部分是match的数组用法，分别在A2:A13中查找A2:A13的每一个值，返回它们的位置，如上图B列所示；ROW(1:12)是一个序号数组，和match返回结果的尺寸大小要一样，如C列所示。MATCH(A2:A13,A2:A13,)=ROW(1:12)这部分是让两者比较，相等的返回true，不相等的返回false，如D列所示。而true的部分刚好对应A列不重复值第1次出现的位置。最后计算true的个数就是不重复值的个数。n函数是将true转为1，false转为0。最后用sumproduct求和，是为了不用按ctrl+shift+enter三键。 未筛选下不重复个数的计算方法理解了，就可以看看筛选后不重复个数的计算方法了。主要是在上一步的基础上，添加一个条件，就是把筛选后隐藏的去除掉。那就用到subtotal这个函数了，它可以忽略筛选后的隐藏行。但是直接用subtotal计算区域只计算未隐藏的，不能去重复。所以还要用到offset的多维引用。 下图是直接用subtotal对区域计算的结果，只能把隐藏的忽略，但不能去重复。因为它是对整个区域运算。 下图是offset多维引用和subtotal结合的用法。公式为=SUBTOTAL(3,OFFSET(A1,ROW(1:12),))。offset的第2参数ROW(1:12)是一个数组，offset返回的结果是A2:A13，但是它是一个多维引用，可以把每个单元格看做一个独立的区域；然后用subtotal分别对多维引用的每个区域（此时一个区域是一个单元格）非空计数，被隐藏的单元格就统计为0，未被隐藏的统计为1。 subtotal返回的结果为{1;1;1;0;1;1;0;1;0;0;1;1}，0对应的位置就是被隐藏的单元格，比如A5对应的就是数组中第4个位置的0。 接下来用SUBTOTAL(3,OFFSET(A1,ROW(1:12),))和MATCH(A2:A13,A2:A13,)=ROW(1:12)相乘，那么既没有隐藏的又是第1次出现的就返回1，否则返回0。最后用sumproduct求和就算出筛选后不重复的个数。所以完整的公式为=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))*(MATCH(A2:A13,A2:A13,)=ROW(1:12)))。 还可以用countif，公式为=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:12),))/COUNTIF(A2:A13,A2:A13))。关键的部分还是subtotal和offset的配合用法。 在这里也希望大家多加群提问，好的问题也可以有好的答案，让我们共同学习。再次感谢杨平老师。 文件链接： https://pan.baidu.com/s/1CKKTiexmfr46J0GDa5REkg 提取码：v687 |
|
|
