Formuła programu Excel: jak naprawić błąd #SPILL! błąd -

Spisie treści

Podsumowanie

Błąd #SPILL występuje, gdy zakres rozlania jest zablokowany przez coś w arkuszu. Rozwiązaniem jest zazwyczaj wyczyszczenie zakresu wycieku wszelkich utrudniających dane. Zobacz poniżej, aby uzyskać więcej informacji i instrukcje rozwiązania.

Wyjaśnienie

O rozlaniu i #SPILL! błąd

Wraz z wprowadzeniem tablic dynamicznych w programie Excel formuły zwracające wiele wartości „przelewają” te wartości bezpośrednio do arkusza. Prostokąt, który otacza wartości, jest nazywany „zakresem rozlewania”. Gdy dane ulegną zmianie, zakres rozlania będzie się rozszerzał lub kurczył w razie potrzeby. Możesz zobaczyć dodane nowe wartości lub zniknąć istniejące wartości.

Wideo: Wycieki i zakres wycieków

Błąd #SPILL występuje, gdy zakres rozlania jest zablokowany przez coś w arkuszu. Czasami jest to oczekiwane. Na przykład wpisałeś formułę, spodziewając się, że się rozleje, ale przeszkodą są istniejące dane w arkuszu. Rozwiązaniem jest po prostu wyczyszczenie zakresu wycieku wszelkich utrudniających dane.

Czasami jednak błąd może być nieoczekiwany i przez to mylący. Przeczytaj poniżej, jak ten błąd może być spowodowany i co możesz zrobić, aby go rozwiązać.

Zachowanie przy rozlaniu jest natywne

Ważne jest, aby zrozumieć, że zachowanie związane z wyciekami jest automatyczne i natywne. W Dynamic Excel (obecnie tylko Office 365 Excel) każda formuła, nawet prosta formuła bez funkcji, może rozlewać wyniki. Chociaż istnieją sposoby na powstrzymanie formuły przed zwracaniem wielu wyników, rozlewania się nie można wyłączyć za pomocą ustawienia globalnego.

Podobnie w programie Excel nie ma opcji „wyłączania błędów #SPILL. Aby naprawić błąd #SPILL, należy zbadać i rozwiązać podstawową przyczynę problemu.

Poprawka nr 1 - wyczyść zasięg wycieku

To najprostszy przypadek do rozwiązania. Formuła powinna rozlewać wiele wartości, ale zamiast tego zwraca #SPILL! ponieważ coś stoi na przeszkodzie. Aby rozwiązać problem, wybierz dowolną komórkę w zakresie rozlania, aby zobaczyć jej granice. Następnie przenieś dane blokujące do nowej lokalizacji lub całkowicie je usuń. Pamiętaj, że komórki w zakresie rozlania muszą być puste, więc zwróć uwagę na komórki zawierające niewidoczne znaki, takie jak spacje.

Na poniższym ekranie „x” blokuje zakres rozlania:

Po usunięciu znaku „x” funkcja UNIQUE rozlewa wyniki normalnie:

Poprawka nr 2 - dodaj znak @

Przed tablicami dynamicznymi program Excel po cichu zastosował zachowanie zwane „niejawnym przecięciem”, aby zapewnić, że niektóre formuły, które mogą zwracać wiele wyników, zwracają tylko jeden wynik. W niedynamicznych tablicach Excel te formuły zwracają normalnie wyglądający wynik bez błędu. Jednak w niektórych przypadkach ta sama formuła wprowadzona w programie Dynamic Excel może generować błąd #SPILL. Na przykład na poniższym ekranie komórka D5 zawiera skopiowaną formułę:

=$B$5:$B$10+3

Ta formuła nie zgłosi błędu, na przykład w programie Excel 2016, ponieważ niejawne przecięcie uniemożliwiłoby formule zwrócenie wielu wyników. Jednak w programie Dynamic Excel formuła automatycznie zwraca wiele wyników do arkusza, które zderzają się ze sobą, ponieważ formuła jest kopiowana z D5: D10.

Jednym z rozwiązań jest użycie znaku @, aby włączyć niejawne przecięcie w następujący sposób:

= @$B$5:$B$10+3

Po tej zmianie każda formuła ponownie zwraca jeden wynik, a błąd #SPILL znika.

Uwaga: to częściowo wyjaśnia, dlaczego możesz nagle zobaczyć znak „@” pojawiający się w formułach utworzonych w starszych wersjach programu Excel. Ma to na celu zachowanie zgodności. Ponieważ formuły w starszych wersjach programu Excel nie mogą rozlewać się na wiele komórek, znak @ jest dodawany, aby zapewnić to samo zachowanie po otwarciu formuły w programie Dynamic Excel.