Naprawianie typowych błędów kopiowania

Większość błędów podczas kopiowania ma tę samą przyczynę: odwołanie przesunęło się, choć powinno pozostać nieruchome, albo pozostało nieruchome, choć powinno się przesunąć.

Odwołania względne przesuwają się podczas wypełniania, dlatego formuła wskazująca jedną wspólną komórkę, na przykład zawierającą stawkę podatku lub nagłówek, będzie przy każdym nowym wierszu oddalać się od tej komórki. Wynikiem są nieprawidłowe liczby lub błędy.

W tej lekcji przedstawiono typowe problemy oraz sposoby ich naprawy.

Załóżmy, że komórka E1 zawiera stawkę podatku 0.08, a w komórce D2 wpisano =C2*E1, po czym formułę wypełniono w dół. Oto, co się stanie:

• D2 = =C2*E1 (prawidłowo)
• D3 = =C3*E2 (E2 jest pusta!)
• D4 = =C4*E3 (nadal pusta)

Odwołanie do E przesunęło się i przestało wskazywać stawkę. Wiersze poniżej wiersza 2 mnożą wartość przez zero, dlatego wszędzie pojawia się 0.

Należy zablokować wspólną komórkę, aby nie mogła się przesuwać. Przed wypełnieniem formułę należy zmienić na =C2*$E$1.

Znaki dolara blokują zarówno kolumnę E, jak i wiersz 1. Po wypełnieniu w dół:

• D3 = =C3*$E$1
• D4 = =C4*$E$1

Każdy wiersz jest mnożony przez prawidłową stawkę. Odwołanie C nadal się przesuwa, a $E$1 pozostaje na swoim miejscu.

Jeśli formuła zostanie skopiowana w miejsce, w którym jej odwołania wyszłyby poza krawędź arkusza, pojawi się #REF!.

Na przykład formuła taka jak =A2-B2 umieszczona w kolumnie A i przeciągnięta w lewo nie ma prawidłowego miejsca, na które mogłaby wskazywać, dlatego zwraca #REF!. Ten błąd oznacza, że „odwołanie już nie istnieje”.

Rozwiązaniem jest wklejenie formuły w miejscu, w którym jej odwołania pozostają w obrębie siatki, albo przepisanie jej tak, aby wskazywała prawidłowe komórki.

Skopiowana formuła dzielenia może trafić na puste komórki i próbować dzielić przez zero, wyświetlając #DIV/0!.

Załóżmy, że formuła =B2/C2 została wypełniona w dół kolumny, w której niektóre komórki C są puste. Dzielenie przez pustą komórkę wywołuje błąd.

Formułę można opakować w IFERROR, aby zamiast tego wyświetlała pusty wynik lub zero: =IFERROR(B2/C2,0). Dzielenie nadal jest wykonywane tam, gdzie jest to możliwe, a błędy są ukrywane tam, gdzie nie jest.

Częstym błędem jest przeciągnięcie uchwytu wypełniania zbyt daleko i nadpisanie potrzebnych komórek albo wklejenie formuły w miejsce wpisanych wartości.

Natychmiastowym rozwiązaniem jest użycie Ctrl+Z (Cmd+Z), aby cofnąć operację. Arkusze kalkulacyjne zachowują długą historię cofania, więc można cofnąć kilka przypadkowych operacji wypełniania.

Po cofnięciu należy ponownie wypełnić zakres, tym razem ostrożniej, zatrzymując przeciąganie w odpowiednim ostatnim wierszu.

Przeciągnięcie uchwytu wypełniania kopiuje wraz z formułą także formatowanie. W ten sposób obramowania, kolory lub formaty walut mogą zostać zastosowane w miejscach, w których nie były potrzebne.

Aby skopiować tylko formułę, po przeciągnięciu należy użyć przycisku Opcje autowypełniania i wybrać opcję Wypełnij bez formatowania. Podczas wklejania można użyć polecenia Wklej specjalnie i wybrać wyłącznie opcję Formuły, aby nie zmieniać wyglądu komórek docelowych.

Gdy coś wygląda nieprawidłowo, pierwszym krokiem powinno być zawsze kliknięcie błędnej komórki i odczytanie zawartości paska formuły.

Należy sprawdzić, czy każde odwołanie wskazuje właściwe miejsce. Jeśli stała, która powinna mieć postać $E$1, jest wyświetlana jako E5, oznacza to brak blokady. Odwołanie wyświetlające #REF! wskazuje, że komórka docelowa została usunięta. Pasek formuły pozwala zamienić tajemniczy nieprawidłowy wynik w oczywistą poprawkę.

Aby jednocześnie sprawdzić cały arkusz, należy włączyć widok Pokaż formuły. W Excelu należy nacisnąć Ctrl+` (klawisz akcentu grawis); w Google Sheets należy wybrać Widok, a następnie Pokaż formuły.

Każda komórka wyświetla swoją formułę zamiast wyniku, dzięki czemu można przejrzeć kolumnę i od razu zauważyć wiersz, w którym odwołania się przesunęły. Ponowne naciśnięcie skrótu przywraca zwykły widok.

Najlepszym rozwiązaniem jest całkowite uniknięcie błędu. Przed przeciągnięciem należy przeanalizować każde odwołanie i zdecydować, czy powinno przesuwać się wraz z kolejnymi wierszami, czy pozostać nieruchome.

Znaki dolara należy dodać do wszystkiego, co musi pozostać nieruchome, na przykład do pojedynczej stawki, sumy nagłówka lub tabeli wyszukiwania. Szybkim sposobem ich dodania jest kliknięcie wewnątrz odwołania i naciśnięcie F4, który przełącza kolejne opcje blokowania. Najpierw należy zablokować odwołania, a dopiero potem wypełnić zakres.

Załóżmy, że kolumna prowizji wyświetla 0 w każdym wierszu z wyjątkiem pierwszego. Po kliknięciu wiersza 5 na pasku formuły widać =C5*E4, ale stawka znajduje się w E1.

Diagnoza jest jasna: E1 nie zostało zablokowane, więc odwołanie przesunęło się do E4. Oryginalną formułę należy zmienić na =C2*$E$1, a następnie ponownie wypełnić kolumnę. Teraz każdy wiersz wyświetla prawidłowy wynik. Jeden znak dolara naprawił całą kolumnę.

Proszę sprawdzić umiejętność diagnozowania problemów z kopiowaniem.

Można już naprawiać formuły, które przestają działać po skopiowaniu:

• Przesuwającą się stałą naprawia się przez zablokowanie jej znakami dolara, na przykład $E$1.
• #REF! oznacza, że odwołanie wyszło poza siatkę; #DIV/0! oznacza dzielenie przez pustą komórkę, które można obsłużyć funkcją IFERROR.
• Ctrl+Z cofa błędne wypełnienie, a opcja Wypełnij bez formatowania pozwala zachować prawidłowe style.
• Problemy można diagnozować za pomocą paska formuły lub widoku Pokaż formuły, a odwołania należy blokować przed wypełnieniem zakresu.

Ukończyli Państwo lekcję „Kopiowanie formuł we właściwy sposób”.