- Aprēķinu dzinēja optimizācija, pārvaldot gaistošās funkcijas un izmantojot manuālo režīmu.
- Datu strukturēšanas stratēģijas, kuru pamatā ir palīgkolonnas un dublējumu novēršana.
- Uzlabotu rīku, piemēram, Power Query un VBA, ieviešana lielu informācijas apjomu apstrādei.
- Veiktspējas mērīšanas metodes, lai identificētu un novērstu vājās vietas sarežģītās grāmatās.
Esmu pārliecināts, ka tas ir noticis ar jums: jūs atverat Excel darbgrāmatu, kas izskatās pēc datu labirinta, un pēkšņi programma sasalst vai vienkārši prasa mūžību, lai atjauninātu summu. Ne jau tāpēc, ka jūsu dators ir lēns; iespējams, ka jūsu izklājlapas struktūrai ir grūtības ar Microsoft apstrādes dzinēju. Strādājot ar tūkstošiem rindu, veiktspēja kļūst kritiski svarīga, lai nezaudētu pacietību vai nepieļautu kļūdas tīras neapmierinātības dēļ.
Lai programma Excel darbotos lieliski, ar jaudīgu procesoru vien nepietiek; ir jāsaprot, kā programmatūra darbojas . Sākot ar RAM pārvaldību un beidzot ar atkarībām no formulām, ir pieejami dažādi triki un konfigurācijas, kas var pārveidot apjomīgu darbgrāmatu par ātru un atsaucīgu rīku. Šajā rakstā mēs aplūkosim visas stratēģijas, sākot no visvienkāršākajām līdz VBA koda izmantošanai, lai jūsu izklājlapas reaģētu uzreiz.
Aprēķinu dzinējs un ātruma pārvaldība
Kopš tā sauktā "lielā režģa" ieviešanas tādās versijās kā Excel 2007 un jaunākās versijās šūnu ierobežojums ir eksponenciāli pieaudzis. Tas ir ļāvis izveidot milzīgas datubāzes, bet tas ir arī atvieglojis lietotājiem ārkārtīgi lēnu darbgrāmatu izstrādi . Veiktspēja ir ļoti svarīga, jo, ja atbildes laiks pārsniedz vienu sekundi, mēs sākam zaudēt koncentrēšanos un darbplūsma pilnībā sabojājas.
Programma Excel izmanto inteliģentu pārrēķina sistēmu, kas izseko atkarības. Tā vietā, lai apstrādātu visu, tā atjaunina tikai mainītās šūnas un no tām atkarīgās šūnas. Tomēr ir gadījumi, kad šī sistēma tiek pārslogota. Lai ar to cīnītos, varam eksperimentēt ar aprēķinu režīmiem: automātiska pārrēķina ir ērta, bet riskanta lielās darbgrāmatās, savukārt manuāla pārrēķina (aktivizēta cilnē Formulas) ļauj mums precīzi izlemt, kad vēlamies, lai programma apstrādātu datus, nospiežot taustiņu F9.
Ja saskaraties ar darbgrāmatām, kuru atvēršana aizņem mūžību, ir pieejama uzlabota īpašība ar nosaukumu ForceFullCalculation . To iespējojot, izmantojot VBA redaktoru, programma Excel ignorēs viedos atjauninājumus un veiks pilnu aprēķinu, kas dažos sarežģītos scenārijos paradoksālā kārtā var būt ātrāk nekā mēģinājums uzturēt atkarību koku.
Kā identificēt un novērst sastrēgumus
Ne visām formulām ir vienāds faila lielums. Parasti lēnums neizriet no faila lieluma, bet gan no atkārtotām un liekām darbībām . Lai precīzi noteiktu problēmu, ideāla pieeja ir izmantot "iedziļināšanās" metodi: izmērīt aprēķina laiku visai darbgrāmatai, pēc tam pa lapām un visbeidzot pa šūnu blokiem. Lai nodrošinātu ķirurģisku precizitāti, varat izmantot laika makro, kuru pamatā ir Windows API (piemēram, MicroTimer funkcija), kas mēra līdz mikrosekundēm.
Kad problēma ir identificēta, mums jāievēro daži zelta likumi. Pirmais ir novērst aprēķinu dublikātus . Ir ļoti bieži, ka sarežģīta formula tiek kopēta tūkstošiem reižu; tā vietā ir vēlams pārvietot atkārtoto aprēķinu uz palīgšūnu un likt pārējiem vienkārši atsaukties uz šo rezultātu. Tas ievērojami samazina atsauču skaitu, kas Excel jāapstrādā.
Otrais noteikums koncentrējas uz funkciju efektivitāti. Piemēram, sakārtotu datu meklēšana ir daudz ātrāka nekā nešķirotu datu meklēšana. Ieteicams arī aizstāt funkciju IF un ISERROR kombināciju ar funkciju IFERROR , kas ir optimizēta ātrumam un tiešumam, iekļaujot profesionāļiem paredzētas uzlabotas Excel funkcijas.
Uzmanieties no gaistošām funkcijām un matricām
Dažas funkcijas ir īsti veiktspējas slazdi. Tā sauktās gaistošās funkcijas , piemēram, OFFSET, INDIRECT, TODAY vai NOW, pārrēķina katru reizi, kad darbgrāmatā notiek izmaiņas, pat ja tās nav saistītas ar formulu. Ja jums ir tūkstošiem šādu funkciju, pastāvīgā apstrāde izraisīs kursora aizturi un katrs klikšķis kļūs par apgrūtinājumu.
No otras puses, masīvu formulas bieži vien ir ļoti jaudīgas, taču patērē pārāk daudz resursu. Bieži vien visefektīvākais risinājums ir sadalīt lielo formulu vairākās palīgkolonnās. Lai gan var šķist, ka mēs pārblīvējam izklājlapu, mēs patiesībā palīdzam Excel daudzpavedienu aprēķiniem efektīvāk sadalīt darba slodzi pa procesora kodoliem.
Nosacījumformatēšana arī ietilpst šajā riska kategorijā. Tā kā tā ir mainīga, sarežģītu krāsu noteikumu piemērošana lieliem diapazoniem var palēnināt ekrāna vizuālo reakciju. Ideālā gadījumā tā jāizmanto taupīgi vai jāaizstāj ar VBA procesiem, ja krāsu loģika ir ļoti sarežģīta.
Uzlaboti datu pārvaldības rīki
Kad datu apjoms pārsniedz tradicionālo formulu ietilpību, ir pienācis laiks izmantot spēcīgākos rīkus. Power Query neapšaubāmi ir labākais papildinājums pēdējos gados. Tas ļauj tīrīt, pārveidot un apvienot datus ārpus galvenā režģa, novēršot darbgrāmatas pārslodzi ar sarežģītām formulām un saglabājot faila atsaucību.
Tiem, kam nepieciešams ātri analizēt milzīgu datu apjomu, Pivot tabula programmā Excel ir labākais rīks, kas apkopo informāciju bez nepieciešamības rakstīt simtiem summēšanas vai skaitīšanas formulu. Turklāt datu diapazonu konvertēšana oficiālās Excel tabulās (Ctrl + T) ievērojami vienkāršo atsauču pārvaldību un padara darbgrāmatu daudz profesionālāku un vieglāk uzturējamu.
Ja esat pieredzējis lietotājs, varat izmantot VBA , lai izveidotu pielāgotas funkcijas. Piemēram, unikālu vērtību skaitīšana, izmantojot VBA kolekciju, var būt simtiem reižu ātrāka nekā sarežģīta masīva formula. Tomēr esiet uzmanīgi: VBA funkcijas var būt lēnākas nekā iebūvētās funkcijas, ja tās nav pareizi ieprogrammētas.
Ātri padomi produktivitātei un apkopei
Lai optimizētu ikdienas darbplūsmu, ir svarīgi apgūt īsinājumtaustiņus . Ctrl+C un Ctrl+V lietošana ir pamata lieta, taču īpašās ielīmēšanas (Alt+E+S+V) apgūšana formulu konvertēšanai statiskās vērtībās ir lielisks triks atmiņas atbrīvošanai, kad dati vairs nav jāpārrēķina.
Arī zibenīgā aizpildīšana ir ļoti noderīga , jo tā nosaka datu modeļus un automātiski aizpilda kolonnas, neizmantojot sarežģītas formulas. Informācijas izguvei aizstājējzīmju (piemēram, zvaigznītes vai jautājuma zīmes) izmantošana ļauj atrast konkrētus datus daudz ātrāk nekā manuāla filtrēšana.
Visbeidzot, ja darbgrāmatu joprojām nav iespējams pārvaldīt, radikālākā stratēģija ir failu segmentācija . Tas ietver darba sadalīšanu trīs atsevišķās darbgrāmatās: vienu neapstrādātu datu ievadīšanai, otru aprēķinu apstrādei un trešo tikai rezultātu un informācijas paneļu attēlošanai. Tas novērš apstrādes slodzes radītu vienas Excel instances avāriju.
Lai programma Excel darbotos nevainojami, ir nepieciešams līdzsvars starp aparatūru, piemēram, pietiekamu RAM apjomu , lai izvairītos no diska lapošanas, un viedu datu arhitektūru, kas vienkāršību vērtē augstāk par formulu sarežģītību. Izvairoties no nepastāvības, samazinot dublēšanos un izmantojot tādus rīkus kā Power Query, jebkurš profesionālis var pārveidot lēnu izklājlapu par jaudīgu un atsaucīgu analītikas sistēmu.
