Cititorii Lifehacker sunt deja familiarizați Denis Batianov care a împărtășit cu noi. Astăzi, Denis va vorbi despre cum să evităm cele mai frecvente probleme cu Excel, pe care adesea le creăm pentru noi înșine.

Permiteți-mi să fac imediat o rezervare că materialul din acest articol este destinat utilizatorilor începători de Excel. Utilizatorii experimentați au dansat deja energic pe această greblă de mai multe ori, așa că sarcina mea este să protejez „dansatorii” tineri și neexperimentați de acest lucru.

Nu dați titluri de coloane de tabel

Multe instrumente Excel, cum ar fi sortarea, filtrarea, tabelele inteligente și tabelele pivot, presupun că datele dvs. conțin titluri de coloane. În caz contrar, fie nu le veți putea folosi deloc, fie nu vor funcționa corect. Asigurați-vă întotdeauna că tabelele dvs. conțin titluri de coloană.

Goliți coloanele și rândurile din interiorul tabelelor dvs

Acest lucru încurcă Excel. Când întâlnește un rând sau o coloană goală în tabelul tău, începe să creadă că ai 2 tabele, nu unul. Va trebui să o corectați constant. De asemenea, nu ascundeți rândurile/coloanele de care nu aveți nevoie în tabel, este mai bine să le ștergeți.

Sunt mai multe tabele pe o singură foaie

Dacă acestea nu sunt tabele minuscule care conțin cărți de referință cu valori, atunci nu ar trebui să faceți acest lucru.

Va fi incomod pentru dvs. să lucrați complet cu mai mult de o masă pe o foaie. De exemplu, dacă un tabel este situat în stânga și al doilea în dreapta, atunci filtrarea unui tabel îl va afecta pe celălalt. Dacă tabelele sunt situate unul sub celălalt, atunci este imposibil să utilizați înghețarea zonelor și, de asemenea, va trebui să căutați constant unul dintre mese și să efectuați manipulări inutile pentru a plasa cursorul tabelului pe acesta. Ai nevoie de el?

Datele de același tip sunt localizate artificial în coloane diferite

Foarte des, utilizatorii care cunosc Excel mai degrabă superficial preferă acest format de tabel:

S-ar părea că avem un format inofensiv pentru acumularea de informații despre vânzările agenților și amenzile acestora. Acest aspect al tabelului este bine perceput vizual de oameni, deoarece este compact. Cu toate acestea, credeți-mă, este un coșmar complet să încercați să extrageți date din astfel de tabele și să obțineți subtotaluri (informații agregate).

Ideea este că acest format conține 2 dimensiuni: până la , trebuie să vă decideți asupra rândului parcurgând bucla prin ramură, grup și agent. Când găsiți coloana potrivită, va trebui să căutați coloana necesară, deoarece există multe dintre ele. Și această „bidimensionalitate” complică foarte mult lucrul cu un astfel de tabel chiar și pentru instrumentele standard Excel - formule și tabele pivot.

Dacă construiți un tabel pivot, veți descoperi că nu există nicio modalitate de a obține cu ușurință date pe an sau trimestru, deoarece indicatorii sunt separați în câmpuri diferite. Nu aveți un câmp de volum de vânzări care poate fi manipulat convenabil, ci mai degrabă 12 câmpuri separate. Va trebui să creați manual câmpuri calculate separate pentru trimestre și ani, deși dacă ar fi toate într-o singură coloană, tabelul pivot ar face acest lucru pentru dvs.

Dacă doriți să utilizați formule de însumare standard precum SUMIF, SUMIFS, SUMPRODUCT, veți constata, de asemenea, că nu vor funcționa eficient cu acest aspect de tabel.

Răspândirea informațiilor în diferite foi ale cărții „pentru comoditate”

O altă greșeală comună este, având un format de tabel standard și nevoie de analize bazate pe aceste date, răspândirea acestora în foi separate într-un registru de lucru Excel. De exemplu, adesea creează foi separate pentru fiecare lună sau an. Ca rezultat, cantitatea de analiză a datelor este efectiv înmulțită cu numărul de foi create. Nu trebuie să faci asta. Acumulați informații pe O Foaie.

Informații în comentarii

Utilizatorii adaugă adesea informatii importante, de care ar putea avea nevoie, în comentariul celulei. Rețineți că ceea ce este în comentarii vă puteți uita doar la (dacă îl găsiți). E greu să-l bagi în celulă. Vă recomand să creați o coloană separată pentru comentarii.

Formatarea mizeriei

Cu siguranță nu va adăuga nimic bun la masa ta. Acest lucru pare dezamăgitor pentru persoanele care vă folosesc foile de calcul. În cel mai bun caz, nu vor acorda nicio importanță acestui lucru, în cel mai rău caz, vor crede că ești neorganizat și neglijent în treburile tale. Urmărește următoarele:

Unirea celulelor

Folosiți îmbinarea celulelor numai atunci când nu vă puteți lipsi de ea. Celulele îmbinate fac foarte dificilă manipularea intervalelor cărora le aparțin. Există probleme la mutarea celulelor, la introducerea celulelor etc.

Combinând text și numere într-o singură celulă

O impresie dureroasă este făcută de o celulă care conține un număr atașat în spate cu constanta de text „RUB”. sau „USD” introdus manual. Mai ales dacă nu este formă tipărită, dar o masă obișnuită. Operațiile aritmetice cu astfel de celule sunt în mod natural imposibile.

Numerele ca text într-o celulă

Evitați stocarea datelor numerice într-o celulă în format text. În timp, unele dintre celulele dintr-o astfel de coloană vor avea format text, iar unele în mod obișnuit. Acest lucru va cauza probleme cu formulele.

Dacă masa dumneavoastră va fi prezentată printr-un proiector LCD

Alege cele mai contrastante combinații de culoare și fundal. Un fundal întunecat și litere deschise arată bine pe proiector. Cea mai teribilă impresie o face roșu pe negru și invers. Această combinație arată un contrast extrem de scăzut pe un proiector - evitați-o.

Modul pagină al foii de lucru în Excel

Acesta este același mod în care Excel arată cum va fi împărțită foaia în pagini atunci când este imprimată. Marginile paginii sunt evidențiate în albastru. Nu recomand să lucrați în mod constant în acest mod, lucru pe care mulți oameni îl fac, deoarece driverul de imprimantă este implicat în procesul de afișare a datelor pe ecran și acest lucru depinde de multe motive (de exemplu, imprimanta este conectată în rețea și în acest moment indisponibil) este plin de înghețuri în procesul de vizualizare și recalcularea formulelor. Lucrați ca de obicei.

Chiar mai mult informatii utile puteți afla despre Excel la

Salutare prieteni. Ți s-a întâmplat vreodată ca, după ce ai introdus o formulă, formula în sine să fie afișată în celulă în locul rezultatului calculului? Acest lucru este puțin descurajator, pentru că ați făcut totul bine, dar nu este clar ce ați primit. Cum pot forța programul să calculeze formula în acest caz?

Mai întâi trebuie să înțelegeți motivele acestui comportament și apoi să corectați situația. Iar motivele pot fi următoarele.

Afișarea formulei este activată

Excel are un mod de verificare a calculelor. Când este pornit, puteți vedea formulele pe foaia de lucru. Am vorbit despre cum și de ce să folosiți afișarea formulei în . Verificați dacă este activat, apoi dezactivați-l. Există un buton pe panglică Formule – Dependențe de formule – Afișați formule. Dacă este activat, faceți clic pe el pentru a-l dezactiva.

Adesea, afișarea formulelor este activată accidental prin apăsarea combinației de taste Ctrl+`.

Formula este percepută de program ca text

Dacă Excel crede că o celulă conține un șir de text, nu va calcula valoarea, ci va afișa pur și simplu conținutul. Pot exista mai multe motive pentru aceasta:

• Formatul celulei este setat la text. Pentru program, acesta este un semnal că conținutul trebuie să fie scos în aceeași formă în care este stocat.
  
  
  Schimbați formatul la Numeric așa cum este menționat.
• Nu există semnul „=" la începutul formulei. După cum am spus în, orice calcul începe cu semnul egal.
  
  
  Dacă îl ratați, conținutul va fi perceput ca text. Adăugați un semn egal și recalculați formula.
• Spațiu înainte de „=". Dacă un spațiu este specificat accidental înaintea semnului egal, acesta va fi interpretat și ca text.
  
  
  Această eroare este greu de observat, așa că va trebui să o verificați manual. Scoateți spațiul suplimentar și recalculați.
• Formula este între ghilimele. Această eroare apare atunci când lucrăm cu date text. Desigur, informațiile între ghilimele sunt tratate ca text.
  
  
  Eliminați ghilimele suplimentare, apăsați Enter pentru a recalcula.

Am enumerat toate cazurile comune în care formula este afișată în loc de rezultat. Cred că cazul dvs. va fi pe lista de mai sus, rezolvarea problemei nu va fi dificilă. Daca nu a iesit, scrie in comentarii.

Promite a fi foarte interesant. Ne vom uita la câteva tehnici simple de scriere rapidă a formulelor. Dacă le aduceți la automatitate, munca voastră se va face mai ușor și mai rapid. Abonați-vă la actualizări pentru a nu rata!

Astăzi am întâlnit o situație în care... formulele excel nu au funcționat la tragere, adică erau două coloane și era necesar să se calculeze diferența primelor două coloane din a treia coloană. Ca rezultat, formula este făcută și întinsă până la sfârșit, dar rezultatul din prima celulă este copiat în celulele rămase.

1 Opțiune

Este posibil să fi dezactivat recalcularea automată a foii pentru a face acest lucru, trebuie doar să apăsați tasta F9

2 Opțiune pentru toate versiunile de Office

Verificați dacă formatul celulei nu este text (din această cauză, calculele și, în consecință, formulele pot să nu funcționeze)

PS pentru a remedia acest lucru, selectați celulele și selectați Format general (vezi figura Format de celule)

3 Opțiune pentru Office 2010

Verificați parametrii de calcul al formulei în setări.

Pentru a face acest lucru, faceți clic pe meniul FIȘIER-> PARAMETRI-> FORMULE și în meniul „Opțiuni de calcul” uitați-vă unde aveți o bifă (vezi imaginea ar trebui să fie așa)

4 Opțiune pentru Office 2007/2010

Pentru Office 2007 calea este așa Formula->Opțiuni de calcul->și puneți o bifă Automat, cu excepția tabelelor de date.(vezi poza de mai jos)

Puteți personaliza afișarea valorilor zero într-o celulă sau puteți utiliza un set de standarde de formatare în tabel care vă solicită să ascundeți valorile zero. Puteți afișa și ascunde valorile nule în diferite moduri.

Ascundeți și afișați toate valorile nule într-o foaie de lucru

Selecta Fişier > Opțiuni > În plus.

Într-un grup

• Afișați zerouri în celulele care conțin valori zero.

Ascunderea valorilor nule în celulele selectate

Acești pași ascund valorile zero în celulele selectate folosind un format numeric. Valorile ascunse apar numai în bara de formule și nu sunt tipărite. Dacă valoarea dintr-una dintre aceste celule se schimbă la o valoare diferită de zero, aceasta va fi afișată în celulă și formatul valorii va fi similar cu formatul general al numărului.

Se afișează valori ascunse.

Evidențiați celulele cu valori zero ascunse.

Puteți apăsa tastele CTRL+1 sau pe filă Acasă clic Formatare > Formatare celule.

Pentru a aplica formatul de număr implicit, selectați Număr > Generalși apăsați butonul Bine.

Ascunderea valorilor nule returnate de o formulă

Pe fila Acasă Formatare condiționată și alegeți Reguli de evidențiere a celulelor > Egal.

În câmpul din stânga, introduceți 0 .

În câmpul din dreapta, selectați Format personalizat.

În câmp Format de celule deschide fila Font.

Pe listă Culoare selectați culoarea albă și faceți clic pe butonul Bine.

Afișați zerourile ca spații sau liniuțe

Pentru a rezolva această problemă, utilizați funcția IF.

Dacă o celulă conține valori zero, utilizați o formulă ca aceasta pentru a returna o celulă goală:

IF(A2-A3=0;"";A2-A3)

Iată cum să citiți formula. Dacă rezultatul calculului (A2-A3) este „0”, nu este afișat nimic, inclusiv „0” (acesta este indicat prin ghilimele duble „”). În caz contrar, este afișat rezultatul calculului A2-A3. Dacă trebuie să lăsați celulele goale, dar să afișați altceva decât „0”, introduceți o cratimă „-” sau alt simbol între ghilimele duble.

Selectați un raport PivotTable.

Pe fila Analizăîn grup Tabel pivot Opțiuniși selectați Opțiuni.

Accesați fila Aspect și format

• Schimbarea afișajului de eroareÎn câmp Format bifați caseta Pentru afisarea erorilor

  Schimbați afișarea celulelor goale Bifați caseta Afișaj pentru celulele goale

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare sau propriile preferințe vă cer să afișați sau să ascundeți valorile nule, există mai multe moduri de a implementa toate aceste cerințe.

Selecta Fişier > Opțiuni > În plus.

Într-un grup Afișați opțiunile pentru foaia următoare selectați o foaie, apoi efectuați una dintre următoarele:

• Pentru a afișa valorile zero (0) în celule, bifați caseta de validare Afișați zerouri în celulele care conțin valori zero.

  Pentru a afișa valori zero ca celule goale, debifați caseta de selectare Afișați zerouri în celulele care conțin valori zero.

Puteți apăsa tastele CTRL+1 sau pe filă Acasă clic Formatare > Formatare celule.

În câmp Tip intra 0;-0;;@

Note:

Valorile ascunse apar doar în bara de formule sau în celulă dacă îi editați conținutul. Aceste valori nu sunt tipărite.

Pentru a afișa din nou valorile ascunse, selectați celulele, apoi apăsați CTRL+1 sau pe filă Acasăîn grup Celulele plasați cursorul peste element Formatși selectați Format de celule. Pentru a aplica formatul de număr implicit, în listă Categorie selecta General. Pentru a afișa din nou data și ora, selectați formatul adecvat de dată și oră din Număr.

Selectați celula care conține valoarea zero (0).

Pe fila Acasăîn grup Stiluri faceți clic pe săgeata de lângă element Formatare condiționată, plasați cursorul peste element Reguli de selecție a celulelorși selectați o opțiune Egal.

În câmpul din stânga, introduceți 0 .

În câmpul din dreapta, selectați Format personalizat.

În caseta de dialog Format de celule deschide fila Font.

În câmp Culoare alege culoarea albă.

Pentru a efectua această sarcină, utilizați funcția DACĂ.

Exemplu

Funcția IF.

Ascunderea valorilor nule într-un raport PivotTable

Pe fila Opțiuniîn grup Opțiuni pentru tabel pivot faceți clic pe săgeata de lângă comandă Opțiuniși selectați Opțiuni.

Accesați fila Aspect și format, apoi faceți următoarele:

În câmp Format bifați caseta Pentru afisarea erorilor. Introduceți în câmp valoarea pe care doriți să o afișați în loc de erori. Pentru a afișa erorile ca celule goale, eliminați tot textul din câmp.

Bifați caseta Afișaj pentru celulele goale. Introduceți în câmp valoarea în care doriți să o afișați celule goale. Pentru a le păstra necompletate, eliminați tot textul din câmp. Pentru a afișa valori zero, debifați această casetă de selectare.

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare sau propriile preferințe vă cer să afișați sau să ascundeți valorile nule, există mai multe moduri de a implementa toate aceste cerințe.

Afișați sau ascundeți toate valorile nule dintr-o foaie de lucru

Ascunderea valorilor zero în celulele selectate folosind formatul numeric

Acești pași vă permit să ascundeți valorile zero în celulele selectate. Dacă valoarea dintr-una dintre celule devine diferită de zero, formatul acesteia va fi același cu formatul general al numărului.

Selectați celulele care conțin valori zero (0) pe care doriți să le ascundeți.

Puteți apăsa tastele CTRL+1 sau pe filă Acasăîn grup Celulele clic Formatare > Formatare celule.

În câmp Tip intra 0;-0;;@

Note:

Ascunderea valorilor nule returnate de o formulă folosind formatarea condiționată

Selectați celula care conține valoarea zero (0).

Pe fila Acasăîn grup Stiluri faceți clic pe săgeata de lângă buton Formatare condiționatăși alegeți Reguli de evidențiere a celulelor > Egal.

În câmpul din stânga, introduceți 0 .

În câmpul din dreapta, selectați Format personalizat.

În caseta de dialog Format de celule deschide fila Font.

În câmp Culoare alege culoarea albă.

Utilizați o formulă pentru a afișa zerourile ca spații sau liniuțe

Pentru a îndeplini această sarcină, utilizați funcția IF.

Exemplu

Pentru a face acest exemplu mai ușor de înțeles, copiați-l pe o coală goală de hârtie.

Copierea unui exemplu

Evidențiați exemplul oferit în acest articol.

Important: Nu evidențiați titlurile de rând sau de coloană.

Evidențierea unui exemplu în ajutor

Apăsați CTRL+C.

În Excel, creați un registru de lucru sau o foaie goală.

Selectați celula A1 de pe foaie și apăsați CTRL+V.

Important: Pentru ca exemplul să funcționeze corect, acesta trebuie introdus în celula A1.

Pentru a comuta între vizualizarea rezultatelor și vizualizarea formulelor care returnează acele rezultate, apăsați CTRL+` (marca de accent) sau pe fila FormuleÎn grupul Dependențe de formule, faceți clic Afișați formule.

Copiind exemplul pe o foaie goală, îl puteți personaliza pentru a se potrivi nevoilor dvs.

Pentru mai multe informații despre utilizarea acestei funcții, consultați articolul IF Function.

Ascunderea valorilor nule într-un raport PivotTable

Faceți clic pe raportul PivotTable.

Pe fila Opțiuniîn grup Opțiuni pentru tabel pivot faceți clic pe săgeata de lângă comandă Opțiuniși selectați Opțiuni.

Accesați fila Aspect și format, apoi faceți următoarele:

Modificați modul în care sunt afișate erorile.În câmp Format bifați caseta Pentru afisarea erorilor. Introduceți în câmp valoarea pe care doriți să o afișați în loc de erori. Pentru a afișa erorile ca celule goale, eliminați tot textul din câmp.

Schimbați modul în care sunt afișate celulele goale. Bifați caseta Afișaj pentru celulele goale. Introduceți valoarea în câmpul pe care doriți să o afișați în celulele goale. Pentru a le păstra necompletate, eliminați tot textul din câmp. Pentru a afișa valori zero, debifați această casetă de selectare.

Excel este folosit pentru mai mult decât pentru a crea tabele. Scopul său principal este de a calcula numere folosind formule. Doar introduceți valori noi în celule și sistemul le va recalcula automat. Cu toate acestea, în unele cazuri, calculul nu are loc. Apoi, trebuie să aflați de ce Excel nu calculează suma.

Principalele cauze ale defecțiunilor

Este posibil ca Excel să nu calculeze sume sau formule din multe motive. Problema este adesea ca formula gresita, și în setările sistemului cărți. Prin urmare, este recomandat să folosiți câteva sfaturi pentru a afla care este potrivit pentru situația dumneavoastră particulară.

Schimbarea formatului celulei

Programul afișează calcule incorecte dacă formatele specificate nu se potrivesc cu valoarea, care se află în celulă. Apoi, calculul fie nu va fi aplicat deloc, fie va produce un număr complet diferit. De exemplu, dacă formatul este text, atunci calculul nu va fi efectuat. Pentru program, acesta este doar text, nu numere. De asemenea, poate apărea o situație când formatul nu corespunde cu realitatea. În acest caz, utilizatorul nu va putea introduce corect calculul, iar Excel nu va calcula suma sau nu va calcula rezultatul formulei.

Pentru a verifica dacă problema este într-adevăr în format, ar trebui să accesați „ Acasă" În primul rând, trebuie să selectați o celulă neverificată. Această filă conține informații de format.

Dacă trebuie să-l schimbați, faceți clic pe săgeată și selectați-l pe cel dorit din listă. După aceasta, sistemul va face un nou calcul.

Lista de formate din această secțiune este completă, dar fără descrieri și parametri. Prin urmare, în unele cazuri utilizatorul nu-l poate găsi pe cel de care are nevoie. Atunci este mai bine să folosiți o altă metodă. La fel ca în prima opțiune, ar trebui selectați celula. După aceea, faceți clic dreapta și deschideți comanda „”.

ÎN deschide fereastra situat lista completa formate cu descriere și setări. Doar selectați-l pe cel dorit și faceți clic pe „ Bine».

Dezactivați modul „Afișați formule”.

Uneori, utilizatorul poate observa că în loc de un număr, calculul în sine este afișat și formula din celulă nu este calculată. Apoi trebuie să-l dezactivați acest mod. După aceasta, sistemul va scoateți rezultatul final calcul, nu expresie.

Pentru a dezactiva " Afișați formule", ar trebui să mergeți la secțiunea corespunzătoare " Formule" Aici este fereastra" Dependente" Aici se află comanda necesară. Pentru a afișa o listă cu toate dependențele, faceți clic pe săgeată. Din listă trebuie să selectați „ Spectacol„și dezactivați acest mod dacă este activ.

Erori de sintaxă

Adesea, afișarea incorectă a rezultatelor este o consecință erori de sintaxă. Acest lucru se întâmplă dacă utilizatorul a intrat în calcul independent și nu a apelat la ajutorul expertului încorporat. Apoi, toate celulele cu erori nu vor produce un calcul.

În acest caz, ar trebui verificați ortografia corectă fiecare celulă care produce un rezultat incorect. Puteți suprascrie toate valorile utilizând expertul încorporat.

Activați recalcularea formulei

Toate calculele pot fi scrise corect, dar dacă valorile celulelor se schimbă, recalcularea nu are loc. Apoi poate fi dezactivat funcția de schimbare automată a calculului. Pentru a verifica acest lucru, accesați secțiunea „ Fişier", apoi" Opțiuni».

În fereastra deschisă, trebuie să mergeți la „ Formule" Iată parametrii de calcul. Doar bifați caseta „ În mod automat" și salvați modificările, astfel încât sistemul să înceapă să recalculeze.

Programul poate efectua un calcul complet, dar în loc de valoarea finală, este afișată o eroare și este posibil ca coloana sau celula să nu adună numerele. În funcție de mesajul afișat, puteți aprecia ce fel de defecțiune a apărut, de ex. împărțirea la zero sau format greșit.

Pentru a verifica sintaxa și a corecta eroarea, ar trebui să mergeți la „ Formule" Dependențele conțin comanda care este responsabilă pentru calcule.

Se va deschide o fereastră care afișează formula în sine. Aici, ar trebui să faceți clic pe „ Calcula" pentru a verifica eroarea.

Alte erori

De asemenea, utilizatorul poate întâlni și alte erori. În funcție de cauză, acestea pot fi corectate în consecință.

Formula nu se întinde

Întinderea este necesară atunci când mai multe celule trebuie efectuați aceleași calcule cu sensuri diferite. Dar uneori acest lucru nu se întâmplă automat. Apoi ar trebui să verificați dacă funcția este instalată umplere automată, care se află în parametri.

Suma celulelor este calculată incorect

De asemenea, suma este considerată incorectă dacă este în carte sunt celule ascunse. Utilizatorul nu le vede, dar sistemul efectuează calculul. Ca rezultat, programul afișează o singură valoare, dar suma reală ar trebui să fie diferită.

Aceeași problemă apare dacă este afișată valori cu numere după virgulă zecimală. În acest caz, acestea trebuie să fie rotunjite, astfel încât calculul să fie efectuat corect.

Formula nu este calculată automat

În fereastra deschisă, accesați configurarea recalculării automate și bifați caseta la comanda corespunzătoare. După aceasta, trebuie să salvați modificările.