Redakcja

Excel w dziale księgowości, cz. 1

Jakub Kudliński

Wojciech Próchnicki

Autorzy:

Jakub Kudliński, Wojciech Próchnicki

Kierownik grupy wydawniczej:

Ewa Ziętek-Maciejczyk

Wydawca:

Monika Kijok

Redaktor prowadzący:

Rafał Janus

Korekta:

Zespół

Skład i łamanie:

Katarzyna H. Kopeć, Norbert Bogajczyk

Projekt okładki:

Piotr Fedorczyk

Druk: Miller

ISBN: 978-83-269-3406-3

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. 1” wraz z przysługującym 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. 1” 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. 1” 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. 1” 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. 1” lub w innych dostępnych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przypadków.

Wstęp

W księgowości Microsoft Excel jest bardzo popularnym narzędziem, ponieważ oferuje wiele funkcji ułatwiających budżetowanie, tworzenie bilansów czy raportów finansowych. Ponadto umożliwia importowanie danych z zewnętrznych źródeł czy korzystanie z szablonów najczęściej używanych dokumentów. Praktyka pokazuje jednak, że większość użytkowników korzysta zaledwie z 10% możliwości oferowanych przez Excela. Efektem jest niepotrzebny nadmiar pracy, którą można uprościć i zautomatyzować.

Autorzy książki pokazują czytelnikom pomysłowe sposoby wykorzystania Excela w księgowości. Pokazują m.in., jak przygotować szablon do planowania kosztów amortyzacji czy szablon do wystawiania faktur mający wbudowane mechanizmy automatycznej numeracji kolejnych dokumentów. Takie możliwości pozwala osiągnąć zastosowanie makr, których proces tworzenia został dokładnie opisany krok po kroku, z wyjaśnieniem działania zastosowanych funkcji Visual Basica. Lektura dostarczy również informacji, jak szybko wyszukiwać przeterminowane należności i z wykorzystaniem narzędzi korespondencji seryjnej sprawnie przygotować listy windykacyjne.

Dzięki lekturze książki Czytelnicy poznają, jak można wykorzystać Excela i bazy danych w biznesie. Dowiedzą się, jak stosować to narzędzie do rozwiązywania rzeczywistych problemów w księgowości. Zrozumienie omawianych zagadnień ułatwi duża liczba zrzutów ekranowych, instrukcje krok po kroku oraz przykładowe pliki Excela, które można pobrać ze strony http://online.wip.pl/download/exceltom7.zip.

Wszystkie pliki Excela z przykładami

omawianymi w książce można pobrać

ze strony:

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

1. Listy windykacyjne sporządzane z użyciem korespondencji seryjnej

W tym rozdziale zadaniem jest przygotowanie pism windykacyjnych dla klientów zalegających z płatnościami. Z systemu Excel pobrał zestawienie zawierające potrzebne mu dane. Okazało się jednak, że osób, do których powinny zostać wysłane przypomnienia o zaległych płatnościach, jest około 340. Musimy stworzyć szablon pisma, a następnie zmieniać dane dla każdej z tych osób oraz przygotować zaadresowane koperty. Przy tak dużej liczbie pism oczywiście można się łatwo pomylić przy kopiowaniu danych, np. adresów czy kwot. Takie sytuacje trzeba będzie później „odkręcać”, a błędne pisma będą źle świadczyć o firmie. Dlatego też trzeba zrobić całą operację w sposób szybki i automatyczny, zmniejszający możliwość wystąpienia błędów do minimum.

W tym celu wykorzystamy narzędzie zwane korespondencją seryjną, które służy do tworzenia w edytorze tekstowym wielu dokumentów, różniących się między sobą jedynie zawartością kluczowych pól. Wartości tych pól pobierane są z zewnętrznej bazy danych.

1.1. Baza klientów i szablon pisma

W pliku mamy zestawienie klientów (w układzie imię, nazwisko, adres, kraj pochodzenia, telefon, adres e-mail) oraz kwotę należności. Lista została wygenerowana z systemu finansowo-księgowego. Baza liczy 333 klientów, do których należy wysłać listy upominawcze. Ich przygotowanie w Wordzie osobno dla każdego klienta jest pracochłonne. Excel w połączeniu z Wordem wydatnie skrócą czas pracy.

Dla utrudnienia zakładamy, że wysyłamy pisma z przypomnieniem o zaleganiu tylko do klientów z Polski. Czyli dodatkowo musimy przy tworzeniu korespondencji wyfiltrować dane z posiadanego zestawienia (rysunek 1.1).

Następnie przygotowujemy w Wordzie krótki list dla klientów z Polski z numerem rachunku. Wyróżnione pola są miejscami, w które będziemy wstawiać dane poszczególnych klientów (rysunek 1.2).

Rysunek 1.1. Baza osób, do których zostaną wysłane powiadomienia

Rysunek 1.2. Szablon pisma o wierzytelnościach

1.2. Połączenie bazy z szablonem, drukowanie listów

Kolejnym krokiem jest podpięcie bazy z klientami do naszego szablonu listu. W tym celu w szablonie pisma przechodzimy na wstążkę Korespondencja/Rozpoczynanie korespondencji seryjnej/Wybierz adresatów/Użyj istniejącej listy (rysunek 1.3).

Rysunek 1.3. Podpięcie bazy

Wskazujemy lokalizację pliku Excela z bazą klientów (rysunek 1.4).

Rysunek 1.4. Wskazanie lokalizacji bazy w pliku Excel

Wskazujemy zakładkę, w której znajduje się zestawienie (rysunek 1.5).

W naszym szablonie pisma w lewym górnym rogu (Dane osobowe klienta) wstawimy bloku adresu, który będzie nam pobierał dane adresowe (imię, nazwisko, ulicę, miejscowość i kod pocztowy) z bazy klientów (rysunek 1.6). Kasujemy wpisany tekst, przechodzimy na wstążkę Korespondencja/Wpisywanie i wstawianie pól/Blok adresu (rysunek 1.6).

Rysunek 1.5. Wskazanie zakładki z zestawieniem

Rysunek 1.6. Lokalizacja bloku adresu

Pojawi się okno wstawiania bloku adresu zawierającego imię, nazwisko i miasto (rysunek 1.7).

Jest to ustawienie domyślne. W celu dopasowania pól z bazy do wymogów bloku adresu wciskamy przycisk Dopasuj pola. W naszym przykładzie pod Adres 1 podpinamy pole Ulica oraz pod adres e-mail podpinamy pole mail (rysunek 1.8).

Rysunek 1.7. Okienko Wstawianie bloku adresu

Rysunek 1.8. Okienko Dopasowywanie pól

W piśmie można wyróżnić miejsca, w których mamy wstawione pola z bazy. W tym celu przechodzimy na wstążkę Korespondencja/Wpisywanie i wstawianie pól/Wyróżnij pola koresp. ser. (rysunek 1.9).

Rysunek 1.9. Opcja Wyróżnij pola koresp. ser.

Miejsca takie wyróżnione zostaną kolorem szarym w szablonie pisma (rysunek 1.10).

Rysunek 1.10. Efekt zastosowania opcji wyróżnienia pól korespondencji seryjnej

Teraz należy dodać warunek, który pobierze nam tylko klientów z Polski. W tym celu przechodzimy na wstążkę Korespondencja/Rozpoczynanie korespondencji seryjnej/Edytuj listę adresatów (rysunek 1.11).

Rysunek 1.11. Opcja Edytuj listę adresatów

Pojawi się lista pobrana z bazy. W oknie dialogowym wybieramy Filtruj (rysunek 1.12).

Rysunek 1.12. Okno Adresaci korespondencji seryjnej

Dla pola Kraj ustawiamy wartość filtra Polska. Dzięki temu stworzymy listy jedynie dla polskich klientów (rysunek 1.13).

Rysunek 1.13. Ustawienia filtra dla klientów pochodzących jedynie z Polski

Następnie w pozostałe zaznaczone wcześniej miejsca wstawiamy nazwisko oraz kwotę zaległości. Umożliwia to przycisk Wstaw pola koresp. ser. Po jego wciśnięciu mamy możliwość wybrania z bazy dowolnego pola (rys. 1.14).

Rysunek 1.14. Wstawianie wybranego pola korespondencji seryjnej

Kolejnym krokiem jest sprawdzenie, czy podczas generowania listów występują błędy. Przechodzimy na wstążkę Korespondencja/Podgląd wyników/Wyszukaj błędy automatycznie (rysunek 1.15).

Rysunek 1.15. Opcja Wyszukaj błędy automatycznie

Następnie wybieramy opcję Symuluj scalanie i raportuj o błędach w nowym dokumencie, która pozwoli nam na sprawdzenie czy podczas generowania pism nie wystąpi żaden błąd (rysunek 1.16).

Rysunek 1.16. Okno Sprawdzanie i raportowanie błędów

Jeśli nie wykryto żadnych błędów, można przejść do tworzenia listów. W tym celu klikamy Korespondencja/Kończenie/Zakończ i scal. Do wyboru są trzy możliwości:

1. Edytuj poszczególne dokumenty – otrzymamy zestawienie w pliku DOC (dokument Worda).

2. Drukuj dokumenty – listy zostaną wydrukowane.

3. Wyślij wiadomości e-mail – jeżeli w bazie jest pole z adresami e-mail, dokumenty zostaną wysłane e-mailem (jako wiadomość lub załącznik) (rysunek 1.17).

Rysunek 1.17. Opcja Zakończ i scal

Wybieramy pierwszą opcję Edytuj poszczególne dokumenty. Zaznaczamy Wszystko i wciskamy OK (rysunek 1.18).

Rysunek 1.18. Okno Scalanie z nowym dokumentem

W nowym pliku Word stworzy nam się 319 pism dla polskich klientów.

1.3. Drukowanie kopert

Przygotujemy również zaadresowane koperty do listów. Otwieramy nowy plik Worda. Następnie podłączamy naszą bazę w Excelu (Korespondencja/Rozpoczynanie korespondencji seryjnej/Wybierz adresatów/Użyj istniejącej listy). Kolejnym krokiem jest ustawienie formatu strony na kopertę. W tym celu przechodzimy na wstążkę Korespondencja/Tworzenie/Koperty (rysunek 1.19) i w nim oknie klikamy Opcje.

Wybieramy rodzaj koperty oraz w zakładce Opcje drukowania ustawiamy układ koperty w drukarce. Klikamy OK, a następnie Dodaj do dokumentu.

Rysunek 1.19. Opcja Koperty

Rysunek 1.20. Okno Opcje kopert

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

1. Listy windykacyjne sporządzane z użyciem korespondencji seryjnej

2. Graficzna prezentacja struktury przychodów kosztów według rodzajów

3. Przygotowanie i konsolidacja arkuszy kosztowych dla poszczególnych MPK

4. Narzędzie do dynamicznego wyszukiwania przeterminowanych należności

5. Importowanie danych z użyciem narzędzia tekst jako kolumny

6. Arkusz do planowania kosztu amortyzacji

7. Automatyczny raport wynagrodzeń w podziale na komórki organizacyjne firmy

8. Okresowy raport czasu pracy

9. Koszt jednostkowy wytworzenia produktu

10. Szablon faktur – zaawansowane możliwości