- Ukuthuthukisa injini yokubala ngokuphathwa kwemisebenzi eguquguqukayo kanye nokusebenzisa imodi yesandla.
- Amasu okuhlela idatha asekelwe kumakholomu asekelayo kanye nokususwa kokuphindaphindeka.
- Ukusetshenziswa kwamathuluzi athuthukile njenge-Power Query kanye ne-VBA ukucubungula ulwazi oluningi.
- Amasu okulinganisa ukusebenza ukuze kutholakale futhi kuqedwe izithiyo ezincwadini eziyinkimbinkimbi.
Ngiyaqiniseka ukuthi sekwenzekile kuwe: uvula incwadi yokusebenzela ye-Excel ebukeka njengenqwaba yedatha, bese kuthi ngokuzumayo uhlelo luqine noma luthathe isikhathi eside ukubuyekeza isamba esilula. Akukhona ukuthi ikhompyutha yakho ihamba kancane; mhlawumbe ukuthi isakhiwo sespredishithi sakho sinenkinga ngenjini yokucubungula ye-Microsoft. Uma ubhekene nezinkulungwane zemigqa, ukusebenza kuba yinto ebalulekile ukuze ugweme ukulahlekelwa yisineke noma ukwenza amaphutha ngenxa yokukhungatheka nje.
Ukuze i-Excel ihambe, iprosesa enamandla ayanele; udinga ukuqonda ukuthi isofthiwe isebenza kanjani . Kusukela ekuphathweni kwe-RAM kuya ekuncikeni kwefomula, kunezindlela zokulungisa ezingaguqula incwadi yokusebenzela enzima ibe ithuluzi elisheshayo neliphendulayo. Kulesi sihloko, sizohlukanisa wonke amasu, kusukela kweyisisekelo kuya ekusebenziseni ikhodi ye-VBA, ukuze amaspredishithi akho aphendule ngokushesha.
Injini yokubala kanye nokuphathwa kwesivinini
Kusukela kwethulwa lokho okubizwa ngokuthi "igridi enkulu" ezinguqulweni ezifana ne-Excel 2007 kanye nezakamuva, umkhawulo weseli ukhule kakhulu. Lokhu kuvumele ukudalwa kwezizindalwazi ezinkulu, kodwa futhi kwenze kwaba lula ngabasebenzisi ukuklama izincwadi zokusebenza ezihamba kancane kakhulu . Ukusebenza kubalulekile ngoba uma isikhathi sokuphendula sidlula umzuzwana owodwa, siqala ukulahlekelwa ukugxila futhi umsebenzi uyaphela ngokuphelele.
I-Excel isebenzisa uhlelo lokubala kabusha oluhlakaniphile olulandelela ukuncika. Esikhundleni sokucubungula konke, ibuyekeza kuphela amaseli ashintshile kanye nalawo ancike kuwo. Kodwa-ke, kunezimo lapho lolu hlelo luba nzima kakhulu khona. Ukuze silwe nalokhu, singazama izindlela zokubala: ukubala kabusha okuzenzakalelayo kulula kodwa kuyingozi ezincwadini ezinkulu zomsebenzi, kuyilapho ukubala kabusha ngesandla (okusebenzayo kuthebhu yeFomula) kusenza sikwazi ukunquma ukuthi sifuna nini uhlelo lucubungule idatha ngokucindezela inkinobho ethi F9.
Uma uhlangana nezincwadi zomsebenzi ezithatha isikhathi eside ukuvulwa, kukhona isici esithuthukisiwe esibizwa ngokuthi i-ForceFullCalculation . Ukuyivumela nge-VBA Editor kuphoqa i-Excel ukuthi izishaye indiva izibuyekezo ezihlakaniphile futhi yenze ukubala okugcwele, okungaba ngokushesha kakhulu kwezinye izimo eziyinkimbinkimbi kunokuzama ukugcina umuthi wokuthembela.
Indlela yokubona nokuqeda izinkinga
Akuwona wonke amafomula anosayizi ofanayo wefayela. Ngokuvamile, ukubambezeleka akuveli kusayizi wefayela, kodwa emisebenzini ephindaphindwayo nephindaphindwayo . Ukuze uthole inkinga, indlela ekahle ukusebenzisa indlela "yokumba": linganisa isikhathi sokubala sencwadi yonke yokusebenza, bese kuba ngeshidi, bese ekugcineni ngamabhulokhi eseli. Ukuze uthole ukunemba kokuhlinzwa, ungasebenzisa ama-timing macros asekelwe ku-Windows API (njengomsebenzi we-MicroTimer) alinganisa ama-microsecond.
Uma inkinga isitholakele, kumelwe silandele imithetho ethile yegolide. Eyokuqala ukususa izibalo eziphindaphindiwe . Kuvamile kakhulu ukukopisha ifomula eyinkimbinkimbi izikhathi eziyizinkulungwane; esikhundleni salokho, kungcono ukuhambisa ukubala okuphindaphindiwe uye kuseli elisizayo bese ezinye zimane zibhekisele kulowo mphumela. Lokhu kunciphisa kakhulu inani lezinkomba i-Excel okufanele izicubungule.
Umthetho wesibili ugxile ekusebenzeni kahle kwemisebenzi. Isibonelo, ukusesha idatha ehleliwe kushesha kakhulu kunokusesha idatha engahlelwanga. Kunconywa futhi ukuthi kufakwe esikhundleni senhlanganisela yomsebenzi we-IF kanye ne-ISERROR nomsebenzi we -IFERROR , olungiselelwe isivinini kanye nokuqondile, okuhlanganisa imisebenzi ye-Excel ethuthukisiwe yochwepheshe.
Qaphela imisebenzi eguquguqukayo kanye nama-matrices
Eminye imisebenzi iyizithiyo zokusebenza ezingokoqobo. Okubizwa ngokuthi imisebenzi eguquguqukayo , njenge-OFFSET, INDIRECT, TODAY, noma NOW, iphinda ibale njalo lapho kwenzeka ushintsho encwadini yokusebenza, noma ngabe aluhlobene nefomula. Uma unezinkulungwane zale misebenzi, ukucubungula okuqhubekayo kuzokwenza i-cursor ibambezeleke futhi ukuchofoza ngakunye kube umsebenzi onzima.
Ngakolunye uhlangothi, amafomula e-array avame ukuba namandla kakhulu kodwa asebenzisa izinsiza eziningi kakhulu. Ngokuvamile, ikhambi elisebenza kahle kakhulu ukuhlukanisa ifomula enkulu ibe amakholomu amaningana asizayo. Nakuba kungase kubonakale sengathi sihlanganisa ispredishithi, empeleni sisiza izibalo ze-Excel ezinemicu eminingi ukusabalalisa umthwalo womsebenzi ngempumelelo enkulu kuwo wonke ama-processor cores.
Ukufometha okunemibandela nakho kuwela kulesi sigaba sengozi. Ngenxa yokuthi kuyashintshashintsha, ukusebenzisa imithetho yemibala eyinkimbinkimbi kumabanga amakhulu kunganciphisa ijubane lokuphendula okubonakalayo kwesikrini. Okungcono kakhulu, kufanele kusetshenziswe kancane noma kuthathelwe indawo yizinqubo ze-VBA uma i-logic yombala iyinkimbinkimbi kakhulu.
Amathuluzi athuthukisiwe okuphathwa kwedatha
Uma idatha idlula umthamo wamafomula avamile, sekuyisikhathi sokukhipha izibhamu ezinkulu. I-Power Query ngokungangabazeki iyona engcono kakhulu eminyakeni yamuva. Ikuvumela ukuthi uhlanze, uguqule, futhi uhlanganise idatha ngaphandle kwegridi eyinhloko, uvimbele incwadi yokusebenzela ukuthi igcwale amafomula asindayo futhi igcine ukuphendula kwefayela.
Kulabo abadinga ukuhlaziya ngokushesha inani elikhulu ledatha, ithebula le-pivot ku-Excel liyithuluzi eliphelele, elifingqa ulwazi ngaphandle kwesidingo sokubhala amakhulu efomula yesamba noma yokubala. Ngaphezu kwalokho, ukuguqula ububanzi bedatha bube amathebula asemthethweni e-Excel (Ctrl + T) kwenza kube lula kakhulu ukuphathwa kwezinkomba futhi kwenza incwadi yokusebenzela ibe nobungcweti kakhulu futhi kube lula ukuyigcina.
Uma ungumsebenzisi othuthukile, ungasebenzisa i-VBA ukudala imisebenzi yangokwezifiso. Isibonelo, ukubala amanani ahlukile usebenzisa iqoqo le-VBA kungaba ngokushesha okuphindwe kangamakhulu kunefomula ye-array eyinkimbinkimbi. Kodwa-ke, qaphela: Imisebenzi ye-VBA ingaba kancane kunemisebenzi eyakhelwe ngaphakathi uma ingahlelwanga kahle.
Amathiphu okukhiqiza okusheshayo nokulungisa
Ukuze wenze ngcono ukuhamba komsebenzi wakho wansuku zonke, kubalulekile ukwazi izinqamuleli zekhibhodi . Ukusebenzisa i-Ctrl+C kanye ne-Ctrl+V kuyinto elula, kodwa ukwazi i-Paste Special (Alt+E+S+V) ukuguqula amafomula abe amanani aqinile kuyindlela eyinhloko yokukhulula imemori lapho ungasadingi idatha ukuthi ibalwe kabusha.
Ukugcwalisa nge-Flash nakho kuyasiza kakhulu , ukuthola amaphethini edatha nokugcwalisa ngokuzenzakalelayo amakholomu ngaphandle kwesidingo samafomula ayinkimbinkimbi. Ukuze uthole ulwazi, ukusebenzisa izinhlamvu ze-wildcard (njenge-asterisk noma uphawu lombuzo) kukuvumela ukuthi uthole idatha ethile ngokushesha kakhulu kunokuhlunga ngesandla.
Okokugcina, uma incwadi yokusebenza ingalawuleki, isu elibaluleke kakhulu ukuhlukaniswa kwamafayela . Lokhu kuhilela ukuhlukanisa umsebenzi ube yizincwadi ezintathu ezihlukene: eyodwa yokufaka idatha eluhlaza, enye yokubala kokucubungula, kanye neyesithathu kuphela yokwethula imiphumela namadeshibhodi. Lokhu kuvimbela umthwalo wokucubungula ukuthi ungaphazamisi isibonelo esisodwa se-Excel.
Ukwenza i-Excel isebenze kahle kuncike ebhalansini phakathi kwehadiwe, njengokuba ne -RAM eyanele yokugwema i-disk paging, kanye nokwakheka kwedatha okuhlakaniphile okubeka phambili ubulula kunenkimbinkimbi yefomula. Ngokugwema ukuguquguquka, ukunciphisa ukuphindaphinda, kanye nokusebenzisa amathuluzi afana ne-Power Query, noma yimuphi uchwepheshe angaguqula ispredishithi ehamba kancane ibe uhlelo lokuhlaziya olunamandla noluphendulayo.
