Note: The other languages of the website are Google-translated. Back to English

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ě Název produktu 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.

doc rozdělit data podle sloupců 1

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:

doc rozdělit data podle sloupců 7

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:

doc rozdělit data podle sloupců 8

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:

doc rozdělit data podle sloupců 2

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:

doc rozdělit data podle sloupců 3

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:

doc rozdělit data podle sloupců 4

4. Nyní jsou data rozdělena do několika listů v novém sešitu.

doc rozdělit data podle sloupců 5

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ší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

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!
officetab dno
Komentáře (299)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Rozdělení dat do více listů na základě sloupce s kódem VBA vykazuje určitou chybu. zkuste to prosím opravit a aktualizovat to samé. Pokud poskytnete ukázkové excelové soubory, bude to opravdu užitečné.
Tento komentář byl moderátorem webu minimalizován
Ahoj, děkuji za kód, který mi funguje! Snažím se najít kód, který rozdělí jeden hlavní list na více listů na základě data
Tento komentář byl moderátorem webu minimalizován
To bylo neuvěřitelné! Tento proces by mi zabral více než hodinu, ale bylo hotovo během 30 sekund. Tento si nechám pro svou knihovnu VBA. Děkuji!
Tento komentář byl moderátorem webu minimalizován
Ahoj, v pracovním listu mám 30000 8 buněk a potřebuji je rozdělit na měsíce. existuje kód, který mohu použít, abych to udělal rychleji. Mám XNUMX sloupců a datum je sloupec B. Hrál jsem si s výše uvedeným kódem, ale moc se mi to nepovedlo. Můžete mi s tím prosím pomoci. Díky předem
Tento komentář byl moderátorem webu minimalizován
Zobrazuje se mi následující chyba: Runtime error '6' overflow Po ladění zobrazit řádek For i = 2 To Ir Moje řádky v Excelu jsou přes 500,000 XNUMX. Existuje nějaké řešení Děkuji mnohokrát za kód. S pozdravem Lok
Tento komentář byl moderátorem webu minimalizován
Dobrý den, moc děkuji za kód. Zobrazuje se mi následující chyba: Runtime error '6' overflow at the line For i = 2 To Ir Jakékoli řešení. Děkuji
Tento komentář byl moderátorem webu minimalizován
Při stisku F5 se zobrazuje chyba - GoTo Box žádá o odkaz?
Tento komentář byl moderátorem webu minimalizován
Proces VBA fungoval perfektně, mnohokrát vám děkuji za sdílení vašich odborných znalostí a ušetřili jste mi spoustu času!
Tento komentář byl moderátorem webu minimalizován
Kód VBA fungoval perfektně. Zdá se, že se listy neaktualizují, protože jsou provedeny změny v Listu1. Prosím pomož.
Tento komentář byl moderátorem webu minimalizován
Ahoj, děkuji za kód, který mi funguje! Mám pouze dvě otázky/připomínky. 1 zkopírovaná data nezahrnují rozložení původního souboru. Bylo by možné zkopírovat data jako tabulku s automatickým filtrem? 2 se zdá, že zkopírovaná data nejsou ohraničena rozsahem titulků. Je možné upravit kód pro konkrétní rozsah nebo název tabulky? Tyto úpravy by byly velmi užitečné. S pozdravem Pieter
Tento komentář byl moderátorem webu minimalizován
Funguje jako kouzlo! Děkuji.
Tento komentář byl moderátorem webu minimalizován
Funguje jako kouzlo... Děkuji za prémiový kód... :lol:
Tento komentář byl moderátorem webu minimalizován
Děkuji mnohokrát, fungovalo to skvěle. Co se však stane, když chci data na každé kartě znovu seřadit (pomocí sloupce prašník)? V podstatě to VBA rozděluje na karty, ale možná bych to chtěl dále rozebrat..je to možné?
Tento komentář byl moderátorem webu minimalizován
Ahoj Jonathane, starý komentář, který znám, ale v budoucnu by mohl být užitečný pro ostatní: Potřeboval jsem to udělat, ale nemohl jsem najít snadný způsob s VBA. Zjistil jsem však, že pokud ve své tabulce vytvoříte nový sloupec jako sloučení těch 2, tak např. =A1&" "&A2 To vám dá 1 buňku s oběma sadami informací. Poté můžete spustit výše uvedený modul a funguje dobře! Upravit – Data ve sloupcích musí být kratší než 30 znaků, jinak se data nezkopírují (zobrazí se jako chyba v modulu) a uprostřed vašich nových listů získáte prázdný list.
Tento komentář byl moderátorem webu minimalizován
Úžasné.. Bylo to ohromující. S tímto problémem jsem se potýkal tak dlouho a tento kód přišel jako oddechovka. Děkujeme za sdílení.
Tento komentář byl moderátorem webu minimalizován
Úžasný. Děkujeme za zveřejnění.
Tento komentář byl moderátorem webu minimalizován
Kód fungoval jako kouzlo pro menší data (méně než 1200 řádků). Zkoušel jsem použít na větší pracovní list s (17000 10 řádků) a po rozdělení na 12-3 listů to prostě spadlo. Zkusili jsme tedy rozdělit původní data do 7 různých sešitů a přesto nás vypnout. Máme Windows XNUMX a naše počítače také nejsou tak pomalé. Navrhujete nějaké omezené řádky na datech, abyste mohli tento kód bezpečně používat? Jakýkoli návrh by byl oceněn.
Tento komentář byl moderátorem webu minimalizován
Kód fungoval jako kouzlo pro menší data (méně než 1200 řádků). Zkoušel jsem použít na větší pracovní list s (17000 10 řádků) a po rozdělení na 12-3 listů to prostě spadlo. Zkusili jsme tedy rozdělit původní data do 7 různých sešitů a přesto nás vypnout. Máme Windows 20 a naše počítače také nejsou tak pomalé. Navrhujete nějaké omezené řádky na datech, abyste mohli tento kód bezpečně používat? Jakýkoli návrh by byl oceněn. Nejsem si jistý: Jaký je maximální počet řádků, které může makro podporovat? Mohl bych si s tím pohrát... Je to někde mezi 40k a XNUMXk![/quote]
Tento komentář byl moderátorem webu minimalizován
Čelí stejnému problému. Kód funguje pro listy, kde je dat méně řádků, ale pro větší data zobrazuje chybu, protože "Excel nemůže dokončit tuto úlohu s dostupnými zdroji. Vyberte méně dat nebo zavřete jiné aplikace" (nefunguje žádná jiná aplikace současně) Kód fungovalo jako kouzlo pro menší data (méně než 1200 řádků). Zkoušel jsem použít na větší pracovní list s (17000 10 řádků) a po rozdělení na 12-3 listů to prostě spadlo. Zkusili jsme tedy rozdělit původní data do 7 různých sešitů a přesto nás vypnout. Máme Windows XNUMX a naše počítače také nejsou tak pomalé. Navrhujete nějaké omezené řádky na datech, abyste mohli tento kód bezpečně používat? Jakýkoli návrh by byl oceněn.
Tento komentář byl moderátorem webu minimalizován
Jsi můj hrdina všech dob! Hledal jsem to měsíce bez úspěchu. Musím to dělat týdenní/měsíční rozdělování zpráv do 147+ pracovních listů a nedostanou mi kutooly. Na tu poznámku... Opravdu se potřebuji naučit kódovat. :( Ale děkuji vám!
Tento komentář byl moderátorem webu minimalizován
Ahoj, mám list, který má 65000 8 záznamů a má 80 různých případů, takže v podstatě by to mělo generovat 6 různých listů. Zkoušel jsem spustit tento kód, ale hází Runtime Error XNUMX Overflow. Lze tento kód upravit, aby se vyřešil můj problém? Prosím, ur pomoc bude velmi oceněna.
Tento komentář byl moderátorem webu minimalizován
[quote]HI, mám list s 65000 záznamy a s 8 různými případy, takže v podstatě by to mělo generovat 80 různých listů. Zkoušel jsem spustit tento kód, ale hází Runtime Error 6 Overflow. Lze tento kód upravit, aby se vyřešil můj problém? Prosím, ur pomoc bude velmi oceněna.Od ACE[/quote] Zkuste změnit Dim vcol, i As Integer na Dim vcol, i As Long
Tento komentář byl moderátorem webu minimalizován
Ahoj, zkusil jsem změnit DIM vcol na LOng a fungovalo to dobře, ale najednou kvůli chybě není dostatek paměti k dokončení této akce, zkuste použít méně dat nebo zavřete jiné aplikace. I když žádné jiné aplikace otevřené nemám. Mám něco málo přes 100 tisíc řádků a cca. velikost souboru 16 MB. jakákoli pomoc by byla oceněna. Díky Mustafa
Tento komentář byl moderátorem webu minimalizován
Úžasný kousek kódu – funguje perfektně (pokud změníte proměnné na ty, které vaše tabulka potřebuje)
Tento komentář byl moderátorem webu minimalizován
Mám list s proměnným počtem řádků. Jedním ze sloupců jsou data od roku 2010 dále. Ostatní sloupce jsou názvy fondů s údaji o NAV pro každý fond k datu. Nechci tedy rozdělit sloupce do různých listů, chci rozdělit každý NÁZEV FONDU na jeho vlastní list s údaji NAV za datum konce měsíce, nikoli s denními daty. Dá se to udělat nebo je to nemožné?
Tento komentář byl moderátorem webu minimalizován
Mám pracovní list, který používám, a snažím se najít kód vba, který znovu rozpozná název účtu a zkopíruje příslušný řádek do nového sešitu a listu se stejným názvem, můžete mi pomoci?
Tento komentář byl moderátorem webu minimalizován
Skvělý! Kód VBA funguje, díky! Potřebuji, aby tyto výstupní listy byly v jednotlivých souborech aplikace Excel namísto listů a při rozdělení na mnoho mnoha listů dojde k chybě.
Tento komentář byl moderátorem webu minimalizován
Starscor a Tim, pokud chcete rozdělit listy souboru do několika souborů pomocí názvů řádků, na stejné webové stránce je malý kód makra, který to dělá, stačí vyhledat „rozdělit sešit na oddělení souborů aplikace Excel“. najde to. Přidejte kód tohoto příkladu na konec tohoto příkladu a samozřejmě smažte duplicitní end sub a sub a pro každý získáte jeden soubor.
Tento komentář byl moderátorem webu minimalizován
může mi někdo pomoci, jak seřadit sloupce v různých listech ve stejném sešitu najednou a také odstranit duplikáty v různých listech, protože mám asi 65 listů ve stejném sešitu
Tento komentář byl moderátorem webu minimalizován
to je tak vzrušující! Děkuji. Už jsem to nějakou dobu hledal.
Tento komentář byl moderátorem webu minimalizován
Skvělé - děkujeme za sdílení. Dokonce propaguje zvýraznění/formát do nových pracovních listů!
Zatím zde nejsou žádné komentáře
Načíst další
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL