跳到主要内容

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

作者:晓阳 最后修改时间:2023-01-03

Kutools for Excel

提升 Excel 300+
强大的功能

假设您有一个工作表,其中包含 A 到 G 列中的数据,销售员的姓名在 A 列中,您需要根据 A 列中的名称自动将这些数据拆分为多个工作表。每个销售员将被拆分为一个新的工作表。 Kutools for Excel分割日期 实用程序可以根据Excel中的选定列将数据快速拆分为多个工作表。

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

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


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

kutools-右箭头

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

假设您具有一个范围数据,如下面的屏幕快照所示,并且您希望通过在一个新工作表中拆分每个名称,将这些数据基于工作簿中的A列快速拆分为多个工作表。 例如,全部 凯利 将被拆分到同一工作表中。 您可以按以下步骤完成它:

1.请根据列选择要拆分为多个工作表的范围。

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

Tips::如果您选择的范围包含标题,请检查 我的数据有标题 选项,您可以根据您的数据指定标题行的数量。 例如,如果您的数据包含两个标题,请键入 2。

3. 请指定新的工作表名称。 您可以使用 列的值 作为新的工作表名称。 你也可以输入一个 字首 or 后缀 对于工作表名称。

4。 点击 OK 要拆分数据,现在您可以看到列中的每个名称都将拆分为新工作簿中的新工作表。 看截图:


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

假设您有一个如下图所示的范围数据,并且您想根据行数快速将这些数据拆分为多个工作表,例如,将每 5 行数据拆分为一个新工作表。 您可以按如下方式获取它:

1.请基于每5行选择要拆分为多个工作表的范围。

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

Tips::如果您选择的范围包含标题,请检查 我的数据有标题 选项,您可以根据您的数据指定标题行的数量。 例如,如果您的数据包含两个标题,请键入 2。

3.请指定新的工作表名称。 您可以使用 行号 作为新的工作表名称。 您也可以输入 字首 or 后缀 为工作表名称。

4。 点击 OK 要拆分数据,此范围的每 5 行将拆分为新工作簿中的新工作表。 看截图:


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

Kutools for Excel:具有300多个方便的Excel加载项,可以在30天内免费试用,没有任何限制。 立即下载并免费试用!

生产力工具推荐
以下工具可以极大地节省您的时间和金钱,哪一种适合您?
Office Tab: 在办公室中使用方便的选项卡,作为Chrome,Firefox和New Internet Explorer的方式。
Kutools for Excel: 超过300种Excel高级功能 2021、2019、2016、2013、2010、2007 和 Office 365。

Kutools for Excel

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

专为 Excel(Office) 2021、2019、2016、2013、2010、2007 和 Office 365 设计。免费下载和使用 30 天。

Kutools for Excel 的屏幕截图

btn了解更多      btn下载     btn购买

Comments (22)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there a way to easily split these worksheets into separate workbooks instead? I have to create 213 workbooks of split data based on a column. It splits it out into sheets which is good too but I then have to manually save each one as a workbook. Just wondering if there is a work around that would speed up the process?
This comment was minimized by the moderator on the site
Split your workbook into multiple worksheets. Then group your worksheets and choose split workbook. It asks you to choose what folder you want them to be saved into and then saves each individual worksheet as a new workbook into the designated folder. I hope I have understood you correctly.
This comment was minimized by the moderator on the site
Hello, Ronel,

Sorry, currently, our feature doesn't support this operation. However, I've conveyed your request to our development team. Should there be any updates, we'll notify you promptly.

Thanks for your feedback.
This comment was minimized by the moderator on the site
It works well, How can I fetch all data from different sheeets to one sheet?

any one to help
This comment was minimized by the moderator on the site
Hello, PETER
To merge data from multiple sheets into one single sheet, the following article may help you.
https://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html
or you can use our Kutools for Excel 's Combine multiple worksheets from workbooks into one worksheet to solve this task, please check this article:
https://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html
Please check it, if you have any other problem, please comment here again.
This comment was minimized by the moderator on the site
This split function is really helpful but I need total also in subsequent split sheets. What should I do? Please help
This comment was minimized by the moderator on the site
Hi there,
Brilliant function to split data to new worksheets.
Sometimes inconsistent naming of the sheets when selecting "Values of Column" as new worksheet name. Reverts to "Sheet 1" etc.
What is the rule that determines this?Are there any workarounds?
Many thanksNaomi
This comment was minimized by the moderator on the site
Currently, we can split based on text string in a certain column, based on specific rows in a certain column. I think it is fantastic to add the ability to split based on the time group in a certain column. For example, I have a date column, values are 01/01/2020, 01/03/2020....03/05/2025.., I want to split the whole table by year into different sheets.
This comment was minimized by the moderator on the site
Simply add a "year" auxiliary column.
This comment was minimized by the moderator on the site
I can split data, but it doesn't keep the formatting. It's almost not worth it to have it split the data without the formatting.
This comment was minimized by the moderator on the site
Is there something similar for Google Sheets?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Has there been a resolution to this? I am unable to split data into multiple worksheets based on the content of a column.
This comment was minimized by the moderator on the site
This function is not working, any suggestions
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations