Tip: Jiné jazyky jsou přeloženy společností Google. Můžete navštívit English verze tohoto odkazu.
Přihlásit se
x
or
x
x
Registrovat
x

or

Jak rozdělit data na více listů na základě sloupce v aplikaci Excel?

Předpokládejme, že máte pracovní list s obrovskými řadami dat a teď je třeba rozdělit data na více listů na základě Jméno (viz následující obrázek) a jména jsou zadána náhodně. Možná je můžete nejprve třídit a potom je zkopírovat a vložit jeden po druhém do dalších pracovních listů. Ale to bude vyžadovat vaši trpělivost kopírovat a vložit opakovaně. Dnes budu mluvit o rychlých trikách, které tento úkol řeší.

doc rozdělit data podle sloupců 1

Rozdělit data do více pracovních listů na základě sloupce s kódem VBA

Rozdělit data do více pracovních listů na základě sloupce s programem Kutools for Excel


Rozdělit data do více pracovních listů založených na konkrétním sloupci nebo řádcích počítat v pracovním listu:

Pokud chcete rozdělit velký list do více listů na základě konkrétních dat sloupců nebo řádků počítat, Kutools pro Excel's Split Data funkce vám pomůže rychle a snadno řešit tento úkol.

doc rozdělit data podle sloupců 6

Kutools pro Excel: s více než 200 šikovnými doplňky aplikace Excel, můžete zkusit bez omezení v 60 dnech. Stáhněte si bezplatnou zkušební verzi teď!


Rozdělit data do více 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 správný výběr následujícího kódu VBA. Postupujte takto:

1. Podržte stisknuté tlačítko 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 Modul.

Sub Splitdatabycol()
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
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
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").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ávesa pro spuštění kódu a zobrazí se výzva k připomenutí výběru řádku záhlaví, viz screenshot:

doc rozdělit data podle sloupců 7

4. A pak klepněte na tlačítko OK , a ve druhém řádku vyberte prosím údaje o sloupci, které chcete rozdělit na základě, viz screenshot:

doc rozdělit data podle sloupců 8

5. Potom klepněte na tlačítko OK, a všechny údaje v aktivním listu jsou rozděleny do více tabulek o hodnotu sloupce. A rozdělené listy jsou pojmenovány názvy rozdělených buněk. Zobrazit screenshot:

doc rozdělit data podle sloupců 2

Poznámka: Rozdělené listy jsou umístěny na konci sešitu, kde se nachází hlavní list.


Rozdělit data do více pracovních listů na základě sloupce s programem Kutools for Excel

Jako začátečník aplikace Excel je tento dlouhý kód VBA pro nás poněkud obtížný a většina z nás dokonce ani neví, jak změnit kód podle našich potřeb.

Zde vám představím multifunkční nástroj -Kutools pro Excel, to je Split Data pomůcka nejen vám může pomoci rozdělit data do více pracovních listů na základě sloupce, ale také rozdělit data podle počtu řádků.

Kutools pro Excel : s více než 300 šikovnými doplňky aplikace Excel, můžete zkusit bez omezení v 60 dnech.

Pokud jste nainstalovali Kutools pro Excel, prosím proveďte následující:

1. Vyberte rozsah dat, které chcete rozdělit.

2, klikněte Kutools Plus > Pracovní list > Split Data, viz screenshot:

doc rozdělit data podle sloupců 3

3. V Rozdělit data do více pracovních listů dialogové okno, je třeba:

1). Vybrat Specifický sloupec možnost v Rozdělit na základě a vyberte hodnotu sloupce, kterou chcete v rozbalovacím seznamu rozdělit na základě údajů. (Pokud mají vaše data záhlaví a chcete je vložit do každého nového rozděleného listu, zkontrolujte prosím Moje data mají záhlaví volba.)

2). Poté můžete zadat názvy rozdělených listů pod Název nového listu , zadejte pravidla názvů listu z pravidla rozbalovací seznam můžete přidat Předpona or Přípona pro názvy listů také.

3). Klepněte na tlačítko OK tlačítko. Zobrazit screenshot:

doc rozdělit data podle sloupců 4

4. Data jsou nyní rozdělena do několika pracovních listů v novém sešitu.

doc rozdělit data podle sloupců 5

Klikněte pro stažení Kutools pro Excel a zkušební verze zdarma!


Rozdělit data do více pracovních listů na základě sloupce s programem Kutools for Excel

Kutools pro Excel obsahuje více než 300 šikovné nástroje Excel. Můžete zkusit bez omezení v 60 dnech. Stáhnout zkušební verzi zdarma!


Související článek:

Jak počítat data do několika listů podle řádků?



Doporučené nástroje pro produktivitu

Karta Office

zlatá hvězda1 Přiveďte praktické karty do aplikace Excel a jiného softwaru Office, stejně jako Chrome, Firefox a nový Internet Explorer.

Kutools pro Excel

zlatá hvězda1 Úžasný! Zvyšte svou produktivitu v minutách 5. Nepotřebujete žádné speciální dovednosti, kromě dvou hodin denně!

zlatá hvězda1 300 Nové funkce pro aplikaci Excel, Značka Excel je velmi snadná a výkonná:

  • Sloučit buňky / řádky / sloupce bez ztráty dat.
  • Kombinovat a konsolidovat více listů a sešitů.
  • Srovnání rozsahů, kopírování více rozsahů, převod textu na datum, konverzi jednotek a měn.
  • Počet podle barvy, dílčí součty stránkování, třídění podle rozšířeného třídění a super filtru,
  • Více Vyberte / Vložit / Odstranit / Text / Formát / Odkaz / Komentář / Workbooks / Worksheets Nástroje ...

Snímka obrazovky Kutools pro Excel

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.
    Katharina · 21 days ago
    Great Script, thanks! What do I have to do to set the header row range and column to use for grouping/splitting within the script? I know it is more elegant to use selectable parameters but for my use case it is always the same. As I do know nothing about VBA, any little hint is appreciated :-) Best, Katharina
  • To post as a guest, your comment is unpublished.
    daiana · 25 days ago
    It doesn't work with 120000 rows. Is there any way to make it work?
  • To post as a guest, your comment is unpublished.
    Radoslav · 28 days ago
    Perfect!!! Works and refer to all my demands. Tnx for that source code.
  • To post as a guest, your comment is unpublished.
    Rudi Miller · 1 months ago
    Sub parse_data()
    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
    vcol = 1
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:C1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 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
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
    Else
    Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub
  • To post as a guest, your comment is unpublished.
    Jason · 2 months ago
    This formula is great, works perfectly for me.
    I want to split out data based on a location, which is in column 1. Which this does.
    However, is it possible to also split out based on column 2, for example. Built and Not Built. So a secondary condition also?