Šis raksts palīdzēs izprast visus #SPILL kļūdu cēloņus, kā arī risinājumus to novēršanai programmā Excel 365.
#IZLIET! ir jauna veida Excel kļūda, kas galvenokārt rodas, ja formula, kas rada vairākus aprēķinu rezultātus, mēģina parādīt savus rezultātus noplūdes diapazonā, bet šajā diapazonā jau ir daži citi dati.
Bloķējošie dati var būt jebkas, tostarp teksta vērtība, sapludinātas šūnas, vienkārša atstarpes rakstzīme vai pat tad, ja nav pietiekami daudz vietas, lai atgrieztu rezultātus. Risinājums ir vienkāršs — notīriet bloķējošo datu diapazonu vai atlasiet tukšu šūnu masīvu, kurā nav nekāda veida datu.
Izplūdes kļūda parasti rodas, aprēķinot dinamiskās masīva formulas, jo dinamiskā masīva formula ir tā, kas izvada rezultātus vairākās šūnās vai masīvā. Apskatīsim sīkāk un sapratīsim, kas izraisa šo kļūdu programmā Excel un kā to novērst.
Kas izraisa noplūdes kļūdu?
Kopš dinamisko masīvu palaišanas 2018. gadā Excel formulas var apstrādāt vairākas vērtības vienlaikus un atgriezt rezultātus vairāk nekā vienā šūnā. Dinamiskie masīvi ir maināmi masīvi, kas ļauj formulām atgriezt vairākus rezultātus darblapas šūnu diapazonā, pamatojoties uz formulu, kas ievadīta vienā šūnā.
Kad dinamiskā masīva formula atgriež vairākus rezultātus, šie rezultāti automātiski nonāk blakus esošajās šūnās. Programmā Excel šī darbība tiek saukta par “izliešanu”. Šūnu diapazons, kurā izplūst rezultāti, tiek saukts par “izplūdes diapazonu”. Noplūdes diapazons paplašināsies vai saruks automātiski, pamatojoties uz avota vērtībām.
Ja formula mēģina aizpildīt noplūdes diapazonu ar vairākiem rezultātiem, bet kaut kas to bloķē šajā diapazonā, rodas kļūda #SPILL.
Programmā Excel tagad ir 9 funkcijas, kas problēmu risināšanai izmanto dinamisko masīvu funkcionalitāti, tostarp:
- SECĪBA
- FILTRA
- TRANSPOZĒT
- KĀRTOT
- KĀRTOT PĒC
- RANDARRAY
- UNIKĀLS
- XLOOKUP
- XMATCH
Dinamiskā masīva formulas ir pieejamas tikai programmā Excel 365, un pašlaik to neatbalsta neviena bezsaistes Excel programmatūra (t.i., Microsoft Excel 2016, 2019).
Noplūdes kļūdas rodas ne tikai datu aizsprostošanās dēļ, ir vairāki iemesli, kāpēc var tikt parādīta #Spill error. Ļaujiet mums izpētīt dažādas situācijas, kurās jūs varētu saskarties ar #IZLĪDZI! kļūdas un kā tās novērst.
Noplūdes diapazons nav tukšs
Viens no galvenajiem noplūdes kļūdu iemesliem ir tas, ka noplūdes diapazons nav tukšs. Piemēram, ja mēģināt parādīt 10 rezultātus, bet, ja kādā no noplūdes apgabala šūnām ir dati, formula atgriež #SPILL! kļūda.
1. piemērs:
Tālāk esošajā piemērā šūnā C2 esam ievadījuši funkciju TRANSPOSE, lai pārveidotu šūnu vertikālo diapazonu (B2:B5) horizontālā diapazonā (C2:F2). Tā vietā, lai pārslēgtu kolonnu uz rindu, programma Excel parāda mums #SPILL! kļūda.
Un, noklikšķinot uz formulas šūnas, jūs redzēsiet svītrotu zilu apmali, kas norāda noplūdes apgabalu/diapazonu (C2:F2), kas nepieciešams, lai parādītu rezultātus, kā parādīts tālāk. Turklāt jūs pamanīsit dzeltenu brīdinājuma zīmi ar izsaukuma zīmi.
Lai saprastu kļūdas iemeslu, noklikšķiniet uz brīdinājuma ikonas blakus kļūdai un skatiet ziņojumu pirmajā rindā, kas iezīmēta pelēkā krāsā. Kā redzat, šeit ir rakstīts: “Izplūdes diapazons nav tukšs”.
Problēma ir tāda, ka šūnās noplūdes diapazonā D2 un E2 ir teksta rakstzīmes (nav tukšas), tāpēc rodas kļūda.
Risinājums:
Risinājums ir vienkāršs — vai nu notīriet datus (pārvietojiet vai dzēsiet), kas atrodas noplūdes diapazonā, vai pārvietojiet formulu uz citu vietu, kur nav šķēršļu.
Tiklīdz izdzēsīsit vai pārvietosiet aizsprostojumu, programma Excel automātiski aizpildīs šūnas ar formulas rezultātiem. Šeit, kad mēs notīrām tekstu D2 un E2, formula pārceļ kolonnu rindā, kā paredzēts.
2. piemērs:
Tālāk esošajā piemērā, lai gan noplūdes diapazons šķiet tukšs, formula joprojām parāda noplūdi! kļūda. Tas ir tāpēc, ka noplūde faktiski nav tukša, tai vienā no šūnām ir neredzams atstarpes raksturs.
Ir grūti atrast atstarpes rakstzīmes vai jebkuru citu neredzamu rakstzīmi, kas slēpjas šķietami tukšās šūnās. Lai atrastu šādas šūnas ar nevēlamiem datiem, noklikšķiniet uz Error floatie (brīdinājuma zīme) un izvēlnē atlasiet “Atlasīt traucējošās šūnas”, un tas novirzīs jūs uz šūnu, kurā ir traucējošie dati.
Kā redzat, zemāk esošajā ekrānuzņēmumā šūnā E2 ir divas atstarpes rakstzīmes. Kad jūs notīrīsit šos datus, jūs iegūsit pareizu izvadi.
Dažreiz neredzamā rakstzīme var būt teksts, kas formatēts ar tādu pašu fonta krāsu kā šūnas aizpildījuma krāsa, vai pielāgota šūnas vērtība, kas formatēta ar cipara kodu ;;;. Pielāgojot šūnas vērtību formatējot ar ;;, viss šajā šūnā tiks paslēpts neatkarīgi no fonta krāsas vai šūnas krāsas.
Noplūdes diapazons satur sapludinātas šūnas
Dažreiz #IZLĪDZ! kļūda rodas, ja noplūdes diapazonā ir apvienotās šūnas. Dinamiskā masīva formula nedarbojas ar sapludinātām šūnām. Lai to labotu, viss, kas jums jādara, ir atcelt šūnu sapludināšanu noplūdes diapazonā vai pārvietot formulu uz citu diapazonu, kurā nav sapludinātu šūnu.
Tālāk esošajā piemērā, lai gan noplūdes diapazons ir tukšs (C2:CC8), formula atgriež noplūdes kļūdu. Tas ir tāpēc, ka šūnas C4 un C5 ir apvienotas.
Lai pārliecinātos, ka sapludinātās šūnas ir iemesls, kāpēc tiek parādīta kļūda, noklikšķiniet uzbrīdinājuma zīmi un pārbaudiet cēloni – “Izplūdes diapazons ir sapludinājis šūnu”.
Risinājums:
Lai atceltu šūnu sapludināšanu, atlasiet sapludinātās šūnas, pēc tam cilnē "Sākums" noklikšķiniet uz pogas "Apvienot un centrēt" un atlasiet "Atcelt šūnu sapludināšanu".
Ja lielajā izklājlapā ir grūti atrast sapludinātās šūnas, brīdinājuma zīmju izvēlnē noklikšķiniet uz opcijas Atlasīt šķēršļus, lai pārietu uz sapludinātajām šūnām.
Noplūdes diapazons tabulā
Izlijušas masīvu formulas Excel tabulās netiek atbalstītas. Dinamiskā masīva formula jāievada tikai vienā atsevišķā šūnā. Ja tabulā ievadāt izlijuša masīva formulu vai kad noplūdes apgabals iekrīt tabulā, tiks parādīta noplūdes kļūda. Ja tas notiek, mēģiniet pārvērst tabulu parastā diapazonā vai pārvietojiet formulu ārpus tabulas.
Piemēram, kad Excel tabulā ievadām tālāk norādīto izlijušā diapazona formulu, mēs saņemsim Spill kļūdu katrā tabulas šūnā, nevis tikai formulas šūnā. Tas ir tāpēc, ka Excel automātiski kopē jebkuru tabulā ievadīto formulu katrā tabulas kolonnas šūnā.
Ja formula mēģinās izliet rezultātus tabulā, jūs saņemsit noplūdes kļūdu. Tālāk esošajā ekrānuzņēmumā noplūdes apgabals ietilpst esošajā tabulā, tāpēc tiek parādīta noplūdes kļūda.
Lai apstiprinātu šīs kļūdas cēloni, noklikšķiniet uz brīdinājuma zīmes un skatiet kļūdas iemeslu — “Noplūdes diapazons tabulā”
Risinājums:
Lai labotu kļūdu, Excel tabulā ir jāatgriež diapazons. Lai to izdarītu, ar peles labo pogu noklikšķiniet jebkurā vietā tabulā, noklikšķiniet uz "Tabula" un pēc tam atlasiet opciju "Konvertēt uz diapazonu". Varat arī noklikšķināt ar peles kreiso taustiņu jebkurā vietā tabulā, pēc tam doties uz cilni "Tabulas noformējums" un atlasīt opciju "Konvertēt uz diapazonu".
Noplūdes diapazons nav zināms
Ja programma Excel nevarēja noteikt izlijušā masīva lielumu, tas aktivizēs noplūdes kļūdu. Dažreiz formula ļauj dinamiskam masīvam mainīt izmērus starp katru aprēķina gājienu. Ja dinamiskā masīva lielums aprēķinu laikā turpina mainīties un nesabalansējas, tas izraisīs #SPILL! Kļūda.
Šāda veida noplūdes kļūda parasti tiek aktivizēta, ja tiek izmantotas nepastāvīgas funkcijas, piemēram, funkcijas RAND, RANDARRAY, RANDBETWEEN, OFFSET un INDIRECT.
Piemēram, kad šūnā B3 izmantojam tālāk norādīto formulu, tiek parādīta noplūdes kļūda:
=SEQUENCE(RANDBWEEN(1, 500))Piemērā funkcija RANDBETWEEN atgriež nejaušu veselu skaitli starp skaitļiem 1 un 500, un tās izvade nepārtraukti mainās. Funkcija SEQUENCE nezina, cik vērtību jāizveido noplūdes masīvā. Līdz ar to #SPILL kļūda.
Jūs varat arī apstiprināt kļūdas cēloni, noklikšķinot uz brīdinājuma zīmes – “Izplūdes diapazons nav zināms”.
Risinājums:
Lai labotu šīs formulas kļūdu, vienīgā izvēle ir aprēķinam izmantot citu formulu.
Noplūdes diapazons ir pārāk liels
Reizēm varat izpildīt formulu, kas izvada izlijušu diapazonu, kas ir pārāk liels, lai darblapa to apstrādātu, un tas var pārsniegt darblapas malas. Kad tas notiks, jūs varat saņemt #SPILL! kļūda. Lai novērstu šo problēmu, varat mēģināt atsaukties uz noteiktu diapazonu vai vienu šūnu, nevis veselas kolonnas vai izmantot rakstzīmi @, lai iespējotu netiešu krustojumu.
Tālāk esošajā piemērā mēs cenšamies aprēķināt 20% no pārdošanas rādītājiem A slejā un atgriezt rezultātus B slejā, taču tā vietā tiek parādīta noplūdes kļūda.
Formula B3 aprēķina 20% no vērtības A3, pēc tam 20% no vērtības A4 un tā tālāk. Tas rada vairāk nekā miljonu rezultātu (1 048 576) un visus tos ievieto B kolonnā, sākot ar šūnu B3, taču tas sasniegs darblapas beigas. Nav pietiekami daudz vietas, lai parādītu visas izejas, kā rezultātā tiek parādīta kļūda #SPILL.
Kā redzat, šīs kļūdas cēlonis ir tas, ka “Izplūdes diapazons ir pārāk liels”.
Risinājumi:
Lai atrisinātu šo problēmu, mēģiniet mainīt visu kolonnu ar atbilstošu diapazonu vai vienas šūnas atsauci vai pievienojiet operatoru @, lai veiktu netiešu krustojumu.
Labot 1: varat mēģināt norādīt diapazonus, nevis visas kolonnas. Šeit mēs mainām visu diapazonu A:A ar A3:A11 formulā, un formula automātiski aizpildīs diapazonu ar rezultātiem.
2. labojums: Aizstājiet visu kolonnu ar tikai šūnas atsauci tajā pašā rindā (A3) un pēc tam kopējiet formulu diapazonā, izmantojot aizpildīšanas turi.
3. labojums: Varat arī mēģināt pirms atsauces pievienot operatoru @, lai veiktu netiešu krustojumu. Izvade tiks parādīta tikai formulas šūnā.
Pēc tam kopējiet formulu no šūnas B3 uz pārējo diapazonu.
Piezīme: Rediģējot izlijušu formulu, varat rediģēt tikai pirmo šūnu noplūdes apgabalā/diapazonā. Jūs varat redzēt formulu citās noplūdes diapazona šūnās, taču tās būs pelēkas un tās nevar atjaunināt.
Pietrūkst atmiņas
Ja izpildāt izlijušu masīva formulu, kuras dēļ programmai Excel pietrūkst atmiņas, tā var izraisīt #SPILL kļūdu. Šādos apstākļos mēģiniet atsaukties uz mazāku masīvu vai diapazonu.
Neatpazīts/atkāpšanās
Varat arī saņemt noplūdes kļūdu pat tad, ja programma Excel neatpazīst vai nevar saskaņot kļūdas cēloni. Šādos gadījumos vēlreiz pārbaudiet formulu un pārliecinieties, vai visi funkciju parametri ir pareizi.
Tagad jūs zināt visus #SPILL iemeslus un risinājumus! kļūdas programmā Excel 365.