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

or

根据Excel中的选定列快速将数据分成多个工作表

假设您的工作表中包含A到G列中的数据,则销售人员的名称位于A列中,您需要根据同一工作簿中的A列自动将此数据拆分为多个工作表。 每个推销员将被拆分为新的工作表。 Kutools for Excel分割日期 实用程序可以根据Excel中的选定列快速将数据拆分为多个工作表。

根据选定的列将数据拆分为多个工作表

根据行号将数据拆分为多个工作表

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 与Office 2007-2019和365一起使用; 支持所有语言; 在您的企业或组织中轻松部署。

点击 Kutools Plus > 工作表 > 分割数据(或Kutools Plus>分割数据)。 查看截图:

拍摄分割数据001 kutools右箭头

根据选定的列将数据拆分为多个工作表

假设您的范围数据如下图所示,并且您希望通过在新工作表中拆分每个名称,将该数据快速拆分为基于工作簿中列A的多个工作表。 例如,所有 凯利 将被拆分为相同的工作表。 你可以按如下方式完成它:

1。 请根据列选择要分割为多个工作表的范围。 小技巧:如果您选择了带标题的范围,请检查 我的数据有标题 选项。

2。 然后通过单击应用该实用程序 Kutools Plus > 工作表 > 拆分数据。 在 将日期分成多个工作表 对话框,请选择 专栏 然后从下拉列表中指定您想要基于数据拆分数据的列。

3。 请指定新的工作表名称。 你可以使用 列的值 作为新的工作表名称。 您也可以输入工作表名称的前缀或后缀。

4。 点击 OK 分割数据。 看截图:

列中的每个名称都已拆分为新工作表。


根据行号将数据拆分为多个工作表

假设你有一个范围的数据如图所示下面的截图,并且要将此数据迅速分成基于行数多个工作表,例如,在同一工作簿中的每一个数据5行分割成一个新的工作表。 您可以按如下方式将其删除:

1。 请根据每个5行选择要分割为多个工作表的范围。

2。 然后通过单击应用该实用程序 Kutools Plus > 工作表 > 拆分数据。 在 将日期分成多个工作表 对话框,请选择 固定行 然后在框中指定行号。

3。 请指定新的工作表名称。 你可以使用 行号 作为新的工作表名称。 您也可以输入工作表名称的前缀或后缀。

4。 点击 OK 分割数据。 看截图:

此范围的每个5行都已吐入同一工作簿中的新工作表中。


演示:根据Excel中的选定列将数据快速拆分为多个工作表

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


生产力推荐工具
以下工具可以大大节省您的时间和金钱,哪一个适合您?
Office Tab: 在您的Office中使用方便的选项卡,作为Chrome,Firefox和新Internet Explorer的方式。
Kutools for Excel: Excel的300高级函数 2019,2016,2013,2010,2007和Office 365。
Classic Menu for Office: 把熟悉的菜单带回到Office 2007,2010,2013,2016,2019和365,就像它是Office 2000和2003一样。

Kutools for Excel

上述功能只是Kutools for Excel的300强大功能之一。

专为Excel(Office)2019,2016,2013,2010,2007和Office 365而设计。 免费下载并使用60天。

Excel的Kutools屏幕截图

BTN阅读更多 BTN下载 btn购买

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.
    Arturo · 1 years ago
    Is there something similar for Google Sheets?
  • To post as a guest, your comment is unpublished.
    Christian · 1 years ago
    There seems to be an issue with the Kutools "Copy Page Setup" print function when used in Office 365 as it isn't able to copy the "Fit All Columns on One Page" setting to the other worksheets. Instead it uses the "Fit Sheet on One Page". Tried finding the printer default settings for the worksheets created by Kutools, but wasn't successful - it doesn't seem to use the settings from the default template? How can this be achieved?
  • To post as a guest, your comment is unpublished.
    Claire Counihan · 2 years ago
    Has there been a resolution to this? I am unable to split data into multiple worksheets based on the content of a column.
  • To post as a guest, your comment is unpublished.
    Debbie Toups · 5 years ago
    This function is not working, any suggestions
    • To post as a guest, your comment is unpublished.
      Algemron · 5 years ago
      I'm having a similar issue with this feature not working.
      seems to copy of of sync. believe its to do with there being only single entries for some of the data.
      cant work out why its not using the selected headers and replacing them with the first entry

      using the macro they have seems to work:
      http://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html
    • To post as a guest, your comment is unpublished.
      Algemron · 5 years ago
      Use the Macro they have on the website to fix this. Seems to work

      http://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html
  • To post as a guest, your comment is unpublished.
    Carrie · 5 years ago
    In my previous comment, the data I was splitting was in a table. Once I converted it to a range, I didn't have the problem with the record. The formatting issue still remains.
    • To post as a guest, your comment is unpublished.
      admin_jay · 5 years ago
      [quote name="Carrie"]In my previous comment, the data I was splitting was in a table. Once I converted it to a range, I didn't have the problem with the record. The formatting issue still remains.[/quote]
      We are going to enhance this utility to accomplish the Table data and try to support keep formatting in upcoming version. :-)

      We are working on it.
  • To post as a guest, your comment is unpublished.
    Carrie · 5 years ago
    When using split data, the last row of each new worksheet has the first record that's supposed to be on the next worksheet. Also, the formatting of the original worksheet is lost in the new worksheets.
  • To post as a guest, your comment is unpublished.
    Lou Oppedisano · 5 years ago
    I cannot locate the Enterprise > Worksheet Tools > Split Data in Excel 2010. Is this a standard tool on the tool bar or do i need to install this from add-ins or some other feature.
  • To post as a guest, your comment is unpublished.
    yohanu · 5 years ago
    I want to split data into multiple worksheets based on columns(2)
    Please give me some help!
    • To post as a guest, your comment is unpublished.
      Ryan · 5 years ago
      I need the same ! I am looking to split the data based on 2 columns. want to see worksheets that show "column1" - "Column2"