Tento článek vám pomůže pochopit všechny příčiny chyb #SPILL a také řešení, jak je opravit v Excelu 365.
#ROZLÍT! je nový druh chyby Excelu, který se vyskytuje hlavně tehdy, když se vzorec, který produkuje více výsledků výpočtů, pokouší zobrazit své výstupy v rozsahu rozlití, ale tento rozsah již obsahuje některá další data.
Blokujícími daty může být cokoli, včetně textové hodnoty, sloučených buněk, znaku prosté mezery, nebo i když není dostatek místa pro vrácení výsledků. Řešení je jednoduché, buď vymažte rozsah jakýchkoli blokujících dat, nebo vyberte prázdné pole buněk, které v něm neobsahují žádný typ dat.
Při výpočtu dynamických maticových vzorců obvykle dochází k chybě rozlití, protože dynamický maticový vzorec je ten, který zobrazuje výsledky do více buněk nebo pole. Podívejme se podrobněji a pochopíme, co spouští tuto chybu v Excelu a jak ji vyřešit.
Co způsobuje chybu rozlití?
Od uvedení dynamických polí v roce 2018 mohou vzorce Excel zpracovávat více hodnot najednou a vracet výsledky ve více než jedné buňce. Dynamická pole jsou pole s měnitelnou velikostí, která umožňují vzorcům vracet více výsledků do rozsahu buněk na listu na základě vzorce zadaného v jedné buňce.
Když dynamický maticový vzorec vrátí více výsledků, tyto výsledky se automaticky rozlijí do sousedních buněk. Toto chování se v Excelu nazývá „Rozlití“. A rozsah buněk, kam se výsledky přelévají, se nazývá ‚Rozsah úniku‘. Rozsah úniku se automaticky rozšíří nebo zmenší na základě hodnot zdroje.
Pokud se vzorec pokouší vyplnit rozsah rozlití více výsledky, ale je něčím v tomto rozsahu blokován, dojde k chybě #SPILL.
Excel má nyní 9 funkcí, které k řešení problémů využívají funkci Dynamic Array, mezi které patří:
- SEKVENCE
- FILTR
- PŘEMÍSTIT
- TŘÍDĚT
- SEŘAZENO PODLE
- RANDARRAY
- UNIKÁTNÍ
- XLOOKUP
- XMATCH
Dynamické maticové vzorce jsou k dispozici pouze v „Excel 365“ a v současnosti je nepodporuje žádný offline software Excel (tj. Microsoft Excel 2016, 2019).
Chyby při rozlití nejsou způsobeny pouze zablokováním dat, existuje několik důvodů, proč se vám může zobrazit chyba #Rozlití. Pojďme prozkoumat různé situace, ve kterých se můžete setkat s #SPILL! chyby a jak je opravit.
Rozsah úniku není prázdný
Jednou z hlavních příčin chyby rozlití je, že rozsah rozlití není prázdný. Pokud se například pokoušíte zobrazit 10 výsledků, ale pokud jsou v jakékoli buňce v oblasti rozlití nějaká data, vzorec vrátí #SPILL! chyba.
Příklad 1:
V níže uvedeném příkladu jsme do buňky C2 zadali funkci TRANSPOSE pro převod vertikálního rozsahu buněk (B2:B5) na horizontální rozsah (C2:F2). Místo přepnutí sloupce na řádek nám Excel ukáže #SPILL! chyba.
A když kliknete na buňku vzorce, uvidíte přerušovaný modrý okraj označující oblast/rozsah úniku (C2:F2), který je potřebný k zobrazení výsledků, jak je uvedeno níže. Také si všimnete žluté varovné značky s vykřičníkem.
Chcete-li pochopit důvod chyby, klikněte na ikonu varování vedle chyby a zobrazte zprávu v prvním řádku zvýrazněnou šedě. Jak vidíte, říká se zde „Rozsah úniku není prázdný“.
Problém je v tom, že buňky v oblasti rozlití D2 a E2 mají textové znaky (ne prázdné), a proto došlo k chybě.
Řešení:
Řešení je jednoduché, buď vymažte data (buď přesunout nebo smazat) umístěná v oblasti úniku nebo přesuňte vzorec na jiné místo, kde není žádná překážka.
Jakmile odstraníte nebo přesunete blokování, Excel automaticky naplní buňky výsledky vzorce. Zde, když vymažeme text v D2 a E2, vzorec transponuje sloupec do řádku, jak bylo zamýšleno.
Příklad 2:
V níže uvedeném příkladu, i když se rozsah rozlití zdá prázdný, vzorec stále zobrazuje Rozlití! chyba. Je to proto, že přelití není ve skutečnosti prázdné, má neviditelný prostorový charakter v jedné z buněk.
Je těžké najít mezery nebo jakýkoli jiný neviditelný znak skrývající se v zdánlivě prázdných buňkách. Chcete-li najít takové buňky s nežádoucími daty, klikněte na plovoucí prvek Error (varovný znak) a z nabídky vyberte možnost „Vybrat blokující buňky“ a dostanete se do buňky, která obsahuje blokující data.
Jak můžete vidět, na níže uvedeném snímku obrazovky má buňka E2 dvě mezery. Když tato data vymažete, získáte správný výstup.
Někdy může být neviditelným znakem text formátovaný stejnou barvou písma, jako je barva výplně buňky, nebo hodnota buňky naformátovaná pomocí číselného kódu ;;;. Když vlastníte formátování hodnoty buňky pomocí ;;;, skryje cokoli v této buňce, bez ohledu na barvu písma nebo barvu buňky.
Rozlitá oblast obsahuje sloučené buňky
Někdy, #SPILL! dojde k chybě, když rozsah rozlití obsahuje sloučené buňky. Vzorec dynamického pole nefunguje se sloučenými buňkami. Chcete-li to opravit, stačí zrušit sloučení buněk v oblasti přelití nebo přesunout vzorec do jiné oblasti, která nemá žádné sloučené buňky.
V níže uvedeném příkladu, i když je rozsah rozlití prázdný (C2:CC8), vzorec vrátí chybu Rozlití. Je to proto, že buňky C4 a C5 jsou sloučeny.
Chcete-li se ujistit, že sloučené buňky jsou důvodem, proč se vám chyba zobrazuje, klikněte navarovný signál a ověřte příčinu – ‚Rozsah rozlití sloučil buňku‘.
Řešení:
Chcete-li zrušit sloučení buněk, vyberte sloučené buňky a poté na kartě ‚Domů‘ klikněte na tlačítko ‚Sloučit a vycentrovat‘ a vyberte ‚Zrušit sloučení buněk‘.
Pokud máte potíže s nalezením sloučených buněk ve velké tabulce, klikněte na možnost „Vybrat blokující buňky“ v nabídce varovných značek a přejděte na sloučené buňky.
Rozsah úniku v tabulce
Rozlité maticové vzorce nejsou v tabulkách aplikace Excel podporovány. Dynamický maticový vzorec by měl být zadán pouze do jedné samostatné buňky. Pokud zadáte vzorec rozlitého pole do tabulky nebo když oblast rozlití spadne do tabulky, zobrazí se chyba Rozlití. Když k tomu dojde, zkuste tabulku převést na normální rozsah nebo přesuňte vzorec mimo tabulku.
Když například zadáme následující vzorec rozlitého rozsahu do tabulky aplikace Excel, dostaneme chybu Spill v každé buňce tabulky, nejen v buňce vzorce. Je to proto, že Excel automaticky zkopíruje jakýkoli vzorec zadaný v tabulce do každé buňky ve sloupci tabulky.
Také se zobrazí chyba rozlití, když se vzorec pokusí rozlít výsledky v tabulce. Na níže uvedeném snímku obrazovky spadá oblast rozlití do stávající tabulky, takže se zobrazí chyba rozlití.
Chcete-li potvrdit příčinu této chyby, klikněte na varovný znak a podívejte se na důvod chyby – „Rozsah úniku v tabulce“
Řešení:
Chcete-li chybu opravit, budete muset vrátit tabulku aplikace Excel zpět do rozsahu. Chcete-li to provést, klikněte pravým tlačítkem myši kdekoli v tabulce, klikněte na „Tabulka“ a poté vyberte možnost „Převést na rozsah“. Případně můžete kliknout levým tlačítkem kdekoli v tabulce, poté přejít na kartu „Návrh tabulky“ a vybrat možnost „Převést na rozsah“.
Rozsah úniku je neznámý
Pokud Excel nebyl schopen určit velikost rozlitého pole, spustí chybu Spill. Někdy vzorec umožňuje změnit velikost dynamického pole mezi jednotlivými průchody výpočtem. Pokud se velikost dynamického pole během výpočtů neustále mění a nevyrovnává, způsobí to #SPILL! Chyba.
Tento typ chyby Spill se obvykle spouští při použití nestálých funkcí, jako jsou funkce RAND, RANDARRAY, RANDBETWEEN, OFFSET a INDIRECT.
Když například použijeme níže uvedený vzorec v buňce B3, dostaneme chybu Spill:
=SEQUENCE(RANDBETWEEN(1, 500))V příkladu funkce RANDBETWEEN vrací náhodné celé číslo mezi čísly 1 a 500 a její výstup se neustále mění. A funkce SEQUENCE neví, kolik hodnot vytvořit v poli rozlití. Chyba #SPILL.
Příčinu chyby můžete také potvrdit kliknutím na varovný znak – „Rozsah úniku není znám“.
Řešení:
Chcete-li opravit chybu tohoto vzorce, jedinou možností je použít pro výpočet jiný vzorec.
Rozsah úniku je příliš velký
Občas můžete spustit vzorec, jehož výstupem je rozlitá oblast, která je příliš velká na to, aby ji list zvládl, a může přesahovat okraje listu. Když se to stane, můžete dostat #SPILL! chyba. Chcete-li tento problém vyřešit, můžete zkusit odkazovat na konkrétní rozsah nebo jednu buňku namísto celých sloupců nebo pomocí znaku „@“ povolit implicitní průnik
V níže uvedeném příkladu se snažíme vypočítat 20 % prodejních čísel ve sloupci A a vrátit výsledky ve sloupci B, ale místo toho se zobrazí chyba Spill.
Vzorec v B3 vypočítá 20 % hodnoty v A3, potom 20 % hodnoty v A4 a tak dále. Vytváří více než milion výsledků (1 048 576) a všechny je vysype do sloupce B počínaje buňkou B3, ale dostane se až na konec listu. Není dostatek místa pro zobrazení všech výstupů, v důsledku toho se zobrazí chyba #SPILL.
Jak můžete vidět, příčinou této chyby je to, že – „Rozsah úniku je příliš velký“.
Řešení:
Chcete-li tento problém vyřešit, zkuste změnit celý sloupec s relevantním rozsahem nebo odkazem na jednu buňku nebo přidáním operátoru @ proveďte implicitní průnik.
Oprava 1: Místo celých sloupců můžete zkusit odkazovat na rozsahy. Zde změníme celý rozsah A:A za A3:A11 ve vzorci a vzorec automaticky vyplní rozsah výsledky.
Oprava 2: Nahraďte celý sloupec pouze odkazem na buňku na stejném řádku (A3) a poté zkopírujte vzorec dolů v rozsahu pomocí úchytu výplně.
Oprava 3: Můžete také zkusit přidat operátor @ před odkaz, abyste provedli implicitní průnik. Tím se výstup zobrazí pouze v buňce vzorce.
Potom zkopírujte vzorec z buňky B3 do zbytku rozsahu.
Poznámka: Když upravujete rozlitý vzorec, můžete upravit pouze první buňku v oblasti/rozsahu rozlití. Vzorec můžete vidět v jiných buňkách rozsahu rozlití, ale budou zašedlé a nelze je aktualizovat.
Nedostatek paměti
Pokud spustíte rozsypaný maticový vzorec, který způsobí, že Excelu dojde paměť, může to způsobit chybu #SPILL. Za těchto okolností zkuste odkazovat na menší pole nebo rozsah.
Nerozpoznáno / Záložní
Chybu rozlití můžete také získat, i když aplikace Excel nerozpozná nebo nemůže uvést příčinu chyby. V takových případech znovu zkontrolujte svůj vzorec a ujistěte se, že všechny parametry funkcí jsou správné.
Nyní znáte všechny příčiny a řešení pro #SPILL! chyby v Excelu 365.