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 le creăm adesea pentru noi înșine.

Permiteți-mi să fac imediat o rezervă că materialul din acest articol este destinat începătorilor. utilizatorii Excel. Utilizatori avansați Am dansat deja energic pe această greblă de mai multe ori, așa că sarcina mea este să protejez „dansatorii” tineri și fără experiență 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 tabelele 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 mici 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

De foarte multe ori, 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).

Adevărul 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 pentru unelte 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 standard de tabel și nevoie de analize bazate pe aceste date, distribuirea lor în foi separate Caiete de lucru Excel. De exemplu, ei creează adesea 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 Informații 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). Este 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 puteți face fără 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. De-a lungul timpului, unele dintre celulele dintr-o astfel de coloană vor avea un format text, iar unele vor avea un format obișnuit. Acest lucru va cauza probleme cu formulele.

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

Alegeți cele mai contrastante combinații de culoare și fundal. Arată bine pe proiector fundal întunecatși litere ușoare. 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 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

Dacă Excel nu poate evalua corect o formulă de foaie de lucru sau funcția; va afișa valoarea de eroare - de exemplu, #NUME?, #NUMĂR!, #VALOARE!, #N/A, #GOL!, #LINK! - în celula în care se află formula. Să ne uităm la tipuri erori în Excel, al lor motive posibileși cum să le eliminați.

Eroare #NAME?

Eroare #NAME apare atunci când un nume care este utilizat într-o formulă a fost eliminat sau nu a fost definit anterior.

Cauze erori #NAME?:

1. Dacă formula folosește un nume care a fost eliminat sau este nedefinit.

Erori Excel - Utilizarea unui nume într-o formulă

Depanare: Definiți un nume. Cum se face acest lucru este descris în acest articol.

1. Eroare la scrierea numelui funcției:

Erori în Excel - Eroare la scrierea funcției MATCH

Depanare: Verificați ortografia funcției.

1. În referința la o serie de celule lipsește două puncte (:).

Erori în Excel - Eroare la scrierea unui interval de celule

Depanare: Corectați formula. În exemplul de mai sus este =SUM(A1:A3).

1. Formula folosește text care nu este cuprins între ghilimele duble. Excel dă o eroare, deoarece tratează un astfel de text ca pe un nume.

Erori Excel - Eroare la combinarea textului cu un număr

Depanare: Închideți textul formulei între ghilimele duble.

Erori Excel - Îmbinarea corectă a textului

Eroare #NUMBER!

Eroare #NUMBER! în Excel este afișat dacă formula conține un număr incorect. De exemplu:

1. Folosesti un număr negativ, când este necesară o valoare pozitivă.

Erori în Excel - Eroare în formulă, valoare argument negativ în funcția SQRT

Depanare: Verificați dacă argumentele introduse în funcție sunt corecte.

1. Formula returnează un număr care este prea mare sau prea mic pentru a fi reprezentat în Excel.

Erori Excel - Eroare de formulă din cauza valorii prea mari

Depanare: Ajustați formula astfel încât rezultatul să fie un număr în intervalul accesibil Excel.

Eroare #VALUE!

Acest eroare Excel apare atunci când în formulă este introdus un argument cu o valoare nevalidă.

Cauzele erorii #VALUE!:

1. Formula conține spații, simboluri sau text, dar trebuie să conțină un număr. De exemplu:

Erori Excel - Însumarea valorilor numerice și text

Depanare: Verificați dacă tipurile de argument din formulă sunt setate corect.

1. Un interval este introdus ca argument al funcției, iar funcția așteaptă să fie introdusă o singură valoare.

Erori în Excel - Funcția VLOOKUP folosește un interval ca argument în loc de o singură valoare

Depanare: Furnizați argumente valide funcției.

1. Când utilizați o formulă matrice, apăsați Enter și Excel afișează o eroare, deoarece o tratează ca pe o formulă normală.

Depanare: Pentru a finaliza introducerea formulei, utilizați combinația de taste Ctrl+Shift+Enter.

Erori Excel - Utilizarea unei formule matrice

Eroare #LINK

Erori în Excel - Eroare în formulă din cauza coloanei A șterse

Depanare: Schimbați formula.

Eroare #DIV/0!

Acest eroareexcela apare la împărțirea la zero, adică atunci când o referință de celulă care conține o valoare zero sau o referință la o celulă goală este utilizată ca divizor.

Erori în Excel - Eroare #DIV/0!

Depanare: Corectați formula.

Eroare #N/A

#N/A eroare în Excelînseamnă că formula utilizează o valoare indisponibilă.

Motive pentru eroarea #N/A:

1. Atunci când utilizați funcțiile CĂUTARE V, CĂUTARE, AFIȘARE, POTRIVIRE, este utilizat argumentul incorect search_value:

Erori în Excel - Valoarea pe care o căutați nu se află în matricea care este vizualizată

Depanare: Setați argumentul corect la valoarea pe care o căutați.

1. Erori în utilizarea funcțiilor CUȚI V sau CUȚI.

Depanare: vezi sectiunea dedicata

1. Erori în lucrul cu matrice: folosirea dimensiunilor inadecvate ale intervalului. De exemplu, argumentele matricei sunt mai mici decât matricea rezultată:

Erori Excel - Erori cu formule de matrice

Depanare: Ajustați intervalul de referințe de formulă pentru a se potrivi cu rândurile și coloanele sau introduceți o formulă matrice în celulele lipsă.

1. Din funcție îi lipsesc unul sau mai multe argumente necesare.

Erori în Excel - Erori în formulă, lipsește argumentul necesar

Depanare: Introduceți toate argumentele funcției necesare.

Eroare #EMPTY!

Eroare #EMPTY! în Excel apare atunci când o formulă utilizează intervale care nu se suprapun.

Erori în Excel - Utilizarea intervalelor care nu se suprapun în formula SUM

Depanare: Verificați ortografia formulei.

Eroare ####

Motivele erorii

1. Lățimea coloanei nu este suficientă pentru a afișa conținutul celulei.

Erori Excel - Creșterea lățimii coloanei pentru a afișa valoarea în celulă

Depanare: Mărește lățimea coloanei/coloanelor.

1. Celula conține o formulă care returnează o valoare negativă atunci când se calculează o dată sau o oră. Data și ora în Excel trebuie să fie valori pozitive.

Erori Excel - Diferențele de dată și oră nu trebuie să fie negative

Depanare: Verificați ortografia formulei, numărul de zile sau de ore a fost un număr pozitiv.

Bună prieteni. Ți s-a întâmplat vreodată ca, după introducerea unei formule, formula în sine să fie afișată în celulă în locul rezultatului calculului? Acest lucru este puțin descurajator, pentru că ai făcut totul bine, dar ai ceva neclar. 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ă într-o celulă ș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 plasat 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 unele tehnici simple scrierea rapidă a formulelor. Dacă le aduceți la automatitate, munca dvs. se va face mai ușor și mai rapid. Abonează-te la actualizări pentru a nu rata!

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ți Fişier > Opțiuni > În plus.

In 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ă Formatarea condiționalăș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 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 masă rotativă.

Pe fila Analiză in grup Masă rotativă Opțiuniși selectați Opțiuni.

Accesați fila Aspect și format

• Schimbarea afișajului de eroareÎn câmp Format bifeaza casuta Pentru afisarea erorilor

  Schimbați afișarea celulelor goale Bifeaza casuta Pentru celulele goale, afișați

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

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

In 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 introduce 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ă in grup Celulele plasați cursorul peste element Formatși selectați Format de celule. Pentru a aplica formatul numeric implicit, în listă Categorie Selectați 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ă in grup Stiluri faceți clic pe săgeata de lângă element Formatarea condițională, 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 in 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 bifeaza casuta 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.

Bifeaza casuta Pentru celulele goale, afișați. 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.

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare sau propriile preferințe necesită să afișați sau să ascundeți valorile nule, există mai multe modalități 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ă in grup Celulele clic Formatare > Formatare celule.

În câmp Tip introduce 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ă in grup Stiluri faceți clic pe săgeata de lângă buton Formatarea condiționalăș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 în foaie albă.

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 in 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 bifeaza casuta 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. Bifeaza casuta Pentru celulele goale, afișați. 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.

Grafice și diagrame (5)
Lucrul cu proiectul VB (12)
Formatare condiționată (5)
Liste și intervale (5)
Macrocomenzi (proceduri VBA) (64)
Diverse (41)
Erori și erori Excel (4)

Excel calculează incorect. De ce?

Adesea, atunci când calculați diferența a două celule în Excel, puteți vedea că nu este egală cu zero, deși numerele sunt aceleași. De exemplu, în celulele A1 și B1 același număr este scris 10,7, iar în C1 îl scădem pe celălalt din una:

Și cel mai ciudat lucru este că până la urmă nu obținem 0! De ce?

Motivul este evident - formatul celulei
În primul rând, cel mai evident răspuns: dacă comparați valorile a două celule, trebuie să vă asigurați că numerele de acolo sunt într-adevăr egale și nu sunt rotunjite de formatul celulei. De exemplu, dacă luăm aceleași numere din exemplul de mai sus, atunci dacă le selectăm - butonul dreapta al mouse-ului - Celule de format-tab Număr-selectați formatul numeric și setați numărul de zecimale la 7:

Acum totul devine evident - numerele sunt diferite și au fost pur și simplu rotunjite de formatul celulei. Și, firește, nu pot fi egali. În acest caz, ar fi optim să înțelegem de ce numerele sunt așa cum sunt și abia apoi să luăm o decizie. Și dacă sunteți sigur că numerele ar trebui să fie rotunjite la zecimi, atunci puteți utiliza funcția ROUND în formula:
=ROUND(B1;1)-ROUND(A1;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
Există și o metodă mai radicală:

• Excel 2007: Buton Office -Opțiuni Excel -În plus (avansat) -
• Excel 2010: Fişier -Opțiuni -În plus (avansat) -Setați precizia așa cum este afișat
• Excel 2013 și versiuni ulterioare: Fişier -Opțiuni -În plus (avansat) -Setați precizia așa cum este afișat

Acest lucru va scrie toate numerele de pe toate foile registrului de lucru exact așa cum sunt afișate de formatul celulei. Această acțiune Este mai bine să o faci pe o copie a cărții, pentru că aduce toate datele numerice din toate foile cărții în formular pe măsură ce sunt afișate pe ecran. Acestea. dacă numărul în sine conține 5 zecimale, iar formatul celulei este setat la doar 1, atunci după utilizarea acestei opțiuni, numărul va fi rotunjit la 1 zecimală. În același timp, anulați această operațiune Acest lucru nu este posibil decât dacă închideți cartea fără a salva.

Motivul este software-ul
Dar adesea în Excel puteți observa un „fenomen” mai interesant: diferența dintre două numere fracționale obținute prin formulă nu este egală cu exact același număr scris direct în celulă. De exemplu, scrieți următoarea formulă într-o celulă:
=10,8-10,7=0,1
după aparență rezultatul ar trebui să fie un răspuns ADEVĂRAT. Dar de fapt va fi FALS. Și acest exemplu nu este singurul - acest comportament Excel este departe de a fi neobișnuit în calcule. Poate fi găsit și într-o formă mai puțin explicită - atunci când calculele se bazează pe valorile altor celule, care la rândul lor sunt calculate și prin formule etc. Dar motivul este același în toate cazurile.

De ce numerele aparent identice nu sunt egale?
Mai întâi, să vedem de ce Excel consideră că expresia de mai sus este falsă. La urma urmei, dacă scădeți 10,7 din 10,8, în orice caz obțineți 0,1. Deci, pe undeva, ceva a mers prost. Să scriem partea stângă a expresiei într-o celulă separată: =10,8-10,7. 0,1 apare în celulă. Acum selectați această celulă - butonul dreapta al mouse-ului - Celule de format-tab Număr-selectați formatul numeric și setați numărul de zecimale la 15:


și acum puteți vedea că de fapt celula conține nu exact 0,1, ci 0,100000000000001. Acestea. în a 15-a cifră semnificativă avem o „coadă” sub forma unei unități suplimentare.
Acum să ne dăm seama de unde provine această „coadă”, pentru că atât logic, cât și matematic, nu ar trebui să fie acolo. Voi încerca să vă spun foarte pe scurt și fără un limbaj abstrus inutil - dacă doriți, puteți găsi multe dintre ele pe acest subiect pe Internet.
Chestia este că în acele vremuri îndepărtate (aceasta este aproximativ anii 1970), când computerul era încă ceva exotic, nu exista un standard unic pentru lucrul cu numere în virgulă mobilă (fracționare, ca să spunem simplu). De ce acest standard? Atunci ce programe de calculator Ei văd numerele în felul lor, dar cele fracționate au, în general, statutul „totul este complicat”. Și, în același timp, același număr fracționar poate fi reprezentat în moduri diferite și pot fi, de asemenea, procesate operațiunile cu acesta. Prin urmare, în acele zile, același program, atunci când lucra cu numere, putea produce rezultate diferite pe computere diferite. Luați în considerare tot ce este posibil roci subacvatice Aceasta nu este o sarcină ușoară pentru fiecare PC, așa că la un moment dat a început dezvoltarea unui standard unificat pentru lucrul cu numere în virgulă mobilă. Omitând diverse detalii, nuanțe și lucruri interesante despre povestea în sine, voi spune doar că până la urmă totul a avut ca rezultat Standard IEEE754. Și în conformitate cu specificațiile sale, în reprezentarea zecimală a oricărui număr, erorile sunt permise în a 15-a cifră semnificativă. Ceea ce duce la erori inevitabile în calcule. Cel mai adesea acest lucru poate fi observat în operațiile de scădere, deoarece Scăderea numerelor care sunt apropiate unele de altele duce la pierderea cifrelor semnificative.
Mai multe informații despre specificația în sine pot fi găsite și în articolul Microsoft: Rezultatele aritmetice în virgulă mobilă în Excel pot fi inexacte
Acesta este tocmai motivul acestui comportament în Excel. Deși, în mod corect, trebuie remarcat faptul că nu numai Excel, ci toate programele bazate pe acest standard. Desigur, acest lucru ridică o întrebare logică: de ce au adoptat un astfel de standard de buggy? Aș spune că a existat un compromis între performanță și funcționalitate. Deși s-ar putea să fi fost și alte motive.

Un alt lucru este mult mai important: cum să tratăm cu asta?
De fapt, în niciun caz, pentru că... Acesta este un „bug” al software-ului. Și în acest caz, nu există altă cale de ieșire decât să folosești tot felul de patch-uri precum ROUND și funcții similare. În același timp, ROUND ar trebui folosit aici nu așa cum sa demonstrat la început, ci puțin diferit:
=ROUND(10,8 - 10,7 ;1)=0,1
=ROUND (10,8-10,7,1)=0,1
acestea. în ROUND trebuie să plasăm expresia „buggy” în sine, și nu fiecare dintre argumentele sale separat. Dacă puneți fiecare argument, nu va avea niciun efect, deoarece problema nu este în numărul în sine, ci în modul în care programul îl vede. Și în acest caz, 10,8 și 10,7 au fost deja rotunjite la o cifră și este clar că rotunjirea fiecărui număr separat nu va avea niciun efect. Poți, totuși, să ieși din ea într-un alt mod. Înmulțiți fiecare număr cu o anumită valoare (să zicem cu 1000, pentru a elimina 100% zecimale) și apoi scădeți și comparați:
=((10,8*1000)-(10,7*1000))/1000=0,1

Aș dori să cred că cel puțin într-o zi Microsoft va putea să învingă caracteristica descrisă a standardului IEEE754 sau cel puțin să facă un patch care va efectua calcule simple nu mai proaste decât un calculator de 50 de ruble :) A ajutat articolul? Distribuie link-ul prietenilor tăi! Lecții video

("Bara de jos":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"dreapta","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"dreapta","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2,":1500,":1500, textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; poziție:absolute; sus:0px; stânga:0px; lățime:100%; înălțime:100% ; culoare de fundal:#333333; opacitate:0,6; filtru:alpha(opacity=60);","titlecss":"display:block; poziție:relativă; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff;","descriptioncss":"display:block; poziție:relativă; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff; margin-top:8px;","buttoncss":"display:block; poziție:relativă; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))