1

:: Trik 1. Wykres przedziałów czasowych
:: Trik 2. Większa liczba warunków w formatowaniu warunkowym
:: Trik 3. Podsumowanie liczb niepoprawnie wprowadzonych do komórek
:: Trik 4. Podpis i dane kontaktowe wpisywane za pomocą formuły
:: Trik 5. Automatyczne formatowanie nowo dopisywanych wartości

Trik 1

Wykres przedziałów czasowych

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter113/1_Prezentacja_przedzialow.zip

Zostałeś poproszony o przygotowanie krótkiej prezentacji dotyczącej użytkowania
maszyn produkcyjnych w firmie. Na wykresie powinny się znaleźć oznaczenia
maszyn, a także lata, w których były one eksploatowane. W jaki sposób czytelnie
przedstawić takie porównanie?

Dane, na podstawie których ma powstać prezentacja, przedstawia rysunek 1.

Rys. 1. Daty rozpoczęcia i zakończenia użytkowania maszyn

Przygotowanie danych

Aby czytelnie przedstawić okresy użytkowania, powinieneś nieznacznie przebudować
tabelę.

=> W tym celu:
1. Do komórki D2 wpisz zwykłą różnicę dat z kolumn C i B:

=C2-B2

2. Formułę skopiuj do kolejnych komórek.
3. Na koniec ukryj kolumnę C.

Twój arkusz powinien teraz wyglądać jak na rysunku 2.

2

Rys. 2. Dane źródłowe są przygotowane

Budowa wykresu

=> Aby przygotować czytelną prezentację na podstawie przygotowanych danych
źródłowych, wykonaj następujące czynności:
1. Zaznacz zakres komórek A1:D6 i zbuduj wykres

Słupkowy

/

Skumulowany

słupkowy

.

Rys. 3. Wybierz odpowiedni typ wykresu

Twój wykres będzie teraz wyglądał jak na rysunku 4.

Rys. 4. Początkowy układ wykresu

Wymaga on jednak drobnych modyfikacji, aby prezentacja danych była
wystarczająco czytelna.

3

=> W tym celu:
1. Kliknij legendę lewym przyciskiem myszy i wciśnij klawisz

Delete

. W razie potrzeby

rozszerz obszar kreślenia.
2. Kliknij prawym przyciskiem myszy dowolny słupek koloru niebieskiego i z menu
podręcznego wybierz polecenie

Formatuj serie danych

.

3. W sekcjach

Obramowanie

i

Obszar

wybierz opcję

Brak

(w Excelu 2007: w sekcji

Kolor krawędzi

wskaż

Brak linii

, a w sekcji

Wypełnienie

zaznacz

Brak wypełnienia

).

4. Zatwierdź ustawienia, klikając przycisk

OK

(w Excelu 2007:

Zamknij

).

5. Kliknij poziomą oś wykresu prawym przyciskiem myszy i z menu podręcznego
wybierz polecenie

Formatuj osie

(w Excelu 2007:

Formatuj oś

).

6. Przejdź do zakładki

Skala

(w Excelu 2007: sekcja

Opcje osi

) i dostosuj

odpowiednio wartości skrajne oraz jednostkę (np.

Minimum

:

1998

;

Maksimum

:

2009

;

Jednostka główna

:

1

).

7. Zatwierdź ustawienia, klikając

OK

.

Po sformatowaniu Twój wykres może wyglądać jak na rysunku 5.

Rys. 5. Gotowy wykres

4

Trik 2

Większa liczba warunków w formatowaniu warunkowym

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter113/2_Rozszerzenie_warunkow.zip

Otrzymałeś listę transakcji zrealizowanych przez handlowców w pewnym okresie.
Potrzebujesz odszukać i oznaczyć kolorem dane dotyczące czterech z nich.
Najwygodniej byłoby posłużyć się formatowaniem warunkowym, ale niestety do
dyspozycji są tylko 3 warunki, a Tobie potrzebne są 4. Okazuje się, że bardzo łatwo
możesz obejść to ograniczenie.

Uwaga
Wspomniane ograniczenie liczby warunków formatowania warunkowego nie dotyczy
Excela 2007.

Przykładową listę transakcji przedstawia rysunek 1.

Rys. 1. Lista transakcji

=> Aby zaznaczyć transakcje zrealizowane przez handlowców o nazwiskach: Witak,
Bełski, Nowak i Małek, wykonaj następujące czynności:
1. Zaznacz zakres A2:C14 i z menu

Format

wybierz polecenie

Formatowanie

warunkowe

(w Excelu 2007: uaktywnij kartę

Narzędzia główne

i w grupie poleceń

Style

wskaż

Formatowanie warunkowe/Nowa reguła

).

2. Z rozwijanej listy po lewej wybierz

Formuła jest

(w Excelu 2007: zaznacz opcję

Użyj formuły do określenia komórek, które należy sformatować

) i wpisz następującą

formułę:

=LUB($A2="Witak";$A2="Bełski";$A2="Nowak";$A2="Małek")

5

Rys. 2. Zdefiniowany warunek formatowania

Wyjaśnienie działania formuły:
Funkcja LUB zwraca wartość logiczną PRAWDA, jeśli choć jeden jej argument zwraca
wynik PRAWDA. Jeżeli zaś wszystkie argumenty zwracają wartość FAŁSZ, wtedy
wynikiem działania funkcji jest także FAŁSZ. W przykładzie w każdym argumencie
funkcji następuje sprawdzenie, czy komórka A2 zawiera jedno z podanych nazwisk.
Jeśli tak, wówczas nadawane jest formatowanie. W ten sposób analizowane są
wszystkie komórki w kolumnie A. Aby uzyskać zaznaczenie całych wierszy w
zestawieniu, zastosowano adresowanie mieszane ($A2).

3. Kliknij przycisk

Formatuj

i wybierz sposób oznaczenia komórek.

4. Na koniec zatwierdź ustawienia, w obu otwartych oknach klikając

OK

.

Rys. 3. Transakcje zostały oznaczone

Wskazówka
Liczbę nazwisk do wyróżnienia możesz zwiększać, dopisując kolejne argumenty w
formule.

6

Trik 3

Podsumowanie liczb niepoprawnie wprowadzonych do komórek

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter113/3_Podsumowanie_liczb.zip

Chcesz podsumować wartości wprowadzone do kolejnych komórek arkusza. Niestety
część z nich ma wstawiona kropkę stanowiącą separator tysięcy. Próba
podsumowania tak wprowadzonych liczb daje niepoprawny wynik jak na rysunku 1.

Rys. 1. Formuła daje błędny wynik

Aby przeprowadzić odpowiednie obliczenia, wystarczy rozbudować formułę:

=> W tym celu:
1. Formułę z komórki B8 zamień na następującą:

=SUMA(WARTOŚĆ(PODSTAW(B3:B7;".";"")))

2. Koniecznie zatwierdź ją kombinacją klawiszy

Ctrl + Shift + Enter

, ponieważ jest to

formuła tablicowa.

Uzyskasz efekt jak na rysunku 2.

Rys. 2. Formuła tablicowa zwróciła poprawny wynik

Wyjaśnienie działania formuły:
Zadaniem funkcji PODSTAW jest sprawdzenie kwot w komórkach B3:B7 i zamiana w
nich kropki na pusty ciąg znaków (tam gdzie jest to konieczne). Poprawiona kwota

7

jest następnie zamieniana na wartość liczbową za pomocą funkcji WARTOŚĆ. Na
koniec dokonywane jest podsumowanie kwot.

8

Trik 4

Podpis i dane kontaktowe wpisywane za pomocą formuły

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter113/4_Wprowadzanie_tekstu.zip

Na pewno często zdarza Ci się wielokrotnie wpisywać do arkusza ten sam tekst.
Przykładowo przygotowujesz kilkanaście raportów cząstkowych, pod którymi powinna
się znaleźć informacja o sporządzającym zestawienie. Może ona wyglądać
następująco:

Przygotował:
Rafał Milewski
Specjalista ds. Sprzedaży
Tel.: [phone]
Email: [e-mail]

Aby zaoszczędzić czas, możesz ją wprowadzić jako stałą tekstową, do której będzie
się odwoływać nazwa. Gdy zostanie ona wprowadzona jako formuła tablicowa,
będziesz mieć możliwość szybkiego wstawiania tych danych do wybranego miejsca w
arkuszu.

=> Aby zdefiniować nazwę odwołującą się do stałej tekstowej:
1. Zaznacz dowolną komórkę w arkuszu.
2. Z menu

Wstaw

wybierz polecenie

Nazwa/Definiuj

(w Excelu 2007: uaktywnij kartę

Formuły

i w grupie poleceń

Nazwy zdefiniowane

wskaż

Definiuj nazwę

).

3. W polu

Nazwy w skoroszycie

(w Excelu 2007:

Nazwa

) wpisz:

dane.

4. W polu

Odwołuje się do:

wpisz tekst, do którego odwołuje się wpisana nazwa, w

następujący sposób:
={"Przygotował:"\"Rafał Milewski"\"Specjalista
ds. Sprzedaży"\"Tel.: [phone]"\"Email: [e-mail]"}

5. Kliknij

Dodaj

, a następnie

OK

.

Rys. 1. Widok okna dialogowego Definiowanie nazw

9

Wskazówka
Definiując nazwę odwołującą się do stałej tekstowej, poszczególne informacje
mające znaleźć się w kolejnych pionowo ułożonych komórkach należy oddzielać
znakiem

"\"

i całość tekstu umieścić w nawiasie klamrowym.

=> Aby wstawić do arkusza zdefiniowaną nazwę:
6. Zaznacz zakres komórek, w których zostanie umieszczona nazwa. Liczba
zaznaczonych komórek w pionie powinna odpowiadać liczbie wierszy tekstu (podczas
definiowania nazwy oddzielonych znakiem

"\"

). W przykładzie będzie to 5 komórek.

7. Wpisz

=dane

i zatwierdź kombinacją klawiszy

Ctrl + Shift + Enter

.

Rys. 2. Widok arkusza po wstawieniu nazwy za pomocą formuły tablicowej

Dane sporządzającego raport zostały wstawione. Oczywiście zdefiniowaną nazwę

dane

możesz zastąpić własną.

10

Trik 5

Automatyczne formatowanie nowo dopisywanych wartości

Pobierz plik z przykładem

http://www.excelwpraktyce.pl/eletter_przyklady/eletter113/5_Automatyczne_uzupelnianie.zip

Może się zdarzyć, że do gotowego zestawienia, które już zostało ostatecznie
sformatowane, trzeba dopisać kolejne dane. W jego komórkach znajdują się
zapewne także formuły. Dopisanie nowych danych wiąże się z ręcznym
przenoszeniem formatowania zastosowanego w obrębie zestawienia i skopiowanie
formuł dla dołączonych wpisów. Okazuje się, że Excel może to robić za Ciebie!

Przykładowe sformatowane zestawienie zawierające formuły przedstawia rysunek 1.

Rys. 1. Przykładowe dane

=> Aby uaktywnić automatyczne nadawanie formatowania dla nowych danych oraz
samoczynne kopiowanie formuł:
1. Z menu

Narzędzia

wybierz polecenie

Opcje

, przejdź do zakładki

Edycja

i zaznacz

pole

Rozszerz formaty

i formuły zakresu danych

. (W Excelu 2007: kliknij

Przycisk

pakietu Office

i wskaż

Opcje programu Excel

. W lewej części okna uaktywnij

kategorię

Zaawansowane

i w sekcji

Opcje Edycji

zaznacz

Rozszerz formaty

i formuły

zakresu danych

).

Rys. 2. Opcje Excela

11

2. Zatwierdź ustawienia, klikając

OK

.

3. Teraz spróbuj uzupełnić dane, wpisując nowy wiersz w naszej tabeli. Kolejne
komórki nie były przez nas formatowane osobno, ale samoczynnie przez Excela.
Formuła także została przekopiowana automatycznie. Efekt końcowy przedstawia
rysunek 3.

Rys. 3. Excel przeniósł formaty i skopiował formułę

Włączenie tej opcji spowoduje, że Excel będzie automatycznie formatował nowe
dane wpisywane na końcu listy tak, aby były zgodne z poprzednimi wierszami i
automatycznie kopiował formuły powtarzające się w każdym wierszu. Formaty i
formuły, aby został rozszerzone, muszą pojawiać się w co najmniej trzech z pięciu
wierszy listy poprzedzających nowy wiersz.