A Lifehacker olvasói már ismerik Denis Batyanov aki megosztotta velünk. Denis ma arról fog beszélni, hogyan lehet elkerülni a leggyakoribb Excel-problémákat, amelyeket gyakran magunknak okozunk.

Hadd tegyek egy fenntartást azonnal, hogy a cikkben szereplő anyag kezdőknek szól. Excel felhasználók. Haladó felhasználók Nem egyszer táncoltunk már energikusan ezen a gereblyén, így az én feladatom, hogy megvédjem ettől a fiatal és tapasztalatlan „táncosokat”.

Nem ad meg a táblázat oszlopfejléceit

Számos Excel-eszköz, például a rendezés, a szűrés, az intelligens táblák és a kimutatástáblák feltételezi, hogy az adatok oszlopfejléceket tartalmaznak. Ellenkező esetben vagy egyáltalán nem fogja tudni használni őket, vagy nem fognak megfelelően működni. Mindig győződjön meg arról, hogy a táblázatok tartalmaznak oszlopfejléceket.

Üres oszlopok és sorok a táblázatokban

Ez összezavarja az Excelt. Amikor egy üres sort vagy oszlopot talál a táblán belül, azt kezdi gondolni, hogy 2 táblája van, nem egy. Folyamatosan javítania kell. Ezenkívül ne rejtse el a táblázatban azokat a sorokat/oszlopokat, amelyekre nincs szüksége; jobb, ha törölje őket.

Egy lapon több táblázat található

Ha ezek nem apró táblázatok, amelyek értékek referenciakönyveit tartalmazzák, akkor ezt ne tegye.

Kényelmetlen lesz egy lapon több asztallal teljes mértékben dolgozni. Például, ha az egyik táblázat a bal oldalon, a másik pedig a jobb oldalon található, akkor az egyik tábla szűrése hatással lesz a másikra. Ha az asztalok egymás alatt helyezkednek el, akkor nem lehet használni a területek befagyasztását, és folyamatosan keresni kell az egyik asztalt, és szükségtelen manipulációkat kell végrehajtania, hogy ráhelyezze a kurzort. Szükséged van rá?

Az azonos típusú adatok mesterségesen különböző oszlopokban helyezkednek el

Az Excelt meglehetősen felületesen ismerő felhasználók gyakran ezt a táblázatformátumot részesítik előnyben:

Úgy tűnik, hogy van egy ártalmatlan formátumunk az ügynökértékesítésekről és a bírságokra vonatkozó információk felhalmozására. Ezt az asztalelrendezést az emberek vizuálisan jól érzékelik, mivel kompakt. Viszont hidd el, teljes rémálom az ilyen táblákból adatokat kinyerni és részösszegeket (összesített információkat) szerezni.

A tény az, hogy ezt a formátumot 2 dimenziót tartalmaz: -hoz, az ágon, a csoporton és az ügynökön keresztül kell döntenie a sorról. Ha megtalálta a kívánt oszlopot, akkor meg kell keresnie a kívánt oszlopot, mivel sok van belőlük. És ez a „kétdimenziósság” nagymértékben megnehezíti az ilyen asztallal való munkát szabványos szerszámok Excel - képletek és pivot táblák.

Ha összeállít egy pivot táblát, azt fogja tapasztalni, hogy nincs mód arra, hogy könnyen lehessen adatokat szerezni év vagy negyedév szerint, mivel a mutatók különböző mezőkre vannak szétválasztva. Nincs egy kényelmesen kezelhető értékesítési mennyiség mező, hanem 12 különálló mező. Manuálisan kell külön kiszámított mezőket létrehoznia negyedévekre és évekre, bár ha mindez egy oszlopban lenne, a kimutatás megtenné helyette.

Ha olyan szabványos összegzési képleteket szeretne használni, mint a SUMIF, SUMIFS, SUMPRODUCT, akkor azt is tapasztalhatja, hogy ezek nem működnek hatékonyan ezzel a táblázatelrendezéssel.

Információk terjesztése a könyv különböző lapjain „a kényelem kedvéért”

Egy másik gyakori hiba az, hogy van valamilyen szabványos táblázatformátum, és ezeken az adatokon kell elemzést végezni, külön lapokra osztva. Excel munkafüzetek. Például gyakran külön lapokat készítenek minden hónaphoz vagy évhez. Ennek eredményeként az adatelemzési munka mennyisége hatékonyan megsokszorozódik a létrehozott lapok számával. Nem kell ezt tenned. Gyűjtsön össze információkat EGY lapon.

Információk a megjegyzésekben

A felhasználók gyakran hozzáteszik fontos információ, amelyre szükségük lehet, a cella megjegyzésében. Ne feledd, hogy ami a kommentekben van, azt csak megnézheted (ha megtalálod). Nehéz bevinni a cellába. Azt javaslom, hogy hozzon létre egy külön oszlopot a megjegyzésekhez.

Formázási rendetlenség

Az biztos, hogy semmi jót nem ad hozzá az asztalhoz. Ez visszataszítónak tűnik a táblázatait használó emberek számára. Legjobb esetben nem tulajdonítanak ennek jelentőséget, rosszabb esetben azt gondolják, hogy szervezetlen és hanyag az ügyeid. Törekedj a következőkre:

Cellák egyesítése

Csak akkor használja a cellaegyesítést, ha egyáltalán nem nélkülözheti. Az egyesített cellák nagyon megnehezítik a hozzájuk tartozó tartományok manipulálását. Problémák vannak cellák mozgatásakor, cellák beillesztésekor stb.

Szöveg és számok kombinálása egy cellában

Fájdalmas benyomást kelt egy cella, amely egy számot tartalmaz a hátulján a „RUB” szövegállandóval. vagy kézzel beírt "USD". Főleg, ha nem nyomtatott formában, hanem egy rendes asztal. Az ilyen cellákkal végzett aritmetikai műveletek természetesen lehetetlenek.

Számok szövegként egy cellában

Kerülje a numerikus adatok cellában való tárolását szöveges formátumban. Idővel egy ilyen oszlop egyes cellái szöveges formátumúak lesznek, mások pedig normál formátumúak lesznek. Ez problémákat okoz a képletekkel.

Ha az asztalát LCD kivetítőn keresztül mutatják be

Válassza ki a szín és a háttér legkontrasztosabb kombinációit. Jól néz ki a kivetítőn sötét háttérés világos betűk. A legszörnyűbb benyomást a piros a feketére és fordítva. Ez a kombináció rendkívül alacsony kontrasztúnak tűnik kivetítőn – kerülje.

A munkalap oldalmódja az Excelben

Ez ugyanaz a mód, amelyben az Excel megmutatja, hogy a lap hogyan lesz felosztva oldalakra nyomtatáskor. Az oldalszegélyek kékkel vannak kiemelve. Nem javaslom, hogy folyamatosan ebben a módban dolgozzon, amit sokan csinálnak, mivel a nyomtató-illesztőprogram részt vesz az adatok képernyőn történő megjelenítésében, és ez sok okból függ (például a nyomtató hálózatba van kötve, Ebben a pillanatban nem elérhető) tele van lefagyással a képletek megjelenítési és újraszámítási folyamatában. Dolgozzon a szokásos módon.

Még több hasznos információ az Excelről itt tájékozódhat

Ha az Excel nem tudja megfelelően értékelni a munkalap képletét vagy funkcióját; megjeleníti a hibaértéket – például #NÉV?, #SZÁM!, #ÉRTÉK!, #N/A, #ÜRES!, #LINK! - abban a cellában, ahol a képlet található. Nézzük a típusokat hibák az Excelben, az övék lehetséges okok, és hogyan lehet megszüntetni őket.

#NAME hiba?

Hiba: #NAME akkor jelenik meg, ha egy képletben használt név eltávolításra került vagy korábban nem volt meghatározva.

Okoz hibák #NAME?:

1. Ha a képlet eltávolított vagy meghatározatlan nevet használ.

Excel hibák – Név használata egy képletben

Hibaelhárítás: Adjon meg egy nevet. Ennek módját ebben a cikkben ismertetjük.

1. Hiba a függvény nevének írása közben:

Hibák az Excelben – Hiba a MATCH függvény írása közben

Hibaelhárítás: Ellenőrizze a függvény helyesírását.

1. A cellatartományra való hivatkozásból hiányzik a kettőspont (:).

Hibák az Excelben – Hiba a cellatartomány írása közben

Hibaelhárítás: Javítsa ki a képletet. A fenti példában ez =SZUM(A1:A3).

1. A képlet olyan szöveget használ, amelyet nem tesz kettős idézőjelbe. Az Excel hibát jelez, mivel az ilyen szöveget névként kezeli.

Excel hibák – Hiba a szöveg és a szám kombinálásakor

Hibaelhárítás: A képlet szövegét idézőjelbe kell tenni.

Excel hibák – Szöveg helyes egyesítése

NUMBER számú hiba!

NUMBER számú hiba! Excelben akkor jelenik meg, ha a képlet helytelen számot tartalmaz. Például:

1. Használod a negatív szám, ha pozitív értékre van szükség.

Hibák az Excelben - Hiba a képletben, negatív argumentumérték az SQRT függvényben

Hibaelhárítás: Ellenőrizze, hogy a függvénybe bevitt argumentumok helyesek-e.

1. A képlet túl nagy vagy túl kicsi számot ad vissza ahhoz, hogy az Excelben megjeleníthető legyen.

Excel-hibák – Túl nagy érték miatti képlethiba

Hibaelhárítás: Állítsa be a képletet úgy, hogy az eredmény egy szám legyen az Excel elérhető tartományán belül.

Hiba: #VALUE!

Ez Excel hiba akkor fordul elő, ha egy érvénytelen értékű argumentumot adunk meg a képletben.

A #VALUE! hiba okai:

1. A képlet szóközöket, szimbólumokat vagy szöveget tartalmaz, de számot kell tartalmaznia. Például:

Excel hibák – numerikus és szöveges értékek összegzése

Hibaelhárítás: Ellenőrizze, hogy a képlet argumentumtípusai megfelelően vannak-e beállítva.

1. A függvény argumentumaként egy tartományt ad meg, és a függvény egyetlen érték megadását várja.

Hibák az Excelben – A VLOOKUP függvény tartományt használ argumentumként egyetlen érték helyett

Hibaelhárítás: Adjon meg érvényes argumentumokat a függvényhez.

1. Ha tömbképletet használ, nyomja meg az Enter billentyűt, és az Excel hibát jelenít meg, mert normál képletként kezeli.

Hibaelhárítás: A képlet beírásának befejezéséhez használja a Ctrl+Shift+Enter billentyűkombinációt.

Excel hibák – Tömbképlet használata

Hiba #LINK

Hibák az Excelben – Hiba a képletben a törölt A oszlop miatt

Hibaelhárítás: A képlet módosítása.

#DIV/0 hiba!

Ez hibaExcel akkor fordul elő, ha nullával osztunk, vagyis amikor egy nulla értéket tartalmazó cellahivatkozást vagy egy üres cellára való hivatkozást használunk osztóként.

Hibák az Excelben – #DIV/0 hiba!

Hibaelhárítás: Javítsa ki a képletet.

#N/A hiba

#N/A hiba az Excelben azt jelenti, hogy a képlet nem elérhető értéket használ.

A #N/A hiba okai:

1. A VLOOKUP, GLOOKUP, VIEW, MATCH függvények használatakor a rendszer helytelen keresési_érték argumentumot használ:

Hibák az Excelben – A keresett érték nem szerepel a megtekintett tömbben

Hibaelhárítás: Állítsa be a megfelelő argumentumot a keresett értékre.

1. Hibák a VLOOKUP vagy a GLOOKUP funkciók használata során.

Hibaelhárítás: lásd a dedikált részt

1. Hibák a tömbökkel való munka során: nem megfelelő tartományméretek használata. Például a tömb argumentumai kisebbek, mint a kapott tömb:

Excel-hibák – Tömbképlet-hibák

Hibaelhárítás: Állítsa be a képlethivatkozások tartományát, hogy illeszkedjen a sorokhoz és oszlopokhoz, vagy írjon be egy tömbképletet a hiányzó cellákba.

1. A függvényből hiányzik egy vagy több kötelező argumentum.

Hibák az Excelben – Hibák a képletben, hiányzik a szükséges argumentum

Hibaelhárítás: Adja meg az összes szükséges függvényargumentumot.

Hiba #ÜRES!

Hiba #ÜRES! Excelben akkor fordul elő, ha egy képlet nem átfedő tartományokat használ.

Hibák az Excelben – Nem átfedő tartományok használata a SUM képletben

Hibaelhárítás: Ellenőrizze a képlet helyesírását.

Hiba ####

A hiba okai

1. Az oszlop szélessége nem elegendő a cella tartalmának megjelenítéséhez.

Excel hibák – Oszlopszélesség növelése a cellában megjelenő értékhez

Hibaelhárítás: Növelje az oszlop/oszlopok szélességét.

1. A cella egy képletet tartalmaz, amely negatív értéket ad vissza a dátum vagy az idő kiszámításakor. Az Excelben a dátumnak és az időpontnak pozitív értékeknek kell lenniük.

Excel-hibák – A dátum és óra eltérései nem lehetnek negatívak

Hibaelhárítás: Ellenőrizze a képlet helyesírását, a napok vagy órák száma pozitív szám volt.

Hello barátok. Előfordult már veled, hogy egy képlet beírása után maga a képlet jelenik meg a cellában a számítási eredmény helyett? Ez egy kicsit elkeserítő, mert mindent jól csináltál, de valami nem világos. Hogyan kényszeríthetem a programot a képlet kiszámítására ebben az esetben?

Először meg kell értenie ennek a viselkedésnek az okait, majd javítania kell a helyzetet. Az okok pedig a következők lehetnek.

Képlet megjelenítése engedélyezve

Az Excel rendelkezik számítás-ellenőrző móddal. Ha be van kapcsolva, láthatja a képleteket a munkalapon. Beszéltem arról, hogyan és miért kell használni a képlet megjelenítését a . Ellenőrizze, hogy aktiválva van-e, majd tiltsa le. A szalagon van egy gomb Képletek – Képletfüggőségek – Képletek megjelenítése. Ha engedélyezve van, kattintson rá a letiltásához.

A képletek megjelenítése gyakran véletlenül kapcsol be a Ctrl+` billentyűkombináció megnyomásával.

A képletet a program szövegként érzékeli

Ha az Excel azt gondolja egy cellában szöveges karakterlánc, nem számítja ki az értéket, hanem egyszerűen megjeleníti a tartalmat. Ennek több oka is lehet:

• A cellaformátum szövegre van állítva. A program számára ez egy jelzés, hogy a tartalmat ugyanabban a formában kell kiadni, mint amilyenben tárolva van.
  
  
  Módosítsa a formátumot Numerikusra a leírtak szerint.
• A képlet elején nincs "=" jel. Ahogy mondtam, minden számítás az egyenlőségjellel kezdődik.
  
  
  Ha kihagyja, a tartalom szövegként fog érzékelni. Adjunk hozzá egyenlőségjelet, és számítsuk újra a képletet.
• Szóköz a "=" előtt. Ha véletlenül szóköz kerül az egyenlőségjel elé, az is szövegként értelmeződik.
  
  
  Ezt a hibát nehéz észrevenni, ezért manuálisan kell ellenőriznie. Távolítsa el a felesleges helyet, és számolja újra.
• A képlet idézőjelben van. Ez a hiba akkor fordul elő, ha szöveges adatokkal dolgozunk. Természetesen az idézőjelben lévő információkat szövegként kezeljük.
  
  
  Távolítsa el a felesleges idézőjeleket, és nyomja meg az Enter billentyűt az újraszámításhoz.

Felsoroltam az összes gyakori esetet, amikor az eredmény helyett a képlet jelenik meg. Úgy gondolom, hogy az Ön esete szerepelni fog a fenti listán; a probléma megoldása nem lesz nehéz. Ha nem sikerült, írd meg kommentben.

Nagyon érdekesnek ígérkezik. Megnézünk néhányat egyszerű technikák gyors formulák írása. Ha automatizálja őket, a munkája könnyebben és gyorsabban fog elvégezni. Iratkozz fel a frissítésekre, hogy ne maradj le!

Testreszabhatja a nulla értékek megjelenítését egy cellában, vagy használhat olyan formázási szabványokat a táblázatban, amelyek megkövetelik a nulla értékek elrejtését. A null értékeket különböző módokon jelenítheti meg és rejtheti el.

Az összes null érték elrejtése és megjelenítése egy munkalapon

Válassza ki Fájl > Lehetőségek > Továbbá.

Csoportban

• Nullák megjelenítése azokban a cellákban, amelyek nulla értéket tartalmaznak.

Null értékek elrejtése a kijelölt cellákban

Ezek a lépések elrejtik a nulla értékeket a kiválasztott cellákban számformátum használatával. A rejtett értékek csak a képletsorban jelennek meg, és nem nyomtatódnak ki. Ha az egyik cellában az érték nullától eltérő értékre változik, akkor az megjelenik a cellában, és az érték formátuma hasonló lesz az általános számformátumhoz.

Rejtett értékek megjelenítése.

Jelölje ki a rejtett nulla értékekkel rendelkező cellákat.

Megnyomhatja a gombokat CTRL+1 vagy a lapon itthon kattintson Formátum > Cellák formázása.

Az alapértelmezett számformátum alkalmazásához válassza a lehetőséget Szám > Általánosés nyomja meg a gombot rendben.

A képlet által visszaadott null értékek elrejtése

A lapon itthon Feltételes formázásés válassza a Cellakiemelési szabályok > Egyenlő lehetőséget.

A bal oldali mezőbe írja be 0 .

A jobb oldali mezőben válassza ki Egyedi formátum.

A terepen Cellaformátum nyissa meg a lapot Betűtípus.

A listán Szín válassza ki a fehér színt, és kattintson a gombra rendben.

A nullákat szóközként vagy kötőjelként jelenítse meg

A probléma megoldásához használja az IF függvényt.

Ha egy cella nulla értéket tartalmaz, használja a következő képletet az üres cella visszaadásához:

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

Így olvassa el a képletet. Ha a számítás eredménye (A2-A3) "0", semmi sem jelenik meg, beleértve a "0"-t is (ezt jelzi dupla idézőjelek""). Ellenkező esetben az A2-A3 számítás eredménye jelenik meg. Ha üresen kell hagynia a cellákat, de a "0"-tól eltérőt kell megjelenítenie, akkor tegyen egy kötőjelet "-" vagy más szimbólumot a dupla idézőjelek közé.

Válasszon ki egy jelentést Pivot tábla.

A lapon Elemzés csoportban Pivot tábla Lehetőségekés válassza ki Lehetőségek.

Menjen a lapra Elrendezés és formátum

• A hibakijelzés megváltoztatása A terepen Formátum jelölje be a négyzetet A hibakijelzéshez

  Módosítsa az üres cellák megjelenítését Jelölje be a négyzetet Üres cellák esetén megjelenítés

Nem mindig merül fel a nulla értékek (0) megjelenítésének szükségessége a lapokon. Függetlenül attól, hogy a formázási szabványok vagy a saját preferenciái megkövetelik a null értékek megjelenítését vagy elrejtését, ezeknek a követelményeknek a megvalósítása többféleképpen lehetséges.

Válassza ki Fájl > Lehetőségek > Továbbá.

Csoportban A következő lap opcióinak megjelenítése válasszon ki egy lapot, majd tegye a következők egyikét:

• Ha nulla (0) értéket szeretne megjeleníteni a cellákban, jelölje be a jelölőnégyzetet Nullák megjelenítése azokban a cellákban, amelyek nulla értéket tartalmaznak.

  Ha nulla értéket szeretne üres cellákként megjeleníteni, törölje a jelölőnégyzet bejelölését Nullák megjelenítése azokban a cellákban, amelyek nulla értéket tartalmaznak.

Megnyomhatja a gombokat CTRL+1 vagy a lapon itthon kattintson Formátum > Cellák formázása.

A terepen típus belép 0;-0;;@

Megjegyzések:

A rejtett értékek csak akkor jelennek meg a képletsorban vagy a cellában, ha módosítja a tartalmát. Ezeket az értékeket a rendszer nem nyomtatja ki.

A rejtett értékek ismételt megjelenítéséhez jelölje ki a cellákat, majd nyomja meg a gombot CTRL+1 vagy a lapon itthon csoportban Sejtek vigye az egérmutatót az elem fölé Formátumés válassza ki Cellaformátum. Az alapértelmezett számformátum alkalmazásához a listában Kategória válassza ki Tábornok. A dátum és az idő ismételt megjelenítéséhez válassza ki a megfelelő dátum- és időformátumot a menüből Szám.

Válassza ki a nulla (0) értéket tartalmazó cellát.

A lapon itthon csoportban Stílusok kattintson az elem melletti nyílra Feltételes formázás, vigye az egérmutatót az elem fölé Cellakiválasztási szabályokés válasszon egy lehetőséget Egyenlő.

A bal oldali mezőbe írja be 0 .

A jobb oldali mezőben válassza ki Egyedi formátum.

A párbeszédpanelen Cellaformátum nyissa meg a lapot Betűtípus.

A terepen Szín válasszon fehér színt.

A feladat végrehajtásához használja a funkciót HA.

Példa

IF függvény.

Null értékek elrejtése a kimutatásban

A lapon Lehetőségek csoportban PivotTable beállításai kattintson a parancs melletti nyílra Lehetőségekés válassza ki Lehetőségek.

Menjen a lapra Elrendezés és formátum, majd tegye a következőket:

A terepen Formátum jelölje be a négyzetet A hibakijelzéshez. Írja be a mezőbe azt az értéket, amelyet a hibák helyett meg szeretne jeleníteni. Ha a hibákat üres cellákként szeretné megjeleníteni, távolítsa el az összes szöveget a mezőből.

Jelölje be a négyzetet Üres cellák esetén megjelenítés. Írja be az értéket az üres cellákban megjeleníteni kívánt mezőbe. Ha üresen szeretné hagyni, távolítsa el az összes szöveget a mezőből. A nulla értékek megjelenítéséhez törölje a jelölőnégyzet jelölését.

Nem mindig merül fel a nulla értékek (0) megjelenítésének szükségessége a lapokon. Függetlenül attól, hogy a formázási szabványok vagy a saját preferenciái megkövetelik a null értékek megjelenítését vagy elrejtését, ezeknek a követelményeknek a megvalósítása többféleképpen lehetséges.

Az összes null érték megjelenítése vagy elrejtése egy munkalapon

Nulla értékek elrejtése a kiválasztott cellákban számformátum használatával

Ezek a lépések lehetővé teszik a nulla értékek elrejtését a kijelölt cellákban. Ha az egyik cellában az érték nullától eltérő lesz, a formátuma megegyezik az általános számformátummal.

Válassza ki azokat a cellákat, amelyek nulla (0) értéket tartalmaznak, és amelyeket el szeretne rejteni.

Megnyomhatja a gombokat CTRL+1 vagy a lapon itthon csoportban Sejtek kattintson Formátum > Cellák formázása.

A terepen típus belép 0;-0;;@

Megjegyzések:

A képlet által visszaadott null értékek elrejtése feltételes formázással

Válassza ki a nulla (0) értéket tartalmazó cellát.

A lapon itthon csoportban Stílusok kattintson a gomb melletti nyílra Feltételes formázásés válassza a Cellakiemelési szabályok > Egyenlő lehetőséget.

A bal oldali mezőbe írja be 0 .

A jobb oldali mezőben válassza ki Egyedi formátum.

A párbeszédpanelen Cellaformátum nyissa meg a lapot Betűtípus.

A terepen Szín válasszon fehér színt.

Képlet segítségével jelenítse meg a nullákat szóközként vagy kötőjelként

A feladat végrehajtásához használja az IF függvényt.

Példa

A példa könnyebb megértése érdekében másolja át ide üres lap.

Példa másolása

Emelje ki a cikkben található példát.

Fontos: Ne jelölje ki a sorok vagy oszlopok fejléceit.

Egy példa kiemelése a súgóban

Nyomja meg a CTRL+C billentyűkombinációt.

Az Excelben hozzon létre egy üres munkafüzetet vagy lapot.

Válassza ki az A1 cellát a lapon, és nyomja meg a CTRL+V billentyűkombinációt.

Fontos: A példa megfelelő működéséhez be kell illeszteni az A1 cellába.

Az eredmények megtekintése és az azokat visszaadó képletek közötti váltáshoz nyomja le a CTRL+` (ékezetes jel) vagy a fület. Képletek A Képletfüggőségek csoportban kattintson a gombra Képletek megjelenítése.

Ha a példát egy üres lapra másolja, testreszabhatja az igényeinek megfelelően.

A funkció használatával kapcsolatos további információkért tekintse meg az IF függvény című cikket.

Null értékek elrejtése a kimutatásban

Kattintson a PivotTable jelentésre.

A lapon Lehetőségek csoportban PivotTable beállításai kattintson a parancs melletti nyílra Lehetőségekés válassza ki Lehetőségek.

Menjen a lapra Elrendezés és formátum, majd tegye a következőket:

Módosítsa a hibák megjelenítési módját. A terepen Formátum jelölje be a négyzetet A hibakijelzéshez. Írja be a mezőbe azt az értéket, amelyet a hibák helyett meg szeretne jeleníteni. Ha a hibákat üres cellákként szeretné megjeleníteni, távolítsa el az összes szöveget a mezőből.

Módosítsa az üres cellák megjelenítési módját. Jelölje be a négyzetet Üres cellák esetén megjelenítés. Írja be az értéket az üres cellákban megjeleníteni kívánt mezőbe. Ha üresen szeretné hagyni, távolítsa el az összes szöveget a mezőből. A nulla értékek megjelenítéséhez törölje a jelölőnégyzet jelölését.

Grafikonok és diagramok (5)
Munka a VB projekttel (12)
Feltételes formázás (5)
Listák és tartományok (5)
Makrók (VBA eljárások) (64)
Vegyes (41)
Excel hibák és hibák (4)

Az Excel rosszul számol. Miért?

Az Excelben két cella különbségének kiszámításakor gyakran láthatja, hogy az nem egyenlő nullával, bár a számok ugyanazok. Például az A1 és B1 cellákban ugyanaz a szám 10,7, a C1-ben pedig kivonjuk a másikat az egyikből:

És a legfurcsább az, hogy a végén nem kapunk 0-t! Miért?

Az ok nyilvánvaló - cellaformátum
Először is, a legkézenfekvőbb válasz: ha két cella értékét hasonlítja össze, meg kell győződnie arról, hogy az ott lévő számok valóban egyenlőek, és nem kerekítik őket a cella formátuma. Például ha ugyanazokat a számokat vesszük a fenti példából, akkor ha kiválasztjuk őket - jobb egérgomb - Formázza a cellákat- fül Szám- Válassza a Numerikus formátumot, és állítsa a tizedesjegyek számát 7-re:

Most minden nyilvánvalóvá válik - a számok eltérőek, és egyszerűen a cellaformátum szerint kerekítették őket. És természetesen nem lehetnek egyenlőek. Ebben az esetben az lenne az optimális, ha megértené, miért olyanok a számok, amilyenek, és csak ezután hozzon döntést. És ha biztos benne, hogy a számokat tizedekre kell kerekíteni, akkor használhatja a ROUND függvényt a képletben:
=KEREK(B1 ;1)-KEREK(A1 ;1)=0
=KEREK(B1,1)-KEREK(A1,1)=0
Van egy radikálisabb módszer is:

• Excel 2007: Iroda gomb -Excel opciók -Ezenkívül (haladó) -
• Excel 2010: Fájl -Lehetőségek -Ezenkívül (haladó) -Állítsa be a megjelenített pontosságot
• Excel 2013 és újabb: Fájl -Lehetőségek -Ezenkívül (haladó) -Állítsa be a megjelenített pontosságot

Ez a munkafüzet összes lapjára pontosan úgy írja fel az összes számot, ahogyan a cellaformátum megjeleníti. Ezt az akciót Jobb, ha a könyv másolatán csinálod, mert a könyv összes lapján lévő összes számadatot a képernyőn megjelenő űrlapra hozza. Azok. ha maga a szám 5 tizedesjegyet tartalmaz, és a cellaformátum csak 1-re van állítva, akkor ennek az opciónak a használata után a szám 1 tizedesjegyre kerekedik. Ugyanakkor törölje ezt a műveletet Ez nem lehetséges, hacsak be nem zárja a könyvet mentés nélkül.

Az ok a szoftver
De gyakran az Excelben egy érdekesebb „jelenség” figyelhető meg: a képlettel kapott két törtszám közötti különbség nem egyenlő a közvetlenül a cellába írt számmal. Például írja be a következő képletet egy cellába:
=10,8-10,7=0,1
megjelenés alapján az eredménynek válasznak kell lennie IGAZ. De valójában az lesz HAMIS. És nem ez az egyetlen példa – ez az Excel viselkedése korántsem szokatlan a számításokban. Megtalálható kevésbé explicit formában is - amikor a számítások más cellák értékein alapulnak, amelyeket viszont szintén képletek számítanak ki, stb. De az ok minden esetben ugyanaz.

Miért nem egyenlőek a látszólag azonos számok?
Először nézzük meg, hogy az Excel miért tartja hamisnak a fenti kifejezést. Hiszen ha 10,8-ból kivonod a 10,7-et, akkor mindenesetre 0,1-et kapsz. Szóval valahol útközben valami elromlott. Írjuk külön cellába a kifejezés bal oldalát: =10,8-10,7. 0,1 jelenik meg a cellában. Most válassza ki ezt a cellát - jobb egérgomb - Formázza a cellákat- fül Szám- Válassza ki a Numerikus formátumot, és állítsa a tizedesjegyek számát 15-re:


és most láthatod, hogy valójában a cella nem pontosan 0,1-et, hanem 0,100000000000001-et tartalmaz. Azok. a 15. szignifikáns számjegyben van egy „farok” extra egység formájában.
Most nézzük meg, honnan jött ez a „farok”, mert logikailag és matematikailag sem szabadna ott lennie. Megpróbálom nagyon röviden és fölösleges elnagyolt nyelvezet nélkül elmondani - ha szeretnéd, az interneten sok ilyet találhatsz ebben a témában.
A helyzet az, hogy azokban a távoli időkben (ez körülbelül az 1970-es évek), amikor a PC még valami egzotikusnak számított, nem volt egységes szabvány a lebegőpontos számokkal (leegyszerűsítve fogalmazva törtekkel) való munkavégzésre. Miért ez a szabvány egyáltalán? Akkor mit számítógépes programok A számokat a maguk módján látják, de a töredékesek általában „minden bonyolult” státusszal rendelkeznek. Ugyanakkor ugyanaz a törtszám többféleképpen ábrázolható, és a vele végzett műveletek is feldolgozhatók. Ezért akkoriban ugyanaz a program, amikor számokkal dolgozott, különböző eredményeket produkált különböző számítógépeken. Vegye figyelembe az összes lehetségeset víz alatti sziklák Ez nem minden PC számára egyszerű feladat, így egy ponton elkezdődött a lebegőpontos számokkal való munka egységes szabványának kidolgozása. Kihagyva magáról a történetről a különféle részleteket, árnyalatokat és érdekességeket, csak annyit mondok, hogy végül mindennek az lett az eredménye, IEEE754 szabvány. A specifikációja szerint pedig bármely szám decimális ábrázolásánál a 15. jelentős számjegyben megengedettek a hibák. Ez elkerülhetetlen hibákhoz vezet a számításokban. Leggyakrabban ez a kivonási műveleteknél figyelhető meg, mert Az egymáshoz közeli számok kivonása a jelentős számjegyek elvesztéséhez vezet.
Magáról a specifikációról további információ található a Microsoft cikkében is: Az Excel lebegőpontos aritmetikai eredményei pontatlanok lehetnek
Pontosan ez az oka ennek a viselkedésnek az Excelben. Bár a tisztesség kedvéért meg kell jegyezni, hogy nem csak az Excel, hanem az összes program alapján ezt a szabványt. Ez persze felvet egy logikus kérdést: miért fogadtak el egy ilyen bugos szabványt? Azt mondanám, hogy kompromisszum volt a teljesítmény és a funkcionalitás között. Bár lehet, hogy más okai is voltak.

Egy másik dolog sokkal fontosabb: hogyan kezeljük ezt?
Valójában semmiképpen, mert... Ez egy szoftveres "hiba". És ebben az esetben nincs más kiút, mint mindenféle folt, mint a ROUND és hasonló funkciók használata. Ugyanakkor a ROUND-ot itt kell használni, nem úgy, ahogy a legelején bemutattuk, hanem egy kicsit másképp:
=KEREK(10,8 - 10,7 ;1)=0,1
=KEREK(10,8-10,7,1)=0,1
azok. A ROUND-ban magát a „hibás” kifejezést kell elhelyeznünk, és nem az egyes argumentumokat külön-külön. Ha minden argumentumot megadsz, annak nem lesz hatása, mert nem magában a számban van a probléma, hanem abban, hogy a program hogyan látja. És ebben az esetben a 10,8 és 10,7 már egy számjegyre kerekítve van, és nyilvánvaló, hogy az egyes számok külön-külön kerekítése egyáltalán nem lesz hatással. Azonban más módon is ki lehet szállni belőle. Szorozzon meg minden számot egy bizonyos értékkel (mondjuk 1000-rel, hogy eltávolítsa a 100%-os tizedesjegyeket), majd vonja ki és hasonlítsa össze:
=((10,8*1000)-(10,7*1000))/1000=0,1

Szeretném hinni, hogy legalább egyszer a Microsoft képes lesz legyőzni az IEEE754 szabvány leírt funkcióját, vagy legalább olyan javítást készíteni, amely az egyszerű számításokat nem rosszabb, mint egy 50 rubeles számológép :) Segített a cikk? Oszd meg a linket ismerőseiddel! Videó leckék

("Bottom bar":("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":"jobb","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 "texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"jobb","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectslidelay2":150 textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; szélesség:100%; magasság:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; pozíció:relatív; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; pozíció:relatív; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; szín:#fff; margin-top:8px;","buttoncss":"display:block; pozíció:relatív; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))