提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中重复单元格值x次?

例如,您在A列中有一个值列表,并且您希望根据B列中的次数重复特定次数的值,如左侧屏幕截图所示,您如何在Excel中实现此目的?


根据具体数量轻松复制和插入行X次

通常,除了处理手动复制和插入之外,没有一种好的方法可以多次复制和插入行。 但是,随着 Kutools for Excel's 基于单元格值重复行/列 功能,你可以轻松解决这个问题。 点击下载Kutools for Excel!

Kutools for Excel:比300方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


用公式和辅助列重复单元格值X次

要重复单元格值X次,可以插入一些帮助列,然后应用一些简单的公式,请执行以下步骤:

1。 在列A的左侧插入一列,然后在单元格A1中键入2,请参见屏幕截图:

2。 然后把这个公式 = A2 + C2 到A3中,然后将填充柄拖到单元格A6中,参见截图:

3。 然后在D1单元格中输入2,并通过拖动自动填充到C列的总次数12来填充该数字,参见截图:

4。 然后输入这个公式 = VLOOKUP(D2,$ A $ 1:$ B $ 6,2) 进入单元格E2并复制下来,您将得到以下结果:

  • 笔记:
  • 1。 在这个公式中, D2 表示您填入数字序列的第一个单元格 A1:B6 代表第一个助手列的范围和您需要重复的原始单元格值。
  • 2。 获取重复值后,您可以将其作为值复制并粘贴到其他任何位置。

用VBA代码重复单元格值X次

如果你考虑第一种方法很难理解,在这里,我也可以向你介绍一下VBA代码来解决它。

1。 按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块窗口.

VBA代码:重复单元格值X次

Sub CopyData()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next
End Sub
3。 然后按 F5 键运行此代码,会出现一个提示框,提醒您选择包含值和重复次数的范围,请参见屏幕截图:

4。 并点击 OK,另一个提示框会弹出,让你选择一个单元格放置结果,看截图:

5。 然后点击 OK,您将获得所选值已根据需要重复指定时间的结果。


使用非常棒的功能复制并插入单元格值X次

有时,您可能需要根据特定数量复制并插入单元格值x次, Kutools for Excel's 基于单元格值重复行/列 可以帮助您根据指定的数字快速复制和插入行。

提示:申请这个 基于单元格值重复行/列 功能,首先,你应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

1。 点击 Kutools > 插页 > 基于单元格值重复行/列,看截图:

2。 在 复制并插入行和列 对话框中选择 复制并插入行 在选项 类型 部分,然后选择要复制的数据范围,然后指定复制和插入行的重复时间,请参见屏幕截图:

4。 然后,点击 Ok or 申请 按钮,您将根据需要获得以下结果:

点击下载Kutools for Excel和免费试用版吧!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    mati · 1 months ago
    thank u! the vlookup function worked!
  • To post as a guest, your comment is unpublished.
    Joe · 2 months ago
    The VLOOKUP table option was the best for my use case. I wanted to take appointment times (i.e. 8am, 9am, 10am, and all in between) and repeat those several times but with different frequencies. It saved having to type a time, copy/paste it multiple times, and then repeat the process until I was done. The VLOOKUP table also gives me the option to change it up some at a later date if I wanted more or less.
  • To post as a guest, your comment is unpublished.
    Yzack Alva · 2 months ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Gary · 2 months ago
    That save me like a million hours of time.
    Thank you a million
  • To post as a guest, your comment is unpublished.
    Cristina · 3 months ago
    Me poupou muito trabalho essa procv!
    Obrigada!
  • To post as a guest, your comment is unpublished.
    L VR · 5 months ago
    This is a VERY sloppy and cumbersome fix.
  • To post as a guest, your comment is unpublished.
    Elektra · 7 months ago
    How can I access this using a MAC?
  • To post as a guest, your comment is unpublished.
    Me · 1 years ago
    Thank You !!
  • To post as a guest, your comment is unpublished.
    hiten · 1 years ago
    great solution for multiple cells. thanks for saving me time.....
  • To post as a guest, your comment is unpublished.
    Pankaj · 1 years ago
    Great solution, saved lot of times, thank you
  • To post as a guest, your comment is unpublished.
    Christina · 1 years ago
    The VLOOKUP option was perfect! Thank you for saving me hours of my life
  • To post as a guest, your comment is unpublished.
    Bill · 1 years ago
    Great solution! Thanks much
  • To post as a guest, your comment is unpublished.
    Kanwaljit · 1 years ago
    The Simplest and The Best !
    Thanks a Lot !!!!!
  • To post as a guest, your comment is unpublished.
    Courtney · 1 years ago
    Thank you so much! The VLOOKUP workaround works perfectly!
  • To post as a guest, your comment is unpublished.
    Master Org · 1 years ago
    Useless page and codes
  • To post as a guest, your comment is unpublished.
    Steve · 2 years ago
    Thank you for making the vlookup formula instructions so clear!!! This was immensely helpful!
  • To post as a guest, your comment is unpublished.
    Lanc3lot · 2 years ago
    Can you alter the vba code to mix also the cells upon result? So the lines won't be in order, but mixed
  • To post as a guest, your comment is unpublished.
    Vassi · 2 years ago
    The VBA code does not work if you don't have Kutools. It gives me error "variable not defined"
  • To post as a guest, your comment is unpublished.
    Prashant · 2 years ago
    The VBA or the formula. Neither of them is working for me. It gives "application defined or object defined error" Error 1004. Can anyone assist?
  • To post as a guest, your comment is unpublished.
    Vipul Pandey · 2 years ago
    मैं बहुत दिनों से यह ऑप्शन ढूढ रहा था
    धन्यवाद sir
  • To post as a guest, your comment is unpublished.
    Max · 2 years ago
    This was very helpful, came in handy to create a spreadsheet for work
  • To post as a guest, your comment is unpublished.
    m · 2 years ago
    Is there a way to alter the code so there are 2 columns with data and 1 column with the qty of repititions. i.e.
    A1 = x
    B2 = y
    C3 = 2

    the macro would then repeat A1 twice and b2 twice in the cels to the right

    Any help appriceated. Thanks.
  • To post as a guest, your comment is unpublished.
    raju · 2 years ago
    Thanks a Lot dude, U done a great job.
  • To post as a guest, your comment is unpublished.
    Odel · 2 years ago
    Still saving projects in 2017! Thanks so much for this! :-)
  • To post as a guest, your comment is unpublished.
    Kamna · 2 years ago
    Awesome! Many Thanks!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Eshan Gupta · 2 years ago
    Hi,

    I have an query:
    Base data:
    Client
    Country
    Overall

    Output:
    Client1
    Client2
    Client3 (if any)
    Country1
    Country2 & so on...(if any)
    Overall1
    Overall2 & so on...(if any)

    Kindly any one can solve this while using formula in excel ASAP
  • To post as a guest, your comment is unpublished.
    Hammad Bin Idrees · 2 years ago
    Thank you so much. :)
  • To post as a guest, your comment is unpublished.
    Cayla · 2 years ago
    Hi,

    Will the suggested VBA code in the article work if the cells with the number of times is not next to the cells with the value? If not, is there a way to modify the VBA to get it to work?
  • To post as a guest, your comment is unpublished.
    SivaM · 3 years ago
    Thanks alott !!!!

    VB Macro worked for me and saved my 1 week time....

    Thanks Again..
  • To post as a guest, your comment is unpublished.
    JohnnyRocket · 3 years ago
    Like #John Said, is there a way to have a set range so you do not need to select. I am also trying to paste each individual Value into its own column. I have changed the line
    Set OutRng=OutRng.offset(xNum,0) to Set OutRng=OutRng.offset(xNum,1) in attempt to make it into columns
    I seem to be getting an error on the line OutRng.Resize(xNum,1).value=xValue even if I copy and paste the code from this site. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Johnny · 3 years ago
    Thanks!! Saved me tons of time. Clever use of vlookup!
  • To post as a guest, your comment is unpublished.
    Ranjeet Ligade · 4 years ago
    Thanks Man ..!! This is working and saved my ample amount of time.
  • To post as a guest, your comment is unpublished.
    Ehsan · 4 years ago
    I used the first approach, it worked flawlessly, Thank you.
  • To post as a guest, your comment is unpublished.
    Narayan · 4 years ago
    Thanks Man!! Saved a lot of time. :-)
  • To post as a guest, your comment is unpublished.
    joe average · 4 years ago
    The VBA code breaks when there is a 0 value in the "number of times" column. Adjust the For Each loop as follows:

    For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    If xNum > 0 Then
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
    End If
    Next
  • To post as a guest, your comment is unpublished.
    John · 4 years ago
    Is there a way to have the macro remember the input and output destination so next time the macro is run you do not need to reselect?
  • To post as a guest, your comment is unpublished.
    Adam · 4 years ago
    Thank you for this, very easy to do and understand. I used to do this manually with 2600 cells.