1

:: Trik 1. Nagłówki kolumn automatycznie oznaczane kolejnymi miesiącami
:: Trik 2. Informacja o stanie ochrony arkusza wyświetlana w komórce
:: Trik 3. Błyskawiczna poprawa zapisu numerów telefonu
:: Trik 4. Elastyczne odwołania do komórek za pomocą funkcji ADR.POŚR
:: Trik 5. Komunikat pojawiający się po kliknięciu komórki

Trik 1

Nagłówki kolumn automatycznie oznaczane kolejnymi
miesiącami

Pobierz plik z przykładem
http://www.excelwpraktyce.pl/eletter_przyklady/eletter119/1_Naglowki_miesiace.zip

Przyjmijmy, że potrzebujesz przygotować podobne raporty dotyczące ostatnich lat.
Każde z tych zestawień składa się z dwunastu kolumn odnoszących się do
poszczególnych miesięcy danego roku (noszą nazwy:

styczeń 2007

,

luty 2007

itd.).

Do tej pory, aby wstawić te nagłówki dla kolejnego okresu, musiałeś za każdym
razem ręcznie wpisywać oznaczenie pierwszego miesiąca i kopiować w prawo do
kolejnych komórek. Specjalnie dla Ciebie znaleźliśmy szybszy sposób.

=> W tym celu:
1. Do komórki A1 wpisz oznaczenie pierwszego roku (np.

2007

).

2. Do komórki A2 wprowadź następującą formułę:

=DATA($A$1;NR.KOLUMNY();1)

i skopiuj w prawo do jedenastu kolejnych komórek.
3. Zaznacz cały wiersz z formułami, klikając jego etykietę i wciśnij kombinację
klawiszy

Ctrl + 1

. W zakładce

Liczby

nadaj niestandardowy format:

mmmm rrrr

i zatwierdź, klikając

OK

.

Otrzymasz gotowe nagłówki jak na rysunku 1.

Rys. 1. Przygotowane nagłówki

2

Wyjaśnienie działania formuły:
Działanie formuły oparte jest na funkcji DATA, która zwraca datę na podstawie
podanych jej argumentów (rok;miesiąc;dzień). Rok określony jest w komórce A1, a
miesiąc wyznaczany przez funkcję NR.KOLUMNY. Wynikiem jej działania jest numer
kolumny, w której się aktualnie znajduje. Trzeci argument funkcji DATA nie ma
istotnego znaczenia, dlatego podano liczbę 1.

Wskazówka
Gdy teraz będziesz chciał wstawić nagłówki miesięcy dla kolejnego roku wystarczy,
że podasz go w komórce A1, a oznaczenia kolumn automatycznie się zaktualizują.

3

Trik 2

Informacja o stanie ochrony arkusza wyświetlana w komórce

Pobierz plik z przykładem
http://www.excelwpraktyce.pl/eletter_przyklady/eletter119/2_Stan_ochrony.zip

Może się zdarzyć, że skoroszyt, w którym aktualnie pracujesz, ma często
uaktywnianą i wyłączaną ochronę arkusza. Aby szybko sprawdzać, czy
zabezpieczenie jest w danej chwili aktywne, proponujemy zastosowanie sprytnej
formuły opartej na funkcji użytkownika przygotowanej w języku Visual Basic. Pozwoli
ona na wyświetlanie w komórce informacji o aktualnym stanie ochrony arkusza.

=> Aby taki efekt uzyskać, skorzystaj z funkcji VBA:
1. Otwórz Edytor Visual Basic, wciskając kombinację klawiszy

lewy Alt + F11

.

2. Z menu

Insert

wybierz polecenie

Module

.

3. W oknie kodu modułu, które się pokaże, wpisz kod funkcji widoczny na rysunku 1.
Znajdziesz go także w pliku z przykładem załączonym do tego triku.

Rys. 1. Kod funkcji VBA

4. Zamknij Edytor Visual Basic.

Od teraz możesz używać funkcji

CzyArkuszJestChroniony

. Zwraca ona wartość

PRAWDA, jeśli arkusz, w którym znajduje się komórka zawierająca tę funkcję, jest
chroniony i FAŁSZ, jeśli arkusz nie jest chroniony.

5. Do komórki, która ma zawierać tekst o stanie ochrony arkusza, wpisz taką
formułę:

=JEśELI(CzyArkuszJestChroniony();"Ochrona";"Brak ochrony")

Jeżeli arkusz jest chroniony, to komórka do której wpisałeś formułę, będzie zawierała
tekst:

Ochrona

. W przeciwnym razie zostanie wyświetlony tekst:

Brak ochrony

.

4

Rys. 2. W arkuszu wyświetlana jest informacja dotycząca zastosowanej ochrony

Uwaga
Wartość zwracana przez tę funkcję nie będzie się automatycznie zmieniać tuż po
operacji włączenia lub zdjęcia ochrony arkusza. Aby nastąpiło odświeżenie wartości
funkcji, należy zmienić dowolną komórkę lub wcisnąć klawisz

F9

.

5

Trik 3

Błyskawiczna poprawa zapisu numerów telefonu

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter119/3_Telefony_formatowanie.zip

W jednym z arkuszy znajdują się zestawienia danych teleadresowych. Niestety zapis
telefonów jest nieaktualny, ponieważ nie są poprzedzone numerem kierunkowym.
Chciałbyś je hurtowo poprawić bez konieczności przebudowy arkusza i wprowadzania
formuł? Proponujemy zastosować formaty niestandardowe.

Przykładowe dane przedstawia rysunek 1.

Rys. 1. Nieaktualny zapis numerów telefonów

Dodanie numeru kierunkowego

W pierwszej kolejności pokażemy Ci, w jaki sposób dodać numer kierunkowy.

=> W tym celu:
1. Zaznacz zakres komórek B2:B10 i skorzystaj ze skrótu klawiaturowego

Ctrl + 1

.

2. Przejdź do zakładki

Liczby

i zaznacz kategorię

Niestandardowe

.

3. W polu

Typ

wpisz następujący format:

"(022)"#

Rys. 2. Nadawanie formatu niestandardowego

6

Po zatwierdzeniu

OK

uzyskasz oczekiwany efekt. Telefony wyświetlane z numerem

kierunkowym.

Rys. 3. Sformatowane numery

Grupy cyfr rozdzielone myślnikami

Dla lepszej czytelności numerów warto w nich rozdzielić myślnikiem grupy cyfr.

=> Aby to zrobić:
1. Zaznacz komórki i ponownie otwórz okno dialogowe

Formatowanie komórek

.

2. W polu

Typ

wprowadź następujący format:

"(022)"###-##-##

Po zatwierdzeniu

OK

uzyskasz efekt jak na rysunku 4.

Rys. 4. Grupy cyfr rozdzielone myślnikiem

Zauważ, że formatowanie nie zmienia rzeczywistych wartości komórek, co jest
widoczne na pasku formuły na rysunku 4. Zmianie ulega jedynie sposób ich
wyświetlania.

7

Trik 4

Elastyczne odwołania do komórek za pomocą funkcji ADR.POŚR

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter119/4_Budowanie_odwolan.zip

Dysponujesz plikiem, który posiada wiele arkuszy z tabelami o identycznej budowie.
Chciałbyś otrzymać wartość z komórki innego arkusza, którego nazwę wpisałeś w
dowolnej komórce. Tak postawiony problem możesz rozwiązać za pomocą formuły
opartej na funkcjach ADR.POŚR i ZŁĄCZ.TEKSTY.

=> A zatem:
1. Jeśli tabela jest tak zbudowana, że wprowadzasz nazwy arkuszy (w przykładzie
będą to skróty nazw miesięcy) do komórki D1 i jako wynik chcesz uzyskać wartość
komórki E10 określonego arkusza, zastosuj taką formułę:

=ADR.POŚR(ZŁĄCZ.TEKSTY(D1;"!E10"))

Rys. 1. Wartość z komórki E10 arkusza Sty została odnaleziona

Wskazówka
Możesz także, oprócz nazwy arkusza, podać adres komórki, do której formuła
powinna „sięgnąć” po wartość. Wtedy cały adres podany zostanie dynamicznie
poprzez wpisanie nazwy arkusza do jednej komórki oraz adresu do drugiej. Formuła
wygląda wówczas tak, jak poniżej i zastosowana jest w sposób pokazany w dolnej
części rysunku 2.

=ADR.POŚR(ZŁĄCZ.TEKSTY(D6;"!";D7))

8

Rys. 2. Także ta formuła zwróciła poprawny wynik

Wyjaśnienie działania formuły:
Funkcja ADR.POŚR zwraca odwołanie, które wyznacza ciąg tekstowy określony w jej
pierwszym argumencie. Ciąg ten został zbudowany dzięki wykorzystaniu funkcji
ZŁĄCZ.TEKSTY. Wynikiem jej działania jest: Sty!E10

Wskazówka
Zamiast funkcji ZŁACZ.TEKSTY możesz stosować znak

&

.

9

Trik 5

Komunikat pojawiający się po kliknięciu komórki

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter119/5_Komunikat_wejsciowy.zip

Gdy projektujesz arkusz, który będzie uzupełniany przez Twoich współpracowników,
to ważne jest, aby przekazać im precyzyjną informację dotyczącą poprawnego
wypełniania konkretnych komórek. Do tego celu proponujemy wykorzystać
sprawdzanie poprawności danych. Po kliknięciu określonej komórki pojawi się małe
okienko z komunikatem, którego treść możesz bardzo łatwo zdefiniować.

=> W tym celu:
1. Zaznacz komórkę, po której kliknięciu ma się pojawić komunikat i z menu

Dane

wybierz polecenie

Sprawdzanie poprawności

(w Excelu 2007: uaktywnij kartę

Dane

i

w grupie poleceń

Narzędzia danych

wskaż

Poprawność danych

).

2. W wyświetlonym oknie dialogowym przejdź do zakładki

Komunikat wejściowy

.

3. W polu

Tytuł

wpisz tytuł komunikatu, np.

Uwaga!

4. Poniżej wprowadź treść informacji.

Rys. 1. Poprawnie wypełnione ustawienia komunikatu

5. Zatwierdź, klikając przycisk

OK

.

Od tego momentu po wybraniu komórki, na którą nałożone jest sprawdzanie
poprawności, pojawi się komunikat jak na rysunku 2.

10

Rys. 2. Komunikat został wyświetlony

Wskazówka
Sprawdzanie poprawności możesz nakładać na wiele komórek jednocześnie. Aby to
zrobić, zaznacz je przed uruchomieniem polecenia. Sprawdzanie poprawności możesz
także przenosić poprzez wypełnianie komórek.