plik

��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 korzySci, 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�liwoS� 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. " 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 Wydawnictwo Helion " Budowanie arkusza Faktury VAT ul. KoSciuszki 1c Excel asystent niezast�piony w ka�dej firmie! 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl 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 wi 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 4 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 6 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 o ci 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 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�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 Excel z elementami VBA w firmie Rysunek 3.1. Wygl d arkusza Odsetki ustawowe Rysunek 3.2. Wygl d arkusza Odsetki 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 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 Przyk ady\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). 70 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 da- nych, 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 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 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 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 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. 72 Excel z elementami VBA w firmie Rysunek 3.5. Elementy sk adowe formantu typu pole daty Rysunek 3.6. Wygl d paska formularza kalendarza po klikni ciu przycisku reprezentuj cego dzie 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 ozna- czone na rysunku 3.7 symbolami od 1 do 8. Rysunek 3.7. Wygl d obszaru formu 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 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: =DZI (). 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 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 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 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 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. 74 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 ca y 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 nalicze odsetek ustawowych Oznaczenie Kom�rka Formu a na rysunku 1 G6 =MAX(H9:H100) 2 G5 =MIN(H9:H100) 3 H7 =($C$4-$C$3)+1 4 I7 =ADR.PO R("$B"&G5) 5 J7 =((($C$2*I7)/100)/365)*H7 Rozdzia 3. Odsetki ustawowe lub podatkowe 75 Rysunek 3.8. Procedura oblicze w obszarze alternatywnym Obliczenia w obszarze alternatywnym s wykonywane przy ka dym doborze parame- tr�w nalicze odsetek ustawowych. Jednak wynik nalicze uzyskany w tym obszarze jest prawdziwy i zostaje przyj ty tylko w przypadku, gdy zawarto kom�rek G5 i G6 jest 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 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 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 ci w 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�wnej 11,50% okre la data uzyskana za pomoc funkcji DZI (), 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, 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 stopy odsetkowej oraz dat pocz tku jej obowi zywania, jak pokazano to na rysunku 3.9. Rysunek 3.9. Rozpocz cie 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 DZI (), a nast pnie przekopiowa zawarte w nim formu y do dw�ch nast pnych wierszy (rysunek 3.10). Rysunek 3.10. Procedura kopiowania formu 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 =DZI () (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 77 Rysunek 3.11. Wygl d kom�rek bezpo rednio po wykonaniu procedury kopiowania formu Rysunek 3.12. Aktualizacja kom�rki zawieraj cej funkcj DZI () Rysunek 3.13. Wygl d kom�rki po wprowadzeniu formu y DZI () 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, 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 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: =Dzi (). 78 Excel z elementami VBA w firmie Tabela 3.2. Stopy procentowe odsetek za zw ok od zaleg o ci 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 pa dziernika 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

Wyszukiwarka

Podobne podstrony:
Excel 2007 Jezyk VBA i makra Rozwiazania w biznesie
Excel 07 w firmie Controlling finanse i nie tylko ex27wf
Excel Vba Course Notes 2 Using Loops
Excel VBA Course Notes 1 Macro Basics
Excel 2013 PL Programowanie w VBA dla bystrzakow
option extended valid elements
Christmas elementary
elements

więcej podobnych podstron