Excel w dziale księgowości. Część 2 - ebook

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.

Darmowy fragment