Adresy Względne i Absolutne

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Adresy względne i Bezwzględne.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Kopiowanie formuł jest niezwykle użyteczne w Excelu. Formuła raz napisana może być skopiowane w ciągu kilku sekund do wielu komórek i właściwe wartości zostaną obliczone w każdej z nich.

Zrozumienie i biegłe opanowanie adresowania względnego i absolutnego jest niezbędne w pracy z Excelem.

Adresowanie absolutne bywa też często nazywane bezwzględnym w poniższej lekcji będę używał obu tych nazw zamiennie.

Przykład 1.

(arkusz ‘Adresy 1’)

W poniższym przykładzie chcielibyśmy przeliczyć przychody w zł w rozbiciu na 3 sklepy i 10 produktów, na walutę euro.

Przychody w euro powinny znaleźć się w zielonych polach.

Wprowadzamy formułę przeliczającą dla pierwszego produktu w pierwszym sklepie (sprzedaż podzielona przez kurs euro z 2013 roku).

Tak napisana formuła poprawnie obliczy wartość w euro dla sprzedaży pierwszego produktu w pierwszym sklepie.

Jednak kiedy skopiujemy ją jedno pole w dół będzie odnosić się do produktu 2 (co jest prawidłowym odwołaniem), ale także komórka, z której formuła ciągnie kurs euro, przesunie się o jedną komórkę w dół.

Tak skopiowana formuła zwróci wartość błędu dzielenia przez zero (w komórce J6 nic nie jest wpisane, czyli jest tam zero).

Podobnie jeśli skopiujemy taką formułę w prawo, wartość sprzedaży produktu 1 w sklepie 2, będzie pobierana właściwie, jednak adres z którego ciągnięty jest kurs euro zmieni się na K5, w której to komórce nic nie ma i ponownie formuła zwróci wartość błędu dzielenia przez zero.

Dodatkowo na poniższym ekranie widać ikonę ostrzeżenia o błędzie (na lewo od komórki G5) i ikonę opcji autowypełnienia (na prawo i w dół od komórki G5), w zależności od ustawień programu ikony te mogą się pokazać, na tym etapie nauki sugeruje je ignorować.

Skasujmy błędne formuły z komórek G5 i F6.

Powinniśmy naszą formułę zmienić tak by adresy komórek z wartościami sprzedaży zmieniały się wraz z kopiowaniem formuły a adres komórki z kursem euro pozostał zawsze taki sam.

W tym celu dwukrotnie klikamy w komórkę F5, wchodząc tym samym w formułę, która się w niej znajduje. Ustawiamy migający kursor (mała pionowa migająca kreska) przed, po lub w środku adresu J5 i wciskamy klawisz F4 na klawiaturze. Wciśnięcie klawisza F4 powoduje dodanie znaków $ do adresu komórki, oznaczają one że adres ten nie będzie się zmieniał podczas kopiowania. Znak $ przed literą oznaczającą kolumnę powoduje, że kolumna nie ulegnie zmianie podczas przegrywania analogicznie znak $ przed numerem wiersza powoduje, że numer ten pozostanie taki sam bez względu na to gdzie formuła zostanie skopiowana.

Tak przygotowaną formułę możemy skopiować do wszystkich zielonych pól tabeli ze sprzedażą. Najlepiej najpierw w prawo, a później klikając w czarny kwadracik w dół.

Wszystkie pola pobierają dane z innej (odpowiedniej) komórki ze sprzedażą w zł i tego samego kursu euro znajdującego się w komórce J5 (zapisanej jako $J$5).

Znak $ może też zostać wprowadzony poprzez ustawienie kursora w odpowiednim miejscu, przytrzymanie klawisza Shift i wciśnięcie cyfry 4.

Używanie klawisza F4, szczególnie po nabraniu wprawy pozwoli znacznie przyspieszyć pracę.

Wciskając klawisz F4 kilkukrotnie będziemy mogli wprowadzić różne kombinacje znaków $ w adresie komórki:

- pierwsze wciśnięcie F4 dodaje znaki $ do wiersza i kolumny $J$5

- drugie wciśnięcie F4 dodaje znak $ tylko do wiersza J$5

- trzecie wciśnięcie F4 dodaje znak $ tylko do kolumny $J5

- czwarte wciśnięcie F4 przywraca oryginalny adres bez $ J5

Każde kolejne powoduje ponowne przejście przez powyżej opisany cykl.

Adres z symbolami $ nazywany jest absolutnym lub bezwzględnym ponieważ zawsze będzie odnosił się do tej samej komórki wiersza lub kolumny, natomiast adres bez tych symboli względnym gdyż zmienia się podczas kopiowania.

Przykład 2.

(arkusz ‘Adresy 1’)

W przykładzie drugim tabelę ze sprzedażą w zł dla 10 produktów na przestrzeni 3 lat chcielibyśmy przeliczyć na euro. W każdym z tych trzech lat kurs euro był inny i znajduje się on w tabelce z prawej strony tabeli ze sprzedażą.

Naszym zadaniem jest przeliczenie sprzedaży w zł na euro używając tylko jednej formuły, którą skopiujemy na cały zielony obszar tabeli ze sprzedażą.

Zaczniemy od wprowadzenia formuły dzielącej sprzedaż produktu 1 w 2013 roku przez kurs euro z 2013.

Z poprzedniego przykładu wiemy już, że ze względu na użycie adresów względnych (bez znaków $) formuła taka nie może być skopiowana na całą tabelę.

Kursy euro znajdują się w komórkach J18, K18 i L18. Wszystkie one znajdują się w wierszu 18, mają natomiast różne kolumny.

Ponieważ chcielibyśmy aby dla kolejnych kolumn tabeli ze sprzedażą, kurs był pobierany z kolejnych kolumn tabeli z kursami, nie możemy wprowadzić znaku dolara przed literą oznaczającą kolumnę.

Ujmując to w innych słowach przegrywając formułę w prawo, adres z którego czerpany jest kurs euro, powinien także przesuwać się w prawo. Przegrywając natomiast formułę w dół, adres nie powinien przesuwać się w dół (bo pod kursami są puste pola i formuła zwróciłaby błąd dzielenia przez zero).

Aby zapobiec przesuwaniu się adresu w dół (zmienianiu się wierszy) musimy wprowadzić znak dolara przed numerem wiersza. A ponieważ chcemy aby adres przesuwał się w prawo nie wprowadzamy znaku $ przed literą będącą oznaczeniem kolumny.

Znak dolara blokuje tylko to co znajduje się za bezpośrednio za nim, w adresie J$18 zablokowany jest tylko wiersz, symbol $ nie ma wpływu na kolumnę będącą przed nim.

Tak wprowadzoną formułę możemy skopiować na całą tabelę.

Po skopiowaniu warto sprawdzić na jednym, lub kilku przykładach, czy na pewno formuły ciągną dane z właściwych komórek.

Możemy to zrobić dwukrotnie klikając lewym przyciskiem myszy na jednej z komórek. Jak widać powyżej komórki do których odwołuje się formuła zostały zaznaczone kolorowymi obwódkami.

Aby było możliwe wykonanie takiego zadania używając tylko jednej formuły tabela z danymi sprzedaży i tabela z kursami muszą mieć taki sam układ, w tym przykładzie zarówno sprzedaż dla kolejnych lat, jak i kursy dla kolejnych lat były ustawione w poziomie. Gdyby ich układ był inny, nie byłoby możliwe zastosowanie jednej formuły do wszystkich pól.

Przykład 3.

(arkusz ‘Adresy 1’)

W tym przykładzie chcemy przeliczyć sprzedaż dla 3 sklepów w 3 kolejnych latach z zł na euro. Lata ustawione są tym razem w pionie, kursy euro w tabelce obok mają ten sam układ.

Wprowadzamy formułę dzielenia.

Komórki z kursami euro znajdują się w komórkach K31, K32, K33. Adresy tych komórek mają taką samą kolumnę ale różne wiersze.

Nie chcemy aby kolumna zmieniała się podczas kopiowania formuły w prawo, ponieważ adres zmieniłby się z kolumny K na L i M, w których to kolumnach nic nie ma.

Chcemy natomiast aby zmieniał się numer wiersza i dla sprzedaży w kolejnych latach wykorzystywane były kursy euro dla odpowiednich lat.

W formule w adresie komórki z kursem euro wprowadzamy znak $ tylko przed literą K oznaczającą kolumnę.

Tak zmienioną formułę możemy skopiować na całą tabelę.

Kiedyś w powyższych przykładach używałem przeliczenia na dolary, ale ponieważ dość często na szkoleniach słyszałem pytanie: „Czy znaki $ są dlatego, że przeliczamy na dolary?”, zacząłem używać kursów Euro. J

Przykład 4.

(arkusz ‘Adresy 2’)

W przykładzie 4 wykonamy kilka najczęściej używanych działań w Excelu.

W kolumnach C i D mamy dane sprzedaży dla 10 brandów w pewnej firmie. W kolumnie E policzymy jaki jest wzrost lub spadek w procentach w stosunku do poprzedniego roku dla każdego z tych brandów a także dla całej sprzedaży.

Przed policzeniem wzrostu sprzedaży zsumujemy sprzedaż w 2013 i 2014 roku dla wszystkich brandów.

Korzystając z funkcji SUMA należy uważać aby nie dodać do wyników sprzedaży roku znajdującego się w nagłówku.

Aby policzyć procentowy wzrost lub spadek należy ‘Nową Wartość’ podzielić przez ‘Starą Wartość’ i od wyniku dzielenia odjąć 1. Czyli:

= Nowa Wartość / Stara Wartość -1

W naszym przykładzie będzie to =D5/C5-1

Formuła ta nie potrzebuje znaków $ ponieważ oba adresy mają się zmieniać kiedy będziemy przegrywać je w dół.

Kopiujemy formułę do poniższych komórek.

Formuła na udział procentowy to Wartość Sprzedaży brandu podzielona przez Sprzedaż Całkowitą. Czyli:

= Sprzedaż Brandu / Sprzedaż Całkowita

A w naszym przykładzie = C5/C$15

W adresie C$15 użyliśmy symbolu $ aby dla kolejnych brandów ich sprzedaż cały czas była dzielona przez sprzedaż całości.

Tak przygotowaną formułę można skopiować na wszystkie komórki w kolumnach „udział ‘13” i „udział ‘14”

Wzrost w pln to po prostu różnica pomiędzy sprzedażą w 2014 i 2013. Nie używamy tu znaku $ ponieważ dla każdego z brandów dane powinny być ciągnięte z innych komórek.

Formułę =D5-C5 możemy skopiować do poniższych komórek.

W ostatniej kolumnie tej tabeli musimy przeliczyć ‘wzrost w pln’ na ‘wzrost w EURO’, kurs podany jest w komórce H2 i ponieważ każda z komórek obliczających wzrost w EURO powinna odnosić się do H2 i adres ten nie może się zmienić podczas kopiowania w dół przed oznaczeniem wiersza ‘2’ wpisujemy $ i tak przygotowaną formułę kopiujemy w dół.

Wynikiem opisanych wyżej działań powinna być tabela z danymi taka jak pokazana poniżej.

Przykład 5.

(arkusz ‘Adresy 3’)

Przeprowadzenie kilku samodzielnych ćwiczeń z adresami względnymi i absolutnymi pozwoli Wam osiągnąć biegłość w posługiwaniu się nimi.

W przykładzie z Tabliczką Mnożenia formuła mnożenia jest już wprowadzona, należy tylko w odpowiednich miejscach adresów dodać symbole $ tak aby formułę tą można było skopiować do całej tabliczki. Należy pamiętać, że wprowadzamy znaki $ w tych częściach adresu, które nie chcemy, aby się zmieniały podczas kopiowania, te, które mają się zmieniać pozostawiamy bez $.

(Zadanie to jest jednym z używanych podczas testów kwalifikacyjnych sprawdzających znajomość Excela dla kandydatów do pracy, aby zostało zaliczone powinno być rozwiązane przy pierwszej próbie.)

Powodzenia!

Przykład 6.

(arkusz ‘Adresy 3’)

W przykładzie z Szachownicą w pierwszym polu szachownicy została wpisana funkcja ZŁĄCZ.TEKSTY, dzięki której w polu tym pojawiła się informacja, jakie jest to pole. Funkcja ZŁĄCZ.TEKSTY łączy dane znajdujące się w komórkach będących argumentami funkcji.

Przed skopiowanie tej funkcji do pozostałych pól należy dodać symbole $ w odpowiednie miejsca adresów.

Aby nie popsuć formatowania szachownicy należy kopiować prawym klawiszem myszy, tak jak zostało to opisane w lekcji ‘Kopiowanie’.

Powodzenia!

Po rozwiązaniu zadania cennym doświadczeniem może być porównanie tego rozwiązania z rozwiązaniem zadania z tabliczką mnożenia.

Przykłady 7. 8. 9.

(arkusz ‘Adresy 4’)

W tych przykładach dla zdobycia jeszcze większej biegłości w posługiwaniu się adresami absolutnymi sugerowałbym przeliczenie wartości w złotych na Euro.

Przykłady te tym różnią się od tych którymi zaczynaliśmy tą lekcję, że samodzielnie należy wybrać która tabela z kursami (pionowa czy pozioma) powinna zostać użyta.

Oczywiście zaglądanie do arkusza ‘Adresy 1’ świadczyć będzie o tym, że do biegłości nieco nam jeszcze brakuje. Powodzenia!