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

or

如何取消合并单元格并在Excel中填入重复值?

如果您有一个包含许多合并单元格的工作表,现在您需要取消合并它们并自动填充合并单元格中的原始值,如下面的截图所示。 你怎么能够快速处理这个任务?

取消合并单元格并使用“转至特殊”命令填充重复数据

取消合并单元格并用VBA代码填充重复数据

取消合并单元格并用一次点击填充重复数据


取消合并单元格并向下复制值:

如果要取消合并的单元格并用合并的单元格的相对值填充单元格, Kutools for Excel取消合并单元格 功能可以帮助您,通过应用它,您可以快速取消合并的单元格并使用相应的数据自动填充每个未合并的单元格。

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


箭头蓝色右泡 取消合并单元格并使用“转至特殊”命令填充重复数据


随着 去特别 命令,您可以取消合并单元格并填写值。 但这种方法有点麻烦,请按以下步骤操作:

1。 选择已合并单元格的列。

2。 点击 主页 > 合并与中心 > 取消合并单元格。 看截图:

3。 合并的单元格没有被合并,只有第一个单元格会填充原始值。 然后再次选择范围。

4。 然后点击 主页 > 查找和选择 > 去特别。 在 去特别 对话框中选择 空白 选项。 看截图:

5。 然后点击 OK,范围内的所有空白单元格都被选中。 然后输入 = 并按下 Up 键盘上的箭头键。 看截图:

6。 然后按 Ctrl + Enter 键,所有的空白单元格都填充了原始的合并数据。 看截图:


箭头蓝色右泡 取消合并单元格并用VBA代码填充重复数据

使用以下VBA代码,您可以快速取消合并单元格并填写值。

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

2。 点击 插页 > 模块,并粘贴在下面的宏 模块窗口。

Sub UnMergeSameCell()'Upadateby20131127 Dim Rng As Range,xCell As Range xTitleId =“KutoolsforExcel”Set WorkRng = Application.Selection Set WorkRng = Application.InputBox(“Range”,xTitleId,WorkRng.Address,Type:= 8)Application.ScreenUpdating = False Application.DisplayAlerts = False For Each Rng In WorkRng If Rng.MergeCells Then With Rng.MergeArea .UnMerge .Formula = Rng.Formula End With End If Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

3。 然后按 F5 键来运行此代码,将显示一个对话框供选择要使用的范围,请参阅屏幕截图:

4. 点击 OK,那么合并的单元格会被取消并自动填充原始合并值。


箭头蓝色右泡 取消合并单元格并用一次点击填充重复数据

随着 取消合并单元格 实用程序 Kutools for Excel,只需点击一下鼠标就可以取消合并单元格并填充重复数据,这种方法非常简单和方便。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel,你可以做如下:

1。 选择您想要取消合并的列并填写数据。

2。 点击 Kutools > Range > 取消合并单元格,看截图:

3。 并且合并的单元格未被合并,并且一次填充重复值。 查看屏幕截图:


提示:如果要合并具有相同值的相邻行,则可以应用合并相同单元格功能,如下面的屏幕截图所示:

点击下载并免费试用Kutools for Excel Now!


箭头蓝色右泡 取消合并单元格并使用Kutools for Excel填充重复数据

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


相关文章:

在Excel中用相同的数据合并相邻的行


Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能30天免费试用。
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.
    excel user 22 · 1 years ago
    Thx!!! It saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    Oyunsuren · 1 years ago
    Great. Thank you very very much.
  • To post as a guest, your comment is unpublished.
    Joel · 1 years ago
    that's brilliant, thanks a bunch, massive sheet with loads of inconsistently merged cells fixed in seconds.
  • To post as a guest, your comment is unpublished.
    Barbs · 2 years ago
    Thanks a million, saved me loads of time using that Go to Special function!
  • To post as a guest, your comment is unpublished.
    Nabil · 2 years ago
    Thanks a lot. Used the Go To Special solution and it worked perfectly.
  • To post as a guest, your comment is unpublished.
    Tari-Chan · 2 years ago
    wow thank you. you save my life.
  • To post as a guest, your comment is unpublished.
    Tari-Chan · 2 years ago
    Thank you sir,I used kutools before, but i don't know why it just doesn't work now since i change to win10pro & office2016, but 'go to special' way is so helpful. thank you again.
  • To post as a guest, your comment is unpublished.
    Shimpundu · 2 years ago
    Awsome! This save a lot of time.
  • To post as a guest, your comment is unpublished.
    Pedro · 2 years ago
    Fantastic! VBA code worked! thank you so much! :)
  • To post as a guest, your comment is unpublished.
    Prabakar · 2 years ago
    Thank you very much; Love your solution :-)
  • To post as a guest, your comment is unpublished.
    Giang · 2 years ago
    "Unmerge cells and fill with duplicate data with VBA code" - It's really fast and useful. Thankyou very much! ^_^
  • To post as a guest, your comment is unpublished.
    Thomas · 2 years ago
    I personally use another tool that seems to work in a similar way. It's called Power-user and you can also unmerge and fill down cells. The (little) difference is that you can track each merged cell separately. www.powerusersoftwares.com
  • To post as a guest, your comment is unpublished.
    Korozja · 3 years ago
    Find & Select / ctrl + enter - THANK YOU
  • To post as a guest, your comment is unpublished.
    kobio · 3 years ago
    Thanks - it worked first time!
  • To post as a guest, your comment is unpublished.
    Doaa · 3 years ago
    Awesome. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Agus · 3 years ago
    Thank you for your tips.. It works well !!
  • To post as a guest, your comment is unpublished.
    Hakim · 3 years ago
    Excellent solution ! Thanks !
  • To post as a guest, your comment is unpublished.
    Neel · 3 years ago
    Good One. saved lot of time

    Thanks
  • To post as a guest, your comment is unpublished.
    Rhonda · 3 years ago
    Saved my life! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Anil · 4 years ago
    Very nice explanation with screen shot.... thanks alot...
  • To post as a guest, your comment is unpublished.
    Tom · 4 years ago
    This is really helpful but I have one question. Is there a way to modify the VBA code to skip blank merged cells? I only want the cell to be unmerged and duplicated if there is a value other than blank. I'm sure it wouldn't take much more than an if/else statement or two, I'm just too dumb to do it.
  • To post as a guest, your comment is unpublished.
    Luan · 4 years ago
    Awesome! Keep going with the good work!
  • To post as a guest, your comment is unpublished.
    Amir · 4 years ago
    Thanks a lot for your solution...
  • To post as a guest, your comment is unpublished.
    john · 5 years ago
    Thank you so much this is a very helpful post. It really save me alot of time
  • To post as a guest, your comment is unpublished.
    arlicay · 5 years ago
    You have made my week! 8)
  • To post as a guest, your comment is unpublished.
    Deva · 5 years ago
    Very helpful..!
    Thank you very much.
  • To post as a guest, your comment is unpublished.
    MarkFrank · 5 years ago
    Extremely helpful! Thanks!
  • To post as a guest, your comment is unpublished.
    Lu · 5 years ago
    Sir you saved my life, you are the reason why i bookmarked this page :)!!
  • To post as a guest, your comment is unpublished.
    Alberto · 5 years ago
    Your instructions are as simple as powerful!!!
    I owe you a dinner.
  • To post as a guest, your comment is unpublished.
    tmar · 5 years ago
    Nice job. But Function method worked on first good on first 2288 lines, then without error or notice, just did not do lines past that. I have over 6000 lines (rows). If I manually select any range beyond the 2288 line, the Function method does not work. Unfortunately, data vendor merges lines in the Excel data delivery.

    Any suggestions?
    Thanks for the great info.
  • To post as a guest, your comment is unpublished.
    Andre · 5 years ago
    This saved me countless hours, thank you so much for this. I did notice one thing I came across with the Go To Special Command that confused me for a second. I needed to search within the document, and the ctrl+enter function copies the code, but not the value. I recommend (if you want to search like i did) to copy the row that was merged, paste special, values. This will get rid of the code and make searchable text. Thank you again for this amazing trick.
  • To post as a guest, your comment is unpublished.
    mo · 5 years ago
    this is awesome. life saver
  • To post as a guest, your comment is unpublished.
    Kim · 5 years ago
    OMG, you just saved me SO MUCH TIME.
  • To post as a guest, your comment is unpublished.
    Mak · 5 years ago
    Both the methods are working perfectly fine. Thank you :-)
  • To post as a guest, your comment is unpublished.
    Kyle Baker · 5 years ago
    The VBA worked great except I used it for a 16 digit number and excel turns the last digit to a "0". Any suggestions on a work around for that?
  • To post as a guest, your comment is unpublished.
    Arty · 5 years ago
    Brilliant !! VBA code works perfectly.

    fixed my export to CSV problem :)
  • To post as a guest, your comment is unpublished.
    didier · 6 years ago
    Thanks for the unmerge and duplicate vba code