Az Excel képletei nem működnek: lehetséges okok és hogyan javíthatók!

Ha képletekkel dolgozik az Excelben, előbb vagy utóbb azzal a problémával találkozik, amikor az Excel képletek egyáltalán nem működnek (vagy rossz eredményt adnak).

Bár nagyszerű lett volna, ha csak néhány lehetséges oka lenne a képletek hibás működésének. Sajnos túl sok dolog hibázhat (és gyakran előfordul).

De mivel egy olyan világban élünk, amely a Pareto -elvet követi, ha ellenőriz néhány gyakori problémát, akkor valószínűleg 80% -át (vagy akár 90% -át vagy 95% -át) fogja megoldani azok a problémák, amelyekben a képletek nem működnek az Excelben.

És ebben a cikkben kiemelem azokat a gyakori problémákat, amelyek valószínűleg az Ön okát jelentik Az Excel képletek nem működnek.

Kezdjük hát el!

A függvény szintaxisa helytelen

Hadd kezdjem azzal, hogy rámutatok a nyilvánvalóra.

Az Excel minden funkciójának saját szintaxisa van - például az elfogadható argumentumok száma vagy az elfogadható érvek típusa.

És sok esetben az oka annak, hogy az Excel képletei nem működnek, vagy rossz eredményt adnak, helytelen érv (vagy hiányzó érvek) lehet.

Például a VLOOKUP függvény három kötelező argumentumot és egy opcionális argumentumot tartalmaz.

Ha rossz érvet ad meg, vagy nem adja meg az opcionális argumentumot (ahol ez szükséges a képlet működéséhez), az rossz eredményt ad.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, ahol ismernie kell a Pontszám pontszámot a 2. vizsgán (az F2 cellában).

Ha az alábbi képletet használom, akkor rossz eredményt kapok, mert rossz értéket használok a harmadik argumentumban (az oszlopindex számát kéri).

= VLOOKUP (A2, A2: C6,2, FALSE)

Ebben az esetben a képlet kiszámítja (mivel értéket ad vissza), de az eredmény helytelen (a 2. vizsga pontszáma helyett az 1. vizsga pontszámát adja meg).

Egy másik példa, ahol óvatosnak kell lennie az érvekkel kapcsolatban, ha a VLOOKUP -ot a hozzávetőleges egyezéssel használja.

Mivel opcionális argumentumot kell használnia annak megadásához, hogy hol szeretné, hogy a VLOOKUP pontos egyezést vagy hozzávetőleges egyezést végezzen, ennek elmulasztása (vagy rossz argumentum használata) problémákat okozhat.

Az alábbiakban egy példa látható, ahol egyes diákok jegyei megvannak, és a jobb oldali táblázatban szereplő diákok jegyeit szeretném kivonni az 1. vizsgán.

Amikor az alábbi VLOOKUP képletet használom, hibát ad néhány névnél.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

Ez történik, mivel nem adtam meg az utolsó argumentumot (amelyet annak meghatározására használnak, hogy pontos egyezést vagy hozzávetőleges egyezést kell -e végezni). Ha nem adja meg az utolsó argumentumot, akkor alapértelmezés szerint automatikusan hozzávetőleges egyezést végez.

Mivel ebben az esetben pontos egyezést kellett végeznünk, a képlet hibát ad vissza néhány név esetén.

Míg a VLOOKUP függvény példáját vettem fel, ebben az esetben ez olyasvalami, amely alkalmazható számos Excel képletre, amelyek opcionális argumentumokkal is rendelkeznek.

Olvassa el még: Hibák azonosítása az Excel képlet hibakeresésével

Váratlan eredményeket okozó extra terek

A vezető, záró szóközöket nehéz kitalálni, és problémákat okozhatnak, ha olyan cellát használ, amelynek ezek a képletei.

Például az alábbi példában, ha a VLOOKUP segítségével próbálom lekérni Mark pontszámát, az a #N/A hibát adja meg (a nem elérhető hiba).

Noha látom, hogy a képlet helyes, és a „Mark” név egyértelműen ott van a listán, nem látom, hogy a névben lévő cellában egy záró szóköz található (a D2 cellában).

Az Excel nem tekinti azonosnak a két cella tartalmát, ezért nem megfelelőnek tartja, amikor az értéket a VLOOKUP (vagy bármely más keresési képlet) használatával lekérheti.

A probléma megoldásához el kell távolítania ezeket az extra karaktereket.

Ezt az alábbi módszerek bármelyikével teheti meg:

1. Mielőtt képletekben használná, tisztítsa meg a cellát, és távolítsa el az első/utolsó szóközöket
2. Használja a TRIM funkciót a képletben, hogy megbizonyosodjon arról, hogy a kezdő/záró/kettős szóközöket figyelmen kívül hagyja.

A fenti példában az alábbi képletet használhatja annak biztosítására, hogy működik.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Bár a VLOOKUP példát vettem fel, ez szintén gyakori probléma a TEXT funkciókkal való munkavégzés során.

Például, ha a LEN funkciót használom a cella összes karakterének számlálására, ha vannak kezdő vagy záró szóközök, akkor ezeket is számolja a rendszer, és rossz eredményt ad.

Elvitel / Hogyan javítható: Ha lehetséges, tisztítsa meg adatait a kezdő/záró vagy kettős szóköz eltávolításával, mielőtt ezeket képletekben használná. Ha nem tudja megváltoztatni az eredeti adatokat, használja a TRIM függvényt a képletekben.

Kézi számítás használata automatikus helyett

Ez az egy beállítás megőrülhet (ha nem tudja, hogy ez okozza az összes problémát).

Az Excelnek két számítási módja van - Automatikus és Kézikönyv.

Alapértelmezés szerint az automatikus mód engedélyezve van, ami azt jelenti, hogy ha képletet használok, vagy bármilyen módosítást végzek a meglévő képletekben, akkor automatikusan (és azonnal) elvégzi a számítást, és megadja az eredményt.

Ezt a beállítást mindannyian megszoktuk.

Az automatikus beállításban, amikor bármilyen változtatást hajt végre a munkalapon (például új képletet ír be akár a cellák bizonyos szövegeibe), az Excel automatikusan újraszámítja az összeset (igen, minden).

De bizonyos esetekben az emberek engedélyezik a kézi számítási beállítást.

Ez leginkább akkor történik, ha nehéz Excel fájlja van, sok adatgal és képlettel. Ilyen esetekben előfordulhat, hogy nem szeretné, hogy az Excel mindent újraszámítson, amikor apró módosításokat hajt végre (mivel néhány másodperc vagy akár perc is eltelhet), amíg ez az újraszámítás befejeződik.

Ha engedélyezi a manuális számítást, az Excel csak akkor számítja ki, ha kényszeríti.

Ez pedig azt gondolhatja, hogy a képlet nem számít.

Ebben az esetben mindössze annyit kell tennie, hogy visszaállítja a számítást automatikusra, vagy kényszeríti az újraszámítást az F9 billentyű megnyomásával.

Az alábbiakban bemutatjuk a számítás manuálisról automatikusra váltásának lépéseit:

1. Kattintson a Képlet fülre
2. Kattintson a Számítási beállítások lehetőségre
3. Válassza az Automatikus lehetőséget

Fontos: Abban az esetben, ha a számítást manuálisról automatikusra változtatja, érdemes biztonsági másolatot készíteni a munkafüzetről (arra az esetre, ha ettől lefagyna a munkafüzet vagy összeomlana az Excel)

Elvitel / Hogyan javítható: Ha azt veszi észre, hogy a képletek nem adják meg a várt eredményt, próbáljon meg valami egyszerűt bármely cellában (például 1 -et adjon hozzá egy meglévő képlethez. Miután azonosította a problémát, ahol a számítási módot módosítani kell, végezzen erőszámítást F9 használatával.

Sorok/oszlopok/cellák törlése, amelyek a #REF címhez vezetnek! Hiba

Az egyik dolog, amely pusztító hatással lehet az Excel meglévő képleteire, ha törli a számításokban használt sorokat vagy oszlopokat.

Amikor ez megtörténik, néha az Excel maga állítja be a hivatkozást, és gondoskodik arról, hogy a képletek megfelelően működjenek.

És néha… nem lehet.

Szerencsére az egyik egyértelmű jelzés, amelyet akkor kap, amikor a képletek törnek a cellák/sorok/oszlopok törlésénél, a #REF! hiba a cellákban. Ez egy hivatkozási hiba, amely azt jelzi, hogy a képletben szereplő hivatkozásokkal valami probléma van.

Hadd mutassam meg, mire gondolok egy példa segítségével.

Az alábbiakban a SUM képlet segítségével adtam hozzá az A2: A6 cellákat.

Most, ha törlöm ezen cellák/sorok bármelyikét, a SUM képlet #REF értéket ad vissza! hiba. Ez azért történik, mert amikor töröltem a sort, a képlet most nem tudja, mire hivatkozzon.

Láthatja, hogy a képlet harmadik argumentuma #REF lett! (amely korábban a törölt cellára utalt).

Elvitel / Hogyan javítható: A képletekben használt adatok törlése előtt győződjön meg arról, hogy a törlés után nincsenek hibák. Azt is javasoljuk, hogy rendszeresen készítsen biztonsági másolatot a munkájáról, hogy mindig legyen valami, amire vissza tud esni.

A zárójelek helytelen elhelyezése (BODMAS)

Ahogy a képletek egyre nagyobbak és összetettebbek, érdemes zárójeleket használni, hogy teljesen tisztázzuk, melyik rész tartozik egymáshoz.

Bizonyos esetekben előfordulhat, hogy a zárójel rossz helyen van, ami vagy rossz eredményt, vagy hibát eredményezhet.

És bizonyos esetekben ajánlott zárójeleket használni annak biztosítására, hogy a képlet megértse, mit kell először csoportosítani és kiszámítani.

Tegyük fel például, hogy a következő képlettel rendelkezik:

=5+10*50

A fenti képletben az eredmény 505, mivel az Excel először elvégzi a szorzást, majd az összeadást (mivel az operátorok tekintetében rangsor van).

Ha azt szeretné, hogy először az összeadást, majd a szorzást végezze el, akkor az alábbi képletet kell használnia:

=(5+10)*50

Bizonyos esetekben előfordulhat, hogy az elsőbbségi sorrend működik az Ön számára, de továbbra is ajánlott a zárójelek használata a zavartság elkerülése érdekében.

Továbbá, ha érdekli, az alábbiakban a képletekben gyakran használt különböző operátorok rangsorát mutatjuk be:

Operátor	Leírás	Elsőbbségi sorrend
: (kettőspont)	Hatótávolság	1
(egyetlen szóköz)	Útkereszteződés	2
, (vessző)	unió	3
-	Tagadás (mint -1)	4
%	Százalék	5
^	Hatványozás	6
* és /	Szorzás és osztás	7
+ és -	Összeadás és kivonás	8
&	Összefűzés	9
= =	Összehasonlítás	10

Elvitel / Hogyan javítható: Mindig használjon zárójelet a zavarok elkerülése érdekében, még akkor is, ha ismeri az elsőbbségi sorrendet, és helyesen használja. A zárójelek használata megkönnyíti a képletek ellenőrzését.

Az abszolút/relatív cellahivatkozások helytelen használata

Amikor képleteket másol és illeszt be az Excelben, az automatikusan beállítja a hivatkozásokat. Néha pontosan ezt akarja (főleg, amikor képleteket másol be-illeszt be az oszlopba), és néha nem szeretné, ha ez megtörténne.

Abszolút hivatkozás az, amikor javít egy cellahivatkozást (vagy tartományhivatkozást), hogy ne változzon a képletek másolásakor és beillesztésekor, a relatív hivatkozás pedig az, amely megváltozik.

Az abszolút, relatív és vegyes referenciákról itt olvashat bővebben.

Hibás eredményt kaphat, ha elfelejti abszolútra módosítani a hivatkozást (vagy fordítva). Ez gyakran előfordul velem, amikor keresési képleteket használok.

Hadd mutassak egy példát.

Az alábbiakban van egy adatkészletem, amelyből le akarom szerezni az 1. vizsga pontszámát az E oszlopban található nevekhez (egyszerű VLOOKUP használati eset)

Az alábbiakban a képletet használom az F2 cellában, majd másolom az alatta lévő összes cellába:

= VLOOKUP (E2, A2: B6,2,0)

Mint látható, ez a képlet bizonyos esetekben hibát ad.

Ez azért történik, mert nem zártam le a tábla tömb argumentumot - ez az A2: B6 az F2 cellában, miközben kellett volna $ A $ 2: $ B $ 6

Ha ezek a dollárjelek a cellahivatkozás sorszáma és oszlop ábécéje előtt vannak, arra kényszerítem az Excel -t, hogy rögzítse ezeket a cellahivatkozásokat. Tehát, még akkor is, ha ezt a képletet lemásolom, a táblázat tömb továbbra is az A2: B6 -ra fog utalni

Profi tipp: A relatív hivatkozás abszolútra való konvertálásához válassza ki a cellahivatkozást a cellában, és nyomja meg az F4 billentyűt. Észreveheti, hogy a dollárjelek hozzáadásával megváltozik. Folytathatja az F4 megnyomását, amíg meg nem kapja a kívánt hivatkozást.

Helytelen hivatkozás a munkalapok / munkafüzetek nevére

Amikor egy képletben más lapokra vagy munkafüzetekre hivatkozik, akkor egy meghatározott formátumot kell követnie. És ha a formátum nem megfelelő, akkor hibaüzenetet kap.

Például, ha a Sheet2 A1 -es cellájára akarok hivatkozni, a referencia az lenne = 2. lap! A1 (ahol felkiáltójel van a lap neve után)

És abban az esetben, ha több szó van a lap nevében (mondjuk, hogy példa adat), a hivatkozás az lenne = 'Példadatok'! A1 (ahol a név egyszeres idézőjelbe van foglalva, utána egy felkiáltójel).

Abban az esetben, ha külső munkafüzetre hivatkozik (tegyük fel, hogy az A1 -es cellára hivatkozik a „Példa munkalap” nevű munkafüzetben), a hivatkozás az alábbiak szerint lesz:

= '[Példa munkafüzet.xlsx] Példalap'! $ A $ 1

És ha bezárja a munkafüzetet, a hivatkozás megváltozik, és a munkafüzet teljes elérési útját tartalmazza (az alábbiak szerint):

= 'C: \ Users \ sumit \ Desktop \ [Példa munkafüzet.xlsx] Példalap'! $ A $ 1

Ha végül megváltoztatja a munkafüzet nevét vagy azt a munkalapot, amelyre a képlet utal, akkor #REF -et fog adni! hiba.

Olvassa el még: Hogyan találhat külső hivatkozásokat és hivatkozásokat az Excelben

Körkörös hivatkozások

Körkörös hivatkozás az, amikor (közvetlenül vagy közvetve) ugyanarra a cellára hivatkozik, ahol a képletet számítják.

Az alábbiakban egy egyszerű példa látható, ahol a SUM képletet használom az A4 -es cellában, miközben maga a számítás során használom.

= ÖSSZES (A1: A4)

Bár az Excel megjelenít egy értesítést, amely tájékoztatja Önt a körkörös hivatkozásról, nem minden esetben fogja megtenni. Ez pedig rossz eredményt adhat (figyelmeztetés nélkül).

Ha körkörös hivatkozásra gyanakszik a játékban, ellenőrizheti, hogy melyik cellában van ez.

Ehhez kattintson a Képlet fülre, és a „Képlet-ellenőrzés” csoportban kattintson a Hibaellenőrzés legördülő ikonra (a kis lefelé mutató nyíl).

Vigye a kurzort a Körkörös referencia opció fölé, és megjelenik a körkörös hivatkozással kapcsolatos cella.

Szövegként formázott cellák

Ha olyan helyzetben találja magát, hogy amint beírja a képletet, mint az Enter billentyűt, a képletet látja az érték helyett, akkor egyértelmű, hogy a cella szövegként van formázva.

Ha egy cella szövegként van formázva, akkor a képletet szöveges karakterláncnak tekinti, és úgy jeleníti meg, ahogy van.

Nem kényszeríti a számításra és az eredmény megadására.

És van rajta könnyű javítás.

1. Módosítsa a formátumot „Általános” értékre a „Szöveg” lehetőségről (ez a Számok csoport Kezdőlap lapján található)
2. Lépjen a képletet tartalmazó cellába, lépjen a szerkesztési módba (használja az F2 billentyűt, vagy kattintson duplán a cellára), és nyomja meg az Enter billentyűt

Abban az esetben, ha a fenti lépések nem oldják meg a problémát, ellenőrizni kell még azt is, hogy a cella elején van -e aposztróf. Sokan aposztróffal egészítik ki a képleteket és számokat szöveggé.

Ha aposztróf van, egyszerűen eltávolíthatja.

A szöveg automatikusan dátumokká alakul

Az Excelnek ez a rossz szokása, hogy a dátumnak tűnő dátumot tényleges dátummá alakítja át. Ha például 1/1 értéket ad meg, az Excel az aktuális év január 1-jére konvertálja.

Bizonyos esetekben ez pontosan az, amit szeretne, és bizonyos esetekben ez ellened is működik.

És mivel az Excel számként tárolja a dátumot és az időt, amint beírja az 1/1 -et, azt az aktuális év január 1 -jét reprezentáló számmá alakítja át. Esetemben, amikor ezt megteszem, 43831-es számmá alakítja át (2020.01.01.).

Ez zavarhatja a képleteket, ha ezeket a cellákat argumentumként használja a képletben.

Hogyan lehet ezt kijavítani?

Ismételten nem szeretnénk, ha az Excel automatikusan kiválasztaná a formátumot számunkra, ezért magunknak kell egyértelműen meghatároznunk a formátumot.

Az alábbiakban bemutatjuk azokat a lépéseket, amelyekkel a formátumot szöveggé kell változtatni, hogy ne konvertálja automatikusan a szöveget dátumra:

1. Válassza ki azokat a cellákat/tartományokat, amelyekben módosítani szeretné a formátumot
2. Kattintson a Kezdőlap fülre
3. A Szám csoportban kattintson a Formátum legördülő menüre
4. Kattintson a Szöveg elemre

Most, amikor bármit beír a kijelölt cellákba, az szövegnek minősül, és nem változik automatikusan.

Megjegyzés: A fenti lépések csak a formázás megváltoztatása után bevitt adatoknál működnek. Ez nem módosítja a formázási módosítás előtti dátumra konvertált szövegeket.

Egy másik példa, ahol ez nagyon elkeserítő lehet, ha olyan szöveget/számot ír be, amelynek nullái vannak. Az Excel automatikusan eltávolítja ezeket a vezető nullákat, mivel haszontalannak tartja őket. Például, ha beír egy 0001 -et egy cellába, az Excel 1 -re változtatja. Ha meg szeretné tartani ezeket az első nullákat, kövesse a fenti lépéseket.

A rejtett sorok/oszlopok váratlan eredményeket adhatnak

Ez nem az a képlet, amely rossz eredményt ad, hanem a rossz képlet használata.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és szeretném megkapni a C oszlop összes látható cellájának összegét.

A C12 cellában a SUM függvényt használtam, hogy megkapjam az összes értékesítési értéket az összes megadott rekordhoz.

Eddig jó!

Most szűrőt alkalmazok a cikk oszlopra, hogy csak a nyomtatóeladások rekordjait jelenítse meg.

És itt van a probléma - a C12 cellában lévő képlet még mindig ugyanazt az eredményt mutatja - azaz az összes rekord összegét.

Mint mondtam, a képlet nem ad rossz eredményt. Valójában a SUM funkció tökéletesen működik.

A probléma az, hogy itt rossz képletet használtunk.

A SUM függvény nem veszi figyelembe a szűrt adatokat, és megadja az összes cella eredményét (rejtett vagy látható). Ha csak a látható cellák összegét/számát/átlagát szeretné megkapni, használja a RÉSZLEGES vagy az ÖSSZEGZÉS függvényeket.

Kulcs elvitelre - Ismerje meg az Excel funkcióinak helyes használatát és korlátait.

Ezek azok a gyakori okok, amelyeket láttam, hogy hol van Előfordulhat, hogy az Excel képletek nem működnek, vagy váratlan vagy hibás eredményeket adnak. Biztos vagyok benne, hogy sokkal több oka lehet annak, hogy az Excel képlet nem működik vagy frissül.

Ha bármilyen más okot tud (esetleg valamit, ami gyakran felbosszantja), tudassa velem a megjegyzések részben.

Remélem, hasznosnak találta ezt az oktatóanyagot!