Tämä artikkeli auttaa sinua ymmärtämään kaikki #SPILL-virheiden syyt sekä ratkaisut niiden korjaamiseen Excel 365:ssä.
#LÄIKKYMINEN! on uudenlainen Excel-virhe, joka ilmenee pääasiassa silloin, kun useita laskentatuloksia tuottava kaava yrittää näyttää tulostensa vuotoalueella, mutta tämä alue sisältää jo jotain muuta dataa.
Estotiedot voivat olla mitä tahansa, mukaan lukien tekstiarvo, yhdistetyt solut, pelkkä välilyönti tai jopa silloin, kun tulosten palauttamiseen ei ole tarpeeksi tilaa. Ratkaisu on yksinkertainen, joko tyhjennä mahdollisten estotietojen alue tai valitse tyhjä solujoukko, joka ei sisällä minkäänlaista dataa.
Vuotovirhe tapahtuu yleensä laskettaessa dynaamisia taulukkokaavoja, koska dynaaminen taulukkokaava tulostaa tulokset useisiin soluihin tai taulukkoon. Katsotaanpa tarkemmin ja ymmärrämme, mikä laukaisee tämän virheen Excelissä ja kuinka se ratkaistaan.
Mikä aiheuttaa vuotovirheen?
Dynaamisten taulukoiden julkaisusta vuonna 2018 lähtien Excel-kaavat voivat käsitellä useita arvoja kerrallaan ja palauttaa tulokset useampaan kuin yhteen soluun. Dynaamiset taulukot ovat taulukoita, joiden kokoa voidaan muuttaa, ja joiden avulla kaavat voivat palauttaa useita tuloksia laskentataulukon solualueelle yhteen soluun syötetyn kaavan perusteella.
Kun dynaaminen taulukkokaava palauttaa useita tuloksia, nämä tulokset leviävät automaattisesti viereisiin soluihin. Tätä toimintaa kutsutaan Excelissä "roiskeeksi". Ja solualuetta, johon tulokset leviävät, kutsutaan "vuotoalueeksi". Vuotoalue laajenee tai supistuu automaattisesti lähdearvojen perusteella.
Jos kaava yrittää täyttää vuotoalueen useilla tuloksilla, mutta jokin tällä alueella estää sen, tapahtuu #SPILL-virhe.
Excelissä on nyt 9 funktiota, jotka käyttävät Dynamic Array -toimintoja ongelmien ratkaisemiseen, mukaan lukien:
- SEKVENSSI
- SUODATTAA
- SIIRTOA
- JÄRJESTELLÄ
- LAJITELLA JONKUN MUKAAN
- RANDARRAY
- AINUTLAATUINEN
- XHAKU
- XMATCH
Dynaamiset taulukkokaavat ovat saatavilla vain Excel 365:ssä, eikä mikään offline-Excel-ohjelmisto (eli Microsoft Excel 2016, 2019) tue sitä tällä hetkellä.
Vuotovirheet eivät johdu vain tietojen tukkimisesta, vaan #Spill errorin saamiseen voi olla useita syitä. Tutustutaan erilaisiin tilanteisiin, joissa saatat kohdata #SPILL! virheitä ja kuinka ne korjataan.
Vuotoalue ei ole tyhjä
Yksi tärkeimmistä vuotovirheiden syistä on se, että vuotoalue ei ole tyhjä. Jos esimerkiksi yrität näyttää 10 tulosta, mutta jos jossakin vuotoalueen solussa on tietoja, kaava palauttaa #SILL! virhe.
Esimerkki 1:
Alla olevassa esimerkissä olemme syöttäneet TRANSPOSE-funktion soluun C2 muuntaaksemme pystysuoran solualueen (B2:B5) vaakasuuntaiseksi alueeksi (C2:F2). Sen sijaan, että vaihtaisit sarakkeen riviksi, Excel näyttää meille #SPILL! virhe.
Ja kun napsautat kaavasolua, näet katkoviivan sinisen reunuksen, joka osoittaa vuotoalueen/alueen (C2:F2), jota tarvitaan tulosten näyttämiseen alla kuvatulla tavalla. Huomaat myös keltaisen varoitusmerkin, jossa on huutomerkki.
Ymmärtääksesi virheen syyn, napsauta virheen vieressä olevaa varoituskuvaketta ja näet ensimmäisen rivin viestin harmaalla korostettuna. Kuten näet, tässä lukee "Vuotoalue ei ole tyhjä".
Ongelma tässä on, että vuotoalueen D2 ja E2 soluissa on tekstimerkkejä (ei tyhjiä), joten virhe.
Ratkaisu:
Ratkaisu on yksinkertainen, joko tyhjennä vuotoalueella olevat tiedot (joko siirrä tai poista) tai siirrä kaava toiseen paikkaan, jossa ei ole esteitä.
Heti kun poistat tai siirrät tukoksen, Excel täyttää solut automaattisesti kaavan tuloksilla. Tässä, kun tyhjennämme tekstin D2:ssa ja E2:ssa, kaava transponoi sarakkeen riviksi tarkoitetulla tavalla.
Esimerkki 2:
Alla olevassa esimerkissä, vaikka vuotoalue näyttää tyhjältä, kaava näyttää silti vuodon! virhe. Se johtuu siitä, että vuoto ei ole itse asiassa tyhjä, sillä yhdessä solussa on näkymätön välilyönti.
On vaikea löytää välilyöntejä tai muita näkymättömiä merkkejä, jotka piiloutuvat tyhjiltä näyttäviin soluihin. Löytääksesi sellaiset solut, joissa on ei-toivottuja tietoja, napsauta Virhe kelluvaa (varoitusmerkki) ja valitse valikosta 'Valitse estävät solut'. Se vie sinut soluun, joka sisältää estävät tiedot.
Kuten näet, alla olevassa kuvakaappauksessa solussa E2 on kaksi välilyöntiä. Kun tyhjennät nämä tiedot, saat oikean tulosteen.
Joskus näkymätön merkki voi olla tekstiä, joka on muotoiltu samalla fontin värillä kuin solun täyttöväri, tai solun arvo, joka on muotoiltu numerokoodilla ;;;. Kun mukautat solun arvon ;;;:llä, se piilottaa kaiken kyseisessä solussa fontin väristä tai solun väristä riippumatta.
Vuotoalue sisältää yhdistettyjä soluja
Joskus #SPILL! virhe tapahtuu, kun vuotoalue sisältää yhdistetyt solut. Dynaaminen taulukkokaava ei toimi yhdistettyjen solujen kanssa. Korjaaksesi tämän, sinun tarvitsee vain poistaa vuotoalueen solujen yhdistäminen tai siirtää kaava toiseen alueeseen, jossa ei ole yhdistettyjä soluja.
Alla olevassa esimerkissä, vaikka vuotoalue on tyhjä (C2:CC8), kaava palauttaa vuotovirheen. Se johtuu siitä, että solut C4 ja C5 yhdistetään.
Varmista, että yhdistetyt solut ovat syy, miksi saat virheen, napsauttamallavaroitusmerkki ja tarkista syy – "Vuotoalue on yhdistetty soluun".
Ratkaisu:
Voit purkaa solujen yhdistämisen valitsemalla yhdistetyt solut ja napsauttamalla sitten Koti-välilehdellä Yhdistä ja keskitä -painiketta ja valitsemalla Unmerge Cells.
Jos sinulla on vaikeuksia paikantaa yhdistettyjä soluja suuresta laskentataulukosta, napsauta "Valitse estävät solut" -vaihtoehtoa varoitusmerkkivalikosta siirtyäksesi yhdistettyihin soluihin.
Vuotoalue taulukossa
Läikkyviä taulukkokaavoja ei tueta Excel-taulukoissa. Dynaamisen taulukon kaava tulisi syöttää vain yhteen yksittäiseen soluun. Jos syötät vuotaneen taulukon kaavan taulukkoon tai kun vuotoalue putoaa taulukkoon, saat Spill-virheen. Kun näin tapahtuu, yritä muuntaa taulukko normaaliksi alueeksi tai siirtää kaava taulukon ulkopuolelle.
Kun esimerkiksi kirjoitamme seuraavan vuodatetun alueen kaavan Excel-taulukkoon, saamme Spill-virheen jokaiseen taulukon soluun, ei vain kaavasoluun. Tämä johtuu siitä, että Excel kopioi automaattisesti minkä tahansa taulukkoon syötetyn kaavan jokaiseen taulukon sarakkeen soluun.
Saat myös vuotovirheilmoituksen, kun kaava yrittää kaataa tuloksia taulukkoon. Alla olevassa kuvakaappauksessa vuotoalue on olemassa olevan taulukon sisällä, joten saamme vuotovirheen.
Vahvista tämän virheen syy napsauttamalla varoitusmerkkiä ja katso virheen syy - "Vuotoalue taulukossa"
Ratkaisu:
Korjaaksesi virheen, sinun on palautettava Excel-taulukko takaisin alueelle. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella mitä tahansa taulukon kohtaa, napsauttamalla "Taulukko" ja valitsemalla sitten "Muunna alueeksi" -vaihtoehto. Vaihtoehtoisesti voit napsauttaa hiiren vasemmalla painikkeella mitä tahansa taulukossa, siirtyä sitten "Taulukon suunnittelu" -välilehteen ja valita "Muunna alueeksi" -vaihtoehdon.
Vuotoalue on tuntematon
Jos Excel ei pystynyt määrittämään vuotaneen taulukon kokoa, se laukaisee Spill-virheen. Joskus kaava mahdollistaa dynaamisen taulukon koon muuttamisen kunkin laskentakerran välillä. Jos dynaamisen taulukon koko muuttuu jatkuvasti laskutoimitusten aikana eikä tasapainotu, se aiheuttaa #SPILL! Virhe.
Tämän tyyppinen vuotovirhe laukeaa yleensä käytettäessä haihtuvia toimintoja, kuten RAND-, RANDARRAY-, RANDBETWEEN-, OFFSET- ja DISIRECT-toimintoja.
Esimerkiksi kun käytämme alla olevaa kaavaa solussa B3, saamme Spill-virheen:
=JÄRJESTELMÄ(RANDBETWEEN(1, 500))Esimerkissä RANDBETWEEN-funktio palauttaa satunnaisen kokonaisluvun numeroiden 1 ja 500 väliltä, ja sen tulos muuttuu jatkuvasti. Ja SEQUENCE-funktio ei tiedä kuinka monta arvoa tulee tuottaa vuototaulukossa. Tästä syystä #SPILL-virhe.
Voit myös vahvistaa virheen syyn napsauttamalla Varoitusmerkkiä - "Vuotoalue on tuntematon".
Ratkaisu:
Tämän kaavan virheen korjaamiseksi ainoa vaihtoehto on käyttää eri kaavaa laskentaan.
Vuotoalue on liian suuri
Toisinaan voit suorittaa kaavan, joka tulostaa vuotaneen alueen, joka on liian suuri laskentataulukon käsittelemiseksi, ja se voi ulottua laskentataulukon reunojen ulkopuolelle. Kun näin tapahtuu, saatat saada #SPILL! virhe. Voit korjata tämän ongelman viittaamalla tiettyyn alueeseen tai yhteen soluun kokonaisten sarakkeiden sijaan tai käyttämällä @-merkkiä implisiittisen leikkauksen käyttöön ottamiseksi.
Alla olevassa esimerkissä yritämme laskea 20 % sarakkeen A myyntiluvuista ja palauttaa tulokset sarakkeessa B, mutta sen sijaan saamme vuotovirheen.
B3:n kaava laskee 20 % A3:n arvosta, sitten 20 % A4:n arvosta ja niin edelleen. Se tuottaa yli miljoona tulosta (1 048 576) ja levittää ne kaikki sarakkeeseen B alkaen solusta B3, mutta se saavuttaa laskentataulukon loppuun. Tila ei riitä kaikkien tulosteiden näyttämiseen, minkä seurauksena saamme #SPILL-virheen.
Kuten näet, syy tähän virheeseen on se, että - "Vuotoalue on liian suuri".
Ratkaisut:
Voit ratkaista tämän ongelman muuttamalla koko sarakkeen asianmukaisella alueella tai yksisoluisella viittauksella tai lisäämällä @-operaattorin suorittamaan implisiittisen leikkauspisteen.
Korjaa 1: Voit yrittää viitata alueisiin kokonaisten sarakkeiden sijaan. Täällä muutamme koko alueen A:A kaavan A3:A11:llä, ja kaava täyttää automaattisesti alueen tuloksilla.
Korjaus 2: Korvaa koko sarake vain soluviittauksella samalla rivillä (A3) ja kopioi sitten kaava alaspäin aluetta käyttämällä täyttökahvaa.
Korjaus 3: Voit myös yrittää lisätä @-operaattorin ennen viittausta suorittaaksesi implisiittisen leikkauksen. Tämä näyttää tulosteen vain kaavasolussa.
Kopioi sitten kaava solusta B3 alueen loppuosaan.
merkintä: Kun muokkaat vuotanutta kaavaa, voit muokata vain vuotoalueen/alueen ensimmäistä solua. Näet kaavan muissa vuotoalueen soluissa, mutta ne näkyvät harmaina, eikä niitä voi päivittää.
Muisti loppu
Jos suoritat vuotaneen taulukkokaavan, joka aiheuttaa Excelin muistin loppumisen, se voi laukaista #SPILL-virheen. Kokeile näissä olosuhteissa viitata pienempään taulukkoon tai alueeseen.
Tuntematon / vara
Voit myös saada Spill-virheen, vaikka Excel ei tunnista tai pysty sovittamaan virheen syytä. Tällaisissa tapauksissa tarkista kaavasi ja varmista, että kaikki funktioiden parametrit ovat oikein.
Nyt tiedät kaikki syyt ja ratkaisut #SPILLiin! virheitä Excel 365:ssä.