Probleme mari în foaia de calcul mică

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.

În timp ce acest caz de afaceri se învârte în jurul unui program intern de formare corporativă, o altă modalitate excelentă de a reduce acest cost de oportunitate este prin cursuri precum școala Excel a Chandoo.org, cursurile VBA și alte cursuri Chandoo.

Ca să nu mai vorbim de alte cursuri fantastice pe care le veți găsi promovate pe web dacă vă uitați.

Și totuși, altele sunt interacțiuni în locuri precum Forumul Chandoo, unde veți găsi o armată de ninja cu mai multă experiență colectivă decât Borg din Star Trek. Mintea de stup care este un forum nu cunoaște egal.

Și, desigur, veți găsi o mulțime de informații chiar pe acest blog, în articole precum am spus că foaia dvs. de calcul este cu adevărat GRASĂ, nu PHAT reală!

Jeff Weir - un local din Galactic North acolo, în Windy Wellington, Noua Zeelandă - este mai volatil decât INDIRECT și mai aleatoriu decât RAND. De fapt, starea sa de spirit se poate rezuma destul de mult prin aceasta:

Așa este, pur #VALUE!

Aflați mai multe la http: www.heavydutydecisions.co.nz

Împărtășește acest sfat cu colegii tăi

Obțineți sfaturi GRATUITE pentru Excel + Power BI

E-mailuri simple, distractive și utile, o dată pe săptămână.

Învață și fii minunat.

• 45 de comentarii
• Puneți o întrebare sau spuneți ceva. Categorie: Howtos Excel, hacks, Learn Excel, Postări de Jeff

Bine ați venit pe Chandoo.org

Vă mulțumesc mult pentru vizită. Scopul meu este să fac ești minunat în Excel și Power BI. Fac acest lucru împărtășind videoclipuri, sfaturi, exemple și descărcări pe acest site. Există mai mult de 1.000 de pagini cu toate lucrurile Excel, Power BI, tablouri de bord și VBA aici. Mergeți mai departe și petreceți câteva minute pentru a fi MINUNAT. Citiți povestea mea • Carte de sfaturi Excel GRATUITĂ

De la simplu la complex, există o formulă pentru fiecare ocazie. Consultați lista acum.

Calendare, facturi, trackere și multe altele. Toate gratuite, distractive și fantastice.

Power Query, Model de date, DAX, Filtre, Slicers, Formate condiționale și diagrame frumoase. Totul este aici.

Încă mai aveți gard despre Power BI? În acest ghid introductiv, aflați ce este Power BI, cum să îl obțineți și cum să creați primul raport de la zero.

Căutare imagine - Cum se afișează o imagine dinamică într-o celulă [Excel Trick]

Doriți vreodată să aveți o căutare de imagini sau imagini în Excel? Ceva de genul descrierii de mai sus.

În acest articol, aflați cum să configurați o căutare de imagini utilizând Excel. Puteți utiliza acest lucru pentru a afișa detaliile personalului, imaginile produsului sau piesele mașinii etc.

Grilă cu 9 cutii pentru cartografierea talentelor - HR pentru Excel - Șablon și explicație

6 Trebuie să știți variațiile graficului liniar pentru analiza datelor

Validarea datelor pe două niveluri [Truc Excel]

Formula Excel pentru a converti formatul calendarului în tabel

• Excel pentru începători
• Abilități avansate Excel
• Tablouri de bord Excel
• Ghid complet pentru tabelele pivot
• Top 10 formule Excel
• Comenzi rapide Excel
• # Buget minunat vs. Diagrama reală
• 40+ Exemple VBA

Sfaturi conexe

Căutare imagine - Cum se afișează o imagine dinamică într-o celulă [Excel Trick]

Grilă cu 9 cutii pentru cartografierea talentelor - HR pentru Excel - Șablon și explicație

Validarea datelor pe două niveluri [Truc Excel]

Formula Excel pentru a converti formatul calendarului în tabel

Plan de proiect - Diagramă Gantt cu capacitate de descărcare în jos [Șabloane]

Aceste trucuri din tabelul pivot vă economisesc masiv timpul

45 de răspunsuri la „Probleme mari în foaia de calcul mică”

M-am confruntat cu aceeași problemă de sine cu delegații mei recent. DE DOUĂ cu doi delegați separați în locuri diferite.

Aruncați o privire asupra propriei mele postări de blog pe una dintre ele, unde descriu soluția mea pentru tabelul pivot: http://excelmaster.co sub titlul aveți nevoie de pivoti

M-am confruntat cu aceeași problemă de sine cu delegații mei recent. DE DOUĂ cu doi delegați separați în locuri diferite.

Aruncați o privire la propria postare de blog pe una dintre ele, unde descriu soluția mea pentru tabelul pivot: excelmaster dot co sub titlul „aveți nevoie de pivoti”

Foarte interesant mulțumesc, mereu am crezut că se datorează foilor mele foarte complicate și unui computer lent (AMD + RAM de 4 GB). Dar, după ce m-am gândit puțin, am eliminat o grămadă întreagă de formatare din mesele mele, iar acum lucrurile rulează puțin mai repede!

Îmi las foile de calcul în modul calc manual, dar asta pentru că lucrez constant cu foi de calcul care conțin între 20.000 - 100.000 de rânduri, uneori până la 600.000, cu în general în jur de 20-30 de coloane (înainte de adăugarea câmpurilor calculate). Poate sunt o excepție, datorită cantității de date cu care lucrez, dar voi arunca asta acolo și voi vedea dacă pierd oportunități de eficiență.

Am un registru de lucru care analizează conturile pentru a determina prioritatea apelului (pentru a colecta AR scadent), luând în calcul ATB-ul, soldul de probă în vârstă, cât sunt datorate, cât este curent, 1-30 zile scadente, 31-60 zile trecute datorate, etc), ADP (zilele medii de plată), procentul din limita de credit utilizată, indiferent dacă au avut un NSF (notificarea insuficientă a fondurilor, practic cecul le-a fost returnat), dacă avem numerar neaplicat în cont care ar putea echilibra o sumă datorată dacă apelăm și obținem aprobarea de a o utiliza în acest scop), condițiile de plată (cât timp au de la facturare până la data scadenței plății) și factorii din toate acele bucăți de date pentru a veni cu un punct ponderat total pentru a vedea ce conturi trebuie să fie în partea de sus a listei de apeluri, care este apoi distribuit între colecționari.

Există, în general, în jur

20.000 de conturi pe listă. Datele provin din 5 rapoarte SAP diferite. Am avut inițial un registru de lucru diferit pentru fiecare raport și l-am conectat din foaia de calcul ranking/calc, dar l-am accelerat puțin punând toate datele pe file (în ordinea calculelor) într-un registru de lucru de la stânga la dreapta care duce la foaie de calcul la final.

Lucrurile pe care le-am găsit până acum care ajută la reducerea timpului de calcul:

Curățați și sortați datele brute înainte de a le introduce în registrul de calcul. Acest lucru poate părea evident sau blasfem pe baza antecedentelor dvs. Obișnuiam să lucrez în ilustrație digitală și era esențial să am întotdeauna datele brute originale, neatinse, blocate în spate, cu toate ajustările stratificate deasupra, astfel încât să nu pierdeți niciodată sursa, așa că practica mea inițială a fost să construiește întotdeauna foile mele pentru a utiliza datele brute exact așa cum ies din SAP. Cu toate acestea, timpul de calcul economisit atunci când faceți doar puțină muncă pentru a vă asigura că datele sunt aranjate în mod ordonat este semnificativ. Totuși, de fiecare dată când atingeți datele, există o altă oportunitate de eroare, deci este ceva echilibrat.

Când trebuie să eliminați anumite rânduri dintr-un tabel pe baza criteriilor, sortați mai întâi tabelul după acel criteriu și reduce drastic timpul necesar când ștergeți toate rândurile cu criterii potrivite.

Dacă aveți mai multe coloane care fac referire la același tabel sursă, nu utilizați index și potrivire în fiecare dintre aceste coloane. Utilizați potrivirea într-o coloană pentru a găsi la ce rând al tabelului sursă să priviți, apoi folosiți doar indexul pentru celelalte coloane și faceți referire la numărul rândului returnat din coloana de potrivire. De exemplu, ultima piesă a registrului meu de lucru este o foaie care construiește o listă de lucru pentru un colecționar individual. Introduceți numărul colecționarului în partea de sus și acesta îl compară cu numărul total de colecționari și creează lista. Dacă aveți 7 colecționari, atunci acesta se uită la clasament și listează fiecare cont al 7-lea de sus în jos. O coloană generează numerele de rânduri ale acelor conturi folosind potrivirea, apoi restul folosesc indexul pentru a trage datele.

Sunt pe cale să schimb lumea din nou, de data aceasta la contabilitate, așa că fac un pic de curățenie pentru a mă asigura că toate SOP-urile mele sunt în ordine și că foile de calcul pe care le folosesc sunt cât mai curate și eficiente posibil. Dacă cineva vede o defecțiune în ceea ce am descris, sau o oportunitate de a o accelera (de preferință fără a utiliza VBA, las lucrarea oamenilor care vor avea nevoie de mult antrenor doar pentru a înțelege indexul și potrivirea, așa că VBA este va fi doar un mister mai profund pentru ei) aș fi bucuros să-l aud.

De asemenea, aș lua în considerare utilizarea Power Pivot, deoarece cred că poate combina date din surse diferite

• Ușor Slow Cooker Rețetă Șuncă Life Made Sweet
• Carbohidrati în McDonalds Oreo McFlurry, dimensiune gustare
• Tort de mere cu caramel într-o aragază lentă Fată flămândă
• Rețetă preferată de supă de legume cu găină lentă; cu Sănătos
• Mărimea fulgului de cereale influențează aportul de calorii - ScienceDaily