將總表按照指定列拆分成多個工作表,隨便哪一列都行

2020-12-17 vba雜談

前景提要

平時的工作中我們經常要和Excel進行打交道，所以對於Excel方面的需求也是多種多樣的，有時候我們需要針對工作表進行合併操作，將多個工作表的內容合併到一個工作表中進行分析，這樣更加的高效，但是有時候我們有需要反過來，將總表的數據按照某種規則進行拆分，拆分成多個工作表，這樣我們就能夠針對個體進行分析了，所以今天我們就來學習下，如果通過VBA來實現工作表按照列來進行拆分的需求

需求說明

將總表按照某列進行拆分，肯定是我們手上的報表是一個總表，我們需要針對個體進行分析，所以這個時候就會使用到針對總表的拆分，我們看看手上的這份報表

我們現在需要將這個工作表按照班級來進行拆分，每個班級一個工作表這樣的形式，方便我們針對班級來進行成績分分析

如果手工操作，你會選擇怎麼操作呢？

將這班級這一列進行篩選，從上往下一個班級篩選一次，然後新建工作表，然後複製粘貼，然後來來一個循環，非常的麻煩，我們一直都強調高效辦公的，那麼碰到這樣的情況，用VBA來如何實現呢？

代碼區

Sub chai()

Dim rng As Range, trng As Range, arr, sthn As Worksheet

Set trng = Application.InputBox("請選擇標題欄", "標題欄的確定", , , , , , 8)

TitleR = trng.Rows.Count

TitleC = trng.Column

Set rng = Application.InputBox("請選擇要拆分的參照列", "參照列的確定", , , , , , 8)

num = ActiveSheet.Index

TargetCol = rng.Column - (TitleC - 1)

Worksheets.Add after:=Worksheets(Worksheets.Count)

rng.Copy Cells(1, 1)

With ActiveSheet.UsedRange

.RemoveDuplicates 1, xlNo

End With

l = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range(Cells(2, 1), Cells(l, 1))

arr = rng

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

For i = 1 To UBound(arr)

Worksheets.Add after:=Worksheets(Worksheets.Count)

Set sthn = ActiveSheet

ActiveSheet.Name = arr(i, 1)

Worksheets(num).Activate

Rows(TitleR).AutoFilter Field:=TargetCol, Criteria1:="=" & arr(i, 1) & ""

With ActiveSheet.UsedRange

.SpecialCells(xlCellTypeVisible).Copy sthn.Cells(1, 1)

End With

Next i

Rows(TitleR).AutoFilter

End Sub

來看看代碼執行的過程

首先代碼會需要我們選擇表頭，這裡我們應該選擇所有的標頭範圍，而不是單獨標題欄這一行

然後需要選擇我們想要拆分的參照列，比方說我們這裡是按照班級來進行拆分的，所以這裡要選擇班級這一列。

然後稍等一小會之後，結果就出來了。

這裡我們選擇其中一個工作表來驗證下結果是否正確

數據都是完全相同的

代碼解析

我們來看看代碼是如何實現這樣的效果的

Set trng = Application.InputBox("請選擇標題欄", "標題欄的確定", , , , , , 8)

TitleR = trng.Rows.Count

TitleC = trng.Column

Set rng = Application.InputBox("請選擇要拆分的參照列", "參照列的確定", , , , , , 8)

num = ActiveSheet.Index

TargetCol = rng.Column - (TitleC - 1)

這一段就會經典中的經典了，利用inputbox來實現交互窗體，然後我門就可以代碼的使用者之間形成一個簡單的交互了，通過這個交互窗體我們得到了我們想要的數據，標頭總共有多少行多少列，還有參照列的位置也得到了了確定

然後進入今天的第一個重點

With ActiveSheet.UsedRange

.RemoveDuplicates 1, xlNo

End With

我們既然需要針對班級進行拆分，自然就需要拿到班級這一列的唯一值，我們通過篩選來進行篩選，也是間接得到唯一值的方法，那麼在代碼中我們如何實現這個唯一值的獲取呢？

就是上面的代碼

大家可以直接套用，這是簡單的套路，看看效果

這樣我們得到了班級列的唯一值，之後我們利用Excel自身的篩選功能來進行篩選

然後今天這是本篇的另外一個重點知識，如果針對篩選後的數據進行複製呢？

With ActiveSheet.UsedRange

.SpecialCells(xlCellTypeVisible).Copy sthn.Cells(1, 1)

End With

通用代碼，直接套用即可

然後就繼續循環下去，就得到了我們想要的結果了。

相關焦點

  • Microsoft Excel怎麼按條件快速將總表數據拆分成多個工作表?
    在使用Microsoft Excel統計數據時，一般情況下會將各類數據匯總到一個工作表中。當需要分類數據時，使用【篩選】功能的話重複性操作太多，還需要將篩選後的數據進行複製粘貼，有什麼辦法能夠按條件快速的將總表數據拆分成多個工作表？這個時候就需要用到強大的Visual Basic for Applications（VBA）了。
  • excel拆分合併技巧：將總表拆分成工作表的方法
    在平時的工作中，我們經常會遇到將工作表拆分，或者合併的問題。大多數人還只會用複製粘貼的方式來解決，雖然操作簡單，但是當遇到數據量較大的情況，無疑會拖垮我們的工作效率。其實工作表的拆分和合併沒有大家想像中的那麼難，本系列將分為上下兩篇教程，分別講解工作表拆分與合併的方法，本篇是上篇，將給大家帶來4種工作表拆分的方法，趕緊來看看吧！
  • excel拆分合併技巧:將總表拆分成工作表的方法
    在平時的工作中，我們經常會遇到將工作表拆分，或者合併的問題。大多數人還只會用複製粘貼的方式來解決，雖然操作簡單，但是當遇到數據量較大的情況，無疑會拖垮我們的工作效率。其實工作表的拆分和合併沒有大家想像中的那麼難，本系列將分為上下兩篇教程，分別講解工作表拆分與合併的方法，本篇是上篇，將給大家帶來4種工作表拆分的方法，趕緊來看看吧！
  • 「Excel技巧」利用數據透視表快速將一個匯總表拆分成多個工作表
    今天要說的是，如何根據某一列將一個Exce工作表拆分成多個工作表。舉慄子，以下這麼一份總表，需要按照班級將其拆分成多個單獨的表，一個班級為一個表。你會按照班級一個一個地篩選複製出來嗎？操作步驟：一、添加輔助列將需要做為拆分標準的列複製一列，作為輔助列。本例中是要按班級拆分，故將班級列拷貝一列作為輔助列。
  • excel拆分合併技巧：將工作表合併成總表的方法
    在上篇文章中我們給大家介紹了4種拆分工作表的方法，分別是函數、透視表、高級篩選、VBA，不知道小夥伴們學習得咋樣了？今天我們將學習合併工作表的三種方法，趕緊來看看吧！>在上篇文章中，對於總表拆分為分表的操作一共給大家分享了四種方法，建議同學們一定要勤加練習，才能熟能生巧。
  • Excel之VBA常用功能應用篇:拆分工作表
    特別個性化的案例就不拿出來說了，今天先分享一個在日常工作中可能會經常遇到的場景——工作表的拆分一、使用數據透視表一個Excel表往往由很多欄位組成，有時我們會通過不同的維度來分析表裡的數據，有時候我們也希望將數據按照某一維度分成多個工作表。比如下表：我們如果想按照城市把項目都放到不同工作表中，這時候最簡單的辦法是「數據透視表」。
  • VBA拆分工作表
    本節實例： 在工作中，我們可能會遇到一張很複雜、繁冗的表格，表格中的數據時公司各個部門的詳細數據清單，我們的任務是：把這個總數據表分解成一張張易於分析的數據表。當表格內容很多時，利用VBA是一種不錯的方法。 假設有如圖所示的數據，我們的任務是按照「地區」把原始數據拆分成一個工作表。
  • Excel-VBA按照表模板批量生成指定工作表
    應用場景1、多適用按照表模板批量複製生成工作表2、簡單填報內容3、附：視頻
  • EXCEL如何按某一列拆分成多個表
    這個需求還是很常見的，所以我們今天來一起學習一下看一下需求，按地區拆分，一個區域一個表（Sheet）文末補充如何拆分成獨立的文件>操作教程1、創建透視表> 輔助地區列，粘貼到G列，修改標題為拆分> 數據點擊數據區域的任意位置（連續區域）>
  • excel如何將一張工作表按篩選欄位拆分到多個工作表？
    我們在製作excel表格時，很多情況數據是放在一張工作表的，而且查詢也是在一張工作表裡，有時候我們希望能夠根據篩選的條件，將工作表拆分成多張工作表，如果一個個篩選然後複製到多個標籤表是很麻煩的，那麼如何能夠快速將一張工作表拆分成多個呢？下面就來看看吧。一、打開一份測試表格，我們希望根據部門將張拆分到多張工作表，每個工作表顯示一個部門數據。
  • 【VBA】宏代碼幫你輕鬆完成重複工作(總表拆分)
    現在要求按《模板》表的格式，將每個貧困戶的數據拆分到單個的工作表中，如下圖。注意加粗字體的單元格的內容都是從《總表》用公式提取過來的，這些單元格也是下一步我們錄製宏時，要操作的單元格。拆分後的效果見下圖。
  • Excel：INDIRECT函數——匯總多個工作表同一單元格值成一列
    ，同一項數據都位於同一個單元格，比如：每個月份的銷量都位於每個月份工作表的B1單元格，而我們需要把每個月的銷量匯總到一個總表中，在該總表中，各個月的銷量分布為同一列。這種匯總情況如下動圖：以上示例中，每個分工作表的命名是有規律的：從1到12月。
  • Excel拆分工作表,工作表拆分到工作簿,想怎麼拆就怎麼拆!
    工作中，我們經常遇到這樣的問題，比如：如何將一個工作表按類別快速拆分成多個工作表？如何將一個工作簿的多個工作表快速拆分另存為多個工作簿？下面就用案例來說明如何解決這兩個問題。第1個問題：有關工作表快速拆分的問題。案例：將員工花名冊按部門拆分成多個工作表。批量拆分工作表的方法：數據透視表1、創建數據透視表，首先按將普通的表格（員工基本情況表）轉換成數據透視表。
  • Excel一個文件如何快速拆分成多個工作表？
    在Excel文件中有多個工作表，如何把裡面的每個工作表拆分 出來生成單獨的文件保存起來呢？上圖中有12個工作表，手動複製-粘貼，十幾分鐘也能完成。如果更多呢工作表呢？2、在VBA窗口中，點擊左側列表中的【ThisWorkbook】，然後輸入代碼：代碼如下：Sub 拆分工作表
  • excel中如何將一列或兩列數據拆分成多列?
    有一天，領導突然讓列印一個花名冊或者一些整個表只有一列兩列數據，如果直接列印會出現大量的空白區域，浪費紙張不說，影響美觀才是最重要的。比如下圖所示，列印左邊數據肯定不行的，我們需要把左邊區域變成右邊區域，該怎麼操作呢？
  • 數據透視表也能拆分工作表？這個用法好神奇！
    有時候需要對一個表中的數據按照某一列進行拆分，例如按客戶名稱將數據分到不同的sheet中：最終實現這樣的效果：第二步：設計透視表布局最關鍵的是將需要進行拆分的欄位拖到篩選區域，然後將其他需要的欄位依次拖到行區域，如圖所示。
  • Excel中按照條件拆分工作表,數據透視表就能實現
    有時候我們在excel中將所有數據放在同一張工作表，但由於工作需要又不得不將他們拆分到不同的工作表中，如果數量較少的時候可以使用函數公式或者篩選的辦法，但是數據量較大又不會vba怎麼辦？今天小編分享一個極其簡單的辦法：案例1：按照班級拆分，數據量較少可以使用vlookup函數第一步：添加輔助列在A2中輸入公式=B2&COUNTIF($B$2:B2,B2)，就是統計班級出現的次數
  • 將多個工作表的數據合併到一個工作表中
    在工作中，我們的數據可能有很多，比如有很多個部門的數據，要分別放在不同的工作表中。但在匯總分析的時候，需要將所有部門的數據合併起來，統一處理。這時，將多表數據合併到一個工作表中，可能就是很多小夥伴頭疼的問題。今天，就來分享一段簡短的代碼來解決這個問題。如下圖所示，有5個班級的數據，分別放在5個不同的工作表中，它們的數據結構一樣。
  • Excel – 跨多個工作表匯總求和,最簡便還是得數 Power Query...
    案例：下圖 1 為某公司全體員工上半年的收入明細表，每個月有一個單獨的工作表，希望做一張匯總表，自動計算每個員工的總收入。當工作表中的數據更新時，刷新總表即可同步數據，效果如下圖 2。選中單元格內容為「Table」的列 --> 右鍵單擊 --> 選擇「刪除其他列」現在 Power Query 編輯器中就只剩下了一列。5.
  • Excel如何拆分工作表？
    Excel進行數據處理的時候需要將匯總的數據進行拆分多個工作表，下面小編就來教大家拆分工作表吧。1.當前我們要將該工作表按照人名進行拆分，所以就要先篩選數據。2.將數據選中，點擊插入中的——數據透視表按鈕。