提示:其他語言是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文件; 一列到多列。
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 將個性化電子郵件批量發送給多個收件人。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過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加載項,可以在30天之內不受限制地免費試用。 下載並免費試用現在!


生產力推薦工具
以下工具可以大大節省您的時間和金錢,哪一個適合您?
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"