Co to jest błąd rozlania? Jak rozwiązać problem #ROZLANIE! błąd w Excelu 365

Jak wszyscy wiemy, Office 365 jest dostarczany z programem Excel 365 w pakiecie. Firma Microsoft dodała różne nowe funkcje do programu Excel 365. Jedną z takich funkcji jest Dynamiczne formuły tablicowe. Zwykle formuła zwraca do komórki tylko jedną wartość. Ale teraz, dzięki tej nowej funkcji, można zwrócić wiele wartości.

Na przykład w programie Excel 2019 i wcześniejszych wersjach załóżmy, że zastosujesz do komórki formułę =D2:D5, wynik będzie ograniczony do pierwszej komórki.

Kiedy musieliśmy zastosować formułę do wszystkich odpowiednich komórek, skorzystaliśmy z notacji Array (Ctrl+Shift+Enter). Jednak w Excel 365 tak nie jest. Gdy zastosujesz tę samą formułę, automatycznie wartości zostaną rozrzucone na wszystkie odpowiednie komórki. Zobacz poniższy obraz, aby uzyskać więcej informacji.

Obszar komórek, do którego rozlewa się wynik, nazywa się Zakres rozlania. Zobacz poniższy obrazek!

NOTATKA:

• ten Rozlanie jest automatycznie włączona z tablicami dynamicznymi (obecnie ta funkcja jest obsługiwana tylko w programie Excel 365) i nie można jej wyłączyć.
instagram story viewer
• Funkcja Spill jest włączona we wszystkich formułach z funkcjami lub bez nich.

Błędy rozlania są widoczne, gdy formuła ma zwracać wiele wartości, jednak wyniki nie mogą być umieszczane w komórkach. Błąd wygląda następująco:

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

• Zakres Rozlanie zawiera pewną wartość, z powodu której wyniki nie mogą być wypełnione w komórkach
• Zakres Spill połączył komórki.
• Gdy stare arkusze (utworzone w programie Excel 2016 lub starszym) z formułami obsługującymi niejawne przecięcie zostaną otwarte w programie Excel365.
• Gdy zastosujesz dynamiczną formułę tablicową w tabeli programu Excel.

Jeśli widzisz błąd #SPILL w programie Excel, nie martw się. W tym artykule zademonstrujemy różne sposoby identyfikacji głównej przyczyny tego problemu, a także przyjrzymy się sposobom naprawienia błędu #SPILL

Zidentyfikuj przyczynę błędu #SPILL

Gdy zobaczysz błąd rozlania, najpierw sprawdź, dlaczego widzisz błąd, aby to zrobić,

Krok 1: Kliknij komórkę, która się wyświetla #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik jak pokazano niżej

Krok 3: Pierwsza linia mówi nam, co powoduje błąd. Na przykład w tym przypadku błąd jest widoczny, ponieważ zakres rozlania nie jest pusty

Poprawki, których należy przestrzegać, gdy zasięg Rozlania nie jest pusty

Postępuj zgodnie z poniższymi poprawkami, gdy zobaczysz, że Zakres rozlania nie jest pusty

Rozwiązanie 1: Usuń dane, które blokują zakres Spill

Jeśli w komórkach z zakresu Rozlanie znajdują się już jakieś dane, podczas stosowania formuły zostanie wyświetlony błąd #ROZLANIE.

Kiedy możesz wyraźnie zobaczyć dane, które blokują zakres Spill

Rozważ poniższy przykład: Po zastosowaniu formuły = D2: D5 do danych, zgłaszany jest błąd ROZLANIA, ponieważ istnieje Jestem tutaj w zasięgu rozlania.

Aby pozbyć się błędu #SPILL wystarczy przenieść dane lub usunąć dane z zakresu rozlania.

Gdy dane blokujące zakres Spill są ukryte

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

W takich przypadkach, aby znaleźć komórkę, która blokuje zakres Spill, wykonaj poniższe czynności:

Krok 1: Kliknij komórkę, która się wyświetla #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik jak pokazano poniżej, widać, że błąd wynika z tego, że Zakres rozlania nie jest pusty.

Krok 3: Z listy rozwijanej kliknij Wybierz blokujące komórki

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

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

Krok 5: Po dokładnym zbadaniu komórki możesz zobaczyć niektóre dane ukryte w komórkach.

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

Naprawić 2: Usuń formatowanie numeru niestandardowego;;; nałożony na komórkę

Czasami, gdy niestandardowe formatowanie liczb ;; ; zostanie zastosowany do komórki, istnieje szansa, że ​​pojawi się błąd SPILL. W takich sprawach,

Krok 1: Kliknij komórkę, która się wyświetla #ROZLANIE! błąd

Krok 2: Kliknij Wykrzyknik jak pokazano niżej.

Krok 3: Z listy rozwijanej kliknij Wybierz blokujące komórki

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

Krok 5: Kliknij prawym przyciskiem myszy blokującą komórkę.

Krok 6: Wybierz Formatuj komórki

Krok 7: Otworzy się okno Formatuj komórki. Przejdź do Numer patka

Krok 8: W lewym panelu wybierz Zwyczaj

Krok 9: Z prawego panelu bocznego zmień Typ z;;; do Ogólny

Krok 10: Kliknij Ok przycisk

Poprawka do naśladowania, gdy zakres rozlania połączył komórki

Jeśli widzisz, że błąd jest spowodowany Zakres rozlania połączył komórki  jak pokazano niżej,

Krok 1: Kliknij Wybierz blokujące komórki  z listy rozwijanej

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

Krok 3: Pod Dom zakładka, kliknij Scal i wyśrodkuj

Krok 4: Z menu wybierz Rozłącz komórki

Poprawka, której należy przestrzegać, gdy Zakres rozlania w tabeli

Dynamiczne formuły tablicowe nie są obsługiwane w tabelach programu Excel. Jeśli widzisz błąd #SPILL w tabeli programu Excel, jak pokazano poniżej z komunikatem Zakres rozlania w tabeli,

Krok 1: Wybierz kompletnie stół

Krok 2: Kliknij Projekt stołu zakładka z górnego paska menu

Krok 3: Wybierz Konwertuj na zakres

Krok 4: Zobaczysz okno dialogowe potwierdzenia, kliknij tak

Poprawka, którą należy zastosować, gdy zakres Spill jest poza pamięcią

Gdy próbujesz zidentyfikować przyczynę błędu #ROZLANIE, jeśli widzisz, że błąd stwierdza Brak pamięci, to dlatego, że dynamiczna formuła tablicowa, której używasz, odwołuje się do dużego zakresu, w takich przypadkach uruchamia Excel brak pamięci powodując błąd rozlania. Aby przezwyciężyć błąd, można spróbować odnieść się do mniejszego zakresu.

Poprawka, którą należy zastosować, gdy zasięg rozlania jest nieznany

Ten błąd pojawia się, gdy zmienia się rozmiar rozlanej tablicy, a program Excel nie jest w stanie ustalić rozmiaru rozlanego zakresu. Ogólnie ten błąd jest wyświetlany, gdy używasz funkcji losowych, takich jak RANDARRAY, RAND lub RANDBETWEEN wraz z funkcjami tablicy dynamicznej, takimi jak SEQUENCE.

Aby lepiej to zrozumieć, rozważmy poniższy przykład, powiedzmy, że używana jest funkcja SEQUENCE(RANDBETWEEN(1,100)). Tutaj RANDBETWEEN generuje losową liczbę całkowitą, która jest większa lub równa 1 i mniejsza lub równa 100. A SEQUENCE generuje liczby sekwencyjne (np. SEQUENCE(5) ​​generuje 1,2,3,4,5). Jednak RANDBETWEEN jest funkcją lotną i zmienia swoją wartość za każdym razem, gdy arkusz Excela jest otwierany lub zmieniany. Z tego powodu funkcja SEQUENCE nie będzie w stanie określić rozmiaru tablicy, którą ma wygenerować. Nie wiedziałby, ile wartości wygenerować i dlatego zgłasza błąd SPILL.

Kiedy identyfikujesz przyczynę błędu, widzisz Zakres rozlania jest nieznany

Aby naprawić ten rodzaj błędu, spróbuj mieć inną formułę, która odpowiada Twoim potrzebom.

Poprawki, których należy przestrzegać, gdy zasięg Rozlania jest zbyt duży

Załóżmy, że identyfikujesz przyczynę i zauważasz, że błąd jest widoczny, ponieważ Zasięg rozlania jest za duży jak pokazano niżej.

Gdy nie było tablicy dynamicznej, w programie Excel istniało coś, co nazywa się niejawnym przecięciem, które zmuszony do zwrócenia pojedynczego wyniku, nawet jeśli formuła ma potencjał zwrócenia wielu wyniki. Rozważmy przykład, jeśli formuła =B: B*5% jest stosowany w programie Excel 2019 lub wcześniejszych wersjach, z niejawnym przecięciem w miejscu, wynik byłby następujący:

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

Aby rozwiązać ten problem, wypróbuj następujące rozwiązania

Rozwiązanie 1: Zastosuj niejawne przecięcie za pomocą operatora @

Kiedy mówimy =B: B, tablica dynamiczna będzie odwoływać się do całej kolumny B. Zamiast tego możemy zmusić program Excel do narzucenia niejawnego przecięcia za pomocą operatora @

Zmień formułę na[e-mail chroniony]:B*5%

Ponieważ dodawane jest niejawne przecięcie, formuła zostanie zastosowana do pojedynczej komórki. W celu rozszerzenia formuły,

1. Wystarczy kliknąć kropkę, jak pokazano poniżej

2. Przeciągnij go w dół na komórki zgodnie z wymaganiami. Spowoduje to zastosowanie tej samej formuły do ​​tych komórek.

Poprawka 2: Zamiast odnosić się do kolumny, zapoznaj się z zakresem

W formule =B: B*5%, mamy na myśli kolumnę B. Zamiast tego odwołaj się do konkretnego zakresu, =B2:B4*5%

To wszystko

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

Uprzejmie polub i skomentuj, jeśli udało Ci się rozwiązać problem za pomocą powyższych metod.

Dziękuję za przeczytanie.