Padalinkite „Excel“ lapą į kelis failus pagal stulpelį naudodami VBA

Ar turite daug duomenų „Excel“ lape ir jums reikia tą lapą paskirstyti keliais lapais, remiantis kai kuriais stulpelio duomenimis? Tai labai paprasta užduotis, tačiau užima daug laiko.

Pavyzdžiui, aš turiu šiuos duomenis. Šie duomenys turi stulpelį pavadinimu Data, rašytojas ir Pavadinimas. Rašytojo stulpelyje yra atitinkamo pavadinimo rašytojo vardas. Noriu gauti kiekvieno rašytojo duomenis atskiruose lapuose.

Norėdami tai padaryti rankiniu būdu, turiu atlikti šiuos veiksmus:

Filtruokite vieną pavadinimą
Nukopijuokite filtruotus duomenis
Pridėkite lapą
Įklijuokite duomenis
Pervardykite lapą
Pakartokite visus aukščiau nurodytus 5 veiksmus kiekvienam.

Šiame pavyzdyje turiu tik tris vardus. Įsivaizduokite, jei turite 100 pavadinimų. Kaip padalintumėte duomenis į skirtingus lapus? Tai užtruks daug laiko ir taip pat nusausins jus.
Norėdami automatizuoti aukščiau aprašytą lapo skaidymo į kelis lapus procesą, atlikite šiuos veiksmus.

Paspauskite Alt+F11. Tai atidarys „Excel“ VB redaktorių
Pridėti naują modulį
Nukopijuokite žemiau esantį kodą modulyje.

 Sub SplitIntoSheets () Su Application .ScreenUpdating = False .DisplayAlerts = False End With ThisWorkbook.Activate Sheet1.Activate 'Clearing filter if Act On' Error Resume Next Sheet1.ShowAllData On Error GoTo 0 Dim lsrClm As Long Dim lstRow As Long 'skaičiuojant paskutinę naudojamą eilutę lstRow = Cells (Rows.Count, 1) .End (xlUp) .Row Dim unikalus kaip diapazonas Dim clm Kaip eilutė, clmNo As Long On Error GoTo handler clm = Application.InputBox ("Iš kurio stulpelio norite kurti failus" & vbCrLf & "Pvz. A, B, C, AB, ZA ir kt. ") ClmNo = Diapazonas (clm &" 1 "). Stulpelių rinkinio unikalumai = Diapazonas (clm &" 2: "& clm & lstRow)" Skambinimas Pašalinti dublikatus, kad gautumėte unikalių pavadinimų rinkinį uniques = RemoveDuplicates (unikalūs) Skambinkite CreateSheets (unikalūs, clmNo) Naudodami programą .ScreenUpdating = Tiesa .DisplayAlerts = Tiesa .AlertBeforeOverwriting = Tiesa .Calculation = xlCalculationAutomatic End With Sheet1.Activate MsgBox "Gerai padaryta!" Išeiti iš antrinių duomenų.ShowAllData tvarkytojas: su programa .ScreenUpdating = Tiesa .DisplayAlerts = Tiesa .AlertBeforeOverwriting = Tiesa .Calculation = xlCalculationAutomatic End With End Sub Funkcija RemoveDuplicates (unikalus kaip diapazonas) kaip diapazonas ThisWorkbook.Activate Sheets.Add On Error Resume Next ActiveSheet.Name = "uniques" Sheets ("uniques"). Activate On Error GoTo 0 unques.Copy Cells (2, 1) .Activate ActiveCell.PasteSpecial xlPasteValues Range ("A1") .Value = "uniques" Dim lstRow As Long lstRow = Ląstelės (Rows.Count, 1) .End (xlUp) .Row Range ("A2: A" & lstRow). Pasirinkite ActiveSheet.Range (Selection.Address) .RemoveDuplicates Columns : = 1, antraštė: = xl Sub CreateSheets (unikalus kaip diapazonas, clmNo kaip ilgai) Dim lstClm Kaip ilgai Dim lstRow Kiek laiko kiekvienam unikaliam unikaliam lapui 1. Aktyvuokite lstRow = Ląstelės (Rows.Count, 1). End (xlUp) .Row lstClm = Ląstelės (1, Columns.Count) .End (xlToLeft) .Column Dim dataSet As Range Set dataSet = Range (Cells (1, 1), Cells (lstRow, lstClm)) dataSet.AutoFilter field: = clmNo, Criteria1: = unique.Value lstRow = Ląstelės (Rows.Count, 1). End ( xlUp). Row lstClm = Ląstelės (1, Columns.Count). End (xlToLeft). Column Debug.Print lstRow; „lstClm Set dataSet = Range“ (langeliai (1, 1), ląstelės (lstRow, lstClm)) dataSet.Copy Sheets.Add ActiveSheet.Name = unique.Value2 ActiveCell.PasteSpecial xlPasteAll Next unikalus pabaiga

Kai bėgsi „SplitIntoSheets“ () procedūra, lapas bus padalintas į kelis lapus, atsižvelgiant į nurodytą stulpelį. Galite pridėti mygtuką lape ir priskirti jam šią makrokomandą.

Kaip tai veikia
Aukščiau pateiktas kodas turi dvi procedūras ir vieną funkciją. Yra dvi procedūros „SplitIntoSheets“ (), „CreateSheets“ (unikalus kaip diapazonas, „clmNo As Long“) ir viena funkcija yra RemoveDuplicates (unikalus kaip diapazonas) kaip diapazonas.

Pirmoji procedūra yra „SplitIntoSheets“ (). Tai yra pagrindinė procedūra. Ši procedūra nustato kintamuosius ir Pašalinti dublikatus gauti unikalius pavadinimus iš nurodyto stulpelio ir tada perduoti tuos pavadinimus „CreateSheets“ lakštų kūrimui.

Pašalinti dublikatus priima vieną argumentą, kuris yra diapazonas, kuriame yra pavadinimas. Pašalina dublikatus iš jų ir grąžina diapazono objektą, kuriame yra unikalūs pavadinimai.

Dabar „CreateSheets“ vadinamas. Tam reikia dviejų argumentų. Pirma, unikalūs pavadinimai ir antra skiltis Nr. iš kurių mes pateiksime duomenis. Dabar „CreateSheets“ paima kiekvieną pavadinimą iš unikalių ir filtruoja nurodytą stulpelio numerį pagal kiekvieną pavadinimą. Nukopijuoja filtruotus duomenis, prideda lapą ir įklijuoja duomenis ten. Jūsų duomenys per kelias sekundes suskirstomi į skirtingus lapus.

Failą galite atsisiųsti čia.
Padalinti į lakštus

Kaip naudoti failą:

- Nukopijuokite duomenis į „Sheet1“. Įsitikinkite, kad jis prasideda nuo A1.

- Spustelėkite mygtuką Padalinti į lapus
- Įveskite stulpelio raidę, iš kurios norite atskirti. Spustelėkite Gerai.

- Pamatysite tokį raginimą. Jūsų lapas yra padalintas.

Tikiuosi, kad straipsnis apie duomenų padalijimą į atskirus lapus jums buvo naudingas. Jei turite kokių nors abejonių dėl šios ar kitos „Excel“ funkcijos, nedvejodami paklauskite jos komentarų skiltyje žemiau.