Excel_z_elementami_VBA_w_firmie_exbiu2 by Jakub Jóźwiak

Excel z elementami VBA w firmie Autor: Sergiusz Flanczewski ISBN: 978-83-246-1008-2

Wykorzystaj potencja³ Excela, by Twoja firma odnios³a sukces! • Jak zaprz¹c dodatki Excela do tworzenia dokumentacji firmowej? • Jak importowaæ pliki z aplikacji zewnêtrznych? • Jak obs³ugiwaæ autofiltry i aktualizowaæ dane?

Excel sta³ siê obecnie niezast¹pionym programem biurowym. Niestety, nie wszyscy potrafi¹ wykorzystaæ drzemi¹cy w nim potencja³, bowiem program ten zwykle uwa¿any jest za trudny do przyswojenia. Praca z nim przynosi jednak odczuwalne korzyœci, a stosowanie jego ró¿norodnych narzêdzi i dodatków mo¿e sprawiæ, ¿e niepotrzebne stan¹ siê wszystkie wyspecjalizowane programy ksiêgowe. Dodatkowym atutem Excela jest mo¿liwoœæ skojarzenia formantów i formularzy z fragmentami programów napisanych w jêzyku VBA. „Excel w firmie” w przystêpny i jasny sposób pokazuje praktyczne rozwi¹zania w zakresie prowadzenia bogatej dokumentacji biurowej. Z tej ksi¹¿ki dowiesz siê, jak zbudowaæ skoroszyt, zaktualizowaæ poszczególne arkusze, wprowadziæ zmiany, stworzyæ gotowe dokumenty i zestawienia ksiêgowe, kadrowe i magazynowe. Zawarte w podrêczniku przyk³ady zosta³y opisane w taki sposób, aby mo¿na je by³o od razu wykorzystaæ w praktyce lub potraktowaæ jako wzory do tworzenia w³asnej dokumentacji.

Wydawnictwo Helion ul. Koœciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl

• Edytor jêzyka Visual Basic • Formularze, makra i formanty • Instalacja dodatków • Przeznaczenie i budowa skoroszytu • Importowanie danych z plików innych aplikacji • Pobieranie danych z internetu • Filtrowanie i sortowanie • Budowanie arkusza „Faktury VAT”

Excel – asystent niezast¹piony w ka¿dej firmie!

Spis tre!ci Wst p .............................................................................................. 7 Rozdzia" 1. Makra, formanty, VBA — czyli narz dzia do programowania Excela .... 9 Makropolecenia — pierwszy krok w programowaniu .................................................... 10 Rejestrowanie makropolecenia ................................................................................. 10 Przegl"danie makra za pomoc" edytora VBA .......................................................... 15 Formanty — informacje podstawowe ............................................................................. 18 Umieszczanie formantu w arkuszu ........................................................................... 19 Wy'wietlenie okna w(a'ciwo'ci formantu ............................................................... 22 Formanty dost)pne z paska narz)dzi Formularze — informacje podstawowe ............... 23 Edytor j)zyka Visual Basic ............................................................................................. 28 Obs(uga edytora VBA .............................................................................................. 29 Formularze ...................................................................................................................... 38 Dodawanie formantów do formularza ...................................................................... 40 W(a'ciwo'ci formularzy ........................................................................................... 40 Wy'wietlanie (aktywacja) formularza ...................................................................... 41

Rozdzia" 2. Instalacja dodatków — czyli "atwe ulepszanie Excela ...................... 43 Kopiowanie dodatków z p(yty CD .................................................................................. 44 Instalowanie dodatków — Excel XP/2003 ..................................................................... 45 Odinstalowanie dodatku ................................................................................................. 47 Instalowanie dodatków — Excel 2007 ........................................................................... 48 Dodatek Wielkanoc.xla — czyli wyznaczanie daty 5wi"t Wielkanocnych .................... 49 Dodatki Slox.xla oraz Slox_m.xla — czyli zamiana warto'ci liczbowej na zapis s(owny .................................................. 53 Uwagi dotycz"ce stosowania funkcji Slox() ............................................................. 54 Uwagi dotycz"ce stosowania funkcji Slox_m() ........................................................ 55 Konstrukcja funkcji Slox() oraz Slox_m() ...................................................................... 56 Zapis instrukcji funkcji Slox() .................................................................................. 56 Zapis instrukcji funkcji Slox_m() ............................................................................. 57 Dodatek Calend.xla — czyli (atwe wprowadzanie dat do arkusza ................................. 58 Obs(uga dodatku Calend .......................................................................................... 59 Uwagi dotycz"ce obs(ugi okien dialogowych dodatku Calend ................................. 63

Excel z elementami VBA w firmie

Rozdzia" 3. Odsetki ustawowe lub podatkowe — czyli wymierne skutki opiesza"o$ci w p"aceniu ............................ 67 Przeznaczenie i budowa skoroszytu ............................................................................... 67 Obs(uga skoroszytu w wersji Excel XP/2003/2007 ........................................................ 69 Arkusz Odsetki ustawowe .............................................................................................. 69 Obszar wprowadzania danych .................................................................................. 69 Obszar formu( ........................................................................................................... 72 Aktualizacja arkusza Odsetki ustawowe ......................................................................... 75 Arkusz Odsetki podatkowe ............................................................................................. 77

Rozdzia" 4. Informacje z innych aplikacji — czyli "atwe wprowadzanie danych .... 79 Otwieranie plików pochodz"cych z aplikacji typu dBase (*.dbf) ................................... 81 Otwieranie plików bazy danych Access (*.mdb) ............................................................ 82 Otwieranie plików tekstowych (*.txt) ............................................................................. 84 Procedura zamiany znaku kropki na znak przecinka ................................................ 88 Importowanie danych z plików innych aplikacji ............................................................ 89 Od'wie<anie danych ................................................................................................. 91 Importowanie plików z Internetu .................................................................................... 92 Dowody kupna i sprzeda<y dewiz — praktyczne zastosowanie danych pobieranych z Internetu ...................................... 95 Przeznaczenie i budowa skoroszytu ......................................................................... 95 Obs(uga arkusza ....................................................................................................... 97 Arkusz Tabela .......................................................................................................... 97 Arkusz Ustawienia ................................................................................................. 100 Arkusz ND ............................................................................................................. 104 Arkusz Baza ........................................................................................................... 105 Arkusz Dowód ....................................................................................................... 108 Arkusz Cennik — drukowanie ............................................................................... 110

Rozdzia" 5. Lista obecno$ci — czyli nasza praca w miesi%cu ........................... 113 Przeznaczenie i budowa skoroszytu ............................................................................. 113 Obs(uga skoroszytu ...................................................................................................... 115 Obs(uga skoroszytu w wersji Excel XP/2003 ......................................................... 115 Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 117 Arkusz Pracownicy ....................................................................................................... 120 Arkusz Lista ................................................................................................................. 121 Cz)'> nag(ówkowa listy ......................................................................................... 122 Cz)'> specyfikacji dni miesi"ca dla listy obecno'ci ............................................... 128 Obszar specyfikacji dni 'wi"tecznych .................................................................... 130 Formatowanie warunkowe — wersja pe(na ............................................................ 132 Formatowanie warunkowe — wersja oszcz)dna .................................................... 135 Uwagi do konstrukcji skoroszytu w wersji Excel 2007 ................................................ 138 Wstawienie formantów w aplikacji Excel 2007 ..................................................... 138 Formatowanie warunkowe Excel 2007 .................................................................. 139 Ustawienia wst)pne skoroszytu Lista obecno'ci .................................................... 144

Rozdzia" 6. Kartoteka obecno$ci — czyli nasza nieobecno$& w pracy .............. 147 Przeznaczenie i budowa skoroszytu ............................................................................. 147 Obs(uga arkusza Excel XP/2003 ............................................................................ 148 Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 150 Arkusz Zestawienie ...................................................................................................... 153 Arkusz Rok ................................................................................................................... 154 Formatowanie warunkowe komórek zawieraj"cych nazwy dni tygodnia ............... 157 Wstawienie formantu typu przycisk pokr)t(a w arkuszu Rok ................................ 160 Wyznaczenie dni 'wi"tecznych w kartotece ........................................................... 163

Spis tre$ci

5 Formatowanie warunkowe komórek zawieraj"cych oznaczenie dni 'wi"tecznych ....... 168 Wstawienie formantu typu pole kombi w arkuszu Rok .......................................... 171 Wstawienie formantu typu przycisk polecenia przeznaczonego do automatycznego wstawiania kodu nieobecno'ci w komórkach arkusza Rok ...... 174 Konstrukcja kodu do obs(ugi przycisku wstawiania kodu nieobecno'ci ................ 176 Obs(uga arkusza Rok .............................................................................................. 178 Ustawienia wst)pne skoroszytu Rok ...................................................................... 179

Rozdzia" 7. Filtrowanie — czyli wydajne szukanie ........................................... 181 Autofiltr — Excel XP/2003 .......................................................................................... 181 Wybór kolumn przeznaczonych do filtrowania ...................................................... 182 Obs(uga Autofiltru .................................................................................................. 184 Autofiltr — Excel 2007 ................................................................................................ 192 Filtrowanie tekstu ................................................................................................... 193 Filtrowanie liczb ..................................................................................................... 195 Filtrowanie dat lub godzin ...................................................................................... 197 Filtrowanie zaawansowane — Excel XP/2003 ............................................................. 200 Wiele warunków w jednej kolumnie ...................................................................... 201 Jeden warunek w dwóch lub wi)kszej liczbie kolumn ........................................... 202 Jeden z dwóch zestawów warunków dla dwóch lub wi)kszej liczby kolumn ........ 203 Opis struktury przyk(adowej tabeli ......................................................................... 204 Filtrowanie zaawansowane — Excel 2007 ................................................................... 211

Rozdzia" 8. Sortowanie — czyli oczekiwana zamiana miejsc ............................ 215 Sortowanie — Excel XP/2003 ...................................................................................... 216 Sortowanie za pomoc" przycisków paska narz)dzi Standardowy .......................... 218 Sortowanie za pomoc" polecenia Sortuj ................................................................. 219 Sortowanie z uwzgl)dnieniem wielko'ci liter ........................................................ 222 Sortowanie wielokolumnowe ................................................................................. 223 Sortowanie za pomoc" listy .................................................................................... 225 Sortowanie — Excel 2007 ............................................................................................ 227

Rozdzia" 9. Za$wiadczenie o zatrudnieniu i zarobkach — czyli co$, bez czego nic nie za"atwimy ...................................... 231 Przeznaczenie i budowa skoroszytu ............................................................................. 231 Obs(uga skoroszytu ...................................................................................................... 233 Obs(uga skoroszytu w wersji Excel XP/2003 ......................................................... 233 Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 234 Arkusz Katalogi ............................................................................................................ 238 Arkusz Dane ................................................................................................................. 239 Wstawienie formantu typu pole kombi s(u<"cego do aktualizacji komórek w kolumnie Stanowisko — Excel XP/2003 ......................................................... 240 Wstawienie formantu typu pole kombi s(u<"cego do aktualizacji komórek w kolumnie Rodzaj zatrudnienia .......................................................................... 247 Aktualizacja komórek za pomoc" formantów typu pole kombi ............................. 249 Wstawienie formantów w aplikacji Excel 2007 ..................................................... 250 Arkusz Za'wiadczenie .................................................................................................. 251 Wstawienie formantu typu pole listy ...................................................................... 254 Drukowanie za'wiadczenia .......................................................................................... 257

Excel z elementami VBA w firmie

Rozdzia" 10. Faktura VAT — czyli to, czego wymaga fiskus ............................... 259 Przeznaczenie i budowa skoroszytu ............................................................................. 259 Obs(uga skoroszytu ...................................................................................................... 261 Obs(uga skoroszytu w wersji Excel XP/2003 ............................................................... 261 Obs(uga skoroszytu w wersji Excel 2007 ..................................................................... 264 Arkusz Towary ............................................................................................................. 267 Konstrukcja makropolecenia do obs(ugi przycisku sortowania towarów ............... 268 Wstawienie formantu typu przycisk w arkuszu Towary oraz przypisanie do niego makropolecenia sortowania towarów ......................... 272 Arkusz Odbiorcy .......................................................................................................... 274 Makropolecenie oraz formant typu przycisk w arkuszu Odbiorcy ......................... 274 Arkusz Ustawienia ........................................................................................................ 275 Arkusz Faktura ............................................................................................................. 276 Tworzenie cz)'ci nag(ówkowej faktury ................................................................. 276 Tworzenie cz)'ci specyfikacji towarów ................................................................. 305 Tworzenie cz)'ci sumuj"cej faktury ....................................................................... 318 Inne operacje zwi"zane z budow" arkusza Faktura ................................................ 319 Ustawienia wst)pne skoroszytu Faktura ................................................................. 323

Skorowidz .................................................................................... 325

Rozdzia 3.

Odsetki ustawowe lub podatkowe — czyli wymierne skutki opiesza/o0ci w p/aceniu Przeznaczenie i budowa skoroszytu Skoroszyt sk!ada si" z dwóch arkuszy o nazwach: Odsetki ustawowe (rysunek 3.1) oraz Odsetki podatkowe (rysunek 3.2). Jak wskazuj# nazwy arkuszy, ich zadaniem jest obliczanie wielko$ci nale%nych odsetek (ustawowych lub podatkowych) przy zadanej stopie procentowej. Zasada dzia!ania obu arkuszy jest taka sama, a sposób obs!ugi stanowi najprostsz# realizacj" procesu naliczania odsetek; oznacza to, %e do budowy arkusza zosta!a u%yta minimalna (niezb"dna) liczba formu!, pozwalaj#ca na ich zaprojektowanie w sposób w pe!ni funkcjonalny. Ze wzgl"du na funkcjonalno$' poszczególnych komórek w arkuszu mo%emy wyró%ni' dwa zasadnicze obszary: 1. Obszar zapisu parametrów nalicze*, przeznaczony do wprowadzania: a) Kwoty b"d#cej podstaw# naliczenia odsetek. b) Daty pocz#tku zobowi#zania podatkowego. c) Daty ko*ca zobowi#zania podatkowego. d) Stopy procentowej odsetek, obowi#zuj#cej w danym okresie. e) Daty pocz#tku obowi#zywania okre$lonej stopy procentowej. 2. Obszar formu!, przeznaczony do wykonania nalicze* wed!ug okre$lonych

parametrów.

68 Rysunek 3.1. Wygl)d arkusza Odsetki ustawowe

Rysunek 3.2. Wygl)d arkusza Odsetki podatkowe

Excel z elementami VBA w firmie

Rozdzia" 3.

Odsetki ustawowe lub podatkowe

Oprócz dost"pnych w Excelu standardowych formu! do budowy arkusza nalicze* odsetek, do obs!ugi skoroszytu zastosowano formularz u%ytkownika za!#czony do niniejszej ksi#%ki jako dodatek Excela, Calendar.xla (opis instalacji dodatku znajduje si" w rozdziale 2.).

Obs/uga skoroszytu w wersji Excel XP/2003/2007 Opis obs!ugi skoroszytu b"dzie bardziej zrozumia!y, je$li zostanie on skopiowany z za!#czonej p!yty CD na dysk twardy, w dowolne miejsce, np. do katalogu Moje dokumenty. W tym celu musisz wykona' nast"puj#ce czynno$ci: 1. Uruchom program Excel. 2. W!ó% p!yt" CD do nap"du CD-ROM. 3. Wybierz polecenie Plik/Otwórz, a nast"pnie w oknie dialogowym Otwieranie

na li$cie rozwijalnej Szukaj w: ustaw $cie%k" dost"pu do nap"du CD-ROM. 4. Odszukaj katalog Przyk6ady\Rozdzial03, a w nim plik Odsetki, nast"pnie

otwórz go, klikaj#c dwukrotnie mysz# jego nazw" lub raz przycisk Otwórz. 5. Wybierz polecenie Plik/Zapisz jako, a nast"pnie w oknie dialogowym Zapisz

jako na li$cie rozwijalnej Zapisz w: ustaw $cie%k" dost"pu do nap"du C:\Moje dokumenty, po czym kliknij przycisk Zapisz. Wykonanie punktu 5. pozwoli na swobodn# aktualizacj" komórek wczytanego przyk!adu.

Arkusz Odsetki ustawowe Obszar wprowadzania danych Obszar wprowadzania danych przez u%ytkownika tworz# nast"puj#ce elementy arkusza: 1. Komórka C2 — umo%liwia wprowadzenie i przechowywanie kwoty stanowi#cej

podstaw" naliczenia odsetek (rysunek 3.3, oznaczenie 3). 2. Komórka C3 — umo%liwia wprowadzenie i przechowywanie daty

pocz#tkowej okresu naliczania odsetek (rysunek 3.3, oznaczenie 2). 3. Komórka C4 — umo%liwia wprowadzenie i przechowywanie daty ko*cowej

okresu naliczania odsetek (rysunek 3.3, oznaczenie 1). 4. Komórki z obszaru B9:B100 — umo%liwiaj# wprowadzenie i przechowywanie

stopy procentowej obowi#zuj#cej w danym przedziale czasowym (rysunek 3.3, oznaczenie 5).

Excel z elementami VBA w firmie

Rysunek 3.3. Wygl)d arkusza Odsetki — obszar wprowadzania danych

5. Komórki z obszaru C9:C100 — umo%liwiaj# wprowadzenie i przechowywanie

daty pocz#tku obowi#zywania okre$lonej stopy procentowej (rysunek 3.3, oznaczenie 6). W cz"$ci arkusza Odsetki ustawowe, nazwanej umownie obszarem wprowadzania danych, wyst"puje jeszcze jedna komórka istotna z u%ytkowego punktu widzenia. Ta komórka to oczywi$cie komórka podaj#ca warto$' naliczonych odsetek (rysunek 3.3, oznaczenie 4). Komórka ta ma formu!" o postaci: =JE!ELI($G$5<>$G$6;SUMA(J9:J100);0) + JE!ELI($G$5=$G$6;J7;0)

umo%liwiaj#c# wybór jednego z dwóch dzia!a*: podsumowanie zakresu komórek zawieraj#cych sumy cz#stkowe naliczonych odsetek — je%eli okres naliczenia zobowi#zania odsetkowego przypada na ró%ne przedzia!y czasowe obowi#zywania okre$lonej stopy procentowej; pobranie wyniku z jednej komórki (w naszym przypadku jest to komórka J7) — je%eli okres naliczenia zobowi#zania odsetkowego zawarty jest w jednym przedziale czasowym obowi#zywania okre$lonej stopy procentowej.

Obs"uga komórek przechowuj#cych okres nalicze$ za pomoc# dodatku Calendar Aby do komórki C3, przechowuj#cej dat" pocz#tku okresu naliczania odsetek, lub komórki C4, w której zawarta jest data ko*ca okresu nalicze*, wprowadzi' wybran# dat", nale%y wykona' jedn# z procedur: 1. Procedur" r"cznego wprowadzania dat do komórek przechowuj#cych okres

naliczania odsetek. 2. Procedur" wprowadzania dat do komórek przechowuj#cych okres naliczania

odsetek za pomoc# formularza u%ytkownika zawartego w dodatku Calendar.xla.

Rozdzia" 3.

Odsetki ustawowe lub podatkowe

O ile r"czne wprowadzanie dat do komórek C3 i C4 nie wymaga szczegó!owych wyja$nie* — oprócz uwagi, %e daty w komórkach nale%y wpisa' w formacie RRRR-MM-DD — o tyle drug# procedur" nale%y wykona' wed!ug poni%szych kroków: 1. Zainstalowa' (jednorazowo) dodatek Calendar.xla wed!ug opisu zawartego

w rozdziale 2. niniejszej ksi#%ki. 2. Klikn#' wybran# komórk" (rysunek 3.4). Rysunek 3.4. Procedura wstawiania dat w celu naliczenia odsetek

3. Aktywowa' formularz dodatku Calendar przez naci$ni"cie kombinacji

klawiszy Ctrl+Shift+C. 4. Dzia!anie z punktu 2. spowoduje wy$wietlenie formularza u%ytkownika

w postaci pokazanej na rysunku 3.4. 5. Manipuluj#c przyciskiem oznaczonym symbolem 1 na rysunku 3.5,

ustawi' numeryczn# prezentacj" roku nalicze* odsetek. 6. Klikn#' przycisk oznaczony symbolem 2, powoduj#c tym samym rozwini"cie

listy nazw miesi"cy (rysunek 3.5, oznaczenie 3). 7. Wybra' miesi#c (klikn#' pozycj" na li$cie). 8. Na klawiaturze numerycznej (oznaczenie 4 na rysunku 3.5) klikn#' przycisk

reprezentuj#cy okre$lony dzie* miesi#ca. 9. Dzia!anie z punktu 8. spowoduje wy$wietlenie na pasku formularza kalendarza

daty okre$lonej przez u%ytkownika (rysunek 3.6). 10. Klikn#' przycisk oznaczony symbolem 5 na rysunku 3.5, co spowoduje

wprowadzenie do aktywnej komórki arkusza daty widocznej na pasku formularza Calendar. 11. Klikni"cie jednego z przycisków oznaczonych symbolem 6 na rysunku 3.5

spowoduje ustawienie jako aktywnej nast"pnej komórki w kolumnie.

Excel z elementami VBA w firmie

Rysunek 3.5. Elementy sk6adowe formantu typu pole daty

Rysunek 3.6. Wygl)d paska formularza kalendarza po klikniHciu przycisku reprezentuj)cego dzieI miesi)ca

Obszar formu" W sk!ad obszaru nazwanego umownie obszarem formu+ (obszar odpowiedzialny za w!a$ciwe naliczenie odsetek ustawowych lub podatkowych) wchodz# elementy oznaczone na rysunku 3.7 symbolami od 1 do 8. Rysunek 3.7. Wygl)d obszaru formu6

Rozdzia" 3.

Odsetki ustawowe lub podatkowe

Poni%ej zosta! przedstawiony opis poszczególnych elementów opisywanego obszaru, zgodnie z oznaczeniami na rysunku 3.7. 1. Komórki kolumny D o etykiecie DO (od wiersza 9.) zawieraj# dat" ko*cow#

okresu obowi#zywania okre$lonej stopy oprocentowania. Data ta jest wyliczana za pomoc# formu!y. Na przyk!ad komórka C9 ma formu!" o postaci: =C10-1. Formu!y o tej postaci s# wpisane od pierwszej do przedostatniej u%ytej komórki w kolumnie C. Ostatnia u%yta komórka ma formu!" o postaci: =DZI3(). 2. Komórki kolumny E o etykiecie DN zawieraj# liczb" dni przypadaj#cych

na okres obowi#zywania okre$lonej stopy procentowej odsetek. Na przyk!ad komórka E9 ma formu!" o postaci: =D9-C9+1. Formu!a ta jest wpisana do wszystkich u%ytych komórek w kolumnie E. 3. Komórki kolumny F o etykiecie N1 (od wiersza 9.) zawieraj# liczb" dni

b"d#cych ró%nic# pomi"dzy dat# ko*cow# obowi#zywania okre$lonej stopy oprocentowania odsetek a pocz#tkow# dat# okresu naliczania odsetek. Na przyk!ad komórka F9 ma formu!" o postaci: =(D9-$C$3)+1. Formu!y te s# wpisane do wszystkich u%ytych komórek w kolumnie F. Taka konstrukcja pozwala na wyznaczenie pierwszego przedzia u czasowego naliczania odsetek, a dok adniej: liczby zawartych w nim dni. Wyznacza go komórka, w której zostanie po raz pierwszy wy;wietlona warto;< dodatnia. 4. Komórki kolumny G o etykiecie N2 (od wiersza 9.) zawieraj# liczb" dni

b"d#cych ró%nic# pomi"dzy ko*cow# dat# okresu nalicze* odsetek a dat# pocz#tkow# obowi#zywania okre$lonej stopy oprocentowania odsetek. Na przyk!ad komórka G9 ma formu!" o postaci: =($C$4-C9)+1. Formu!a ta jest wpisana do wszystkich u%ytych komórek w kolumnie G. Taka konstrukcja pozwala na wyznaczenie ostatniego przedzia u czasowego naliczania odsetek, a dok adniej: liczby zawartych w nim dni. Wyznacza go komórka, w której zostanie po raz ostatni wy;wietlona warto;< dodatnia. 5. Komórki kolumny H o etykiecie WSK (od wiersza 9.) zawieraj# numer wiersza,

w którym odpowiednie komórki kolumn F i G s# dodatnie. Na przyk!ad komórka H9 ma formu!" o postaci: =JE!ELI(ORAZ(F9>0;G9>0);WIERSZ();""). Do budowy warunku sprawdzaj#cego u%yto funkcji ORAZ(), która zwraca warto$' logiczn# PRAWDA, je%eli wszystkie u%yte w niej argumenty maj# warto$' PRAWDA. Formu!a ta jest wpisana do wszystkich u%ytych komórek w kolumnie H. Taka konstrukcja pozwala na wyznaczenie wszystkich obowi=zuj=cych przedzia ów czasowych naliczania odsetek, a dok adniej: wyznaczenie wierszy, w których zosta y one zapisane.

Excel z elementami VBA w firmie 6. Komórki kolumny I o etykiecie DNI (od wiersza 9.) zawieraj# liczb" dni

z danego przedzia!u czasowego obowi#zywania okre$lonej stopy oprocentowania odsetek uwzgl"dnion# (przyj"t#) do naliczenia warto$ci odsetek ustawowych. Na przyk!ad komórka I9 ma formu!" o postaci: =JE!ELI(H9=$G$5;F9;0) + JE!ELI(ORAZ(H9>$G$5;H9<$G$6);E9;0) + JE!ELI(H9=$G$6;G9;0). Formu!a ta jest wpisana do wszystkich u%ytych komórek w kolumnie I i zapewnia pobranie: Liczby dni wypadaj#cych w pierwszym przedziale czasowym obowi#zywania okre$lonej stopy oprocentowania odsetek, okre$lonym przez dat" pocz#tku naliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórki kolumny F (kolumny o etykiecie N1). Liczby dni ca!ego przedzia!u czasowego obowi#zywania okre$lonej stopy oprocentowania odsetek, w przypadku gdy jest on zawarty w wybranym okresie naliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórki kolumny E (kolumny o etykiecie DN). Liczby dni wypadaj#cych w ostatnim przedziale czasowym obowi#zywania okre$lonej stopy oprocentowania odsetek, okre$lonym przez dat" ko*ca naliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórki kolumny G (kolumny o etykiecie N2). 7. Komórki kolumny J o etykiecie WART (od wiersza 9.) zawieraj# warto$ci

odsetek ustawowych wyliczonych w okre$lonym przedziale czasowym obowi#zywania okre$lonej stopy procentowej odsetek ustawowych. Na przyk!ad komórka J9 ma formu!" o postaci: =((($C$2*B9)/100)/365)*I9. Formu!a ta jest wpisana do wszystkich u%ytych komórek w kolumnie I. 8. Obszar komórek przeznaczony do alternatywnych nalicze* odsetek w przypadku,

gdy ca6y wybrany okres nalicze* odsetek mie$ci si" w jednym przedziale czasowym obowi#zywania okre$lonej stopy procentowej. Tak# sytuacj" przedstawia rysunek 3.8, na którym pokazano okres nalicze* odsetek przypadaj#cy od dnia 24.09.2003 do dnia 15.08.2003, zawieraj#cy si" ca!kowicie w przedziale czasowym od dnia 01.02.2003 do dnia 24.09.3002, w którym obowi#zuje stopa procentowa równa 13,00%. Przy tak okre$lonych datach nalicze* odsetek komórki kolumny H o etykiecie WSK zawieraj# tylko jeden wiersz, w którym odpowiednie komórki kolumn F i G s# dodatnie — jest to wiersz o numerze 22. W tym przypadku pozosta!e dane w komórkach 22. wiersza — !#czna liczba dni do nalicze* odsetek oraz wyliczona warto$' odsetek — s, b+-dne. Do obs!ugi takiej sytuacji arkusz Odsetki ustawowe ma dodatkowe komórki, zaprojektowane wed!ug specyfikacji przedstawionej w tabeli 3.1. Tabela 3.1. Specyfikacja alternatywnego obszaru naliczeI odsetek ustawowych Oznaczenie na rysunku

Komórka

Formu"a

=MAX(H9:H100)

=MIN(H9:H100)

=($C$4-$C$3)+1

=ADR.PO3R("$B"&G5)

=((($C$2*I7)/100)/365)*H7

Rozdzia" 3.

Odsetki ustawowe lub podatkowe

Rysunek 3.8. Procedura obliczeI w obszarze „alternatywnym”

Obliczenia w obszarze alternatywnym s= wykonywane przy ka?dym doborze parametrów nalicze@ odsetek ustawowych. Jednak wynik nalicze@ uzyskany w tym obszarze jest prawdziwy i zostaje przyjBty tylko w przypadku, gdy zawarto;< komórek G5 i G6 jest równa. Taki wybór — wyniku ko@cowego — nalicze@ odsetek ustawowych zapewnia formu a umieszczona w komórce B6, maj=ca posta<: =JE!ELI($G$5<>$G$6;SUMA(J9:J100);0) + JE!ELI($G$5=$G$6;J7;0)

Aktualizacja arkusza Odsetki ustawowe Aktualizacj" arkusza Odsetki ustawowe b"dziemy musieli przeprowadzi' za ka%dym razem, gdy „organa decydenckie” zmieni# stop" procentow# u%ywan# przy naliczaniu odsetek ustawowych. W naszym arkuszu zmiana ta b"dzie polega!a na dopisaniu kolejnego wiersza i uzupe!nieniu go odpowiednimi informacjami. O ile modyfikacja komórek zawieraj#cych wysoko$' nowej stopy procentowej oraz dat" pocz#tku jej obowi#zywania jest stosunkowo prosta i polega wy!#cznie na wpisaniu okre$lonych warto$ci w komórkach kolumn B i C, o tyle modyfikacja obszaru formu! wymaga bardziej skomplikowanych czynno$ci. Aktualizacj" t" mo%emy przeprowadzi' od pocz#tku do ko*ca „r"cznie”.

Excel z elementami VBA w firmie

W omawianym arkuszu ko*cow# dat" obowi#zywania stopy procentowej równej 11,50% okre$la data „uzyskana” za pomoc# funkcji DZI3(), co sprawia, %e dopóki nie zostanie okre$lona przez ustawodawc" nowa stopa odsetkowa, wyliczenia odsetek mo%na prowadzi' zaraz po otwarciu arkusza, bez konieczno$ci wpisywania aktualnej daty do komórki D. W chwili kiedy zostanie wyznaczona przez ustawodawc" nowa stopa odsetkowa, nale%y zaktualizowa' arkusz. Na przyk!ad dla poni%szych danych: Aktualizacj" arkusza przeprowadzamy w dniu 06.03.2007 r. Nowa stopa procentowa w wysoko$ci 12,00% obowi#zuje od dnia 15.02.2007 r. Procedur" aktualizacji nale%y przeprowadzi' wed!ug nast"puj#cych kroków: 1. W komórkach kolumn B i C wpisa', odpowiednio, now# warto$' stopy

odsetkowej oraz dat" pocz#tku jej obowi#zywania, jak pokazano to na rysunku 3.9. Rysunek 3.9. RozpoczHcie procedury aktualizacji tabeli odsetek ustawowych

2. Zaznaczy' komórki w zakresie D:J, znajduj#ce si" w wierszu po!o%onym powy%ej wiersza z komórk# maj#c# zapis formu!y DZI3(), a nast"pnie przekopiowa'

zawarte w nim formu!y do dwóch nast"pnych wierszy (rysunek 3.10). Rysunek 3.10. Procedura kopiowania formu6

3. Bezpo$rednio po skopiowaniu formu! wiersze otrzymaj# posta' pokazan#

na rysunku 3.11. 4. Klikn#' komórk" D, w której zosta!y wy$wietlone znaki ######### (data ujemna), po czym wpisa' w niej formu!" =DZI3() (rysunek 3.12). 5. W wyniku dzia!ania wykonanego w punkcie 4. komórka przybierze wygl#d

jak na rysunku 3.13, co zako*czy proces aktualizacji — dopisania nowej pozycji „odsetkowej”.

Rozdzia" 3.

Odsetki ustawowe lub podatkowe

Rysunek 3.11. Wygl)d komórek bezpoXrednio po wykonaniu procedury kopiowania formu6

Rysunek 3.12. Aktualizacja komórki zawieraj)cej funkcjH DZIY()

Rysunek 3.13. Wygl)d komórki po wprowadzeniu formu6y DZIY()

Arkusz Odsetki podatkowe Arkusz Odsetki podatkowe ma identyczn# budow" jak arkusz Odsetki ustawowe. Jedyn# ró%nic# jest tabela stóp procentowych, a dok!adniej kolumny B i C, w których przechowywane s# stopy procentowe odsetek podatkowych oraz daty ich obowi#zywania (specyfikacja w tabeli 3.2). Aby otrzyma' arkusz do nalicze* odsetek podatkowych, wystarczy skopiowa' arkusz Odsetki ustawowe do nowego arkusza, po czym w nowym arkuszu zast#pi' zapisy stóp procentowych oraz dat pocz#tku obowi#zywania (zapisane s# w tabeli drukiem pogrubionym) danymi z poni%szej tabeli, pami"taj#c przy tym, %e kolejno$' wpisów w arkuszu musi by' odwrotna do zapisu informacji podanych w tabeli. W komórce ostatniego zapisanego wiersza, w kolumnie DO, nale%y wpisa' formu!" o postaci: =DziK().

Excel z elementami VBA w firmie

Tabela 3.2. Stopy procentowe odsetek za zw6okH od zaleg6oXci podatkowych Stopa

Okres obowi#zywania

11,5%

od 26 kwietnia 2007

11%

od 1 marca 2006 do 25 kwietnia 2007

11,5%

od 1 lutego 2006 do 28 lutego 2006

12%

od 1 wrze&nia 2005 do 31 stycznia 2006

12,5%

od 28 lipca 2005 do 31 sierpnia 2005

13%

od 30 czerwca 2005 do 27 lipca 2005

14%

od 28 kwietnia 2005 do 29 czerwca 2005

15%

od 31 marca 2005 do 27 kwietnia 2005

16%

od 26 sierpnia 2004 do 30 marca 2005

15%

od 29 lipca 2004 do 25 sierpnia 2004

14,5%

od 1 lipca 2004 do 28 lipca 2004

13,5%

od 26 czerwca 2003 do 30 czerwca 2004

14%

od 29 maja 2003 do 25 czerwca 2003

14,5%

od 25 kwietnia 2003 do 28 maja 2003

15,5%

od 27 marca 2003 do 24 kwietnia 2003

16%

od 27 lutego 2003 do 26 marca 2003

17%

od 30 stycznia 2003 do 26 lutego 2003

17,5%

od 28 listopada 2002 do 29 stycznia 2003

18%

od 24 pa.dziernika 2002 do 27 listopada 2002

20%

od 26 wrze&nia 2002 do 23 paFdziernika 2002

21%

od 29 sierpnia 2002 do 25 wrze$nia 2002

23%

od 27 czerwca 2002 do 28 sierpnia 2002

24%

od 30 maja 2002 do 26 czerwca 2002

25%

od 26 kwietnia 2002 do 29 maja 2002