Wydawnictwo Helionul. Ko�ciuszki 1c44-100 Gliwicetel. 032 230 98 63e-mail: helion@helion.pl

Excel z elementamiVBA w firmieAutor: Sergiusz FlanczewskiISBN: 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¹zaniaw 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.

� 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 .... 9Makropolecenia — pierwszy krok w programowaniu .................................................... 10

Rejestrowanie makropolecenia ................................................................................. 10Przegl"danie makra za pomoc" edytora VBA .......................................................... 15

Formanty — informacje podstawowe ............................................................................. 18Umieszczanie formantu w arkuszu ........................................................................... 19Wy'wietlenie okna w(a'ciwo'ci formantu ............................................................... 22

Formanty dost)pne z paska narz)dzi Formularze — informacje podstawowe ............... 23Edytor j)zyka Visual Basic ............................................................................................. 28

Obs(uga edytora VBA .............................................................................................. 29Formularze ...................................................................................................................... 38

Dodawanie formantów do formularza ...................................................................... 40W(a'ciwo'ci formularzy ........................................................................................... 40Wy'wietlanie (aktywacja) formularza ...................................................................... 41

Rozdzia" 2. Instalacja dodatków — czyli "atwe ulepszanie Excela ...................... 43Kopiowanie dodatków z p(yty CD .................................................................................. 44Instalowanie dodatków — Excel XP/2003 ..................................................................... 45Odinstalowanie dodatku ................................................................................................. 47Instalowanie dodatków — Excel 2007 ........................................................................... 48Dodatek Wielkanoc.xla — czyli wyznaczanie daty 5wi"t Wielkanocnych .................... 49Dodatki Slox.xla oraz Slox_m.xla

— czyli zamiana warto'ci liczbowej na zapis s(owny .................................................. 53Uwagi dotycz"ce stosowania funkcji Slox() ............................................................. 54Uwagi dotycz"ce stosowania funkcji Slox_m() ........................................................ 55

Konstrukcja funkcji Slox() oraz Slox_m() ...................................................................... 56Zapis instrukcji funkcji Slox() .................................................................................. 56Zapis instrukcji funkcji Slox_m() ............................................................................. 57

Dodatek Calend.xla — czyli (atwe wprowadzanie dat do arkusza ................................. 58Obs(uga dodatku Calend .......................................................................................... 59Uwagi dotycz"ce obs(ugi okien dialogowych dodatku Calend ................................. 63

4 Excel z elementami VBA w firmie

Rozdzia" 3. Odsetki ustawowe lub podatkowe— czyli wymierne skutki opiesza"o$ci w p"aceniu ............................ 67Przeznaczenie i budowa skoroszytu ............................................................................... 67Obs(uga skoroszytu w wersji Excel XP/2003/2007 ........................................................ 69Arkusz Odsetki ustawowe .............................................................................................. 69

Obszar wprowadzania danych .................................................................................. 69Obszar formu( ........................................................................................................... 72

Aktualizacja arkusza Odsetki ustawowe ......................................................................... 75Arkusz Odsetki podatkowe ............................................................................................. 77

Rozdzia" 4. Informacje z innych aplikacji — czyli "atwe wprowadzanie danych .... 79Otwieranie plików pochodz"cych z aplikacji typu dBase (*.dbf) ................................... 81Otwieranie plików bazy danych Access (*.mdb) ............................................................ 82Otwieranie plików tekstowych (*.txt) ............................................................................. 84

Procedura zamiany znaku kropki na znak przecinka ................................................ 88Importowanie danych z plików innych aplikacji ............................................................ 89

Od'wie<anie danych ................................................................................................. 91Importowanie plików z Internetu .................................................................................... 92Dowody kupna i sprzeda<y dewiz

— praktyczne zastosowanie danych pobieranych z Internetu ...................................... 95Przeznaczenie i budowa skoroszytu ......................................................................... 95Obs(uga arkusza ....................................................................................................... 97Arkusz Tabela .......................................................................................................... 97Arkusz Ustawienia ................................................................................................. 100Arkusz ND ............................................................................................................. 104Arkusz Baza ........................................................................................................... 105Arkusz Dowód ....................................................................................................... 108Arkusz Cennik — drukowanie ............................................................................... 110

Rozdzia" 5. Lista obecno$ci — czyli nasza praca w miesi%cu ........................... 113Przeznaczenie i budowa skoroszytu ............................................................................. 113Obs(uga skoroszytu ...................................................................................................... 115

Obs(uga skoroszytu w wersji Excel XP/2003 ......................................................... 115Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 117

Arkusz Pracownicy ....................................................................................................... 120Arkusz Lista ................................................................................................................. 121

Cz)'> nag(ówkowa listy ......................................................................................... 122Cz)'> specyfikacji dni miesi"ca dla listy obecno'ci ............................................... 128Obszar specyfikacji dni 'wi"tecznych .................................................................... 130Formatowanie warunkowe — wersja pe(na ............................................................ 132Formatowanie warunkowe — wersja oszcz)dna .................................................... 135

Uwagi do konstrukcji skoroszytu w wersji Excel 2007 ................................................ 138Wstawienie formantów w aplikacji Excel 2007 ..................................................... 138Formatowanie warunkowe Excel 2007 .................................................................. 139Ustawienia wst)pne skoroszytu Lista obecno'ci .................................................... 144

Rozdzia" 6. Kartoteka obecno$ci — czyli nasza nieobecno$& w pracy .............. 147Przeznaczenie i budowa skoroszytu ............................................................................. 147

Obs(uga arkusza Excel XP/2003 ............................................................................ 148Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 150

Arkusz Zestawienie ...................................................................................................... 153Arkusz Rok ................................................................................................................... 154

Formatowanie warunkowe komórek zawieraj"cych nazwy dni tygodnia ............... 157Wstawienie formantu typu przycisk pokr)t(a w arkuszu Rok ................................ 160Wyznaczenie dni 'wi"tecznych w kartotece ........................................................... 163

Spis tre$ci 5

Formatowanie warunkowe komórek zawieraj"cych oznaczenie dni 'wi"tecznych ....... 168Wstawienie formantu typu pole kombi w arkuszu Rok .......................................... 171Wstawienie formantu typu przycisk polecenia przeznaczonego

do automatycznego wstawiania kodu nieobecno'ci w komórkach arkusza Rok ...... 174Konstrukcja kodu do obs(ugi przycisku wstawiania kodu nieobecno'ci ................ 176Obs(uga arkusza Rok .............................................................................................. 178Ustawienia wst)pne skoroszytu Rok ...................................................................... 179

Rozdzia" 7. Filtrowanie — czyli wydajne szukanie ........................................... 181Autofiltr — Excel XP/2003 .......................................................................................... 181

Wybór kolumn przeznaczonych do filtrowania ...................................................... 182Obs(uga Autofiltru .................................................................................................. 184

Autofiltr — Excel 2007 ................................................................................................ 192Filtrowanie tekstu ................................................................................................... 193Filtrowanie liczb ..................................................................................................... 195Filtrowanie dat lub godzin ...................................................................................... 197

Filtrowanie zaawansowane — Excel XP/2003 ............................................................. 200Wiele warunków w jednej kolumnie ...................................................................... 201Jeden warunek w dwóch lub wi)kszej liczbie kolumn ........................................... 202Jeden z dwóch zestawów warunków dla dwóch lub wi)kszej liczby kolumn ........ 203Opis struktury przyk(adowej tabeli ......................................................................... 204

Filtrowanie zaawansowane — Excel 2007 ................................................................... 211

Rozdzia" 8. Sortowanie — czyli oczekiwana zamiana miejsc ............................ 215Sortowanie — Excel XP/2003 ...................................................................................... 216

Sortowanie za pomoc" przycisków paska narz)dzi Standardowy .......................... 218Sortowanie za pomoc" polecenia Sortuj ................................................................. 219Sortowanie z uwzgl)dnieniem wielko'ci liter ........................................................ 222Sortowanie wielokolumnowe ................................................................................. 223Sortowanie za pomoc" listy .................................................................................... 225

Sortowanie — Excel 2007 ............................................................................................ 227

Rozdzia" 9. Za$wiadczenie o zatrudnieniu i zarobkach— czyli co$, bez czego nic nie za"atwimy ...................................... 231Przeznaczenie i budowa skoroszytu ............................................................................. 231Obs(uga skoroszytu ...................................................................................................... 233

Obs(uga skoroszytu w wersji Excel XP/2003 ......................................................... 233Obs(uga skoroszytu w wersji Excel 2007 ............................................................... 234

Arkusz Katalogi ............................................................................................................ 238Arkusz Dane ................................................................................................................. 239

Wstawienie formantu typu pole kombi s(u<"cego do aktualizacji komórekw kolumnie Stanowisko — Excel XP/2003 ......................................................... 240

Wstawienie formantu typu pole kombi s(u<"cego do aktualizacji komórekw kolumnie Rodzaj zatrudnienia .......................................................................... 247

Aktualizacja komórek za pomoc" formantów typu pole kombi ............................. 249Wstawienie formantów w aplikacji Excel 2007 ..................................................... 250

Arkusz Za'wiadczenie .................................................................................................. 251Wstawienie formantu typu pole listy ...................................................................... 254

Drukowanie za'wiadczenia .......................................................................................... 257

6 Excel z elementami VBA w firmie

Rozdzia" 10. Faktura VAT — czyli to, czego wymaga fiskus ............................... 259Przeznaczenie i budowa skoroszytu ............................................................................. 259Obs(uga skoroszytu ...................................................................................................... 261Obs(uga skoroszytu w wersji Excel XP/2003 ............................................................... 261Obs(uga skoroszytu w wersji Excel 2007 ..................................................................... 264Arkusz Towary ............................................................................................................. 267

Konstrukcja makropolecenia do obs(ugi przycisku sortowania towarów ............... 268Wstawienie formantu typu przycisk w arkuszu Towary

oraz przypisanie do niego makropolecenia sortowania towarów ......................... 272Arkusz Odbiorcy .......................................................................................................... 274

Makropolecenie oraz formant typu przycisk w arkuszu Odbiorcy ......................... 274Arkusz Ustawienia ........................................................................................................ 275Arkusz Faktura ............................................................................................................. 276

Tworzenie cz)'ci nag(ówkowej faktury ................................................................. 276Tworzenie cz)'ci specyfikacji towarów ................................................................. 305Tworzenie cz)'ci sumuj"cej faktury ....................................................................... 318Inne operacje zwi"zane z budow" arkusza Faktura ................................................ 319Ustawienia wst)pne skoroszytu Faktura ................................................................. 323

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

Rozdzia 3.

Odsetki ustawowelub podatkowe— czyli wymierne skutkiopiesza/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 jestobliczanie wielko$ci nale%nych odsetek (ustawowych lub podatkowych) przy zadanejstopie procentowej. Zasada dzia!ania obu arkuszy jest taka sama, a sposób obs!ugi sta-nowi najprostsz# realizacj" procesu naliczania odsetek; oznacza to, %e do budowy arku-sza zosta!a u%yta minimalna (niezb"dna) liczba formu!, pozwalaj#ca na ich zaprojek-towanie w sposób w pe!ni funkcjonalny. Ze wzgl"du na funkcjonalno$' poszczególnychkomó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$lonychparametrów.

68 Excel z elementami VBA w firmie

Rysunek 3.1.Wygl)d arkuszaOdsetki ustawowe

Rysunek 3.2.Wygl)d arkuszaOdsetki podatkowe

Rozdzia" 3. Odsetki ustawowe lub podatkowe 69

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

Obs/uga skoroszytuw 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 Otwieraniena 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"pnieotwó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 Zapiszjako na li$cie rozwijalnej Zapisz w: ustaw $cie%k" dost"pu do nap"du C:\Mojedokumenty, 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#cejpodstaw" naliczenia odsetek (rysunek 3.3, oznaczenie 3).

2. Komórka C3 — umo%liwia wprowadzenie i przechowywanie datypocz#tkowej okresu naliczania odsetek (rysunek 3.3, oznaczenie 2).

3. Komórka C4 — umo%liwia wprowadzenie i przechowywanie daty ko*cowejokresu naliczania odsetek (rysunek 3.3, oznaczenie 1).

4. Komórki z obszaru B9:B100 — umo%liwiaj# wprowadzenie i przechowywaniestopy procentowej obowi#zuj#cej w danym przedziale czasowym (rysunek 3.3,oznaczenie 5).

70 Excel z elementami VBA w firmie

Rysunek 3.3.Wygl)d arkuszaOdsetki — obszarwprowadzania danych

5. Komórki z obszaru C9:C100 — umo%liwiaj# wprowadzenie i przechowywaniedaty pocz#tku obowi#zywania okre$lonej stopy procentowej (rysunek 3.3,oznaczenie 6).

W cz"$ci arkusza Odsetki ustawowe, nazwanej umownie obszarem wprowadzania da-nych, wyst"puje jeszcze jedna komórka istotna z u%ytkowego punktu widzenia. Takomó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 naliczonychodsetek — je%eli okres naliczenia zobowi#zania odsetkowego przypada na ró%neprzedzia!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 jednymprzedziale 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 ko-mó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 okresnaliczania odsetek.

2. Procedur" wprowadzania dat do komórek przechowuj#cych okres naliczaniaodsetek za pomoc# formularza u%ytkownika zawartego w dodatku Calendar.xla.

Rozdzia" 3. Odsetki ustawowe lub podatkowe 71

O ile r"czne wprowadzanie dat do komórek C3 i C4 nie wymaga szczegó!owych wy-ja$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 zawartegow rozdziale 2. niniejszej ksi#%ki.

2. Klikn#' wybran# komórk" (rysunek 3.4).

Rysunek 3.4.Procedurawstawiania datw celu naliczeniaodsetek

3. Aktywowa' formularz dodatku Calendar przez naci$ni"cie kombinacjiklawiszy Ctrl+Shift+C.

4. Dzia!anie z punktu 2. spowoduje wy$wietlenie formularza u%ytkownikaw 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"cielisty 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#' przyciskreprezentuj#cy okre$lony dzie* miesi#ca.

9. Dzia!anie z punktu 8. spowoduje wy$wietlenie na pasku formularza kalendarzadaty okre$lonej przez u%ytkownika (rysunek 3.6).

10. Klikn#' przycisk oznaczony symbolem 5 na rysunku 3.5, co spowodujewprowadzenie do aktywnej komórki arkusza daty widocznej na paskuformularza Calendar.

11. Klikni"cie jednego z przycisków oznaczonych symbolem 6 na rysunku 3.5spowoduje ustawienie jako aktywnej nast"pnej komórki w kolumnie.

72 Excel z elementami VBA w firmie

Rysunek 3.5.Elementy sk6adoweformantu typupole daty

Rysunek 3.6.Wygl)d paskaformularzakalendarzapo klikniHciuprzyciskureprezentuj)cegodzieI miesi)ca

Obszar formu"

W sk!ad obszaru nazwanego umownie obszarem formu+ (obszar odpowiedzialny zaw!a$ciwe naliczenie odsetek ustawowych lub podatkowych) wchodz# elementy ozna-czone na rysunku 3.7 symbolami od 1 do 8.

Rysunek 3.7.Wygl)d obszaruformu6

Rozdzia" 3. Odsetki ustawowe lub podatkowe 73

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 wyliczanaza 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órkiw kolumnie C. Ostatnia u%yta komórka ma formu!" o postaci: =DZI3().

2. Komórki kolumny E o etykiecie DN zawieraj# liczb" dni przypadaj#cychna okres obowi#zywania okre$lonej stopy procentowej odsetek. Na przyk!adkomórka E9 ma formu!" o postaci: =D9-C9+1. Formu!a ta jest wpisanado wszystkich u%ytych komórek w kolumnie E.

3. Komórki kolumny F o etykiecie N1 (od wiersza 9.) zawieraj# liczb" dnib"d#cych ró%nic# pomi"dzy dat# ko*cow# obowi#zywania okre$lonejstopy 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 nalicza-nia 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" dnib"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 tajest wpisana do wszystkich u%ytych komórek w kolumnie G.

Taka konstrukcja pozwala na wyznaczenie ostatniego przedzia u czasowego naliczaniaodsetek, a dok adniej: liczby zawartych w nim dni. Wyznacza go komórka, w której zo-stanie 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!adkomó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 zwracawarto$' logiczn# PRAWDA, je%eli wszystkie u%yte w niej argumenty maj#warto$' PRAWDA. Formu!a ta jest wpisana do wszystkich u%ytych komórekw kolumnie H.

Taka konstrukcja pozwala na wyznaczenie wszystkich obowi=zuj=cych przedzia ówczasowych naliczania odsetek, a dok adniej: wyznaczenie wierszy, w których zosta y onezapisane.

74 Excel z elementami VBA w firmie

6. Komórki kolumny I o etykiecie DNI (od wiersza 9.) zawieraj# liczb" dniz danego przedzia!u czasowego obowi#zywania okre$lonej stopy oprocentowaniaodsetek 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 tajest wpisana do wszystkich u%ytych komórek w kolumnie I i zapewnia pobranie:

Liczby dni wypadaj#cych w pierwszym przedziale czasowym obowi#zywaniaokre$lonej stopy oprocentowania odsetek, okre$lonym przez dat" pocz#tkunaliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórkikolumny F (kolumny o etykiecie N1).

Liczby dni ca!ego przedzia!u czasowego obowi#zywania okre$lonej stopyoprocentowania odsetek, w przypadku gdy jest on zawarty w wybranymokresie naliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórkikolumny E (kolumny o etykiecie DN).

Liczby dni wypadaj#cych w ostatnim przedziale czasowym obowi#zywaniaokre$lonej stopy oprocentowania odsetek, okre$lonym przez dat" ko*canaliczania odsetek. Pobranie liczby dni nast"puje wtedy z komórkikolumny G (kolumny o etykiecie N2).

7. Komórki kolumny J o etykiecie WART (od wiersza 9.) zawieraj# warto$ciodsetek ustawowych wyliczonych w okre$lonym przedziale czasowymobowi#zywania okre$lonej stopy procentowej odsetek ustawowych. Na przyk!adkomórka J9 ma formu!" o postaci: =((($C$2*B9)/100)/365)*I9. Formu!a tajest 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 przedzialeczasowym obowi#zywania okre$lonej stopy procentowej. Tak# sytuacj"przedstawia rysunek 3.8, na którym pokazano okres nalicze* odsetekprzypadaj#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$lonychdatach nalicze* odsetek komórki kolumny H o etykiecie WSK zawieraj# tylkojeden wiersz, w którym odpowiednie komórki kolumn F i G s# dodatnie — jestto 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 dodatkowekomórki, zaprojektowane wed!ug specyfikacji przedstawionej w tabeli 3.1.

Tabela 3.1. Specyfikacja alternatywnego obszaru naliczeI odsetek ustawowych

Oznaczeniena rysunku

Komórka Formu"a

1 G6 =MAX(H9:H100)

2 G5 =MIN(H9:H100)

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

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

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

Rozdzia" 3. Odsetki ustawowe lub podatkowe 75

Rysunek 3.8.Procedura obliczeIw obszarze„alternatywnym”

Obliczenia w obszarze alternatywnym s= wykonywane przy ka?dym doborze parame-trów nalicze@ odsetek ustawowych. Jednak wynik nalicze@ uzyskany w tym obszarzejest prawdziwy i zostaje przyjBty tylko w przypadku, gdy zawarto;< komórek G5 i G6jest równa. Taki wybór — wyniku ko@cowego — nalicze@ odsetek ustawowych za-pewnia 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 arkuszaOdsetki ustawowe

Aktualizacj" arkusza Odsetki ustawowe b"dziemy musieli przeprowadzi' za ka%dymrazem, gdy „organa decydenckie” zmieni# stop" procentow# u%ywan# przy naliczaniuodsetek ustawowych. W naszym arkuszu zmiana ta b"dzie polega!a na dopisaniu kolej-nego 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$ciw komórkach kolumn B i C, o tyle modyfikacja obszaru formu! wymaga bardziej skom-plikowanych czynno$ci. Aktualizacj" t" mo%emy przeprowadzi' od pocz#tku do ko*ca„r"cznie”.

76 Excel z elementami VBA w firmie

W omawianym arkuszu ko*cow# dat" obowi#zywania stopy procentowej równej11,50% okre$la data „uzyskana” za pomoc# funkcji DZI3(), co sprawia, %e dopóki niezostanie okre$lona przez ustawodawc" nowa stopa odsetkowa, wyliczenia odsetek mo%naprowadzi' zaraz po otwarciu arkusza, bez konieczno$ci wpisywania aktualnej daty dokomórki D.

W chwili kiedy zostanie wyznaczona przez ustawodawc" nowa stopa odsetkowa, na-le%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$' stopyodsetkowej oraz dat" pocz#tku jej obowi#zywania, jak pokazano to na rysunku 3.9.

Rysunek 3.9.RozpoczHcie proceduryaktualizacji tabeliodsetek ustawowych

2. Zaznaczy' komórki w zakresie D:J, znajduj#ce si" w wierszu po!o%onym powy%ejwiersza 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.Procedurakopiowania 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#djak na rysunku 3.13, co zako*czy proces aktualizacji — dopisania nowej pozycji„odsetkowej”.

Rozdzia" 3. Odsetki ustawowe lub podatkowe 77

Rysunek 3.11.Wygl)d komórekbezpoXredniopo wykonaniuprocedurykopiowania formu6

Rysunek 3.12.Aktualizacja komórkizawieraj)cejfunkcjH DZIY()

Rysunek 3.13.Wygl)d komórkipo wprowadzeniuformu6y 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 przecho-wywane s# stopy procentowe odsetek podatkowych oraz daty ich obowi#zywania (spe-cyfikacja w tabeli 3.2). Aby otrzyma' arkusz do nalicze* odsetek podatkowych, wystarczyskopiowa' arkusz Odsetki ustawowe do nowego arkusza, po czym w nowym arkuszuzast#pi' zapisy stóp procentowych oraz dat pocz#tku obowi#zywania (zapisane s# w ta-beli 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ór-ce ostatniego zapisanego wiersza, w kolumnie DO, nale%y wpisa' formu!" o postaci:=DziK().

78 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