Ovaj će vam članak pomoći razumjeti sve uzroke #SPILL pogrešaka kao i rješenja za njihovo ispravljanje u Excelu 365.
#PROSUTI! je nova vrsta Excel pogreške koja se uglavnom javlja kada formula koja daje višestruke rezultate izračuna pokušava prikazati svoje izlaze u rasponu izlijevanja, ali taj raspon već sadrži neke druge podatke.
Podaci za blokiranje mogu biti bilo što, uključujući tekstualnu vrijednost, spojene ćelije, razmakni znak ili čak kada nema dovoljno mjesta za vraćanje rezultata. Rješenje je jednostavno, ili obrišite raspon svih blokirajućih podataka ili odaberite prazan niz ćelija koje u sebi ne sadrže nikakvu vrstu podataka.
Pogreška prelijevanja obično se događa pri izračunavanju formula dinamičkog niza, jer je formula dinamičkog niza ona koja rezultate daje u više ćelija ili niz. Pogledajmo detaljnije i shvatimo što pokreće ovu pogrešku u Excelu i kako je riješiti.
Što uzrokuje pogrešku pri izlivanju?
Od pokretanja dinamičkih nizova 2018., Excel formule mogu istovremeno obraditi više vrijednosti i vratiti rezultate u više od jedne ćelije. Dinamički nizovi su nizovi promjenjive veličine koji omogućuju formulama da vrate više rezultata u raspon ćelija na radnom listu na temelju formule unesene u jednu ćeliju.
Kada formula dinamičkog niza vrati više rezultata, ti se rezultati automatski prelijevaju u susjedne ćelije. Ovo ponašanje se u Excelu naziva 'Prolijevanje'. A raspon ćelija u koji se rezultati izlijevaju naziva se 'Raspon izlijevanja'. Raspon izlijevanja će se automatski proširiti ili skupiti na temelju izvornih vrijednosti.
Ako formula pokušava ispuniti raspon izlijevanja s više rezultata, ali je nešto blokira u tom rasponu, pojavljuje se pogreška #SPILL.
Excel sada ima 9 funkcija koje koriste funkciju Dynamic Array za rješavanje problema, a to uključuje:
- SLIJED
- FILTAR
- PRENOSITI
- VRSTA
- SORTIRAJ
- RANDARRAY
- JEDINSTVENO
- XLOOKUP
- XMATCH
Formule dinamičkog niza dostupne su samo u "Excel 365" i trenutačno ih ne podržava nijedan izvanmrežni Excel softver (tj. Microsoft Excel 2016, 2019).
Pogreške pri izlivanju nisu uzrokovane samo ometanjem podataka, postoji nekoliko razloga zašto možete dobiti pogrešku #Spill. Dopustite nam da istražimo različite situacije u kojima biste se mogli susresti s #PROLIVANJEM! greške i kako ih popraviti.
Raspon izlijevanja nije prazan
Jedan od primarnih uzroka pogreške pri izlivanju je taj što raspon izlijevanja nije prazan. Na primjer, ako pokušavate prikazati 10 rezultata, ali ako ima bilo kakvih podataka u bilo kojoj od ćelija u području izlijevanja, formula vraća #PROLIVANJE! pogreška.
Primjer 1:
U donjem primjeru uneli smo funkciju TRANSPOSE u ćeliju C2 za pretvaranje vertikalnog raspona ćelija (B2:B5) u horizontalni raspon (C2:F2). Umjesto da stupac prebaci u redak, Excel nam pokazuje #PROLIVANJE! pogreška.
A kada kliknete na ćeliju formule, vidjet ćete isprekidani plavi obrub koji označava područje/raspon izlijevanja (C2:F2) koji je potreban za prikaz rezultata kao što je prikazano u nastavku. Također, primijetit ćete žuti znak upozorenja sa uskličnikom na njemu.
Da biste razumjeli razlog pogreške, kliknite ikonu upozorenja pored pogreške i pogledajte poruku u prvom retku označenu sivom bojom. Kao što vidite, ovdje piše "Raspon izlijevanja nije prazan".
Ovdje je problem u tome što ćelije u rasponu izlijevanja D2 i E2 imaju tekstualne znakove (ne prazne), stoga je pogreška.
Riješenje:
Rješenje je jednostavno, ili obrišite podatke (ili premjestite ili izbrišite) koji se nalaze u rasponu izlijevanja ili premjestite formulu na drugo mjesto gdje nema prepreka.
Čim izbrišete ili premjestite blokadu, Excel će automatski popuniti ćelije s rezultatima formule. Ovdje, kada izbrišemo tekst u D2 i E2, formula transponira stupac u redak kako je predviđeno.
Primjer 2:
U primjeru u nastavku, iako se raspon izlijevanja čini praznim, formula i dalje pokazuje izlijevanje! pogreška. To je zato što izlijevanje zapravo nije prazno, već ima karakter nevidljivog prostora u jednoj od ćelija.
Teško je locirati znakove razmaka ili bilo koji drugi nevidljivi znak koji se skriva u onome što se čini kao prazne ćelije. Da biste pronašli takve ćelije s neželjenim podacima, kliknite floatie Error (znak upozorenja) i odaberite 'Odaberi ćelije koje ometaju' s izbornika i odvest će vas do ćelije koja sadrži podatke koji ometaju.
Kao što možete vidjeti, na donjoj snimci zaslona, ćelija E2 ima dva znaka razmaka. Kada izbrišete te podatke, dobit ćete ispravan izlaz.
Ponekad nevidljivi znak može biti tekst formatiran istom bojom fonta kao i boja ispune ćelije ili vrijednost ćelije prilagođena kodu broja ;;;. Kada prilagođeno formatirate vrijednost ćelije pomoću ;;;, ona će sakriti sve u toj ćeliji, bez obzira na boju fonta ili boju ćelije.
Raspon izlijevanja sadrži spojene ćelije
Ponekad, #PROLIVANJE! pogreška se javlja kada raspon izlijevanja sadrži spojene ćelije. Formula dinamičkog niza ne radi sa spojenim ćelijama. Da biste to popravili, sve što trebate učiniti je poništiti spajanje ćelija u rasponu izlijevanja ili premjestiti formulu u drugi raspon koji nema spojenih ćelija.
U primjeru u nastavku, iako je raspon izlijevanja prazan (C2:CC8), formula vraća pogrešku izlijevanja. To je zato što su stanice C4 i C5 spojene.
Da biste bili sigurni da su spojene ćelije razlog zašto dobivate pogrešku, kliknite naznak upozorenja i provjerite uzrok – ‘Raspon izlijevanja spojio je ćeliju’.
Riješenje:
Da biste poništili spajanje ćelija, odaberite spojene ćelije, a zatim na kartici "Početna" kliknite gumb "Spajanje i središte" i odaberite "Poništi spajanje ćelija".
Ako vam je teško locirati spojene ćelije u velikoj proračunskoj tablici, kliknite opciju "Odaberi ćelije koje ometaju" na izborniku znakova upozorenja da biste prešli na spojene ćelije.
Raspon izlijevanja u tablici
Formule izlivenog polja nisu podržane u Excel tablicama. Formulu dinamičkog niza treba unijeti samo u jednu pojedinačnu ćeliju. Ako unesete formulu izlivenog niza u tablicu ili kada područje izlijevanja padne u tablicu, dobit ćete pogrešku izlijevanja. Kada se to dogodi, pokušajte pretvoriti tablicu u normalni raspon ili premjestite formulu izvan tablice.
Na primjer, kada unesemo sljedeću formulu raspona izlijevanja u Excel tablicu, dobili bismo pogrešku izlijevanja u svakoj ćeliji tablice, a ne samo u ćeliji formule. To je zato što Excel automatski kopira bilo koju formulu unesenu u tablicu u svaku ćeliju u stupcu tablice.
Također, dobit ćete pogrešku izlijevanja kada formula pokuša preliti rezultate u tablicu. Na snimci zaslona ispod, područje izlijevanja spada u postojeću tablicu, pa dobivamo pogrešku izlijevanja.
Da biste potvrdili uzrok ove pogreške, kliknite znak upozorenja i pogledajte razlog pogreške – ‘Raspon izlijevanja u tablici’
Riješenje:
Da biste ispravili pogrešku, morat ćete vratiti Excel tablicu natrag u raspon. Da biste to učinili, desnom tipkom miša kliknite bilo gdje unutar tablice, kliknite "Tablica", a zatim odaberite opciju "Pretvori u raspon". Alternativno, možete kliknuti lijevom tipkom bilo gdje unutar tablice, zatim otići na karticu "Dizajn tablice" i odabrati opciju "Pretvori u raspon".
Raspon izlijevanja je nepoznat
Ako Excel nije mogao utvrditi veličinu prolivenog niza, pokrenut će pogrešku izlijevanja. Ponekad formula omogućuje promjenu veličine dinamičkog niza između svakog prolaska izračuna. Ako se veličina dinamičkog niza nastavi mijenjati tijekom izračunavanja i ne uravnoteži se, to će uzrokovati #PROLIVANJE! Greška.
Ova vrsta pogreške izlijevanja obično se pokreće kada se koriste nestabilne funkcije kao što su funkcije RAND, RANDARRAY, RANDBETWEEN, OFFSET i INDIRECT.
Na primjer, kada koristimo formulu u nastavku u ćeliji B3, dobivamo pogrešku izlijevanja:
=SEQUENCE(RAZMEĐU (1, 500))U primjeru, funkcija RANDBETWEEN vraća nasumični cijeli broj između brojeva 1 i 500, a njezin se izlaz kontinuirano mijenja. A funkcija SEQUENCE ne zna koliko vrijednosti proizvesti u nizu izlijevanja. Dakle, pogreška #SPILL.
Uzrok pogreške možete potvrditi i klikom na znak upozorenja – ‘Raspon izlijevanja je nepoznat’.
Riješenje:
Da biste ispravili pogrešku za ovu formulu, vaš jedini izbor je korištenje druge formule za izračun.
Raspon izlijevanja je prevelik
Ponekad možete izvršiti formulu koja daje prosuti raspon koji je prevelik da bi ga radni list mogao rukovati i može se protezati izvan rubova radnog lista. Kada se to dogodi, možda ćete dobiti #PROLIVANJE! pogreška. Da biste riješili ovaj problem, možete pokušati referencirati određeni raspon ili jednu ćeliju umjesto cijelih stupaca ili upotrijebiti znak '@' kako biste omogućili implicitni presjek
U primjeru u nastavku pokušavamo izračunati 20% brojeva prodaje u stupcu A i vratiti rezultate u stupac B, ali umjesto toga dobivamo pogrešku izlijevanja.
Formula u B3 izračunava 20% vrijednosti u A3, zatim 20% vrijednosti u A4, i tako dalje. Daje više od milijun rezultata (1,048,576) i sve ih prebacuje u stupac B počevši od ćelije B3, ali će doći do kraja radnog lista. Nema dovoljno prostora za prikaz svih izlaza, kao rezultat, dobivamo pogrešku #SPILL.
Kao što možete vidjeti, uzrok ove pogreške je taj – ‘Raspon izlijevanja je prevelik’.
rješenja:
Da biste riješili ovaj problem, pokušajte promijeniti cijeli stupac s relevantnim rasponom ili referencom jedne ćelije ili dodajte operator @ za izvođenje implicitnog presjeka.
Popravi 1: Možete pokušati referencirati raspone umjesto cijelih stupaca. Ovdje mijenjamo cijeli raspon A:A s A3:A11 u formuli, a formula će automatski popuniti raspon rezultatima.
Popravak 2: Zamijenite cijeli stupac samo referencom ćelije u istom retku (A3), a zatim kopirajte formulu niz raspon pomoću ručke za popunjavanje.
Popravak 3: Također možete pokušati dodati @ operator prije reference za izvođenje implicitnog presjeka. Ovo će prikazati izlaz samo u ćeliji formule.
Zatim kopirajte formulu iz ćelije B3 u ostatak raspona.
Bilješka: Kada uređujete prolivenu formulu, možete uređivati samo prvu ćeliju u području/rasponu izlijevanja. Možete vidjeti formulu u drugim ćelijama raspona izlijevanja, ali one će biti zasivljene i ne mogu se ažurirati.
Bez memorije
Ako izvršite prolivenu formulu polja koja uzrokuje da Excel ostane bez memorije, to može pokrenuti pogrešku #SPILL. U tim okolnostima pokušajte referencirati manji niz ili raspon.
Neprepoznato / zamjenski
Također možete dobiti pogrešku izlijevanja čak i kada Excel ne prepoznaje ili ne može pomiriti uzrok pogreške. U takvim slučajevima još jednom provjerite svoju formulu i provjerite jesu li svi parametri funkcija točni.
Sada znate sve uzroke i rješenja za #PROLIVANJE! pogreške u programu Excel 365.