Dostosowywanie odwołań podczas kopiowania

Gdy w formule wpisują Państwo =A2, arkusz nie zapamiętuje tak naprawdę „komórki A2”. Zapamiętuje kierunek i odległość, na przykład „komórkę oddaloną o dwie kolumny w lewo”.

Nazywa się to odwołaniem względnym. Ponieważ odwołanie jest opisane względem położenia samej formuły, dostosowuje się za każdym razem, gdy formuła zostaje skopiowana w nowe miejsce. To właśnie ta właściwość sprawia, że wypełnianie działa tak dobrze.

Proszę przygotować dwie kolumny liczb:

• A2 = 100, B2 = 50
• A3 = 200, B3 = 80

W komórce C2 proszę wpisać =A2+B2. Zostanie wyświetlona wartość 150. Następnie proszę skopiować C2 do C3. Wynikiem będzie 280, ponieważ formuła zmieni się na =A3+B3.

Nie trzeba było niczego edytować. Odwołania podążyły za formułą o jeden wiersz w dół.

Podczas kopiowania formuły w dół zmieniają się tylko numery wierszy. Litery kolumn pozostają bez zmian.

Punkt wyjścia to =A2+B2:

• O jeden wiersz w dół otrzymujemy =A3+B3
• O dwa wiersze w dół otrzymujemy =A4+B4
• O trzy wiersze w dół otrzymujemy =A5+B5

Kolumny A i B nigdy się nie przesuwają, ponieważ formuła nie jest przenoszona w bok.

Kopiowanie formuły w bok zmienia litery kolumn, a numery wierszy pozostawia bez zmian.

Załóżmy, że w komórce B2 znajduje się =B1*2. Proszę skopiować ją w prawo do C2 i D2:

• W C2 otrzymamy =C1*2
• W D2 otrzymamy =D1*2

Wiersz pozostaje równy 1, ponieważ formuła została przesunięta tylko w poprzek, a nie w dół. Przy jednoczesnym przesunięciu w obu kierunkach obie części zmieniają się razem.

Pomocny model myślowy jest następujący: odwołanie względne przechowuje przesunięcie względem komórki z formułą.

Jeśli w C2 znajduje się =A2, przesunięcie wynosi „dwie kolumny w lewo, ten sam wiersz”. Po skopiowaniu tej formuły do C5 przesunięcie zostaje zastosowane względem nowego położenia: dwie kolumny w lewo od C5, w tym samym wierszu, czyli A5.

Strzałka między formułą a jej celem zawsze zachowuje tę samą długość i kierunek.

Wyobraźmy sobie fakturę obejmującą 1000 wierszy. W każdym wierszu trzeba pomnożyć ilość przez cenę. Bez odwołań względnych trzeba byłoby ręcznie napisać 1000 różnych formuł.

Z odwołaniami względnymi wystarczy raz wpisać =A2*B2 i wypełnić komórki w dół. Każdy wiersz automatycznie odwoła się do własnej ilości i ceny. Jedna formuła, tysiąc poprawnych wyników i zero literówek.

Odwołania względne zmieniają żmudne zadanie w jedno podwójne kliknięcie.

Mogą Państwo sprawdzić to samodzielnie. Po wypełnieniu komórek należy kliknąć dowolną ze skopiowanych komórek i spojrzeć na pasek formuły u góry.

Proszę skopiować =A2+B2 o pięć wierszy w dół, a następnie kliknąć ostatnią komórkę. Pasek formuły pokaże =A7+B7, a nie =A2+B2. Każda komórka zawiera własną, dostosowaną wersję formuły.

Przechodzenie przez kolumnę i odczytywanie paska formuły to najlepszy sposób na zrozumienie, jak działa kopiowanie.

Dostosowanie względne jest zwykle tym, czego Państwo potrzebują, ale nie zawsze. Załóżmy, że każdy wiersz powinien być mnożony przez jedną stawkę podatku znajdującą się w komórce E1.

Jeśli wpiszą Państwo =C2*E1 i wypełnią komórki w dół, część E1 przesunie się do E2, E3, E4 i tak dalej, trafiając do pustych komórek. Wynik będzie niepoprawny.

Komórka C2 powinna przesuwać się w dół, ale E1 ma pozostać zablokowana. Wymaga to użycia odwołania bezwzględnego, które omówiono w następnym kursie.

Oto krótkie spojrzenie na rozwiązanie. Umieszczenie znaku dolara przed częścią odwołania blokuje ją, uniemożliwiając jej przesuwanie.

Proszę wpisać =C2*$E$1 i wypełnić komórki w dół. Teraz C2 przesunie się do C3, C4 i tak dalej, a $E$1 za każdym razem pozostanie przypisane do stawki podatku.

Wkrótce opanują Państwo tę sztuczkę ze znakiem dolara. Na razie wystarczy wiedzieć, że jest ona dostępna.

Wszystko opisane tutaj działa identycznie w programach Microsoft Excel i Google Sheets. W obu przypadkach odwołania są domyślnie względne, podczas wypełniania w dół przesuwają się numery wierszy, a podczas wypełniania w poprzek — litery kolumn.

Składnia blokowania za pomocą znaku dolara jest również taka sama w obu aplikacjach. Umiejętności zdobyte podczas kopiowania formuł w jednej aplikacji można bezpośrednio wykorzystać w drugiej.

Jeśli wypełniona kolumna pokazuje nieprawidłowe liczby, przyczyną jest niemal zawsze odwołanie, które przesunęło się, choć powinno pozostać na miejscu.

Należy kliknąć nieprawidłowo działającą komórkę i odczytać pasek formuły. Proszę zadać sobie pytanie: czy odwołanie wskazuje właściwe miejsce? Jeśli odwołanie, które powinno być stałe, przesunęło się, znaleźli Państwo błąd — rozwiązaniem jest znak dolara.

Znają już Państwo mechanizm działania wypełniania:

• Odwołania są domyślnie względne i przechowują przesunięcie względem komórki z formułą.
• Wypełnianie w dół zmienia numery wierszy, a wypełnianie w poprzek zmienia litery kolumn.
• To automatyczne dostosowanie pozwala jednej formule obsługiwać tysiące wierszy.
• Gdy odwołanie musi pozostać na miejscu, można je zablokować za pomocą znaku dolara. Tego nauczą się Państwo w następnym kursie.

Gdy wypełniona kolumna wygląda nieprawidłowo, należy sprawdzić pasek formuły.