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.: 022 765 22 32
Email: milewski@xedos.pl
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.: 022 765 22 32"\"Email: milewski@xedos.pl"}
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.