Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Ki jan yo fann done nan plizyè fich ki baze sou kolòn nan Excel?

Sipoze ou gen yon Fèy travay ak ranje gwo nan done, e kounye a, ou bezwen fann done yo nan fichye miltip ki baze sou la non kolòn (gade D swiv), ak non yo antre nan owaza. Petèt ou ka sòt yo an premye, ak Lè sa a, kopye epi kole yo youn pa youn nan nouvo fich nouvo. Men, sa ap bezwen pasyans ou pou w kopye epi kole repete. Jodi a, mwen pral pale sou kèk ke trik nouvèl rapid yo rezoud travay sa a.

doc fann done pa kolòn 1

Done done nan plizyè fich ki baze sou kolòn ak kòd VBA

Done done nan plizyè fich ki baze sou kolòn ak Kutools pou Excel


Done Split nan fichye miltip ki baze sou kolòn espesifik oswa ranje konte nan fich travay:

Si ou vle fann yon Fèy travay gwo nan fèy papye miltip ki baze sou done espesifik kolòn oswa ranje konte, a Kutools pou Excel's Done Split karakteristik ka ede ou rezoud travay sa a byen vit ak byen fasil.

doc fann done pa kolòn 6

Kutools pou Excel: ak plis pase 200 sou la men Excel ajoute-ins, gratis eseye ki pa gen okenn limit nan jou 60. Download ak gratis jijman Koulye a!


Done done nan plizyè fich ki baze sou kolòn ak kòd VBA


Si ou vle fann done yo ki baze sou valè kolòn byen vit ak otomatikman, kòd VBA sa a se yon bon chwa. Tanpri fè tankou sa a:

1. Kenbe desann la ALT + F11 kle yo louvri a Microsoft Visual Basic pou aplikasyon pou fenèt.

2. Klike sou Antre > Modil, epi kole kòd sa a nan fenèt modil la.

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. Lè sa a, peze F5 kle nan kouri kòd la, epi li se yon bwat èd memwa déchirure soti nan fè ou sonje ranje ranje header nan, gade Ekran:

doc fann done pa kolòn 7

4. Lè sa a, klike sou OK bouton, ak nan bwat dezyèm fwa a, tanpri chwazi done yo kolòn ke ou vle fann ki baze sou, gade D:

doc fann done pa kolòn 8

5. Lè sa a, klike sou OK, ak tout done nan Fèy travay la aktif se divize an fichye miltip pa valè a kolòn. Ak fichye yo fann yo te rele ak non yo selil divize. Al gade nan Ekran:

doc fann done pa kolòn 2

nòt: Fèy yo fann yo mete nan fen liv la kote travay la mèt se nan.


Done done nan plizyè fich ki baze sou kolòn ak Kutools pou Excel

Kòm yon debutan Excel, kòd long VBA sa a yon ti jan difisil pou nou, epi pifò nan nou menm pa konnen ki jan yo modifye kòd la kòm bezwen nou an.

Isit la, mwen pral entwodui ou yon zouti multifonksyonèl -Kutools pou Excel, li yo Done Split sèvis piblik pa sèlman ka ede ou fann done nan plizyè fich ki baze sou kolòn, men tou, ka fann done pa ranje konte.

Kutools pou Excel : ak plis pase 300 sou la men Excel ajoute-ins, gratis eseye ki pa gen okenn limit nan jou 60.

Si ou te enstale Kutools pou Excel, tanpri fè jan sa a:

1. Chwazi ranje a nan done ke ou vle fann.

2. Klike sou Kutools Plis > Fèy Travay > Done Split, gade D:

doc fann done pa kolòn 3

3. Nan Done Split nan Fèy Miltip bwat dyalòg, ou bezwen:

1). Chwazi Espesifik kolòn opsyon nan la Split ki baze sou seksyon, epi chwazi valè kolòn nan ki ou vle fann done ki baze sou nan lis la drop-desann. (Si done ou a gen Tèt epi ou vle mete yo nan chak Fèy travay fann nouvo, tanpri tcheke Done mwen gen tit opsyon.)

2). Lè sa a, ou ka presize non Fèy travay yo, anba a Nouvo non fich seksyon, presize fichye non yo règ ki soti nan la Règleman gout desann lis, ou ka ajoute a Prefiks or sifiks pou non yo fèy kòm byen.

3). Klike sou la OK bouton. Al gade nan Ekran:

doc fann done pa kolòn 4

4. Koulye a, done yo ap fann nan fichye miltip nan yon nouvo liv.

doc fann done pa kolòn 5

Klike sou Download Kutools pou Excel ak gratis jijman Koulye a,!


Done done nan plizyè fich ki baze sou kolòn ak Kutools pou Excel

Kutools pou Excel gen ladan plis pase 300 sou la meni Excel zouti. Gratis eseye pa gen okenn limit nan jou 60. Download gratis jijman an kounye a!


Atik ki gen rapò:

Ki jan yo fann done nan plizyè fich pa ranje konte?



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou 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 · 14 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 · 18 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 · 21 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 · 28 days 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?