Kugawa tebulo kukhala mapepala

Microsoft Excel ili ndi zida zambiri zosonkhanitsira deta kuchokera kumatebulo angapo (kuchokera pamasamba osiyanasiyana kapena kuchokera ku mafayilo osiyanasiyana): maulalo achindunji, ntchito KUMALO (KODI), Power Query ndi Power Pivot add-ons, etc. Kuchokera kumbali iyi ya barricade, chirichonse chikuwoneka bwino.

Koma ngati mutakumana ndi vuto losiyana - kufalitsa deta kuchokera pa tebulo limodzi kupita ku mapepala osiyanasiyana - ndiye kuti zonse zidzakhala zomvetsa chisoni kwambiri. Pakadali pano, palibe zida zotukuka zomwe zimapangidwira zolekanitsa deta mu zida za Excel, mwatsoka. Chifukwa chake muyenera kugwiritsa ntchito macro mu Visual Basic, kapena gwiritsani ntchito chojambulira chachikulu + Power Query kuphatikiza ndi "kukonza mafayilo" pang'ono pambuyo pake.

Tiyeni tione bwinobwino mmene zimenezi zingakwaniritsidwire.

Kupanga vuto

Tili ndi data yoyambira tebulo lokhala ndi mizere yopitilira 5000 yogulitsa:

Ntchito: kugawa deta kuchokera pa tebulo ili ndi mzinda pamasamba osiyana a bukhuli. Iwo. pazotulutsa, muyenera kupeza pa pepala lililonse mizere yokhayo kuchokera patebulo pomwe kugulitsa kunali mumzinda wofananira:

Konzani

Kuti tisasokoneze ma macro code ndikupangitsa kuti ikhale yosavuta kumvetsetsa momwe tingathere, tiyeni tichitepo njira zingapo zokonzekera.

Choyamba, a pangani tebulo loyang'ana lapadera, pomwe gawo limodzi lidzalemba mizinda yonse yomwe mukufuna kupanga mapepala osiyana. Zoonadi, bukhuli silingakhale ndi mizinda yonse yomwe ilipo muzinthu zoyambira, koma zokhazo zomwe tikufuna malipoti. Njira yosavuta yopangira tebulo lotere ndikugwiritsa ntchito lamulo Deta - Chotsani Zobwerezedwa (Deta - Chotsani zobwereza) kwa kope lazambiri maganizo kapena ntchito UNIK (CHIYAMBIRA) - ngati muli ndi mtundu waposachedwa wa Excel 365.

Popeza mapepala atsopano mu Excel amapangidwa mwachisawawa kale (kumanzere kwa) komweko (m'mbuyomo), ndizomveka kuyika mizinda yomwe ili mu bukhuli mwadongosolo lotsika (kuchokera ku Z mpaka A) - kenako pambuyo pa kulenga, mzindawu. mapepala adzakonzedwa motsatira zilembo.

Chachiwiri, пsinthani ma tebulo onse kukhala osinthika (“wanzeru”) kuti zikhale zosavuta kugwira nawo ntchito. Timagwiritsa ntchito lamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table) kapena njira yachidule ya kiyibodi Ctrl+T. Pa tabu yomwe ikuwoneka Constructor (Kapangidwe) tiyeni tiwaitane tablProdaji и TableCitymotsatana:

Njira 1. Macro yogawa ndi mapepala

Pa Advanced tabu Woyambitsa (Wolemba Mapulogalamu) dinani batani Zooneka Basic kapena gwiritsani ntchito njira yachidule ya kiyibodi alt+F11. Pazenera la macro editor lomwe limatsegulidwa, ikani gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module ndi kukopera khodi ili:

Sub Splitter() Pa selo Lililonse Mu Mulingo("таблГорода") Mtundu("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCell(xlCellTypeVisible).Koperani). Mapepala.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

Apa ndi lupu Kwa Iliyonse ... Chotsatira adakhazikitsa ndimeyi kudzera m'maselo a chikwatu TableCity, komwe kwa mzinda uliwonse umasefedwa (njira Zosintha) patebulo loyambirira lazogulitsa ndikukopera zotsatira ku pepala lomwe langopangidwa kumene. Panjira, pepala lopangidwa limasinthidwa kukhala dzina lomwelo la mzindawo ndikudziyimira pawokha m'lifupi mwa mizati yokongola imatsegulidwa.

Mutha kuyendetsa macro opangidwa mu Excel pa tabu Woyambitsa batani Macros (Wopanga - Macros) kapena njira yachidule ya kiyibodi alt+F8.

Njira 2. Pangani mafunso angapo mu Power Query

Njira yapitayi, chifukwa cha kuphatikizika kwake konse ndi kuphweka, ili ndi zovuta zazikulu - mapepala opangidwa ndi macro samasinthidwa pamene kusintha kumapangidwa mu tebulo loyambirira la malonda. Ngati kukonzanso pa ntchentche kuli kofunikira, ndiye kuti muyenera kugwiritsa ntchito VBA + Power Query mtolo, kapena m'malo mwake, pangani pogwiritsa ntchito macro osati mapepala okhala ndi deta yosasunthika, koma mafunso osinthidwa a Power Query.

Ma macro pankhaniyi ndi ofanana pang'ono ndi yapitayo (ilinso ndi kuzungulira Kwa Iliyonse ... Chotsatira kubwereza mizinda yomwe ili m'ndandanda), koma mkati mwa loop sipadzakhalanso kusefa ndi kukopera, koma kupanga funso la Power Query ndikuyika zotsatira zake papepala latsopano:

Sub Splitter2() Pa selo Lililonse Mu Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Gwero = Excel.CurrentWorkbook(){[Name=""TableSales""][Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category", type text}, {""Name"", type text}, {""City", type text}, {""Manager"", type text}, {""Deal deti "", lembani tsiku}, {""Cost"", lembani nambala}})," & Chr(13) & "" & Chr(10) & " #""Mizere yokhala ndi fyuluta yogwiritsidwa ntchito" = Table.Se " & _ "lectRows(#""Mtundu wosinthidwa"", iliyonse ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "mu " & Chr(13) & "" & Chr(10) & " #""Mizere yokhala ndi fyuluta yayikidwa""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Wopereka =Microsoft.Mashup.OleDb.1; Gwero la Data=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Kopita:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("Sankhani *KUCHOKERA ku [" & cell.Value & "]") .RowNumbers = Zabodza .FillAdjacentFormulas = Zabodza .PreserveFormatting = Zoona .RefreshOnFileOpen = Zabodza .BackgroundQuery = Zoona .RefreshStyle = xlInsertDeleteCells .SavePassword. SaveData = Zoona .AdjustColumnWidth = Zoona .RefreshPeriod = 0 .PreserveColumnInfo = Zoona  

Pambuyo pa kukhazikitsidwa kwake, tiwona mapepala omwewo ndi mzinda, koma mafunso opangidwa kale a Power Query adzawapanga:

Ndi kusintha kulikonse mu deta yochokera, zidzakhala zokwanira kukonzanso tebulo lofanana ndi batani lamanja la mbewa - lamulo Sinthani & Sungani (Bwezeretsani) kapena sinthani mizinda yonse nthawi imodzi mochulukira pogwiritsa ntchito batani Sinthani Zonse tsamba Deta (Deta - Tsitsani Zonse).

• Kodi macros ndi chiyani, momwe mungapangire ndikuzigwiritsa ntchito
• Kusunga mapepala ogwirira ntchito ngati mafayilo osiyana
• Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi