Contents
Samaynta dhibaatada
Waxaan haynaa unugyo kala duwan oo leh xog ka kooban unugyo madhan:
Hawshu waa in meesha laga saaro unugyo madhan, iyada oo laga tegayo oo keliya unugyo xog leh.
Habka 1. Qalafsan oo degdeg ah
- Doorashada kala duwanaanshaha asalka ah
- Fure fure F5, badhanka xiga highlight (Gaar ah). Daaqada furmo, dooro Unugyo madhan(Madhan) oo guji OK.
Dhammaan unugyada madhan ee kala duwan ayaa la doortay.
- Waxaan ku siin amar ku jira liiska si loo tirtiro unugyada la doortay: midig-guji- Tirtir unugyada (Tirtir Unugyada) oo leh kor u kac.
Habka 2: Habayn Formula
Si loo fududeeyo, aynu magacawno tirada shaqadeena annagoo adeegsanayna Magaca Maamulaha (Maamulaha Magaca) tab formula (Formula) ama, Excel 2003 iyo wixii ka weyn, menu-ka Geli - Magaca - Magacaabid ( Geli - Magaca - Qeexi )
Magaca xadka B3:B10 Madhan, kala duwan D3:D10 - Wax madhan. Heerkulka waa in ay ahaadaan kuwo isku mid ah oo isku mid ah, waxayna ku yaalaan meel kasta oo qaraabo ah.
Hadda dooro unugga koowaad ee kala duwanaanshaha labaad (D3) oo geli qaacidada cabsida leh:
= HADDII ( ROW () -ROW (NoEmpty)+1>NOTROWS(YesEmpty) -COUNTBLANK(Haa Madhan);"";CINWAANKA(CINWAANKA (UGU HOOSEEYA) + ROWS(Waxaa jira faaruq)); LINE()-ROW (Ma Madhan)+1); COLUMN(Waxaa jira faaruq);
Qaybta Ingiriisiga waxay noqon doontaa:
= HADDII( ROW()-ROW(Lama Madhan)+1>ROW(Madhan) -COUNTBLANK(madhan),"",CINWAANKA YAR((HADDII (Madhan)) +ROWS(HaveEmpty))), ROW()-ROW(NoEmpty)+1), COLUMN(HaveEmpty),4)))
Waxaa intaa dheer, waa in loo geliyo qaab habaysan, tusaale ahaan riix ka dib dhajinta Ku qor (sida caadiga ah) iyo Ctrl + Shift + Ku qor. Hadda caanaha waxaa lagu koobi karaa iyadoo la isticmaalayo autocomplete (jiid iskutallaabta madow ee geeska midig ee hoose ee unugga) - waxaanan heli doonaa tirada asalka ah, laakiin iyada oo aan lahayn unugyo madhan:
Habka 3. Shaqada gaarka ah ee VBA
Haddii uu jiro tuhun ah in aad inta badan u baahan tahay inaad ku celiso habka looga saaro unugyada madhan ee kala duwan, ka dibna waxa fiican inaad ku darto shaqadaada si aad uga saarto unugyada madhan hal mar, oo u isticmaal dhammaan kiisaska xiga.
Si tan loo sameeyo, fur Visual Basic Editor (ALT + F11Geli module cusub oo madhan (menu Geli - Module) oo ku koobbi qoraalka shaqadan halkaas:
Shaqada NoBlanks(DataRange Sida Range) Sida Kaladuwan() Dim N Inta Dim N2 Inta U Dhaxaysa Dim Rng Sida Range Dim MaxCells Sida Dheer Dim Natiijooyinka Codsiga = Rng.Qiimaha Dhamaadka Hadii Rng Ku Xiga N1 = N + 1 Ilaa MaxCells Natiijooyinka(N1, 1) = vbNullString Xiga N1 Hadii Codsiga.Caller.Rows.Count = 2 Kadib NoBlanks = Application Dhammaadka Haddi Dhamaadka Shaqada
Ha iloobin inaad kaydiso faylka oo aad dib uga beddesho Visual Basic Editor una beddelo Excel. Si aan u isticmaalno shaqadan tusaalaheena:
- Dooro tiro ku filan oo unugyo madhan ah, tusaale ahaan F3:F10.
- Tag liiska Gelida - Shaqada ( Geli - Shaqada)ama guji badhanka Geli shaqada (Shaqada Gelida) tab formula (Formula) noocyada cusub ee Excel. Qaybta Qeexitaanka Isticmaalaha (Isticmaalku Qeexay) dooro shaqadeena NoBlanks.
- Ku qeex kala duwanaanshaha isha oo leh faaruq (B3:B10) sida doodda shaqada oo riix Ctrl + Shift + Ku qorsi aad shaqada u geliso qaab habaysan.
:
- Tirtir dhammaan safafka madhan ee miis halmar leh makro fudud
- Ka saarida dhammaan safafka madhan xaashida shaqada hal mar iyadoo la isticmaalayo PLEX add-on
- Dhakhso u buuxi dhammaan unugyada madhan
- Waa maxay macros, halka laga geliyo koodka weyn ee VBA