Vidakuzi vinatusaidia kutoa huduma zetu. Kwa kutumia huduma zetu, unakubaliana na matumizi yetu ya kuki.
Kidokezo: Lugha zingine ni Google-Ilitafsiriwa. Unaweza kutembelea English toleo la kiungo hiki.
Ingia
x
or
x
x
Daftari
x

or

Jinsi ya kupasua data katika karatasi nyingi za kazi kulingana na safu ya Excel?

Ikiwa una fomu ya karatasi yenye safu kubwa za data, na sasa, unahitaji kupasua data katika karatasi nyingi za kazi kulingana na jina safu (tazama skrini ifuatayo), na majina yameingia kwa nasibu. Labda unaweza kuwatenga kwanza, na kisha ukipakue na kuziweka moja kwa moja kwenye karatasi zingine za kazi. Lakini hii itahitaji uvumilivu wako nakala na kushikilia mara kwa mara. Leo, nitazungumzia kuhusu mbinu za haraka za kutatua kazi hii.

doc kupasua data na nguzo 1

Piga data katika karatasi nyingi za kazi kulingana na safu na msimbo wa VBA

Piga data katika karatasi nyingi za kazi kulingana na safu na Kutools kwa Excel


Piga data katika karatasi nyingi za kazi kulingana na safu maalum au safu za kuhesabu katika karatasi:

Ikiwa unataka kupasua karatasi kubwa katika karatasi nyingi kulingana na data maalum ya safu au safu za safu, Kutools kwa Excel's Split Data kipengele kinaweza kukusaidia kutatua kazi hii kwa haraka na kwa urahisi.

doc kupasua data na nguzo 6

Kutools kwa Excel: na zaidi ya 200 handy Excel inserts, huru kujaribu na hakuna kikomo katika siku 60. Pakua na jaribio la bure Sasa!


Piga data katika karatasi nyingi za kazi kulingana na safu na msimbo wa VBA


Ikiwa unataka kupasua data kulingana na thamani ya safu ya haraka na kwa moja kwa moja, kanuni ya VBA ifuatayo ni chaguo nzuri. Tafadhali fanya kama hii:

1. Weka chini ALT + F11 funguo za kufungua Microsoft Visual Msingi kwa Maombi dirisha.

2. Bonyeza Ingiza > Modules, na ushirike nambari ifuatayo kwenye Dirisha la Module.

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. Kisha, bonyeza F5 ufunguo wa kuendesha msimbo, na sanduku la haraka linakuja kukukumbusha kuchagua safu ya kichwa, angalia skrini:

doc kupasua data na nguzo 7

4. Na kisha, bofya OK kifungo, na katika sanduku la pili la haraka, tafadhali chagua data ya safu ya unataka kupasuliwa kulingana na, ona skrini:

doc kupasua data na nguzo 8

5. Kisha, bofya OK, na data zote katika karatasi ya kazi hugawanyika katika karatasi nyingi za kazi na thamani ya safu. Na karatasi za kupasuliwa zinajulikana na majina ya seli ya mgawanyiko. Angalia skrini:

doc kupasua data na nguzo 2

Kumbuka: Karatasi za kupasuliwa zinawekwa mwishoni mwa kitabu cha kazi ambapo karatasi ya kazi iko.


Piga data katika karatasi nyingi za kazi kulingana na safu na Kutools kwa Excel

Kama mtangulizi wa Excel, msimbo huu wa VBA mrefu ni vigumu sana kwetu, na wengi wetu hata hawajui jinsi ya kurekebisha kanuni kama mahitaji yetu.

Hapa, nitakuanzisha chombo cha multifunctional -Kutools kwa Excel, yake Split Data huduma sio tu inaweza kukusaidia kupasua data katika karatasi nyingi za kazi kulingana na safu, lakini pia inaweza kupasua data kwa hesabu safu.

Kutools kwa Excel : na zaidi ya 300 handy Excel add-ins, bure kujaribu na hakuna kikomo katika siku 60.

Ikiwa umeweka Kutools kwa Excel, tafadhali fanya ifuatavyo:

1. Chagua data mbalimbali unayotaka kugawanya.

2. Bonyeza Kutools Zaidi > Karatasi ya > Split Data, angalia skrini:

doc kupasua data na nguzo 3

3. Ndani ya Split Data katika Fashihi nyingi za Kazi sanduku la mazungumzo, unahitaji:

1). Chagua Safu maalum chaguo katika Split msingi sehemu, na uchague thamani ya safu ambayo unataka kupasua data kulingana na orodha ya kushuka. (Kama data yako ina kichwa na unataka kuingiza kwenye kila karatasi mpya ya kupasuliwa, tafadhali angalia Data yangu ina vichwa vya habari chaguo.)

2). Kisha unaweza kutaja majina ya karatasi ya kupasuliwa, chini ya Jina jipya la majarida sehemu, bayana majina ya majarida ya kazi kutoka kwa Sheria tone chini, unaweza kuongeza Kiambatisho or Suffix kwa majina ya karatasi pia.

3). Bofya OK kifungo. Angalia skrini:

doc kupasua data na nguzo 4

4. Sasa data imegawanywa katika karatasi nyingi za kazi katika kitabu cha kazi mpya.

doc kupasua data na nguzo 5

Bofya ili kupakua Kutools kwa Excel na jaribio la bure Sasa!


Piga data katika karatasi nyingi za kazi kulingana na safu na Kutools kwa Excel

Kutools kwa Excel inajumuisha zana zaidi ya 300 Handy Excel. Huru ya kujaribu bila kupunguzwa kwa siku za 60. Pakua jaribio la bure sasa!


Makala inayohusiana:

Jinsi ya kupasua data katika karatasi nyingi za kazi kwa hesabu safu?



Zilizopendekeza Vifaa vya Uzalishaji

Tabia ya Ofisi

nyota ya dhahabu1 Kuleta tabo za Handy kwa Excel na programu nyingine za Ofisi, kama Chrome, Firefox na Internet Explorer mpya.

Kutools kwa Excel

nyota ya dhahabu1 Kushangaza! Ongeza uzalishaji wako katika dakika ya 5. Hauna haja ujuzi wowote maalum, ila saa mbili kila siku!

nyota ya dhahabu1 Features mpya ya 300 kwa Excel, Fanya Excel Rahisi Rahisi na Nguvu:

  • Unganisha Kiini / Rangi / nguzo bila kupoteza Data.
  • Kuchanganya na Kuunganisha Karatasi nyingi na Vitabu vya Kazi.
  • Linganisha Mipangilio, Nakili Ranges nyingi, Badilisha Nakala hadi Tarehe, Kitengo na Ubadilishaji wa Fedha.
  • Hesabu na Rangi, Vipindi vya Paging, Chagua cha Juu na cha Filter Super,
  • Chagua zaidi / Ingiza / Futa / Nakala / Fomu / Kiungo / Maoni / Vitabu vya Kazi / Vifungu vya Kazi ...

Screen shot ya Kutools kwa 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.
    Rudi Miller · 5 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 · 1 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?
  • To post as a guest, your comment is unpublished.
    jose · 1 months ago
    can someone help please im using this but i keep getting to many columns. i have to keep deleting rows every time i use this.

    This is what im using


    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:AN1"
    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.
    avinash · 1 months ago
    Thanks for VBA, it works great. In addition to that question, I have column which is dependent. So when i select some value my column values get changed hence I need vba solution to replace earlier split worksheet to replace with new value of columns. Can anyone help me out.?

    Thanks in advance
  • To post as a guest, your comment is unpublished.
    JP Tontegode · 1 months ago
    Is there a way to have the macro create a separate spreadsheet for each new tab instead of just adding a tab into the current worksheet? Thanks!