:: Trik 1. Formatowanie przenoszone z wykresu na wykres
:: Trik 2. Dostosowanie kolejności danych w tabeli przestawnej
:: Trik 3. Wyznaczanie najmniejszej wartości z pominięciem liczb ujemnych
:: Trik 4. Formuła zwracająca w komórce nazwę arkusza
:: Trik 5. Szybka lista dat końcowych poszczególnych miesięcy
Trik 1
Formatowanie przenoszone z wykresu na wykres
Pobierz plik z przykładem
Pracujesz nad raportem, który będzie wzbogacany o kolejno przygotowywane wykresy. W pierwszym z nich wprowadziłeś dużo modyfikacji (nadałeś formatowanie seriom danych, zmieniłeś krój czcionki itd.). Teraz chciałbyś budować kolejne prezentacje, ale ręczne formatowanie każdego wykresu z osobna to strata czasu. W jaki sposób przenieść formatowanie z bazowego wykresu na kolejne tak, aby raport wyglądał estetycznie? Jest szybki sposób, żeby to zrobić.
=> W tym celu:
1. W pierwszej kolejności zaznacz cały wykres bazowy (kliknij obszar wykresu, a nie np. obszar kreślenia) i skopiuj go do schowka, wciskając kombinację klawiszy Ctrl + C.
Rys. 1. Zaznacz wykres do skopiowania
2. Teraz zaznacz nowo utworzony wykres i z menu Edycja wybierz polecenie Wklej specjalnie (w Excelu 2007: uaktywnij kartę Narzędzia główne i w grupie poleceń Schowek rozwiń listę Wklej, a następnie wybierz Wklej specjalnie). Pojawi się okno dialogowe jak na rysunku 2.
3. Zaznacz pole Formaty i zatwierdź, klikając OK.
Rys. 2. Wklejanie formatu wykresu
Formatowanie zostanie przeniesione na wykres. Jedyną niedogodnością w korzystaniu z tego triku jest to, że nie można przenosić formatów na wiele wykresów jednocześnie.
Trik 2
Dostosowanie kolejności danych w tabeli przestawnej
Pobierz plik z przykładem
Tabela przestawna to jedno z bardziej elastycznych narzędzi wbudowanych w Excela. Dzięki niej możesz z łatwością grupować i filtrować dane, aby uzyskać różne warianty raportów. Czasami jednak wartości w tabeli przestawnej chcesz ułożyć w określonej kolejności, np. od największej do najmniejszej. W jaki sposób posortować dane?
Fragment przykładowej listy, na podstawie której powstanie tabela przestawna, przedstawia rysunek 1.
Rys. 1. Dane do raportu przestawnego
Tworzenie tabeli przestawnej
W pierwszej kolejności zbuduj tabelę przestawną. Dopiero kolejnym etapem będzie sortowanie danych.
=> W tym celu:
1. Kliknij dowolną komórkę w obrębie listy i z menu Dane wybierz polecenie Raport tabeli przestawnej i wykresu przestawnego (w Excelu 2007: uaktywnij kartę Wstawianie i w grupie poleceń Tabele kliknij Tabela przestawna).
2. Zaznacz opcje: Lista lub baza danych programu Microsoft Office Excel oraz Tabela przestawna (w Excelu 2007: uaktywnij opcję Zaznacz tabelę lub zakres i upewnij się, że Excel poprawnie rozpoznał zakres danych źródłowych).
3. Kliknij przycisk Dalej i w kolejnym oknie także Dalej (w Excelu 2007: pomiń ten krok).
4. Wskaż miejsce wstawienia nowej tabeli (Nowy arkusz) i kliknij Zakończ (w Excelu 2007: OK). Zostanie wyświetlony projekt tabeli przestawnej jak na rysunku 2.
Rys. 2. Projekt nowo tworzonej tabeli przestawnej
5. W oknie Lista pól tabeli przestawnej chwyć myszą pole Sprzedawca i z wciśniętym lewym przyciskiem myszy przeciągnij je nad obszar nazwany Upuść pola wierszy tutaj.
6. Tym samym sposobem przenieś pole Miasto do obszaru Upuść pola kolumn tutaj.
7. Pole Kwota przenieś do obszaru Upuść elementy danych tutaj.
Rys. 3. Gotowa tabela przestawna
W kolumnie H znajdują się podsumowania sprzedaży osiągniętej przez poszczególnych handlowców we wszystkich miastach. Ich kolejność jest narzucona porządkiem alfabetycznym nazwisk z kolumny A. Ty chcesz te kwoty posortować rosnąco.
=> Dostosowanie kolejności danych wykonasz bardzo szybko:
1. Kliknij dowolną komórkę z kwotą podsumowania i na pasku narzędziowym wybierz odpowiednie polecenie sortowania. W Excelu 2007 po zaznaczeniu komórki kliknij prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Sortuj/Sortuj od najmniejszych do największych lub Sortuj od największych do najmniejszych.
Rys. 4. Sortowanie danych w tabeli przestawnej
Wskazówka
W analogiczny sposób możesz posortować podsumowania w wierszu 12. Aby to zrobić, zaznacz dowolną komórkę w obszarze podsumowań i określ porządek danych.
Trik 3
Wyznaczanie najmniejszej wartości z pominięciem liczb ujemnych
Pobierz plik z przykładem
Jeżeli w Twoim zestawieniu znajdują się liczby dodatnie i ujemne, to wynikiem działania formuły opartej jedynie na funkcji MIN będzie wartość ujemna. Co zatem zrobić w sytuacji, gdy liczby ujemne przy obliczaniu najmniejszej wartości mają być ignorowane?
Możesz zastosować odpowiednią formułę tablicową, której składnia jest następująca:
=MIN(JEŻELI(zakres<0;"";zakres))
- W miejsce słowa zakres wpisujesz adres komórek, w których znajdują się wszystkie dane źródłowe.
- Jest to formuła tablicowa, a zatem za każdym razem powinieneś ją zatwierdzać kombinacją klawiszy Ctrl + Shift + Enter.
- Użyty w ten sposób operator porównawczy mniejsze niż (<) powoduje, że jeżeli w zestawieniu źródłowym znajduje się wartość zerowa, to ona będzie wynikiem końcowym jako najmniejsza nieujemna liczba. Jeśli chcesz otrzymać najmniejszą liczbę dodatnią, użyj operatora mniejsze niż lub równe (<=).
- Jeżeli pomiędzy danymi źródłowymi znajdą się wartości tekstowe lub puste komórki, zostaną przez formułę zignorowane.
Na rysunku 1 zaprezentowano trzy różne wyniki wyznaczania wartości minimalnej. W komórce B3 znajduje się zwykła formuła zwracająca najmniejszą (ujemną) liczbę.
W komórce B4 zastosowano natomiast formułę, której zadaniem jest wyznaczenie najmniejszej liczby spoza zbioru liczb ujemnych. Ze względu na to, że w obszarze źródłowym znajduje się wartość zerowa, stanowi ona wynik końcowy obliczenia:
=MIN(JEŻELI(A7:C11<0;"";A7:C11))
W komórce D5 zastosowano formułę, która zwraca najmniejszą wartość z zakresu liczb dodatnich. Dzięki wykorzystaniu operatora porównawczego mniejsze niż lub równe (<=) zero zostało wyłączone z obliczeń:
=MIN(JEŻELI(A7:C11<=0;"";A7:C11))
Rys. 1. Wartość najmniejsza spoza zbioru liczb ujemnych
Wyjaśnienie działania formuły:
- Funkcja JEŻELI sprawdza każdą komórkę z zakresu A7:C11 pod kątem występowania liczb ujemnych (<0).
- Tworzona jest tablica wartości logicznych PRAWDA (dla liczb ujemnych) i FAŁSZ (dla liczb dodatnich i w tym przypadku zera).
- Wszystkie wartości FAŁSZ są zamieniane w tablicy na rzeczywiste liczby z komórek, a w miejsce wartości PRAWDA jest podstawiany pusty ciąg znaków.
- Na koniec funkcja MIN przeszukuje liczby znajdujące się w tablicy i zwraca najmniejszą.
Trik 4
Formuła zwracająca w komórce nazwę arkusza
Pobierz plik z przykładem
Skończone raporty są najczęściej drukowane. Czasami jednak zachodzi potrzeba odnalezienia arkusza, w którym znajduje się dana tabela. Czeka Cię wówczas przeglądanie skoroszytu arkusz po arkuszu. Czy nie lepiej byłoby umieścić nad zestawieniem nazwę arkusza? W Excelu możesz to zrobić w bardzo szybko.
=> W tym celu:
1. Wpisz do dowolnej pustej komórki arkusza (w przykładzie A1) następującą formułę:
=FRAGMENT.TEKSTU(KOMÓRKA("nazwa_pliku";$A$1);
ZNAJDŹ("]";KOMÓRKA("nazwa_pliku";$A$1))+1;
DŁ(KOMÓRKA("nazwa_pliku";$A$1)))
Rys. 1. Nazwa arkusza wyświetlana w komórce
Formuła jest bardzo elastyczna i możesz ją spokojnie kopiować do innych arkuszy. Dodatkowo przy zmianie nazwy arkusza już po wprowadzeniu formuły, wyświetlana przez nią wartość automatycznie się zaktualizuje.
Wyjaśnienie działania formuły:
Sposób działania formuły bazuje na funkcji KOMÓRKA, która z argumentem nazwa_pliku zwraca ścieżkę dostępu do skoroszytu w komputerze, a ostatnim członem tej ścieżki jest właśnie nazwa bieżącego arkusza. Jest ona pobierana za pomocą odpowiednich funkcji tekstowych. Wpisz do arkusza formułę =KOMÓRKA("nazwa_pliku"), a szybko zorientujesz się, w jaki sposób jest skonstruowana formuła pobierająca nazwę arkusza.
Trik 5
Szybka lista dat końcowych poszczególnych miesięcy
Pobierz plik z przykładem
Potrzebujesz przygotować roczne zestawienia nadgodzin każdego pracownika. W związku z tym nazwałeś arkusze nazwiskami i w każdym z nich powinieneś wprowadzić daty ostatnich dni wszystkich miesięcy, a obok łączną liczbę nadgodzin dla każdego okresu. Czy musisz pisać skomplikowane formuły, aby wyznaczyć taką listę dat? Niekoniecznie.
=> Aby to zrobić:
1. W pierwszej kolejności zgrupuj arkusze pracownicze: kliknij zakładkę pierwszego z nich, wciśnij klawisz Shift i kliknij zakładkę ostatniego. Powinny zostać podświetlone na biało.
2. Do komórki A1 wpisz datę ostatniego dnia w styczniu w odpowiednim formacie (np. rrrr-mm-dd).
3. Kliknij prawy dolny róg komórki z datą i przeciągnij w dół do kolejnych 11 komórek. Pojawi się menu podręczne widoczne na rysunku 1.
Rys. 1. Menu wypełniania serią
3. Wybierz polecenie Wypełnij miesiącami.
Uzyskasz efekt jak na rysunku 2.
Rys. 2. Daty ostatnich dni każdego miesiąca
4. Kliknij dowolną zakładkę arkusza, co spowoduje ich rozgrupowanie. Lista dat została wprowadzona do wszystkich arkuszy.
|