作者:绿色风
分类:
时间:2022-08-18
浏览:100
楼主 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楼 畅心 |
学习! |
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素,
经与ExcelTip.Net站长Apolloh商议并征得其同意,
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示,
供有需要的人士查询使用,也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议,
可与本人沟通提出,或于本站点留言,我们会尽快处理。
在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一