Contents
Microsoft Excel ji bo berhevkirina daneyan ji çend tabloyan (ji pelên cihêreng an ji pelên cihêreng) gelek amûr hene: girêdanên rasterast, fonksiyon NADIREKT (BNDXWNE), Power Query û Power Pivot pêvekên, hwd. Ji vî aliyê barîkatê, her tişt baş xuya dike.
Lê heke hûn ketin pirsgirêkek berevajî - belavkirina daneyan ji yek tabloyê li pelên cûda - wê hingê dê her tişt pir xemgîntir be. Heya nuha, mixabin, di cebilxaneya Excel de amûrên çêkirî yên şaristanî ji bo veqetandina daneya wusa tune. Ji ber vê yekê hûn neçar in ku di Visual Basic-ê de makroyek bikar bînin, an jî pêvek bi "paqijkirina pelê" piçûkek tomarkerê makro + Power Query bikar bînin.
Werin em ji nêz ve binihêrin ka ev çawa dikare were sepandin.
Formulasyona pirsgirêkê
Ji bo firotanê me wekî daneyên destpêkê tabloyek bi mezinahiya ji 5000 rêzan zêdetir heye:
Kar: Daneyên vê tabloyê li gorî bajaran li ser pelên cihê yên vê pirtûkê belav bikin. Ewan. di encam de, hûn hewce ne ku li ser her pelê tenê wan rêzan ji tabloya ku firot li bajarê têkildar lê bû, bistînin:
Amadekirin
Ji bo ku koda makro tevlihev nebe û ew bi qasî ku gengaz tê fêm kirin hêsan nebe, em çend gavên amadekariyê pêk bînin.
Yekem, tabloyek lêgerînê ya cihê biafirînin, ku stûnek yekane dê hemî bajarên ku hûn dixwazin pelên cûda ji bo wan biafirînin navnîş bike. Bê guman, dibe ku ev peldank hemî bajarên ku di daneya çavkaniyê de hene nehewîne, lê tenê yên ku ji bo me hewceyê raporan in. Rêya herî hêsan a çêkirina tabloyek weha karanîna fermanê ye Daneyên - Duberan jêbirin (Daneyên - Duberan jêbirin) ji bo kopiya stûnê Bajar an fonksiyonê UNIK (YEKANE) - heke we guhertoya herî dawî ya Excel 365 heye.
Ji ber ku pelên nû yên di Excel de berî (li milê çepê) yê heyî (berê) bi xwerû têne afirandin, di heman demê de maqûl e ku meriv bajarên di vê pelrêçayê de bi rêza xwarê (ji Z ber A) rêz bike - paşê piştî afirandinê, bajar kaxez dê li gorî alfabeyê bêne rêz kirin.
Ya duyemîn, пherdu tabloyên dînamîkî veguherînin ("aqilmend") da ku hêsantir bi wan re bixebite. Em fermanê bikar tînin Mal - Wek tabloyê format bikin (Mal - Format wek Tablo) an jî kurteya klavyeyê Ctrl+T. Li ser tabloya ku xuya dike Kêrker (Mînakkirin) em gazî wan bikin tablProdaji и TableCity, bi rêzê:
Rêbaz 1. Makro ji bo dabeşkirina bi pelan
Li ser tabloya Pêşkeftî pêşvebirinê (pêşdebir) pêl bişkoka bike Visual Basic an jî kurteya klavyeyê bikar bînin alt+F11. Di pencereya edîtorê makro ya ku vedibe de, modulek nû ya vala têxe nav menuyê Têxe - Modul û koda jêrîn li wir kopî bikin:
Sub Splitter() Ji bo Her şaneyek Di Rêjeyê de("таблГорода") Rêze("таблПродажи").Qada Fîltera Xweser:=3, Pîvan1:=cell.Nirxa Nirx("таблПродажи[#Hemû]").Celên Taybet(xlCellTypeVisible). Kopî Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Karûbarên hucreya Paşe("Данные").ShowAllData End Sub Li vir bi loop Ji bo Her… Paşê derbasbûna di nav hucreyên pelrêça de pêk anî TableCity, ku ji bo her bajarî tê fîltrekirin (rêbaz Fîltera Xweser) di tabloya firotanê ya orîjînal de û dûv re encaman li pelê ku nû hatî afirandin kopî bikin. Di rê de, pelika hatî afirandin bi heman navê bajêr tê guheztin û ji bo bedewiyê firehiya stûnan bixweber li ser tê zivirandin.
Hûn dikarin makroya çêkirî ya li Excel-ê li ser tabê bimeşînin pêşvebirinê pişkov Macros (Pêşvebir - Macro) an jî kurteya klavyeyê alt+F8.
Rêbaz 2. Pir pirsan di Power Query de çêbikin
Rêbaza berê, digel hemî tevlihevî û sadebûna xwe, xwedan kêmasiyek girîng e - pelên ku ji hêla makro ve hatine afirandin nayên nûve kirin dema ku di tabloya firotanê ya orjînal de guhertin têne çêkirin. Ger nûvekirina bi lez hewce be, wê hingê hûn neçar in ku pakêta VBA + Power Query bikar bînin, an bêtir, bi karanîna makroyek ne tenê pelên bi daneyên statîk, lê pirsên Power Query nûvekirî biafirînin.
Makro di vê rewşê de qismî mîna ya berê ye (di heman demê de çerxek jî heye Ji bo Her… Paşê ji bo dubarekirina bajarên di pelrêçê de), lê di hundurê lûkê de êdî dê fîlterkirin û kopîkirin tune be, lê pirsek Power Query çêbike û encamên wê li pelek nû bar bike:
Sub Splitter2() Ji bo Her şaneyek Di Rêzeyê de ("Tabloya Bajar") ActiveWorkbook.Queries.Navê lê zêde bike:=cell.Nirx, Formula:= _ "bila" & Chr(13) & "" & Chr(10) & " Çavkanî = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Naverok]," & Chr(13) & "" & Chr(10) & " #""Cûreya Guherîn" = Table.TransformColumnTypes(Çavkanî , {{""Kategorî"", nivîsê binivîsin}, {""Nav", nivîsê binivîsin}, {""Bajar"", nivîsê binivîsin}, {""Rêveber"", nivîsê binivîsin}, {""Danûstandin" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rêzên bi fîlter hatine sepandin" = Table.Se " & _ "lectRows(#""Cîpa guherî"", her yek ([Bajar] = """ & şan.Nirx & """))" & Chr(13) & "" & Chr(10) & "li " & Chr(13) & "" & Chr(10) & " #""Rêzên bi fîlter hatine sepandin""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Çavkanî:= _ "OLEDB; Pêşkêşker =Microsoft.Mashup.OleDb.1;Çavkaniya daneyan=$Workbook$;Cih=" & cell.Nirx & ";Taybetmendiyên Berfireh=""""" _ , Mebest:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *JI [" & cell.Nirx & "]") .RewNumbers = Derew .FillAdjacentFormulas = Derew .PreserveFormatting = Rast .RefreshOnFileOpen = Şaş SaveData = Rast .AdjustColumnWidth = Rast .RefreshPeriod = 0 .PreserveColumnInfo = Rast .ListObject.DisplayName = cell.Value .Banga BackgroundQuery nûve bike:=Dawiya Derew Bi ActiveSheet Endam.Name = Hucreya NextV. Piştî destpêkirina wê, em ê heman pelan li gorî bajaran bibînin, lê pirsên Power Query yên jixwe hatine afirandin dê wan çêbikin:
Bi her guheztina daneyên çavkaniyê re, ew ê bes be ku tabloya têkildar bi bişkoja rastê ya mişkê nûve bikin - ferman Nûvekirin & Save (Hênikkirin) an jî bi karanîna bişkojkê bi yekcarî hemî bajaran nûve bikin Hemî nûve bikin tab Jimare (Daneyên - Hemî nûve bikin).
- Makro çi ne, çawa têne çêkirin û bikar anîn
- Pelên pirtûka xebatê wekî pelên cûda tomar dikin
- Daneyên ji hemî pelên pirtûkê di yek tabloyê de berhev dike