Hibás Excel-függvények elkerülése

A több argumentumot tartalmazó képletek listaelválasztókkal választják el egymástól az argumentumokat. A használt elválasztó az operációs rendszer területi beállításaitól és az Excel beállításaitól függően változhat. A leggyakoribb listaelválasztó a vessző (,) és a pontosvessző (;).

A képletek megszakadnak, ha valamelyik függvényük helytelen elválasztójeleket használ.

További információ: Képlethibák, ha a listaelválasztó nincs megfelelően beállítva

Az Excel többféle, kettős kereszttel (#) bevezetett hibával (pl. #ÉRTÉK!, #HIV!, #SZÁM!, #HIÁNYZIK, #ZÉRÓOSZTÓ!, #NÉV? és #NULLA!) jelzi, ha a képlet egyik része nem működik megfelelően. Például az #ÉRTÉK! hibaüzenetet az okozza, hogy helytelen a formátum, vagy az argumentumokban nem támogatott adattípus van. A #HIV! hibaüzenet pedig akkor jelenik meg, ha egy képlet olyan cellákra hivatkozik, amelyeket időközben töröltek vagy más adatokra cseréltek. A hibaelhárítás módja minden hiba esetén más lesz.

Kövesse a megjelenő kettős keresztes hibának megfelelő témakör lépéseit:

• A #SZÁM! hiba javítása

• Az #ÉRTÉK! hiba javítása

• A #HIÁNYZIK hiba javítása

• A #ZÉRÓOSZTÓ! hiba javítása

• A #HIV! hiba javítása

• A #NÉV? hiba javítása

• A #NULLA! hiba javítása

Minden alkalommal, amikor olyan számolótáblát nyit meg, amely más számolótáblákban szereplő értékekre hivatkozó képleteket tartalmaz, a program rákérdez arra, hogy frissíti-e a hivatkozásokat, vagy a jelenlegi formájukban őrzi meg őket.

Az Excel a fenti párbeszédpanel megjelenítésével gondoskodik arról, hogy az aktuális számolótáblában lévő képletek mindig a legfrissebb értékre mutassanak, amennyiben a hivatkozásérték megváltozott. Dönthet úgy, hogy frissíti a hivatkozásokat, vagy kihagyhatja ezt a lépést, ha nem szeretné őket frissíteni. Még ha úgy is dönt, hogy nem frissíti a hivatkozásokat, a számolótáblában található csatolásokat bármikor frissítheti manuálisan.

Bármikor letilthatja a párbeszédpanel megjelenítését a program indításakor. Ehhez válassza a Fájl > Beállítások > Speciális > Általános lehetőséget, és törölje a Rákérdezés az automatikus csatolás frissítésére jelölőnégyzet jelölését.

Ha a képlet nem jeleníti meg az értéket, kövesse az alábbi lépéseket:

• Győződjön meg arról, hogy az Excel úgy van beállítva, hogy megjelenítse a képleteket a számolótáblában. Ehhez válassza ki a Képletek lap Képletvizsgálat csoportjában a Képletek lehetőséget.

  Tipp: A Ctrl+` (a Tab billentyű fölötti billentyű) billentyűparancsot is használhatja. Ha ezt teszi, az oszlopok automatikusan szélesednek a képletek megjelenítéséhez, de ne aggódjon, amikor visszaáll a normál nézetre, az oszlopok átméreteződnek.

• Ha a fenti lépésekkel sem sikerül megoldani a problémát, akkor lehetséges, hogy a cella szövegként van formázva. Kattintson a jobb gombbal a cellára, válassza a Cellák formázása > Általános lehetőséget (vagy nyomja le a Ctrl+1 billentyűparancsot), majd a formátum módosításához nyomja le az F2 > Enter billentyűt.

• Ha egy oszlopban sok, szövegként formázott cellából álló tartománya van, jelölje ki a tartományt, alkalmazza az Ön által választott számformátumot, és válassza az Adatok > Szövegből oszlopok > Befejezés lehetőséget. Ezzel a formátumot az összes kijelölt cellára alkalmazza.

  

Ha nem történik meg egy képlet kiszámítása, ellenőrizze, hogy engedélyezve van-e az Excelben az automatikus számítás. Ha a manuális számítás van engedélyezve, akkor nem történik meg a képletek kiszámítása. Az alábbi lépéseket követve engedélyezheti az automatikus számítást.

1. Válassza ki a Fájl lap Beállítások parancsát, majd a Képletek kategóriát.

2. A Számolási beállítások > Munkafüzet kiszámítása csoportban ellenőrizze, hogy az Automatikus beállítás van-e kiválasztva.

   

A számításokról további információt a Képlet újraszámolásának, közelítésének és pontosságának módosítása című témakörben talál.

Körkörös hivatkozás akkor fordul elő, ha egy képlet arra a cellára hivatkozik, amelyben található. A hiba kijavításához helyezze át a képletet egy másik cellába, vagy módosítsa a képlet szintaxisát olyanra, amely elkerüli a körkörös hivatkozásokat. Egyes esetekben azonban szükség lehet a körkörös hivatkozásokra, mivel azok hatására a képletek iterációt végeznek – addig ismétlődnek, amíg meg nem felelnek egy adott numerikus feltételnek. Ilyen esetben engedélyezze a Körkörös hivatkozások eltávolítása vagy engedélyezése opciót.

A körkörös hivatkozásokról további információt a Körkörös hivatkozások eltávolítása vagy engedélyezése című témakörben talál.

Ha a bejegyzés nem egyenlőségjellel kezdődik, az nem képlet, és nem lesz kiszámítva – ez gyakori hiba.

Amikor például csak a SZUM(A1:A10) szöveget írja be egyenlőségjel nélkül, az Excel nem a képlet eredményét fogja megjeleníteni, hanem a SZUM(A1:A10) szöveges karakterláncot. A másik módszer, ha a 11/2 értéket írja be, a program a 02.nov vagy a 2009.11.02 dátumot jeleníti meg ahelyett, hogy elosztaná a tizenegyet kettővel.

Az ilyen váratlan eredmények elkerülése érdekében mindig egyenlőségjellel kell kezdeni. Például így: =SZUM(A1:A10) és =11/2.

Ha a képletben függvényt használ, annak megfelelő működéséhez fontos, hogy minden nyitó zárójelhez tartozzon záró zárójel. Győződjön meg arról, hogy minden zárójelnek megvan a párja. Az =HA(B5<0),"Érvénytelen";B5*1.05) képlet például nem fog működni, mert két záró zárójel van, de csak egy nyitó zárójel. A helyes képlet a következő: =HA(B5<0;"Nem érvényes";B5*1,05).

A legtöbb Excel-függvényhez argumentumokat is meg kell adni, mert nélkülük a függvény működésképtelen. Argumentumok nélkül csak néhány függvény (például a PI és a MA függvény) működik. A függvény beírásának megkezdésekor megjelenő képletszintaxis ellenőrzésével győződjön meg arról, hogy beírta az összes szükséges argumentumot.

A NAGYBETŰS függvény például csak egy szöveges karakterláncot vagy egy cellahivatkozást fogad el argumentumként az =NAGYBETŰS("szia") vagy az =NAGYBETŰS(C2) alakban.

Ezenkívül bizonyos függvényekhez (például a SZUM függvényhez) kizárólag numerikus argumentumok használhatók, míg másokhoz (például a CSERE függvényhez) legalább egy argumentum esetén szöveges értéket kell megadni. A helytelen adattípus használatakor a függvények esetleg nem a várt eredményt adják, vagy az #ÉRTÉK! hibaérték jelenik meg.

Ha gyorsan utána kell néznie egy adott függvény szintaxisának, a figyelmébe ajánljuk Az Excel függvényeinek kategória szerinti listája című témakört.

Dollárjellel ($) vagy ezreselválasztóval (.) formázott számokat ne írjon a képletekbe, mert az előbbiek abszolút hivatkozásokat, az utóbbiak pedig argumentumelválasztókat jelölnek. Emiatt az 1000 $ helyett az 1000 értéket írja be a képletbe.

Ha formázott számokat használ argumentumokban, váratlan számítási eredményeket kap, de a #NUM! hiba is megjelenhet. Amennyiben például az =ABS(-2.134) képlettel próbálja meg kiszámítani -2134 abszolút értékét, az Excel a #SZÁM! hibaüzenetet jeleníti meg, mert az ABS függvény egyetlen argumentumot fogad el, és a -2 és a 134 külön argumentumként jelenik meg.

Előfordulhat, hogy a képlet nem a várt eredményeket adja vissza, ha a cella adattípusa nem használható számításokban. Ha például egy egyszerű =2+3 képletet ír be egy szövegként formázott cellába, az Excel nem tudja kiszámítani a beírt adatokat. A cellában megjelenő érték =2+3 lesz. A probléma megoldásához módosítsa a cella adattípusát Szöveg értékről Általános értékre az alábbi módon:

1. Jelölje ki a cellát.

2. A Kezdőlap lehetőséget választva bontsa ki a Szám vagy Számformátum csoportot (vagy nyomja le a Ctrl + 1 billentyűkombinációt). Ezután válassza az Általános lehetőséget.

3. Nyomja le az F2 billentyűt, hogy a cella szerkesztőmódba váltson, és nyomja le az Enter billentyűt a képlet elfogadásához.

A Szám adattípust használó cellákban megadott dátumok dátum helyett numerikus dátumértékként jelenhetnek meg. Egy szám dátumként való megjelenítéséhez válassza a Dátum formátumot a Számformátum gyűjteményben.

Gyakori, hogy valaki az x karakterrel próbál szorzást jelölni egy képletben, ám az Excel csak a csillag (*) karaktert fogadja el a szorzás operátoraként. Ha állandókat használ a képletben, az Excel hibaüzenetet jelenít meg, és felajánlja a képlet kijavításának lehetőségét úgy, hogy az x karaktert csillag (*) karakterre cseréli.

Ha azonban cellahivatkozásokat használ, az Excel #NÉV? típusú hibát jelenít meg.

Ha szöveget tartalmazó képletet ír, a szöveget írógépstílusú idézőjelekkel (") kell határolni.

A ="Ma " & SZÖVEG(MA();"hhhh nn., nnnn") & " van" képlet például kombinálja a "Ma " szövegrészt a SZÖVEG és a MA függvény eredményével, és a következő eredményt adja vissza, például: Ma május 30., hétfő van.

A képletben a „Ma” szó után egy szóköz áll. Ez azért fontos, hogy a „Ma” szó és a „május 30., hétfő van” szövegrész közé a program beszúrja a szükséges szóközt. Ha nem zárja idézőjelek közé a szöveget, a képlet esetleg a #NÉV? hibát jeleníti meg. Ha nem zárja idézőjelek közé a szöveget, előfordulhat, hogy a képlet a #NÉV? hibát jeleníti meg.

Egy képleten belül legfeljebb 64 szintig ágyazhatók egymásba függvények.

A =HA(GYÖK(PI())<2;"Kettőnél kisebb!";"Kettőnél nagyobb!") képlet például három függvényt tartalmaz: a PI függvény a GYÖK függvénybe van ágyazva, ez pedig a HA függvénybe ágyazódik.

Ha a képlet egy másik munkalapon lévő cellákra vagy értékekre hivatkozik, és a másik munkalap neve nem betű jellegű karaktert (például szóközt) tartalmaz, a nevet aposztrófok (') közé kell foglalni.

Ha például a D3 cella értékét szeretné visszaadni az ugyanabban a munkafüzetben található Negyedéves adatok munkalapról, írja be a következőt: ='Negyedéves adatok'!D3. Ha nem zárja idézőjelek közé a munkalap nevét, a képlet a #NÉV? hibát jeleníti meg.

A másik munkalapon lévő értékeket vagy cellákat kiválasztva is hivatkozhat rájuk a képletben. Az Excel ekkor automatikusan aposztrófok közé foglalja a munkalapneveket.

A másik munkafüzetben lévő értékekre vagy cellákra mutató hivatkozásban szerepelnie kell a munkafüzet nevének, amelyet szögletes zárójelek ([]) közé kell foglalni. A hivatkozásnak az értékeket vagy cellákat tartalmazó munkalap nevét is tartalmaznia kell.

Ha például az Excelben megnyitott Q2 Operations munkafüzet Értékesítés lapjának A1–A8 celláira szeretne hivatkozni, írja be a következőt: =[2. negyedév Operations.xlsx]Értékesítés! A1:A8. A szögletes zárójelek nélkül a képlet a #HIV! hibát jeleníti meg.

Ha a munkafüzet nincs megnyitva az Excelben, írja be a fájl teljes elérési útját.

=SOROK('C:\Dokumentumok\[2. negyedév adatai.xlsx]Értékesítés'!A1:A8).

Ha egy cellát olyan cellával oszt el, amely nullát (0) tartalmaz vagy nem tartalmaz értéket, a #ZÉRÓOSZTÓ! hiba.

A hiba elkerülése érdekében közvetlenül megoldhatja a problémát a nevező meglétének ellenőrzésével. Másik lehetőségként használhatja ezt:

=HA(B1,A1/B1,0)

Ez a következőt jelenti: HA(B1 létezik, akkor az A1 cella osztása a B1 cellával, ellenkező esetben 0 érték visszaadása).

Az adatok törlése előtt mindig ellenőrizze, hogy van-e olyan képlete, amely a cellákban, tartományokban, definiált nevekben, munkalapokon vagy munkafüzetekben szereplő adatokra hivatkozik. Ezután a hivatkozott adatok eltávolítása előtt a képletet helyettesítheti a kiszámolt értékével.

Ha nem tudja lecserélni a képleteket az eredményeikre, tekintse át ezt az információt a hibákról és a lehetséges megoldásokról:

• Ha egy képlet törölt vagy más adatokkal helyettesített cellákra hivatkozik, és ha #HIV! hibát jelezadja vissza, jelölje ki a #HIV! hibát adó cellát. A szerkesztőlécen jelölje ki és törölje a #HIV! hibát. Ezután adja meg újból a képlet tartományát.

• Ha egy definiált név hiányzik, és egy arra hivatkozó képlet #NÉV? hibátad vissza, a javításhoz adjon meg a kívánt tartományra hivatkozó új nevet, vagy módosítsa úgy a képletet, hogy közvetlenül a cellatartományra hivatkozzon (például A2:D8).

• Ha egy munkalap hiányzik, és egy arra hivatkozó képlet #HIV! hiba esetén sajnos nincs mód a probléma megoldására – a törölt munkalapok nem állíthatók helyre.

• Ha egy munkafüzet hiányzik, az arra hivatkozó képlet sértetlen marad addig, amíg nem frissíti a képletet.

  Ha például a képlete a =[Munkafüzet1.xlsx]Munka1'!A1 és már nincs meg a Munkafüzet1.xlsx, a munkafüzetben hivatkozott értékek továbbra is elérhetők. Ha azonban az adott munkafüzetre hivatkozó képletet szerkeszt és ment, az Excel megjeleníti az Értékek frissítése párbeszédpanelt, és kéri, hogy adjon meg egy fájlnevet. Válassza a Mégse gombot, majd győződjön meg arról, hogy ezek az adatok nem vesznek el úgy, hogy a hiányzó munkafüzetre hivatkozó képleteket lecseréli a képlet eredményeire.

Előfordulhat olykor, hogy amikor a vágólapra másolja egy cella tartalmát, akkor utána csak az értéket szeretné beilleszteni, a szerkesztőlécen megjelenő mögöttes képletet azonban nem.

Lehet, például, hogy egy képlet eredményét egy másik munkalap cellájába szeretné másolni. Vagy törölni szeretné a képletben használt értékeket, miután az eredményül kapott értéket a munkalap egy másik cellájába másolja. Mindkét művelet esetében érvénytelen cellahivatkozásra utaló (#HIV!) hiba jelenik meg a célcellában, mert a képletben használt értékeket tartalmazó cellákra a továbbiakban már nem lehet hivatkozni.

Ezt a hibát úgy kerülheti el, hogy a képletek eredményét a képletek nélkül illeszti be a célcellákba.

1. A munkalapon jelölje ki azokat a cellákat, amelyek a másolni kívánt képleteredményt tartalmazzák.

2. A Kezdőlap lap Vágólap csoportjában válassza a Másolás lehetőséget.

   

   Billentyűparancs: Nyomja le a CTRL+C billentyűkombinációt.

3. Jelölje ki a beillesztési terület bal felső celláját.

   Tipp: Ha a kijelölt elemeket másik munkalapra vagy munkafüzetbe szeretné másolni vagy áthelyezni, válassza a megfelelő munkalap fülét, vagy váltson a megfelelő munkafüzetre, és jelölje ki a beillesztési terület bal felső celláját.

4. A Kezdőlap lap Vágólap csoportjában válassza a Beillesztés lehetőséget, majd válassza az Értékek beillesztése lehetőséget, vagy nyomja le az Alt > E > S > V > Enter for Windows, vagy az Option > Command > V > V > Enter billentyűt Mac gépen.

Ha meg szeretné érteni, hogy egy összetett vagy beágyazott képlet hogyan számítja ki a végeredményt, kiértékelheti az adott képletet.

1. Jelölje ki a kiértékelendő képletet.

2. Válassza a Képletek > Képletkiértékelő elemet.

   

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.

   

4. Ha a képlet aláhúzott része egy másik képletre mutató hivatkozás, a Belelépés lehetőséget kiválasztva jelenítse meg a másik képletet a Kiértékelés mezőben. A Visszaugrás lehetőséget kiválasztva lépjen vissza az előző cellába és képletre.

   A Lépés be lépése gomb nem érhető el, amikor a hivatkozás másodszor jelenik meg a képletben – vagy ha a képlet egy másik munkafüzet egyik cellájára hivatkozik.

5. Addig folytassa a műveletet, amíg a képlet minden részét ki nem értékeli.

   A Képletkiértékelő eszköz nem feltétlenül tájékoztatja arról, hogy miért hibás a képlet, de a helyét segíthet meghatározni. Az eszköz különösen jól jöhet olyan nagyobb méretű képletek esetén, amelyeknél más módon bonyolult lenne megtalálni a problémát.

   Megjegyzések: 

   • A HA és a CHOOSE függvény egyes részei nem lesznek kiértékelve, és a #N/A hiba jelenhet meg a Kiértékelés mezőben.

   • Az üres hivatkozások zéró (0) értékként jelennek meg a Kiértékelés mezőben.

   • Egyes függvények a munkalap minden módosításakor újraszámításra kerülnek. Ezek a függvények (többek között a VÉL, TERÜLET, INDEX, ELTOLÁS, CELLA, INDIREKT, SOROK, OSZLOPOK, MOST, MA és VÉLETLEN.KÖZÖTT) kiválthatják, hogy a Képletkiértékelő párbeszédpanel megjelenítse az eredményeket, amelyek különböznek a munkalap cellájában szereplő aktuális eredményektől.