Okuqukethwe
I-Microsoft Excel inamathuluzi amaningi okuqoqa idatha kumathebula amaningana (kusuka kumakhasi ahlukene noma kumafayela ahlukene): izixhumanisi eziqondile, umsebenzi INDALO (INDIRECT), Umbuzo Wamandla kanye nezengezo ze-Pivot Yamandla, njll. Kusukela kulolu hlangothi lwe-barricade, yonke into ibonakala iyinhle.
Kodwa uma ungena enkingeni ephambene - ukusabalalisa idatha kusuka etafuleni elilodwa kuya kumashidi ahlukene - khona-ke konke kuzoba buhlungu kakhulu. Okwamanje, awekho amathuluzi akhelwe ngaphakathi aphucuzekile okuhlukaniswa kwedatha ku-arsenal ye-Excel, ngeshwa. Ngakho-ke kuzodingeka usebenzise i-macro ku-Visual Basic, noma usebenzise inhlanganisela yerekhoda ye-macro + Power Query "nokulungiswa kwefayela" okuncane ngemva kwalokho.
Ake sihlolisise ukuthi lokhu kungenziwa kanjani.
Ukwakhiwa kwenkinga
Sinedatha yokuqala yethebula elinjalo elinosayizi wemigqa engaphezu kuka-5000 yokuthengisa:
Umsebenzi: ukusabalalisa idatha esuka kuleli thebula ngedolobha emakhasini ahlukene ale ncwadi. Labo. ekuphumeni, udinga ukuthola eshidini ngalinye kuphela leyo migqa esuka kuthebula lapho ukuthengiswa bekusedolobheni elihambisanayo:
Lungiselela
Ukuze ungaxabanisi ikhodi ye-macro futhi uyenze ibe lula ukuyiqonda ngangokunokwenzeka, ake senze izinyathelo ezimbalwa zokulungiselela.
Okokuqala, i dala itafula lokubheka elihlukile, lapho ikholomu eyodwa izoklelisa wonke amadolobha ofuna ukuwadalela amashidi ahlukene. Impela, lolu hlu lwemibhalo kungenzeka lungaqukathi wonke amadolobha akhona kudatha yomthombo, kodwa kuphela lawo esiwadingayo imibiko. Indlela elula yokwenza ithebula elinjalo ukusebenzisa umyalo Idatha - Susa Izimpinda (Idatha - Susa izimpinda) ngekhophi yekholomu imizwa noma umsebenzi I-UNIK (OKUHLUKILE) - uma unenguqulo yakamuva ye-Excel 365.
Njengoba amashidi amasha ku-Excel enziwa ngokuzenzakalelayo ngaphambi (ngakwesokunxele) kwelamanje (langaphambilini), kunengqondo futhi ukuhlela amadolobha akulolu hlu lwemibhalo ngokulandelana okwehlayo (kusuka ku-Z kuye ku-A) - bese kuthi ngemuva kokudalwa, idolobha. amashidi azohlelwa ngama-alfabhethi.
Okwesibili, пguqula womabili amathebula abe yi-dynamic (“smart”) ukwenza kube lula ukusebenza nabo. Sisebenzisa umyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula) noma isinqamuleli sekhibhodi Ctrl+T. Kuthebhu evelayo umakhi (Umklamo) asibabize tablProdaji и I-TableCity, ngokulandelana:
Indlela 1. Imakhro yokuhlukaniswa ngamashidi
Kuthebhu ethuthukisiwe Unjiniyela (Unjiniyela) chofoza inkinobho Visual Basic noma sebenzisa isinqamuleli sekhibhodi Alt+F11. Ewindini lomhleli omkhulu elivulayo, faka imojuli entsha engenalutho ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:
I-Sub Splitter() Kuseli Ngalinye Ku-Range("таблГорода") Ububanzi("таблПродажи").Inkambu Yesihlungi Esizenzakalelayo:=3, Umbandela1:=iseli.Ububanzi Benani("таблПродажи[#All]").Amaseli Akhethekile(xlCellTypeVisible).Kopisha. AmaSpredishithi.Engeza Ishidi Elisebenzayo.Namathisela I-ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Amaseli Okusebenzela Alandelayo("Данные").ShowAllData End Sub Lapha ngeluphu Kokunye … Okulandelayo sebenzisa ukudlula kumaseli ohla lwemibhalo I-TableCity, lapho idolobha ngalinye lihlungwa khona (indlela I-AutoFilter) kuthebula lokuthengisa langempela bese ukopisha imiphumela eshidini elisanda kwakhiwa. Endleleni, ishidi elidaliwe liqanjwe kabusha egameni elifanayo ledolobha futhi ukufaka ngokuzenzakalela ububanzi bamakholomu ngobuhle kuvulwe kulo.
Ungaqalisa i-macro edaliwe ku-Excel kuthebhu Unjiniyela inkinobho amamakhro (Unjiniyela - Amamakhro) noma isinqamuleli sekhibhodi Alt+F8.
Indlela 2. Dala imibuzo eminingi Kumbuzo Wamandla
Indlela yangaphambilini, kukho konke ukubumbana kwayo nokulula, ine-drawback ephawulekayo - amashidi adalwe yi-macro awabuyekeziwe lapho izinguquko zenziwa etafuleni lokuqala lokuthengisa. Uma ukuvuselela endizeni kudingekile, kuzomele usebenzise inqwaba ye-VBA + Power Query, noma kunalokho, udale usebenzisa i-macro hhayi nje amashidi anedatha emile, kodwa imibuzo ebuyekeziwe ye-Power Query.
I-macro kuleli cala ifana ngokwengxenye neyangaphambili (iphinde ibe nomjikelezo Kokunye … Okulandelayo ukuphindaphinda amadolobha kuhla lwemibhalo), kodwa ngaphakathi kweluphu ngeke kusahlungwa futhi kukopishwe, kodwa kudala umbuzo we-Power Query nokulayisha imiphumela yawo eshidini elisha:
I-Sub Splitter2() Kuseli Ngalinye Ku-Range("Ithebula Ledolobha") I-ActiveWorkbook.Queries.Engeza Igama:=cell.Value, Ifomula:= _ "vumela" & Chr(13) & "" & Chr(10) & " Umthombo = Excel.CurrentWorkbook(){[Name=""TableSales""][Okuqukethwe]," & Chr(13) & "" & Chr(10) & " #""Uhlobo Oluguquliwe"" = Ithebula.TransformColumnTypes(Umthombo , {{""Isigaba", thayipha umbhalo}, {""Igama"", thayipha umbhalo}, {""Idolobha"", thayipha umbhalo}, {""Umphathi"", thayipha umbhalo}, {""Idili idethi "", thayipha idethi}, {""Izindleko"", uhlobo lwenombolo}})," & Chr(13) & "" & Chr(10) & " #""Kusetshenziswe imigqa enesihlungi"" = Table.Se " & _ "lectRows(#""Uhlobo olushintshiwe"", ngayinye ([Idolobha] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Kusetshenziswe imigqa enesihlungi""" ActiveWorkbook.Worksheets.Engeza Nge-ActiveSheet.ListObjects.Engeza(Uhlobo Lomthombo:=0, Umthombo:= _ "OLEDB; Umhlinzeki =Microsoft.Mashup.OleDb.1;Umthombo Wedatha=$Workbook$;Indawo=" & cell.Value & ";Izakhiwo Ezandisiwe=""""" _ , Indawo:=Ububanzi("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("KHETHA *KUSUKA [" & cell.Value & "]") .RowNumbers = Amanga .FillAdjacentFormulas = Amanga .PreserveFormatting = True .RefreshOnFileOpen = Amanga .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells =FalPasword. SaveData = Iqiniso .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Isiphetho esingamanga Nge-ActiveSheet.Name = cell.Value Next cell End Sub Sub Ngemva kokwethulwa, sizobona amashidi afanayo nedolobha, kodwa imibuzo eseyakhiwe kakade Yombuzo Wamandla izowakha:
Nganoma yiziphi izinguquko kudatha yomthombo, kuzokwanela ukubuyekeza ithebula elihambisanayo nenkinobho yegundane elingakwesokudla - umyalo Buyekeza & Londoloza (Vuselela) noma ubuyekeze wonke amadolobha ngesikhathi esisodwa ngobuningi usebenzisa inkinobho Vuselela konke ithebhu Idatha (Idatha — Vuselela Konke).
- Ayini ama-macros, angawadala kanjani futhi uwasebenzise
- Ukugcina amashidi encwadi yokusebenzela njengamafayela ahlukene
- Iqoqa idatha kusuka kuwo wonke amashidi encwadi etafuleni elilodwa