Contents
Microsoft Excel waxay leedahay qalab badan oo lagu ururiyo xogta dhowr miisas (laga bilaabo xaashiyo kala duwan ama faylal kala duwan): xiriirinta tooska ah, shaqada TOOS AH (TOOS AH), Query Query iyo Power Pivot add-ons, iwm. Laga soo bilaabo dhinacan barricade, wax walba waxay u muuqdaan kuwo wanaagsan.
Laakiin haddii aad la kulanto dhibaato rogan ah - ku faafinta xogta hal miis ilaa xaashi kala duwan - markaa wax walbaa waxay noqon doonaan kuwo murugo badan. Waqtigan xaadirka ah, ma jiraan qalab ilbax ah oo ku dhex dhisan oo loogu talagalay kala-soocidda xogta noocaas ah ee arsenal ee Excel, nasiib darro. Markaa waa inaad isticmaashaa makro gudaha Visual Basic, ama isticmaal duubaha makro + isku darka Query Query oo leh wax yar “sifayn faylka” kadib.
Aynu si qoto dheer u eegno sida tan loo hirgelin karo.
Samaynta dhibaatada
Waxaan haynaa xogta bilawga ah miiskan oo kale oo leh cabbir ka badan 5000 oo saf oo iib ah:
Hawsha: in xogta shaxdan magaalo loo qaybiyo xaashiyo kala duwan oo buuggan ah. Kuwaas. Marka la soo saaro, waxaad u baahan tahay inaad ka hesho xaashi kasta oo keliya safafka miiska halka iibku ku yaallo magaalada u dhiganta:
Diyaarso
Si aan loo adkayn code-ka weyn oo aan uga dhigno mid fudud sida ugu macquulsan, aynu samayno dhawr tillaabo oo diyaarin ah.
Marka hore, Samee miis raadin gaar ah, halkaas oo tiir keliya ay ku taxan doonaan dhammaan magaalooyinka aad rabto inaad u samayso xaashiyo kala duwan. Dabcan, buug-gacmeedkani waxa laga yaabaa inaanay ku jirin dhammaan magaalooyinka ku jira xogta isha, laakiin kaliya kuwa aan u baahanahay warbixinno. Habka ugu fudud ee loo samayn karo miiska noocaas ah waa in la isticmaalo amarka Xogta – Ka saar Nuqullada (Xogta - Ka saar nuqullada) koobiga tiirka dareenka ama shaqada UNIK ( GAAR AH) - Haddii aad haysato nuqulkii ugu dambeeyay ee Excel 365.
Maaddaama xaashida cusub ee Excel loo sameeyay si caadi ah kahor (bidix ka mid ah) kan hadda (hore) mid, sidoo kale waxay macno samaynaysaa in la kala saaro magaalooyinka ku yaal buuggan sida ay u kala horreeyaan (laga bilaabo Z ilaa A) - ka dib abuurista, magaalada xaashida waxaa loo habayn doonaa qaab alifbeeto ah.
Marka labaad, пLabada shax u beddel kuwa firfircoon ("smart") si loo fududeeyo in lala shaqeeyo. Waxaan isticmaalnaa amarka Guriga – Qaab miis ahaan (Guriga - Qaabka Shaxda) ama kiiboodhka gaaban Ctrl+T. On tab ka muuqda Constructor (Naqshad) aan u yeedhno tablProdaji и TableCity, siday u kala horreeyaan:
Habka 1. Macro loogu qaybinayo xaashida
On the Advanced tab sameeysa (horumariye) guji badhanka Visual Basic ama isticmaal kiiboodhka gaaban Alt+F11. Daaqadda tifaftiraha macro ee furma, geli module cusub oo madhan menu-ka Geli - Module oo halkaa ku koobbi koodka soo socda:
Qayb-hoosaad () Unug kasta oo ku jira Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Range Value("таблПродажи[#All]").SpecialCells(xlCell). Xaashiyaha Halkan oo leh loop Mid kasta… Xiga hirgaliyay marinka unugyada hagaha TableCity, halka magaalo kasta lagu sifeeyo (habka AutoFilter) miiska iibka asalka ah ka dibna koobiyaynta natiijooyinka xaashida cusub ee la sameeyay. Jidka dhexdiisa, xaashida la abuuray ayaa loo beddelay isla magaca magaalada oo si otomaatig ah loogu dhejiyay ballaadhka tiirarka quruxda ayaa lagu shiday.
Waxaad ku socodsiin kartaa macro-ga la abuuray ee Excel ee tabka sameeysa button Macro (Developer — Macros) ama kiiboodhka gaaban Alt+F8.
Habka 2. Ku samee su'aalo badan Query Query
Habka hore, dhammaan isafgaradkiisa iyo fududaantiisa, ayaa leh dib-u-dhac weyn - xaashida uu abuuray makro lama cusbooneysiiyo marka isbeddel lagu sameeyo miiska iibka asalka ah. Haddii cusboonaysiinta duullimaadka ay lagama maarmaan tahay, markaa waa inaad isticmaashaa xidhmada Query ee VBA + Power, ama taa beddelkeeda, samee adigoo isticmaalaya makro ma aha oo kaliya xaashi xog sugan leh, laakiin la cusboonaysiiyay weydiimaha Query Power.
Makro-ka kiiskani wuxuu qayb ahaan la mid yahay kii hore (sidoo kale wuxuu leeyahay wareeg Mid kasta… Xiga in lagu soo rogo magaalooyinka ku jira hagaha), laakiin gudaha loop-ka ma jiri doono shaandhaynta iyo koobiyaynta, laakiin abuurista su'aal koronto ah oo ku dhejinta natiijooyinkeeda xaashi cusub:
Sub Splitter2() Unug kasta oo Range ku jira("miiska magaalada") ActiveWorkbook = Excel.CurrentWorkbook(){[Name=""TableSales"][Content]," & Chr(13) & "" & Chr(10) &" #""Nooca La Bedelay"" = Table.TransformColumnTypes(Source) , {{""Qeybta"'', qor qoraalka}, {"Magaca", qor qoraalka}, {""Magaalada", ku qor qoraalka}, {"Maareeye", qor qoraalka}, {""Deal taariikhda "", nooca datetime}, {"Qiimaha", nooca nambarka}}), & Chr(13) & "" & Chr(10) & " #"" Saf leh shaandho la dabaqay"" = Table.Se " & _ "lectRows(#""Nooca la beddelay", mid walba ([Magaalada] = """ & unugga. Qiimaha & ""))" & Chr(13) & "" & Chr(10) &" gudaha " & Chr (13) & "" & Chr(10) & " #"" Saf leh shaandheyn lagu dabaqay"" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=13, Source:= _"OLEDB; Bixiyaha =Microsoft.Mashup.OleDb.10;Data Source=$book Work$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:= Range("$A$0"). QueryTable .CommandType = xlCmd Sql .CommandText = Array("Xul *FROM [" & cell.Qiimaha & "]") .RowNumbers = Been .FillAdjacentFormulas = Been .PreserveFormatting = Run .RefreshOnFileOpen = Been .BackgroundQuery = Run SaveData = Run .AdjustColumnWidth = Run .RefreshPeriod = 1 .PreserveColumnInfo = Run Daah-furkeeda kadib, waxaan arki doonaa isla xaashiyo magaalo ahaan, laakiin mar hore ayaa la abuuray su'aalaha Query Power ayaa samayn doona:
Isbeddel kasta oo ku yimaada xogta isha, waxay ku filnaan doontaa in la cusboonaysiiyo miiska u dhigma badhanka midigta ee jiirka - amarka Cusbooneysii & Keydi (Cusbooneysii) ama cusboonaysii dhammaan magaalooyinka hal mar si guud adigoo isticmaalaya badhanka Cusboonaysii Dhammaan tab Data (Xogta - Dhammaan dib u cusbooneysii).
- Waa maxay macros, sida loo abuuro oo loo isticmaalo
- Kaydinta xaashida shaqada sidii faylal gaar ah
- Xog ururinta dhammaan xaashiyaha buugga oo la geliyo hal miis