:: Trik 1. Czytelna prezentacja kolumnowa z liniami odniesienia
:: Trik 2. Wygodne przeglądanie danych na podstawie nazw arkuszy
:: Trik 3. Łączna wartość sprzedaży wyznaczona jedną formułą
:: Trik 4. Automatyczne odliczanie czasu do ważnego spotkania
:: Trik 5. Formatowanie warunkowe z odwołaniem do innej kolumny
Trik 1
Czytelna prezentacja kolumnowa z liniami odniesienia
Pobierz plik z przykładem
Potrzebujesz zbudować prezentację porównawczą, na której znajdą się roczne wyniki finansowe poszczególnych oddziałów firmy. Na wykresie powinny być także widoczne linie określające założone wcześniej wartości minimalną i maksymalną. Przygotowanie takiej prezentacji jest prostsze niż mogłoby się wydawać.
Przykładowe wyniki finansowe oddziałów przedstawia rysunek 1.
Rys. 1. Dane źródłowe do wykresu
=> W tym celu:
1. W pierwszej kolejności wpisz do zakresu C2:C6 minimalny założony wynik oddziału (np. 250000).
2. Do komórek D2:D6 wpisz maksymalną wartość (np. 350000).
3. Zaznacz zakres komórek A2:D6 i zbuduj standardowy wykres kolumnowy.
4. Kliknij prawym przyciskiem myszy serię danych oznaczającą minimalny wynik finansowy i z menu podręcznego wybierz polecenie Typ wykresu (w Excelu 2007: Zmień typ wykresu seryjnego).
5. Wybierz typ Liniowy i zatwierdź, klikając OK.
Rys. 2. Zmiana typu wykresu
6. W ten sam sposób postąp z serią danych oznaczającą maksymalny wynik.
7. Na koniec kliknij pole legendy i wciśnij na klawiaturze Delete.
Po sformatowaniu Twój wykres może wyglądać jak na rysunku 3.
Rys. 3. Wykres z liniami odniesienia
Trik 2
Wygodne przeglądanie danych na podstawie nazw arkuszy
Pobierz plik z przykładem
W skoroszycie masz zgromadzone tabele z danymi dotyczącymi poszczególnych produktów, które znajdują się w oddzielnych arkuszach noszących nazwy ProduktA, ProduktB, ProduktC oraz ProduktD. Dane w każdym z nich mają ten sam układ widoczny na rysunku 1.
Rys. 1. Widok arkusza dotyczącego jednego z produktów
Do arkusza o nazwie Zbiorczy chcesz wstawić kwoty z komórki B7 dotyczące poszczególnych produktów.
Rys. 2. W tej komórce powinna się znaleźć wartość 285
Potrzebujesz zatem zbudować formułę, która będzie się odwoływała do tej samej komórki, ale w innych arkuszach. Ich nazwy formuła powinna pobierać z komórek kolumny A arkusza Zbiorczy. Typowe odwołanie do komórki znajdującej się w innym arkuszy wygląda następująco:
Nazwa arkusza i adres komórki są rozdzielone wykrzyknikiem. Kierując się tą zasadą, należałoby zatem w komórce B2 arkusza Zbiorczy wstawić następującą formułę:
=A2&!&B7
Rys. 3. Odwołanie budowane za pomocą operatora sklejenia (&)
Niestety przy próbie wprowadzenia tej formuły pojawi się komunikat o błędzie. Aby zbudować tego typu odwołanie, które będziesz mógł skopiować do kolejnych komórek kolumny B, powinieneś posłużyć się funkcją ADR.POŚR. Jej zadaniem jest zwrócenie odwołania określonego jako ciąg tekstowy.
=> Aby ją zastosować:
1. W komórce B2 arkusza Zbiorczy wprowadź następującą formułę:
2. Następnie skopiuj ją do komórek poniżej.
Rys. 4. Formuła zwróciła poprawne wyniki
Trik 3
Łączna wartość sprzedaży wyznaczona jedną formułą
Pobierz plik z przykładem
W arkuszu znajduje się zestawienie sprzedaży towarów. Zgromadzono w nim jedynie liczby sztuk i ceny jednostkowe. Aby obliczyć całkowitą sprzedaż, należałoby w pierwszej kolejności wyznaczyć wartość sprzedaży każdego towaru (iloczyn liczby sztuk i ceny jednostkowej), a dopiero później dodać uzyskane wartości. Okazuje się, że można to zrobić szybciej za pomocą odpowiedniej formuły tablicowej.
Rys. 1. Zestawienie sprzedaży
=> W celu szybkiego podsumowania sprzedaży:
1. W dowolnej pustej komórce wprowadź następującą formułę:
2. Koniecznie zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.
Uzyskasz efekt jak na rysunku 2.
Rys. 2. Jedna formuła podsumowała sprzedaż
Wyjaśnienie działania formuły:
Formuła tablicowa tworzy tablicę wartości, na których są wykonywane operacje. W tym przypadku jest to tablica iloczynów z poszczególnych komórek kolumn B i C. Na koniec uzyskane wyniki cząstkowe są podsumowane funkcją SUMA.
Trik 4
Automatyczne odliczanie czasu do ważnego spotkania
Pobierz plik z przykładem
Przygotowałeś w arkuszu podręczny przypominacz. Za pomocą funkcji TERAZ wprowadziłeś daty bieżącą oraz następnego zebrania zarządu. Chcesz, aby po otwarciu arkusza wyświetlała się informacja: ile dni, godzin i minut pozostało do tego ważnego spotkania. Jak osiągnąć taki efekt?
Rys. 1. Projekt przypominacza
Zauważ, że w Excelu czas to także liczba, a dokładniej wartość 1 odpowiada jednej dobie. Jedna godzina to dokładnie 1/24; minuta - odpowiednia liczba reprezentująca godzinę podzielona przez 60. W związku z tym możesz spokojnie odejmować daty za pomocą znaku odejmowania. Aby obliczyć liczbę dni, godzin i minut pomiędzy datami, wynik odejmowania dat podaj jako argument jednej z funkcji: DZIEŃ, GODZINA i MINUTA.
=> W tym celu:
1. W komórce B8 wpisz formułę:
Wskazówka
Funkcja DZIEŃ podaje wartość jako liczbę całkowitą z zakresu od 0 do 31. Jeżeli przewidujesz, że różnica dni może być większa, to użyj funkcji:
2. W komórce D8 wpisz formułę:
Uwaga
Po wpisaniu formuł operujących na komórkach sformatowanych jako data lub czas może dojść do automatycznej zmiany formatowania komórki z formułą na jeden z formatów liczbowych daty. Aby to skorygować, zaznacz komórkę, wciśnij Ctrl + 1, w zakładce Liczby zmień format liczbowy komórki na Ogólne lub Liczbowe.
Arkusz po wpisaniu odpowiednich formuł widoczny jest na rysunku 2.
Rys. 2. Tyle czasu pozostało do ważnego spotkania
Trik 5
Formatowanie warunkowe z odwołaniem do innej kolumny
Pobierz plik z przykładem
Przyjmijmy, że na długiej liście premii rocznych potrzebujesz oznaczyć kolorem te, które stanowią więcej niż 50% miesięcznego wynagrodzenia danego pracownika. Niestety do przejrzenia jest kilkaset komórek, a Ty nie masz czasu na stosowanie obliczeń pomocniczych wyliczających procentowy udział dodatku. Okazuje się, że możesz do tego celu wykorzystać formatowanie warunkowe z odwołaniem do kolumny z wynagrodzeniem.
Przykładową listę premii i wynagrodzeń przedstawia rysunek 1.
Rys. 1. Lista premii
=> Aby szybko oznaczyć wartości spełniające warunek:
1. Zaznacz zakres komórek B2:B16 i z menu Format wybierz polecenie Formatowanie warunkowe (w Excelu 2007: uaktywnij kartę Narzędzia główna i w grupie poleceń Style wskaż Formatowanie warunkowe).
2. W oknie, które się wyświetli, z pierwszej rozwijanej listy wybierz pozycję Wartość komórki jest (w Excelu 2007: z rozwiniętej listy wybierz Reguły wyróżniania komórek/Większe niż).
3. Na drugiej wskaż większa niż (w Excelu 2007: pomiń ten krok).
4. Wpisz następującą formułę:
5. Wybierz przycisk Formatuj, określ sposób wyróżnienia komórek (w Excelu 2007: rozwiń listę po prawej stronie i wybierz styl formatowania).
Rys. 2. Poprawnie zdefiniowany warunek formatowania
6. Zatwierdź, klikając OK.
Uzyskasz efekt jak na rysunku 3. Oznaczone są te premie, których wartość przekracza połowę wynagrodzenia podstawowego.
Rys. 3. Odpowiednie komórki zostały oznaczone
|