Contents
Microsoft Excel yana da kayan aiki da yawa don tattara bayanai daga tebur da yawa (daga zanen gado ko daga fayiloli daban-daban): hanyoyin haɗin kai tsaye, aiki GASKIYA (GASKIYA), Query Query da Power Pivot add-ons, da dai sauransu Daga wannan gefen shinge, duk abin yana da kyau.
Amma idan kun shiga cikin matsala mara kyau - yada bayanai daga tebur ɗaya zuwa zanen gado daban-daban - to komai zai zama bakin ciki sosai. A halin yanzu, babu ingantaccen kayan aikin ginannun kayan aikin don irin wannan rabuwar bayanai a cikin arsenal na Excel, da rashin alheri. Don haka dole ne ku yi amfani da macro a cikin Visual Basic, ko amfani da macro rikodin + Power Query hade tare da ɗan “gyaran fayil” bayan.
Bari mu dubi yadda za a iya aiwatar da hakan.
Samar da matsala
Muna da bayanan farko irin wannan tebur tare da girman fiye da layuka 5000 don siyarwa:
Aiki: don rarraba bayanai daga wannan tebur ta gari akan zanen gado daban na wannan littafin. Wadancan. a fitarwa, kuna buƙatar samun akan kowane takarda kawai waɗannan layuka daga teburin inda siyarwar ta kasance a cikin birni mai dacewa:
Yi
Don kar a rikitar da macro code kuma sanya shi sauƙin fahimta sosai, bari mu aiwatar da matakai biyu na shirye-shiryen.
Na farko, da ƙirƙirar tebur dubawa daban, inda ginshiƙi ɗaya zai jera duk garuruwan da kuke son ƙirƙirar zane daban don su. Tabbas, wannan kundin adireshi bazai ƙunshi duk garuruwan da ke cikin bayanan tushen ba, amma waɗanda muke buƙatar rahotanni kawai. Hanya mafi sauƙi don ƙirƙirar irin wannan tebur shine amfani da umarnin Bayanai - Cire Kwafi (Bayani - Cire kwafi) don kwafin shafi City ko aiki UNIK (Na musamman) - idan kuna da sabon sigar Excel 365.
Tun da an ƙirƙiri sabbin zanen gado a cikin Excel ta tsohuwa kafin (zuwa hagu) na yanzu (na baya), yana da ma'ana don tsara biranen a cikin wannan jagorar a cikin tsari mai saukowa (daga Z zuwa A) - sannan bayan halitta, birni. za a shirya zanen gado da haruffa.
Na biyu, da пmaida duka allunan zuwa ƙarfi ("smart") don sauƙaƙe aiki tare da su. Muna amfani da umarnin Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur) ko gajeriyar hanyar keyboard Ctrl+T. A kan shafin da ya bayyana Constructor (Zane) mu kira su tablProdaji и TableCity, bi da bi:
Hanyar 1. Macro don rarraba ta zanen gado
A kan Babba shafin developer (Mai haɓakawa) danna maballin Kayayyakin aikin Basic ko amfani da gajeriyar hanyar keyboard alt+F11. A cikin taga mai gyara macro da ke buɗewa, saka sabon tsarin komai a cikin menu Saka - Module sannan ka kwafi wannan code din can:
Sub Splitter() Ga Kowane Tantanin halitta A Range("таблГорода") Range("таблПродажи").Filin Filter:=3, Ma'auni1:=cell.Value Range("таблПродажи[#All]").SpecialCells(ypesible) Sheets.Ƙara ActiveSheet.Manna ActiveSheet.Name = cell.Value ActiveSheet.Amfani da Range.Columns.AutoFit Na gaba cell Worksheets("Ƙarshen").ShowAllData Ƙarshen Sub Anan tare da madauki Ga Kowanne… Na gaba aiwatar da hanyar ta cikin sel na directory TableCity, inda kowane birni ake tace shi (hanyar Mai tacewa) a cikin teburin tallace-tallace na asali sannan kuma kwafi sakamakon zuwa sabon takardar da aka ƙirƙira. A kan hanya, takardar da aka ƙirƙira an sake suna zuwa sunan ɗaya na birnin kuma an kunna ta atomatik nisa na ginshiƙan don kyakkyawa.
Kuna iya gudanar da macro da aka ƙirƙira a cikin Excel akan shafin developer button Macros (Developer - Macros) ko gajeriyar hanyar keyboard alt+F8.
Hanya 2. Ƙirƙiri tambayoyi da yawa a cikin Tambayoyin Wuta
Hanyar da ta gabata, don duk ƙaƙƙarfansa da sauƙi, yana da babban tasiri - zanen gadon da macro ya ƙirƙira ba a sabunta su ba lokacin da aka canza canje-canje a cikin teburin tallace-tallace na asali. Idan sabuntawa akan tashi ya zama dole, to dole ne ku yi amfani da gunkin Query na VBA + Power, ko kuma a maimakon haka, ƙirƙira ta amfani da macro ba kawai zanen gado tare da tsayayyen bayanai ba, amma sabunta tambayoyin Power Query.
Macro a cikin wannan yanayin yana kama da na baya (shima yana da zagayowar Ga Kowanne… Na gaba don ƙididdige biranen da ke cikin kundin adireshin), amma a cikin madauki ba za a ƙara yin tacewa da kwafi ba, amma ƙirƙirar tambayar Tambayoyin Wuta da loda sakamakonta zuwa sabon takardar:
Sub Splitter2() Ga Kowane Tantanin halitta A Range("Table Birni") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _"bari" & Chr(13) &"" & Chr(10) &" Source = Excel.CurrentWorkbook(){[Name=""TableSales"] [Content]," & Chr(13) & "" & Chr(10) &" #"" Canji Nau'in"" = Table.TransformColumnTypes(Source) . kwanan wata "", rubuta datetime}, {""Cost", rubuta lamba}}), & Chr(13) & "" & Chr(10) & " #""Layukan da aka shafa tace"" = Table.Se " & _"lectRows(#""Changed type"",kowanne ([Birnin] = """ & cell.Value & ""))" & Chr(13) &"" & Chr(10) & "in " & Chr(13) &"" & Chr(10) &" #""Layuka tare da tacewa"" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _"OLEDB; Mai bayarwa =Microsoft.Mashup.OleDb.1;Data Source=$bookbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Tsari("Zabi *DAGA ["& cell.Value & "]") SaveData = Gaskiya .AdjustColumnWidth = Gaskiya .RefreshPeriod = 0 .PreserveColumnInfo = Gaskiya .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Ƙarshen Ƙarya Tare da ActiveSheet.Name = cell.Value Next cell Sub Sub. Bayan ƙaddamar da shi, za mu ga zanen gado iri ɗaya ta gari, amma an riga an ƙirƙira Tambayoyin Ƙarfin Wuta za su samar da su:
Tare da kowane canje-canje a cikin bayanan tushen, zai isa ya sabunta tebur mai dacewa tare da maɓallin linzamin kwamfuta na dama - umarnin Sabunta & Ajiye (Sake sabuntawa) ko sabunta duk biranen lokaci guda a cikin girma ta amfani da maɓallin Ɗaukaka Duk tab data (Bayanai - Refresh Duk).
- Menene macros, yadda ake ƙirƙira da amfani da su
- Ajiye zanen gadon littafin aiki azaman fayiloli daban
- Ana tattara bayanai daga dukkan zanen gado na littafin cikin tebur guda