Jak rozdělit data do více listů na základě sloupce v aplikaci Excel?
Předpokládejme, že máte list s obrovskými řádky dat, a nyní musíte data rozdělit do více listů na základě Příjmení sloupec (viz následující snímek obrazovky) a jména se zadávají náhodně. Možná je můžete nejprve seřadit a potom je zkopírovat a vložit jeden po druhém do dalších nových listů. K opakovanému kopírování a vkládání však bude zapotřebí vaší trpělivosti. Dnes budu mluvit o několika rychlých tricích k vyřešení tohoto úkolu.
Rozdělte data do několika pracovních listů na základě sloupce s kódem VBA
Rozdělte data do několika pracovních listů na základě sloupců pomocí Kutools pro Excel
Rozdělte data do několika pracovních listů na základě sloupce s kódem VBA
Chcete-li rychle a automaticky rozdělit data na základě hodnoty sloupce, je dobrou volbou následující kód VBA. Udělejte prosím toto:
1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2, klikněte Vložit > Modula vložte následující kód do okna modulu.
Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub
3. Poté stiskněte tlačítko F5 klíč ke spuštění kódu a zobrazí se okno s výzvou, které vám připomene výběr řádku záhlaví, viz screenshot:
4. A pak klikněte OK Tlačítko a v druhém řádku výzvy vyberte data sloupce, podle kterých se chcete rozdělit, viz screenshot:
5. Potom klepněte na tlačítko OKa všechna data v aktivním listu jsou rozdělena do více listů podle hodnoty sloupce. A rozdělené listy jsou pojmenovány s názvy rozdělené buňky. Viz screenshot:
Poznámka: Rozdělené listy jsou umístěny na konec sešitu, kde je hlavní list.
Rozdělte data do několika pracovních listů na základě sloupců pomocí Kutools pro Excel
Jako začátečník aplikace Excel je pro nás tento dlouhý kód VBA poněkud obtížný a většina z nás dokonce neví, jak jej upravit podle potřeby. Zde vám představím multifunkční nástroj -Kutools pro Excel, to je Rozdělit data nástroj vám nejen pomůže rozdělit data do více listů na základě sloupců, ale také může rozdělit data podle počtu řádků.
Poznámka:Použít toto Rozdělit dataNejprve byste si měli stáhnout soubor Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.
Po instalaci Kutools pro Excel, udělejte prosím toto:
1. Vyberte rozsah dat, který chcete rozdělit.
2, klikněte Kutools Plus > Pracovní list > Rozdělit data, viz screenshot:
3. V Rozdělte data do více pracovních listů dialogové okno, musíte:
1). Vybrat Specifický sloupec možnost v Rozdělit na základě a v rozevíracím seznamu vyberte hodnotu sloupce, podle kterého chcete rozdělit data. (Pokud mají vaše data záhlaví a chcete je vložit do každého nového děleného listu, zkontrolujte to Moje data mají záhlaví volba.)
2). Poté můžete určit názvy dělených listů pod položkou Název nového listu V části zadejte pravidla pro názvy listů z okna pravidla z rozevíracího seznamu můžete přidat Předpona or Přípona i pro názvy listů.
3). Klikněte na ikonu OK knoflík. Viz screenshot:
4. Nyní jsou data rozdělena do několika listů v novém sešitu.
Klikněte a stáhněte si Kutools pro Excel a bezplatnou zkušební verzi hned teď!
Rozdělte data do několika pracovních listů na základě sloupců pomocí Kutools pro Excel
Kutools pro Excel obsahuje více než 300 užitečných nástrojů aplikace Excel. Zdarma to můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si bezplatnou zkušební verzi hned teď!
Související článek:
Jak rozdělit data do více listů podle počtu řádků?
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
