您的位置: 首页 > EXCEL技巧 > Excel基础 >

在Excel中按指定的重复次数填充数据到一列

时间:2013-12-14 整理:docExcel.net

在工作中有时需要将Excel某列中的数据按指定的次数依次重复填充到另一列。例如在下图的示例中,要将A列的编号按B列对应次数重复填充到D列,即首先将第一个编号“WOY756”在D列中填充2次,接着将第二个编号“QLG752”填充1次,将第三个编号“RWR880”填充5次,……。

按指定次数重复填充数据示例

可用数组公式或VBA来实现这样的填充,示例文件下载地址:http://www.excel123.cn/Soft/hanshu/201212/41.html。

在D2单元格输入下面的数组公式后按Ctrl+Shift+Enter结束(下同),然后拖动填充柄向下填充公式,直到最后一个编号按指定的次数全部出现为止。

公式1:

=OFFSET(A$1,MIN(IF(COUNTIF($D$1:D1,A$2:A$21)<B$2:B$21,ROW(A$1:A$20))),)

说明:COUNTIF函数统计公式所在单元格以上区域中所产生的各个“编号”数量,将其与B列对应次数进行对比,如果未达到B列对应次数,则返回“编号”相对A1单元格的偏移量,否则返回“FALSE”,这将得到一个包含“FALSE”和偏移量的数组。MIN函数取得最小值,即“编号”的偏移量,最后用OFFSET函数返回“编号”。

公式2:

=INDEX(A$1:A$21,SMALL(IF($B$2:$B$21>=COLUMN($A:$J),ROW($B$2:$B$21)),ROW(A1)))

说明:“IF($B$2:$B$21>=COLUMN($A:$J),ROW($B$2:$B$21))”产生一个20行10列由FALSE和数字组成的数组(本例A列共有20个数据,最大重复次数为10),其中的数字为各“编号”对应的行号,且各数字的数量与B列的重复次数相同。如16行“BXQ763”对应的“重复次数”为3,数组中即包含3个“16”。“COLUMN($A:$J)”需根据B列的最大值进行修改,例如“重复次数”中最大值为26,则改为“COLUMN($A:$Z)”。

公式3:

=INDEX(A$2:A$21,MATCH(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$1,,,ROW($1:$21))),1))

说明:MATCH函数的第二个参数“SUBTOTAL(9,OFFSET(B$1,,,ROW($1:$21)))”,在本例中产生一个对B列数字进行累加的内存数组“{0;2;3;8;10;13;22;29;31;37;44;46;56;59;69;72;75;85;94;97;99}”,第三个参数为“1”,MATCH函数会查找小于或等于第一个参数的最大值,并返回其在数组中的相对位置,即A2:A21区域中的相对行号。例如对于D17单元格,MATCH函数的第一个参数“ROW(A16)-1”返回15,数组中小于等于15的最大值为13,13处于数组中的第6个位置,MATCH函数返回6,公式返回A2:A21区域中的第6行数据,即编号“JGN347”。

如果数据量较大,用下面的VBA代码较为快捷。按Alt+F11,打开VBA编辑器,在代码窗口中粘贴下列代码并运行即可。

Sub 按指定次数重复数据()
Dim Rng, Arr()
Dim i As Integer, j As Integer, k   As Integer
Dim LastRow As Integer, Total As Integer
LastRow = [A65536].End(xlUp).Row
Total = Application.WorksheetFunction.Sum(Range("B2:B" & LastRow))
Rng = Range("A1:B" & LastRow)
ReDim Arr(1 To Total, 1 To 1)
For i = 2 To UBound(Rng, 1)
For j = 1 To Rng(i, 2)
  k = k + 1
  Arr(k, 1) = Rng(i, 1)
Next
Next
Range("D2").Resize(k, 1).Value = Arr
End Sub

根据指定次数重复Excel2007单元格的内容 问题:根据指定次数重复Excel2007单元格的内容
回答:...格输入公式: =SUM($B$2:B2)-ROW(A1) ②回车得到结果1,拖动填充柄,向下填充,直到出现0为止。然后点击数据选项卡里面的排序按钮。 ③弹出排序提醒对话框,勾选上方的扩展选定区域,点击排序按钮。 ④排序完成,选择A2...
突出显示Excel2013中不符合要求的行 问题:突出显示Excel2013中不符合要求的行
回答:...2,0) 点击格式按钮。 ③弹出设置单元格格式界面,点击填充选项卡。 ④选择一款颜色,确定。 ⑤这时会返回编辑格式规则界面,确定。 ⑥不符合规则的人员被红色标记出来了,如下图所示: 公式说明 countif(在哪里...
统计指定数字在区域中的出现次数 问题:统计指定数字在区域中的出现次数
回答:大家知道,Excel中的COUNTIF函数只能对区域中满足单个指定条件的单元格进行计数。如果要在一列包含文本格式数值的区域中统计某个数字出现的次数,就无法使用COUNTIF函数。这时可用下面的公式,假如数据在A2:A11区域中,要统...
从一列中按出现次数多少顺序提取数据 问题:从一列中按出现次数多少顺序提取数据
回答:...:$A$21, $A$2:$A$21)*IF(COUNTIF(B$1:$B1, $A$2:$A$21),0,1), 0)) 然后拖动填充柄向下填充公式,直到出现“#N/A”为止。 或使用下面的数组公式: =INDEX($A$1:$A$21,MOD(SMALL(IF(MATCH($A$2:$A$21,$A$2:$A$21,)=ROW(A$1:A$20),ROW(A$2:A$21)-COUNTIF($A$2:$A$21,$A$2:$A$21)*10^3),R...
将区域中的值按出现次数的多少提取到一列 问题:将区域中的值按出现次数的多少提取到一列
回答:...0)=0,A$1:E$10)) 公式输入完毕按Ctrl+Shift+Enter结束,然后拖动填充柄向下填充公式,直到出现“#N/A”为止。 受MODE函数的限制,该公式仅对数值有效,且不能提取只出现一次的数值。要将区域中所有的数据按出现次数提取到一列,...
Excel统计一列中连续相同值的几个数组公式 问题:Excel统计一列中连续相同值的几个数组公式
回答:...ROW($1:$19)),IF($A$2:$A$20<> 合格 ,ROW($1:$19)))=D5)) 然后拖动填充柄向下填充,即可得到各种连续次数对应出现的数量。 三、依次列出连续“合格”的次数 本例中,A列从上向下连续“合格”出现的次数分别为:2次、7次、3次、2次...
相关推荐: