:: Trik 1. Wartości prognozowane bardziej czytelne na wykresie
:: Trik 2. Szybkie łączenie danych z 2 zestawień
:: Trik 3. Problem z zaokrąglaniem kwot do pełnej złotówki
:: Trik 4. Wyodrębnianie informacji cząstkowych z identyfikatorów
:: Trik 5. Sprawdzenie liczebności danych w wielu arkuszach jednocześnie
Trik 1
Wartości prognozowane bardziej czytelne na wykresie
Pobierz plik z przykładem
Dość często pojawia się problem czytelnego przedstawienia na wykresie wartości prognozowanych. Nie chodzi tutaj o odróżnienie kolorami danych rzeczywistych od przewidywanych, ale o taką modyfikację elementów i układu wykresu, aby przekaz był łatwo zauważalny przez odbiorców. Pokażemy Ci zatem, jak możesz modyfikować wykres liniowy, aby uzyskać czytelną i efektowną prezentację.
Wskazówka
Aby dobrze przedstawić prognozę, powinieneś posłużyć się odpowiednio skonstruowaną tabelą. Poprawny układ danych pozwoli zachować właściwą kolejność tworzenia poszczególnych elementów wykresu i odróżnić oba rodzaje wartości.
Przykładowe zestawienie danych źródłowych przedstawia rysunek 1. Zauważ, że w kolumnie C wprowadziliśmy rzeczywiste wartości dla lat 2004-2008.
Rys. 1. Dane źródłowe do wykresu
=> Aby zbudować wykres:
1. Zaznacz zakres komórek A2:C12.
2. Uruchom kreator wykresów (w Excelu 2007: uaktywnij kartę Wstawianie) i wybierz typ wykresu liniowy oraz podtyp liniowy.
3. Kliknij przycisk Zakończ, aby zakończyć pracę kreatora (w Excelu 2007: pomiń ten krok).
4. Zaznacz legendę, klikając ją jednokrotnie lewym przyciskiem myszy, a następnie wciśnij na klawiaturze Delete. W ten sposób usuniesz legendę, która wyświetlałaby tylko zbędną informację o serii danych i niepotrzebnie mogłaby zaciemniać końcowy efekt prezentacji.
Po sformatowaniu Twój wykres może wyglądać jak na rysunku 2.
Rys. 2. Układ danych na wykresie
W tak utworzonym wykresie nie widać dokładnie, w którym miejscu masz do czynienia z danymi rzeczywistymi, a odkąd zaczynają się dane prognozowane.
=> Dla pokazania tego przejścia powinieneś wykonać następujące kroki:
1. Kliknij prawym przyciskiem myszy linię obrazująca dane i wybierz z menu podręcznego Formatuj serie danych.
2. W zakładce Desenie (w Excelu 2007: w grupie opcji Styl linii) wybierz z rozwijanej listy przerywany styl linii. Jeśli pracujesz w Excelu 2007 zatwierdź ustawienia, klikając OK.
3. Przejdź do zakładki Kolejność serii, zaznacz Serie2 i kliknij przycisk Przenieś w górę. (W Excelu 2007 kliknij wykres prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Zaznacz dane. W okienku Wpisy legendy zaznacz Serie2 i kliknij małą strzałkę w górę).
Jeśli teraz zatwierdzisz ustawienia przyciskiem OK, linia oparta na danych rzeczywistych będzie widoczna nad linią prognozowaną, zasłaniając ją w początkowym przebiegu. W związku z tym linia przerywana przedstawiająca przebieg prognozy, widoczna jest dopiero od pozycji, w której nie ma już danych rzeczywistych.
Wskazówka
Jeśli będziesz chciał zmieniać kolor lub grubość linii, zwróć uwagę, którą serię danych zaznaczyłeś (prezentującą dane rzeczywiste lub prognozowane).
Rys. 3. Prognoza jest czytelnie przedstawiona
Trik 2
Szybkie łączenie danych z 2 zestawień
Pobierz plik z przykładem
Załóżmy, że razem ze swoim współpracownikiem przygotowywaliście raport okresowy. Korzystaliście z tego samego szablonu, ale każdy pracował w swoim pliku. Potrzebujesz teraz połączyć obie tabele, ale pojawił się problem: nie wszystkie komórki są uzupełnione. Dodatkowo puste rubryki znajdują się w różnych miejscach obu arkuszy. Jak zatem połączyć dane, aby automatycznie wypełnić brakujące obszary? Jest na to szybki sposób!
Na rysunku 1 przedstawione są 2 zestawienia. Oba zawierają puste komórki w różnych miejscach. Brakujące dane w zestawieniu A można uzupełnić wartościami, które zostały wprowadzone do odpowiadających komórek w zestawieniu B.
Rys. 1. Dane do połączenia
Niestety, jeżeli skopiujesz dane z zestawienia A i wkleisz je do zestawienia B, nie uzyskasz oczekiwanego efektu (rys. 2).
Rys. 2. Dane zostały nadpisane razem z pustymi komórkami
=> Aby wykonać to poprawnie:
1. Skopiuj zakres komórek B2:E5, a więc dane z zestawienia A.
2. Zaznacz obszar zestawienia B (B8:E11) i z menu Edycja wybierz polecenie Wklej specjalnie (w Excelu 2007: uaktywnij kartę Narzędzia główne, w grupie poleceń Schowek rozwiń listę Wklej i wybierz pozycję Wklej specjalnie).
3. U dołu okna dialogowego zaznacz pole Pomijaj puste i zatwierdź, klikając OK.
Rys. 3. Wklejanie specjalne
Ta operacja pozwoliła poprawnie połączyć dane z obu zestawień. Efekt końcowy przedstawia rysunek 4.
Rys. 4. Połączone dane
Zauważ, że dane, które były jedynie w zestawieniu B, zostały wstawione w odpowiadające im puste komórki zestawienia A. Dzięki temu w szybki sposób przygotowałeś kompletny raport.
Trik 3
Problem z zaokrąglaniem kwot do pełnej złotówki
Pobierz plik z przykładem
W trakcie przygotowywania zestawień finansowych bardzo często wymagane jest zaokrąglenie kwot do pełnej złotówki. Nie chodzi tutaj o zmianę formatowania, kiedy to wartość w komórce nie ulega zmianie, a jedynie sposób jej wyświetlania. W tym przypadku do obliczeń są bowiem brane kwoty razem z miejscami dziesiętnymi, co może istotnie wpłynąć na końcowy wynik przeprowadzanej analizy.
Należy zatem skorzystać z odpowiedniej funkcji zaokrąglającej. I tutaj w zależności od potrzeb możesz użyć jednej z dwóch funkcji: ZAOKR lub ZAOKR.DO.CAŁK. Aby pokazać Ci różnice w ich działaniu, posłużymy się przykładem z rysunku 1.
Rys. 1. Przykład zaokrągleń
Jeżeli potrzebujesz zaokrąglić kwoty i wartość groszy ma być uwzględniona, powinieneś skorzystać z funkcji ZAOKR. Kwota w komórce B4 została zaokrąglona do pełnej złotówki w górę, ponieważ wartość groszy w kwocie bazowej (z komórki A4) jest większa niż 49. A zatem kwota 213,49 będzie zaokrąglona w dół i wynikiem funkcji będzie 213,00. Kwota 213,50 zostanie natomiast zaokrąglona w górę i funkcja wyświetli 214,00.
Jeśli potrzebujesz po prostu odciąć wartość groszy w taki sposób, aby nie miała wpływu na liczbę całkowitą, bez względu na to czy przekracza 49 groszy czy też nie, powinieneś skorzystać z funkcji ZAOKR.DO.CAŁK w sposób przedstawiony na rysunku 1.
Trik 4
Wyodrębnianie informacji cząstkowych z identyfikatorów
Pobierz plik z przykładem
Różnego rodzaju identyfikatory (klienta, produktu itp.) zawierają w sobie istotne czasami informacje np. rok produkcji, oznaczenie kategorii produktu. Jeśli nie dysponujesz tymi danymi w osobnych komórkach, możesz je bardzo łatwo wyodrębnić z ciągu znaków tworzącego identyfikator i to bez konieczności budowania bardzo złożonych formuł.
Przykładowe zestawienie produktów przedstawia rysunek 1.
Rys. 1. Identyfikatory zawierające informacje cząstkowe
Poszczególne informacje są rozdzielone ukośnikami. Pierwsza to oznaczenie zakładu produkcyjnego, druga to unikalny numer produktu, trzecia rodzaj gatunku i ostatnia - rok produkcji.
=> Aby te dane rozdzielić do osobnych kolumn:
1. Zaznacz zakres komórek A2:A10 i z menu Dane wybierz polecenie Tekst jako kolumny (w Excelu 2007: uaktywnij kartę Dane i w grupie poleceń Narzędzia danych wskaż Tekst jako kolumny).
2. W pierwszym oknie kreatora wybierz opcję Rozdzielany.
3. Kliknij Dalej, aby przejść do kolejnego okna.
4. Odznacz domyślnie zaznaczone pole Tabulator, a po prawej stronie pola Inny wpisz ukośnik. Kliknij Dalej.
Rys. 2. Drugi krok kreatora
5. W kolejnym kroku określ format danych dla rozdzielonych kolumn i w polu Miejsce docelowe określ komórkę, od której mają rozpoczynać się wstawione dane. Dla potrzeb tego przykładu wybierz D2.
6. Kliknij przycisk Zakończ.
Po uzupełnieniu nazw nagłówków nowo wstawionych kolumn Twoje zestawienie może wyglądać jak na rysunku 3.
Rys. 3. Wyodrębnione informacje cząstkowe
Błyskawicznie rozbudowałeś swoją tabelę o szczegółowe dane. Zauważ, że dzięki skorzystaniu z kreatora konwersji tekstu, wartości liczbowe (Numer i Rok prod.) zostały wyświetlone w poprawnym formacie.
Trik 5
Sprawdzenie liczebności danych w wielu arkuszach jednocześnie
Pobierz plik z przykładem
W trzech różnych arkuszach (Sty, Lut, Mar) zgromadziłeś informacje dotyczące nowo pozyskanych klientów. Każda lista składa się z tych samych kolumn, ale ma inną liczbę wierszy.
Rys. 1. Fragment listy klientów
W osobnym arkuszu chcesz szybko sporządzić zestawienie zawierające liczby klientów pozyskanych w poszczególnych okresach i przy okazji dokonać podsumowania. Czy musisz przechodzić do każdego arkusza z osobna, zliczać pozycje na liście i ręcznie wprowadzać je do raportu? Niekoniecznie! Rozwiązaniem są proste formuły tablicowe.
=> Aby z nich skorzystać:
1. W pierwszej kolejności wstaw nowy arkusz za pomocą kombinacji klawiszy Shift + F11.
2. W dowolnej pustej komórce wstaw formułę zliczającą klientów pozyskanych w styczniu:
=SUMA(JEŻELI(CZY.PUSTA(Sty!$A$2:$A$1000);0;1))
3. Zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.
4. Aby zliczyć pozycje w pozostałych arkuszach, skopiuj formułę w dół i zmień nazwę Sty na odpowiednie oznaczenia arkuszy. Pamiętaj, aby po wyedytowaniu formuł zatwierdzić je wspomnianą kombinacją 3 klawiszy.
Rys. 2. Tyle pozycji znajduje się na listach w różnych arkuszach
|