Remedierea dimensiunii fișierelor umflate și a calculului lent în Excel
Jeff Weir
Oameni buni. Jeff aici. Recent am făcut o prezentare despre eficiența Excel pentru o grămadă de analiști, în care - printre altele - am subliniat că, dacă vreți să vă trezi vreodată să comutați calculul pe Manual, probabil că este ceva în neregulă cu foaia dvs. de calcul. Iată diapozitivul:
Acest lucru l-a determinat pe unul dintre participanți să vină la mine pentru sfaturi cu privire la restructurarea unei foi de calcul cu această problemă. Acest analist avea un fișier cu doar 6000 de rânduri de date în el, dar dimensiunea fișierului era ceva de genul 35MB și, după fiecare schimbare, a trebuit să aștepte cel puțin un minut pentru ca fișierul să se recalculeze înainte de a putea face altceva.
Se pare că au existat două probleme cu fișierele ei ușor de rezolvat.
Gama Confused
În primul rând, a apărut o problemă cu gama utilizată - zona dintr-o foaie de lucru despre care Excel crede că conține toate funcționările și datele dvs. Puteți afla ce este aceasta pentru fiecare foaie de calcul apăsând [Ctrl] + [End] și văzând la ce celulă vă duce. Sperăm că vă va duce la celula din partea de jos, din dreapta pe care ați folosit-o de fapt în foaie:
Dar, ocazional, veți vedea că vă poate duce mult, mult sub celula respectivă. Poate până în partea de jos a grilei:
Asta e rău. De ce? Deoarece atunci când Excel salvează un fișier, acesta include informații despre lucruri, cum ar fi tipul de formatare a celulei utilizat în intervalul utilizat. Dacă intervalul utilizat include milioane de celule care nici măcar nu sunt utilizate, atunci informațiile pe care Excel le salvează cu privire la aceste celule pot arunca cu adevărat dimensiunea fișierului. Exact acest lucru se întâmplase în cazul foii de calcul în cauză. După ce am resetat intervalul utilizat, dimensiunea fișierului a scăzut de la 35 MB la aproximativ 2 MB.
De multe ori puteți reseta gama utilizată pur și simplu selectând toate rândurile goale de sub datele dvs. și apoi ștergându-le. Pentru aceasta, selectați întregul rând imediat sub datele dvs., apoi apăsați [Ctrl] + [Săgeată în jos] pentru a extinde selecția chiar în partea de jos a foii, apoi faceți clic dreapta și selectați Șterge:
Rețineți că trebuie să utilizați opțiunea clic dreapta> ȘTERGERE, NU tasta Ștergere de pe tastatură. Apăsând tasta Ștergere nu resetați intervalul utilizat. De fapt, acesta este de multe ori motivul pentru care gama utilizată este greșită, totuși reflectă în continuare unele date care erau în foaie, dar pe care utilizatorul le-a șters ulterior folosind tastatura.
După ce ați făcut acest lucru, apăsați din nou pe [Ctrl] + [End] și vedeți unde ajungeți - in speranta în colțul din dreapta jos al datelor.
Uneori, acest lucru nu rezolvă problema și totuși vă aflați cu mult sub datele dvs. În acest caz, un pic de VBA va fi de obicei suficient. Aș sugera să introduceți codul de mai jos în registrul dvs. de lucru Macro personal, pentru momente ca acestea:
Prea mult SUMIF
A doua problemă este că fiecare fișier conținea ceva de genul a 60.000 de formule SUMIF în ele. Și fiecare dintre aceste formule a făcut referire la două coloane întregi, mai degrabă decât la cele 2500 de rânduri care conțineau de fapt date. Este foarte ușor să vedeți cât de mare este o problemă pe care ați putea să o aveți, pur și simplu făcând un Find All pentru numele funcției pe care o urmăriți:
Puteți arunca 60.000 de instrucțiuni VLOOKUPS sau IF sau alte funcții run-of-the-mill pe Excel și nici măcar nu va clipi. Dar 60.000 de funcții de reducere a numărului de resurse, cum ar fi SUMIF, SUMPRODUCT, COUNTIF etc., îndreptate către intervale foarte mari, vor face ca Excel să scuture, dacă nu închide ochii complet pentru perioade mari de timp.
Asta pentru că aceste funcții seamănă cu cele ale lui Ferrari ... foarte puternice, dar foarte scumpe. Un SUMIF va călători foarte repede pe autostradă. Câteva sute de SUMIF-uri pe aceeași întindere vor încerca să fluiere destul de repede. Zeci de mii dintre ei se vor prăbuși unul cu celălalt:
(Imaginea de mai sus provine din acest articol din New York Times, care detaliază o grămadă spectaculoasă de trafic în Japonia în 2011, care a lăsat o autostradă presărată cu resturile sparte de opt mașini sport Ferrari, Lamborghini și trei Mercedes. Nimeni nu a fost rănit grav în afară de răniți grav. mândrie și o creștere semnificativă a primelor de asigurare în anul următor.)
Adesea puteți utiliza un tabel pivot pentru a face același lucru ca o grămadă de funcții precum SUMIF, COUNTIF, SUMPRODUCT etc. PivotTables sunt instrumente naturale de agregare și filtrare. În acest caz eu ar putea utilizați doar un singur tabel pivot pentru a înlocui cele 60.000 de SUMIF-uri, iar timpul de recalculare a scăzut de la minute la milisecunde. Acum, raportarea despre acest proces de afaceri este fără efort.
O foaie de calcul, două moravuri
Am două moravuri de împărtășit cu privire la acest lucru.
Primul este să vă țineți ochii deschiși pentru a găsi semne de probleme în foile de calcul. Gândiți-vă la FileSize and Recalculation Time ca la contorul de turații al mașinii dvs. ... dacă devine din ce în ce mai roșu, apoi trageți și verificați sub capotă.
Al doilea - și nu pot sublinia acest lucru suficient - este importanța pentru organizații de a educa toți utilizatorii cu privire la modul de recunoaștere a simptomelor ineficienței. Nu toți trebuie să știe cum să o trateze (deși ar fi bine), ci doar cum să o diagnosticheze. Deoarece dacă nu este diagnosticată, ineficiența evitabilă impune costuri semnificative, continue și foarte reale de oportunitate. O sumă reală de dolari.
Creșterea gradului de conștientizare a semnelor de pericol este probabil cea mai mare oportunitate de câștig de eficiență și reducere a riscurilor pe care orice inițiativă de formare o poate oferi, la cel mai mic cost. Este un schimbător de jocuri.
Două moravuri, mai multe remedii.
Pe blogul Daily Dose of Excel, am postat recent un simulacru de afaceri centrat pe investiții corporative în programul de formare Excel. Există mult mai mult de gândit acolo și chiar mai mult în comentarii, așa că mergeți să aruncați o privire și vă rog să lăsați un comentariu acolo cu propriile gânduri.