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

Kako razdeliti podatke na več delovnih listov glede na stolpec v Excelu?

Recimo, da imate delovni list z ogromnimi vrstami podatkov, zdaj pa morate podatke razdeliti na več delovnih listov, ki temeljijo na Ime stolpca (glej naslednjo sliko zaslona), imena pa se vnesejo naključno. Morda jih lahko najprej razvrstite, nato pa kopirate in prilepite enega za drugim v druge nove delovne liste. Vendar boste za potrpljenje večkrat potrebovali potrpljenje. Danes bom govoril o nekaj hitrih trikih za rešitev te naloge.

doc razdeli podatke po stolpcih 1

Razdelite podatke na več delovnih listov na podlagi stolpca s kodo VBA

Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel


Razdelite podatke na več delovnih listov na podlagi stolpca s kodo VBA

Če želite podatke na podlagi vrednosti stolpca razdeliti hitro in samodejno, je naslednja koda VBA dobra izbira. Naredite to:

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v okno modula.

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. Nato pritisnite F5 tipko za zagon kode in izpiše se pozivno polje, ki vas opomni, da izberete vrstico glave, glejte sliko zaslona:

doc razdeli podatke po stolpcih 7

4. In nato kliknite OK in v drugem pozivu izberite podatke stolpca, na podlagi katerih želite razdeliti, glejte sliko zaslona:

doc razdeli podatke po stolpcih 8

5. Nato kliknite OK, vsi podatki v aktivnem delovnem listu pa so glede na vrednost stolpca razdeljeni na več delovnih listov. In deljeni delovni listi so poimenovani z imeni deljenih celic. Oglejte si posnetek zaslona:

doc razdeli podatke po stolpcih 2

Opombe: Razdeljeni delovni listi so nameščeni na koncu delovnega zvezka, kjer je glavni delovni list.


Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel

Kot začetnik Excela je ta dolga koda VBA za nas nekoliko težka in večina od nas niti ne ve, kako spremeniti kodo, kot jo potrebujemo. Tukaj vam bom predstavil večnamensko orodje -Kutools za Excel, njeno Razdeljeni podatki pripomoček vam lahko pomaga razdeliti podatke na več delovnih listov glede na stolpec, temveč lahko podatke razdeli po številu vrstic.

Opomba:Če želite uporabiti to Razdeljeni podatki, najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg podatkov, ki jih želite razdeliti.

2. Kliknite Kutools Plus > Delovni list > Razdeljeni podatki, glej posnetek zaslona:

doc razdeli podatke po stolpcih 3

3. v Razdelite podatke na več delovnih listov pogovorno okno, morate:

1). Izberite Poseben stolpec možnost v Razdeljeno na podlagi in izberite vrednost stolpca, na podlagi katerega želite razdeliti podatke na spustnem seznamu. (Če imajo vaši podatki glave in jih želite vstaviti v vsak nov deljeni delovni list, preverite Moji podatki imajo glave možnost.)

2). Nato lahko pod imenom imena delovnega lista določite Ime novega delovnega lista v razdelku določite pravila za imena delovnih listov iz Pravila spustni seznam, lahko dodate Prefiks or pripona tudi za imena listov.

3). Kliknite OK . Oglejte si posnetek zaslona:

doc razdeli podatke po stolpcih 4

4. Zdaj so podatki razdeljeni na več delovnih listov v novem delovnem zvezku.

doc razdeli podatke po stolpcih 5

Kliknite za prenos Kutools za Excel in brezplačno preskusno različico zdaj!


Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel

Kutools za Excel vključuje več kot 300 priročnih orodij Excel. Brezplačno poskusite brez omejitev v 30 dneh. Prenesite brezplačno preskusno različico zdaj!


Povezani članek:

Kako razdeliti podatke na več delovnih listov po številu vrstic?


Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Komentarji (303)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
Razdelitev podatkov na več delovnih listov na podlagi stolpca s kodo VBA kaže nekaj napake. prosim, poskusite to popraviti in posodobiti isto. Če zagotovite vzorčne datoteke excel, bo to res koristno.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, hvala za kodo, ki mi deluje! Poskušam najti kodo, ki razdeli en glavni list na več listov glede na datum
Ta komentar je moderator na spletnem mestu minimiziral
To je bilo neverjetno! Ta postopek bi mi vzel več kot eno uro, vendar je bil opravljen v 30 sekundah. To bom obdržal za svojo knjižnico VBA. Hvala vam!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, v svojem delovnem listu imam 30000 celic in jih moram razdeliti na mesece. ali obstaja koda, ki jo lahko uporabim, da to naredim hitreje. Imam 8 stolpcev in datum je stolpec B. Igral sem se z zgornjo kodo, ki je bila podana, vendar mi je velikokrat uspelo. Mi lahko prosim pomagate pri tem. Hvala vnaprej
Ta komentar je moderator na spletnem mestu minimiziral
Dobivam naslednjo napako: Runtime error '6' overflow Po odpravljanju napak pokažite vrstico For i = 2 To Ir Mojih excelovih vrstic je več kot 500,000. Ali obstaja kakšna rešitev Najlepša hvala za kodo. Lep pozdrav Lok
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, najlepša hvala za kodo. Dobivam naslednjo napako: Runtime error '6' overflow na vrstici Za i = 2 To Ir Kakršna koli rešitev za to. Hvala vam
Ta komentar je moderator na spletnem mestu minimiziral
Dobim napako, ko pritisnem F5 - GoTo Box prosim za referenco??
Ta komentar je moderator na spletnem mestu minimiziral
Postopek VBA je deloval odlično, najlepša hvala, ker ste delili svoje strokovno znanje in mi prihranili veliko časa!
Ta komentar je moderator na spletnem mestu minimiziral
Koda VBA je delovala odlično. Zdi se, da ne posodablja listov, saj so bile spremembe narejene na Sheet1. Prosim pomagaj.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, hvala za kodo, ki mi deluje! Imam samo dve vprašanji/opombi. 1 kopirani podatki ne vključujejo postavitve izvirne datoteke. Ali bi bilo mogoče podatke kopirati kot tabelo s samodejnim filtrom? 2 se zdi, da kopirani podatki niso omejeni na obseg naslova. Ali je mogoče prilagoditi kodo za določen obseg ali ime tabele? Te prilagoditve bi bile zelo koristne. Lep pozdrav, Pieter
Ta komentar je moderator na spletnem mestu minimiziral
Deluje kot čar! Hvala vam.
Ta komentar je moderator na spletnem mestu minimiziral
Deluje kot čar... Hvala za premium kodo... :lol:
Ta komentar je moderator na spletnem mestu minimiziral
Najlepša hvala, to je delovalo odlično. Vendar, kaj se zgodi, če želim, da so podatki znotraj vsakega zavihka ponovno razvrščeni (z uporabo prašnega stolpca)? V bistvu ga ta VBA razdeli na zavihke, vendar bi ga morda želel nadalje razčleniti..je to mogoče?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Jonathan, poznam star komentar, vendar bi lahko bil v pomoč drugim v prihodnosti: to sem moral narediti, vendar nisem mogel najti preprostega načina z VBA. Vendar sem ugotovil, da če naredite nov stolpec v svoji preglednici kot združitev 2, tako npr. =A1&" "&A2 To vam potem da 1 celico z obema nizoma informacij. Nato lahko zaženete zgornji modul in deluje v redu! Uredi – podatki v stolpcih morajo biti krajši od 30 znakov, sicer se podatki ne kopirajo (prikaže se kot napaka v modulu) in na sredini novih listov dobite prazen list.
Ta komentar je moderator na spletnem mestu minimiziral
Super.. To je bilo osupljivo. Tako dolgo sem se boril s to težavo in ta koda je prišla kot odloga. Hvala, ker ste ga delili.
Ta komentar je moderator na spletnem mestu minimiziral
Neverjetno. Hvala za objavo.
Ta komentar je moderator na spletnem mestu minimiziral
Koda je delovala kot čar za manjše podatke (manj kot 1200 vrstic). Poskušal sem uporabiti na večjem delovnem listu z (17000 vrstic) in se je pravkar zrušil, potem ko se je razdelil na 10-12 listov. Tako smo poskušali prvotne podatke razdeliti v 3 različne delovne zvezke in nas vseeno zaprli. Imamo Windows 7 in tudi naši računalniki niso tako počasni. Ali predlagate kakšne omejene vrstice podatkov za varno uporabo te kode? Vsak predlog bi bil hvaležen.
Ta komentar je moderator na spletnem mestu minimiziral
Koda je delovala kot čar za manjše podatke (manj kot 1200 vrstic). Poskušal sem uporabiti na večjem delovnem listu z (17000 vrstic) in se je pravkar zrušil, potem ko se je razdelil na 10-12 listov. Tako smo poskušali prvotne podatke razdeliti v 3 različne delovne zvezke in nas vseeno zaprli. Imamo Windows 7 in tudi naši računalniki niso tako počasni. Ali predlagate kakšne omejene vrstice podatkov za varno uporabo te kode? Vsak predlog bi bil hvaležen. Nisem prepričan: Kakšno je največje število vrstic, ki jih makro lahko podpira? Lahko bi se poigral s tem ... Je nekje med 20k in 40k![/quote]
Ta komentar je moderator na spletnem mestu minimiziral
Soočanje z isto težavo. Koda deluje za liste, kjer je podatkov manj vrstic, vendar pri večjih podatkih prikaže napako kot "Excel ne more dokončati te naloge z razpoložljivimi viri. Izberite manj podatkov ali zaprite druge aplikacije" (nobena druga aplikacija ne deluje istočasno) Koda deloval kot čar za manjše podatke (manj kot 1200 vrstic). Poskušal sem uporabiti na večjem delovnem listu z (17000 vrstic) in se je pravkar zrušil, potem ko se je razdelil na 10-12 listov. Tako smo poskušali prvotne podatke razdeliti v 3 različne delovne zvezke in nas vseeno zaprli. Imamo Windows 7 in tudi naši računalniki niso tako počasni. Ali predlagate kakšne omejene vrstice podatkov za varno uporabo te kode? Vsak predlog bi bil hvaležen.
Ta komentar je moderator na spletnem mestu minimiziral
Ti si moj junak vseh časov! To sem iskal več mesecev brez sreče. Ta tedenska/mesečna poročila moram narediti na več kot 147 delovnih listov in ne bodo mi dobili kutools. Glede na to.. Res se moram naučiti kodiranja. :( Ampak hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, imam list, ki ima 65000 zapisov in ima 8 različnih primerov, tako da bi v bistvu moral ustvariti 80 različnih listov. Poskušal sem zagnati to kodo, vendar sproži Runtime Error 6 Overflow. Ali je mogoče to kodo prilagoditi za rešitev moje težave? Prosim, vaša pomoč bo zelo cenjena.
Ta komentar je moderator na spletnem mestu minimiziral
[quote]HI , imam list, ki ima 65000 zapisov in ima 8 različnih primerov, tako da bi v bistvu moral ustvariti 80 različnih listov. Poskušal sem zagnati to kodo, vendar sproži Runtime Error 6 Overflow. Ali je mogoče to kodo prilagoditi za rešitev moje težave? Prosim, vaša pomoč bo zelo cenjena.Avtor ACE[/quote] Poskusite spremeniti Dim vcol, i As Integer v Dim vcol, i As Long
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, poskusil sem spremeniti vcol DIM v LOng in je deloval dobro, vendar je nenadoma zaradi napake ni dovolj pomnilnika za dokončanje tega dejanja, poskusite uporabiti manj podatkov ali zapreti druge aplikacije. Čeprav nimam odprtih nobenih drugih aplikacij. Imam malo več kot 100 vrstic in cca. Velikost datoteke 16 mb. vsaka pomoč bi bila hvaležna. Hvala Mustafa
Ta komentar je moderator na spletnem mestu minimiziral
Čudovit del kode - deluje odlično (če spremenite spremenljivke v tisto, ki jo potrebuje vaša preglednica)
Ta komentar je moderator na spletnem mestu minimiziral
Imam list s spremenljivim številom vrstic. Eden od stolpcev so datumi od leta 2010 dalje. Drugi stolpci so imena skladov s podatki NAV za vsak sklad glede na datum. Torej ne želim razdeliti stolpcev na različne liste, želim si razdeliti vsako IME SKLADA na svoj list s podatki NAV na datum konca meseca, ne dnevnih datumov. Ali je to mogoče ali je to nemogoče?
Ta komentar je moderator na spletnem mestu minimiziral
Imam delovni list, ki ga uporabljam in poskušam najti kodo vba, ki bo prepoznala ime računa in kopirala posebno vrstico v nov delovni zvezek in list z istim imenom, ali lahko pomagate?
Ta komentar je moderator na spletnem mestu minimiziral
Super! Koda VBA deluje, hvala! Potrebujem, da so ti izhodni delovni listi v posameznih Excelovih datotekah namesto v delovnih listih in pride do napake, ko razdelim na veliko delovnih listov.
Ta komentar je moderator na spletnem mestu minimiziral
Starscor in Tim, če želite liste datoteke razdeliti na več datotek z uporabo imen vrstic, je na tej isti spletni strani majhna koda makra, ki to naredi, samo poiščite "razdeli delovni zvezek za ločene datoteke Excel". bo našel. Dodajte kodo tega primera na konec tega, pri čemer seveda izbrišete podvojeni končni pod in pod in dobili boste eno datoteko za vsakega.
Ta komentar je moderator na spletnem mestu minimiziral
mi lahko kdo pomaga, kako razvrstiti stolpce na različnih listih v istem delovnem zvezku hkrati in odstraniti dvojnike na različnih listih, saj imam v istem delovnem zvezku približno 65 listov
Ta komentar je moderator na spletnem mestu minimiziral
to je tako razburljivo! Hvala vam. To sem iskal že nekaj časa.
Ta komentar je moderator na spletnem mestu minimiziral
Odlično - hvala, ker ste to delili. Celo širi poudarke/format na nove delovne liste!
Tu še ni objavljenih komentarjev
Obremenitev Več
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL