Jak adresować komórki w formułach (adresowanie/odwołanie bezwzględne)?

Problem:

Jak adresować komórki w formułach (adresowanie/odwołanie bezwzglę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. Tworząc formułę obliczeniową używamy tych odwołań aby wykorzystać dane znajdujące się w określonej komórce. Jeśli takie formuły komórek chcemy kopiować do innych miejsc w arkuszu to może się nam przydać wiedza dotycząca odwołań bezwzględnych.

Odwołanie bezwzględne $A$1
Określa adres komórki źródłowej, który jest niezależny od bieżącego położenia. Aby zastosować takie odwołanie posługujemy sie symbolem $ (czyt. znak dolara).

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

  

Załóżmy, że wszystkie te liczby chcemy pomnożyć przez jedną wielkość – Vat, znajdującą się w komórce C3.

Utworzymy formułę obliczeniową dla pierwszej ceny:

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

{code}
A3*C3
{/code}

a następnie wciśnij Enter. Wynik powinien pojawić się w komórce B3.

Jeśli teraz po prostu skopiujemy tę formułe do komórek B4:B7 otrzymamy błąd. Każde wklejenie formuły spowoduje zmianę argumentów, np: w komórce B4 z A3*C3 powstanie A4*C4

Okazuje się, że komórka C4 jest pusta więc Excel domyślnie traktuje ją jako 0. Wynik będzie wyglądał tak:

Aby formuła działała poprawnie przy kopiowaniu nie może się zmienić adres komórki C3. Dlatego użyjemy odwołania bezwględnego.

W tym celu klikamy na komórkę B3 aby zmienić oryginalną formułę. Zamiast:

{code}
A3*C3 (odwołania względne)
{/code}

musimy wpisać:

{code}
A3*$C$3 (odwołanie mieszane: względne + bezwzględne)
{/code}

Używając symbolu $ niejako „zamrażamy” adres komórki. Tak więc inaczej niż w odwołaniu względnym – numer wiersza i oznaczenie kolumny nie ulegają zmianie przy kopiowaniu.

Mając zaznaczoną komórkę B3 (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 (w naszym przykładzie to komórki: B4, B5, B6, B7) i naciśnij kombinację klawiszy Ctrl+V (wklejanie).

Jak widać tym razem wszystko zostało obliczone poprawnie. Odwołanie bezwględne jest wygodne dlatego, że możemy kopiować formułę do sporego zakresu i Excel automatycznie zmieni jeden z adresów(A3 na A4, A5 itd.) natomiast drugi (ten adresowany bezwglęnie: $C$3) pozostanie taki sam.

Zauważmy, że symbol dolara wpisujemy zarówno przez oznaczeniem kolumny $C jak i wiersza $3 dzięki czemu żadna część adresu nie zmienia się przy kopiowaniu.

Przetestujmy działanie takiego adresowania:

W komórce A1 wpiszemy wartość 3. Następnie w komórce B1 wpiszemy formułę =$A$1. Zatem pojawi się w niej wartość 3. Jeśli taką formułę skopiujemy do kilku innych komórek wszędzie pojawi się identyczna formuła i taka sama wartość.

Tabela przedstawia zestawienie wyników kopiowania formuły z adresowaniem bezwzględnym.

  • Komórka źródłowa – komórka, z której kopiujemy zawartość.
  • Komórka docelowa – komórka, do której wklejamy zawartość.

Zauważmy, że możemy kopiować do innego wiersza (C5) lub do innej kolumny (B1) lub zmienić wiersz i kolumnę (D7), a wynik pozostanie identyczny.

Dodam, że bez symbolu $ kopiowana formuła zmieniałaby się odpowiednio w każdej z podanych komórek, a otrzymany wynik (w tym konkretnym przypadku) wyniósłby 0.

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