Jak adresować komórki w formułach – adresowanie względne

Problem

Jak adresować komórki w formułach – adresowanie (odwołanie) względne.

Rozwiązanie

Pisząc formuły w Excelu bardzo często odwołujemy się do innych komórek, aby skorzystać ze znajdujących się w nich danych. Pytanie: Czy za każdym razem stosujemy prawidłowe odwołanie?

Poniżej przedstawiam opis i przykłady dotyczące, najczęściej stosowanego, odwołania względnego.

Odwołanie względne (A1 lub W[1]K[1])

Określa adres komórki docelowej w odniesieniu do adresu (pozycji) komórki źródłowej. W programie Excel stosowane jest domyślnie.

Najlepiej będzie pokazać to na przykładzie. Na poniższym obrazku widać kilka komórek wypełnionych liczbami.

Przykładowe dane liczbowe

Spróbujemy teraz napisać formułę, w jednej z komórek (np. C2), która będzie sumowała dwie komórki po jej lewej stronie (A2 i B2). Następnie, aby się nie narobić, spróbujemy skopiować tą formułę do pozostałych komórek z sumą.

Kliknij komórkę C2 i wpisz do niej formułę:

{code}

Jeżeli masz włączone odwołania A1 (standardowo włączone):

=A2+B2

Jeżeli masz włączone odwołania W1K1 (nagłówki kolumn opisane liczbami, a nie literami):

=WK[-2]+WK[-1]

{/code}

a następnie wciśnij Enter. Suma powinna pojawić się w komórce C2 (poniższe przykłady będą dla odwołań A1).

Sprawdźmy teraz czy uda nam się skopiować tę formułę do pozostałych komórek z sumami tak aby pojawiły się w nich prawidłowe sumy.

Mając zaznaczoną komórkę C2 (tą do której wpisaliśmy przed chwilą naszą formułę) kliknij kombinację klawiszy Ctrl+C (kopiowanie). Po skopiowaniu formuły do schowka zaznacz pozostałe komórki z sumami (w naszym przykładzie to komórki: C3, C4, F6, F7) i naciśnij kombinację klawiszy Ctrl+V (wklejanie).

Czy takiego wyniku się spodziewaliśmy?

Dokładnie tak! Pytanie skąd wiedzieliśmy, że Excel, po skopiowaniu formuły, zmieni nasze formuły, dostosowując je do nowych komórek (czyli do innych danych do sumowania)?

Żeby móc na nie odpowiedzieć musimy wiedzieć jak dokładnie działa odwołanie względne. Najprościej, jak zwykle, będzie to zrobić na przykładzie.

Wypełnijmy cztery wiersze i trzy kolumny przykładowymi cyframi/liczbami. Następnie kliknijmy komórkę B6 i wpiszmy do niej najprostszą formułę wyciągającą wartość, np. z komórki A2.

{code}

=A2

{/code}

efekt powinien być podobny do poniższego:

Skopiujmy (Ctrl+C) teraz formułę z komórki B6 i wklejmy ją do komórki D7 (Ctrl+V). Jaką wartość powinniśmy otrzymać w komórce D7 i dlaczego?

Ta wartość to: 11 (z komórki C3). Dzieje się tak dlatego, że przy odwoływaniu względnym Excel oblicza o ile wierszy i o ile kolumn przesunęliśmy się do komórki docelowej z komórki bazowej – patrz rysunek poniżej dla naszego przykładu. 

Jak widać odwołanie docelowe tworzone jest za pomocą dodawania lub odejmowania odpowiedniej liczby wierszy i liczby kolumn o jaką przesunęliśmy się kopiując formułę z komórki bazowej.

Poniżej kilka przykładów:

Formuła w komórce B3 Opis Formuła w komórce docelowej
=A2 Kopiujemy tą formułę do komórki C4. Najpierw sprawdzamy o ile przesunęliśmy się na wierszach i kolumnach pomiędzy komórkami. Mamy więc kolumnę: B (bazowa) i przesuwamy się o 1 kolumnę w prawo do kolumny C (docelowa). Następnie przesuwamy się o 1 wiersz w dół (od nr 3 do nr 4). Teraz Excel bierze formułę w komórce bazowej i każde odwołanie występujące w formule zamienia na odwołanie o 1 kolumnę i 1 wiersz „dalsze”, co w naszym przypadku daje nam zmianę z A2 (bazowa) na B3 (docelowa). =B3
=B5+D6 Kopiujemy tą formułę do komórki A1. Czyli z komórki bazowej (B3) przesunęliśmy się o 1 kolumnę w lewo oraz o 2 wiersze do góry do komórki docelowej (A1). Co oznacza, że musimy zmienić nasze wszystkie odwołania względne uwzględniając te wartości: B5 zmieni się na A3, a D6 zmieni się na C4. =A3+C4
=A5 Kopiujemy tą formułę do komórki A1. W tym przykładzie pokażę jaki błąd pojawia się podczas błędnego skopiowania odwołań względnych. Jak widać, chcemy skopiować formułę przesuwając ją z B3 (komórka bazowa) do A1 (komórka docelowa) czyli przesunięcie o 1 kolumnę w lewo oraz o 2 wiersze w górę. Chcąc teraz uzyskać prawidłową formułę docelową po prostu nie mamy takiej możliwości, ponieważ nie da się przesunąć o 1 kolumnę w lewą stronę kolumny A, tj. nie ma żadnej litery w alfabecie „przed literą A”. Dlatego też w tym wypadku dostaniemy błąd. =#REF!

Jak widać nie jest to prosty temat do wytłumaczenia „na piśmie”, jednak poświęcając mu kilkanaście minut czasu, można w przyszłości zaoszczędzić kilkanaście godzin i/lub dni pracy 🙂

Zobacz również:

Jak dodać nowy arkusz?
Jak włączyć wyszukiwanie pionowe?
Jak umieścić tekst pod kątem?
Jak ustawić tło arkusza?
Jak wstawić pojedynczą komórkę do arkusza?

Janusz Dreziński

Od ponad 20 lat związany z branżą IT. Pasjonat technologii Microsoft z naciskiem na pakiet Office. Od kilku lat pomaga innym prowadząc portal Office.info.pl oraz rozwiązując ich codzienne problemy dotyczące przede wszystkim programów Microsoft Excel i Word. Jego hobby to żeglarstwo oraz siatkówka, a ulubione miejsce to... Mazury.

Pokaż pozostałe moje porady