:: Trik 1. Wyróżnienie kwot sum częściowych
:: Trik 2. Dane w tabeli przestawnej pogrupowane tygodniami
:: Trik 3. Hurtowa zamiana liczb rzymskich na arabskie
:: Trik 4. Daty dni roboczych na osi wykresu
:: Trik 5. Rejestr bez pustych wierszy
Trik 1
Wyróżnienie kwot sum częściowych
Pobierz plik z przykładem
Sumy częściowe pozwalają szybko podsumować wszystkie kwoty na liście odpowiadające określonemu kryterium. Poniżej każdego zestawu wartości odnoszących się do konkretnej grupy wstawiany jest dodatkowy wiersz, w którym program wyświetla nazwę charakterystycznego elementu grupy oraz tekst Suma. W komórce obok wstawiana jest suma częściowa. Opis wiersza z podsumowaniem jest pogrubiony, a zatem jest w miarę czytelny. Wartość sumy częściowej nie jest już niestety w żaden sposób uwydatniona. Rysunek 1 przedstawia arkusz po zastosowaniu sum częściowych.
Rys. 1. Wartości sum częściowych nie są wyróżnione
W przypadku tak niewielkiej listy możesz je ręcznie sformatować. Jeżeli jednak sum częściowych do wyróżnienia będzie kilkadziesiąt albo kilkaset, to takie zadanie będzie niezwykle pracochłonne. Proponujemy zatem skorzystać z formatowania warunkowego opartego na formule.
=> W tym celu:
1. Zaznacz zakres komórek B2:C20 w taki sposób, aby aktywną komórką była B2.
2. 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).
3. Z rozwijanej listy po lewej stronie wybierz pozycję Formuła jest (w Excelu 2007: w wyświetlonym oknie zaznacz opcję Użyj formuły do określenia komórek, które należy sformatować).
4. Wprowadź następującą formułę:
=NIE(CZY.BŁĄD(ZNAJDŹ("Suma";$B2)))
Wyjaśnienie działania formuły:
Funkcja ZNAJDŹ sprawdza każdą komórkę w kolumnie B zaznaczonego zakresu pod kątem występowania słowa Suma. Jeżeli je znajdzie, zwraca pewną liczbę (w tym przykładzie nie ma ona znaczenia), a jeśli nie - symbol błędu. Wynik jest przekazywany funkcji CZY.BŁĄD, która dla błędu zwróci wartość logiczną PRAWDA, a dla wartości liczbowej (funkcja ZNAJDŹ odnalazła słowo Suma) wartość logiczną FAŁSZ. Formatowanie warunkowe będzie nadawane tylko wówczas, gdy wynikiem formuły dla danej komórki będzie PRAWDA. Za pomocą funkcji NIE należy zatem zamienić wartość PRAWDA na FAŁSZ (dla komórek, które nie zawierają słowa Suma) i FAŁSZ na PRAWDA (dla komórek, które je zawierają).
5. Kliknij przycisk Formatuj i w wyświetlonym oknie przejdź do zakładki Czcionka.
6. W polu Styl czcionki zaznacz pozycję Pogrubiony i kliknij OK.
Rys. 2. Okno z warunkiem formatowania
7. Zatwierdź, klikając OK.
Uzyskałeś oczekiwany efekt. Wartości sum pośrednich są pogrubione, jak to przedstawia rysunek 3.
Rys. 3. Odpowiednio sformatowane wartości
Wskazówka
Oczywiście możesz dowolnie sformatować komórki zawierające wartość sumy pośredniej (kolor tła komórki czcionki czy obramowanie). Pogrubienie czcionki jest tylko przykładowym sposobem wyróżnienia danych.
Trik 2
Dane w tabeli przestawnej pogrupowane tygodniami
Pobierz plik z przykładem
W arkuszu znajduje się lista dziennej sprzedaży poszczególnych towarów. Na jej podstawie chcesz stworzyć raport przestawny, w którym znajdą się nazwy produktów, a także wielkości sprzedaży z podziałem na tygodnie. Niestety nie możesz znaleźć opcji, która pozwalałaby uzyskać pogrupowanie danych tygodniami. Jak poradzić sobie z tym problemem? Okazuje się, że jest bardzo szybkie rozwiązanie.
Przykładową listę sprzedaży, na podstawie której powstanie tabela przestawna, przedstawia rysunek 1.
Rys. 1. Przykładowe dane źródłowe
Tabela przestawna we wcześniejszych wersjach Excela
=> Aby zbudować tabelę przestawną, wykonaj następujące kroki:
1. Zaznacz dowolną komórkę w obrębie listy i z menu Dane wybierz polecenie Raport tabeli przestawnej i wykresu przestawnego.
2. W kroku 1. zaznacz na górze pole Lista lub baza danych Microsoft Excel, a na dole Tabela przestawna.
3. Kliknij Dalej, aby przejść do kolejnego kroku.
4. W kroku 2. nie zmieniaj ustawień i kliknij Dalej.
5. W ostatnim kroku zaznacz pole Nowy arkusz i kliknij przycisk Zakończ.
Tabela przestawna w Excelu 2007
1. Zaznacz komórkę w obrębie listy, uaktywnij kartę Wstawianie i grupie poleceń Tabele kliknij Tabela przestawna.
2. W wyświetlonym oknie upewnij się, czy Excel poprawnie rozpoznał zakres danych źródłowych.
3. U dołu zaznacz opcję Nowy arkusz i kliknij OK.
Zostanie wyświetlony projekt tabeli przestawnej jak na rysunku 2.
Rys. 2. Projekt tabeli przestawnej
6. Chwyć myszą pole Data i przeciągnij je nad obszar nazwany Upuść pola wierszy tutaj.
7. Pole Towar umieść w obszarze Upuść pola kolumn tutaj.
8. Pole Liczba szt. przenieś do obszaru Upuść elementy danych tutaj.
Grupowanie sprzedaży tygodniami
Tabela jest obecnie w wyjściowej postaci. W kolumnie A znajdują się daty pojedynczych dni. Ty chcesz je pogrupować tygodniami.
=> W tym celu:
1. Prawym przyciskiem myszy kliknij dowolną datę w kolumnie A.
2. Z menu podręcznego, które się pojawiło, wybierz polecenie Grupuj i pokaż szczegóły/Grupuj (w Excelu 2007: Grupuj).
3. W oknie dialogowym Grupowanie w polu Według zaznacz pozycję Dni.
4. W polu Liczba dni wpisz 7.
Rys. 3. Opcje grupowania
5. Zatwierdź ustawienia, klikając OK.
Uzyskasz efekt jak na rysunku 4.
Rys. 4. Sprzedaż tygodniowa
W ten szybki sposób daty z kolumny A zostały zamienione na okresy tygodniowe. Oczywiście, jeśli chcesz uzyskać inny wariant raportu sprzedaży, możesz dowolnie zmieniać ustawienia w oknie dialogowym Grupowanie.
Trik 3
Hurtowa zamiana liczb rzymskich na arabskie
Pobierz plik z przykładem
Masz kłopoty z odczytywaniem liczb rzymskich, a otrzymałeś arkusz zawierający takie wartości? Przygotuj odpowiednią funkcję VBA, która sama przekonwertuje liczby rzymskie na arabskie.
Przyjmijmy, że w arkuszu znajduje się lista filmów. Rok produkcji każdego z nich jest zapisany cyframi rzymskimi.
Rys. 1. Przykładowe liczby zapisane cyframi rzymskimi
=> Aby liczby w kolumnie B w przedstawić za pomocą cyfr arabskich:
1. Otwórz Edytor VBA za pomocą kombinacji klawiszy lewy Alt + F11.
2. Następnie z menu Insert wybierz polecenie Module.
3. Do okna po prawej wpisz kod funkcji widoczny na rysunku 2. Znajdziesz go także w pliku z przykładem załączonym do tego triku.
Rys. 2. Kod funkcji VBA
4. Zapisz i zamknij Edytor VBA.
Z tak przygotowanej funkcji możesz korzystać jak z każdej innej funkcji arkuszowej.
5. Wpisz zatem do komórki C2 następującą formułę:
a następnie skopiuj ją poniżej. Uzyskasz pożądany zapis jak na rysunku 3.
Rys. 3. Zapis cyframi arabskimi
Wskazówka
Jeśli chcesz wykonać odwrotną operację, czyli liczby arabskie przedstawić cyframi rzymskimi, skorzystaj z wbudowanej funkcji RZYMSKIE. Korzystasz z niej tak samo jak funkcji ARABSKIE.
Trik 4
Daty dni roboczych na osi wykresu
Pobierz plik z przykładem
Otrzymałeś spis utargów osiągniętych w kolejnych dniach roboczych. Zbudowałeś na jego podstawie wykres liniowy, ale pojawił się problem. Na poziomej osi znajdują się także daty sobót i niedziel, mimo że nie ma ich w tabeli źródłowej i w tych dniach nie było żadnych utargów. W jaki sposób pozbyć się z wykresu tych niepożądanych dat? Wystarczy szybka zmiana ustawień prezentacji.
Rys. 1. Wykres zawierający daty weekendowe
=> Aby pozbyć się niepożądanych dat:
1. Kliknij obszar wykresu prawym przyciskiem myszy i z menu podręcznego wybierz polecenie Opcje wykresu (w Excelu 2007: kliknij prawym przyciskiem oś poziomą i wybierz Formatuj Oś).
2. Przejdź do zakładki Osie (w Excelu 2007: Opcje osi) i w sekcji Oś główna (w Excelu 2007: Typ osi) zaznacz pole Kategoria (w Excelu 2007: Oś tekstu).
Rys. 2. Okno opcji wykresu
3. Zatwierdź, klikając OK.
Rys. 3. Wykres tylko z datami dni roboczych
Trik 5
Rejestr bez pustych wierszy
Pobierz plik z przykładem
Zdarza się, że niektóre rejestry dajesz do wypełnienia swojemu współpracownikowi. Warto wówczas zabezpieczyć się przed pozostawianiem przez niego pustych wierszy pomiędzy danymi. Wiele narzędzi Excela wymaga bowiem, aby analizowany obszar był spójny i kompletnie wypełniony. Jeśli tak nie jest, czeka Cię żmudne uzupełnianie braków lub ręczne usuwanie pustych wierszy. Okazuje się, że możesz się przed tym bardzo łatwo uchronić.
Fragment przykładowej tabeli przedstawia rysunek 1.
Rys. 1. Arkusz, który będzie uzupełniany kolejnymi danymi
W przedstawionym arkuszy chciałbyś nadać takie ograniczenie w zakresie A2:D30, aby niemożliwe było pozostawienie pustych komórek wewnątrz listy.
=> Aby to zrobić:
1. Rozpoczynając od komórki A2, zaznacz zakres A2:D30.
2. Z menu Dane wywołaj polecenie Sprawdzanie poprawności (w Excelu 2007: uaktywnij kartę Dane i w grupie poleceń Narzędzia danych wskaż Poprawność danych).
3. Z listy dozwolonych kryteriów poprawności wybierz pozycję Niestandardowe.
4. Usuń zaznaczenie pola Ignoruj puste.
5. W polu Formuła wprowadź:
=LICZ.PUSTE(A$2:A2)=0
Rys. 2. Ustawienia sprawdzania poprawności
6. Zatwierdź ustawienia, klikając przycisk OK.
Teraz jeżeli ponad kolejnym wpisem pozostawisz pustą komórkę, pojawi się komunikat ostrzegawczy widoczny na rysunku 3.
Rys. 3. Pozostawienie pustych wierszy jest niemożliwe
W ten oto sposób masz pewność, że arkusz uzupełniony przez współpracownika jest gotowy do analizy i nie wymaga poprawek.
|