Šis straipsnis padės suprasti visas #SPILL klaidų priežastis ir sprendimus, kaip jas ištaisyti programoje Excel 365.
#IŠSIILDYTI! yra naujos rūšies „Excel“ klaida, kuri dažniausiai įvyksta, kai formulė, kuri pateikia kelis skaičiavimo rezultatus, bando parodyti savo rezultatus išsiliejimo diapazone, tačiau tame diapazone jau yra kitų duomenų.
Blokuojami duomenys gali būti bet kas, įskaitant teksto reikšmę, sujungtus langelius, paprasto tarpo simbolį arba net tada, kai nėra pakankamai vietos rezultatams pateikti. Sprendimas paprastas: išvalykite bet kokių blokuojančių duomenų diapazoną arba pasirinkite tuščią langelių masyvą, kuriame nėra jokių duomenų.
Apskaičiuojant dinamines masyvo formules dažniausiai pasitaiko išsiliejimo klaida, nes dinaminė masyvo formulė išveda rezultatus į keletą langelių arba masyvą. Panagrinėkime išsamiau ir suprasime, kas sukelia šią klaidą programoje „Excel“ ir kaip ją išspręsti.
Kas sukelia išsiliejimo klaidą?
Nuo tada, kai 2018 m. buvo paleisti dinaminiai masyvai, „Excel“ formulės vienu metu gali apdoroti kelias reikšmes ir pateikti rezultatus daugiau nei viename langelyje. Dinaminiai masyvai yra keičiamo dydžio masyvai, leidžiantys formulėms grąžinti kelis rezultatus į darbalapio langelių diapazoną pagal formulę, įvestą viename langelyje.
Kai dinaminė masyvo formulė pateikia kelis rezultatus, šie rezultatai automatiškai patenka į gretimus langelius. Šis elgesys „Excel“ vadinamas „išsiliejimu“. O ląstelių diapazonas, į kurį patenka rezultatai, vadinamas „išsiliejimo diapazonu“. Išsiliejimo diapazonas išsiplės arba susitrauks automatiškai, atsižvelgiant į šaltinio vertes.
Jei formulė bando užpildyti išsiliejimo diapazoną keliais rezultatais, bet kažkas tame diapazone ją užblokuoja, įvyksta #SPILL klaida.
„Excel“ dabar turi 9 funkcijas, kurios naudoja dinaminio masyvo funkcijas problemoms spręsti, įskaitant:
- SEKA
- FILTRAS
- PERKENKTI
- RŪŠIUOTI
- RŪŠIUOTI PAGAL
- RANDARRAY
- UNIKALUS
- XLOOKUP
- XMATCH
Dinaminės masyvo formulės pasiekiamos tik „Excel 365“ ir šiuo metu jos nepalaiko jokia neprisijungus naudojama „Excel“ programinė įranga (pvz., „Microsoft Excel 2016“, 2019 m.).
Išsiliejimo klaidas sukelia ne tik trukdantys duomenims, bet yra keletas priežasčių, kodėl galite gauti #Spill error. Leiskite mums ištirti įvairias situacijas, kuriose galite susidurti su #Išliejimu! klaidas ir kaip jas ištaisyti.
Išsiliejimo diapazonas nėra tuščias
Viena iš pagrindinių išsiliejimo klaidų priežasčių yra ta, kad išsiliejimo diapazonas nėra tuščias. Pavyzdžiui, jei bandote parodyti 10 rezultatų, bet jei kurioje nors išsiliejimo srityje esančiame langelyje yra duomenų, formulė pateikia #IŠSIILDYTI! klaida.
1 pavyzdys:
Toliau pateiktame pavyzdyje įvedėme funkciją TRANSPOSE langelyje C2, kad vertikalų langelių diapazoną (B2:B5) paverstume horizontaliu diapazonu (C2:F2). Užuot perjungusi stulpelį į eilutę, „Excel“ mums rodo #SPILL! klaida.
Ir kai spustelėsite formulės langelį, pamatysite brūkšninę mėlyną kraštinę, nurodančią išsiliejimo sritį / diapazoną (C2:F2), kurio reikia rezultatams rodyti, kaip parodyta toliau. Taip pat pastebėsite geltoną įspėjamąjį ženklą su šauktuku.
Norėdami suprasti klaidos priežastį, spustelėkite šalia klaidos esančią įspėjimo piktogramą ir pirmoje eilutėje pamatysite pranešimą, paryškintą pilka spalva. Kaip matote, čia parašyta „Išsiliejimo diapazonas nėra tuščias“.
Problema ta, kad išsiliejimo diapazono D2 ir E2 langeliuose yra teksto simbolių (ne tuščių), taigi, klaida.
Sprendimas:
Sprendimas paprastas: arba išvalykite duomenis (perkelkite arba ištrinkite), esančius išsiliejimo diapazone, arba perkelkite formulę į kitą vietą, kur nėra kliūčių.
Kai tik ištrinsite arba perkelsite blokavimą, „Excel“ automatiškai užpildys langelius formulės rezultatais. Čia, kai išvalome tekstą D2 ir E2, formulė perkelia stulpelį į eilutę, kaip numatyta.
2 pavyzdys:
Toliau pateiktame pavyzdyje, nors išsiliejimo diapazonas atrodo tuščias, formulė vis tiek rodo išsiliejimą! klaida. Taip yra todėl, kad išsiliejimas iš tikrųjų nėra tuščias, jis turi nematomą erdvės pobūdį vienoje iš langelių.
Sunku rasti tarpo simbolius ar bet kokį kitą nematomą simbolį, besislepiantį tuščiuose langeliuose. Norėdami rasti tokias ląsteles su nepageidaujamais duomenimis, spustelėkite klaidą (įspėjamasis ženklas) ir meniu pasirinkite „Select Obstructing Cells“ ir pateksite į langelį, kuriame yra trukdantys duomenys.
Kaip matote, toliau esančioje ekrano kopijoje langelyje E2 yra du tarpo simboliai. Išvalę šiuos duomenis gausite tinkamą išvestį.
Kartais nematomas simbolis gali būti tekstas, suformatuotas ta pačia šrifto spalva, kaip ir langelio užpildymo spalva, arba langelio reikšmė, suformatuota naudojant skaičių kodą ;;;. Kai tinkinsite langelio reikšmę su ;;;, viskas tame langelyje bus paslėpta, neatsižvelgiant į šrifto ar langelio spalvą.
Išsiliejimo diapazone yra sujungtų langelių
Kartais #išsiliejimas! klaida įvyksta, kai išsiliejimo diapazone yra sujungtų langelių. Dinaminė masyvo formulė neveikia su sujungtais langeliais. Norėdami tai ištaisyti, tereikia panaikinti išsiliejimo diapazono langelių sujungimą arba perkelti formulę į kitą diapazoną, kuriame nėra sujungtų langelių.
Toliau pateiktame pavyzdyje, net jei išsiliejimo diapazonas yra tuščias (C2:CC8), formulė grąžina išsiliejimo klaidą. Taip yra todėl, kad ląstelės C4 ir C5 yra sujungtos.
Norėdami įsitikinti, kad klaidos priežastis yra sujungti langeliai, spustelėkiteįspėjamasis ženklas ir patikrinkite priežastį – „Išsiliejimo diapazonas sujungė langelį“.
Sprendimas:
Norėdami panaikinti langelių sujungimą, pasirinkite sujungtus langelius, tada skirtuke „Pagrindinis“ spustelėkite mygtuką „Sujungti ir centras“ ir pasirinkite „Unmerge Cells“.
Jei didelėje skaičiuoklėje sunku rasti sujungtus langelius, įspėjamojo ženklo meniu spustelėkite parinktį „Pasirinkti trukdančius langelius“, kad pereitumėte prie sujungtų langelių.
Išsiliejimo diapazonas lentelėje
Išsiliejusios masyvo formulės nepalaikomos „Excel“ lentelėse. Dinaminė masyvo formulė turėtų būti įvedama tik į vieną atskirą langelį. Jei įvesite išsiliejusio masyvo formulę į lentelę arba kai išsiliejimo sritis patenka į lentelę, gausite išsiliejimo klaidą. Kai taip atsitiks, pabandykite konvertuoti lentelę į įprastą diapazoną arba perkelkite formulę už lentelės ribų.
Pavyzdžiui, kai įvesime toliau pateiktą išsiliejusio diapazono formulę į Excel lentelę, gautume išsiliejimo klaidą kiekviename lentelės langelyje, o ne tik formulės langelyje. Taip yra todėl, kad „Excel“ automatiškai nukopijuoja bet kurią lentelėje įvestą formulę į kiekvieną lentelės stulpelio langelį.
Be to, gausite išsiliejimo klaidą, kai formulė bandys išlieti rezultatus lentelėje. Žemiau esančioje ekrano kopijoje išsiliejimo sritis patenka į esamą lentelę, todėl gauname išsiliejimo klaidą.
Norėdami patvirtinti šios klaidos priežastį, spustelėkite įspėjamąjį ženklą ir peržiūrėkite klaidos priežastį – „Išsiliejimo diapazonas lentelėje“
Sprendimas:
Norėdami ištaisyti klaidą, turėsite grąžinti "Excel" lentelę į diapazoną. Norėdami tai padaryti, dešiniuoju pelės mygtuku spustelėkite bet kurią lentelės vietą, spustelėkite „Lentelė“ ir pasirinkite parinktį „Konvertuoti į diapazoną“. Arba galite spustelėti kairiuoju pelės klavišu bet kurioje lentelės vietoje, tada eiti į skirtuką „Lentelės dizainas“ ir pasirinkti parinktį „Konvertuoti į diapazoną“.
Išsiliejimo diapazonas nežinomas
Jei „Excel“ nepavyko nustatyti išsiliejusio masyvo dydžio, ji suaktyvins išsiliejimo klaidą. Kartais formulė leidžia keisti dinaminio masyvo dydį tarp kiekvieno skaičiavimo veiksmo. Jei dinaminio masyvo dydis keičiasi atliekant skaičiavimus ir nesibalansuoja, tai sukels #IŠSILIJIMO! Klaida.
Šio tipo išsiliejimo klaida dažniausiai suveikia, kai naudojamos nepastovios funkcijos, pvz., RAND, RANDARRAY, RANDBETWEEN, OFFSET ir INDIRECT.
Pavyzdžiui, kai langelyje B3 naudojame toliau pateiktą formulę, gauname išsiliejimo klaidą:
=SEKA (RANDBWEEN(1, 500))Pavyzdyje funkcija RANDBETWEEN grąžina atsitiktinį sveikąjį skaičių nuo 1 iki 500, o jo išvestis nuolat kinta. O SEQUENCE funkcija nežino, kiek reikšmių sukurti išsiliejimo masyve. Vadinasi, #SPILL klaida.
Taip pat galite patvirtinti klaidos priežastį spustelėdami įspėjamąjį ženklą – „Išsiliejimo diapazonas nežinomas“.
Sprendimas:
Norėdami ištaisyti šios formulės klaidą, vienintelis pasirinkimas yra naudoti kitą skaičiavimo formulę.
Išsiliejimo diapazonas yra per didelis
Kartais galite vykdyti formulę, kuri išveda išsiliejusį diapazoną, kuris yra per didelis, kad būtų galima apdoroti darbalapyje, ir jis gali apimti už darbalapio kraštų. Kai tai atsitiks, galite gauti #SILL! klaida. Norėdami išspręsti šią problemą, galite pabandyti nurodyti konkretų diapazoną arba vieną langelį, o ne visus stulpelius arba naudoti simbolį @, kad įgalintumėte numanomą sankirtą
Toliau pateiktame pavyzdyje bandome apskaičiuoti 20 % pardavimo skaičių A stulpelyje ir pateikti rezultatus B stulpelyje, bet vietoj to gauname išsiliejimo klaidą.
B3 formulė apskaičiuoja 20% vertės A3, tada 20% vertės A4 ir pan. Jis pateikia daugiau nei milijoną rezultatų (1 048 576) ir visus juos išskleidžia B stulpelyje, pradedant nuo B3 langelio, tačiau jis pasieks darbalapio pabaigą. Nėra pakankamai vietos, kad būtų rodomi visi išėjimai, todėl gauname #SPILL klaidą.
Kaip matote, šios klaidos priežastis yra ta, kad – „Išsiliejimo diapazonas yra per didelis“.
Sprendimai:
Norėdami išspręsti šią problemą, pabandykite pakeisti visą stulpelį atitinkamu diapazonu arba vieno langelio nuoroda arba pridėkite @ operatorių, kad atliktumėte numanomą sankirtą.
Pataisyti 1: galite pabandyti nurodyti diapazonus, o ne visus stulpelius. Čia mes keičiame visą diapazoną A:A su A3:A11 formulėje ir formulė automatiškai užpildys diapazoną rezultatais.
2 pataisymas: Pakeiskite visą stulpelį tik langelio nuoroda toje pačioje eilutėje (A3), tada nukopijuokite formulę diapazonu, naudodami užpildymo rankenėlę.
3 pataisymas: Taip pat galite pabandyti pridėti @ operatorių prieš nuorodą, kad atliktumėte numanomą sankirtą. Išvestis bus rodoma tik formulės langelyje.
Tada nukopijuokite formulę iš langelio B3 į likusį diapazoną.
Pastaba: Kai redaguojate išsiliejusią formulę, galite redaguoti tik pirmą langelį išsiliejimo srityje / diapazone. Formulę galite matyti kituose išsiliejimo diapazono langeliuose, tačiau jie bus pilki ir jų negalima atnaujinti.
Nepakanka atminties
Jei vykdote išsiliejusią masyvo formulę, dėl kurios „Excel“ pritrūksta atminties, tai gali sukelti #SPILL klaidą. Tokiomis aplinkybėmis pabandykite nurodyti mažesnį masyvą arba diapazoną.
Neatpažintas / atsarginis
Taip pat galite gauti išsiliejimo klaidą net tada, kai „Excel“ neatpažįsta arba negali suderinti klaidos priežasties. Tokiais atvejais dar kartą patikrinkite formulę ir įsitikinkite, kad visi funkcijų parametrai yra teisingi.
Dabar jūs žinote visas #SPILL priežastis ir sprendimus! klaidų programoje „Excel 365“.