|
先看下源数据和要实现的效果。下图左表是源数据，包含要重复的内容和次数，如①所示。右边两列是重复后的效果，如②所示。也就是A重复2次，B重复3次，以此类推。首先，我们用辅助列的方法来完成。在C列添加辅助列，C3单元格输入公式=SUM(B$3:B3)，向下填充。得到上图C列的效果，实际就是对次数的累加。辅助列构建好，在E2单元格输入公式=LOOKUP(1,0/FREQUENCY(ROW(A1),C$3:C$6),A$3:A$6)&""，向下填充，完成。这里主要用到frequency和lookup。lookup的经典查询套路，lookup(1,0/(条件区域=条件),返回的区域)，我相信很多同学都会了。主要的是frequency的用法，它是一个频率分步函数，统计一组数据在区间间隔中出现的次数。函数语法结构如下，FREQUENCY(data_array, bins_array) ，有2个参数。第1参数是要计算频率分布的数组，第2参数是区间间隔。frequency会返回一个垂直数组。下面举一个简单的例子说明一下frequency的基础用法，如下图所示。左表是一个成绩表，现在要统计各区间分数的个数有多少个？各区间如绿色部门所示。简单来说，就是在B列的成绩中，统计小于等于60分的有几个，大于60且小于等于70分的有几个，以此类推。那么就可以用frequency来计算，选中E2:E6单元格，输入公式=FREQUENCY(B2:B9,{60,70,80,90})，按ctrl+shift+enter三键结束。要注意的地方是frequecny返回的结果是一个垂直数组{0;2;1;5;0}，也就是元素之间用分号分隔，同时它的元素个数比第2参数{60,70,80,90}多一个。它的统计规则其实就是我上面写的区间规则，60对应的是小于等于60的个数，70对应的是大于60小于等于70的个数，80对应的是大于70小于等于80的个数，90对应的是大于80小于等于90的个数，最后多出来的一个是大于90的个数。现在{60,70,80,90}是按升序排列的，假如它不是按升序排序的，函数统计的时候自己会先排序，但返回的结果还是要对应的。（这句话可能不好理解，但很重要）。frequency基础用法和原理说明后，就可以看我们最开始的公式了。如下图所示，我们先看frequency的这部分，也就是标①的那部分。第1参数row(a1)就是{1}，第2参数C3:C6就是{2;5;6;10}。frequency返回的结果是{1;0;0;0;0}，也就是②那部分，我用红色写出来的。那这个结果是怎么产生的？其实就是在第1参数{1}中小于等于2的有1个，大于2小于等于5的有0个，大于5小于等于6的有0个，大于6小于等于10的有0个，大于10的有0个。如果上面我说的frequency你明白了，剩下的就是lookup的用法。其他单元格是同样的计算规律，你可以自己查看，就知道为什么可以按指定次数重复内容了。不用辅助列的方法和用辅助列是一样的，只不过用offset的多维引用代替辅助列，直接给出公式，不再详细说明。在G2单元格输入公式=LOOKUP(1,0/FREQUENCY(ROW(A1),SUMIF(OFFSET(B$3,,,ROW($1:$4)),"<>")),A$3:A$6)&""，向下填充，完成。frequency的进阶用法，我还在学习研究中，希望能有所进步。感兴趣的同学，可以下载文件练习起来。https://pan.baidu.com/s/1_5RgUd9T3REYMbX3VkM4eg
|
