Obsługa błędu SPILL

Gdy formuła tablicy dynamicznej nie może rozmieścić całego wyniku, Excel wyświetla w komórce zakotwiczenia wartość #SPILL!. Sama formuła jest zwykle poprawna; problem polega na tym, że coś blokuje miejsce, w którym powinny pojawić się wyniki.

Można to porównać do próby zaparkowania autobusu w miejscu, w którym stoi już samochód. Autobus jest sprawny, ale miejsce jest zablokowane.

Najczęstszą przyczyną jest to, że jedna lub więcej komórek w zakresie rozlewania zawiera już dane. Jeśli formuła =UNIQUE(A2:A12) potrzebuje wypełnić zakres C1:C3, ale komórka C2 zawiera przypadkową wartość, rozlewanie zostanie zablokowane.

Rozwiązanie jest proste: zaznacz komórkę zakotwiczenia, sprawdź przerywany obrys zakresu rozlewania i wyczyść wszystkie niepuste komórki znajdujące się w jego obrębie.

Kliknij komórkę wyświetlającą #SPILL!. Excel narysuje przerywaną ramkę wokół obszaru, który powinien zająć wynik. Przyczyną problemu jest każda niepusta komórka znajdująca się wewnątrz tej ramki.

Możesz również kliknąć trójkąt ostrzeżenia obok komórki i wybrać opcję Select Obstructing Cells. Spowoduje to bezpośrednie przejście do problematycznych komórek, aby można było usunąć ich zawartość.

Zakres rozlewania nie może nachodzić na żadne scalone komórki. Nawet pusta scalona komórka jest uznawana za przeszkodę, ponieważ rozlewanie wymaga osobnych komórek do wypełnienia.

Jeśli wynik ma pojawić się na scalonym pasku tytułu lub etykiecie, rozdziel te komórki (karta Home, przełącznik Merge and Center), a formuła zostanie poprawnie rozlana.

Rozlewanie nie powiedzie się również wtedy, gdy wynik wychodziłby poza krawędź arkusza lub wchodził do tabeli. Na przykład formuła znajdująca się w pobliżu ostatniego wiersza, która ma rozlewać wynik w dół, nie będzie miała dokąd się rozlać.

Przenieś formułę wyżej lub do kolumny, pod którą jest wolne miejsce. Upewnij się, że w kierunku rozlewania znajduje się wystarczająca liczba pustych komórek.

Tabele programu Excel (utworzone za pomocą skrótu Ctrl+T) nie pozwalają na rozlewanie tablic dynamicznych wewnątrz nich, ponieważ tabela zakłada, że każdą komórkę można edytować.

Jeśli formuła rozlewająca znajduje się w kolumnie tabeli, pojawi się błąd #SPILL!. Możesz przekonwertować tabelę z powrotem na zwykły zakres (Table Design, Convert to Range) albo przenieść formułę poza tabelę.

Wskazanie w formule rozlewającej całej kolumny może również wywołać błąd #SPILL!, ponieważ wynik staje się ogromny i może kolidować z inną zawartością albo przekroczyć limity.

Na przykład =A:A*2 próbuje rozlać wynik na milion wierszy. Ogranicz odwołanie do rzeczywistego zakresu danych, na przykład =A2:A100*2, aby rozlew miał rozsądny i jasno określony rozmiar.

Najedź kursorem na trójkąt ostrzegawczy w komórce z błędem #SPILL!, a Excel dokładnie wyjaśni przyczynę. Typowe komunikaty to Zakres rozlewu nie jest pusty, Zakres rozlewu zawiera scalone komórki oraz Zakres rozlewu jest zbyt duży.

Zawsze najpierw przeczytaj tę etykietkę. Wskaże ona bezpośrednio przyczynę, zamiast zmuszać do zgadywania, a każdy komunikat odpowiada jednemu z omówionych sposobów naprawy.

Warto pamiętać, że błąd #SPILL! rzadko oznacza problem z logiką formuły. Obliczenia zakończyły się pomyślnie — problem dotyczy wyłącznie umieszczenia wyniku.

Po usunięciu przeszkody ta sama, niezmieniona formuła rozleje się prawidłowo. Dla porównania błędy #VALUE! lub #REF! zwykle rzeczywiście sygnalizują problem wewnątrz samej formuły.

Wpisujesz =SORT(UNIQUE(A2:A20)) w komórce D1 i otrzymujesz #SPILL!. Zaznaczasz D1, widzisz przerywany obrys obejmujący zakres D1:D5 i zauważasz starą notatkę w komórce D3.

Usuwasz zawartość komórki D3. Natychmiast formuła rozlewa posortowaną listę unikatowych wartości w dół zakresu D1:D5. Nie trzeba było w ogóle edytować formuły.

Odrobina planowania pozwala uniknąć większości błędów rozlewania. Zapewnij formułom tablic dynamicznych pustą kolumnę lub obszar, w którym mogą się rozwijać, i nie umieszczaj etykiet ani notatek na drodze rozlewu.

Odwołując się do innego rozlewu, użyj operatora #, na przykład =SUM(D1#), aby formuły dostosowywały się do zmiany rozmiaru zakresów. Umieszczaj wyniki w przejrzystych, pustych obszarach, a błąd #SPILL! będzie pojawiał się bardzo rzadko.

Sprawdź swoje zrozumienie błędu SPILL.

Dowiedziałeś się, że #SPILL! to problem z umieszczeniem wyniku, a nie błąd logiki formuły:

• Najczęściej komórka w zakresie rozlewu nie jest pusta.
• Scalone komórki i tabele blokują rozlewanie.
• Unikaj odwołań do całych kolumn, które powodują powstanie zbyt dużego wyniku.
• Użyj polecenia Select Obstructing Cells oraz etykietki, aby znaleźć przyczynę.

To kończy kurs dotyczący tablic dynamicznych i rozlewania. Możesz teraz zwracać wiele wyników za pomocą jednej formuły i skutecznie naprawiać problemy z rozlewaniem.