Adresowanie komórek

Adresowanie komórek (odwołania)
1. Adresy względne, bezwzględne i mieszane
Adres identyfikuje komórkę lub zakres komórek w arkuszu i instruuje program,
gdzie szukać wartości lub danych, które mają być uŜyte w formule. Adres składa się
z nazwy kolumny i numeru wiersza, np. D5, który moŜemy uŜyć we wzorze, np.:
=A5*G5/A20
=C4^3
Ponadto podczas kopiowania cała formuła ulega zmianie, dopasowując się do
nowego połoŜenia. Taki sposób adresowania nazywamy adresowaniem względnym.
Dzięki niemu nie musimy wprowadzać od nowa formuł do sąsiednich komórek.
MoŜemy przeciągnąć krzyŜyk w prawym dolnym rogu komórki z formułą
w dowolnym kierunku, kopiując wzór do zaznaczonego obszaru. Kopiując w prawo
lub w lewo zmienia się w adresie nazwa kolumny, natomiast w dół lub w górę – numer
wiersza.
Bywają jednak przypadki, Ŝe nie chcemy by adres się zmieniał podczas
kopiowania. MoŜemy taką komórkę zablokować wstawiając znak $ przed kolumną
i przed wierszem, np. $A$6. Kopiowanie takiego adresu w dowolnym kierunku nie
zmieni odwołania do zablokowanej w ten sposób komórki. Ten rodzaj adresowania
komórek nazywamy adresowaniem bezwzględnym.
Spotyka się takŜe adresowanie mieszane, np. A$5 lub $B4, w którym blokowana
jest tylko kolumna lub tylko wiersz, zaleŜnie od potrzeb. Na rysunku 41 moŜna
porównać, jak zachowują się formuły podczas kopiowania do sąsiednich komórek
przy zastosowaniu róŜnych typów adresowania. W podpunkcie a) widać zmianę
numerów wierszy przy kopiowaniu w dół, i zmianę liter kolumn przy kopiowaniu
w prawo. W przykładzie b) po skopiowaniu adres z zablokowaną kolumną i wierszem
nie zmieniał się, a w c) zmienia się częściowo.
a) względne
b) bezwzględne
c) mieszane
Rys. 1. Przykład zachowania się adresów po kopiowaniu przy uŜyciu trzech rodzajów blokowania
Klasycznym przykładem zastosowania adresowania mieszanego jest wypełnienie
jedną formułą całej tabliczki mnoŜenia.
Ćwiczenie
Wykonaj tabliczkę mnoŜenia 100x100, adresując w sposób mieszany komórki,
tak by jedną skopiowaną formułą wypełnić całą tablicę. Sformatuj odpowiednio
arkusz.
Rozwiązanie
Tworzymy arkusz jak na rys. 42. Formatujemy wiersz nagłówkowy i pierwszą
kolumnę wprowadzając automatycznie kolejne liczby z zakresu od 1 do 100 od
komórki B1 w prawo i od A2 w dół, pozostawiając komórkę A1 pustą.
Rys. 2. Arkusz „Tabliczka mnoŜenia” – poprawna formuła w komórce B2
Wprowadzamy do komórki B3 formułę =A2*B1. Teraz musimy poblokować
kolumny lub wiersze, by podczas kopiowania uzyskać właściwy efekt. Najlepiej
zawsze przeanalizować: najpierw kopiowanie w dół, potem w prawo i dopiero wtedy
gotowy wzór kopiować.
1. Kopiowanie w dół
Podczas kopiowania w dół zmieniają się numery wierszy; wiersze z pierwszego
składnika działania - A1 - mają się zmieniać, poniewaŜ kolejne liczby w kolumnie A
mnoŜone będą przez B1. Komórka B1 natomiast ma być stała, a więc wiersz pierwszy
zablokujemy znakiem $ (Shift+4). Formuła ma aktualnie postać =A2*B$1.
2. Kopiowanie w prawo
Teraz badamy, co zablokować dla kopiowania w prawo. Będziemy mnoŜyć
liczby z kolumny A przez liczby z wiersza 1, musimy więc kolumnę A zablokować
w formule. Będzie ona miała teraz postać =$A2*B$1.
Kopiujemy gotowy wzór najpierw w dół, a potem całość w prawo. ZwęŜamy
kolumny, by tabliczka mnoŜenia była czytelna.
2. Odwołania do innych arkuszy i skoroszytów
MoŜna uŜywać takŜe odwołania do danych, znajdujących się w innych arkuszach
tego samego skoroszytu, a takŜe do danych w innych skoroszytach. Wystarczy
wybierać myszą arkusze i otwarte pliki – odwołania do odpowiednich komórek same
się pojawią w edytowanej komórce.
=Arkusz1!A1
='Lista plac'!B4
Jeśli nazwa arkusza zawiera spacje lub inne znaki nie będące symbolami
alfanumerycznymi, naleŜy ująć ją w apostrofy, jak w drugim przykładzie.
Odwołania do komórek w innych skoroszytach są zwane łączami.
=[Zeszyt1]Arkusz3!$A$1
=[Planety.xls]układ!$B$4
='[Planety.xls]Układ Słoneczny'!$F$2 (występują spacje)
3. Nazwy w formułach
Zamiast etykiet kolumn i wierszy w arkuszu do opisu połoŜenia komórki słuŜy
moŜna tworzyć opisowe nazwy. Jest to wyraz lub ciąg znaków, który reprezentuje
komórkę, zakres komórek, formułę lub wartość stałą.
Aby nazwać komórkę lub zakres komórek naleŜy je zaznaczyć a następnie
w „polu nazwy” wpisujemy wybraną nazwę (rys.43).
pole nazwy
Rys. 3. Nadawanie nazwy bloku komórek
Zadanie
Wykonaj uproszczoną listę płac - arkusz jak na rys. 44. Sformatuj i wypełnij
danymi kolumny D, E i F, a następnie wpisz formuły, by obliczyć brutto (stawka*ilość
godzin+premia), kwotę podatku (brutto*0,18), netto (brutto - kwota podatku). Zastosuj
funkcje w celu obliczenia sumy, średniej, minimalnej i maksymalnej płacy netto.
Dodaj potem do tabeli trzy kolumny: odchylenie od średniej (róŜnica pomiędzy płacą
danego pracownika a obliczoną średnią płac), procentowy udział płacy pracownika
w całkowitych zarobkach zakładu (płaca/suma*100) oraz płacę w euro wg kursu
podanego w komórce C14. Zadania te wykonasz przy uŜyciu adresowania mieszanego.
Przemyśl, które wiersze trzeba zablokować.
Sformatuj ładnie arkusz i zachowaj, gdyŜ będzie nam potrzebny w innych
zadaniach.
Rys. 4. Lista płac