【公式解析系列】按指定记录次数重复数据解法

楼主 gouweicao78	数据按指定重复次数显示，一般有3种解法。按指定次数重复数据.jpg 解法1、辅助列+普通公式。在D2输入公式向下复制，依次得到累积重复次数之和： =SUM(C$1:C1) 然后在G4单元格输入以下公式，向右向下复制，即可得到按次数重复的结果： =IF(ROW(1:1)>SUM($C:$C),"",LOOKUP(ROW(1:1)-1,$D:$D,A:A)) 解法2、数组公式，利用MMULT函数产生累积次数和的内存数组。在M4单元格输入以下公式，按CTRL+SHIFT+ENTER结束，向右向下复制： =IF(ROW(1:1)>SUM($C:$C),"",LOOKUP(ROW(1:1)-1,MMULT(--(ROW($2:$5)>TRANSPOSE(ROW($2:$5))),$C$2:$C$5),A$2:A$5)) 其中，MMULT函数部分是求累积和的通用解法，ROW>TRANSPOSE产生一个行、列数相同的矩阵，相当于枚举，进行一一对比，再进行矩阵相乘（见原理图）。如果要对A1:A10区域求得一个0、A1、A1+A2、A1+A2+A3……组成的累计和的数组，公式如下： =MMULT(--(ROW($1:$10)>TRANSPOSE(ROW($1:$10))),A$1:A$10) 当行数不多时，可以用COLUMN来代替TRANSPOSE，改为： =MMULT(--(ROW($1:$10)>COLUMN($A:$J)),A$1:A$10) 【点评】这两种解法原理都是对次数产生累积求和，作为记录指针查找的依据，最后都利用LOOKUP进行升序查找。由于MMULT受数组运算的限制，且数组公式的运算速度是个瓶颈，当具有较大数组量时，建议采用解法1，即使用辅助列+普通公式来完成。解法3、数组公式，利用记录数与1~最大记录数的列（行）号相比，以此返回需重复记录数（sylzldd 在J4单元格输入公式，按CTRL+SHIFT+ENTER结束，向右向下复制： =INDEX(A:A,SMALL(IF($C$2:$C$5>=COLUMN(INDIRECT("C1:C"&MAX(C:C),0)),ROW($2:$5),4^8),ROW(1:1)))&"" 本例中，已知C列最多记录次数为8，可以用COLUMN($A:$H)代替。如果记录次数是可变的，则需用上面通用解法。其中，C2:C5>=COLUMN(A:H)返回4行8列的数组，见原理图，每一行包含对应记录个数的行号，多余部分为65536行（空行）行号。原理.jpg 按指定次数重复数据.rar
2楼卫今仑军	感觉国良老师分享。
3楼 sylzldd	=INDEX(A:A,SMALL(IF($C$2:$C$5>=COLUMN($A:$H),ROW($2:$5),4^8),ROW(1:1)))&""
4楼 lrlxxqxa	看到【公式解析系列】这几个字，非常亲切，呵呵
5楼 gouweicao78	谢谢补充，这是解法3，其中COLUMN($A:$H)是根据C列最大记录个数来确定。其通用解法为： =INDEX(A:A,SMALL(IF($C$2:$C$5>=COLUMN(INDIRECT("C1:C"&MAX(C:C),0)),ROW($2:$5),4^8),ROW(1:1)))&"" 此外，这个解法比1楼解法2有点在于，不受MMULT函数数组个数限制。在1楼补充解析。
6楼 sylzldd	荣幸得到版主指点！多谢！
7楼此人隐身	Sub 按指定记录次数重复数据() Application.ScreenUpdating = False s = Range("a65536").End(xlUp).Row '获取A列数据最后一行行号 For j = 2 To s '由2开始循环到s，目的取A:B列数据进行复制 For i = 1 To Cells(j, 3).Value '循环1到单元格c列的值，确定复制次数 k = Range("e65536").End(xlUp).Offset(1, 0).Row '确定粘贴位置，每次下移一行 Range(Cells(j, 1), Cells(j, 2)).Copy Cells(k, 5) '复制A:B列数据，粘贴到E列 Next Next Application.ScreenUpdating = True End Sub
8楼好大一棵树王玉	这个看不太懂!
9楼 Chris1029	牛，先学习第一解！
10楼畅心	学习！