Nilalaman
Ang Microsoft Excel ay may maraming mga tool para sa pagkolekta ng data mula sa ilang mga talahanayan (mula sa iba't ibang mga sheet o mula sa iba't ibang mga file): mga direktang link, function HINDI DIREKTA (INDIRECT), Power Query at Power Pivot add-on, atbp. Mula sa bahaging ito ng barikada, lahat ay mukhang maayos.
Ngunit kung magkakaroon ka ng kabaligtaran na problema - pagkalat ng data mula sa isang talahanayan patungo sa iba't ibang mga sheet - kung gayon ang lahat ay magiging mas malungkot. Sa ngayon, walang sibilisadong built-in na tool para sa naturang paghihiwalay ng data sa arsenal ng Excel, sa kasamaang-palad. Kaya kakailanganin mong gumamit ng macro sa Visual Basic, o gamitin ang macro recorder + Power Query na kumbinasyon na may kaunting "pagpipino ng file" pagkatapos.
Tingnan natin kung paano ito maipapatupad.
Pagbubuo ng problema
Mayroon kaming paunang data tulad ng isang talahanayan na may sukat na higit sa 5000 mga hilera para sa mga benta:
Gawain: upang ipamahagi ang data mula sa talahanayang ito ayon sa lungsod sa magkahiwalay na mga sheet ng aklat na ito. Yung. sa output, kailangan mong makuha sa bawat sheet lamang ang mga hilera mula sa talahanayan kung saan ang pagbebenta ay nasa kaukulang lungsod:
Maghanda
Upang hindi gawing kumplikado ang macro code at gawin itong madaling maunawaan hangga't maaari, magsagawa tayo ng ilang hakbang sa paghahanda.
Una, ang gumawa ng hiwalay na lookup table, kung saan ililista ng isang column ang lahat ng lungsod kung saan mo gustong gumawa ng hiwalay na mga sheet. Siyempre, ang direktoryo na ito ay maaaring hindi naglalaman ng lahat ng mga lungsod na nasa source data, ngunit ang mga lamang kung saan kailangan namin ng mga ulat. Ang pinakamadaling paraan upang lumikha ng naturang talahanayan ay ang paggamit ng command Data – Alisin ang mga Duplicate (Data — Alisin ang mga duplicate) para sa kopya ng hanay lungsod o function UNIK (NATATANGI) – kung mayroon kang pinakabagong bersyon ng Excel 365.
Dahil ang mga bagong sheet sa Excel ay nilikha bilang default bago (sa kaliwa ng) ang kasalukuyang (nakaraang) isa, makatuwiran din na pag-uri-uriin ang mga lungsod sa direktoryong ito sa pababang pagkakasunud-sunod (mula Z hanggang A) - pagkatapos ay pagkatapos ng paglikha, ang lungsod ang mga sheet ay aayusin ayon sa alpabeto.
Pangalawa, ang пi-convert ang parehong mga talahanayan sa dynamic (“matalino”) upang gawing mas madali ang pakikipagtulungan sa kanila. Ginagamit namin ang utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan) o keyboard shortcut Ctrl+T. Sa tab na lalabas Tagagawa (Disenyo) tawagan natin sila tablProdaji и TableCity, ayon sa pagkakabanggit:
Paraan 1. Macro para sa paghahati ayon sa mga sheet
Sa Advanced tab developer (Developer) i-click ang pindutan Visual Basic o gumamit ng keyboard shortcut Alt+F11. Sa window ng macro editor na bubukas, magpasok ng bagong walang laman na module sa pamamagitan ng menu Ipasok – Module at kopyahin ang sumusunod na code doon:
Sub Splitter() Para sa Bawat cell na Nasa Saklaw("таблГорода") Range("таблПродажи"). AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#Lahat]").SpecialCells(xlCellTypeVisible). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Dito na may loop Para sa Bawat … Susunod ipinatupad ang pagpasa sa mga cell ng direktoryo TableCity, kung saan para sa bawat lungsod ito ay sinasala (paraan AutoFilter) sa orihinal na talahanayan ng mga benta at pagkatapos ay kopyahin ang mga resulta sa bagong likhang sheet. Sa kahabaan ng paraan, ang ginawang sheet ay pinalitan ng pangalan sa parehong pangalan ng lungsod at ang auto-fitting sa lapad ng mga column para sa kagandahan ay naka-on dito.
Maaari mong patakbuhin ang ginawang macro sa Excel sa tab developer butones Macros (Developer — Macros) o keyboard shortcut Alt+F8.
Paraan 2. Gumawa ng maraming query sa Power Query
Ang nakaraang pamamaraan, para sa lahat ng pagiging compact at pagiging simple nito, ay may isang makabuluhang disbentaha - ang mga sheet na ginawa ng macro ay hindi ina-update kapag ang mga pagbabago ay ginawa sa orihinal na talahanayan ng mga benta. Kung kinakailangan ang mabilisang pag-update, kakailanganin mong gamitin ang VBA + Power Query bundle, o sa halip, gumawa gamit ang isang macro hindi lamang mga sheet na may static na data, ngunit na-update na mga query sa Power Query.
Ang macro sa kasong ito ay bahagyang katulad ng nauna (mayroon din itong cycle Para sa Bawat … Susunod upang umulit sa mga lungsod sa direktoryo), ngunit sa loob ng loop ay hindi na magkakaroon ng pag-filter at pagkopya, ngunit ang paggawa ng Power Query query at pag-upload ng mga resulta nito sa isang bagong sheet:
Sub Splitter2() Para sa Bawat cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Pangalan"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal petsa "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Row with filter applied"" = Table.Se " & _ "lectRows(#""Binago ang uri"", bawat isa ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Mga hilera na may filter na inilapat""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *MULA sa [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells . =SavePasssword SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub Pagkatapos ng paglulunsad nito, makikita natin ang parehong mga sheet ayon sa lungsod, ngunit bubuo sa kanila ang mga nagawa nang Power Query na query:
Sa anumang mga pagbabago sa source data, sapat na upang i-update ang kaukulang talahanayan gamit ang kanang pindutan ng mouse - ang command I-update at I-save (Refresh) o i-update ang lahat ng mga lungsod nang sabay-sabay nang maramihan gamit ang button I-update ang Lahat tab data (Data — I-refresh Lahat).
- Ano ang mga macro, kung paano lumikha at gamitin ang mga ito
- Sine-save ang mga workbook sheet bilang hiwalay na mga file
- Pagkolekta ng data mula sa lahat ng mga sheet ng libro sa isang talahanayan