Microsoft Word - Eletter_119

:: 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

Otrzymasz gotowe nagłówki jak na rysunku 1.

Rys. 1. Przygotowane nagłówki

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ą.

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

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

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

Po zatwierdzeniu

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

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.

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))

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

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

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

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