Tento článok vám pomôže pochopiť všetky príčiny chýb #SPILL, ako aj riešenia na ich opravu v Exceli 365.
#VYLIAŤ! je nový druh chyby Excelu, ktorý sa vyskytuje hlavne vtedy, keď sa vzorec, ktorý vytvára viacero výsledkov výpočtu, pokúša zobraziť svoje výstupy v rozličnom rozsahu, ale tento rozsah už obsahuje nejaké iné údaje.
Blokovacími údajmi môže byť čokoľvek vrátane textovej hodnoty, zlúčených buniek, znaku obyčajnej medzery alebo aj v prípade, že nie je dostatok miesta na vrátenie výsledkov. Riešenie je jednoduché, buď vymažte rozsah všetkých blokujúcich údajov alebo vyberte prázdne pole buniek, ktoré neobsahujú žiadny typ údajov.
Chyba rozliatia sa zvyčajne vyskytuje pri výpočte vzorcov dynamického poľa, pretože vzorec dynamického poľa je ten, ktorý zobrazuje výsledky do viacerých buniek alebo poľa. Pozrime sa podrobnejšie a pochopíme, čo spúšťa túto chybu v Exceli a ako ju vyriešiť.
Čo spôsobuje chybu rozliatia?
Od spustenia dynamických polí v roku 2018 môžu vzorce Excel spracovať viacero hodnôt naraz a vrátiť výsledky vo viac ako jednej bunke. Dynamické polia sú polia s meniteľnou veľkosťou, ktoré umožňujú vzorcom vrátiť viacero výsledkov do rozsahu buniek v hárku na základe vzorca zadaného v jednej bunke.
Keď vzorec dynamického poľa vráti viacero výsledkov, tieto výsledky sa automaticky rozlejú do susedných buniek. Toto správanie sa v Exceli nazýva „Rozliatie“. A rozsah buniek, do ktorých sa výsledky prelievajú, sa nazýva „Rozsah úniku“. Rozsah úniku sa automaticky rozšíri alebo zmenší na základe zdrojových hodnôt.
Ak sa vzorec pokúša vyplniť rozsah rozliatia viacerými výsledkami, ale je zablokovaný niečím v tomto rozsahu, objaví sa chyba #SPILL.
Excel má teraz 9 funkcií, ktoré na riešenie problémov využívajú funkciu dynamického poľa, medzi ktoré patria:
- SEKVENCIA
- FILTER
- TRANSPOSE
- TRIEDIŤ
- TRIEDIŤ PODĽA
- RANDARRAY
- UNIKÁTNY
- XLOOKUP
- XMATCH
Vzorce dynamického poľa sú k dispozícii iba v programe „Excel 365“ a momentálne ho nepodporuje žiadny offline softvér Excel (t. j. Microsoft Excel 2016, 2019).
Chyby pri rozlievaní nie sú spôsobené len zablokovaním údajov, existuje niekoľko dôvodov, prečo sa vám môže zobraziť chyba #Rozliatie. Poďme preskúmať rôzne situácie, v ktorých sa môžete stretnúť s #SPILL! chyby a ako ich opraviť.
Rozsah úniku nie je prázdny
Jednou z hlavných príčin chyby rozliatia je, že rozsah rozliatia nie je prázdny. Ak sa napríklad pokúšate zobraziť 10 výsledkov, ale ak sa v niektorej z buniek v oblasti úniku nachádzajú nejaké údaje, vzorec vráti #VYLIAŤ! chyba.
Príklad 1:
V nižšie uvedenom príklade sme do bunky C2 zadali funkciu TRANSPOSE na konverziu vertikálneho rozsahu buniek (B2:B5) na horizontálny rozsah (C2:F2). Namiesto prepínania stĺpca na riadok nám Excel zobrazuje #SPILL! chyba.
A keď kliknete na bunku vzorca, uvidíte prerušovaný modrý okraj označujúci oblasť/rozsah úniku (C2:F2), ktorý je potrebný na zobrazenie výsledkov, ako je uvedené nižšie. Tiež si všimnete žltý výstražný znak s výkričníkom.
Ak chcete pochopiť dôvod chyby, kliknite na ikonu upozornenia vedľa chyby a pozrite si správu v prvom riadku zvýraznenú sivou farbou. Ako vidíte, hovorí sa tu „Rozsah úniku nie je prázdny“.
Problém je v tom, že bunky v rozsahu rozliatia D2 a E2 majú textové znaky (nie prázdne), a preto došlo k chybe.
Riešenie:
Riešenie je jednoduché, buď vymažte údaje (buď presuňte alebo odstráňte) nachádzajúce sa v oblasti úniku alebo presuňte vzorec na iné miesto, kde nie je žiadna prekážka.
Hneď ako odstránite alebo presuniete blokádu, Excel automaticky vyplní bunky výsledkami vzorca. Tu, keď vymažeme text v D2 a E2, vzorec transponuje stĺpec do riadku podľa plánu.
Príklad 2:
V nižšie uvedenom príklade, aj keď sa rozsah rozliatia zdá prázdny, vzorec stále zobrazuje Rozliatie! chyba. Je to preto, že preliačina v skutočnosti nie je prázdna, má neviditeľný priestorový charakter v jednej z buniek.
Je ťažké nájsť medzery alebo akýkoľvek iný neviditeľný znak, ktorý sa skrýva v tom, čo sa zdá byť prázdnymi bunkami. Ak chcete nájsť takéto bunky s nechcenými údajmi, kliknite na ikonu Error floatie (výstražný znak) a z ponuky vyberte možnosť „Vybrať blokujúce bunky“ a dostanete sa do bunky, ktorá obsahuje brániace údaje.
Ako vidíte, na obrázku nižšie má bunka E2 dve medzery. Keď tieto údaje vymažete, získate správny výstup.
Niekedy môže byť neviditeľným znakom text naformátovaný rovnakou farbou písma ako je farba výplne bunky alebo hodnota bunky naformátovaná na základe číselného kódu ;;;. Keď naformátujete hodnotu bunky pomocou ;;;, skryje všetko v tejto bunke bez ohľadu na farbu písma alebo farbu bunky.
Rozsah úniku obsahuje zlúčené bunky
Niekedy, #SPILL! chyba nastane, keď rozsah úniku obsahuje zlúčené bunky. Vzorec dynamického poľa nefunguje so zlúčenými bunkami. Ak to chcete vyriešiť, stačí zrušiť zlúčenie buniek v rozsahu rozliatia alebo presunúť vzorec do iného rozsahu, ktorý nemá žiadne zlúčené bunky.
V nižšie uvedenom príklade, aj keď je rozsah úniku prázdny (C2:CC8), vzorec vráti chybu úniku. Je to preto, že bunky C4 a C5 sú zlúčené.
Aby ste sa uistili, že zlúčené bunky sú dôvodom, prečo sa vám zobrazuje chyba, kliknite navarovný signál a overte príčinu – „Rozsah úniku sa zlúčil do bunky“.
Riešenie:
Ak chcete zrušiť zlúčenie buniek, vyberte zlúčené bunky, potom na karte „Domov“ kliknite na tlačidlo „Zlúčiť a vycentrovať“ a vyberte „Zrušiť zlúčenie buniek“.
Ak máte problém nájsť zlúčené bunky vo veľkej tabuľke, kliknite na možnosť „Vybrať brániace bunky“ v ponuke výstražného znaku a preskočte na zlúčené bunky.
Rozsah úniku v tabuľke
Vzorce rozliateho poľa nie sú podporované v tabuľkách programu Excel. Vzorec dynamického poľa by sa mal zadať iba do jednej samostatnej bunky. Ak zadáte vzorec rozliateho poľa do tabuľky alebo keď oblasť rozliatia spadne do tabuľky, dostanete chybu Rozliatie. Keď sa to stane, skúste tabuľku skonvertovať na normálny rozsah alebo presuňte vzorec mimo tabuľky.
Napríklad, keď zadáme nasledujúci vzorec rozliateho rozsahu do tabuľky programu Excel, dostaneme chybu Spill v každej bunke tabuľky, nielen v bunke vzorca. Je to preto, že Excel automaticky skopíruje akýkoľvek vzorec zadaný v tabuľke do každej bunky v stĺpci tabuľky.
Tiež sa vám zobrazí chyba rozliatia, keď sa vzorec pokúsi vyliať výsledky do tabuľky. Na snímke obrazovky nižšie oblasť rozliatia spadá do existujúcej tabuľky, takže sa zobrazí chyba rozliatia.
Ak chcete potvrdiť príčinu tejto chyby, kliknite na varovný znak a pozrite si dôvod chyby – „Rozsah v tabuľke“
Riešenie:
Ak chcete chybu opraviť, budete musieť vrátiť tabuľku programu Excel späť do rozsahu. Ak to chcete urobiť, kliknite pravým tlačidlom myši kdekoľvek v tabuľke, kliknite na „Tabuľka“ a potom vyberte možnosť „Previesť na rozsah“. Prípadne môžete kliknúť ľavým tlačidlom myši kdekoľvek v tabuľke, potom prejsť na kartu „Návrh tabuľky“ a vybrať možnosť „Previesť na rozsah“.
Rozsah úniku je neznámy
Ak Excel nedokázal určiť veľkosť rozliateho poľa, spustí chybu Rozliatia. Vzorec niekedy umožňuje zmeniť veľkosť dynamického poľa medzi jednotlivými prechodmi výpočtov. Ak sa veľkosť dynamického poľa počas výpočtov neustále mení a nie je v rovnováhe, spôsobí to #SPILL! Chyba.
Tento typ chyby rozliatia sa zvyčajne spustí pri použití nestálych funkcií, ako sú funkcie RAND, RANDARRAY, RANDBETWEEN, OFFSET a INDIRECT.
Napríklad, keď použijeme nižšie uvedený vzorec v bunke B3, dostaneme chybu Spill:
=SEQUENCE(RANDBETWEEN(1, 500))V príklade funkcia RANDBETWEEN vracia náhodné celé číslo medzi číslami 1 a 500 a jej výstup sa neustále mení. A funkcia SEQUENCE nevie, koľko hodnôt sa má vytvoriť v poli úniku. Preto chyba #SPILL.
Príčinu chyby môžete potvrdiť aj kliknutím na výstražný znak – „Rozsah úniku je neznámy“.
Riešenie:
Ak chcete opraviť chybu tohto vzorca, vašou jedinou možnosťou je použiť na výpočet iný vzorec.
Rozsah úniku je príliš veľký
Občas môžete spustiť vzorec, ktorý vygeneruje rozliaty rozsah, ktorý je príliš veľký na to, aby ho hárok spracoval, a môže presahovať okraje hárka. Keď sa to stane, môžete dostať #SPILL! chyba. Ak chcete tento problém vyriešiť, môžete sa pokúsiť odkázať na konkrétny rozsah alebo jednu bunku namiesto celých stĺpcov alebo pomocou znaku „@“ povoliť implicitné priesečníky
V nižšie uvedenom príklade sa pokúšame vypočítať 20 % predajných čísel v stĺpci A a vrátiť výsledky v stĺpci B, no namiesto toho sa nám zobrazí chyba rozliatia.
Vzorec v B3 vypočíta 20 % hodnoty v A3, potom 20 % hodnoty v A4 atď. Produkuje viac ako milión výsledkov (1 048 576) a všetky z nich vysype do stĺpca B počnúc bunkou B3, ale dostane sa až na koniec pracovného hárka. Nie je dostatok miesta na zobrazenie všetkých výstupov, v dôsledku čoho sa zobrazí chyba #SPILL.
Ako vidíte, príčinou tejto chyby je, že – „Rozsah úniku je príliš veľký“.
Riešenia:
Ak chcete tento problém vyriešiť, skúste zmeniť celý stĺpec s relevantným rozsahom alebo odkazom na jednu bunku, prípadne pridajte operátor @, aby ste vykonali implicitné priesečníky.
Oprava 1: Namiesto celých stĺpcov môžete skúsiť odkazovať na rozsahy. Tu vo vzorci zmeníme celý rozsah A:A za A3:A11 a vzorec automaticky vyplní rozsah výsledkami.
Oprava 2: Nahraďte celý stĺpec iba odkazom na bunku v rovnakom riadku (A3) a potom skopírujte vzorec nadol v rozsahu pomocou rukoväte výplne.
Oprava 3: Môžete tiež skúsiť pridať operátor @ pred odkaz na vykonanie implicitného priesečníka. Tým sa výstup zobrazí iba v bunke vzorca.
Potom skopírujte vzorec z bunky B3 do zvyšku rozsahu.
Poznámka: Keď upravujete rozliaty vzorec, môžete upraviť iba prvú bunku v oblasti/rozsahu rozliatia. Vzorec môžete vidieť v iných bunkách rozsahu úniku, ale budú sivé a nemožno ich aktualizovať.
Nedostatok pamäte
Ak spustíte vzorec rozliateho poľa, ktorý spôsobí nedostatok pamäte Excelu, môže to spustiť chybu #SPILL. Za týchto okolností skúste odkazovať na menšie pole alebo rozsah.
Nerozpoznané / záložné
Chybu preliatia môžete dostať aj vtedy, keď Excel nerozpozná alebo nedokáže zosúladiť príčinu chyby. V takýchto prípadoch dvakrát skontrolujte svoj vzorec a uistite sa, že všetky parametre funkcií sú správne.
Teraz poznáte všetky príčiny a riešenia #SPILL! chyby v Exceli 365.