Ez a cikk segít megérteni a #SPILL hibák okait, valamint a megoldásokat az Excel 365-ben történő javításukra.
#BUKÁS! egy újfajta Excel hiba, amely főként akkor fordul elő, ha egy több számítási eredményt előállító képlet egy spill-tartományban próbálja megjeleníteni a kimeneteit, de ez a tartomány már tartalmaz más adatokat.
A blokkoló adat bármi lehet, beleértve a szöveges értéket, az egyesített cellákat, a sima szóköz karaktert, vagy még akkor is, ha nincs elég hely az eredmények visszaadásához. A megoldás egyszerű, vagy törölje a blokkoló adatok tartományát, vagy válasszon ki egy üres cellatömböt, amely nem tartalmaz semmilyen típusú adatot.
Kihagyási hiba általában dinamikus tömbképletek kiszámításakor fordul elő, mivel a dinamikus tömbképlet az, amely több cellába vagy egy tömbbe adja ki az eredményeket. Nézzük meg részletesebben, és értsük meg, mi váltja ki ezt a hibát az Excelben, és hogyan lehet megoldani.
Mi okozza a kiömlési hibát?
A dinamikus tömbök 2018-as bevezetése óta az Excel-képletek egyszerre több értéket is kezelhetnek, és több cellában is visszaadják az eredményeket. A dinamikus tömbök olyan átméretezhető tömbök, amelyek lehetővé teszik, hogy a képletek több eredményt adjanak vissza a munkalap celláinak egy tartományába az egyetlen cellába beírt képlet alapján.
Ha egy dinamikus tömbképlet több eredményt ad vissza, ezek az eredmények automatikusan a szomszédos cellákba kerülnek. Ezt a viselkedést az Excelben „kiömlésnek” nevezik. És a cellák azon tartományát, ahová az eredmények átfolynak, „kiömlési tartománynak” nevezik. A kiömlési tartomány automatikusan bővül vagy szűkül a forrásértékek alapján.
Ha egy képlet több eredménnyel próbál kitölteni egy kiömlési tartományt, de valami blokkolja a tartományban, akkor #SPILL hiba történik.
Az Excel immár 9 olyan funkcióval rendelkezik, amelyek a Dynamic Array funkciót használják a problémák megoldására, ezek a következők:
- SORREND
- SZŰRŐ
- TRANSPOSE
- FAJTA
- SORREND
- RANDARRAY
- EGYEDI
- XLOOKUP
- XMATCH
A dinamikus tömbképletek csak az „Excel 365” programban érhetők el, és jelenleg egyetlen offline Excel szoftver sem támogatja (azaz Microsoft Excel 2016, 2019).
A kiömlési hibákat nem csak az adatok akadályozása okozza, hanem több oka is lehet annak, hogy #Spill errort kaphat. Fedezzük fel azokat a különböző helyzeteket, amikor találkozhat a #KIFOGÁSSAL! hibákat és azok kijavítását.
A kiömlési tartomány nem üres
A kiömlési hiba egyik elsődleges oka az, hogy a kiömlési tartomány nem üres. Például, ha 10 eredményt próbál megjeleníteni, de a kiömlési területen lévő cellák bármelyikében van adat, a képlet a #SPILL! hiba.
1. példa:
Az alábbi példában beírtuk a TRANSPOSE függvényt a C2 cellába, hogy a függőleges cellatartományt (B2:B5) vízszintes tartományba (C2:F2) alakítsuk át. Ahelyett, hogy az oszlopot sorra váltaná, az Excel a #SPILL! hiba.
És amikor rákattint a képlet cellájára, egy szaggatott-kék szegélyt fog látni, amely jelzi a kiömlési területet/tartományt (C2:F2), amelyre szükség van az eredmények lentebb látható megjelenítéséhez. Ezenkívül észrevesz egy sárga figyelmeztető táblát felkiáltójellel.
A hiba okának megértéséhez kattintson a hiba melletti figyelmeztető ikonra, és tekintse meg az üzenetet az első sorban szürkével kiemelve. Amint látja, itt azt írja: „A kiömlési tartomány nem üres”.
A probléma itt az, hogy a D2 és E2 kihagyási tartomány celláiban szövegkarakterek vannak (nem üresek), ezért a hiba.
Megoldás:
A megoldás egyszerű: vagy törölje a kiömlési tartományban található adatokat (vagy helyezze át vagy törölje), vagy helyezze át a képletet egy másik helyre, ahol nincs akadály.
Amint törli vagy áthelyezi az elzáródást, az Excel automatikusan feltölti a cellákat a képlet eredményeivel. Itt, amikor töröljük a szöveget a D2-ben és az E2-ben, a képlet az oszlopot a szándék szerint sorba transzponálja.
2. példa:
Az alábbi példában, bár a kiömlési tartomány üresnek tűnik, a képlet továbbra is a kiömlést mutatja! hiba. Ez azért van, mert a kiömlés valójában nem üres, az egyik cellában van egy láthatatlan szóköz.
Nehéz megtalálni a szóköz karaktereket vagy bármely más láthatatlan karaktert, amely üresnek tűnő cellákban rejtőzik. A nem kívánt adatokat tartalmazó cellák megtalálásához kattintson az Error floatie (figyelmeztető jel) elemre, és válassza ki a menüből a 'Select Obstructing Cells' menüpontot, és az akadályozó adatokat tartalmazó cellához kerül.
Mint látható, az alábbi képernyőképen az E2 cellában két szóköz van. Ha törli ezeket az adatokat, megkapja a megfelelő kimenetet.
Néha a láthatatlan karakter lehet a cella kitöltési színével megegyező betűszínnel formázott szöveg vagy a ;;; számkóddal egyénileg formázott cellaérték. Ha egyénileg formáz egy cellaértéket a ;;;-vel, akkor a cellában lévő elemeket elrejti, a betűtípustól vagy a cella színétől függetlenül.
A kiömlési tartomány egyesített cellákat tartalmaz
Néha a #KIFOLYÁS! hiba történik, ha a kiömlési tartomány tartalmazza az egyesített cellákat. A dinamikus tömbképlet nem működik egyesített cellákkal. Ennek kijavításához mindössze annyit kell tennie, hogy szüntesse meg a cellák egyesítését a kiszóródási tartományban, vagy helyezze át a képletet egy másik tartományba, amelyben nincsenek egyesített cellák.
Az alábbi példában annak ellenére, hogy a kiömlési tartomány üres (C2:CC8), a képlet a Kiömlési hibát adja vissza. Ez azért van, mert a C4 és C5 cellák egyesültek.
Ha meg szeretné győződni arról, hogy az egyesített cellák okozzák a hibát, kattintson a ikonrafigyelmeztető jelet, és ellenőrizze az okot – „A kiömlési tartomány egyesült cellával”.
Megoldás:
A cellák egyesítésének megszüntetéséhez jelölje ki az egyesített cellákat, majd a „Kezdőlap” fülön kattintson az „Egyesítés és központ” gombra, és válassza a „Cellák összevonása” lehetőséget.
Ha nehezen találja meg az egyesített cellákat a nagyméretű táblázatban, kattintson a figyelmeztető jelek menüjének „Akadályozó cellák kiválasztása” parancsára, hogy az egyesített cellákra ugorjon.
Kiömlési tartomány a táblázatban
A kiszórt tömbképletek nem támogatottak az Excel-táblázatokban. A dinamikus tömb képletét csak egyetlen cellában szabad megadni. Ha beír egy kiömlött tömbképletet egy táblázatba, vagy amikor a kiömlött terület egy táblázatba esik, akkor a Kiömlés hibaüzenetet kapja. Ha ez megtörténik, próbálja meg átalakítani a táblázatot normál tartományba, vagy mozgassa a képletet a táblázaton kívülre.
Például, ha beírjuk a következő kiszórt tartomány képletet egy Excel-táblázatba, akkor a táblázat minden cellájában kihagyáshiba jelenik meg, nem csak a képletcellában. Ennek az az oka, hogy az Excel automatikusan átmásolja a táblázatban megadott képleteket a táblázat oszlopának minden cellájába.
Ezenkívül kiöntési hibaüzenetet kap, amikor egy képlet megpróbálja kiönteni az eredményeket egy táblázatba. Az alábbi képernyőképen a kiömlési terület a meglévő táblázatba esik, így kiömlési hibát kapunk.
A hiba okának megerősítéséhez kattintson a figyelmeztető jelre, és nézze meg a hiba okát – „A kiömlési tartomány a táblázatban”
Megoldás:
A hiba kijavításához vissza kell állítania az Excel táblázatot a tartományba. Ehhez kattintson a jobb gombbal bárhová a táblázatban, kattintson a "Táblázat" elemre, majd válassza a "Konvertálás tartományba" lehetőséget. Alternatív megoldásként kattintson a bal egérgombbal bárhová a táblázatban, majd lépjen a „Táblázattervezés” fülre, és válassza a „Tartományra konvertálás” lehetőséget.
A kiömlési tartomány ismeretlen
Ha az Excel nem tudta megállapítani a kiszóródott tömb méretét, akkor kiváltja a Kiszórt hibát. Néha a képlet lehetővé teszi, hogy egy dinamikus tömb átméretezzen az egyes számítási lépések között. Ha a dinamikus tömb mérete folyamatosan változik a számítások során, és nem egyensúlyoz ki, az #SPILL! Hiba.
Az ilyen típusú kiömlési hiba általában olyan illékony funkciók használatakor lép fel, mint a RAND, RANDARRAY, RANDBETWEEN, OFFSET és INDIRECT funkciók.
Például, ha az alábbi képletet használjuk a B3 cellában, akkor a Spill (Kiömlés) hibát kapjuk:
=SOROZAT(RANDBETWEEN(1, 500))A példában a RANDBETWEEN függvény egy 1 és 500 közötti véletlenszerű egész számot ad vissza, és a kimenete folyamatosan változik. A SEQUENCE függvény pedig nem tudja, hogy hány értéket állítson elő egy kiszóródó tömbben. Ezért a #SPILL hiba.
A hiba okát a Figyelmeztető jelre kattintva is megerősítheti – „A kiömlési tartomány ismeretlen”.
Megoldás:
A képlet hibájának kijavításához egyetlen választása az, hogy más képletet használ a számításhoz.
A kiömlési tartomány túl nagy
Időnként olyan képletet hajthat végre, amely túl nagy kiszórt tartományt ad ki ahhoz, hogy a munkalap kezelje, és túlnyúlhat a munkalap szélein. Amikor ez megtörténik, előfordulhat, hogy #SPILL! hiba. A probléma megoldásához megpróbálhat egy adott tartományra vagy egy cellára hivatkozni a teljes oszlopok helyett, vagy a „@” karakter használatával engedélyezheti az implicit metszéspontot.
Az alábbi példában megpróbáljuk kiszámítani az A oszlopban lévő értékesítési számok 20%-át, és az eredményeket a B oszlopban visszaadni, de ehelyett kiömlési hibát kapunk.
A B3-ban szereplő képlet az A3-ban szereplő érték 20%-át, majd az A4-es érték 20%-át számítja ki, és így tovább. Több mint egymillió eredményt (1 048 576) ad, és mindegyiket a B oszlopba szórja ki a B3 cellától kezdve, de eléri a munkalap végét. Nincs elég hely az összes kimenet megjelenítéséhez, ennek eredményeként #SPILL hibát kapunk.
Amint láthatja, ennek a hibának az az oka, hogy – „túl nagy a kiömlési tartomány”.
Megoldások:
A probléma megoldásához próbálja meg módosítani a teljes oszlopot egy releváns tartományra vagy egy egycellás hivatkozásra, vagy adja hozzá a @ operátort az implicit metszés végrehajtásához.
Javítás 1: A teljes oszlopok helyett megpróbálhat tartományokra hivatkozni. Itt megváltoztatjuk a teljes A:A tartományt A3:A11-gyel a képletben, és a képlet automatikusan feltölti a tartományt az eredményekkel.
2. javítás: Cserélje ki a teljes oszlopot csak a cellahivatkozással ugyanabban a sorban (A3), majd a kitöltő fogantyúval másolja le a képletet a tartományban.
3. javítás: Megpróbálhatja a @ operátort is hozzáadni a hivatkozás elé, hogy implicit metszéspontot hajtson végre. Ez csak a képlet cellában jeleníti meg a kimenetet.
Ezután másolja a képletet a B3 cellából a tartomány többi részébe.
Jegyzet: Kiömlött képlet szerkesztésekor csak a kiöntési terület/tartomány első celláját szerkesztheti. A képlet a kiömlési tartomány többi cellájában is látható, de ezek szürkék lesznek, és nem frissíthetők.
Elfogyott a memória
Ha olyan kiszóródott tömbképletet hajt végre, amely miatt az Excel kifogy a memóriából, az #SPILL hibát okozhat. Ilyen körülmények között próbáljon meg egy kisebb tömbre vagy tartományra hivatkozni.
Felismeretlen / Tartalék
Akkor is kaphat kiömlési hibát, ha az Excel nem ismeri fel vagy nem tudja összeegyeztetni a hiba okát. Ilyen esetekben ellenőrizze még egyszer a képletet, és győződjön meg arról, hogy a függvények összes paramétere helyes.
Most már ismeri a #SPILL összes okát és megoldását! hibák az Excel 365-ben.