提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

根據Excel中的選定列快速將數據分成多個工作表

假設您的工作表中包含A到G列中的數據,則銷售人員的名稱位於A列中,您需要根據同一工作簿中的A列自動將此數據拆分為多個工作表。 每個推銷員將被拆分為新的工作表。 Kutools for Excel拆分日期 實用程序可以根據Excel中的選定列快速將數據拆分為多個工作表。

根據選定的列將數據拆分為多個工作表

根據行號將數據拆分為多個工作表


點擊 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 · 7 months 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 · 1 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 · 4 years ago
    This function is not working, any suggestions
    • To post as a guest, your comment is unpublished.
      Algemron · 4 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 · 4 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 · 4 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 · 4 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.