Cum să eliminați valorile nule în Excel. Cum să eliminați eroarea de împărțire la zero în Excel folosind o formulă

Cum să puneți un zero în Excel Când compilați rapoarte în Excel, este necesar să puneți zerouri în celulele goale, sau invers, trebuie să eliminați valorile zero. După cum se spune, „stăpânul este stăpânul” sau „orice capriciu pentru banii tăi”. Dar se dovedește că acest lucru provoacă multe dificultăți pentru mulți. Totul depinde de setările programului. Ea fie pune zerouri acolo unde este necesar și nu este necesar, fie nu le pune deloc. Prin urmare, astăzi vom lua în considerare aceste două întrebări.

Eliminați sau puneți zerouri în Microsoft Excel 2003

Pentru a face acest lucru, accesați meniul Serviciu,și selectați în partea de jos Opțiuni.

Se va deschide fereastra cu același nume. Opțiuni.

Pe fila Vedereîn panou Opțiuni ferestre, în coloana din mijloc, bifați caseta pentru parametru Valori nule. Acum vor fi afișate toate valorile dvs. zero din celule.

În același mod este posibil elimina zerouri din excel. Pentru a face acest lucru, debifați această opțiune și zerourile nu vor fi afișate.

Cum să puneți sau să eliminați zerouri în Microsoft Excel 2007

Faceți clic pe pictograma programului multicoloră din colțul din stânga sus.

În meniul care se deschide în partea de jos, faceți clic pe butonul Opțiuniexcela.

În fereastra deschisă Opțiuniexcela accesați fila În plus, iar în fereastra mare din dreapta, găsiți și bifați caseta de lângă intrarea -.

în Microsoft Excel 2010

Totul este la fel ca în Excel 2007, doar că mai întâi deschidem meniul Fişier, mergi la Opțiuni, și apoi - În plus – Afișați opțiunile pentru foaia următoare- eliminați sau bifați caseta - Afișați zerouri în celulele care conțin valori zero.

În matematică, împărțirea la zero este imposibilă! O modalitate de a explica această regulă este analizarea procesului, care arată ce se întâmplă atunci când un număr este împărțit la altul.

Împărțiți la zero eroare în Excel

În realitate, împărțirea este în esență aceeași cu scăderea. De exemplu, împărțirea a 10 la 2 înseamnă a scădea 2 din 10 de mai multe ori. Multiplicitatea se repetă până când rezultatul este egal cu 0. Astfel, este necesar să se scadă numărul 2 din zece exact de 5 ori:

1. 10-2=8
2. 8-2=6
3. 6-2=4
4. 4-2=2
5. 2-2=0

Dacă încercăm să împărțim numărul 10 la 0, nu vom obține niciodată rezultatul egal cu 0, deoarece la scăderea 10-0 va fi întotdeauna 10. Un număr infinit de scăderi de zero din zece nu ne va conduce la rezultatul = 0. Va exista întotdeauna același rezultat după operația de scădere =10:

• 10-0=10
• 10-0=10
• 10-0=10
• ∞ infinit.

În holul matematicienilor, ei spun că rezultatul împărțirii oricărui număr la zero este „nelimitat”. Orice program de calculator care încearcă să împartă la 0 pur și simplu returnează o eroare. În Excel, această eroare este afișată de valoarea din celula #DIV/0!.

Dar, dacă este necesar, puteți evita apariția unei erori de împărțire cu 0 în Excel. Trebuie doar să omiteți operația de împărțire dacă numitorul este 0. Soluția este implementată prin plasarea operanzilor în argumentele funcției =IF():

Astfel, formula Excel ne permite să „împărțim” numărul la 0 fără erori. Când împărțim orice număr la 0, formula va returna valoarea 0. Adică obținem următorul rezultat după împărțire: 10/0=0.

Cum funcționează formula pentru eliminarea erorii împărțire la zero?

Pentru a funcționa corect, funcția IF necesită completarea a 3 dintre argumentele sale:

1. Condiție booleană.
2. Acțiuni sau valori care vor fi efectuate dacă condiția booleană rezultată se evaluează la TRUE.
3. Acțiuni sau valori care trebuie executate atunci când condiția booleană este evaluată la FALS.

În acest caz, argumentul condiționat conține o verificare a valorii. Dacă valorile celulelor din coloana Vânzări sunt 0. Primul argument al funcției IF trebuie să aibă întotdeauna operatori de comparație între două valori pentru a obține rezultatul condiției ca TRUE sau FALSE. În cele mai multe cazuri, semnul egal este utilizat ca operator de comparație, dar pot fi utilizați alții, cum ar fi mai mare decât > sau mai mic decât >. Sau combinațiile lor - mai mari sau egale cu >=, nu egale cu!=.

Dacă condiția din primul argument returnează TRUE, atunci formula va umple celula cu valoarea de la al doilea argument la funcția IF. În acest exemplu, al doilea argument conține numărul 0 ca valoare. Aceasta înseamnă că celula din coloana „Performanță” va fi pur și simplu completată cu numărul 0 dacă există 0 vânzări în celula opusă coloanei „Vânzări”.

Dacă condiția din primul argument este evaluată la FALS, atunci valoarea de la al treilea argument la funcția IF este utilizată. În acest caz, această valoare se formează în urma acțiunii de împărțire a indicatorului din coloana „Vânzări” la indicatorul din coloana „Plan”.

Formula pentru împărțirea la zero sau zero la un număr

Să ne complicăm formula cu funcția =OR(). Să adăugăm un alt agent de vânzări cu vânzări zero. Acum formula ar trebui schimbată în:

Copiați această formulă în toate celulele din coloana Execuție:

Acum, indiferent de locul în care există zero în numitor sau în numărător, formula va funcționa conform nevoilor utilizatorului.

Când utilizați formule în Excel, dacă celulele la care face referire operator sunt goale, zona de calcul va fi implicit la zerouri. Estetic, acest lucru nu arată foarte frumos, mai ales dacă tabelul are o mulțime de intervale similare cu valori zero. Și este mai dificil pentru utilizator să navigheze prin date comparativ cu situația dacă astfel de zone ar fi complet goale. Să aflăm în ce moduri puteți elimina afișarea datelor zero în Excel.

Excel oferă posibilitatea de a elimina zerourile din celule în mai multe moduri. Acest lucru se poate face fie prin utilizarea funcțiilor speciale, fie prin aplicarea formatării. De asemenea, este posibil să dezactivați afișarea unor astfel de date în întreaga foaie.

Metoda 1: setări Excel

La nivel global, această problemă poate fi rezolvată prin modificarea setărilor Excel pentru foaia curentă. Acest lucru vă permite să goliți absolut toate celulele care conțin zerouri.

După aceste acțiuni, toate celulele foii curente care conțin valori zero vor fi afișate ca goale.

Metoda 2: Aplicați formatarea

Puteți ascunde valorile celulelor goale schimbând formatul acestora.

Acum toate zonele care conțin valori nule vor fi goale.

Metoda 3: Formatarea condiționată

Pentru a elimina zerourile suplimentare, puteți utiliza și un instrument atât de puternic precum formatarea condiționată.

Acum, cu condiția ca valoarea din celulă să fie zero, atunci va fi invizibil pentru utilizator, deoarece culoarea fontului se va îmbina cu culoarea de fundal.

Metoda 4: Aplicarea funcției IF

O altă opțiune pentru ascunderea zerourilor implică utilizarea operatorului DACĂ.

Apropo, dacă în fereastra de argumente din câmp „valoare dacă adevărat” setat la o liniuță, atunci când rezultatul este afișat în celule cu o valoare zero, nu va exista un spațiu, ci o liniuță.

Metoda 5: Utilizarea funcției ISNUMBER

Următoarea metodă este un fel de combinație de funcții DACĂși ISNUMBER.

Există mai multe moduri de a elimina numărul „0” dintr-o celulă dacă are valoarea zero. Cel mai simplu mod este de a dezactiva afișarea zerourilor în setările Excel. Dar apoi trebuie remarcat faptul că acestea vor dispărea pe toată foaie. Dacă trebuie să aplicați dezactivarea exclusiv într-o anumită zonă, atunci formatarea intervalului, formatarea condiționată și aplicarea funcțiilor vor veni în ajutor. Care dintre aceste metode să alegeți depinde deja de situația specifică, precum și de abilitățile și preferințele personale ale utilizatorului.

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

Ascunderea și afișarea tuturor valorilor nule într-o foaie de lucru

Selectați 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 nule în celulele selectate folosind un format numeric. Valorile ascunse sunt afișate numai în bara de formule și nu sunt tipărite. Dacă valoarea dintr-una dintre aceste celule se schimbă la zero, aceasta va fi afișată în celulă și formatul valorii va fi similar cu formatul general al numărului.

Afișați valori ascunse.

Selectați celule cu valori zero ascunse.

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

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

Ascunderea valorilor nule returnate de o formulă

Pe fila Acasă Formatarea condiționalăși selectați Reguli de selecție a celulelor > Egal.

In campul din stanga intra 0 .

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

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

Listate Culoare selectați culoarea albă și apăsați butonul O.K.

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

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

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

DACA(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ă nu lăsați celulele goale, dar să afișați altceva decât „0”, introduceți o cratimă „-” sau un alt caracter între ghilimele duble.

Selectați un raport PivotTable.

Pe fila Analizăîntr-un grup masă rotativă Opțiuniși selectați elementul Opțiuni.

Accesați fila Marcare și format

• Modificarea afișajului erorilorÎn câmp Format bifeaza casuta Pentru afisarea erorilor

  Schimbați afișarea celulelor goale Caseta de bifat Pentru celulele goale, afișați

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare necesită ca dvs. sau propriile preferințe 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.

Î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 selectare Afișați zerouri în celulele care conțin valori zero.

  Pentru a afișa valorile nule ca celule goale, debifați 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 de introduce 0;-0;;@

Note:

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

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

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

Pe fila Acasăîntr-un 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.

In campul din stanga intra 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 albul.

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

Exemplu

Funcția IF.

Ascunderea valorilor nule într-un raport PivotTable

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

Accesați fila Marcare ș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.

Caseta de bifat Pentru celulele goale, afișați. Introduceți în câmp valoarea pe care doriți să o afișați în celulele goale. Pentru a le păstra goale, eliminați tot textul din câmp. Pentru a afișa valori nule, debifați această casetă de validare.

Necesitatea de a afișa valori zero (0) pe foi nu apare întotdeauna. Indiferent dacă standardele de formatare necesită ca dvs. sau propriile preferințe 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 zero într-o foaie de lucru

Ascunderea valorilor nule în celulele selectate folosind formatul numeric

Aceste acțiuni vă permit să ascundeți valorile nule în celulele selectate. Dacă valoarea dintr-una dintre celule devine diferită de zero, formatul acesteia va fi similar cu formatul general al numărului.

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

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

În câmp Tip de 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ăîntr-un grup Stiluri faceți clic pe săgeata de lângă buton Formatarea condiționalăși selectați Reguli de selecție a celulelor > Egal.

In campul din stanga intra 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 albul.

Folosind o formulă pentru a afișa zerouri 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 foaie goală.

Copierea unui exemplu

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

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

Evidențiați un exemplu în ajutor

Apăsați CTRL+C.

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

Selectați celula A1 din foaia de lucru ș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+` (accent) sau pe filă Formuleîn grupul Dependențe de formule, faceți clic Afișați formule.

Copiind exemplul pe o foaie goală, îl puteți personaliza așa cum doriți.

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

Ascunderea valorilor nule într-un raport PivotTable

Faceți clic pe raportul PivotTable.

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

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

Modificarea modului î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.

Modificați modul în care sunt afișate celulele goale. Caseta de bifat Pentru celulele goale, afișați. Introduceți în câmp valoarea pe care doriți să o afișați în celulele goale. Pentru a le păstra goale, eliminați tot textul din câmp. Pentru a afișa valori nule, debifați această casetă de validare.

Valorile afișate în celulele foilor de calcul din Microsoft Office Excel sunt adesea generate de formulele scrise în ele. Rezultatul calculelor poate fi, de asemenea, o valoare zero, care nu este de dorit să fie afișată într-o celulă. Zerourile nu îmbunătățesc percepția generală a rezultatelor, mai ales dacă formulele din alte celule ale coloanei afișează valori de text mai degrabă decât valori numerice. Această problemă poate fi rezolvată în mai multe moduri.

Vei avea nevoie

• Editor de foi de calcul Microsoft Office Excel 2007 sau 2010.

Instruire