Ten artykuł pomoże Ci zrozumieć wszystkie przyczyny błędów #SPILL, a także rozwiązania umożliwiające ich naprawienie w programie Excel 365.
#ROZLANIE! to nowy rodzaj błędu programu Excel, który występuje głównie wtedy, gdy formuła generująca wiele wyników obliczeń próbuje wyświetlić dane wyjściowe w zakresie rozlania, ale ten zakres zawiera już inne dane.
Dane blokujące mogą być dowolne, w tym wartością tekstową, scalonymi komórkami, zwykłym znakiem spacji, a nawet brakiem miejsca na zwrócenie wyników. Rozwiązanie jest proste: wyczyść zakres wszelkich danych blokujących lub wybierz pustą tablicę komórek, które nie zawierają w sobie żadnego typu danych.
Błąd rozlania zwykle występuje podczas obliczania dynamicznych formuł tablicowych, ponieważ dynamiczna formuła tablicowa to taka, która wyprowadza wyniki do wielu komórek lub tablicy. Przyjrzyjmy się bardziej szczegółowo i zrozumiemy, co powoduje ten błąd w programie Excel i jak go rozwiązać.
Co powoduje błąd rozlania?
Od czasu wprowadzenia tablic dynamicznych w 2018 r. formuły programu Excel mogą obsługiwać wiele wartości naraz i zwracać wyniki w więcej niż jednej komórce. Tablice dynamiczne to tablice o zmiennej wielkości, które umożliwiają formułom zwracanie wielu wyników do zakresu komórek w arkuszu na podstawie formuły wprowadzonej w pojedynczej komórce.
Gdy dynamiczna formuła tablicowa zwraca wiele wyników, wyniki te automatycznie rozlewają się na sąsiednie komórki. To zachowanie nosi nazwę „Rozlanie” w programie Excel. A zakres komórek, do których rozlewają się wyniki, nazywa się „Zakresem rozlania”. Zakres rozlania rozszerzy się lub skurczy automatycznie na podstawie wartości źródłowych.
Jeśli formuła próbuje wypełnić zakres rozlania wieloma wynikami, ale jest blokowana przez coś w tym zakresie, wystąpi błąd #ROZPYLANIE.
Program Excel ma teraz 9 funkcji, które wykorzystują funkcjonalność tablicy dynamicznej do rozwiązywania problemów, w tym:
- SEKWENCJA
- FILTR
- TRANSPONOWAĆ
- SORTOWAĆ
- SORTUJ WEDŁUG
- RANDARRAY
- JEDYNY W SWOIM RODZAJU
- XWYSZUKAJ
- XMATCH
Dynamiczne formuły tablicowe są dostępne tylko w programie „Excel 365” i nie są obecnie obsługiwane przez żadne oprogramowanie Excel offline (np. Microsoft Excel 2016, 2019).
Błędy wycieku są spowodowane nie tylko blokowaniem danych, istnieje kilka powodów, dla których możesz otrzymać błąd #Spill. Pozwól nam zbadać różne sytuacje, w których możesz napotkać #ROZLANIE! błąd i jak je naprawić.
Zakres rozlania nie jest pusty
Jedną z głównych przyczyn błędu rozlania jest to, że zakres rozlania nie jest pusty. Na przykład, jeśli próbujesz wyświetlić 10 wyników, ale w którejkolwiek z komórek w obszarze rozlania są jakieś dane, formuła zwraca #ROZLANIE! błąd.
Przykład 1:
W poniższym przykładzie wprowadziliśmy funkcję TRANSPONUJ w komórce C2, aby przekonwertować pionowy zakres komórek (B2:B5) na zakres poziomy (C2:F2). Zamiast przełączać kolumnę do wiersza, Excel pokazuje nam #ROZLANIE! błąd.
Po kliknięciu komórki z formułą zobaczysz przerywaną niebieską ramkę wskazującą obszar/zakres rozlania (C2:F2), który jest potrzebny do wyświetlenia wyników, jak pokazano poniżej. Zauważysz też żółty znak ostrzegawczy z wykrzyknikiem.
Aby zrozumieć przyczynę błędu, kliknij ikonę ostrzeżenia obok błędu i zobacz komunikat w pierwszym wierszu podświetlony na szaro. Jak widać, tutaj jest napisane „Zakres rozlania nie jest pusty”.
Problem polega na tym, że komórki w zakresie rozlania D2 i E2 mają znaki tekstowe (nie puste), stąd błąd.
Rozwiązanie:
Rozwiązanie jest proste: wyczyść dane (przenieś lub usuń) znajdujące się w zakresie rozlania lub przenieś formułę w inne miejsce, w którym nie ma przeszkód.
Jak tylko usuniesz lub przeniesiesz blokadę, Excel automatycznie wypełni komórki wynikami formuły. Tutaj, gdy wyczyścimy tekst w D2 i E2, formuła zgodnie z zamierzeniami transponuje kolumnę do wiersza.
Przykład 2:
W poniższym przykładzie, chociaż zakres rozlania wydaje się pusty, formuła nadal pokazuje Spill! błąd. Dzieje się tak dlatego, że wyciek nie jest w rzeczywistości pusty, ma niewidoczny charakter przestrzenny w jednej z komórek.
Trudno jest zlokalizować znaki kosmiczne lub jakąkolwiek inną niewidzialną postać ukrywającą się w czymś, co wydaje się być pustymi komórkami. Aby znaleźć takie komórki z niechcianymi danymi, kliknij pływak Błąd (znak ostrzegawczy) i wybierz z menu „Wybierz blokujące komórki”, a zostaniesz przeniesiony do komórki zawierającej blokujące dane.
Jak widać, na poniższym zrzucie ekranu komórka E2 ma dwie spacje. Po wyczyszczeniu tych danych otrzymasz właściwe dane wyjściowe.
Czasami niewidoczny znak może być tekstem sformatowanym przy użyciu tego samego koloru czcionki, co kolor wypełnienia komórki lub niestandardową wartością komórki sformatowaną kodem liczbowym ;;;. Gdy sformatujesz wartość komórki za pomocą ;;;, ukryje to wszystko w tej komórce, niezależnie od koloru czcionki lub koloru komórki.
Zakres wycieków zawiera scalone komórki
Czasami #ROZLANIE! błąd występuje, gdy zakres rozlania zawiera scalone komórki. Formuła tablicowa dynamiczna nie działa ze scalonymi komórkami. Aby to naprawić, wystarczy rozdzielić komórki w zakresie rozlania lub przenieść formułę do innego zakresu, w którym nie ma scalonych komórek.
W poniższym przykładzie, mimo że zakres rozlania jest pusty (C2:CC8), formuła zwraca błąd rozlania. Dzieje się tak, ponieważ komórki C4 i C5 są połączone.
Aby upewnić się, że przyczyną błędu są scalone komórki, kliknijznak ostrzegawczy i zweryfikuj przyczynę – „Zakres rozlania połączył komórkę”.
Rozwiązanie:
Aby rozłączyć komórki, wybierz scalone komórki, a następnie na karcie „Strona główna” kliknij przycisk „Scal i wyśrodkuj” i wybierz „Rozłącz komórki”.
Jeśli masz trudności ze znalezieniem scalonych komórek w dużym arkuszu kalkulacyjnym, kliknij opcję „Wybierz blokujące komórki” z menu znaku ostrzegawczego, aby przejść do scalonych komórek.
Zakres rozlania w tabeli
Rozlane formuły tablicowe nie są obsługiwane w tabelach programu Excel. Formuła tablicowa dynamiczna powinna być wprowadzana tylko w pojedynczej komórce. Jeśli wprowadzisz rozlaną formułę tablicową do tabeli lub gdy obszar rozlania spadnie do tabeli, pojawi się błąd Rozlanie. W takim przypadku spróbuj przekonwertować tabelę na normalny zakres lub przenieś formułę poza tabelę.
Na przykład, gdy wprowadzimy następującą formułę rozlanego zakresu w tabeli programu Excel, otrzymamy błąd rozlania w każdej komórce tabeli, a nie tylko w komórce z formułą. Dzieje się tak, ponieważ program Excel automatycznie kopiuje dowolną formułę wprowadzoną w tabeli do każdej komórki w kolumnie tabeli.
Ponadto otrzymasz błąd rozlania, gdy formuła spróbuje rozlać wyniki w tabeli. Na poniższym zrzucie ekranu obszar rozlania mieści się w istniejącej tabeli, więc otrzymujemy błąd wycieku.
Aby potwierdzić przyczynę tego błędu, kliknij znak ostrzegawczy i zobacz przyczynę błędu – „Zakres rozlania w tabeli”
Rozwiązanie:
Aby naprawić błąd, musisz przywrócić tabelę Excela z powrotem do zakresu. Aby to zrobić, kliknij prawym przyciskiem myszy w dowolnym miejscu w tabeli, kliknij „Tabela”, a następnie wybierz opcję „Konwertuj na zakres”. Możesz też kliknąć lewym przyciskiem myszy w dowolnym miejscu w tabeli, a następnie przejść do zakładki „Projekt stołu” i wybrać opcję „Konwertuj na zakres”.
Zakres rozlania jest nieznany
Jeśli program Excel nie był w stanie ustalić rozmiaru rozlanej tablicy, wywoła błąd rozlania. Czasami formuła umożliwia zmianę rozmiaru tablicy dynamicznej między każdym przebiegiem obliczeń. Jeśli rozmiar tablicy dynamicznej zmienia się w trakcie obliczeń i nie równoważy się, spowoduje to #ROZLANIE! Błąd.
Ten typ błędu rozlania jest zwykle wyzwalany podczas korzystania z funkcji zmiennych, takich jak RAND, RANDARRAY, RANDBETWEEN, OFFSET i ADRESS.
Na przykład, gdy użyjemy poniższej formuły w komórce B3, otrzymamy błąd rozlania:
=SEKWENCJA(LOSY MIĘDZY (1, 500))W tym przykładzie funkcja RANDBETWEEN zwraca losową liczbę całkowitą z przedziału od 1 do 500, a jej dane wyjściowe zmieniają się w sposób ciągły. A funkcja SEKWENCJA nie wie, ile wartości wytworzyć w tablicy rozlania. Stąd błąd #SPILL.
Możesz również potwierdzić przyczynę błędu, klikając znak ostrzegawczy – „Zakres rozlania jest nieznany”.
Rozwiązanie:
Aby naprawić błąd w tej formule, jedynym wyborem jest użycie innej formuły do obliczeń.
Zakres rozlania jest zbyt duży
Czasami możesz wykonać formułę, która wyprowadza rozlany zakres, który jest zbyt duży, aby mógł obsłużyć arkusz i może wykraczać poza krawędzie arkusza. Kiedy tak się stanie, możesz dostać #ROZLANIE! błąd. Aby rozwiązać ten problem, możesz spróbować odwołać się do określonego zakresu lub jednej komórki zamiast całych kolumn lub użyć znaku „@”, aby włączyć niejawne przecięcie
W poniższym przykładzie próbujemy obliczyć 20% wartości sprzedaży w kolumnie A i zwrócić wyniki w kolumnie B, ale zamiast tego otrzymujemy błąd rozlania.
Formuła w B3 oblicza 20% wartości w A3, następnie 20% wartości w A4 i tak dalej. Daje ponad milion wyników (1 048 576) i rozlewa je wszystkie w kolumnie B, zaczynając od komórki B3, ale dotrze do końca arkusza. Nie ma wystarczającej ilości miejsca, aby pokazać wszystkie wyjścia, w rezultacie otrzymujemy błąd #SPILL.
Jak widać, przyczyną tego błędu jest to, że – „Zasięg rozlania jest zbyt duży”.
Rozwiązania:
Aby rozwiązać ten problem, spróbuj zmienić całą kolumnę z odpowiednim zakresem lub odwołaniem do pojedynczej komórki albo dodaj operator @, aby wykonać niejawne przecięcie.
Napraw 1: możesz spróbować odnosić się do zakresów, a nie całych kolumn. Tutaj zmieniamy cały zakres A: A na A3: A11 w formule, a formuła automatycznie wypełni zakres wynikami.
Poprawka 2: Zastąp całą kolumnę tylko odwołaniem do komórki w tym samym wierszu (A3), a następnie skopiuj formułę w dół zakresu za pomocą uchwytu wypełniania.
Poprawka 3: Możesz również spróbować dodać operator @ przed odwołaniem, aby wykonać niejawne przecięcie. Spowoduje to wyświetlenie danych wyjściowych tylko w komórce z formułą.
Następnie skopiuj formułę z komórki B3 do reszty zakresu.
Notatka: Podczas edytowania rozlanej formuły możesz edytować tylko pierwszą komórkę w obszarze/zakresie rozlania. Możesz zobaczyć formułę w innych komórkach zakresu rozlania, ale będą one wyszarzone i nie można ich zaktualizować.
Brak pamięci
Jeśli wykonasz rozlaną formułę tablicową, która spowoduje, że program Excel zabraknie pamięci, może to spowodować błąd #PILL. W takich okolicznościach spróbuj odwołać się do mniejszej tablicy lub zakresu.
Nierozpoznany / awaryjny
Możesz również otrzymać błąd zrzutu, nawet jeśli program Excel nie rozpoznaje lub nie może ustalić przyczyny błędu. W takich przypadkach sprawdź dokładnie formułę i upewnij się, że wszystkie parametry funkcji są poprawne.
Teraz znasz już wszystkie przyczyny i rozwiązania problemu #ROZLANIE! błędy w programie Excel 365.