See artikkel aitab teil mõista kõiki #SPILL-tõrgete põhjuseid ja lahendusi nende parandamiseks rakenduses Excel 365.
#SPILL! on uut tüüpi Exceli tõrge, mis ilmneb peamiselt siis, kui valem, mis toodab mitu arvutustulemust, üritab kuvada oma väljundeid spillide vahemikus, kuid see vahemik sisaldab juba muid andmeid.
Blokeerivad andmed võivad olla mis tahes, sealhulgas tekstiväärtus, ühendatud lahtrid, tühik või isegi siis, kui tulemuste tagastamiseks pole piisavalt kohta. Lahendus on lihtne, kas tühjendage blokeerivate andmete vahemik või valige tühi lahtrite massiiv, mis ei sisalda selles ühtegi tüüpi andmeid.
Dünaamiliste massiivivalemite arvutamisel ilmneb tavaliselt tõrge, kuna dünaamilise massiivi valem väljastab tulemused mitmesse lahtrisse või massiivi. Vaatame üksikasjalikumalt ja mõistame, mis käivitab selle vea Excelis ja kuidas seda lahendada.
Mis põhjustab lekkeviga?
Alates dünaamiliste massiivide käivitamisest 2018. aastal saavad Exceli valemid käsitleda mitut väärtust korraga ja tagastada tulemused rohkem kui ühes lahtris. Dünaamilised massiivid on muudetava suurusega massiivid, mis võimaldavad valemitel tagastada töölehe lahtrivahemikku mitu tulemust ühte lahtrisse sisestatud valemi alusel.
Kui dünaamilise massiivi valem tagastab mitu tulemust, kanduvad need tulemused automaatselt naaberlahtritesse. Seda käitumist nimetatakse Excelis "spilliks". Ja lahtrite vahemikku, kuhu tulemused kanduvad, nimetatakse "lekkevahemikuks". Lekkevahemik laieneb või kahaneb automaatselt lähteväärtuste alusel.
Kui valem üritab täita lekkevahemikku mitme tulemusega, kuid miski selles vahemikus blokeerib, ilmneb tõrge #SPILL.
Excelis on nüüd 9 funktsiooni, mis kasutavad probleemide lahendamiseks dünaamilise massiivi funktsioone, sealhulgas:
- JÄRJESTUS
- FILTER
- ÜLEKANDE
- SORTI
- SORTEERIMA
- RANDARRAY
- UNIKAALNE
- XLOOKUP
- XMATCH
Dünaamilised massiivivalemid on saadaval ainult rakenduses Excel 365 ja seda ei toeta praegu ükski võrguühenduseta Exceli tarkvara (nt Microsoft Excel 2016, 2019).
Lekkevigu ei põhjusta mitte ainult andmete takistamine, vaid võib olla mitu põhjust, miks #Spill error kuvatakse. Uurime erinevaid olukordi, kus võite #LEKKUSEGA kokku puutuda! viga ja kuidas neid parandada.
Lekkevahemik ei ole tühi
Üks lekkevea peamisi põhjuseid on see, et lekkepiirkond ei ole tühi. Näiteks kui proovite kuvada 10 tulemust, kuid kui lekeala mõnes lahtris on andmeid, tagastab valem #SPILL! viga.
Näide 1:
Allolevas näites oleme sisestanud lahtrisse C2 funktsiooni TRANSPOSE, et teisendada lahtrite vertikaalne vahemik (B2:B5) horisontaalseks vahemikuks (C2:F2). Selle asemel, et veerg reaks vahetada, näitab Excel meile #SPILL! viga.
Ja kui klõpsate valemilahtril, näete katkend-sinist äärist, mis näitab lekkeala/vahemikku (C2:F2), mis on vajalik tulemuste kuvamiseks, nagu allpool näidatud. Samuti märkate kollast hoiatusmärki koos hüüumärgiga.
Vea põhjuse mõistmiseks klõpsake vea kõrval oleval hoiatusikoonil ja vaadake teadet esimesel real, mis on halliga esile tõstetud. Nagu näete, on siin kirjas "Lekkevahemik ei ole tühi".
Probleem on selles, et lahtrites spillide vahemikus D2 ja E2 on tekstimärgid (mitte tühjad), seega viga.
Lahendus:
Lahendus on lihtne: kas tühjendage lekkepiirkonnas asuvad andmed (kas teisaldage või kustutage) või viige valem teise kohta, kus pole takistusi.
Niipea, kui ummistuse kustutate või teisaldate, täidab Excel lahtrid automaatselt valemi tulemustega. Siin, kui kustutame teksti D2 ja E2, muudab valem veeru reaks, nagu ette nähtud.
Näide 2:
Kuigi allolevas näites näib lekkevahemik tühi, näitab valem ikkagi lekke! viga. Selle põhjuseks on asjaolu, et leke ei ole tegelikult tühi, sellel on ühes lahtris nähtamatu tühik.
Tühjades lahtrites peituvaid tühikumärke või muid nähtamatuid märke on raske leida. Selliste soovimatute andmetega lahtrite leidmiseks klõpsake veamärki (hoiatusmärk) ja valige menüüst „Select Obstructing Cells“ ning see viib teid lahtrisse, mis sisaldab takistavaid andmeid.
Nagu näete, on alloleval ekraanipildil lahtris E2 kaks tühikumärki. Kui need andmed kustutate, saate õige väljundi.
Mõnikord võib nähtamatu märk olla tekst, mis on vormindatud sama fondivärviga kui lahtri täitevärv, või lahtri väärtus, mis on kohandatud numbrikoodiga ;;;. Kui kohandate lahtri väärtuse vormindamist ;;;-ga, peidab see lahtri kõik, olenemata fondi värvist või lahtri värvist.
Lekkevahemik sisaldab ühendatud lahtreid
Mõnikord võib #SPILL! viga ilmneb siis, kui lekkevahemik sisaldab ühendatud lahtreid. Dünaamilise massiivi valem ei tööta ühendatud lahtritega. Selle parandamiseks peate vaid tühistama lahtrite ühendamise eraldusvahemikus või teisaldama valemi teise vahemikku, kus pole liidetud lahtreid.
Allolevas näites, kuigi lekkevahemik on tühi (C2:CC8), tagastab valem lekkevea. Põhjus on selles, et lahtrid C4 ja C5 on ühendatud.
Veendumaks, et ühendatud lahtrid on vea põhjus, klõpsake nuppuhoiatusmärk ja kontrollige põhjust – „Lekkevahemik on ühendatud lahtriga”.
Lahendus:
Lahtrite ühendamiseks valige liidetud lahtrid, seejärel klõpsake vahekaardil "Kodu" nuppu "Ühenda ja keskel" ja valige "Unmerge Cells".
Kui teil on suurel arvutustabelis raskusi liidetud lahtrite leidmisega, klõpsake ühendatud lahtrite juurde liikumiseks hoiatusmärgi menüüs valikut „Vali takistavad lahtrid”.
Lekkevahemik tabelis
Mahavalgunud massiivi valemeid Exceli tabelites ei toetata. Dünaamilise massiivi valem tuleks sisestada ainult ühte üksikusse lahtrisse. Kui sisestate tabelisse mahavalgunud massiivi valemi või kui lekkeala langeb tabelisse, kuvatakse tõrketeade. Kui see juhtub, proovige tabel teisendada tavavahemikku või teisaldage valem tabelist välja.
Näiteks kui sisestame Exceli tabelisse järgmise väljalastud vahemiku valemi, saame tõrketeate tabeli igas lahtris, mitte ainult valemilahtris. Selle põhjuseks on asjaolu, et Excel kopeerib tabelisse sisestatud valemi automaatselt tabeli veeru igasse lahtrisse.
Samuti saate lekkevea, kui valem proovib tulemusi tabelisse üle kanda. Alloleval ekraanipildil jääb lekkeala olemasolevasse tabelisse, seega saame lekkevea.
Vea põhjuse kinnitamiseks klõpsake hoiatussilti ja vaadake tõrke põhjust – "Lekkevahemik tabelis"
Lahendus:
Vea parandamiseks peate Exceli tabeli vahemikku tagasi viima. Selleks paremklõpsake tabelis suvalises kohas, klõpsake nuppu "Tabel" ja seejärel valige suvand "Teisenda vahemikku". Teise võimalusena saate tabelis suvalises kohas vasakklõpsata, seejärel minna vahekaardile „Tabeli kujundus” ja valida suvandi „Teisenda vahemikku”.
Lekkepiirkond on teadmata
Kui Excel ei suutnud väljavalgunud massiivi suurust määrata, käivitab see tõrketeate. Mõnikord võimaldab valem dünaamilise massiivi suurust iga arvutuskäigu vahel muuta. Kui dünaamilise massiivi suurus muutub arvutuste tegemise ajal ja see ei tasakaalusta, põhjustab see #SPILL! Viga.
Seda tüüpi lekketõrge käivitatakse tavaliselt lenduvate funktsioonide (nt RAND, RANDARRAY, RANDBETWEEN, OFFSET ja INDIRECT) kasutamisel.
Näiteks kui kasutame lahtris B3 allolevat valemit, saame tõrketeate Spill:
=JÄRJUS(RANDBETWEEN(1, 500))Näites tagastab funktsioon RANDBETWEEN juhusliku täisarvu numbrite 1 ja 500 vahel ja selle väljund muutub pidevalt. Ja funktsioon SEQUENCE ei tea, kui palju väärtusi tuleks levimassiivis luua. Seega viga #SPILL.
Vea põhjust saate ka kinnitada, klõpsates hoiatussilti – ‘Lekkevahemik on teadmata’.
Lahendus:
Selle valemi vea parandamiseks on teie ainus valik kasutada arvutamisel teist valemit.
Lekkevahemik on liiga suur
Mõnikord võite käivitada valemi, mis väljastab väljalangenud vahemiku, mis on töölehe käsitlemiseks liiga suur, ja see võib ulatuda töölehe servadest kaugemale. Kui see juhtub, võite saada #SPILL! viga. Selle probleemi lahendamiseks võite proovida tervete veergude asemel viidata kindlale vahemikule või ühele lahtrile või kasutada kaudse ristumise lubamiseks märki @
Allolevas näites proovime arvutada 20% veerus A olevatest müüginumbritest ja tagastada tulemused veerus B, kuid selle asemel saame veateate.
B3-s olev valem arvutab 20% väärtusest A3-s, seejärel 20% väärtusest A4-s ja nii edasi. See annab üle miljoni tulemuse (1 048 576) ja lisab need kõik veergu B alates lahtrist B3, kuid jõuab töölehe lõppu. Kõigi väljundite kuvamiseks pole piisavalt ruumi, selle tulemusena saame veateate #SPILL.
Nagu näete, on selle vea põhjuseks see, et – „Lekkevahemik on liiga suur”.
Lahendused:
Selle probleemi lahendamiseks proovige muuta kogu veergu asjakohase vahemiku või ühest lahtrist koosneva viitega või lisage kaudse ristumiskoha tegemiseks operaator @.
Parandage 1: võite proovida viidata vahemikele, mitte tervetele veergudele. Siin muudame kogu vahemiku A:A valemis A3:A11-ga ja valem täidab vahemiku automaatselt tulemustega.
Parandus 2: Asendage kogu veerg ainult lahtri viitega samal real (A3) ja seejärel kopeerige valem täitepideme abil vahemikku allapoole.
Parandus 3: Samuti võite proovida lisada enne viidet operaatori @, et teostada kaudset ristumist. See kuvab väljundi ainult valemilahtris.
Seejärel kopeerige valem lahtrist B3 ülejäänud vahemikku.
Märge: Kui redigeerite mahavalgunud valemit, saate redigeerida ainult lekkeala/vahemiku esimest lahtrit. Valemit näete ka teistes lekkevahemiku lahtrites, kuid need on hallid ja neid ei saa värskendada.
Mälu täis
Kui käivitate mahavalgunud massiivivalemi, mis põhjustab Exceli mälu tühjenemise, võib see käivitada tõrke #SPILL. Sel juhul proovige viidata väiksemale massiivile või vahemikule.
Tundmatu / tagavara
Samuti võite saada tõrketeate isegi siis, kui Excel ei tuvasta või ei suuda tõrke põhjust ühitada. Sellistel juhtudel kontrollige oma valemit ja veenduge, et funktsioonide kõik parameetrid on õiged.
Nüüd teate kõiki #SPILLi põhjuseid ja lahendusi! vead Excel 365-s.