Jakiś czas temu wspominałem o tym, że pojedynczej komórce można nadać swoją własną nazwę. Jest to świetne rozwiązanie, gdyż jak już wcześniej pisałem dużo czytelniej wygląda formuła =masa/wzrost^2 niż =B3/B5^2.
Tamten wpis tylko lekko dotknął zalążka jakim jest nadawanie nazw komórkom czy zakresom. Tutaj rozszerzę ten temat.
Pobierz przykładowy plik z ćwiczeniami
Nadawanie nazw zakresowi komórek
Rzuć okiem na poniższy rysunek:
Jeśli chciałbym w komórce B7 obliczyć sumę przychodów, to mógłbym użyć funkcji SUMA:
=SUMA(B2:B6).Jednak taka formuła jest mało czytelna (szczególnie, gdy suma przychodów znajduje się dwa ekrany dalej niż mamy wypisane przychody). Dlatego też zaznaczyłem zakres komórek B2:B6 a następnie na pasku adresu wpisałem Przychody. Dzięki temu w komórce B7 będę mógł wpisać czytelniejszą formułę:
=SUMA(Przychody)Oczywiście jeśli np. pomiędzy 3 a 4 wierszem wstawię nowy wiersz i wpiszę kolejny rodzaj przychodu, to program Excel automatycznie rozciągnie ten zakres z B2:B6 a B2:B7 (wyjątkiem jest sytuacja, gdy po 6 wierszu chcesz wstawić nowy wiersz – wtedy Excel nie rozszerzy zakresu na nowo dodaną komórkę B7).
Edycja zakresów
Czasem okazuje się, że chcemy ręcznie zwiększyć/zmniejszyć (albo nawet usunąć) utworzony zakres. W tym celu najprościej będzie nacisnąć kombinację klawiszy Ctrl + F3 lub z karty Formuły wybrać z grupy poleceń Nazwy zdefiniowane polecenie Menedżer nazw.
Pojawi się okno dialogowe Menedżer nazw, w którym możemy podglądnąć jakie są zdefiniowane nazwy, możemy niektóre nazwy usunąć, lub edytować. Myślę, że nie ma co się tutaj rozwodzić – trzeba chwilę się pobawić, aby poznać w praktyce to narzędzie.
Automatyczne tworzenie nazw
Rzuć okiem na taki oto arkusz
Pewnie domyślasz się – co będę chciał zrobić. Otóż zakresowi komórek B2:B6 będę chciał nadać nazwę styczeń, C2:C6 będę chciał nadać nazwę luty i tak aż do zakresu M2:M6, któremu będę chciał nadać nazwę grudzień.
A jeszcze sobie utrudnijmy sprawę: zakresowi komórek B2:M2 będę chciał nadać nazwę Kraków, …, aż do zakresu B6:M6, któremu będę chciał nadać nazwę Rzeszów.
Strasznie dużo roboty. Aż 17 zakresów należy przeklikać, aby nadać im swoje nazwy. Jednak tutaj mamy do czynienia z prostym narzędziem. Otóż w pierwszej kolejności zaznacz całą tabelkę. Następnie na karcie Formuły obok polecenia Menedżer nazw znajduje się polecenie Utwórz z zaznaczenia. Pojawi się okno dialogowe Tworzenie nazw z zaznaczenia. Ponieważ nasze nagłówki (miesiące) znajdują się w górnej części tabeli jak i z lewej strony (oddziały) to domyślnie zostawiam zaznaczone opcje Górny wiersz i Lewa kolumna.
Po automatycznym utworzeniu nazw mogę włączyć menedżera nazw, aby sprawdzić, czy wszystko jest OK
Definiowanie własnych stałych
Możemy Menedżera nazw wykorzystać do utworzenia własnych stałych. Przykładowo mamy pewne takie wartości, które rzadko zmieniają swoją wartość, ale dość często w różnych miejscach się do niech odwołujemy. Takie wartości to mogą być np.
- Stawka podatku VAT
- Stawka podatku dochodowego
- Kurs euro
- Wysokość miesięcznej premii w firmie
Aby utworzyć własną stałą należy na karcie Formuły wybrać polecenie Definiuj nazwę znajdujące się w grupie poleceń Nazwy zdefiniowane (ewentualnie w oknie dialogowym Menedżer nazw można kliknąć w przycisk Nowy).
Pojawi się okno dialogowe Nowa nazwa. Należy w tym oknie podać nazwę naszej stałej a także jej wartość. Opcjonalnie możemy zmienić zasięg widoczności naszej stałej (domyślnie jest wybrany Skoroszyt ale możemy wybrać, że stała jest widoczna tylko w wybranym przez nas arkuszu).
Następnie w naszych formułach zamiast pisać 23% możemy używać nazwy naszej stałej: VAT.
Formuły
Oprócz własnych stałych możemy też definiować własne zmienne, do których obliczenia będą wywołane standardowe funkcje Excela (jak również niestandardowe funkcje użytkownika napisane w VBA lub też możemy używać nazw funkcji z Makr Excel4.0). Przykładowo zdefiniowałem własną nazwę o nazwie Obecny_ROK, która odwołuje się do prostej formuły: =ROK(DZIŚ())
Dzięki temu w dowolnym miejsc skoroszytu zamiast pisać „skomplikowaną” formułę:
=ROK(DZIŚ())Mogę wpisać
=Obecny_ROKOwszem, tutaj oszczędność czasu jest niewielka, ale w podobny sposób można definiować nieco bardziej skomplikowane formuły.
Odwołania względne i bezwzględne
Ciekawe, czy ktoś pamięta czasu denominacji (która miała miejsce w latach 1995-1997). Otóż aby policzyć cenę „w nowych złotówkach” należało podzielić cenę w „starych złotówkach” przez 10 000. Nawet krążyło takie hasło: Zakryj palcem zera cztery, będzie pieniądz nowej ery!
Oto prosty arkusz zawierający wysokość pensji minimalnej w poszczególnych latach (przed denominacją).
WAŻNE: umieść kursor w komórce C2. Następnie na karcie Formuły w grupie poleceń Nazwy zdefiniowane wybierzmy polecenie Definiuj nazwę. Pojawi się okno dialogowe Nowa nazwa. W polu Nazwa wpiszmy przykładową nazwę np. Denominacja a w polu Odwołuje się do prostą formułę: =B2/10000.
I teraz mała uwaga: ponieważ podczas tworzenia tej nazwy kursor znajdował się w komórce C2 a formuła odwołuje się do komórki B2 to nasza nazwa działa w ten sposób, że do obliczeń nie bierze „na sztywno” wartości z komórki B2, tylko tak jakby jedną na lewo względem siebie.
Zresztą wpisz w komórce C2 odwołanie do naszej nazwy czyli =Denominacja. A następnie skopiuj tę formułę do pozostałych komórek. Efekt powinien być jak na poniższym rysunku:
A czy Ty znasz jakieś ciekawe zastosowania Menedżera nazw?
