:: Trik 1. Obsługa rejestrów za pomocą pól wyboru
:: Trik 2. Rozbudowa obliczeń w tabeli przestawnej
:: Trik 3. Lista dat późniejszych o określoną liczbę miesięcy
:: Trik 4. Przypisywanie cen do towarów z danej kategorii
:: Trik 5. Powiększanie zestawienia tylko na wydruku
Trik 1
Obsługa rejestrów za pomocą pól wyboru
Pobierz plik z przykładem
Arkusz Excela jest bardzo często wykorzystywany do budowy rejestrów. Zwykle służą one jedynie do zaznaczania, który projekt został zakończony, jakie produkty zostały wysłane do danego odbiorcy itd. Jeśli tworzysz podobne zestawienia, to proponujemy Ci umieszczanie w nich formantów o nazwie pole wyboru. Dzięki temu posługiwanie się rejestrem będzie dużo prostsze i wygodniejsze. Przykładowy arkusz przedstawia rysunek 1.
Rys. 1. Prosty rejestr
Zaznaczasz w nim, które towary zostały wysłane do danego odbiorcy. Możesz to wygodnie robić z wykorzystaniem formantu.
=> W tym celu:
1. Kliknij prawym przyciskiem myszy dowolny pasek narzędziowy i wybierz z listy pozycję Formularze (w Excelu 2007: uaktywnij kartę Deweloper, w grupie poleceń Formanty kliknij przycisk Wstaw).
Uwaga
Jeśli w Twoim Excelu karta Deweloper jest niewidoczna, uaktywnij ją w oknie opcji programu.
2. Zaznacz pole wyboru (w Excelu 2007: użyj formantu z grupy Formanty formularza) i narysuj formant w obrębie komórki B2.
Rys. 2. Wstawianie pola wyboru
3. Kliknij go prawym przyciskiem myszy i wybierz polecenie Edytuj tekst.
4. Usuń opis formantu, wciskając i przytrzymując klawisz Delete.
5. Zaznacz komórkę B2 (nie formant) i skopiuj ją do pozostałych komórek rejestru, chwytając prawy dolny róg komórki (uchwyt wypełnienia).
Gotowe! Formanty zostały skopiowane, a Ty w kilka chwil stworzyłeś wygodny w korzystaniu rejestr.
Rys. 3. Wygodny rejestr jest gotowy
Trik 2
Rozbudowa obliczeń w tabeli przestawnej
Pobierz plik z przykładem
Przyjmijmy, że utworzyłeś prostą tabelę przestawną. Okazało się jednak, że zawarte w niej kwoty powinny uwzględniać 22% podatek. Czy to oznacza, że Twoja dotychczasowa praca poszła na marne i musisz modyfikować dane źródłowe, a następnie od nowa budować raport przestawny? Niezupełnie! Niewielu użytkowników Excela wie, że w tabelach przestawnych można dodawać własne pola obliczeniowe.
Rys. 1. Tabela przestawna z kwotami, które trzeba poprawić
=> Aby to zrobić:
1. Kliknij dowolną komórkę tabeli przestawnej. Wyświetlone zostanie okno dialogowe z listą pól tabeli oraz pasek narzędziowy Tabela przestawna, jeżeli korzystasz z wersji Excela wcześniejszej niż 2007.
Wskazówka
Jeśli po kliknięciu tabeli przestawnej nie został wyświetlony pasek narzędziowy, musisz go uaktywnić ręcznie. Kliknij prawym przyciskiem myszy w obszarze tabeli i wybierz z menu podręcznego polecenie Pokaż pasek narzędzi Tabela przestawna.
2. Na uaktywnionym pasku narzędziowym wybierz polecenie Tabela przestawna/Formuły/Pole obliczeniowe (w Excelu 2007: uaktywnij kartę Opcje i w grupie poleceń Narzędzia wskaż Formuły/Pole obliczeniowe).
3. Po wywołaniu tego polecenia wyświetlone zostanie okno dialogowe Wstawianie pola obliczeniowego. W polu Nazwa wpisz nazwę opisującą dane, które będziesz obliczał, np. Brutto.
4. Przejdź teraz do pola Formuła i usuń domyślnie wpisaną tam cyfrę 0.
5. Wartość brutto uzyskasz, mnożąc kwoty przez 1,22 - więc taką formułę musisz zbudować w bieżącym polu. Aby to zrobić, kliknij dwukrotnie pozycję Kwota w dolnej części okna, w wyniku czego automatycznie nazwa zakresu zostanie wstawiona do formuły. Teraz dopisz jeszcze *1,22 i gotowe. Rysunek 2 prezentuje opisywane etap modyfikacji tabeli przestawnej.
Rys. 2. Tworzenie dodatkowego pola obliczeniowego
Po wybraniu przycisku OK Excel zmodyfikuje istniejącą tabelę przestawną, uwzględniając utworzone właśnie pole obliczeniowe. Tabela przestawna będzie teraz rozbudowana o dodatkową pozycję - patrz kolejny rysunek.
Rys. 3. Obliczenia zostały uwzględnione w tabeli
Trik 3
Lista dat późniejszych o określoną liczbę miesięcy
Pobierz plik z przykładem
Twoja firma prowadzi ratalną sprzedaż produktów. Klient poprosił Cię o przygotowanie listy dni, w jakie powinny wpłynąć kolejne płatności z założeniem, że każda rata jest opóźniona o 2 miesiące. Przykładowe zestawienie przedstawia rysunek 1.
Rys. 1. Tabela wpłat
=> Aby w uzyskać listę terminów, wykonaj następujące czynności:
1. Wprowadź datę pierwszej wpłaty do komórki B4, np. 2009-01-31.
2. Do komórki poniżej wprowadź następującą formułę:
Uwaga
Aby móc korzystać z funkcji EDATE, należy uaktywnić dodatek Excela o nazwie Analysis ToolPak. Dotyczy to Excela w wersji wcześniejszej niż 2007. W tym celu z menu Narzędzia wybierz polecenie Dodatki. Zaznacz dodatek na liście i kliknij OK.
3. Następnie skopiuj ją poniżej.
4. Uzyskanym liczbom nadaj format daty. Zaznacz je i wciśnij kombinację klawiszy Ctrl + 1.
5. Przejdź do zakładki Liczby, zaznacz kategorie Daty i czasu, a następnie zatwierdź, klikając OK.
Rys. 2. Wyznaczone terminy
Trik 4
Przypisywanie cen do towarów z danej kategorii
Pobierz plik z przykładem
W arkuszu znajduje się spis wszystkich towarów oferowanych przez Twoją firmę. Każdy z nich ma swój identyfikator, w którym pierwszy znak (litera) oznacza kategorię cenową. Twoim zadaniem jest zbudowanie na podstawie takiej tabeli cennika towarów.
Rys. 1. Przykładowy spis towarów
Pierwszym krokiem w celu zbudowania cennika będzie określenie w tabeli pomocniczej cen dotyczących poszczególnych kategorii. Może ona wyglądać jak na rysunku 2.
Rys. 2. Tabela z kategorii cenowych w kolumnach E i F
Aby przyporządkować poszczególne ceny do towarów, potrzebujesz posłużyć się w kolumnie C odpowiednią formułą wyszukującą. Powinna najpierw porównać pierwszy znak identyfikatora towaru z kategorią cenową i w przypadku zgodności zwrócić odpowiednią kwotę. Do tego celu świetnie nadadzą się odpowiednio połączone funkcje LEWY oraz WYSZUKAJ.PIONOWO.
=> Aby wyznaczyć ceny towarów:
1. W komórce C2 wprowadź następującą formułę:
=WYSZUKAJ.PIONOWO(LEWY(A2;1);$E$2:$F$5;2;FAŁSZ)
2. Skopiuj ją do komórek poniżej i otrzymanym wartościom nadaj format walutowy.
Rys. 3. Ceny zostały przyporządkowane poszczególnym produktom
Wyjaśnienie działania formuły:
W pierwszym argumencie funkcji WYSZUKAJ.PIONOWO wskazujesz szukaną wartość. Ze względu na to, że nie znajduje się ona w osobnej komórce, a stanowi fragment innej wartości, zastosowaliśmy funkcję LEWY. Zwróci ona pierwszy znak wpisu z komórki A2, a zatem potrzebne oznaczenie kategorii. W drugim argumencie funkcji podajesz tablice, która ma zostać przeszukana pod kątem występowania znaku z pierwszego argumentu (literowe oznaczenie kategorii) oraz zawiera wartości, które mają być zwrócone przez formułę (ceny). W trzecim argumencie funkcji WYSZUKAJ.PIONOWO podajesz numer kolumny w obrębie tablicy z drugiego argumentu, w której znajdują się ceny. Ostatni argument funkcji (FAŁSZ) oznacza, że w trakcie wyszukiwania danych ma być zastosowane dokładne dopasowanie.
Trik 5
Powiększanie zestawienia tylko na wydruku
Pobierz plik z przykładem
Próbujesz wydrukować zestawienie obejmujące kilka lub kilkanaście komórek. Po przeniesieniu na papier formatu A4 będzie ono mało widoczne i zajmie niewielki fragment strony. Aby uczynić dane bardziej czytelnymi, użytkownicy Excela często powiększają czcionkę w arkuszu (co niestety zwykle powoduje zmianę układu danych w komórkach), a po wydrukowaniu wracają do pierwotnego rozmiaru. Proponujemy Ci szybsze rozwiązanie.
=> W tym celu:
1. Zaznacz zakres, który obejmuje zestawienie, z menu Plik wybierz polecenie Ustawienia strony i przejdź do zakładki Strona (w Excelu 2007: uaktywnij kartę Układ strony i kliknij mały przycisk Więcej opcji znajdujący się w prawym dolnym rogu grupy poleceń Ustawienia strony).
2. W sekcji Skalowanie, w polu Dopasuj do ustaw większa wartość niż 100% - np. 150%.
Rys. 1. Skalowanie
3. Zanim zatwierdzisz OK kliknij przycisk Podgląd wydruku, aby sprawdzić, jak powiększone zestawienie będzie wyglądać na drukowanej stronie.
|