Ta članek vam bo pomagal razumeti vse vzroke za napake #SPILL ter rešitve za njihovo odpravljanje v Excelu 365.
#RAZLI! je nova vrsta Excelove napake, ki se v glavnem pojavi, ko formula, ki ustvari več rezultatov izračuna, poskuša prikazati svoje rezultate v območju razlitja, vendar ta obseg že vsebuje nekatere druge podatke.
Podatki o blokiranju so lahko karkoli, vključno z besedilno vrednostjo, združenimi celicami, navadnim presledkom ali celo, ko ni dovolj prostora za vrnitev rezultatov. Rešitev je preprosta, bodisi počistite obseg vseh blokirajočih podatkov ali izberite prazen niz celic, ki v njem ne vsebujejo nobene vrste podatkov.
Napaka pri razlitju se običajno zgodi pri izračunu formul dinamičnega niza, ker je formula dinamičnega niza tista, ki izpiše rezultate v več celic ali matriko. Oglejmo si podrobneje in razumemo, kaj sproži to napako v Excelu in kako jo odpraviti.
Kaj povzroča napako pri razlitju?
Od začetka dinamičnih nizov leta 2018 lahko Excelove formule obravnavajo več vrednosti hkrati in vrnejo rezultate v več kot eni celici. Dinamični nizi so nizi, ki jih je mogoče spremeniti, ki omogočajo formulam, da vrnejo več rezultatov v obseg celic na delovnem listu na podlagi formule, vnesene v eno celico.
Ko formula dinamičnega niza vrne več rezultatov, se ti rezultati samodejno prelijejo v sosednje celice. To vedenje se v Excelu imenuje "razlitje". Obseg celic, v katerega se razlijejo rezultati, se imenuje "obseg razlitja". Obseg razlitja se bo samodejno razširil ali skrčil glede na izvorne vrednosti.
Če formula poskuša zapolniti obseg razlitja z več rezultati, vendar jo nekaj v tem obsegu blokira, pride do napake #SPILL.
Excel ima zdaj 9 funkcij, ki uporabljajo funkcijo dinamičnega niza za reševanje težav, med njimi:
- ZAPOREDJE
- FILTER
- PRENOS
- RAZVRŠI
- RAZVRSTI PO
- RANDARRAY
- EDINSTVEN
- XLOOKUP
- XMATCH
Formule dinamičnega niza so na voljo samo v »Excel 365« in jih trenutno ne podpira nobena programska oprema Excel brez povezave (tj. Microsoft Excel 2016, 2019).
Napake pri razlitju niso samo posledica oviranja podatkov, obstaja več razlogov, zakaj lahko dobite napako #Spill. Dovolite nam, da raziščemo različne situacije, v katerih bi lahko naleteli na #SPILL! napake in kako jih odpraviti.
Obseg razlitja ni prazen
Eden od glavnih vzrokov za napako pri razlitju je, da območje razlitja ni prazno. Na primer, če poskušate prikazati 10 rezultatov, vendar če so v kateri koli celici na območju razlitja podatki, formula vrne #SPILL! napaka.
Primer 1:
V spodnjem primeru smo v celico C2 vnesli funkcijo TRANSPOSE, da pretvorimo navpični obseg celic (B2:B5) v vodoravni obseg (C2:F2). Namesto da bi stolpec preklopil v vrstico, nam Excel pokaže #SPILL! napaka.
Ko kliknete celico formule, boste videli črtkano modro obrobo, ki označuje območje/obseg razlitja (C2:F2), ki je potreben za prikaz rezultatov, kot je prikazano spodaj. Prav tako boste opazili rumen opozorilni znak s klicajem.
Če želite razumeti razlog za napako, kliknite ikono opozorila poleg napake in si oglejte sporočilo v prvi vrstici, označeno s sivo. Kot lahko vidite, tukaj piše »Obseg razlitja ni prazen«.
Težava je v tem, da imajo celice v območju razlitja D2 in E2 besedilne znake (niso prazne), zato je napaka.
rešitev:
Rešitev je preprosta, bodisi počistite podatke (premaknite ali izbrišite), ki se nahajajo v območju razlitja, ali premaknite formulo na drugo mesto, kjer ni ovir.
Takoj, ko izbrišete ali premaknete blokado, bo Excel samodejno zapolnil celice z rezultati formule. Tukaj, ko počistimo besedilo v D2 in E2, formula prestavi stolpec v vrstico, kot je predvideno.
2. primer:
V spodnjem primeru, čeprav je obseg razlitja videti prazen, formula še vedno prikazuje razlitje! napaka. Ker razlitje dejansko ni prazno, ima v eni od celic neviden prostorski značaj.
Težko je najti presledke ali kateri koli drug neviden znak, ki se skriva v nekaj, kar se zdi, da so prazne celice. Če želite poiskati takšne celice z nezaželenimi podatki, kliknite lebdeče napako (opozorilni znak) in v meniju izberite »Izberi ovirajoče celice« in popeljal vas bo do celice, ki vsebuje ovirajoče podatke.
Kot lahko vidite, ima na spodnjem posnetku zaslona celica E2 dva presledka. Ko počistite te podatke, boste dobili ustrezen izhod.
Včasih je lahko nevidni znak besedilo, oblikovano z isto barvo pisave kot barva polnila celice, ali vrednost celice, oblikovana po meri s številsko kodo ;;;. Ko vrednost celice oblikujete po meri z ;;;, bo skrila karkoli v tej celici, ne glede na barvo pisave ali barvo celice.
Obseg razlitja vsebuje združene celice
Včasih, #SPILL! do napake pride, ko obseg razlitja vsebuje združene celice. Formula dinamičnega niza ne deluje z združenimi celicami. Če želite to popraviti, morate samo prekiniti spajanje celic v območju razlitja ali premakniti formulo v drug obseg, ki nima združenih celic.
V spodnjem primeru, čeprav je obseg razlitja prazen (C2:CC8), formula vrne napako pri razlitju. To je zato, ker sta celici C4 in C5 združeni.
Če želite zagotoviti, da so združene celice razlog za prikaz napake, kliknite naopozorilni znak in preverite vzrok – »Obseg razlitja je združil celico«.
rešitev:
Če želite prekiniti spajanje celic, izberite združene celice, nato na zavihku »Domov« kliknite gumb »Spoji in sredi« in izberite »Razdruži celice«.
Če imate težave pri iskanju združenih celic v veliki preglednici, kliknite možnost »Izberi ovirajoče celice« v meniju z opozorilnim znakom, da skočite na združene celice.
Razpon razlitja v tabeli
Formule razlitih matrik niso podprte v Excelovih tabelah. Formulo dinamičnega niza je treba vnesti samo v eno posamezno celico. Če v tabelo vnesete formulo razlitega polja ali ko območje razlitja pade v tabelo, bi dobili napako pri razlitju. Ko se to zgodi, poskusite pretvoriti tabelo v običajni obseg ali premaknite formulo izven tabele.
Na primer, ko v Excelovo tabelo vnesemo naslednjo formulo razlitega obsega, bi dobili napako prelivanja v vsaki celici tabele, ne samo v celici formule. To je zato, ker Excel samodejno kopira katero koli formulo, vneseno v tabelo, v vsako celico v stolpcu tabele.
Prav tako boste dobili napako pri razlitju, ko formula poskuša razliti rezultate v tabeli. Na spodnjem posnetku zaslona območje razlitja spada v obstoječo tabelo, zato dobimo napako pri razlitju.
Če želite potrditi vzrok za to napako, kliknite opozorilni znak in si oglejte razlog za napako – »Obseg razlitja v tabeli«
rešitev:
Če želite odpraviti napako, boste morali Excelovo tabelo vrniti nazaj v obseg. Če želite to narediti, z desno tipko miške kliknite kjer koli v tabeli, kliknite »Tabela« in nato izberite možnost »Pretvori v obseg«. Lahko pa tudi kliknete z levo tipko miške kjer koli v tabeli, nato pojdite na zavihek »Načrt tabele« in izberete možnost »Pretvori v obseg«.
Obseg razlitja je neznan
Če Excel ni mogel določiti velikosti razlitega niza, bo sprožil napako pri razlitju. Včasih formula omogoča spreminjanje velikosti dinamičnega niza med vsakim izračunom. Če se velikost dinamičnega niza med potekom izračunov še naprej spreminja in se ne uravnoteži, bo to povzročilo #SPILL! Napaka.
Ta vrsta napake pri razlitju se običajno sproži pri uporabi hlapnih funkcij, kot so funkcije RAND, RANDARRAY, RANDBETWEEN, OFFSET in INDIRECT.
Na primer, ko v celici B3 uporabimo spodnjo formulo, dobimo napako pri razlitju:
=SEQUENCE(RANDBETMED(1, 500))V primeru funkcija RANDBETWEEN vrne naključno celo število med številkama 1 in 500, njen izhod pa se nenehno spreminja. In funkcija SEQUENCE ne ve, koliko vrednosti naj proizvede v nizu razlitja. Zato je napaka #SPILL.
Vzrok za napako lahko potrdite tudi s klikom na Opozorilni znak – ‘Obseg razlitja ni znan’.
rešitev:
Če želite odpraviti napako za to formulo, je vaša edina izbira, da za izračun uporabite drugo formulo.
Obseg razlitja je prevelik
Včasih lahko izvedete formulo, ki izpiše razlito območje, ki je preveliko za obdelavo delovnega lista, in lahko sega čez robove delovnega lista. Ko se to zgodi, boste morda prejeli #SPILL! napaka. Če želite odpraviti to težavo, se lahko poskusite sklicevati na določen obseg ali eno celico namesto na celotne stolpce ali uporabite znak '@', da omogočite implicitno presečišče
V spodnjem primeru poskušamo izračunati 20 % prodajnih številk v stolpcu A in vrniti rezultate v stolpcu B, vendar namesto tega dobimo napako pri razlitju.
Formula v B3 izračuna 20 % vrednosti v A3, nato 20 % vrednosti v A4 itd. Ustvari več kot milijon rezultatov (1.048.576) in jih vse razlije v stolpec B, ki se začne v celici B3, vendar bo dosegel konec delovnega lista. Ni dovolj prostora za prikaz vseh izhodov, zato dobimo napako #SPILL.
Kot lahko vidite, je vzrok za to napako ta, da je – »Obseg razlitja prevelik«.
rešitve:
Če želite rešiti to težavo, poskusite spremeniti celoten stolpec z ustreznim obsegom ali sklicem na eno celico ali dodajte operator @ za izvedbo implicitnega preseka.
Popravi 1: Lahko poskusite napotiti obsege in ne celotne stolpce. Tukaj spremenimo celoten obseg A:A z A3:A11 v formuli in formula bo samodejno zapolnila obseg z rezultati.
Popravek 2: Zamenjajte celoten stolpec samo s sklicevanjem na celico v isti vrstici (A3) in nato s ročico za polnjenje kopirajte formulo navzdol po obsegu.
Popravek 3: Poskusite lahko tudi dodati operator @ pred sklic, da izvedete implicitno presečišče. To bo prikazalo izhod samo v celici formule.
Nato kopirajte formulo iz celice B3 v preostali del obsega.
Opomba: Ko urejate razlito formulo, lahko urejate samo prvo celico v območju/območju razlitja. Formulo lahko vidite v drugih celicah obsega razlitja, vendar bodo zatemnjene in jih ni mogoče posodobiti.
Zmanjkalo je spomina
Če izvedete razlito formulo matrike, zaradi katere Excelu zmanjka pomnilnika, lahko to sproži napako #SPILL. V teh okoliščinah se poskusite sklicevati na manjši niz ali obseg.
Neprepoznano / Nadomestni
Prav tako lahko dobite napako pri razlitju, tudi če Excel ne prepozna ali ne more uskladiti vzroka za napako. V takih primerih še enkrat preverite formulo in se prepričajte, da so vsi parametri funkcij pravilni.
Zdaj poznate vse vzroke in rešitve za #SPILL! napake v Excelu 365.