Contents
Safafka faaruqa ah iyo tiirarka waxay noqon karaan xanuun miisaska xaalado badan. Hawlaha caadiga ah ee kala-soocidda, shaandhaynta, soo koobidda, samaynta miisaska pivot, iwm. waxay u arkaan safafka madhan iyo tiirarka sida miiska nasashada, iyada oo aan la soo qaadin xogta ku taal gadaashooda. Haddii ay jiraan meelo badan oo sidan oo kale ah, ka dibna gacanta ka saarista waxay noqon kartaa mid aad u qaali ah, mana shaqeyn doonto in la saaro dhammaan "bulk" iyadoo la adeegsanayo shaandhaynta, sababtoo ah shaandheynta ayaa sidoo kale "turunsan doonta" nasashada.
Aynu eegno dhowr siyaabood oo lagu xallin karo dhibaatadan.
Habka 1. Raadi unugyo madhan
Tani ma noqon karto tan ugu habboon, laakiin hubaal habka ugu fudud ayaa mudan in la xuso.
Ka soo qaad in aanu la macaamileyno shaxdan oo kale oo ay ku jiraan safaf badan oo madhan iyo tiirar gudaha ah (oo loo iftiimiyay si loo caddeeyo):
Ka soo qaad in aan hubno in tiirka koowaad ee miiskayaga (column B) uu had iyo jeer ka kooban yahay magaca magaalada. Markaa unugyada madhan ee tiirkan waxay calaamad u noqon doonaan safaf madhan oo aan loo baahnayn. Si aad dhaqso uga saarto dhamaantood, samee waxyaabaha soo socda:
- Kala dooro magaalooyinka (B2:B26)
- Fure fure F5 kadibna riix highlight (Tag Special) ama ka dooro tab Guriga - Hel oo Dooro - Dooro koox unugyo ah (Guriga - Hel&Dooro - Tag gaar ah).
- Daaqada furmo, dooro ikhtiyaarka Unugyo madhan (Madhan) iyo saxaafadda OK - Dhammaan unugyada madhan ee tiirka kowaad ee miiskayaga waa in la doortaa.
- Hadda ka dooro tab Bogga Hore Command Tirtir - Tirtir safafka xaashida (Tirtir - Tirtir safafka) ama taabo furaha kiiboodhka Ctrl+laga jaray – hawshayadana waa la xalliyey.
Dabcan, waxaad ka takhalusi kartaa tiirarka madhan si isku mid ah, adoo isticmaalaya madaxa miiska oo saldhig u ah.
Habka 2: Raadi saf madhan
Sida laga yaabo inaad horeba u ogaatay, habka hore wuxuu shaqayn doonaa oo kaliya haddii xogtayadu ay ka kooban tahay saf iyo tiirar si buuxda loo buuxiyey, kuwaas oo lagu dhejin karo marka la raadinayo unugyo madhan. Laakiin maxaa dhacaya haddii aysan jirin kalsoonidaas oo kale, xogtana waxaa ku jiri kara unugyo madhan sidoo kale?
Bal u fiirso shaxdan soo socota, tusaale ahaan, kiis caynkaas ah oo keliya:
Halkan habka ayaa noqon doona mid ka sii dhib yar:
- Geli unugga A2 shaqada COUNT (COUNTA), kaas oo xisaabin doona tirada unugyada buuxsan ee safka dhanka midig ku yaal oo koobi doona caanahan ilaa miiska oo dhan:
- Dooro unugga A2 oo shid shaandhada amarka leh Xogta – Shaandhee (Xogta - Shaandhayso) ama kiiboodhka gaaban Ctrl+shift+L.
- Aynu ku shaandhayno eber tiirka la xisaabiyay, ie, dhammaan safafka aan xogtu jirin.
- Way hadhaysaa in la doorto khadadka la sifeeyay oo lagu tirtiro amarka Guriga - Tirtir -' Tirtir safafka xaashida (Guriga - Tirtir - Tirtir safafka) ama kiiboodhka gaaban Ctrl+laga jaray.
- Waxaan damin filtarka oo hel xogtayada iyada oo aan khadadka madhan.
Nasiib darro, khiyaamadan laguma samayn karo tiirar - Excel weli ma baran sida loo shaandheeyo tiirarka.
Habka 3. Macro si loo saaro dhammaan safafka madhan iyo tiirarka xaashida
Waxa kale oo aad isticmaali kartaa makro fudud si aad hawshan si otomaatig ah ugu samayso. Riix jidka gaaban ee kiiboodhka Alt+F11 ama ka dooro tab sameeysa - Visual Basic (Developer — Visual Basic Editor). Haddii tabs sameeysa ma muuqato, waxaad awood u yeelan kartaa iyada oo loo marayo Faylka - Ikhtiyaarada - Dejinta Ribbon (Faylka - Ikhtiyaarada - Habboon Ribbon).
Daaqadda tafatiraha Visual Basic ee furmo, dooro amarka menu-ka Geli - Module oo moduleka madhan ee soo muuqda, koobiyi oo dheji khadadkan soo socda:
Tirtir-hoosaad Madhan rng Is Nothing then Set rng = Rows(r) Else Set rng = Midawga(rng, Rows(r)) Dhammaadka Hadii Xigta r Hadii Aan rng Ahayn Wax Ka Dib rng.Delete 'удаляем пустые столбцы Set rng = Waxba r = 1 ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Tiirarka r)) Dhammays haddii r haddii aan rng waxba ahayn markaa rng.Delete End Sub
Xir tifaftiraha oo ku soo celi Excel.
Hadda ku dhufo isku darka Alt+F8 ama badhan Macro tab sameeysa. Daaqadda furmi doonta waxay taxaysaa dhammaan macros aad hadda diyaar u tahay inaad ku shaqeyso, oo uu ku jiro makro-ka aad hadda abuurtay. Masaari tirtir. Dooro oo guji badhanka Run (orod) - Dhammaan safafka madhan iyo tiirarka xaashida ayaa isla markiiba la tirtiri doonaa.
Habka 4: Weydiinta Awoodda
Si kale oo lagu xalliyo dhibadeena iyo dhacdo caadi ah ayaa ah in meesha laga saaro safafka madhan iyo tiirarka ku jira Query Query.
Marka hore, aan ku shubno miiskayaga Tifaftiraha Query Query Query. Waxaad u bedeli kartaa mid firfircoon oo "smart" leh oo leh furaha gaaban ee kiiboodhka Ctrl+T ama kaliya dooro tiradayada xogta oo bixi magac (tusaale ahaan. Data) barta qaacidada, una beddeleysa magaca:
Hadda waxaan isticmaalnaa amarka Xogta - Hel xogta - Laga soo bilaabo miiska / kala duwan (Xogta - Hel Xog - Miis / kala duwan) oo wax walba ku dheji Query Query:
Markaa wax walba waa sahlan yihiin:
- Waxaan ku tirtirnaa khadadka madhan amarka Guriga - Yaraynta khadadka - Tirtir khadadka - Tirtir khadadka madhan (Guriga - Ka saar Safafka - Ka saar safafka madhan).
- Midig ku dhufo ciwaanka safka hore ee Magaalada oo ka dooro Amarka Tiirarka kale ee Unpivot ka menu macnaha guud. Shaxdayadu waxay ahaan doontaa, sida farsamo ahaan loogu yeero, caadi ah - oo loo beddelo saddex tiir: magaalo, bisha iyo qiimaha laga bilaabo isgoyska magaalada iyo bisha laga bilaabo miiska asalka ah. Farqiga u gaarka ah hawlgalkan ee Query Query waa in uu ka boodo unugyo madhan xogta isha, taas oo ah waxa aan u baahanahay:
- Hadda waxaan sameynaa qalliinka rogaal celiska - waxaan dib ugu celineynaa miiska soo baxay mid laba-geesood ah si aan ugu soo celino qaabkiisii hore. Dooro tiirka leh bilaha iyo tabka Badalka dooro koox Tiirka pivot (Beddel - Tiirka Pivot). Daaqadda furmo, sida safka qiyamka, dooro kan ugu dambeeya (Qiimaha), iyo xulashooyinka horumarsan - hawlgalka Ha isku geyn (Ha isku geyn):
- Way hadhay in natiijada dib loogu celiyo Excel iyadoo la raacayo amarka Guriga - Xidh oo Raro - Xidh oo ku shub gudaha… (Guriga - Xir & Soodeji - Xidh & Ku Raro…)
- Waa maxay makro, sidee buu u shaqeeyaa, meesha laga koobi karo qoraalka makro, sida loo maamulo makro?
- Buuxinta dhammaan unugyada madhan ee liiska ku jira qiyamka unugyada waalidka
- Ka saarida dhammaan unugyada madhan xadka la bixiyay
- Ka saarida dhammaan safafka madhan xaashida shaqada ee leh PLEX add-on