Adresy Względne i
Bezwzględne
Przykłady opisane w tej lekcji
dostępne są w arkuszu Excela: Adresy Wzgledne
i Bezwzgledne.xls, tylko ich samodzielne przerobienie daje gwarancję
zapamiętania tej lekcji.
Przykłady dla wszystkich lekcji
szkolenia Excel 2003: ExcelSzkolenie.pl
Cwiczenia Excel 2003.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
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.
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
bezwzględnym ponieważ zawsze będzie odnosił się do tej samej komórki, natomiast
adres bez tych symboli względnym, 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ł ciągnięty 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.
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
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
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 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łady 5. i 6.
(Arkusz:
‘Adresy
Przeprowadzenie kilku samodzielnych
ćwiczeń z adresami względnymi i bezwzględnymi 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!
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 poprzedniej lekcji.
Powodzenia!
Przykłady 7. 8. 9.
(arkusz ‘Adresy
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.
Poniżej prawidłowe rozwiązanie:
