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. Microsoft dodał różne nowe funkcje do 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 formułę =D2:D5 do komórki, wynik byłby 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 obrazek, aby uzyskać więcej informacji.
Obszar komórek, na który rozlewa się wynik, nazywa się Zakres gry . Zobacz poniższy obrazek!
NOTATKA:
- ten Gra 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ć.
- Funkcja Spill jest włączona we wszystkich formułach z funkcjami lub bez nich.
Błędy w grze 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 przy użyciu programu Excel 2016 lub starszego) 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 #PILL
Spis treści
- Zidentyfikuj przyczynę błędu #SPILL
- Poprawki, których należy przestrzegać, gdy zasięg Rozlania nie jest pusty
- Poprawka do naśladowania, gdy zakres rozlania połączył komórki
- Poprawka, której należy przestrzegać, gdy w tabeli zasięg rozlania
- Poprawka, którą należy zastosować, gdy zakres Spill jest poza pamięcią
- Poprawka, którą należy zastosować, gdy zasięg rozlania jest nieznany
- Poprawki, których należy przestrzegać, gdy zasięg Rozlania jest zbyt duży
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 #GRA! 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 SPILL, 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 zasięg Spill są ukryte i niezbyt oczywiste, jak widać w przypadku 1. Rozważmy 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 #GRA! 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ć dwa: Usuń formatowanie liczb niestandardowych; ; ; 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 #GRA! 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 Dobrze 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 Rozdziel 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 komunikat o błędzie: 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ć odwołać 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 SEKWENCJA generuje liczby sekwencyjne (np. SEKWENCJA(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ć, a tym samym 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ło się niejawnym przecięciem, które zmuszało do zwrócenia jednego wyniku, nawet jeśli formuła mogła zwrócić wiele wyników. Rozważmy przykład, jeśli formuła =B:B*5% zostanie zastosowany w programie Excel 2019 lub wcześniejszych wersjach, z niejawnym przecięciem w miejscu, wynik będzie 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 [ochrona poczty e-mail] :B*5%
Ponieważ dodawane jest niejawne przecięcie, formuła zostanie zastosowana do pojedynczej komórki. W celu rozszerzenia formuły,
1. Po prostu kliknij 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łujemy 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.