Co to jest błąd rozlania? Jak rozwiązać #spill! Błąd w programie Excel 365

Jak wszyscy wiemy, Office 365 jest wyposażony w Excel 365 pakiet z nim. Microsoft dodał różne nowe funkcje do programu Excel 365. Jedną z takich funkcji jest Formuły tablicy dynamicznej. Zwykle formuła zwróciłaby tylko jedną wartość w wyniku komórki. Ale teraz, z tą nową funkcją, można zwrócić wiele wartości.

Na przykład w Excel 2019 i wcześniejszych wersjach powiedzmy, że zastosujesz formułę = d2: d5 na komórce, wynik byłby ograniczony do pierwszej komórki.

Kiedy musieliśmy zastosować formułę na wszystkich odpowiednich komórkach, wykorzystaliśmy notację tablicy (Ctrl+Shift+Enter). Jednak w programie Excel 365. Po zastosowaniu tej samej formuły automatycznie wartości są rozlane na wszystkie odpowiednie komórki. Więcej informacji można znaleźć w poniższym obrazku.

Obszar komórek, w których wylewa się wynik, jest wywoływany Zakres wycieków. Patrz poniższe zdjęcie

NOTATKA:

• Rozlanie jest automatycznie włączony do tablic dynamicznych (obecnie ta funkcja jest obsługiwana tylko w programie Excel 365), a funkcji nie można wyłączyć.
• Funkcja wycieku jest włączona na wszystkich formułach z funkcjami lub bez.

Błędy rozlania są widoczne, kiedy formuła ma zwrócić wiele wartości, jednak wyników nie można umieścić na komórkach. Błąd wygląda następująco:

Możliwe przyczyny napotkania błędu #spill to:

• Zakres wycieków zawiera pewną wartość, ponieważ wyniki nie mogą być wypełnione w komórkach
• Zakres wycieków ma połączone komórki.
• Kiedy stare arkusze (utworzone za pomocą Excel 2016 lub wcześniejszych) z formułami wspierającymi niejawne skrzyżowanie są otwarte w programie Excel365.
• Po zastosowaniu formuły tablicy dynamicznej na tabeli Excel.

Jeśli widzisz błąd #spill w programie Excel, nie martw się. W tym artykule będziemy demonstrować różne sposoby zidentyfikowania głównej przyczyny tego problemu, a także przyjrzeć się sposobom naprawy błędu #spill

Spis treści

• Zidentyfikuj, co powoduje błąd #spill
• Poprawki, które należy przestrzegać, gdy zasięg wycieków nie jest pusty
• Naprawić, gdy zakres rozlania połączył komórki
• Naprawić, gdy rozlanie się w tabeli
• Naprawić, gdy zasięg wycieków jest poza pamięcią
• Naprawić, gdy zasięg wycieków jest nieznany
• Poprawki, które należy przestrzegać, gdy zasięg wycieków jest zbyt duży

Zidentyfikuj, co powoduje błąd #spill

Kiedy widzisz błąd wycieku, najpierw sprawdź, dlaczego widzisz błąd, aby to zrobić,

Krok 1: Kliknij komórkę, która pokazuje #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik jak pokazano niżej

Krok 3: Pierwszy wiersz mówi nam, co powoduje błąd. Na przykład w tym przypadku błąd jest postrzegany jako zasięg wycieków nie jest pusty

Poprawki, które należy przestrzegać, gdy zasięg wycieków nie jest pusty

Postępuj zgodnie z poniższymi poprawkami, gdy zobaczysz Zakres wycieków nie jest pusty

Poprawka 1: Usuń dane, które blokują zakres wycieków

Jeśli w komórkach znajdują się już pewne dane w zakresie wycieków, zobaczysz błąd #SPILL w zastosowaniu wzoru.

Kiedy wyraźnie zobaczysz dane, które blokują zakres wycieków

Rozważ poniższy przykład, gdy zastosujesz formułę = d2: d5 na danych, błąd rozlania jest wyrzucany, tak jak jest Jestem tutaj W zakresie wycieków.

Aby pozbyć się błędu #spill, po prostu przesuń dane lub usuń dane z zakresu wycieku.

Gdy dane blokujące zakres wycieków są ukryte

W niektórych przypadkach dane blokujące zakres wycieków są ukryte i niezbyt oczywiste, jak widać w przypadku 1. Rozważ poniższy przykład,

W takich przypadkach, aby znaleźć komórkę, która blokuje zakres wycieku, wykonaj poniższe kroki:

Krok 1: Kliknij komórkę, która pokazuje #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik Jak pokazano poniżej, widać, że błąd wynosi Zakres wycieków nie jest pusty.

Krok 3: Z rozwijania, kliknij Wybierz utrudnianie komórek

Krok 4: Komórka, która blokuje zakres wycieków, jest podświetlona, ​​jak pokazano poniżej

Teraz wiesz, która komórka blokuje, sprawdź, co dokładnie powoduje problem.

Krok 5: Podczas dokładnego zbadania komórki można zobaczyć niektóre dane ukryte w komórkach.

Jak widać na powyższym obrazie, są pewne dane. Ponieważ czcionka ma biały kolor, nie jest łatwo rozpoznać blokadę. Aby pozbyć się błędu, usuń dane z komórki w zakresie rozlania.

Naprawić 2: Usuń niestandardowe formatowanie numerów; ; ; zastosowane na komórce

Czasami, gdy niestandardowe formatowanie liczb ; ; ; jest stosowany na komórce, istnieje szansa, aby zobaczyć błąd rozlania. W takich sprawach,

Krok 1: Kliknij komórkę, która pokazuje #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik jak pokazano niżej.

Krok 3: Z rozwijania, kliknij Wybierz utrudnianie komórek

Krok 4: Komórka, która blokuje zakres wycieków, jest podświetlona, ​​jak pokazano poniżej

Krok 5: Kliknij prawym przyciskiem myszy ogniwo przeszkadzające.

Krok 6: Wybierz Formatowe komórki

Krok 7: Otwiera się okno komórek formatowych. Idź do Numer patka

Krok 8: Z panelu po lewej stronie wybierz Zwyczaj

Krok 9: Z panelu po prawej stronie zmień typ z; ; ; Do Ogólny

Krok 10: Kliknij OK przycisk

Naprawić, gdy zakres rozlania połączył komórki

Jeśli zobaczysz, że błąd jest spowodowany tym, że Zakres wycieków ma połączone komórki jak pokazano niżej,

Krok 1: Kliknij Wybierz utrudnianie komórek Z rozwijania

Krok 2: Komórka przeszkadzająca będzie podświetlony

Krok 3: pod pod Dom Tab, kliknij Scal & Center

Krok 4: Z rozwijania wybierz Komórki niezmierzone

Naprawić, kiedy Zakres wycieków w tabeli

Formuły dynamicznych tablic nie są obsługiwane w tabelach Excel. Jeśli zobaczysz błąd #spill w tabeli Excel, jak pokazano poniżej z komunikatem Zakres wycieków w tabeli,

Krok 1: Całkowicie wybierz tabelę

Krok 2: Kliknij Projektowanie stołu Zakładka z górnego paska menu

Krok 3: Wybierz Konwertuj na zakres

Krok 4: Widać okno dialogowe potwierdzenia, kliknij Tak

Naprawić, gdy zasięg wycieków jest poza pamięcią

Kiedy próbujesz zidentyfikować przyczynę błędu #spill, jeśli zobaczysz, że błąd stwierdza Brak pamięci, Zatem dlatego, że formuła tablicy dynamicznej używasz odniesień do dużego zakresu, w takich przypadkach Excel działa brak pamięci powodując błąd wycieku. Aby przezwyciężyć błąd, można spróbować odwołać się do mniejszego zakresu.

Naprawić, gdy zasięg wycieków jest nieznany

Ten błąd jest widoczny, gdy rozmiar rozlanej tablicy i Excel nie jest w stanie ustalić wielkości rozlanego zakresu. Zasadniczo, gdy używasz losowych funkcji, takich jak Randarray, Rand lub Randbet między funkcjami tablicy dynamicznej, takie jak sekwencja, ten błąd jest widoczny.

Aby lepiej to zrozumieć, rozważ poniższy przykład, powiedzmy, że używana jest sekwencja funkcji (Randbetween (1100)). Tutaj randbetween generuje liczbę liczbową losową, która jest większa lub równa 1 i mniejsza lub równa 100. A sekwencja generuje liczby sekwencyjne (np. Sensencja (5) generuje 1,2,3,4,5). Jednak Randbetween jest funkcją niestabilną i ciągle zmienia swoją wartość za każdym razem, gdy arkusz Excel jest otwierany lub zmieniany. Ponieważ z tego funkcja sekwencji nie będzie w stanie określić wielkości tablicy, którą musi wygenerować. Nie wiedziałoby, ile wartości wygenerował, a tym samym rzuca błąd rozlania.

Widzisz, gdy identyfikujesz przyczynę błędu Zakres wycieków jest nieznany

Aby naprawić tego rodzaju błąd, Spróbuj mieć inną formułę, która odpowiada Twojej potrzebie.

Poprawki, które należy przestrzegać, gdy zasięg wycieków jest zbyt duży

Powiedzmy, że identyfikujesz przyczynę i zauważasz, że błąd jest widoczny, ponieważ Zakres wycieków jest zbyt duży jak pokazano niżej.

Kiedy nie było dynamicznej tablicy, było coś, co nazywa się niejawne skrzyżowanie w programie Excel, które zmuszone do zwrócenia jednego wyniku, nawet gdy formuła miała potencjał zwracania wielu wyników. Rozważ przykład, jeśli formuła = B: B*5% jest stosowany w wersjach Excel 2019 lub wcześniejszych, wraz z niejawnym skrzyżowaniem, wynik byłby następujący:

Jednak gdy ta sama formuła jest używana w programie Excel 365, widać następujący błąd

Aby to rozwiązać, wypróbuj następujące poprawki

Napraw 1: Zastosuj niejawne skrzyżowanie za pomocą @ operator

Kiedy mówimy = B: B, tablica dynamiczna odniesie całą kolumnę B. Zamiast tego możemy zmusić Excel do nałożenia niejawnego skrzyżowania za pomocą operatora @

Zmień formułę na =@B: b*5%

Ponieważ dodaje się niejawne przecięcie, formuła zostanie zastosowana do pojedynczej komórki. Aby przedłużyć formułę,

1. Po prostu kliknij kropkę, jak pokazano poniżej

2. Przeciągnij go w dół do komórek zgodnie z wymaganiami. Zastosuje to tę samą formułę do tych komórek.

Napraw 2: Zamiast odnosić się do kolumny, patrz zakres

W formule, = B: B*5% , Odnosimy się do kolumny B. Zamiast tego odnoszą się do określonego zakresu, = B2: B4*5%

To wszystko

Mamy nadzieję, że ten artykuł był pouczający.

Uprzejmie polub i skomentuj, jeśli byłeś w stanie rozwiązać problem z powyższymi metodami.

Dziękuję za przeczytanie.