Adresy Względne i
Bezwzględne
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 2007: ExcelSzkolenie.pl
Cwiczenia Excel 2007.zip
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.
W przypadku formuł należy jednak uważać przy
ich kopiowaniu.
Zrozumienie i biegłe opanowanie adresowania
względnego i bezwzględnego jest niezbędne w pracy z Excelem.
Adresowanie bezwzględne bywa też często
nazywane absolutnym 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
2008 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 autowy peł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ć.
Powinniśmy więc 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żą.
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 2008 roku przez kurs euro z 2008.
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 wierszy 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 2006 i 2007 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:
= NowaWartość / 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 udził 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ł ‘06” i „udział ‘07”
Wzrost w pln to po prostu różnica pomiędzy
sprzedażą w 2007 i 2006. 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 tablicy.
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!
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.