:: Trik 1. Numeracja porządkowa uwzględniająca niewypełnione wiersze
:: Trik 2. Otwieranie i zamykanie wielu skoroszytów za jednym razem
:: Trik 3. Hurtowe poprawianie błędnie rozpoznanych dat
:: Trik 4. Szybkie zliczanie kwot należących do poszczególnych przedziałów
:: Trik 5. Czytelna prezentacja porównawcza na wykresie giełdowym
Trik 1
Numeracja porządkowa uwzględniająca niewypełnione wiersze
Pobierz plik z przykładem
W arkuszu znajduje się zestawienie transakcji. Niestety pojawia się problem. Musisz ponumerować poszczególne pozycje, a na liście jest mnóstwo pustych wierszy. W jaki sposób to zrobić bez konieczności ręcznego usuwania niewypełnionych zakresów? Rozwiązaniem jest sprytna formuła.
Przykładowa tabela z pustymi wierszami jest przedstawiona na rysunku 1.
Rys. 1. Tabela z pustymi wierszami
=> Aby nadać jej poprawną numerację:
1. W pierwszej kolejności wstaw pustą kolumną po lewej. W tym celu kliknij nagłówek kolumny A, aby ją całą zaznaczyć i wciśnij kombinację klawiszy Ctrl + Shift + = (znak równości).
2. Do komórki A2 wprowadź następującą formułę:
=JEŻELI(B2<>"";ILE.NIEPUSTYCH($B$2:B2);"")
3. Skopiuj ją do komórek poniżej.
Rys. 2. Lista poprawnie ponumerowana
Wyjaśnienie działania formuły:
W pierwszym argumencie funkcji JEŻELI sprawdzane jest, czy komórka B2 nie jest pusta. Jeśli nie jest pusta, wówczas wykonywana jest funkcja zawarta w drugim argumencie. W przeciwnym razie zwracany jest pusty ciąg znaków. W drugim argumencie funkcji JEŻELI zastosowano funkcję ILE.NIEPUSTYCH, która zwraca liczbę wypełnionych komórek w określonym zakresie. Zauważ, że dzięki odpowiedniemu adresowaniu ($B$2:B2), w miarę kopiowania w dół rozszerza się zakres działania funkcji. Np. w komórce A6 funkcja ILE.NIEPUSTYCH będzie operować już na zakresie B2:B6. Cała numeracja wprowadzana za pomocą formuły opiera się zatem na zliczaniu wypełnionych komórek ze stale rozszerzającego się zakresu.
Trik 2
Otwieranie i zamykanie wielu skoroszytów za jednym razem
Pobierz plik z przykładem
Dość często się zdarza, że dane, które są Ci potrzebne do wykonania analizy znajdują się w oddzielnych plikach (skoroszytach). Przed rozpoczęciem obliczeń klikasz więc kolejno pliki znajdujące się w określonym folderze. Okazuje się, że można to zrobić szybciej i otworzyć wiele plików za jednym razem.
=> W tym celu:
1. Uruchom Excela i z menu Dane wybierz polecenie Otwórz (w Excelu 2007: polecenie Otwórz znajduje się na pasku Szybki dostęp). Możesz także skorzystać ze skrótu klawiaturowego Ctrl + O.
2. W wyświetlonym oknie dialogowym odszukaj folder zawierający skoroszyty, z których chcesz skorzystać.
3. Kliknij pierwszy z nich, przytrzymaj klawisz Shift, a następnie kliknij ostatni.
4. Wybierz przycisk Otwórz, a wszystkie zaznaczone w ten sposób pliki zostaną otwarte.
Rys. 1. Jednoczesne otwieranie wielu plików
Wskazówka
Jeśli chcesz podświetlić wybrane skoroszyty, zamiast klawisza Shift użyj Ctrl.
=> Aby jednocześnie zamknąć wszystkie otwarte skoroszyty (sposób działa w wersjach Excela wcześniejszych niż 2007):
1. Trzymając wciśnięty klawisz Shift, kliknij menu Plik.
2. Pojawi się w nim nowe polecenie Zamknij wszystkie.
Rys. 2. Nowe polecenie w menu Plik
Wskazówka
W Excelu 2007 też masz możliwość szybkiego zamykania wszystkich skoroszytów. W tym celu należy do paska narzędzi Szybki dostęp dodać polecenie Zamknij wszystkie.
Trik 3
Hurtowe poprawianie błędnie rozpoznanych dat
Pobierz plik z przykładem
Przyjmijmy, że poprosiłeś swojego pracownika o wprowadzenie do arkusza Excela szczegółowych informacji o zamówieniach. Niestety w jednej z kolumn daty zostały niepoprawnie wpisane. Np. data 23 listopada 2008 r. jest wyświetlana w postaci 23-11-08 i Excel traktuje ją jako 2023-11-08. Tak wprowadzone daty są bezużyteczne, gdyż nie będzie możliwości przygotowania chociażby raportu okresowego z wykorzystaniem tabel przestawnych. Czy czeka Cię zatem ręczne poprawianie kilkudziesięciu dat? Niekoniecznie!
Rys. 1. Niepoprawnie wprowadzone daty
=> Aby uzyskać poprawny zapis:
1. Wstaw nową kolumnę pomiędzy B i C. W tym celu kliknij nagłówek kolumny C, aby ją zaznaczyć i wciśnij kombinację klawiszy Ctrl + Shift + = (znak równości).
2. Nadaj jej nazwę Data.
3. Do komórki C2 wprowadź następującą formułę:
=DATA(DZIEŃ(B2)+ZAOKR.DÓŁ(ROK(B2);-2);
MIESIĄC(B2);MOD(ROK(B2);100))
i skopiuj ją na wymaganą liczbę wierszy.
4. Zaznacz uzyskane daty i wciśnij kombinację klawiszy Ctrl + C.
5. Nie zmieniając zaznaczenia, z menu Edycja (w Excelu 2007: uaktywnij kartę Narzędzia główne i w grupie poleceń Schowek rozwiń listę Wklej) wybierz polecenie Wklej specjalnie.
6. Zaznacz opcję Wartości i kliknij OK.
7. Usuń kolumnę z błędnymi datami.
Rys. 2. Daty zostały poprawione
Przedstawiona formuła jest uniwersalna i możesz ją stosować w dowolnych zestawieniach. Dostosuj jedynie odwołanie do komórki, w której znajduje się błędna data.
Trik 4
Szybkie zliczanie kwot należących do poszczególnych przedziałów
Pobierz plik z przykładem
Jeśli zachodzi potrzeba zliczenia liczb należących do określonego przedziału, najczęściej używa się funkcji LICZ.JEŻELI. Jeśli jednak potrzebujesz uwzględnić różne przedziały, wówczas korzystniej jest zastosować funkcję CZĘSTOŚĆ. Przyjmijmy, że potrzebujesz szybko sprawdzić, ile jest transakcji mniejszych lub równych 1000; większych niż 1000 i mniejszych lub równych 5000 oraz większych od 5000.
Przykładową listę transakcji, a także pomocniczą tabelkę wynikową zawierającą kryteria, przedstawia rysunek 1.
Rys. 1. Przykładowa lista transakcji
=> Aby błyskawicznie uzyskać wyniki:
1. Zaznacz zakres komórek E2:E4.
2. Wpisz następującą formułę:
=CZĘSTOŚĆ(B2:B13;{1000;5000})
3. Koniecznie zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter.
Prawidłowo wprowadzona formuła powinna zostać ujęta w nawiasy klamrowe (widoczne na pasku formuły).
Rys. 2. Formuła błyskawicznie zwróciła poprawne wyniki
Trik 5
Czytelna prezentacja porównawcza na wykresie giełdowym
Pobierz plik z przykładem
Prezentacja porównawcza, która ma uwzględnić różne kategorie wartości, nie zawsze jest łatwa do przygotowania. Wprawdzie Excel posiada dość bogatą ofertę wykresów, ale nie zawsze da się szybko podjąć decyzję, który typ prezentacji będzie najlepszy do przejrzystego zobrazowania informacji. Jeżeli przyjdzie Ci konfrontować ze sobą wartości maksymalne, minimalne i średnie, proponujemy zastosować wykres giełdowy.
Aby tabele z danymi źródłowymi mogły być przedstawione na wykresach typu giełdowego, powinny spełniać pewne wymagania. Na rysunku 1 znajdują się wielkości utargów, osiągniętych w czasie trwania 10-tygodniowej kampanii reklamowej, która nie była przeprowadzana równolegle we wszystkich oddziałach.
Rys. 1. Dane źródłowe
Twoim zadaniem jest zaprezentowanie wartości średnich, minimalnych i maksymalnych, powinieneś zatem w pierwszej kolejności je wyznaczyć.
=> W tym celu:
1. W komórce B12 wpisz formułę:
4. Skopiuj wszystkie formuły do komórek po prawej.
Rys. 2. Niezbędne obliczenia
Tabela źródłowa z potrzebnymi danymi jest gotowa. Teraz przejdź do przygotowania samego wykresu.
=> W tym celu:
1. Zaznacz obszar nagłówków B1:D1 i przytrzymując klawisz Ctrl, dodatkowo podświetl komórki B12:D14.
2. Uruchom Kreator wykresów (w Excelu 2007: uaktywnij kartę Wstawianie i kliknij Inne wykresy) i na liście typów wykresów zaznacz Giełdowy.
3. Wskaż podtyp Maks-min-zamknięcie.
4. Teraz kliknij dwa razy Dalej, aby przejść do 3 kroku Kreatora wykresów (w Excelu 2007: pomiń ten krok).
5. Tam przejdź do zakładki Legenda i odznacz opcję Pokazuj legendę (w Excelu 2007: na wstawionym wykresie zaznacz legendę i wciśnij klawisz Delete).
6. Kliknij przycisk Zakończ (w Excelu 2007: pomiń ten krok).
Rys. 3. Gotowy wykres
Z pionowych linii rozpiętości odczytasz, w jakim przedziale wartości, zlokalizowane są utargi każdego oddziału. Górne końce linii przedstawiają każdorazowo wartości maksimum dla każdego oddziału, dolne końce - to minimum. Oddział 1 ma największą rozpiętość, a drugi - najmniejszą. Trzecią informacją, jaką przedstawia wykres w opisywanej postaci, jest średnia. Jest zaznaczona poziomą kreską.
|