Excel z elementami VBA w firmie

Wydawnictwo Helionul. Ko�ciuszki 1c44-100 Gliwicetel. 032 230 98 63e-mail: [email protected]

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!

http://helion.pl/view/2547w



http://helion.pl/add/2547w/exbiu2




mailto:[email protected]

http://helion.pl/view/2547w/exbiu2.htm

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


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.


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).


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.


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.


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


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.


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


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”.


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”.


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().


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

Excel z elementami VBA w firmie

Technology

Transcript of Excel z elementami VBA w firmie