Mukati
Microsoft Excel ine maturusi mazhinji ekuunganidza data kubva kumatafura akati wandei (kubva pamashiti akasiyana kana kubva kune akasiyana mafaera): zvakanangana zvinongedzo, basa. INDIRECT (ZVINOKOSHA), Power Query uye Power Pivot add-ons, etc. Kubva kune rumwe rutivi rwechivharo, zvinhu zvose zvinotaridzika zvakanaka.
Asi kana iwe ukasangana nedambudziko rakasiyana - kuparadzira data kubva kune imwe tafura kuenda kune akasiyana mashizha - ipapo zvose zvichava zvakanyanya kusuruvara. Parizvino, hapana maturusi akavakirwa-mukati maturusi ekuparadzanisa data rakadaro mune arsenal yeExcel, zvinosiririsa. Saka iwe uchafanirwa kushandisa macro muVisual Basic, kana kushandisa iyo macro rekodhi + Power Query musanganiswa nediki "faira refinement" mushure.
Ngatinyatsoongororai kuti izvi zvingaitwa sei.
Kugadziriswa kwedambudziko
Isu tine sekutanga data tafura yakadaro ine saizi inodarika mazana mashanu mitsara yekutengesa:
Basa: kugovera data kubva patafura iyi neguta pamapepa akasiyana ebhuku rino. Avo. pane zvakabuda, iwe unofanirwa kuwana pane imwe neimwe pepa chete iyo mitsara kubva patafura iyo kutengeswa kwaive muguta rinoenderana:
Gadzirira
Kuti urege kuomesera macro kodhi uye kuita kuti ive nyore kunzwisisa sezvinobvira, ngatiite akati wandei ekugadzirira matanho.
Kutanga, a gadzira tafura yekutarisa yakasiyana, uko koramu imwe chete ichanyora maguta ese aunoda kugadzira mapepa akaparadzana. Ehe, dhairekitori iri rinogona kunge risina maguta ese aripo mune iyo sosi data, asi iwo chete ayo atinoda mishumo. Nzira iri nyore yekugadzira tafura yakadaro kushandisa murairo Data - Bvisa Duplicates (Data - Bvisa zvakapetwa) nokuda kwekopi kopi manzwiro kana basa UNIK (ZAKASIYANA) - kana iwe uine yazvino vhezheni yeExcel 365.
Sezvo mapepa matsva muExcel akagadzirwa nekusarudzika pamberi (kuruboshwe kwe) yazvino (yapfuura) imwe, zvine musorowo kuronga maguta ari mune iri dhairekitori mukudzika kurongeka (kubva paZ kuenda kuA) - ipapo mushure mekusikwa, guta. mapepa acharongwa nemaarufabheti.
Chechipiri, iyo пshandura matafura ari maviri kuti ave dynamic (“akachenjera”) kuti zvive nyore kushanda navo. Isu tinoshandisa murairo Kumba – Fomati setafura (Kumba - Fomati seTafura) kana nzira yekudimbudzira keyboard Ctrl+T. Pane tab inooneka Constructor (Dhizaini) ngativadaidze tablProdaji и TableCity, zvichiteerana:
Nzira 1. Macro yekuparadzanisa nemashizha
Pa Advanced tabo yokuvaka (Mugadziri) tinya pane bhatani Visual Basic kana kushandisa nzira yekudimbudzira kiibhodhi alt+F11. Mune iyo macro edhita hwindo rinovhura, isa nyowani isina chinhu module kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:
Sub Splitter() Yesero rega rega MuRange("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Kopi). Sheets.Add ActiveSheet.Namira ActiveSheet.Zita = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Mashizha ekushanda("Данные").ShowAllData End Sub Pano ne loop Kune Imwe neimwe… Inotevera shandisa ndima kuburikidza nemasero edhairekitori TableCity, uko kune rimwe nerimwe guta rinosefa (method AutoFilter) mutafura yepakutanga yekutengesa uye wozokopa zvawanikwa kune pepa richangogadzirwa. Panzira, pepa rakagadzirwa rinotumidzwa zita rimwe chete reguta uye auto-fitting upamhi hwemakoramu ekunaka kunovhurwa pairi.
Iwe unogona kumhanya iyo yakagadzirwa macro muExcel pane iyo tab yokuvaka bhatani macro (Mugadziri - Macros) kana nzira yekudimbudzira keyboard alt+F8.
Nzira 2. Gadzira mibvunzo yakawanda muMasimba Query
Nzira yakapfuura, nokuda kwekubatana kwayo kwose uye nyore, ine chidimbu chakakosha - mapepa akagadzirwa ne macro haagadziriswi kana kuchinja kunoitwa mutafura yepakutanga yekutengesa. Kana kugadzirisa panhunzi kuchidikanwa, saka uchafanirwa kushandisa VBA + Power Query bundle, kana kuti, gadzira uchishandisa macro kwete masheets ane static data, asi yakagadziridzwa Power Query mibvunzo.
Iyo macro mune iyi kesi yakafanana neyakapfuura (ine zvakare ine kutenderera Kune Imwe neimwe… Inotevera kudzokorora pamusoro pemaguta ari mudhairekitori), asi mukati meiyo loop hapachazove nekusefa nekukopa, asi kugadzira Power Query mubvunzo uye kurodha mibairo yayo kune pepa idzva:
Sub Splitter2() Yesero rega rega MuRange("tafura yeguta") ActiveWorkbook.Queries.Wedzera Zita:=cell.Value, Formula:= _ "rega" & Chr(13) & "" & Chr(10) & " Kwakabva = Excel.CurrentWorkbook(){[Zita=""TableSales""][Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Mabviro , {{""Category", type text}, {""Zita"", type text}, {""Guta", type text}, {""Manager"", type text}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Mitsetse ine sefa yakaiswa"" = Table.Se " & _ "lectRows(#""Changed type"", imwe neimwe ([Guta] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Mitsara ine sefa yakaiswa""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Nzvimbo=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *KUBVA [" & cell.Value & "]") .RowNumbers = Nhema .FillAdjacentFormulas = Nhema .PreserveFormatting = True .RefreshOnFileOpen = Nhema .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells =FalPassword. SaveData = Chokwadi .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True Mushure mekutangwa kwayo, isu tichaona iwo mashiti akafanana neguta, asi yakatogadzirwa Power Query mibvunzo inovaumba:
Nezve chero shanduko mune iyo sosi data, ichave yakakwana kugadzirisa tafura inoenderana nerudyi bhatani bhatani - iwo murairo Gadziridza & Chengeta (Refresh) kana kugadzirisa maguta ese kamwechete muhuwandu uchishandisa bhatani Gadzirisa Zvese Tab Data (Data - Zorora Zvese).
- Chii chinonzi macros, kugadzira uye kuishandisa
- Kuchengetedza mapepa ebhuku rekushanda semafaira akasiyana
- Kuunganidza data kubva pamapepa ese ebhuku mune imwe tafura