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.

Zobacz również:

Jak nadać nazwy zakresom danych?
Jak wstawić logo lub grafikę do wydruku?
Jak policzyć odchylenie standardowe próbki?
Jak udostępnić pracownikom edycję części arkusza?
Jak zmienić domyślną liczbę otwieranych arkuszy?

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