Porady programu Excel: Podziel dane na wiele arkuszy/zeszytów na podstawie wartości kolumny
Podczas zarządzania dużymi zbiorami danych w programie Excel bardzo korzystne może być podzielenie danych na wiele arkuszy kalkulacyjnych w oparciu o określone wartości kolumn. Metoda ta poprawia nie tylko organizację danych, ale także zwiększa ich czytelność i ułatwia analizę danych.
Załóżmy, że masz duży rekord sprzedaży zawierający wiele wpisów, takich jak nazwa produktu i ilość sprzedana w pierwszym kwartale. Celem jest podzielenie tych danych na osobne arkusze w oparciu o nazwę każdego produktu, aby można było osobno analizować poszczególne wyniki sprzedaży.
Podziel dane na wiele arkuszy na podstawie wartości kolumny
Podziel dane na wiele skoroszytów na podstawie wartości kolumny za pomocą kodu VBA
Podziel dane na wiele arkuszy na podstawie wartości kolumny
Zwykle możesz najpierw posortować listę danych, a następnie skopiować je i wkleić jeden po drugim do innych nowych arkuszy. Jednak wielokrotne kopiowanie i wklejanie będzie wymagało cierpliwości. W tej sekcji przedstawimy dwie proste metody skutecznego rozwiązania tego zadania w programie Excel, oszczędzając czas i zmniejszając ryzyko błędów.
Podziel dane na wiele arkuszy na podstawie wartości kolumny za pomocą kodu VBA
1. Przytrzymaj przycisk ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
2. kliknij wstawka > Modułi wklej następujący kod w oknie modułu.
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 Sub3. Następnie naciśnij F5 klawisz, aby uruchomić kod, po czym pojawi się okno przypominające o wybraniu wiersza nagłówka, a następnie kliknij OK. Zobacz zrzut ekranu:
4. W drugim oknie zachęty wybierz dane kolumny, na podstawie których chcesz dokonać podziału, a następnie kliknij OK. Zobacz zrzut ekranu:
5. Wszystkie dane w aktywnym arkuszu są podzielone na wiele arkuszy na podstawie wartości kolumn. Powstałe arkusze są nazywane zgodnie z wartościami w podzielonych komórkach i umieszczane na końcu skoroszytu. Zobacz zrzut ekranu:
Podziel dane na wiele arkuszy kalkulacyjnych na podstawie wartości kolumny za pomocą Kutools for Excel
Kutools dla programu Excel zapewnia inteligentną funkcję – Podziel dane bezpośrednio do środowiska Excel. Dzielenie danych na wiele arkuszy nie jest już wyzwaniem. Nasze intuicyjne narzędzie automatycznie dzieli Twój zbiór danych na podstawie wybranej wartości kolumny lub liczby wierszy, zapewniając, że każda informacja znajdzie się dokładnie tam, gdzie jej potrzebujesz. Pożegnaj żmudne zadanie ręcznego porządkowania arkuszy kalkulacyjnych i skorzystaj z szybszego i bezbłędnego sposobu zarządzania danymi.
Po zainstalowaniu Kutools dla programu Excel, wybierz zakres danych i kliknij Kutools Plus > Podziel dane otworzyć Podziel dane na wiele arkuszy okno dialogowe.
- Wybierz Konkretna kolumna opcja w Podziel na podstawie i wybierz z listy rozwijanej wartość kolumny, na podstawie której chcesz podzielić dane.
- Jeśli Twoje dane mają nagłówki i chcesz wstawić je do każdego nowego podzielonego arkusza, sprawdź Moje dane mają nagłówki opcja. (Możesz określić liczbę wierszy nagłówka na podstawie swoich danych. Na przykład, jeśli Twoje dane zawierają dwa nagłówki, wpisz 2.)
- Następnie możesz określić nazwy podzielonych arkuszy roboczych w obszarze Nowa nazwa arkusza sekcji, określ regułę nazw arkuszy z listy rozwijanej Reguły, możesz dodać Prefiks or Przyrostek również dla nazw arkuszy.
- Kliknij OK przycisk. Zobacz zrzut ekranu:
Teraz dane w arkuszu zostaną podzielone na wiele arkuszy w nowym skoroszycie.
Podziel dane na wiele skoroszytów na podstawie wartości kolumny za pomocą kodu VBA
Czasami zamiast dzielić dane na wiele arkuszy, bardziej korzystne może być podzielenie danych na osobne skoroszyty na podstawie kolumny kluczowej. Oto przewodnik krok po kroku, jak używać kodu VBA do automatyzacji procesu dzielenia danych na wiele skoroszytów na podstawie określonej wartości kolumny.
1. Przytrzymaj przycisk ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
2. kliknij wstawka > Modułi wklej następujący kod w Okno modułu.
Sub SplitDataByColToWorkbooks()
' Updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWS As Workbook
Dim savePath As String
' Set the directory to save new workbooks
savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
Application.DisplayAlerts = False
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.Address(False, False)
titlerow = xTRg.Row
ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
For i = 2 To UBound(myarr)
Set xWS = Workbooks.Add
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"
xWS.Close SaveChanges:=False
Next i
ws.AutoFilterMode = False
Application.DisplayAlerts = True
ws.Activate
End Sub
3. Następnie naciśnij F5 klawisz, aby uruchomić kod, po czym pojawi się okno przypominające o wybraniu wiersza nagłówka, a następnie kliknij OK. Zobacz zrzut ekranu:
4. W drugim oknie zachęty wybierz dane kolumny, na podstawie których chcesz dokonać podziału, a następnie kliknij OK. Zobacz zrzut ekranu:
5. Po podzieleniu wszystkie dane w aktywnym arkuszu są dzielone na wiele skoroszytów na podstawie wartości kolumn. Wszystkie podzielone skoroszyty zostaną zapisane w określonym folderze. Zobacz zrzut ekranu:
Podobne artykuły:
- Podziel dane na wiele arkuszy według liczby wierszy
- Efektywne podzielenie dużego zakresu danych na wiele arkuszy programu Excel w oparciu o określoną liczbę wierszy może usprawnić zarządzanie danymi. Na przykład podzielenie zbioru danych co 5 wierszy na wiele arkuszy może ułatwić zarządzanie nim i jego organizację. W tym przewodniku przedstawiono dwie praktyczne metody szybkiego i łatwego wykonania tego zadania.
- Scal dwie lub więcej tabel w jedną na podstawie kluczowych kolumn
- Przypuśćmy, że masz trzy tabele w skoroszycie, teraz chcesz scalić te tabele w jedną tabelę na podstawie odpowiednich kolumn kluczowych, aby uzyskać wynik jak na poniższym zrzucie ekranu. Dla większości z nas może to być kłopotliwe zadanie, ale proszę się nie martwić, w tym artykule przedstawię kilka metod rozwiązania tego problemu.
- Podziel ciągi tekstowe według ograniczników na wiele wierszy
- Zwykle można użyć funkcji Tekst na kolumnę, aby podzielić zawartość komórki na wiele kolumn za pomocą określonego ogranicznika, takiego jak przecinek, kropka, średnik, ukośnik itp. Czasami jednak może być konieczne podzielenie rozdzielanej zawartości komórki na wiele wierszy i powtórz dane z innych kolumn, jak pokazano na poniższym zrzucie ekranu. Czy masz jakieś dobre sposoby radzenia sobie z tym zadaniem w Excelu? W tym samouczku przedstawimy kilka skutecznych metod wykonywania tej pracy w programie Excel.
- Podziel zawartość komórek wielowierszowych na oddzielne wiersze/kolumny
- Przypuśćmy, że masz wielowierszową zawartość komórki, która jest oddzielona Alt + Enter, a teraz musisz podzielić zawartość wielowierszową na oddzielone wiersze lub kolumny, co możesz zrobić? W tym artykule dowiesz się, jak szybko podzielić zawartość komórek wielowierszowych na oddzielne wiersze lub kolumny.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
