Redakcja

Autorzy:

Piotr Dynia, Jakub Kudliński

Kierownik grupy wydawniczej:

Ewa Ziętek-Maciejczyk

Wydawca:

Monika Kijok

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład, przygotowaniew do wersji elektronicznej:

Norbert Bogajczyk

Przygotowanie do wersji elektronicznej:

RASTER studio, 603 59 59 71

Projekt okładki:

Piotr Fedorczyk

Druk: Miller

ISBN: 978-83-269-3484-1

Copyright by Wydawnictwo Wiedza i Praktyka sp. z o.o.

Warszawa 2014

Wydawnictwo Wiedza i Praktyka sp. z o.o.

03-918 Warszawa, ul. Łotewska 9a

tel. 22 518 29 29, faks 22 617 60 10

NIP: 526-19-92-256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

„Excel w dziale księgowości, cz. 2” wraz z przysługującymi Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w „Excelu w dziale księgowości, cz. 2” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Publikacja „Excel w dziale księgowości, cz. 2” została przygotowana z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Excel w dziale księgowości, cz. 2” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Excel w dziale księgowości, cz. 2” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przypadków.

Wstęp

Księgowość nie sprowadza się tylko do wystawiania takich dokumentów jak rachunki czy faktury. Do zadań księgowego należy także sporządzanie deklaracji podatkowych, uzgadnianie sald, przygotowywanie raportów i sprawozdań finansowych. W związku z tym musi sporządzać szereg różnych dokumentów i sprawozdań, w czym bardzo pomocny jest Excel. W tej książce przedstawiamy dość dobrze znane narzędzia tego programu, ale skupiamy się na ich zaawansowanych możliwościach, których większość użytkowników nie zna.

Czytelnicy poznają, np. filtr zaawansowany. Jego możliwości znacznie przekraczają to, co oferuje dobrze znany autofiltr. Wchodzimy również w tajniki sortowania, pokazując, jak stworzyć własną, niestandardową listę sortowania. Dzięki tym dwóm funkcjom można przeanalizować dane czy wybrać informacje potrzebne do sporządzenia raportów.

Sporo miejsca poświęcamy zagadnieniom związanym z automatyzacją i usprawnianiem pracy. Pokazujemy, jak można zastąpić długie formuły krótkimi nazwami, co znacznie ułatwia ich wielokrotne używanie. Krok po kroku opisujemy, jak przygotować szablon zaświadczenia o zarobkach i napisać makra, które automatycznie wypełnią ten formularz niezbędnymi danymi.

Gdzie to tylko możliwe, pokazujemy szybsze sposoby wykonania różnych czynności – za pomocą lub z użyciem przydatnych skrótów. Warto się ich nauczyć, ponieważ znacznie przyspieszają pracę z Excelem. Wszystkie arkusze Excela z przykładami omawianymi w książce można pobrać ze strony http://online.wip.pl/download/exceltom8.zip.

Wszystkie pliki Excela z przykładami

omawianymi w książce można pobrać

ze strony:

http://online.wip.pl/download/exceltom8.zip

1. Nazwy zdefiniowane – szybkie wyznaczanie ważnych dat

Obliczenia, np. związane z wartościami reprezentującymi daty, są niezwykle kłopotliwe szczególnie dla początkujących użytkowników Excela. Trzeba nabrać doświadczenia i poznać zasady działania na datach, aby swobodnie pracować w arkuszu z liczbami odnoszącymi się do tych wartości. W tym rozdziale pokazujemy, jak przygotować formuły-szablony, które można wykorzystywać do szybkiego wyznaczenia dat specjalnych, np. ostatniego dnia kwartału czy ostatniego czwartku w miesiącu. Jest to przydatne w tych firmach, w których dniem wypłaty pensji jest właśnie któryś ostatni dzień tygodnia w miesiącu. Dzięki nadaniu formułom nazw dostęp do nich będzie szybki i wygodny.

1.1. Często używane formuły zwracające daty

Jeśli formułom zwracającym specjalne daty nadamy nazwy, oszczędzimy wiele czasu i pracy na samą analizę danych. Obok szybkiego dostępu do formuł zyskamy również to, że odpowiednio zbudowane formuły będą działały zależnie od aktualnej daty.

Zaproponujemy pięć formuł dla specjalnych dat:

początek miesiąca – PMIESIĄCA,

koniec miesiąca – KMIESIĄCA,

ostatni czwartek miesiąca – OCZWARTEK,

początek kwartału – PKWARTAŁU,

koniec kwartału – KKWARTAŁU.

Określenie pierwszego dnia bieżącego miesiąca bywa bardzo przydatne w zestawieniach, w których wykorzystuje się czas trwania pewnego okresu do wyznaczenia niezbędnych zależności.

Oczywiście można każdorazowo wpisywać ręcznie datę do arkusza, jednak istotą formuły jest to, że będzie automatycznie aktualizowała zawartość komórki z datą. Na rysunku xx.1 pokazano fragment arkusza, w którym obliczana jest liczba dni, która upłynęła od danej daty do początku bieżącego miesiąca.

Wprowadzona w komórkę B3 formuła wygląda następująco:

=DATA(ROK(DZIŚ());MIESIĄC(DZIŚ());1)

Rysunek 1.1. Formuła zwracająca pierwszy dzień bieżącego miesiąca

Zasada działania formuły nie jest skomplikowana. Zastosowana w formule funkcja DATA ma za zadanie zwrócić w wyniku wartość reprezentującą datę. Budowana jest poprzez podawanie informacji kolejno o roku, miesiącu oraz dniu. W zaproponowanej formule zarówno rok, jak i miesiąc definiujemy z wykorzystaniem funkcji DZIŚ. Wartość reprezentującą dzień wpisujesz natomiast „na sztywno” w postaci cyfry 1, ponieważ właśnie pierwszy dzień miesiąca powinien zostać obliczony przez tę formułę.

Rysunek 1.2. Zaznaczanie składni formuły i kopiowanie jej do schowka

Żeby móc szybko stosować do różnych obliczeń raz zbudowaną formułę, warto wykorzystać jedną z zalet Excela, polegającą na możliwości nadawania formułom nazw. W tym celu:

1. Kliknijmy komórkę z formułą (B3), a następnie zaznaczmy na pasku formuły jej składnię.

2. Skopiujmy do schowka całą formułę, wykorzystując menu podręczne myszy lub kombinację klawiszy Ctrl + C.

3. Za pomocą klawisza Esc wyjdźmy z trybu edycji paska formuły.

4. Wywołajmy kartę Formuły i kliknijmy polecenie Definiuj nazwę.

5. W polu Nazwa nadajmy właściwą nazwę, w tym przypadku PMIESIĄCA.

Rysunek 1.3. Definiowanie nazwy dla formuły

6. Kliknijmy pole Odwołuje się do i zaznaczmy myszką zawartość pola.

7. Korzystając ze skrótu klawiaturowego Ctrl + V, wklejmy przechowywaną w schowku formułę.

8. Kliknijmy OK, aby opuścić okno dialogowe. Zdefiniujemy tym samym nową nazwę.

Po wykonaniu opisanych czynności można korzystać w arkuszu z nazwy PMIESIĄCA, aby obliczyć datę reprezentującą początek bieżącego miesiąca. Korzystanie ze zdefiniowanej nazwy polega na traktowaniu jej jak zwykłej funkcji, a więc na poprzedzeniu nazwy znakiem równości.

UWAGA

Zdefiniowana przez nas funkcja PMIESIĄCA zwraca datę w postaci liczby całkowitej. Jeśli chcemy, aby w komórce wyświetlała się jako data, należy zmienić format tej komórki na format daty. Służy do tego kombinacja klawiszy Ctrl Shift 3.

Rysunek 1.4. Nazwa działa jak formuła

Nie trzeba przy tym pamiętać wszystkich zdefiniowanych nazw. Zamiast wpisywać je ręcznie bezpośrednio w komórce, można wykorzystać na karcie Formuły polecenie Użyj w formule. Po wybraniu tego polecenia wyświetlone zostanie okno dialogowe (rysunek xx.5), w którym będziesz mógł wskazać jedną ze zdefiniowanych nazw i wstawić ją do aktywnej komórki w arkuszu.

Rysunek 1.5. Nazwę można również wstawiać przez wybieranie jej z listy

1.2. Przykłady formuł obliczających charakterystyczne daty

Powtarzając czynności opisane dla formuły obliczającej początek bieżącego miesiąca, można utworzyć cztery dalsze, których zadaniem będzie obliczanie kolejno:

końca miesiąca (KMIESIĄCA),

ostatniego czwartku miesiąca (OCZWARTEK),

początku kwartału (PKWARTAŁU),

końca kwartału (KKWARTAŁU).

Formuła obliczająca ostatni dzień bieżącego miesiąca ma następującą budowę:

=DATA(ROK(DZIŚ());MIESIĄC(DZIŚ())+1;1)-1

Zasada działania tej formuły jest bardzo podobna do opisanej wcześniej. Kluczową rolę pełni w dalszym ciągu funkcja DATA, która zbiera informacje o składowych daty (rok, miesiąc, dzień). Jej zadaniem jest zwrócenie do komórki końcowej wartości. Również bez zmian pozostaje zasada obliczenia z pomocą funkcji DZIŚ bieżącego roku, natomiast do obliczonego bieżącego miesiąca dodana została wartość 1. Powoduje to obliczenie daty pierwszego dnia następnego miesiąca. Dzień określony jest również na stałe jako pierwszy (1). W sumie powoduje to zwrócenie daty opisującej pierwszy dzień następnego miesiąca. Ostatni element formuły, czyli odejmowanie wartości 1, daje w efekcie ostatni dzień bieżącego miesiąca, więc formule tej można nadać nazwę KMIESIĄCA.

Mając formułę obliczającą ostatni dzień miesiąca, można z jej wykorzystaniem obliczyć ostatni czwartek (lub inny dzień tygodnia) w miesiącu na podstawie bieżącej daty.

=KMIESIACA-(DZIEŃ.TYG(KMIESIACA;14)-1)

Ta formuła najpierw oblicza datę ostatniego dnia bieżącego miesiąca. W naszym przykładzie wykorzystaliśmy do tego zdefiniowaną wcześniej funkcję KMIESIACA. Następnie w nawiasie funkcją DZIEŃ.TYG obliczamy dla ostatniego dnia miesiąca jego numer dnia tygodnia. Jako drugi parametr należy wpisać 14, co oznacza, że Excel będzie traktować czwartek jako pierwszy dzień tygodnia (czwartek będzie miał więc numer 1). Od tak otrzymanej wartości odejmujemy 1. Chodzi o to, aby dla czwartku wartość w nawiasie wynosiła zero. Nie chcemy bowiem modyfikować daty ostatniego dnia miesiąca, jeśli akurat jest to czwartek. Następnie formuła odejmuje od ostatniego dnia miesiąca wartość obliczoną w nawiasie. Efektem zawsze jest data ostatniego czwartku miesiąca. Należy zwrócić uwagę, że zewnętrzny nawias w opisanej formule został wprowadzony tylko po to, aby łatwiejsze było zrozumienie jej działania. Po usunięciu nawiasu należy przed wartością 1 zmienić znak na plus.

Rysunek 1.6. Formuła obliczająca datę dla ostatniego czwartku bieżącego miesiąca

Następny przykład obliczenia charakterystycznej daty to formuła zwracająca pierwszy dzień (początek) aktualnego kwartału. Ma następującą postać:

=DATA(ROK(DZIŚ());ZAOKR.GÓRA(MIESIĄC(DZIŚ())/3;0)*3-2;1)

Działanie formuły również oparte jest na funkcji DATA, przy czym podstawowa różnica względem formuł poprzednich polega na specjalnym określeniu liczby miesiąca. Jeśli liczbę określającą numer kolejny bieżącego miesiąca (np. wartość 6 dla czerwca) podzielimy przez liczbę miesięcy kwartału – czyli 3 – i wynik zaokrąglimy w górę do całości, a następnie ponownie przemnożymy przez 3 i od wyniku odejmiemy 2, to zawsze otrzymamy liczbę określającą numer miesiąca rozpoczynającego bieżący kwartał. Właśnie z tej zależności korzysta formuła. Liczba określająca dzień pozostaje stała i jest równa 1.

Rysunek 1.7. Efekt działania formuły obliczającej pierwszy dzień aktualnego kwartału

Ostatnia proponowana formuła zwraca datę ostatniego dnia bieżącego kwartału (koniec kwartału). Jest bardzo podobna do ostatniej, obliczającej pierwszy dzień kwartału.

=DATA(ROK(DZIŚ());ZAOKR.GÓRA(MIESIĄC(DZIŚ())/3;0)*3+1;1)-1

Różnica w budowie, a tym samym w działaniu formuły, jest jasna. Podobnie jak w przypadku obliczenia ostatniego dnia miesiąca, zasada jest taka: najpierw obliczany zostaje pierwszy dzień kwartału następnego, a potem odejmowany jeden dzień, co daje w efekcie ostatni dzień bieżącego kwartału.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Wstęp

1. Nazwy zdefiniowane – szybkie wyznaczanie ważnych dat

2. Filtrowanie i sprawdzanie poprawności danych

3. Zaawansowane sortowanie danych

4. Sumy częściowe i grupowanie danych

5. Porządkowanie danych z wykorzystaniem funkcji tekstowych

6. Szybkie wystawianie zaświadczeń o zarobkach z użyciem makr

7. Drukowanie analiz

8. Wymiana danych między Excelem a Wordem

9. Szybki wybór danych do analizy – funkcje bazodanowe