souhrn
Chyba # SPILL nastane, když je rozsah rozlití blokován něčím na listu. Řešením je obvykle vyčistit rozsah úniku jakýchkoli překážejících dat. Níže naleznete další informace a kroky k vyřešení.
Vysvětlení
O rozlití a # SPILL! chyba
Se zavedením dynamických polí v aplikaci Excel vzorce, které vracejí více hodnot, „vysypou“ tyto hodnoty přímo do listu. Obdélník, který uzavírá hodnoty, se nazývá „rozsah rozlití“. Při změně dat se rozsah rozlití podle potřeby rozšíří nebo zmenší. Můžete vidět přidané nové hodnoty nebo zmizení stávajících hodnot.
Video: Rozlití a rozsah úniku
Chyba # SPILL nastane, když je rozsah rozlití blokován něčím na listu. Někdy se to očekává. Například jste zadali vzorec a očekávali jste, že se rozlije, ale stávající data v listu jsou v cestě. Řešením je pouze vyčistit rozsah úniku jakýchkoli překážejících dat.
Někdy však může být chyba neočekávaná, a proto matoucí. Níže si přečtěte, jak může být tato chyba způsobena, a co můžete udělat pro její vyřešení.
Chování při rozlití je nativní
Je důležité si uvědomit, že chování při rozlití je automatické a nativní. V Dynamickém Excelu (aktuálně pouze Office 365 Excel) může jakýkoli vzorec, dokonce i jednoduchý vzorec bez funkcí, rozlit výsledky. Ačkoli existují způsoby, jak zabránit vzorci v návratu více výsledků, samotné rozlití nelze deaktivovat globálním nastavením.
Podobně v aplikaci Excel neexistuje žádná možnost „zakázat chyby #SPILL. Chcete-li opravit chybu #SPILL, budete muset vyšetřit a vyřešit hlavní příčinu problému.
Oprava # 1 - vyčistěte rozsah úniku
Toto je nejjednodušší případ řešení. Vzorec by měl rozlit více hodnot, ale místo toho vrátí #SPILL! protože něco stojí v cestě. Chcete-li chybu vyřešit, vyberte libovolnou buňku v rozsahu rozlití, abyste viděli její hranice. Pak buď přesuňte data blokování na nové místo, nebo data zcela odstraňte. Upozorňujeme, že buňky v rozsahu rozlití musí být prázdné, proto věnujte pozornost buňkám, které obsahují neviditelné znaky, například mezery.
Na obrazovce níže blokuje „x“ rozsah rozlití:
Po odebrání „x“ funkce UNIQUE rozlije výsledky normálně:
Oprava # 2 - přidat znak @
Před dynamickými poli aplikace Excel tiše použila chování zvané „implicitní průnik“, aby zajistila, že určité vzorce s potenciálem vrátit více výsledků vrátí pouze jeden výsledek. V nedynamickém poli Excel tyto vzorce vrátí normálně vypadající výsledek bez chyby. V určitých případech však stejný vzorec zadaný v dynamickém Excelu může vygenerovat chybu #SPILL. Například na obrazovce níže obsahuje buňka D5 tento vzorec zkopírovaný dolů:
=$B$5:$B$10+3
Tento vzorec by nevrhl chybu, řekněme Excel 2016, protože implicitní průnik by zabránil tomu, aby vzorec vrátil více výsledků. V Dynamickém Excelu však vzorec automaticky vrací více rozlitých výsledků do listu a které do sebe spadnou, protože vzorec je zkopírován z D5: D10.
Jedním z řešení je použít znak @ k povolení implicitní křižovatky takto:
= @$B$5:$B$10+3
S touto změnou každý vzorec vrátí jeden výsledek znovu a chyba #SPILL zmizí.
Poznámka: Toto částečně vysvětluje, proč se ve vzorcích vytvořených ve starších verzích aplikace Excel může náhle objevit znak „@“. Důvodem je zachování kompatibility. Vzhledem k tomu, že vzorce ve starších verzích aplikace Excel nelze rozlit do více buněk, přidá se znak @, aby se zajistilo stejné chování při otevření vzorce v dynamickém Excelu.
Oprava č. 3 - nativní vzorec dynamického pole
Dalším (lepším) způsobem, jak opravit chybu #SPILL zobrazenou výše, je použití nativního vzorce dynamického pole v D5 takto:
=B5:B10+3
V Dynamickém Excelu tento jediný vzorec přenese výsledky do rozsahu D5: D10, jak je vidět na následujícím obrázku:
Všimněte si, že není nutné používat absolutní referenci.
