Hevitra ato Anatiny
Microsoft Excel dia manana fitaovana maro hanangonana angona avy amin'ny latabatra maromaro (avy amin'ny takelaka samihafa na avy amin'ny rakitra samihafa): rohy mivantana, fiasa ankolaka (TSY MISY), Fanampiana Power Query sy Power Pivot, sns. Avy amin'ity sisin'ny barricade ity dia toa tsara daholo ny zava-drehetra.
Saingy raha sendra olana mifanohitra ianao - manaparitaka angona avy amin'ny latabatra iray mankany amin'ny takelaka samihafa - dia ho mampalahelo kokoa ny zava-drehetra. Amin'izao fotoana izao, tsy misy fitaovana sivilizasiona natsangana ho an'ny fisarahana angon-drakitra toy izany ao amin'ny arsenal Excel, indrisy. Noho izany dia tsy maintsy mampiasa macro amin'ny Visual Basic ianao, na mampiasa ny macro recorder + Power Query miaraka amin'ny "fanatsarana rakitra" kely aorian'izay.
Andeha hojerentsika akaiky ny fomba fampiharana izany.
Famolavolana ny olana
Manana angon-drakitra voalohany toy ny latabatra misy laharana mihoatra ny 5000 izahay ho an'ny varotra:
Asa: mizara ny angon-drakitra avy amin'ity tabilao ity isaky ny tanàna amin'ny taratasy misaraka amin'ity boky ity. Ireo. Amin'ny vokatra dia tsy maintsy mahazo ny laharana tsirairay avy amin'ny latabatra misy ny fivarotana ao amin'ny tanàna mifanandrify ianao:
Miomana
Mba tsy hanasarotra ny kaody macro sy hahatonga azy ho mora takarina araka izay azo atao, andao hanao dingana fanomanana roa.
Voalohany, ny mamorona latabatra fitadiavana misaraka, izay misy tsanganana tokana hitanisana ny tanàna rehetra tianao hamoronana takelaka misaraka. Mazava ho azy fa ity lahatahiry ity dia mety tsy ahitana ny tanàna rehetra ao amin'ny angona loharano, fa ireo izay ilaintsika tatitra ihany. Ny fomba tsotra indrindra hamoronana latabatra toy izany dia ny fampiasana ny baiko Data - Esory ny dika mitovy (Data - Esory ny dika mitovy) ho an'ny kopia tsanganana fihetseham-po na asa UNIK (UNIQUE) - raha manana ny kinova farany an'ny Excel 365 ianao.
Koa satria ny takelaka vaovao ao amin'ny Excel dia noforonina amin'ny alàlan'ny default alohan'ny (eo ankavia) ny ankehitriny (teo aloha), dia misy dikany ihany koa ny mandamina ny tanàna ao amin'ity lahatahiry ity amin'ny filaharana midina (avy amin'ny Z ka hatramin'ny A) - avy eo aorian'ny famoronana, ny tanàna. ny takelaka dia halamina araka ny abidy.
Faharoa, ny пavadika ho dinamika ny tabilao roa (“smart”) mba hanamora ny fiaraha-miasa amin’izy ireo. Mampiasa ny baiko izahay Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra) na hitsin-dàlana fitendry Ctrl+T. Ao amin'ny tabilao miseho Mpanao (Volavola) andao hiantso azy ireo tablProdaji и TableCity, tsirairay:
Fomba 1. Macro ho an'ny fizarana amin'ny takelaka
Amin'ny tabilao mandroso Developer (mpamorona) tsindrio ny bokotra Visual Basic na mampiasa hitsin-dàlana fitendry Alt+F11. Ao amin'ny varavarankelin'ny mpanonta macro izay misokatra, ampidiro ny maody tsy misy na inona na inona amin'ny alàlan'ny menio Ampidiro - Module ary kopia ity code manaraka ity any:
Sub Splitter() Ho an'ny sela tsirairay ao anatin'ny Range("таблГорода") Range("таблПродажи"). saha AutoFilter:=3, Criteria1:=cell.Value Range("таблПродажи[#All]") Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Data").ShowAllData End Sub Eto miaraka amin'ny loop Ho an'ny tsirairay… Manaraka nampihatra ny andalan-teny amin'ny alalan'ny sela ny lahatahiry TableCity, izay ho an'ny tanàna tsirairay dia voasivana (fomba AutoFilter) ao amin'ny latabatra fivarotana tany am-boalohany ary avy eo mandika ny valiny amin'ny takelaka vao noforonina. Teny an-dalana, ny taratasy noforonina dia nomena anarana mitovy amin'ny anaran'ny tanàna ary mandeha ho azy ny sakan'ny tsanganana ho an'ny hatsaran-tarehy.
Azonao atao ny mampandeha ny macro noforonina ao amin'ny Excel eo amin'ny tabilao Developer bokotra macros (Developer - Macros) na hitsin-dàlana fitendry Alt+F8.
Fomba 2. Mamorona fanontaniana maro ao amin'ny Power Query
Ny fomba teo aloha, noho ny fahatomombanana sy ny fahatsorany rehetra, dia manana lesoka lehibe - ny takelaka noforonin'ny macro dia tsy havaozina rehefa misy fiovana ao amin'ny latabatra fivarotana tany am-boalohany. Raha ilaina ny fanavaozana amin'ny lalitra, dia tsy maintsy mampiasa ny VBA + Power Query bundle ianao, na ny marimarina kokoa, mamorona mampiasa macro fa tsy takelaka misy angon-drakitra static, fa fanavaozana Power Query fangatahana.
Ny macro amin'ity tranga ity dia mitovy amin'ny teo aloha (misy tsingerina ihany koa Ho an'ny tsirairay… Manaraka mba hamerenana ny tanàna ao amin'ny lahatahiry), fa ao anatin'ny tadivavarana dia tsy hisy intsony ny sivana sy ny kopia, fa ny famoronana fangatahana Power Query sy ny fampiakarana ny valiny amin'ny takelaka vaovao:
Sub Splitter2() Ho an'ny sela tsirairay ao amin'ny faritra ("latabatra tanàna") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "avelao" & Chr(13) & "" & Chr(10) & " Loharano = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""karazana niova"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Anarana"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal daty "", soraty datetime}, {""Sarany"", karazana isa}}), & Chr(13) & "" & Chr(10) & " #""Andalana misy sivana ampiasaina"" = Table.Se " & _ "lectRows(#""karazana niova"", tsirairay avy ([Tanàna] = """ & cell.Value & ""))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Andalana misy sivana ampiharina""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Loharano angona=$Workbook$;Location=" & cell.Value & ";Properties Extended=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT * FROM [" & cell.Value & "]") .RowNumbers = Diso .FillAdjacentFormulas = Diso .PreserveFormatting = True .RefreshOnFileOpen = FalseryCell = FalseryCell lls .SavePassword = Diso . SaveData = Marina .AdjustColumnWidth = Marina .RefreshPeriod = 0 .PreserveColumnInfo = Marina .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name = cell.Value Next cell End Sub Aorian'ny fandefasana azy dia hahita takelaka mitovy amin'ny tanàna isika, fa ny fangatahana Power Query efa noforonina dia hamorona azy ireo:
Miaraka amin'ny fiovana rehetra amin'ny angona loharano, dia ampy ny manavao ny latabatra mifanandrify amin'ny bokotra havanana amin'ny totozy - ny baiko Fanavaozana & Tehirizo (Havaozina) na manavao ny tanàna rehetra indray mandeha amin'ny fampiasana ny bokotra Update All vakizoro Data (Angona - Havaozy daholo).
- Inona ny macros, ny fomba hamoronana sy fampiasana azy ireo
- Mitahiry ny takelaka fiasana ho rakitra misaraka
- Manangona angona avy amin'ny takelaka rehetra amin'ny boky ao anaty latabatra iray