A képlethibák feltárása

Az alábbi táblázat összefoglalja a képletek beírásakor leggyakrabban elkövetett hibákat és kijavításuk módját:

Ellenőrizendő szempont	További információ
Az egyenlőségjel (=) használata a függvények elején	Ha kihagyja az egyenlőségjelet, a beírt szöveg szövegként vagy dátumként is megjeleníthető. Ha például a SZUM(A1:A10) szöveget írja be, Excel megjeleníti a SZUM(A1:A10) szöveges sztringet, és nem hajtja végre a számítást. Ha a 11/2 értéket írja be, Excel a 11 és a 2 érték osztása helyett a 2-edik dátumot jeleníti meg (feltéve, hogy a cella formátuma Általános).
A nyitó és záró zárójelek párosítása	Győződjön meg arról, hogy minden zárójelnek megvan a (nyitó, illetve záró) párja. Ha függvényt használ egy képletben, fontos, hogy minden zárójel a megfelelő pozícióban legyen ahhoz, hogy a függvény megfelelően működjön. Például a =HA(B5<0);"Nem érvényes";B5*1,05) képlet nem fog működni, mert két záró és csak egy nyitó zárójelet tartalmaz (csak egynek kellene lennie mindkettőből). A képletnek a következőnek kell lennie: =HA(B5<0;"Nem érvényes";B5*1,05).
A tartomány jelölése kettősponttal	Amikor cellatartományra hivatkozik, akkor a tartomány első és utolsó cellájára való hivatkozás között használjon kettőspontot (:). Például: =SZUM(A1:A5), és nem =SZUM(A1 A5), amely #NULLA! hibát ad eredményül.
Az összes szükséges argumentum megadása	Egyes függvények kötelező argumentumokkal rendelkeznek. Ügyeljen arra, hogy túl sok argumentumot se írjon be.
A megfelelő típusú argumentumok megadása	Egyes függvények, például a SZUM, numerikus argumentumokat vesznek fel. Más függvények, például a CSERE esetén az argumentumok legalább egyikéhez szöveges értéket kell megadni. Ha nem a megfelelő típusú adatokat használja argumentumként, Excel váratlan eredményeket adhat vissza, vagy hibát jeleníthet meg.
A függvények egymásba ágyazásának legfeljebb 64 szintje	Egy függvényen belül nem lehet 64-nél több szinten megadni, illetve egymásba ágyazni a függvényeket.
Más lapnevek aposztrófok közé foglalása	Ha a képlet más munkalapon vagy munkafüzetben lévő cellákra vagy értékekre hivatkozik, és a másik munkafüzet vagy munkalap neve szóközt vagy más, nem betű jellegű karaktert tartalmaz, a nevet aposztrófok ( ' ) közé kell foglalni, például: ='Negyedéves adatok'!D3 vagy =‘123’!A1.
Felkiáltójel (!) elhelyezése a képletekben a hivatkozott munkalapok neve után	Ha például a D3 cella értékét szeretné visszaadni az ugyanabban a munkafüzetben található Negyedéves adatok munkalapról, használja a következő képletet: ='Negyedéves adatok'!D3.
A külső munkafüzetek elérési útjának feltüntetése	Győződjön meg arról, hogy minden külső hivatkozás tartalmazza a munkafüzet nevét és elérési útját. A munkafüzetre való hivatkozásban szerepelnie kell a munkafüzet nevének, amelynek szögletes zárójelben ([Munkafüzetneve.xlsx]) kell lennie. A hivatkozásnak a munkafüzet megfelelő munkalapjának nevét is tartalmaznia kell. Ha a hivatkozni kívánt munkafüzet nincs megnyitva Excel- ben, akkor is hivatkozhat rá egy képletben. Adja meg a fájl teljes elérési útját, például a következő példában: =ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'! A1:A8). Ez a képlet a tartomány azon sorainak számát adja vissza, amelyek a másik munkafüzet A1–A8 celláit tartalmazzák (8). Megjegyzés: Ha a teljes elérési út szóközöket tartalmaz, ahogyan az iménti példában is, akkor az elérési utat aposztrófok között kell megadni (az aposztrófokat az elérési út előtt, illetve a munkalap neve és a felkiáltójel között kell elhelyezni).
A számok formázás nélküli beírása a képletbe	Ne formázza a számokat, amikor képletbe írja őket. Ha például a beírandó érték 1000 Ft, írja be az 1000 értéket a képletbe. Ha egy szám részeként vesszőt ad meg, Excel elválasztó karakterként kezeli. Ha azt szeretné, hogy a számok több ezer vagy millió elválasztójelet vagy pénznemszimbólumot jelenítsenek meg, formázza a cellákat a számok beírása után. Ha például 3100-at szeretne hozzáadni az A3 cellában lévő értékhez, és az =SZUM(3 100;A3) képletet írja be, Excel hozzáadja a 3 és a 100 számot, majd hozzáadja ezt az összeget az A3 cellából származó értékhez ahelyett, hogy 3100-at adna hozzá az A3 cellához, amely =SZUM(3100;A3) lenne. Vagy ha az =ABS(-2,134) képletet adja meg, az Excel hibát jelenít meg, mert az =ABS(-2134) függvény csak egy argumentumot fogad el.

A képletek ellenőrzéséhez lehetőség van bizonyos szabályok bevezetésére. Ezek a szabályok nem garantálják, hogy a munkalap hibátlan legyen, de segítségükkel sok gyakori hiba felderíthető. A szabályok egyenként be- vagy kikapcsolhatók.

A hibákat kétféle módon lehet megjelölni és javítani: egyesével, mint például a helyesírás-ellenőrzők esetében, vagy előfordulásukkor azonnal, a munkalapon történő adatbevitellel egyidejűleg.

A hibákat az Excel által megjelenített beállításokkal oldhatja meg, vagy figyelmen kívül hagyhatja a hibát a Hiba mellőzése lehetőség kiválasztásával. Ha egy adott cellában észlelt hibát figyelmen kívül hagy, az a későbbi hibaellenőrzések alkalmával nem jelenik meg. Mindazonáltal a korábban figyelmen kívül hagyott hibák észlelése alaphelyzetbe állítható, így a hibák újra megjeleníthetők.

1. Windows Excel esetén válassza a Fájl > beállítások > Képletek lehetőséget, vagy
   Mac Excel esetén válassza az Excel menüt, > Beállítások > Hibaellenőrzés lehetőséget.

2. A Hibaellenőrzés csoportban törölje az Automatikus hibaellenőrzés a háttérben jelölőnégyzet jelölését. A talált hibák egy háromszöggel vannak megjelölve a cella bal felső sarkában.

   

3. Ha meg szeretné változtatni a hibák előfordulását jelző háromszög színét, válasszon a Hibák jelzése ezzel a színnel listában lévő lehetőségek közül.

4. A Hibaellenőrzési szabályok területen a következő szabályok jelölőnégyzeteit jelölheti be, illetve törölheti:

   • Hibát eredményező képleteket tartalmazó cellák: A képletek nem a várt szintaxist, argumentumokat vagy adattípusokat használják. A hibaértékek a következők: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, és #VALUE!. Ezeknek a hibaértékeknek különböző okai vannak, és különböző módokon oldhatók meg.

     Megjegyzés: Ha közvetlenül egy cellába ír be hibaértéket, a rendszer ezt a hibaértéket tárolja, de nem jelöli meg hibaként. Ha azonban egy másik cellában található képlet erre a cellára hivatkozik, akkor a képlet visszaadja az adott cellából a hibaértéket.

   • Inkonzisztens számított oszlop képlete a táblázatokban: A számított oszlopok tartalmazhatnak olyan egyéni képleteket, amelyek eltérnek a főoszlop képletétől, ami kivételt hoz létre. A számított oszlopnál akkor jön létre kivétel, ha a következő műveletek valamelyikét hajtja végre:

     • Számított oszlop cellájába képlettől eltérő adatot ír be.

     • Írjon be egy képletet egy számított oszlopcellába, majd használja a Ctrl +Z billentyűkombinációt , vagy válassza a Visszavonás lehetőséget a gyorselérési eszköztáron.

     • Új képletet ír be egy olyan számított oszlopba, amely már tartalmaz egy vagy több kivételt.

     • Olyan adatokat másol be a számított oszlopba, amelyek nem felelnek meg a számított oszlop képletének. Ha a másolt adat képletet tartalmaz, a képlet felülírja a számított oszlopban szereplő adatokat.

     • A munkalap olyan területének celláját helyezi át vagy törli, amelyre a számított oszlop egyik sora hivatkozik.

   • 2 számjegyű éveket tartalmazó cellák: A cella olyan szöveges dátumot tartalmaz, amely helytelen századként értelmezhető, amikor képletekben használják. Például az =ÉV("1/1/31") képletben szereplő dátum lehet 1931 vagy 2031. Ezzel a szabálysal kétértelmű szövegdátumokat kereshet.

   • Szövegként formázott vagy aposztróf előtti számok: A cella szövegként tárolt számokat tartalmaz. Ez általában a más forrásból importált adatokkal fordul elő. A szövegként tárolt számok nem várt rendezési eredményeket okozhatnak, ezért érdemes számmá alakítani őket. A '=SZUM(A1:A10) szövegnek tekinthető.

   • A régió más képleteivel inkonzisztens képletek: A képlet nem egyezik a hozzá közeli többi képlet mintájával. Sok esetben a más képletekkel szomszédos képletek csak a használt hivatkozásokban térnek el egymástól. A következő négy szomszédos képletet tartalmazó példában az Excel hibát jelenít meg a D4 cellában lévő =SZUM(A10:C10) képlet mellett, mert a szomszédos képletek egy sorral növekednek, és az egyik 8 sorral növekszik – az Excel a =SZUM(A4:C4) képletet várja.

     

     Ha a képletekben használt hivatkozások nem összhangban vannak a szomszédos képletekben szereplő hivatkozásokkal, az Excel hibát jelenít meg.

   • Egy régió celláit kihagyó képletek: A képletek nem feltétlenül tartalmaznak automatikusan olyan adatokra mutató hivatkozásokat, amelyeket az eredeti adattartomány és a képletet tartalmazó cella közé szúr be. Ez a szabály összehasonlítja a képletben található hivatkozást a képletet tartalmazó cellával szomszédos tartománnyal. Ha a szomszédos cellák további értékeket tartalmaznak (nem üresek), az Excel hibát jelez a képlet mellett.

     Az Excel például hibát szúr be a =SZUM(D2:D4) képlet mellé a szabály alkalmazásakor, mivel a D5, D6 és D7 cellák szomszédosak a képletben hivatkozott cellákkal és a képletet tartalmazó cellával (D8), és ezek a cellák olyan adatokat tartalmaznak, amelyekre a képletben hivatkozni kellett volna.

     

   • Képleteket tartalmazó feloldott cellák: A képlet nincs zárolva védelemre. Alapértelmezés szerint a munkalap összes celláját zárolta a rendszer, így nem módosíthatók, ha a munkalap védett. Ez segíthet elkerülni a véletlen hibákat, például a képletek véletlen törlését vagy módosítását. Ez a hiba azt jelzi, hogy a cella zárolásának feloldására van beállítva, de a lap nincs védve. Ellenőrizze, hogy nem szeretné-e zárolni a cellát.

   • Üres cellákra hivatkozó képletek: A képlet üres cellára mutató hivatkozást tartalmaz. Ez nem kívánt eredményhez vezethet, amint az a következő példában is látható.

     Tegyük fel, hogy az alábbi oszlopban található számok átlagát szeretné kiszámítani. Ha a harmadik cella üres, akkor nem szerepel a számításban, és az eredmény 22,75. Ha ebben cellában 0 van, az eredmény 18,2.

     

   • A táblába beírt adatok érvénytelenek: Érvényesítési hiba történt egy táblában. Ellenőrizze a cella érvényesítési beállítását az Adatok lap > Adateszközök csoport > Adatérvényesítés területen.

1. Jelölje ki az ellenőrizni kívánt munkalapot.

2. Ha a munkalapon nincs beállítva az automatikus újraszámolás, akkor nyomja le az F9 billentyűt.

   Ha a Hibaellenőrzés párbeszédpanel nem jelenik meg, válassza a Képletek > Képletvizsgálat > Hibaellenőrzés lehetőséget.

3. Ha korábban figyelmen kívül hagyott minden hibát, a következő lépésekkel ellenőrizheti ezeket a hibákat: nyissa meg a Fájl > beállítások > Képletek lehetőséget. Mac Excel esetén válassza az Excel menüt, > Beállítások > hibaellenőrzést.

   A Hibaellenőrzés szakaszban válassza a Mellőzött hibák visszaállítása > OK lehetőséget.

   

   Megjegyzés: A mellőzött hibák visszaállításával az összes hibát visszaállítja az aktív munkafüzet minden lapján.

   Tipp: Segíthet, ha a Hibaellenőrzés párbeszédpanelt közvetlenül a képletsáv alá helyezi.

   

4. Válassza ki a párbeszédpanel jobb oldalán található akciógombok egyikét. Az elérhető műveletek különböző típusú hibák esetén eltérnek.

5. Válassza a Tovább lehetőséget.

1. A cella mellett válassza a Hibaellenőrzés lehetőséget, majd válassza ki a kívánt beállítást. Az elérhető parancsok a hibatípusoktól és a hibát leíró első bejegyzéstől függően eltérők lehetnek.

   Ha a Hiba mellőzése lehetőséget választja, a rendszer minden egymást követő ellenőrzésnél figyelmen kívül hagyja a hibát.

   

Ha egy képlet nem tudja megfelelően kiértékelni az eredményt, az Excel hibaértéket jelenít meg, például #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, és #VALUE!. Minden hibatípusnak különböző okai és megoldásai vannak.

Az alábbi táblázat hivatkozásokat tartalmaz az e hibákkal részletesen foglalkozó cikkekre, és rövid leírással segíti az első lépéseket.

Témakör	Leírás
A #### hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha az adott oszlop nem elég széles a benne lévő összes karakter megjelenítéséhez, illetve negatív dátum vagy időérték van valamelyik cellában. Ez történik, ha egy képlet jövőbeli dátumból múltbéli dátumot von ki (pl. =2008.06.15.-2008.07.21.), mert az eredmény negatív dátumérték lesz. Tipp: Az oszlopfejlécek közé duplán kattintva próbálja meg automatikusan igazítani a cellát. Ha a ### azért jelenik meg, mert az Excel nem tudja megjeleníteni az összes karaktert, az kijavítja azt.
A #ZÉRÓOSZTÓ! hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha egy számot nullával (0) vagy értéket nem tartalmazó cellával próbál osztani. Tipp: Vegyen fel egy hibakezelőt, mint például a következő példában: =HA(C2;B2/C2;0)
A #HIÁNYZIK hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha egy érték nem áll rendelkezésre egy függvényhez vagy képlethez. Egy függvény, például az FKERES használata esetén van a keresésének megfelelő elem a keresési tartományban? Többnyire nincs. A #HIÁNYZIK hiba elkerüléséhez használja a HAHIBA függvényt. Ebben az esetben használhatja a következőt: =HAHIBA(FKERES(D2;$D$6:$E$8;2;IGAZ);0)
A #NÉV? hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha nem ismeri fel a képletben lévő szöveget. Előfordulhat például, hogy egy tartománynév vagy egy függvény neve helytelenül van beírva. Megjegyzés: Ha függvényt használ, ellenőrizze, hogy helyesen írta-e a nevét. Ebben az esetben a SZUM helyesírási hibát tartalmaz. Távolítsa el az "e" betűt, és az Excel kijavítja.
A #NULLA! hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha két olyan tartomány metszetét adta meg, amelyek nem metszik egymást. A metszetoperátor egy szóközkarakter, amely elválasztja a képletbe írt hivatkozásokat. Megjegyzés: Ellenőrizze, hogy a tartományok helyesen vannak-e elválasztva – a C2:C3 és az E4:E6 nem metszik egymást, így az =SZUM(C2:C3 E4:E6) a #NULLA! hibát adja vissza. Ha vesszőt helyez a C és az E tartomány közé, akkor a = SZUM(C2:C3;E4:E6)
A #SZÁM! hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha egy képlet vagy függvény érvénytelen számértékeket tartalmaz. Iteráló függvényt (például BMR vagy RÁTA) használ? Ha igen, a #SZÁM! hibát valószínűleg az okozza, hogy a függvény nem talál eredményt. A megoldáshoz szükséges lépéseket a súgótémakörben találja.
A #HIV! hiba javítása	Az Excel akkor jeleníti meg ezt a hibát, ha érvénytelen egy cellahivatkozás. Előfordulhat például, hogy törölte azokat a cellákat, amelyekre más képletek hivatkoztak, vagy olyan cellákat illesztett be, amelyekre más képletek hivatkoztak. Véletlenül törölt egy sort vagy oszlopot? A =SZUM(A2;B2;C2) képletben töröltük a B oszlopot, és lássuk, hogy mi történik. Használja a Visszavonás (Ctrl+Z) parancsot a törlés visszavonásához, készítse el újra a képletet, vagy használjon egy folyamatos hivatkozástartományt, például a következőt: =SZUM(A2:C2), amely automatikusan frissült a B oszlop törlésekor.
Az #ÉRTÉK! hiba javítása	Az Excel akkor jelenítheti meg ezt a hibát, ha a képlet különböző adattípusú cellákat tartalmaz. Matematikai operátorokat (+, -, *, /, ^) használ különböző adattípusokkal? Ha igen, próbáljon meg inkább egy függvényt használni. Ebben az esetben a =SZUM(F2:F5) megoldja a problémát.

Ha a cellák nem láthatók a munkalapon, a Figyelőablak eszköztáron watch ezeket a cellákat és képleteiket. A Figyelőablak segítségével a nagy méretű munkalapokon kényelmesen figyelheti, vizsgálhatja vagy megerősítheti egyes képletek számításait és eredményeit. A Figyelőablak használatakor nem kell újra és újra a munkalap különböző részeire görgetnie vagy ugrania.

Ez az eszköztár más eszköztárakhoz hasonlóan áthelyezhető vagy rögzíthető. Rögzítheti például az ablak alján. Az eszköztár a következő cellatulajdonságokat követi nyomon: 1) Munkafüzet, 2) Lap, 3) Név (ha a cella nevesített tartományt tartalmaz), 4) Cellacím, 5) Érték és 6) Képlet.

Cellák hozzáadása a Figyelőablakhoz

1. Jelölje ki a figyelni kívánt cellákat.

   Ha képleteket tartalmazó munkalap összes celláját ki szeretné jelölni, nyissa meg a Kezdőlap > Szerkesztés > válassza a Kijelölés keresése & lehetőséget (macen használhatja a Ctrl+G vagy a Control+G billentyűkombinációt )> Ugrás a speciális > képletekre lehetőséget.

   

2. Lépjen a Képletek > Képletvizsgálat > válassza a Figyelőablak lehetőséget.

3. Válassza a Figyelőpont hozzáadása lehetőséget.

   

4. Győződjön meg arról, hogy az összes watch kívánt cellát kijelölte, majd válassza a Hozzáadás lehetőséget.

   

5. A Figyelőablak oszlopszélességének módosításához húzza az oszlopfejléc jobb oldalán lévő szegélyt.

6. A Figyelőablak eszköztáron lévő bejegyzéshez tartozó cella megjelenítéséhez kattintson duplán a bejegyzésre.

   Megjegyzés: A másik munkafüzetre mutató külső hivatkozásokat tartalmazó cellák csak akkor jelennek meg a Figyelőablak eszköztáron, ha a másik munkafüzet is meg van nyitva.

Cellák eltávolítása a Figyelőablakból

1. Ha a Figyelőablak eszköztár nem jelenik meg, lépjen a Képletek > Képletvizsgálat > válassza a Figyelőablak lehetőséget.

2. Jelölje ki az eltávolítani kívánt cellákat.

   Több cella kijelöléséhez nyomja le a CTRL billentyűt, majd jelölje ki a cellákat.

3. Válassza a Figyelőpont törlése lehetőséget.

   

Sokszor nehéz átlátni, hogy egy beágyazott képlet hogyan számítja ki a végeredményt, hiszen az alkalmazás számos közbenső számítást és logikai tesztet végez e folyamat során. A Képletkiértékelő párbeszédpanel segítségével azonban megtekintheti a beágyazott képlet különböző részeit a számítások sorrendje szerint kiértékelve. Az =HA(ÁTLAG(D2:D5)>50;SZUM(E2:E5);0) képlet például könnyebben érthető, ha a következő köztes eredményeket látja:

A Képletkiértékelő párbeszédpanelen	Leírás
=HA(ÁTLAG(D2:D5)>50;SZUM(E2:E5);0)	Kezdetben a beágyazott képlet látható. Az ÁTLAG és a SZUM függvény a HA függvénybe van ágyazva. A D2:D5 cellatartomány az 55, a 35, a 45 és a 25 értéket tartalmazza, ezért az ÁTLAG(D2:D5) függvény eredménye 40 lesz.
=HA(40>50;SZUM(E2:E5);0)	A D2:D5 cellatartomány az 55, a 35, a 45 és a 25 értéket tartalmazza, ezért az ÁTLAG(D2:D5) függvény eredménye 40 lesz.
=HA(Hamis,SZUM(E2:E5);0)	A 40 nem nagyobb, mint 50, ezért a HA függvény első argumentumában (a logikai_vizsgálat argumentumban) található kifejezés hamis. A HA függvény a harmadik (az érték_ha_hamis) argumentum értékét adja eredményül. A SZUM függvényt nem kell kiértékelni, mivel ez a HA függvény második (érték_ha_igaz) argumentuma, és ezt az alkalmazás csak akkor értékeli, ha a kifejezés igaz.

1. Jelölje ki a kiértékelni kívánt cellát. Egyszerre csak egy cellát lehet kiértékelni.

2. Lépjen a Képletek > Képletvizsgálat > Képletkiértékelő elemre.

3. Válassza ki a Kiértékelés lehetőséget az aláhúzott hivatkozás megvizsgálásához. A kiértékelés eredménye dőlt formázással jelenik meg.

   Ha a képlet aláhúzott része egy másik képletre mutató hivatkozás, válassza a Be lépés lehetőséget a másik képletnek a Kiértékelés mezőben való megjelenítéséhez. A Visszaugrás lehetőséget kiválasztva lépjen vissza az előző cellába és képletre.

   A Belelépés gomb nem használható a képletben második alkalommal megjelenő hivatkozás esetében, illetve ha a képlet külön munkafüzetben lévő cellára hivatkozik.

4. Folytassa az Értékelés kiválasztását, amíg a képlet minden egyes része ki nem lett értékelve.

5. A kiértékelés újbóli megtekintéséhez válassza az Újraindítás lehetőséget.

6. A kiértékelés befejezéséhez válassza a Bezárás lehetőséget.