:: 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
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
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:
2. Formułę skopiuj do kolejnych komórek.
3. Na koniec ukryj kolumnę C.
Twój arkusz powinien teraz wyglądać jak na rysunku 2.
Rys. 2. Dane źródłowe są przygotowane
=> 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.
=> 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
Trik 2
Większa liczba warunków w formatowaniu warunkowym
Pobierz plik z przykładem
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")
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.
Trik 3
Podsumowanie liczb niepoprawnie wprowadzonych do komórek
Pobierz plik z przykładem
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 jest następnie zamieniana na wartość liczbową za pomocą funkcji WARTOŚĆ. Na koniec dokonywane jest podsumowanie kwot.
Trik 4
Podpis i dane kontaktowe wpisywane za pomocą formuły
Pobierz plik z przykładem
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:
5. Kliknij Dodaj, a następnie OK.
Rys. 1. Widok okna dialogowego Definiowanie nazw
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 ukośnika 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 ukośnika). 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ą.
Trik 5
Automatyczne formatowanie nowo dopisywanych wartości
Pobierz plik z przykładem
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
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.
|