Triki ExcelowePraktyczny poradnik dla controllera i analityka

MARIUSZ JANKOWSKIprogramista VBA, doświadczony praktyk w zakresie wykorzystania Excela i VBA w biznesie

BIBlIOTEKAINfORlEx

2

Spis treści:

Wła sny pa sek na rzę dzi z li stą dni ty go dnia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

WY SZU KAJ .PIO NO WO() od do łu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Po bra nie da nych z ze wnętrz nej ba zy Ac cess . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

So lver – opty ma li za cja kosz tów wy ciecz ki szkol nej . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

Skró ty kla wia tu ro we . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Śledzenie zmian w arkuszu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Zdarzenia formularza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Drukowanie w Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Obsługa błedów w kodzie makra . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Za okrą gla nie liczb w Exce lu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Li sta pli ków w ka ta lo gu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Śred nia w Exce lu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Ko rzy sta nie z ko lek cji w VBA – ko lek cja czy słow nik? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Okno dia lo go we . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Operacje na plikach i folderach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

Kwo ty skła da ją ce się na da ną su mę . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Sprawdzanie poprawności – lista zawężająca . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

Książ ki do na uki VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

Ro dza je błę dów w VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Wy sy ła nie pli ku Exce la e-m ailem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Osadzenie pliku wideo w Excelu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

Obliczanie kosztów czasu pracy maszyny . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

Utworzenie i instalacja dodatku . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Kalendarz w komórce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Ilość danej litery w arkuszu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53

Zaawansowane formatowanie warunkowe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Najpopularniejsze funkcje VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Edytor VB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Problem plecakowy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Nowe funkcje obliczeniowe w Excelu 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Wyszukiwanie według dwóch kryteriów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

kwiecień 2012 33

problem

rozwiązanie

Wła sny pa sek na rzę dziz li stą dni ty go dnia

W Exce lu moż na two rzyć wła sne pa ski na rzę dzi. Ko rzy stam czę sto z tej moż li wo ści, ale tym ra zem spra wa jest trud niej sza. Po pierw sze, chcę utwo rzyć pa sek z po zio mu VBA, tak aby był do stęp ny tyl ko w jed nym pli ku. Po dru gie, chciał bym mieć na nim kon tro l kę roz wi ja ną z na zwa mi wszyst kich dni ty go dnia – po dob ną do tej, któ ra wy świe tla li stę wszyst kich czcio-nek na pa sku „For ma to wa nie”. Mia ło by to dzia łać w ten spo sób, że po za zna cze niu ko mó rek w ar ku szu i wy bo rze od po wied nie go dnia z li sty, war tość z kon tro l ki wkle ja ła by się do tych ko mó rek. Czy coś ta kie go jest moż li we do wy ko na nia?

Roz wią za niem te go za da nia jest uru cho mie nie po niż sze go ma kra w try bie Work bo ok_Open() .

Wi­dok­1 po ka zu je za sto so wa nie ta kie go pa ska w ko mór kach ar ku sza .

44

Wi dok 1

n

Pliki do pobrania w interecieZe strony www.isc.infor.pl – zakładka „Excel

pliki do samodzielnych ćwiczeń” (zasoby dodatkowo płatne)

– można pobrać pliki z przykładami do opracowań z niniejszego poradnika .

kwiecień 2012 5

WY SZU KAJ.PIO NO WO() od do łuproblem

rozwiązanie

Bar dzo czę sto ko rzy stam z funk cji WY SZU KAJ.PIO NO WO() do przy pi sa nia kon kret nej war to ści z ko lum ny za miesz czo nej na pra wo od szu ka nej. Wiem, że funk cja ta po sia da wie le ogra ni czeń, ale jest bar dzo po pu lar na i sto sun ko wo pro sta w uży ciu. Funk cja ta zwra ca za wsze pierw szą zna le zio ną war tość, na wet je że li szu ka ny wpis wy stę pu je w pierw szej ko lum nie kil ka krot nie. Czy jest moż li wość, aby funk cja zwra ca ła pierw szą zna-le zio ną war tość, ale li cząc od do łu? Czy coś ta kie go jest moż li we do osią gnię cia tą lub in ną funk cją? Czy ko niecz ne jest uży cie ma kra?

Funk cja WY SZU KAJ .PIO NO WO() po tra fi w wy ni ku zwró cić ostat nią war tość z te go sa me go wier sza na pra wo od szu ka ne go wpi su, ale tyl ko pod jed nym, bar-dzo waż nym wa run kiem – da ne w pierw szej ko lum nie za kre su bę dą ce go ta be lą//ta bli cą dla funk cji WY SZU KAJ .PIO NO WO() mu szą być po sor to wa ne ro sną co . W prze ciw nym ra zie funk cja zwra ca nie pra wi dło wą war tość .

Wi dok 1 po ka zu je fik cyj ne da ty do ty czą ce sprze da ży pew nej fir my . Za da nie po le ga na tym, aby przy pi sać war tość ostat niej sprze da ży dla dnia 25 paź dzier ni-ka – da ta ta wy stę pu je na li ście trzy krot nie . For mu ła, któ ra zwra ca pra wi dło wy

wy nik (3), wy glą da na stę pu ją co:=WY SZU KAJ .PIO NO WO(DA TA(2010;10;25);$A$1:$B$11;2;PRAW DA)Funk cja sto su je al go rytm „pierw sze więk sze”, a to ozna cza, że szu ka pierw szej więk szej war to ści

(naj mniej szej spo śród więk szych od szu ka nej) i ja ko wy nik przy po rząd ko wu je kon kret ną war tość z wier sza po wy żej . W tym kon kret nym przy pad ku funk cja znaj du je da tę 27 paź dzier ni ka i w wy ni ku zwra ca war tość z ko mór ki po wy żej, czy li 3 .

Ist nie je tak że moż li wość, aby za da nie to wy ko nać w zu peł nie in ny spo sób – za po mo cą for mu ły ta bli co wej . W tym przy pad ku da ne nie mu szą być po sor to wa ne .l =IN DEKS($B$1:$B$11;MAX(JE ŻE LI($A$2:$A$11=DA TA(2010;10;25);WIERSZ($2:$11);’’ ‘’)))l Naj pierw po rów ny wa ne są da ty w ko lum nie A z da tą 25 paź dzier ni ka . Funk cja JE ŻE LI() zwra ca

ta bli cę: {””\””\””\5\6\7\””\””\””\””\}l Funk cja MAX() na stęp nie po bie ra war tość mak-

sy mal ną z tej ta bli cy – czy li 7 .l Funk cja IN DEKS() zwra ca w wy ni ku war tość

z siód me go wier sza ko lum ny A (3) .l Po nie waż jest to for mu ła ta bli co wa, na le ży ją

za twier dzić po przez kom bi na cję kla wi szy Ctrl+Alt+En ter .

l Na le ży pod kre ślić, że for mu ły ta bli co we dzia ła-ją wol no i moc no ob cią ża ją roz miar sko ro szy tu . W przy pad ku spo rej ilo ści ta kich for muł w pli-ku war to roz wa żyć na pi sa nie wła snej funk cji VBA . n

6

Po bra nie da nychz ze wnętrz nej ba zy Ac cess

problem

rozwiązanie

In te re su je mnie za gad nie nie współ pra cy po mię dzy ar ku szem kal ku la cyj nym Excel a pro gra-mem do za rzą dza nia ba za mi da nych Ac cess. Wiem, że moż na za rów no eks por to wać do Exce la da ne z ba zy da nych, jak i im por to wać do Ac ces sa ta be le utwo rzo ne w Exce lu. Cie ka-wi mnie na to miast, czy jest moż li we, aby po brać do Exce la frag ment ba zy Ac ces sa (np. kwe-ren dę) za po mo cą klik nię cia w przy cisk (ma kro). Dla przy kła du, mam utwo rzo ną ba zę da nych o na zwie „Roz li cze nia”, w któ rej znaj du je się kil ka ta bel po wią za nych ze so bą po przez re la cje. W jed nej ta be li mam ad re sy i da ne wszyst kich urzę dów skar bo wych (widok 1). Czy jest moż li we, aby po brać do Exce la tyl ko te z Po zna nia? Je że li tak, to czy jest moż li wość, aby w ła twy spo sób po brać da ne dla urzę dów skar bo wych z Kra ko wa?

Excel po zwa la na łą cze nie się z ze wnętrz ny mi pli ka mi (in ne sko ro szy ty, pli ki tek sto we, ba zy da nych Ac cess) za po mo cą mo de lu ADO (Ac ti veX Da ta Ob jects) . Obiekt ten po zwa la więc na po bra nie da nych nie tyl ko z ze wnętrz nych sko ro szy-tów, ale tak że z baz da nych . Aby po brać da ne z Ac ces sa, na le ży: zde fi nio wać po łą cze nie do ba zy, okre ślić za py ta nie SQL, sko pio wać wy ni ki do ar ku sza . Po niż-sze ma kro re ali zu je to za da nie .

Wi dok 2 po ka zu je wy nik dzia ła nia ma kra . Da ne do ty czą ce urzę dów skar bo-wych z Po zna nia zo sta ły pra wi dło wo prze ko pio wa ne do ar ku sza . Chcąc po brać

kwiecień 2012 77

da ne dla urzę dów z in ne go mia sta, na le ży po dać od po wied nią war tość dla sta łej sMia­sto.­ n

Wi dok 1

Wi dok 2

8

So lver – opty ma li za cja kosz tówwy ciecz ki szkol nej

problem

rozwiązanie

W Exce lu na co dzień wy ko nu ję róż ne ob li cze nia, pro ste ope ra cje, pod su mo wa nia. Tym ra zem mam do wy ko na nia du żo trud niej sze za da nie. Pro blem jest zwią za ny z sze ro ko po ję tym za gad nie niem opty ma li za cji, a mó wiąc ści ślej, do ty czy wy bo ru od po wied niej ilo ści au to ka rów na wy ciecz kę szkol ną dla dzie ci. Fir ma prze wo zo wa ofe ru je trzy ro dza je au to ka rów: A – au to kar miesz czą cy 32 oso by – koszt 600 zł, B – au to kar miesz czą cy 45 osób – koszt 800 zł, C – au to kar miesz czą cy 60 osób – koszt 1000 zł. Wszyst kich dzie ci jest aż 250. Zamierzam wy na jąć od po wied nią ilość au to ka rów, tak aby za brać wszyst kie dzie ci i jed no cze śnie nie prze pła cić, a w au to ka rach zo sta ło jak naj mniej pu stych miejsc. Czy te go ty pu za da nie jest moż li we do wy ko na nia za po mo cą Exce la?

Za da nia do ty czą ce opty ma li za cji i ob li cza nia rów nań z co naj mniej dwo ma nie-wia do my mi są w Exce lu wy ko ny wa ne za po mo cą do dat ku So­lver . Do da tek ten nie jest in sta lo wa ny stan dar do wo ra zem z pro gra mem . Ko niecz ne jest za tem je go do in-sta lo wa nie . Na le ży to zro bić po przez opcję NA­RZĘ­DZIA/DO­DAT­KI.. . Po za in sta lo-wa niu do dat ku mo że my przejść do zde fi nio wa nia wa run ków . Wi­dok­1 przed sta wia wstęp ny pro jekt ar ku sza .

Na pierw szy rzut oka roz wią za nie wy da je się dość skom pli ko wa ne . Opisz my je za tem:

1) Ko mór ki w ko lum nie E nie za wie ra ją żad nych for muł . Są to na sze zmien ne, któ re wy świe tlą kon-kret ną war tość po wy ko na niu za da nia . Ko mór ki te ozna cza ją ilość au to ka rów kon kret ne go ty pu, któ re na le ży wy na jąć .

Wi dok 1

kwiecień 2012 99

Wi dok 2

Wi dok 3

2) Ko mór ka C8 prze cho wu je for mu łę: =SU­MA.ILO­CZY­NÓW(Ce­na;Licz­ba_au­to­ka­rów). For mu ła ta ob li cza za tem ce nę, któ rą trze ba bę dzie za pła cić za wy na jem wszyst kich au to ka rów .

3) Ko mór ka C9 prze cho wu je for mu łę =­SU­MA.­ILO­CZY­NÓW(Licz­ba_osób;Licz­ba_au­to­ka­rów). For mu ła ta ob li cza su mę do stęp nych miejsc we wszyst kich wy na ję tych au to ka rach . Na stęp nym kro kiem jest okre śle nie wa run ków dla za da nia . Wi­dok­2 po ka zu je ogra ni cze nia, ja kie

mu szą zo stać uwzględ nio ne . 1) Ko mór ką ce lu jest C8, czy li kwo ta, ja ką mu si my za pła cić za wy na ję cie au to ka rów . Po nie waż dą ży my

do te go, aby zmi ni ma li zo wać koszt, wy bie ra my dru gą opcję MIN . 2) Na szy mi zmien ny mi są ko mór ki E3:E5 . 3) Licz ba miejsc we wszyst kich au to ka rach mu si być rów na lub więk sza 250 . 4) Ilość au to ka rów mu si być licz bą cał ko wi tą, więk szą lub rów ną 0 . 5) Po wpro wa dze niu wa run ków na le ży klik nąć przy cisk Roz wiąż (So lve) . Wi­dok­3 po ka zu je roz wią-

za nie za da nia . Oka zu je się, że naj bar dziej opła cal ną opcją jest wy na ję cie trzech au to ka rów za 800 zł i dwóch za 1000 zł . W ta kiej kon fi gu ra cji ist nie je pięć wol nych miejsc . n

10

Skró ty kla wia tu ro weproblem

rozwiązanie

In te re su je mnie za gad nie nie skró tów kla wia tu ro wych. Po wszech nie uwa ża się, że ich sto so-wa nie znacz nie uła twia i przy spie sza pra cę z Exce lem. Na do brą spra wę jed nak nie wie lu użyt kow ni ków w prak ty ce ko rzy sta z kla wia tu ry – więk szość uru cha mia po szcze gól ne opcje, kli ka jąc w ikon ki na pa skach lub wstąż kach. Czy jest ja kiś pro sty spo sób, aby na uczyć się skró tów kla wia tu ro wych Exce la? W in ter ne cie moż na zna leźć wie le róż nych stron ze skró ta-mi, ale przy swo je nie tej wie dzy jest do syć cięż ko straw ne.

Mo im zda niem naj lep sze i naj szyb sze efek ty przy no si wy dru ko wa nie ta be li skró-tów utwo rzo nej w for mie pew nej ma cie rzy .

Do naj po pu lar niej szych i naj bar dziej przy dat nych skró tów mo że my za li czyć:F1­–­Po­mocF2­–­Tryb­edy­cji­ko­mór­kiF5­–­Przejdź­do...­Ko­mór­ki­spe­cjal­neF8­–­Tryb­za­zna­cza­nia­ob­sza­rówF9­–­Prze­li­cze­nie­for­muł

kwiecień 2012 1111

F12­–­Za­pisz­ja­ko...De­le­te­–­Wy­czysz­cze­nie­za­war­to­ści­ko­mó­rekCtrl­+­A­–­Za­zna­cze­nie­wszyst­kich­ko­mó­rek­ar­ku­szaCtrl­+­B­–­Włą­cze­nie/wy­łą­cze­nie­po­gru­bie­nia­ko­mór­kiCtrl­+­F­–­Znajdź...Ctrl­+­H­–­Za­mień­na...Ctrl­+­N­–­No­wy­sko­ro­szytCtrl­+­O­–­Otwórz­sko­ro­szytCtrl­+­S­–­Za­pi­sa­nie­pli­kuCtrl­+­Z­–­Cof­nię­cie­ostat­niej­ope­ra­cji­ n

12

problem

rozwiązanie

Śledzenie zmian w arkuszu

W mojej pracy korzystam z pliku, który zamieszczony jest na dysku sieciowym. Plik jest ogólnodostępny – mogą z niego korzystać różne osoby. Chciałbym się dowiedzieć, czy jest możliwość monitorowania zmian wprowadzonych w obrębie jednego z arkuszy w tym pliku. Wiem, że Excel oferuje opcję śledzenia i raportowania zmian, ale jest ona mało popu-larna, a korzystanie z niej wymaga współdzielenia skoroszytu – czy się tego chce czy nie. Oprócz tego właczenie śledzenia zmian wyłącza wiele standardowych funkcji Excela. Reasumując, czy jest jakiś sposób na to, aby monitorować zmiany wprowadzone w obrębie jednego arkusza?

Rozwiązaniem może być utworzenie ukrytego i chronionego arkusza, który przechowywać będzie historię modyfikacji arkusza źródłowego . Widok 1 pokazuje przykładową tabelę zamieszczoną w Arkusz1 . Modyfikacje tego arkusza będą reje-strowane w zakładce Arkusz2 (widok 2) .

Widok 1

Widok 2

Obydwa makra rejestrujące zmianę powinny zostac umieszczone w module zakładki Arkusz1 .Option ExplicitDim vStaraWartosc As Variant

kwiecień 2012 13

Pri va te Sub Work she et_Chan ge(By Val Tar get As Ran ge)­­'Za­kończ­je­że­li­za­zna­czo­no­wię­cej­niż­1­ko­mór­kę If Tar get .Cells .Co unt > 1 Then Exit Sub

­­'Wy­łą­cze­nie­zda­rzeń­i­od­świe­ża­nia­ekra­nu With Ap pli ca tion .Scre enUp da ting = Fal se .Ena ble Events = Fal se End With

'Ścią­gnię­cie­ochro­ny­z­ar­ku­sza­re­je­stru­ją­ce­go Ar kusz2 .Unpro tect Pas sword:=''Taj ne''

'Za­pi­sa­nie­zmian­w­ar­ku­szu­re­je­stru­ją­cym With Ar kusz2 .Ran ge(''A'' & Rows .Co unt) .End(xlUp) .Of f set(1, 0) .Va lue = Tar get .Ad dress .Of f set(0, 1) = vSta ra War tosc .Of f set(0, 2) = Tar get .Of f set(0, 3) = Da te .Of f set(0, 4) = Ti me End With

­­'Przy­wró­ce­nie­ochro­ny­z­ar­ku­sza­re­je­stru­ją­ce­go Ar kusz2 .Pro tect Pas sword:=''Taj ne''

­­'Przy­wró­ce­nie­usta­wień­apli­ka­cji With Ap pli ca tion .Scre enUp da ting = True .Ena ble Events = True End WithEnd Sub

Pri va te Sub Work she et_Se lec tion Chan ge(By Val Tar get As Ran ge)­­'Po­bierz­do­zmien­nej­mo­du­łu­war­tość­za­zna­czo­nej­ko­mór­ki If Not In ter sect(Ar kusz1 .Ran ge(''A1'') .Cur ren tRe gion, Tar get) Is No thing Then vSta ra War tosc = Tar get End IfEnd Sub

n

14

Zda rze nia for mu la rza

problem

Do pie ro nie daw no za czą łem swo ją przy go dę z pi sa niem makr dla Exce la. Mam pro blem ze zro zu mie niem te go, w ja ki spo sób funk cjo nu ją for mu la rze w VBA. Nie ro zu miem, ja ka jest róż ni ca po mię dzy ukry ciem for mu la rza a usu nię ciem go z pa mię ci. Trud no mi wy ła pać róż-ni ce po mię dzy zda rze nia mi for mu la rza, po nie waż są one bar dzo po dob ne. Nie wiem, jak się ma In i tia li ze() do Ac ti va te() lub Qu eryC lo se() do Ter mi na te() i De ac ti va te(). Nie za wsze wiem, kie dy za sto so wać me to dy ta kie, jak: Lo ad, Unlo ad i Hi de. Czy mógł bym po pro sić o ja kieś pro ste wy ja śnie nie lub roz róż nie nie tych zda rzeń i me tod?

For mu larz mu si naj pierw zo stać za ła do wa ny do pa mię ci (zda rze nie In i tia li ze), za nim zo sta nie wy świe tlo ny (zda rze nie Ac ti va te) . Je że li for mu larz zo stał za ła do wa-ny i wy świe tlo ny, a na stęp nie ukry ty, wów czas je go usta wie nia na dal prze cho wy-wa ne są w pa mię ci kom pu te ra (zo sta ją za pa mię ta ne ostat nie usta wie nia wpro wa-dzo ne przez użyt kow ni ka przed ukry ciem, np . li sta źró dło wa dla po la kom bi wraz z ak tu al nym wy bo rem, za zna czo ne po la wy bo ru czy do kład ny wpis w po lu tek sto-wym itp .) . Gdy for mu larz jest na to miast za my ka ny, na stę pu je je go usu nię cie z pa mię ci kom pu te ra (wła ści wo ści je go kon tro lek przyj mu ją do myśl ne war to ści – ostat nio wpro wa dzo ne usta wie nia zo sta ją nie ja ko wy ka so wa ne z pa mię ci . Dla te-

go nie moż na się do nich od wo ły wać póź niej w ko dzie – chy ba że zo sta ły one za pi sa ne do ko mó rek ar ku sza lub do zmien nych pu blicz nych) .

Naj lep szym spo so bem na to, aby zo bra zo wać róż ni ce po mię dzy wy bra ny mi me to da mi i zda rze nia mi, ja kie ge ne ru ją, jest mo ni to ro wa nie (śle dze nie) za cho wa nia for mu la rza . Wi dok 1 przed sta wia for mu larz z trze ma przy ci ska mi, każ dy z nich ma przy pi sa ne osob ne ma kro .

Sprawdź my za tem, ja kie zda rze nia bę dą ge ne ro wa ne po za sto so wa niu kon kret nych po le ceń .l Lo­ad­User­Form1

Ta in struk cja ła du je for mu larz do pa mię ci, ale nie wy świe tla go . Wy ko na ne zo sta je za tem tyl ko zda-rze nie In i tia li ze . For mu larz jest nie wi docz ny, więc zda rze nie Ac ti va te nie wy stę pu je .l User­Form1.Show

Ta in struk cja wy ko nu je dwie czyn no ści: ła du je for mu larz do pa mię ci (In­i­tia­li­ze), a na stęp nie wy świe-tla go (Ac­ti­va­te) w sy tu acji, gdy for mu larz two rzo ny jest od no wa (nie ma go w pa mię ci) . Je że li na to-miast for mu larz jest ukry ty w pa mię ci, po da na in struk cja je dy nie go wy świe tla (Ac­ti­va­te) .l User­Form1.Hi­de­lub­Me.Hi­de­(gdy kod za miesz czo ny w mo du le for mu la rza)

Ta in struk cja ukry wa for mu larz, ale nie usu wa go z pa mię ci . Ak tu al ne da ne z for mu la rza są wciąż do stęp ne w pa mię ci kom pu te ra . Me to da ta nie ge ne ru je żad ne go zda rze nia . Aby wy świe tlić for mu larz, z po wro tem na le ży sko rzy stać z me to-dy Show .l Unlo­ad­ User­Form1­ lub­ Unlo­ad­ Me (gdy kod za miesz czo ny w mo du le for mu la rza)

Ta in struk cja cał ko wi cie usu wa for mu larz z pa mię ci kom pu te ra, spra wia, że prze sta je on być wi docz ny i do stęp ny z po zio mu ko du VBA .

rozwiązanie

Wi dok 1

kwiecień 2012 15

Pri va te Sub cmdWy swietl Na step ny_Click() User Form2 .ShowEnd Sub

Pri va te Sub cmdU kryj_Click() Me .Hi deEnd Sub

Pri va te Sub cmdZa mknij_Click() Unlo ad MeEnd Sub

In struk cja ta jest wy wo ły wa na po klik nię ciu w krzy żyk znaj du ją cy się w pra wym gór nym ro gu for mu-la rza – ge ne ru je dwa zda rze nia . Pierw sze z nich Qu­eryC­lo­se­po zwa la nam na anu lo wa nie de cy zji i umoż li wia dal szy do stęp do for mu la rza . Dru gie to Ter­mi­na­te, któ re wy stę pu je już po usu nię ciu for-mu la rza z pa mię ci .l User­Form2.Show

Ta in struk cja wy świe tla dru gi for mu larz – nie ukry wa ani nie usu wa pierw sze go . W mo men cie wy świe tle nia dru gie go for mu la rza za cho dzi na to miast zda rze nie De ac ti va te dla for mu-

la rza pierw sze go i jest to lo gicz ne, po nie waż to dru gi for mu larz sta je się for mu la rzem ak tyw nym . n

16

problem

Dru ko wa nie w Exce lu

Ja kie są naj bar dziej przy dat ne opcje i usta wie nia do ty czą ce dru ko wa nia wy bra nych ko mó-rek w ar ku szu? Po nie waż pro jekt, któ rym się zaj mu ję, jest czę ścią więk szej apli ka cji, in te re-so wa ły by mnie krót kie in struk cje VBA wraz z ko men ta rzem.

Dru ko wa nie ar -ku szy z po zio mu VBA jest tak sa mo pro ste jak dru ko-wa nie z po zio mu Exce la . Ar kusz1 ozna cza na zwę ko do wą ar ku sza za wie ra-ją ce go ta be lę .

● Ar­kusz1.Pa­ge­Se­tup.Orien­ta­tion­==­xlPor­tra­it'xlLand­sca­pe

Wy dru ko wa nie ta be li w orien ta cji pio-no wej (xlPor­tra­it) lub po zio mej (xlLand-sca­pe) .● ­Ar­kusz1.Pa­ge­Se­tup.Prin­tQu­ali­ty­=

=­600Usta wie nie ja ko ści wy dru ku – do myśl-nie 600 dpi .● Ar­kusz1.Pa­ge­Se­tup.Pa­per­Si­ze­=­xlPa-

pe­rA4Usta wie nie roz mia ru wy dru ku – do myśl nie A4 .● Ar­kusz1.Pa­ge­Se­tup.Prin­tA­rea­=

=­''$A$1:$C$27''Ob szar wy dru ku – za kres ko mó rek przy le ga ją cych do ko mór ki A1 .● Ar­kusz1.Pa­ge­Se­tup.Print­Ti­tle­Rows­=

=­“$1:$1”Bar dzo przy dat na opcja – po wtó rze nie na głów ków ta be li na każ dej stro nie wy dru ku .● Ar­kusz1.Pa­ge­Se­tup.Prin­tHe­adings­=

=­TrueTa be la wy dru ko wa na wraz z na głów-ka mi ar ku sza .● Ar­kusz1.Pa­ge­Se­tup.Print­Gri­dli­nes­=­

=­TrueTa be la wy dru ko wa na wraz z li nia mi siat ki .

rozwiązanie

Wi dok 1

Wi dok 2

kwiecień 2012 17

Wi dok 3 ● Ar­kusz1.Pa­ge­Se­tup.Print­Com­ments­=­xlPrin­tIn­Pla­ce

Wy dru ko wa nie ko men ta rzy tak jak w ar ku szu .● Ar­kusz1.Pa­ge­Se­tup.Le­ftHe­ader­=­

''&F''Wy dru ko wa nie na zwy pli ku w le wym na głów ku stro ny .● Ar­kusz1.Pa­ge­Se­tup.Ri­gh­tHe­ader­ =­­

=­''&D''­&­Chr(10)­&­''&T''Wy dru ko wa nie da ty i cza su w pra-wym gór nym na głów ku stro ny .● Ar­kusz1.Pa­ge­Se­tup.Cen­ter­Fo­oter­ =­

=­''Stro­na­&P­z­&N''Wy dru ko wa nie nu me ru ak tu al nej stro ny i wszyst kich stron w środ ko wej stop ce .In ne przy dat ne po le ce nia:● ­Ar­kusz1.Print­Pre­view

Pod gląd wy dru ku dla zde fi nio wa ne go wcze śniej za kre su ko mó rek .● Ar­kusz1.Prin­tO­utBez po śred ni wy druk (moż li wość usta wie nia m .in . za kre su stron, licz by ko pii) .● Ac­ti­ve­Win­dow.View­=­xlNor­ma­l­ViewWy świe tle nie ak tyw ne go okna w try bie nor mal nym .● Ac­ti­ve­Win­dow.View­=­xlPa­ge­Bre­ak­Pre­viewWy świe tle nie ak tyw ne go okna w try bie pod glą du po dzia łu stron .● Ar­kusz1.Di­splay­Au­to­ma­tic­Pa­ge­Bre­aks­=­TrueWy świe tle nie li nii po dzia łu stron dla ar ku sza . n

18

problem

Ob słu ga błę dów w ko dzie ma kra

Od dłuż sze go cza su ko rzy stam z Exce la. Do pie ro nie daw no jed nak za czą łem swo ją przy go-dę z pi sa niem makr. Prze glą dam róż ne ko dy i nie po tra fię zro zu mieć, w ja ki spo sób dzia ła ją po szcze gól ne in struk cje ob słu gi błę dów. Nie wiem tak że, czy two rze nie ta kiej ob słu gi jest w ogó le ko niecz ne, po nie waż tyl ko nie któ re ma kra ją po sia da ją. Czy mógł bym zo ba czyć przy kła do we ma kro wraz z do brze na pi sa ną ob słu gą błę dów?

Pro fe sjo nal na ob słu ga błę dów jest nie zwy kle waż na, jest czę sto tym, co od róż nia do bre go fa chow ca od pseu do pro gra mi sty . Błę dy są nie od łącz nym ele men tem pro gra-mów . Im bar dziej zło żo na apli ka cja, tym wię cej wa run ków na le ży ob słu żyć, tym wię cej sy tu acji trze ba prze wi dzieć . Nie za wsze jed nak je ste śmy w sta nie prze wi dzieć wszyst kich sce na riu szy, te go, co mo że zro bić użyt kow nik, dla te go do bra ob słu ga błę-dów mu si obej mo wać wszyst kie moż li we przy pad ki wy stą pie nia błę dów .

Po niż sze ma kro do da je do pli ku no wy ar kusz . Wy da wać by się mo gło, że ta z po zo ru pro sta ope ra cja po win na za mknąć się w jed nej li nij ce ko du . W tym przy-pad ku za sto so wa łem jed nak ob słu gę błę dów, któ ra po zwa la użyt kow ni ko wi na po no wie nie pró by przy pi sa nia wła ści wej na zwy dla no wo utwo rzo ne go ar ku sza .

rozwiązanie

kwiecień 2012 1919

W pierw szej ko lej no ści okre ślam, że gdy wy stą pi błąd w ma krze, pro ce du ra po win na przejść do ety kie ty Ob­slu­ga­Ble­dow . Na stęp nie wy świe tla się okien ko, w któ rym pro szę użyt kow ni ka o wpi sa nie na zwy dla ar ku sza . Ko lej ną in struk cją jest do da nie no we go ar ku sza i przy pi sa nie mu zmien nej obiek-to wej, a na stęp ną zmia na na zwy na tę po da ną w okien ku…

I tu taj mo gą po ja wić się pro ble my, z co naj mniej dwóch przy czyn . Po pierw sze, na zwa ar ku sza nie mo że być po wie lo na, każ dy ar kusz w pli ku mu si mieć in ną na zwę . Po dru gie, na zwa ar ku sza mu si speł niać kil ka wy mo gów do ty czą cych dłu go ści i za war to ści tek stu . Oby dwie sy tu acje ge ne ru ją błąd 1004 (nie pra wi dło wa na zwa ar ku sza) . W sy tu acji gdy użyt kow nik po da je złą na zwę, ma kro nie jest w sta nie zmie nić na zwy dla te go no we go ar ku sza, wy zwa la błąd i prze cho dzi do ob słu gi błę-dów . Po now nie wy świe tlo ne zo sta je okien ko, a w nim proś ba o po da nie wła ści wej na zwy ar ku sza . Je że li użyt kow nik po da tym ra zem pra wi dło wą na zwę – na zwa zo sta nie zmie nio na, a ma kro za koń-czo ne . Je że li nie wpi sze nic (klik nie przy cisk Anu­luj­ lub OK), ar kusz zo sta nie ska so wa ny z pli ku . Je że li zno wu po da błęd ną na zwę – po now nie wy świe tli się okien ko z proś bą o wpi sa nie pra wi dło wej na zwy . Je że li użyt kow nik wpi sze na sa mym po cząt ku pra wi dło wą na zwę, wów czas ma kro zo sta nie wy ko na ne wła ści wie i za koń czy się na li nii Exit­Sub (nie przej dzie do ob słu gi błę dów) . n

20

Za okrą gla nie liczb w Exce lu

Pra cu ję w dzia le kadr i mam pro blem ze zro zu mie niem, w ja ki spo sób Excel za okrą gla licz by. Mój pierw szy po le ga na tym, że Excel źle ob li cza mi su mę wszyst kich za ku pio nych pro duk tów, mi mo że kwo ty wy świe tla ją się pra wi dło wo. Mój dru gi pro blem po le ga na tym, że nie po tra fię na pi sać for mu ły, któ ra bę dzie za okrą gla ła kwo tę do dwóch miejsc po prze-cin ku. Licz ba 5 ma być za okrą gla na w gó rę, zgod nie z ka no na mi obo wią zu ją cy mi w księ go wo ści. W Exce lu jest bar dzo du żo funk cji za okrą gla ją cych, czy mo gła bym pro sić o po ka za nie róż nic mię dzy ni mi?

problem

Od po wiedź na py ta nie pierw sze jest dość pro sta i mo gli by śmy ją stre ścić sło wa mi: „Licz ba, któ rą wi dzisz w ko mór ce ar ku sza, nie jest nią w rze czy wi sto ści” . Cho dzi o to, że licz ba umiesz czo na w ko mór ce ar ku sza bar dzo czę sto jest sfor ma to wa na w od po wied ni spo sób . Mo że to być for mat wa lu to wy, pro cen to wy lub np . bar dzo czę sto sto so wa ny for mat licz bo wy z dwo ma miej sca mi po prze cin ku . Licz ba 125,2384 sfor ma to wa na w ostat ni spo sób wy świe tlo na zo sta nie w ko mór ce ja ko 125,24 . Więk-szość po cząt ku ją cych użyt kow ni ków uwa ża, że Excel sam za okrą gla tę licz bę do dwóch miejsc po prze cin ku i to, co wi dzi my w ko mór ce (125,24), jest już no wą licz bą, na któ rej mo że my da lej ope ro wać . Jest to przy czy ną wie lu błę dów i nie po ro-

zu mień, po nie waż Excel w rze czy wi sto ści zmie nia tyl ko spo sób wy świe tla nia licz by, nie in ge ru je na to-miast w sa mą licz bę – jej rze czy wi stą war tość moż na po dej rzeć na pa sku for mu ły (125,2384) . Do da jąc kwo tę po dat ku VAT, do da je my ją do licz by 125,2384, a nie 125,24 – jak mo gło by się wy da wać .

Od po wia da jąc na py ta nie dru gie . Fak tycz nie w Exce lu ma my bar dzo du żo funk cji za okrą gla ją cych, z któ rych tak na praw dę rzad ko ko rzy sta się w prak ty ce . Co wię cej, ist nie je spo ra nie kon se kwen cja pro gra mi stów Mi cro so ftu w al go ryt mach wy ko rzy sta nych przy za okrą gla niu . Prze ana li zuj my so bie do kład nie ta be lę za war tą na wi do ku 1 . Ma my tam ce ny pod sta wo we (przed za okrą gle niem) trzech pro-duk tów i sie dem róż nych funk cji za okrą gla ją cych . Tyl ko w dwóch przy pad kach uzy sku je my ta ki sam wy nik . Prze ana li zuj my po ko lei każ dą funk cję: l ZA OKR .DO .CAŁK() – funk cja w każ dym przy pad ku po zo sta wia część cał ko wi tą licz by i ob ci na część

ułam ko wą .

rozwiązanie

Wi dok 1

kwiecień 2012 2121

l ZA OKR() – funk cja za okrą gla kwo tę do dwóch miejsc po prze cin ku, za tem za okrą gla na jest za wsze trze cia cy fra po prze cin ku . Funk cja sto su je al go rytm stan dar do wy, za tem 39,434 zo sta je za mie nio ne na 39,43, a 17,185 na 17,19 (piąt ka za wsze jest za okrą gla na w gó rę) . Jest to do kład nie ta kie roz wią-za nie, o ja kie nam cho dzi (i je dy ne, jak prze ko na my się ana li zu jąc na stęp ne for mu ły) .

l ZA OKR .DÓŁ() – w tym przy pad ku funk cja ob ci na każ dą trze cią licz bę po prze cin ku, a mó wiąc pre-cy zyj niej, za okrą gla ją w dół, czy li do ze ra . W efek cie kwo ta 17,185 zo sta ła za okrą glo na do 17,18 . Trze cia cy fra po prze cin ku jest w tym przy pad ku nie waż na – dla kwo ty 17,189 Excel rów nież zwró-ci war tość 17,18 .

l ZA OKR .GÓ RA() – funk cja dzia ła od wrot nie do funk cji ZA OKR .DÓŁ() . W na szym przy pad ku za okrą-gla w gó rę każ dą dru gą cy frę po prze cin ku, dla te go 17,185 zo sta je prze kon wer to wa ne na 17,19, a 39,434 na 39,44 .

l ZA OKR .W .DÓŁ() – funk cja ta ma nie co in ną bu do wę od funk cji ZA OKR .DÓŁ() . Tak że po sia da dwa ar gu men ty, ale dru gi peł ni zu peł nie in ną ro lę . Funk cja ta za okrą gla licz bę w dół, w kie run ku ze ra, do naj bliż szej wie lo krot no ści istot no ści .

l ZA OKR .W .GÓ RĘ() – funk cja dzia ła ją ca od wrot nie do funk cji ZA OKR .W .DÓŁ() . l ZA OKR_VBA – jest to funk cja wła sna VBA, któ ra wy glą da w ten spo sób:

Funk cja ta sto su je al go rytm ban kier ski, któ ry trak tu je cy frę 5 w spe cy ficz ny spo sób . Je że li cy fra po prze dza ją ca piąt kę jest licz bą pa rzy stą, wte dy Excel za okrą gla licz bę w dół (17,185 zo sta ło za okrą-glo ne do 17,18), na to miast je że li cy fra po prze dza ją ca piąt kę jest licz bą nie pa rzy stą, wte dy Excel za okrą-gla licz bę w gó rę (21,435 zo sta ło za okrą glo ne do 21,44) . n

22

Li sta pli ków w ka ta lo gu

Wiem, że ist nie je ma kro, któ re po zwa la na zro bie nie spi su pli ków znaj du ją cych się w wy bra-nym ka ta lo gu (z opcjo nal nym uwzględ nie niem pod fol de rów). Ta kie ma kro jest jed nak dość skom pli ko wa ne – trud no je zro zu mieć. Czy mógł bym otrzy mać przy kład ko du wraz z do kład-nym ko men ta rzem?

Rze czy wi ście, ta kie ma kro ist nie je . Po ni żej przy kła do wy kod:

problem

rozwiązanie

kwiecień 2012 23

24

Efekt dzia ła nia ma kra moż na prze śle dzić po ni żej .

Naj pierw ma kro two rzy no wy plik i do da je w je go pierw szym ar ku szu sfor ma to wa ny na głó wek z in for ma cją o ka ta lo gu, a tak że wsta wia na zwy ko lumn . Na stęp nie na stę pu je ak ty wa cja pod pro ce du-ry, w któ rej przy pi su je my fol de ro wi głów ne mu kon kret ną zmien ną obiek to wą . W pierw szej ko lej no ści na stę pu je pę tla po pli kach znaj du ją cych się bez po śred nio w ka ta lo gu głów nym, czy li­C:\Pro­gram­Fi­les\In­ter­net­Explo­rer (wła śnie te pli ki po ka zu je wi­dok­1) . Po wy pi sa niu wszyst kich pli ków, ma kro w pę tli spraw dza wszyst kie ist nie ją ce w ka ta lo gu głów nym fol de ry i po now nie wy wo łu je pod pro ce du rę dla każ de go z nich . Je że li da ny fol der za wie ra ja kieś pli ki, to zo sta ną one do pi sa ne do li sty . Je że li w fol-de rze tym do dat ko wo znaj du ją się in ne pod fol de ry, wów czas tak że zo sta ną one uwzględ nio ne .

W ten spryt ny spo sób ma my do czy nie nia z pę tlą, któ ra do pi su je do li sty wszyst kie pli ki znaj du ją-ce się we wszyst kich pod fol de rach ka ta lo gu głów ne go C:\Pro­gram­Fi­les\In­ter­net­Explo­rer .

Wi dok 1

kwiecień 2012 25

Śred nia w Exce lu

Mam kil ka py tań do ty czą cych ob li cza nia śred niej. Po pierw sze, chciał bym się do wie dzieć, ja ka jest róż ni ca po mię dzy funk cja mi: ŚRED NIA() a ŚRED NIA.A(). Wy glą da ją one bar dzo po dob nie, ale na pew no nie dzia ła ją iden tycz nie. Po dru gie, za uwa ży łem, że w Exce lu znaj du je się kil ka in nych funk cji po zwa la ją cych na ob li cze nie śred niej. Szcze rze mó wiąc, nie wiem, jak one dzia ła ją – czy mógł bym po pro sić o przy kła dy? Po trze cie, czy w Exce lu ist nie-je wbu do wa na funk cja, któ ra po zwo li ła by mi ob li czyć śred nią wa żo ną?

problem

Rze czy wi ście, Excel po sia da w swo im zbio rze funk cji ar ku szo wych za rów no funk cję ŚRED NIA(), jak i ŚRED NIA .A() . Róż ni ce po mię dzy ni mi do ty czą spo so bu trak to wa nia tek stu i war to ści lo gicz nych PRAW DA/FAŁSZ .

Jak po ka zu je wi dok 1, wy ni ki dla obu funk cji róż nią się aż w trzech przy pad-kach . Stan dar do wa funk cja ŚRED NIA() po mi ja w swo ich ob li cze niach wszyst ko to, co nie jest licz bą (pu sta ko mór ka, for mu ła zwra ca ją ca w wy ni ku pu sty ciąg, tekst, war tość lo gicz na) . W każ dym przy pad ku funk cja zwra ca w wy ni ku 3, czy li de fac-to bie rze do ob li czeń tyl ko dwie licz by (2 i 4) . Funk cja ŚRED NIA .A() co praw da

po mi ja tak że pu ste ko mór ki, ale już bie rze pod uwa gę for mu ły zwra ca ją ce pu ste cią gi, tekst i war to ści lo gicz ne, a mó wiąc do kład niej, za mie nia je na ze ra . Stąd też w wy ni kach for muł otrzy ma li śmy 2 ja ko wy nik dzia ła nia ma te ma tycz ne go (0+2+4/)/3 .

Excel, oprócz tych dwóch funk cji, po sia da w swo jej ko lek cji tak że in ne po zwa la ją ce ob li czyć śred nią dla da ne go zbio ru liczb:l ­ ŚRED NIA .GEO ME TRYCZ NA() – po zwa la ob li czyć śred nią geo me trycz ną liczb po da nych przez ar gu-

men ty (wszyst kie mu szą być więk sze od ze ra) . W prak ty ce funk cja ta mo że być wy ko rzy sta na do ob li cze nia śred nie go, pro cen to we go przy ro stu zy sków fir my np . w ska li ro ku (pod wa run kiem, że w każ dym ro ku ma ją tek fir my się zwięk sza) lub po pro stu ob li cze nia śred nie go współ czyn ni ka przy-ro stu dla do wol nej zmien nej w okre ślo nym cza sie (np . śred ni rocz ny przy rost wzro stu ma łe go dziec-ka, li czo ny na prze strze ni kil ku lat) .

l ­ ŚRED NIA .HAR MO NICZ NA() – jest od wrot no ścią śred niej aryt me tycz nej od wrot no ści . Śred nia har-mo nicz na jest za wsze mniej sza niż śred nia geo me trycz na, któ ra z ko lei jest za wsze mniej sza niż śred nia aryt me tycz na . Śred niej tej uży wa my, gdy da ne są war to ścia mi względ ny mi (np . licz ba miesz-kań ców na km2 dla da ne go mia sta) z rów ny mi wa ga mi (ta ka sa ma licz ba miesz kań ców wszyst kich ba da nych miast) . Śred nia har mo nicz na w wy ni ku zwró ci śred nią gę stość za lud nie nia dla obu miast łącz nie .

rozwiązanie

Wi dok 1

26

l ­ ŚRED NIA .WEWN() – bar dzo przy dat na funk cja, ale sto sun ko wo ma ło zna na i rzad ko wy ko rzy sty wa-na . Po zwa la na ob li cze nie śred niej aryt me tycz nej dla da ne go zbio ru liczb po od rzu ce niu war to ści skraj nych . Dru gi ar gu ment funk cji de cy du je o tym, ja ka część da nych po win na zo stać wy klu czo na z ob li czeń . Dla przy kła du, je że li zbiór li czy 10 ele men tów, a war tość dru gie go ar gu men tu to 0,2, wów czas zo sta ną z nie go wy klu czo ne dwie war to ści: jed na skraj na gór na i jed na skraj na dol na . Funk cja wy klu cza skraj ne war to ści w spo sób sy me trycz ny – ty le sa mo z gó ry co z do łu . Mo że być ona sto so wa na wszę dzie tam, gdzie ana li zu je się pew ne da ne za wie ra ją ce spo re od chy le nia i war to-ści skraj ne, np . po miar rze czy wi stej śred niej za rob ków w kra ju .Excel nie po sia da na to miast funk cji, któ ra po zwa la ła by ob li czyć śred nią wa żo ną . Ist nie ją dar mo we

do dat ki, któ re ta ką funk cję za wie ra ją, np . Mo re func . Moż na też ta ką for mu łę skon stru ować so bie sa me mu .Wi dok 2 po ka zu je ob li cze nie śred niej wa żo nej ucznia na ko niec se me stru . Na uczy ciel uznał, że naj-

waż niej szy jest spraw dzian koń co wy (wa ga 5), na stęp nie re fe rat (4) i kart ków ka (2) .For mu ła w ko mór ce E2 to:

=SU MA(B2*5;C2*4;D2*2)/11Naj pierw na stę pu je prze mno że nie ocen przez kon kret ne wa gi, na stęp nie zsu mo wa nie wy ni ków

i po dzie le nie war to ści przez su mę wag (5+4+2) . Ta ki spo sób po dej ścia do wy sta wia nia ocen jest z pew-no ścią lep szy od pro ste go wy li cze nia śred niej aryt me tycz nej .

Wi dok 2

kwiecień 2012 27

Ko rzy sta nie z ko lek cji w VBA– ko lek cja czy słow nik?

W swo ich ma krach bar dzo czę sto ko rzy stam z ko lek cji. To po zwa la mi w ła twy spo sób umie-ścić te sa me obiek ty w jed nej gru pie. Ko lek cję wy ko rzy stu ję tak że do po bra nia uni ka to wych war to ści z za kre su ko mó rek (póź niej prze no szę je do ta bli cy i wy peł niam ni mi po le kom bi lub po le li sty na for mu la rzu). Nie daw no prze czy ta łem, że ja ko al ter na ty wę moż na sto so wać obiekt Dic tio na ry – słow nik, któ ry da je więk sze moż li wo ści i jest bar dziej ela stycz ny. Czy rze czy wi ście za miast ko lek cji lep szym roz wią za niem jest sto so wa nie w ko dzie słow ni ka? Czy mógł bym otrzy mać ja kieś do dat ko we, cie ka we in for ma cje do ty czą ce ko lek cji, tak że w kon tek ście po rów na nia ze słow ni kiem?

VBA jest ję zy kiem obiek to wym, a to ozna cza, że pi sząc kod, od wo łu je my się do kon kret nych obiek tów (ich wła ści wo ści, me tod czy na wet zda rzeń) . Obiek tem mo że być sam Excel (obiekt Ap­pli­ca­tion), sko ro szyt (obiekt Work­bo­ok), po je dyn czy ar kusz (obiekt Work­she­et), ko mór ka w ar ku szu (obiekt Ran­ge) czy ta be la prze staw na (obiekt Pi­vot­Ta­ble) . Oprócz obiek tów ist nie ją w bi blio te ce Exce la ko lek cje – czy li gru py jed-na ko wych obiek tów . Excel za wie ra ko lek cję otwar tych sko ro szy tów (Work­bo­oks), sko ro szyt za wie ra ko lek cję ar ku szy (Work­she­ets), po je dyn czy ar kusz mo że za wie rać ko lek cję wier szy (Rows), ko lumn (Co­lumns), ta bel prze staw nych (Pi­vot­Ta­bles) lub wy kre sów (Char­tO­bjects) . Re gu łą jest, że na zwy ko lek cji za zwy czaj koń czą się li te -

rą s (licz ba mno ga, bo mó wi my o gru pie obiek tów) .Do po szcze gól ne go ele men tu ko lek cji (obiek tu) mo że my się od wo łać na dwa spo so by:

● po przez po da nie w pa ra me trze in dek su (licz ba cał ko wi ta więk sza od 0, wska zu ją ca na nu mer ele-men tu w ko lek cji, np . Work­she­ets(1).Se­lect,

● po przez od wo ła nie do uni ka to we go klu cza (naj czę ściej jest nim na zwa obiek tu), np . Work­she­ets(”Bu­d-żet”).Se­lect .Czę sto w ko dzie wy ko rzy stu je się wła sne ko lek cje prze cho wu ją ce obiek ty lub zwy kłe war to ści . Aby

za ini cjo wać no wą ko lek cję, wy star czą dwie li nij ki ko du:

Do da nie do ko lek cji no wej war to ści jest ba nal nie pro ste (me to da Add):

Pa ra metr Item jest obo wiąz ko wy . Jest to wy ra że nie do wol ne go ty pu (mo że być nim obiekt lub np . zwy kły tekst) . Pa ra metr Key jest opcjo nal ny, ale je go po da nie jest za le ca ne . Obo wiąz ko wo mu si to być tekst, a za ra-zem uni kat, po któ rym bę dzie moż na się od wo łać do kon kret ne go ele men tu ko lek cji . W na szym przy pad ku klu czem są ini cja ły pra cow ni ka wraz z przy po rząd ko wa ną mu kon kret ną licz bą . Każ dy pra cow nik po sia da swój uni ka to wy kod – w fir mie pra cu je dwóch Ja­nów­Ko­wal­skich (każ dy z nich ma osob ny kod) . W ten spo sób do pra cow ni ka mo że my się od wo łać po przez in struk cję col­Pra­cow­ni­cy(”JK1”). W przy pad ku nie poda-nia klu cza od wo ła nie jest moż li we tyl ko za po mo cą in dek su, np . col­Pra­cow­ni­cy(1).

problem

rozwiązanie

28

Wi­dok­1 po ka zu je krót ką ta be lę, w któ rej nie któ re da ne się po wta rza ją . Na szym ce lem jest stwo rze nie uni ka to wej li sty pra cow ni ków . Jest ich w su mie sze-ściu . Ma kro za miesz czo ne pod zdję ciem po ka zu je, w ja ki spo sób uzy skać ta ką li stę za rów no za po mo cą ko lek cji, jak i słow ni ka .

W przy pad ku ko lek cji ma kro roz po czy na się od in truk cji On­Er­ror­Re­su­me­Next . Ma to na ce lu spe-cjal ne po mi nię cie błę dów, któ re wy stą pią w trak cie pró by do da nia du pli ka tu . Ma kro w pę tli pró bu je do dać każ de go pra cow ni ka do uni ka to wej ko lek cji – na pod sta wie je go ko du . Je że li ko du nie ma w ko lek cji, ope ra cja zo sta je za koń czo na po wo dze-niem . Je że li kod jest już na to miast w ko lek cji – ma kro ge ne ru je błąd (pra cow nik nie mo że zo stać do da ny), jed nak jest on po mi nię ty ze wzglę du na li nię On­Er­ror­Re­su­me­Next . W ja ki spo sób ma kro

od czy tu je, czy kon kret ny pra cow nik znaj du je się w ko lek cji? Wła śnie za po mo cą klu cza (Key), któ ry mu si być uni ka tem za pi sa nym obo wiąz ko wo ja ko tekst . W na szym przy pad ku klu czem jest uni ka to wy kod pra cow ni ka – pró ba do da nia do ko lek cji pra cow ni ka z ta kim sa mym klu czem (ko dem) koń czy się wy stą pie niem błę du .

Znacz nie pro ściej wy glą da to w przy pad ku słow ni ka . Ma kro za po mo cą me to dy Exi­sts spraw dza, czy kod znaj du je się w słow ni ku . Je że li już ist nie je – pę tla prze cho dzi do na stęp ne go ko du pra cow ni ka . Je że li nie ist nie je, jest on do da wa ny do słow ni ka .

Wi dok 1

kwiecień 2012 29

Słow nik po sia da kil ka bar dzo wy raź nych prze wag nad ko lek cją, m .in .:1 . Ist nie nie me to dy Exi­sts po zwa la w bar dzo pro sty i szyb ki spo sób spraw dzić, czy da na war tość znaj-

du je się w słow ni ku . W przy pad ku ko lek cji ko niecz ne jest przej ście w pę tli po wszyst kich ele men tach .2 . W przy pad ku ko lek cji je ste śmy w sta nie otrzy mać tyl ko war to ści ele men tów (na zwi ska pra cow ni ków)

– klu czy (ko dów) już nie . W przy pad ku słow ni ka ma my do stęp za rów no do war to ści, jak i klu czy . Po niż szy kod wy świe tla wszyst kie ele men ty wraz z klu cza mi:

3 . W przy pad ku słow ni ka za rów no klu cze (ko dy), jak i ele men ty (na zwi ska) za pi sa ne są do osob nych ta blic jed no wy mia ro wych . Dzię ki te mu mo że my w ła twy spo sób sczy tać je do ko mó rek ar ku sza lub kon tro lek na for mu la rzu .

4 . W przy pad ku słow ni ka moż na w pro sty spo sób zmie nić war tość klu cza:

W przy pad ku ko lek cji na le ża ło by usu nąć ele ment i do dać go po now nie, z no wym klu czem .

30

5 . W przy pad ku słow ni ka nie je ste śmy ogra ni cze ni do wpro wa dza nia klu czy ja ko tek stu . Klucz jest obo wiąz ko wy i mu si być uni ka tem, ale typ da nych jest bez zna cze nia (tekst, licz ba, war tość lo gicz na) .

6 . Słow nik po sia da me to dę Re­mo­ve­All po zwa la ją cą na wy czysz cze nie ca łej za war to ści słow ni ka jed nym po le ce niem . W przy pad ku ko lek cji na le ża ło by usu nąć ele men ty w pę tli .

7 . Słow nik po sia da wła ści wość Com­pa­re­Mo­de, któ ra po zwa la na wpro wa dze nie roz róż nie nia wiel ko ści li ter dla klu czy, tj . moż na do dać do słow ni ka pra cow ni ka z ko dem ”JK1”,­jak rów nież in ne go pra-cow ni ka z ko dem ”jk1” .

kwiecień 2012 31

Okno dia lo go we

Czę sto wi dzę w ma krach opcję, któ ra wy świe tla okien ko dia lo go we, w któ rym użyt kow nik jest po pro szo ny o za zna cze nie ka ta lo gu lub kil ku pli ków. W ja ki spo sób naj pro ściej wy świe-tlić ta kie okien ko? Czy ła two jest po brać np. peł ne ścież ki do tych pli ków? W koń cu, jak zmu sić użyt kow ni ka do te go, aby wy brał np. dwa pli ki?

VBA ofe ru je kil ka spo so bów na wy świe tle nie okien ka dia lo go we go . Do naj po pu-lar niej szych roz wią zań na le żą dwa:1 . Me to da Ge­tO­pen­Fi­le­na­me­obiek tu Ap pli ca tion .2 . Obiekt Fi­le­Dia­log bę dą cy wła ści wo ścią obiek tu Ap pli ca tion .

Zde cy do wa nie pre fe ru ję to dru gie roz wią za nie, po nie waż da je du żo wię cej moż li-wo ści zwią za nych z wy świe tle niem okna dia lo go we go . Aby wy ko nać ta kie za da nie, moż na się po słu żyć wła sną funk cją VBA . W przy pad ku gdy użyt kow nik wy bie rze pra-wi dło wo dwa pli ki – funk cja zwró ci w wy ni ku ich ad re sy . W sy tu acji gdy użyt kow nik

nie do ko na wy bo ru lub za zna czy in ną licz bę pli ków niż 2 – funk cja zwró ci w wy ni ku pu sty ciąg .Ma jąc na pi sa ną ta ką funk cję, mo że my póź niej w ma krze głów nym spraw dzić, czy ta funk cja zwra ca

w wy ni ku pu sty ciąg – je że li tak, ozna cza to, że użyt kow nik nie za zna czył pra wi dło wo dwóch pli ków – je że li nie, ozna cza to, że wy ni kiem jest ta bli ca dwóch ad re sów (użyt kow nik za cho wał się wła ś-ci wie) . Kod z funk cją i ko men ta rza mi znaj du je się po ni żej:

problem

rozwiązanie

32

Wi­dok­ 1­ przedstawia okien ko dia lo go we, któ re zo sta nie po ka za ne użyt-kow ni ko wi . Wy mo giem dzia ła nia funk cji jest ist nie-nie ka ta lo gu „D:\Ra­por­ty”.

Wi dok 1

kwiecień 2012 33

Operacje na plikach i folderach

Za sta na wiam się, czy VBA po sia da po le ce nia, in struk cje, któ re po zwa la ją do ko ny wać ope-ra cji na pli kach i ka ta lo gach. Przede wszyst kim mam na my śli ich ko pio wa nie, wy ci na nie, zmia nę na zwy, utwo rze nie od no wa, wy ka so wa nie. In te re su je mnie zwłasz cza ta ostat nia ope ra cja, bo chciał bym się do wie dzieć, czy ma kra fak tycz nie mo gą być nie bez piecz ne, czy mo gą do ko ny wać zmian np. w sys te mie ope ra cyj nym Win dows?

W Exce lu z po zio mu VBA jak naj bar dziej moż na do ko ny wać ope ra cji na pli-kach i fol de rach . Moż na to ro bić na dwa spo so by: a) po przez od wo ła nie do bi blio te ki Mi­cro­soft­Scrip­ting­Run­ti­me, któ ra za wie ra do sko-

na ły obiekt Fi­le­Sys­te­m­O­bject,­b) po przez po je dyn cze funk cje VBA . 1.­Ko­pio­wa­nie­pli­ków­i­fol­de­rów

Po je dyn czy plik moż na sko pio wać za po mo cą me to dy Fi­le­Co­py . ● Fi­le­Co­py­‘’C:\Moj­Plik.xls’’,­‘’D:\Moj­Plik.xls’’

Po wyż sza in struk cja ko piu je plik Exce lo wy bez po śred nio z dys ku C na dysk D . Pierw szy ar gu ment okre śla ak tu al ną ścież kę do pli ku, dru gi ar gu ment – ścież kę do ce lo wą . ● ­Fi­le­Co­py­‘’C:\Moj­Plik.xls’’,­‘’D:\Moj­Plik­-KO­PIA.xls’’

Ta in struk cja ko piu je plik Exce lo wy bez po śred nio z dys ku C na dysk D pod zmie nio ną na zwą Moj­Plik­-KO­PIA.xls.­

VBA nie po sia da nie ste ty, po dob nej me to dy do ko pio wa nia fol de rów . Aby sko pio wać fol der w in ne miej sce, na le ży sko rzy stać z me to dy Co­py­Fol­der obiek tu Fi­le­Sys­te­mO­bject.­

Je śli chce my sko pio wać więk szą licz bę pli ków, tak że mu si my od wo łać się do te go obiek tu . 2.­Prze­no­sze­nie­pli­ków­i­fol­de­rów

Po je dyn czy plik moż na prze nieść za po mo cą funk cji Na me . ● Na­me­‘’C:\Moj­Plik.xls’’­As­‘’D:\Moj­Plik.xls’’

Po dob nie jak w przy pad ku ko pio wa nia, plik moż na prze nieść zmie nia jąc mu na zwę nie ja ko „w lo cie” . Ana lo gicz nie jak w przy pad ku ko pio wa nia, VBA nie po sia da me to dy, któ ra po zwo li ła by w pro sty spo sób prze nieść kil ka pli ków lub ca ły ka ta log w in ne miej sce (w tym przy pad ku na le-ża ło by po słu żyć się me to dą Mo­ve­Fol­der­obiek tu Fi­le­Sys­te­mO­bject) . 3.­Zmia­na­na­zwy­pli­ku­lub­fol­de­ru

Na zwę po je dyn cze go pli ku moż na zmie nić tak że, ko rzy sta jąc z funk cji Na­me.­● Na­me­‘’C:\Moj­Plik.xls’’­As­‘’C:\Moj­Plik­-new.xls’’

VBA nie po sia da me to dy, któ ra po zwo li ła by w pro sty spo sób prze nieść kil ka pli ków lub ca ły ka ta log w in ne miej sce (w tym przy pad ku na le ża ło by po słu żyć się me to dą Mo­ve­Fol­der­obiek tu Fi­le­Sys­te­mO­bject) . Zmia na na zwy pli ku dzia ła w ta ki sam spo sób jak je go prze nie sie nie w in ne miej sce pod in ną na zwą . 4.­Utwo­rze­nie­no­we­go­pli­ku­i­ka­ta­lo­gu

W przy pad ku utwo rze nia no we go pli ku wszyst ko za le ży od te go, ja ki kon kret nie typ nas in te re-su je . Pli ki tek sto we moż na two rzyć bez po śred nio za po mo cą po le ce nia Open­– np . gdy chce my za pi-sać da ne z ko mó rek ar ku sza wła śnie do pli ku tek sto we go (w ta kiej sy tu acji po da je się peł ną ścież kę do pli ku – je że li plik ist nie je, ma kro do pi su je da ne do już ist nie ją cych lub ka su je wcze śniej sze da ne – je że li plik nie ist nie je, ma kro two rzy go au to ma tycz nie) . W przy pad ku do ku men tów MS Of fi ce (sko ro szy ty Exce la, do ku men ty Wor da, pre zen ta cje Po wer Po in ta, ba zy Ac ces sa) na le ży naj pierw pro-gra mo wo do dać no wy plik, a na stęp nie za pi sać go pod kon kret ną na zwą na dys ku .

rozwiązanie

problem

34

Je że li chce my na to miast utwo rzyć no wy fol der, mo że my za sto so wać po le ce nie MkDir . ● ­MkDir­‘’C:\Moj­No­wy­Fol­der’’

Na le ży się upew nić, że ka ta log o ta kiej ścież ce nie ist nie je – w prze ciw nym ra zie ma kro wy wo ła błąd . 5.­Ka­so­wa­nie­pli­ków­i­fol­de­rów

Ka so wa nie da nych z po zio mu VBA jest bar dzo pro ste i do ty czy to za rów no pli ków, jak i fol de rów . Oby dwie ope ra cje mo że my wy ko nać bez od wo ły wa nia się do bi blio te ki Mi­cro­soft­Scrip­ting­Run­ti­me . Aby ska so wać po je dyn czy plik lub więk szą ilość pli ków, na le ży użyć in struk cji Kill, zaś w przy pad ku ka ta lo gu moż na wy ko rzy stać in struk cję RmDir . ● Kill­‘’C:\Moj­Plik.xls’’

Po wyż sza in struk cja ka su je sko ro szyt Moj­Plik.­xls znaj du ją cy się bez po śred nio na dys ku C . ●­­Kill­‘’C:\*.xls’’

Ta in struk cja ka su je wszyst kie sko ro szy ty z roz sze rze niem *.xls­znaj du ją ce się bez po śred nio na dys-ku C . ● ­Kill­‘’C:\*.xl*’’

Ta in struk cja ka su je wszyst kie pli ki Exce la znaj du ją ce się bez po śred nio na dys ku C, a któ rych roz-sze rze nie za czy na się od se kwen cji zna ków *.xl* (sko ro szy ty, sza blo ny, do dat ki itd .) . ● ­Kill­‘’C:\*.*’’

Ta in struk cja ka su je wszyst kie pli ki znaj du ją ce się bez po śred nio na dys ku C .● ­RmDir­‘’C:\Moj­Fol­der’’

Ta in struk cja ka su je ka ta log Moj­Fol­der znaj du ją cy się bez po śred nio na dys ku C (Ka ta log mu si być pu sty!) . Aby ska so wać ca ły fol der wraz z za war to ścią, na le ża ło by naj pierw usu nąć w nim wszyst kie pli ki, a na stęp nie ska so wać już pu sty ka ta log . � n

kwiecień 2012 35

problem

Kwo ty skła da ją ce się na da ną su mę

W ja ki spo sób zna leźć kwo ty, któ re skła da ją się na da ną su mę? Przy kła do wo, w ko lum nie A mam li stę wpłat (tyl ko war to ści licz bo we). Chcia ła bym wie dzieć, któ re z tych wpłat na le ży zsu mo wać, aby w wy ni ku otrzy mać kon kret ną su mę, np. 9500 zł? Co w sy tu acji, gdy żad na kom bi na cja nie da je ta kiej su my?

W Exce lu ta kie za da nia moż na z po wo dze niem wy ko ny wać za po mo cą do dat ku So lver . Wi­dok­1 po ka zu je przy kła do we da ne . W ko lum nie A znaj du ją się wpła ty (licz by), w ko lum nie B ilość dla każ- dej z nich (do myśl-nie są to ze ra) . Wi­dok­ 2 po ka zu je na to miast, w ja ki

spo sób usta wić pa ra me try So lve ra, aby otrzy mać w wy ni ku po żą da ną su mę . ● Klu czo wa jest for mu ła za war ta

w ko mór ce D2: =SU­MA.ILO­CZY-NÓW(Wpła­ty;Ilość).­

● Ko mór ką ce lu jest ko mór ka D2 . Po nie-waż chce my uzy skać w wy ni ku kon-kret ną kwo tę 9500 zł, mu si my za zna-czyć trze ci przy cisk opcji War­tość i wpi sać tam do ce lo wą su mę .

● Zmien ny mi w na szym przy kła dzie są ilo ści . Że by upro ścić nie co wa run ki za da nia, za łóż my, że każ da wpła ta

rozwiązanie

Widok 1

Widok 2

36

mo że być wy ko rzy sta na wię cej niż je den raz . ● Ma my dwa wa run ki ogra ni cza ją ce .

Pierw szy do ty czy te go, że ilość mu si być licz bą cał ko wi tą – nie mo że być ułam kiem . Dru gi wa ru nek okre śla, że ilość nie mo że być licz bą ujem ną – mu si być licz bą więk szą od ze ra lub ze rem . Gdy by śmy chcie li każ dą z wpłat wy ko rzy stać mak sy mal nie je den raz, mu sie li by śmy jesz cze do dać wa ru nek Ilość<2.­

● Po wpro wa dze niu wa run ków na le ży klik nąć przy cisk Roz­wiąż. Po wy ko na-niu ob li czeń wy świe tlo ny zo sta nie mo nit z in for ma cją od no śnie do te go, czy wpro wa dzo ne zmia ny po win ny zo stać za pi sa ne .

● Jak po ka zu je wi­dok­3, na su mę 9500 zł skła da ją się trzy kwo ty prze mno żo ne przez 2:

3040 zł (1520 zł × 2) + 3120 zł (1560 zł × 2) + 3340 zł (1670 zł × 2) = 9500 zł .

● Po nie waż na szym ce lem jest uzy ska nie kon kret nej su my, mo że się zda rzyć, że żad na z kom bi na-cji nie bę dzie da wać pra wi dło we go wy ni ku . W ta kiej sy tu acji So lver wy świe tli ko mu ni kat o prze kro cze niu li mi tu cza su lub błę dzie (cza sa mi wy nik jest pra wi dło wy, ale kosz tem zmian w wa run kach ogra ni cza ją cych) . n

Widok 3

kwiecień 2012 37

problem

Sprawdzanie poprawności– lista zawężająca

In te re su je mnie za gad nie nie two rze nia li sty ka ska do wej (za wę ża ją cej) po przez opcję spraw-dza nia po praw no ści. Dla przy kła du, w jed nej ko mór ce chciał bym mieć moż li wość wy bo ru na zwy pań stwa, w dru giej zaś ko mór ce po win na po ja wić się li sta miast znaj du ją cych się tyl ko w tym kra ju. Wiem, że ta kie li sty moż na two rzyć przy uży ciu nazw, ale jest to bar dzo uciąż li we. Czy jest ja kiś in ny spo sób, aby zro bić to za po mo cą for muł? Chciał bym też, aby li sta sa ma się ak tu ali zo wa ła, tzn. aby oby dwa po la by ły za wsze ze so bą zsyn chro ni zo wa ne – w mo men cie gdy wy bio rę in ny kraj, od ra zu zmie nia ła by mi się war tość i li sta w ko mór ce obok. Czy coś ta kie go jest moż li we do wy ko na nia?

Two rze nie list za wę ża ją cych jest jed nym z cie kaw szych za gad nień w Exce lu . Wbrew po zo rom ta kie roz wią za nie sto su je się dość czę sto, z lep szym lub gor szym skut kiem . Wi­dok­1 po ka zu je roz wią za nie ta kie go za da nia . W pierw szej ko lum nie znaj du ją się pań stwa, w dru giej mia sta . Aby upro ścić przy kład, wpro wa dzi łem kil ka nazw: ● Kraj – od no si się do sta łe go za kre su państw w ko lum nie A, ● Mia­sto – od no si się do sta łe go za kre su miast w ko lum nie B, ● Kra­je_Uni­ka­ty – od no si się do uni ka to wej li sty państw w ko lum nie G, ● ­Kraj_Wy­bor­– od no si się do ko mór ki D2,

● Mia­sta_Dla_Kra­ju – dy na micz ny za kres ko mó rek od no szą cy się do wszyst kich miast wy bra ne go kra-ju np . je że li w ko mór ce Kraj_Wy­bor­wy bie rze my Cze­chy, to za kre sem dla na zwy Mia­sta_Dla_Kra­ju­bę dzie B9:B10 .Ko mór ki D2 i E2 za wie ra ją for mu ły w me cha ni zmie spraw dza nia po praw no ści (li sta):

rozwiązanie

Widok 1

38

● ­D2 – na pierw szy rzut oka wy da je się, że wy star czy ła by tu taj pro sta for mu ła =Kra­je_Uni­ka­ty . W ta kiej sy tu acji mie li by śmy w ko mór ce D2 za wsze li stę wszyst kich kra jów . Ta kie roz wią za nie stwa rza jed nak pe wien pro-blem . W przy pad ku zmia ny na zwy kra ju, np . z Pol­ska na Niem­cy, w ko mór ce obok mie li by-śmy na dal na zwę pierw sze go mia sta… z Pol-ski . Mi mo że li sta zo sta ła by pod mie nio na na mia sta nie miec kie, to do pie ro klik nię cie na ko mór ce i wy bór z li sty od po wied nie go mia sta nie miec kie go zmie nił by war tość ko mór ki E2 . Roz wią za niem mo że być uży cie bar dzo

spryt nej for mu ły . Je że li ko mór ka z mia stem nie jest pu sta, wów czas ogra ni cza my wy bór kra ju – tyl ko do te go, w któ rym znaj du je się mia sto (wi­dok­2) . Wy ka so wa nie mia sta po wo du je na to miast wy świe tle nie ca łej li sty państw (wi­dok­3) . ● ­E2 – Wy bór od po wied nie go kra ju da je moż li wość wy świe tle nia li sty za wę ża ją cej dla miast (wi­dok­4) .

n

Widok 2 Widok 3

Widok 4

kwiecień 2012 39

problem

Książ ki do na uki VBA

Exce la znam dość do brze od stro ny funk cji i wbu do wa nych w nim na rzę dzi. Kom plet nie jed-nak nie znam się na ma krach. Ja ką książ kę ku pić na po czą tek, aby nie by ła zbyt trud na? Czy po wi nie nem ku pić książ kę dla Exce la 2010 czy jed nak dla wer sji wcze śniej szej?

Rze czy wi ście, na ryn ku do stęp nych jest co raz wię cej ksią żek po świę co nych pro-gra mo wa niu w VBA dla Mi cro soft Excel . Mo im zda niem na po czą tek le piej ku pić książ kę prze zna czo ną dla Exce la w wer sji 2003, po nie waż bar dzo du ża część użyt-kow ni ków wciąż ko rzy sta z tej wer sji pro gra mu . Uwa żam, że na sam po czą tek na le ży wy brać książ kę, któ ra tłu ma czy pod sta wy ję zy ka, je go spe cy fi kę, a do pie ro póź niej za opa trzyć się w droż szą, bar dziej ob szer ną li te ra tu rę . Opi szę kil ka ksią żek war tych uwa gi, za czy na jąc od naj prost szych, po naj bar dziej za awan so wa ne .

● �Agniesz�ka�Snar�ska�–�„Ma�kro�po�le�ce�nia�w�Exce�lu”�–�ce�na�ok.�35�złDo bra książ ka wpro wa dza ją ca w świat pro gra mo wa nia VBA, na pi sa na z my ślą o Exce lu 2003 i wer-

sjach wcze śniej szych . Jej naj więk szym atu tem jest bar dzo du ża ilość prak tycz nych, in te re su ją cych przy-kła dów – już roz wią za nych, jak też do sa mo dziel ne go wy ko na nia . Książ ka oma wia pod sta wy pro gra-mo wa nia: re je stra tor makr, ty py zmien nych, ko mu ni ka cję z użyt kow ni kiem, pę tle, ele men ty ste ru ją ce ar ku sza, pod sta wo we obiek ty Exce la i na rzę dzie MS Qu ery .

● �Da�wid�Haw�ley,�Ra�ina�Haw�ley�–�„100�spo�so�bów�na�Excel”�–�ce�na�ok.�55�złNie jest to książ ka po świę co na na uce pi sa nia makr, ale za wie ra licz ne przy kła dy, w któ rych wy ko-

rzy stu je kod . Jest ona prze zna czo na dla za awan so wa nych użyt kow ni ków Exce la i ta kich, któ rzy po sia-da ją już pod sta wo wą wie dzę na te mat pro gra mo wa nia . Książ ka jest tak na praw dę zbio rem pra wie 140 po rad (li czy po nad 350 stron) po dzie lo nych na kil ka ka te go rii (sko ro szy ty i ar ku sze, na rzę dzia, na zwy, ta be le prze staw ne, wy kre sy, for mu ły, ma kra, współ pra ca z in ny mi pro duk ta mi MS Of fi ce) . Wie dza do bra na jest se lek tyw nie – każ da po ra da zna czą co róż ni się od po przed niej .

● �John�Wal�ken�bach�–�„Excel�2003.�Pro�gra�mo�wa�nie�w�VBA.�Va�de�me�cum�pro�fe�sjo�na�li�sty”�–�ce�na�ok.�80�złKsiąż ka, któ rą śmia ło moż na na zwać „bi blią”, po nie waż to wła śnie od niej więk szość de we lo pe rów

za czy na ła swo ją przy go dę z VBA . Spo śród wszyst kich po zy cji do stęp nych na ryn ku jest ona naj czę ściej po le ca na po cząt ku ją cym adep tom, głów nie ze wzglę du na to, że jest bar dzo kom plet na, zwar ta te ma-tycz nie i dość przej rzy ście na pi sa na . Co cie ka we, po ja wia się tro chę za rzu tów wo bec tej książ ki oraz sa me go au to ra, gu ru Exce la – Joh na Wal ken ba cha . Do ty czą one przede wszyst kim po wta rzal no ści ma te-ria łu w wie lu książ kach, wo do lej stwa, pew ne go za dę cia w sty lu wy po wie dzi . Mi mo tych obie go wych opi nii książ ka ma kil ka nie pod wa żal nych za let . Po pierw sze, kod na pi sa ny przez au to ra jest bar dzo czy tel ny (nie jest nad mia ro wy), ła two przy swa jal ny i do brze udo ku men to wa ny . Po dru gie, książ ka jest bar dzo spój na, a każ dy roz dział za wie ra licz ne, war to ścio we przy kła dy . Po trze cie, oprócz roz dzia łów książ ka za wie ra bar dzo war to ścio we do dat ki, m . in . ob szer ne FAQ do ty czą ce VBA i nu me ry wszyst kich moż li wych błę dów wraz z do kład nym opi sem przy czyn wy stą pie nia . Po zy cja zde cy do wa nie obo wiąz-ko wa w bi blio te ce każ de go pro gra mi sty VBA .

● �Bill�Je�len,�Tra�cy�Syr�stad�–�„Excel�2007.�Ję�zyk�VBA�i�ma�kra”�–�ce�na�ok.�90�złŚwiet na książ ka, któ ra by wa czę sto sto so wa na ja ko al ter na ty wa lub uzu peł nie nie po zy cji Wal ken ba-

cha . Jest na pi sa na bar dzo pro stym, zro zu mia łym ję zy kiem – za wie ra prak tycz ne, am bit ne przy kła dy, któ rych próż no szu kać w in nych opra co wa niach . Bar dzo do bry wstęp, któ ry w pro sty spo sób opi su je me cha ni zmy dzia ła nia ję zy ka VBA, opcje de bug go wa nia ko du, a tak że nie do sko na ło ści re je stra to ra

rozwiązanie

40

makr . Książ ka za wie ra kil ka wy śmie ni tych roz dzia łów do ty czą cych m .in . for muł w sty lu W1K1, pro-gra mo wa nia zda rzeń, fil tra za awan so wa ne go, ta bel prze staw nych, for ma to wa nia wa run ko we go i wy kre-sów w no wej wer sji Exce la, a tak że pod staw współ pra cy z in ny mi pro gra ma mi Of fi ce (Word, Ac cess) . Mo że my tak że w niej zna leźć roz dzia ły, w któ rych wy ko rzy sta ne zo sta ły ory gi nal ne ma kra in ter nau tów . Książ ka za wie ra po nad 700 stron – jej wa dą jest brak twar dej okład ki i sła be wy od ręb nie nie sek cji ko du, przez co zle wa się on tro chę ze zwy kłym tek stem . Za war tość me ry to rycz na i wie dza pły ną ca z tej książ ki w zu peł no ści jed nak po kry wa ją te nie do stat ki .

● �Ste�ven�Ro�man�–�„Excel�2000.�Ma�kro�de�fi�ni�cje”Książ ka bar dzo trud no do stęp na na ryn ku, ra czej je dy nie na au kcji in ter ne to wej . Ja sam na by łem

ją w po dob ny spo sób i szcze rze mó wiąc, je stem jej wiel kim fa nem . Książ ka ta ma kon cep cję nie co in ną od resz ty ksią żek po dej mu ją cych za gad nie nie pi sa nia makr w Exce lu . Część pierw sza oma wia śro do wi sko VBA (edy tor, opcje de bug go wa nia) . Za wie ra ona m .in . świet ny, krót ki tekst opi su ją cy ro dza je błę dów, ja kie moż na na po tkać pi sząc kod . Część dru ga to opis zmien nych, wbu do wa nych funk cji i in struk cji . Roz dzia ły te zaj mu ją ok . 100 stron . Część trze cia tej książ ki (ok . 350 stron) to już bar dzo do kład ny opis naj waż niej szych obiek tów w Exce lu, ich wła ści wo ści (w tym tak że sta łych enu me rycz nych) i me tod . Książ ka przez swo ją trud ną do stęp ność jest ra czej ma ło po pu lar na, jed nak sta no wi świet ne kom pen dium wie dzy dla osób już nie co za zna jo mio nych z te ma tem . Ra czej nie dla po cząt ku ją cych .

● �John�Gre�en,�Ste�phen�Bul�len,�Rob�Bo�vey,�Mi�cha�el�Ale�xan�der�–�„Excel�2007.�Pro�gra�mo�wa�nie�w�VBA”�–�ce�na�ok.�170�złKsiąż ka na pi sa na przez czte ry oso by, z cha rak te ry stycz ną czer wo ną, twar dą okład ką, jest chy ba naj-

bar dziej za awan so wa ną i pro fe sjo nal ną książ ką dla do świad czo nych pro gra mi stów VBA . Jest bar dzo sta ran nie wy ko na na i wy koń czo na . Wszyst kie in for ma cje są bar dzo czy tel ne, kod za wsze znaj du je się na sza rym tle . Książ ka jest nie zwy kle ob szer na – włącz nie z do dat ka mi zaj mu je po nad 1200 stron . Jest dość dro ga, jed nak jej za kup bar dzo szyb ko się amor ty zu je . Już pierw szy roz dział, za ty tu ło wa ny Ele-men tarz za wie ra po nad 60 stron waż nych i nie ba nal nych in for ma cji do ty czą cych m .in . two rze nia wła-snych funk cji, mo de lu obiek to we go Exce la, spo so bów wy wo ły wa nia pro ce dur, sto so wa nia na wia sów, pę tli i ta blic . Z te go wzglę du nie jest to książ ka dla no wi cju szy ani dla po cząt ku ją cych pro gra mi stów, ale dla osób z co naj mniej do brą zna jo mo ścią te ma tu . Dzię ki tej książ ce do wie my się m .in ., jak prze-twa rzać pli ki tek sto we, jak ko rzy stać z obiek tu ADO do im por to wa nia in for ma cji z za mknię tych sko ro-szy tów i baz da nych Ac cess po przez bu do wa nie za py tań SQL, jak pra co wać z pli ka mi XML, jak two rzyć od pod staw wstąż ki i for mu la rze . Książ ka po ru sza tak że te mat two rze nia do dat ków au to ma ty za cji, do dat ków COM, wła snych bi blio tek . Dzię ki niej po zna my tak że struk tu rę edy to ra Vi su al Ba sic (VBE) i roz ma ite spo so by na wy ko rzy sta nie funk cji Win dows API . Zwień cze niem tej wy bit nej książ ki są trzy do dat ki . Pierw szy z nich oma wia ca ły mo del obiek to wy Exce la (po nad 330 stron!), dru gi do ty czy VBE, na to miast trze ci oma wia wspól ne obiek ty wszyst kich apli ka cji Of fi ce (np . pa ski na rzę dzi) .

●��John�Gre�en,�Ste�phen�Bul�len,�Rob�Bo�vey�–�„Excel�2003.�Pro�gra�mo�wa�nie�dla�pro�fe�sjo�na�li�stów”�–�ce�na�ok.�120�złKsiąż ka, jak wska zu je jej ty tuł, prze zna czo na dla pro fe sjo nal nych pro gra mi stów VBA . Jest to pierw-

sza po zy cja na tym ryn ku, któ ra nie opi su je po szcze gól nych za gad nień krok po kro ku, ale po ka zu je, w ja ki spo sób za pro jek to wać i opro gra mo wać od pod staw apli ka cję dyk ta tor ską . Książ ka po sia da kil ka re we la cyj nych roz dzia łów, m .in . naj lep sze prak ty ki pro gra mo wa nia w Exce lu i VBA (w tym kon wen cja nazw zmien nych), pro jek to wa nie ar ku sza (m .in . me cha nizm we ry fi ka cji po praw no ści da nych czy uży wa nie sty li i sza blo nów), uży wa nie mo du łów klas, ob słu ga błę dów, de bug go wa nie ko dów, opty ma li za cja wy daj no ści VBA . Koń co we roz dzia ły po świę co ne są już za gad nie niem, któ re zde cy do wa nie wy kra cza ją po za zwy kłą na ukę Exce la i VBA (bi blio te ki dll, funk cje API C, do dat ki w Vi su al Ba sic 6, ko rzy sta nie z VB .NET i Vi su al Stu dio To ols for Of fi ce) . Pu bli ka cja trud na w od bio-rze (kod wy da je się nad mia ro wy), ale zde cy do wa nie war ta uwa gi ze wzglę du na wy mie nio ne prze ze mnie roz dzia ły . n

kwiecień 2012 41

Ro dza je błę dów w VBA

Czy ist nie ją ja kieś kon kret ne ro dza je błę dów po peł nia nych przez oso by pro gra mu ją ce w VBA? Do pie ro po zna ję taj ni ki pi sa nia makr i czę sto w trak cie pra cy wy ska ku jąmi róż ne okien ka z ko mu ni ka ta mi błę dów. Czym się one mię dzy so bą róż nią? Czy moż na je ja koś po gru po wać?

problem

W pro gra mie utwo rzo nym w Vi su al Ba sic mo gą zda rzyć się trzy ty py błę dów: błę dy kom pi la cji (w tym skład ni), błę dy pod czas dzia ła nia pro gra mu i błę dy lo gicz-ne . Nie któ rzy od dzie la ją od sie bie błę dy skład ni (cza su pro jek to wa nia) od błę dów kom pi la cji . Wi dok 1 po ka zu je przy kła dy ta kich błę dów . 1 . Błę­dy­skład­ni — są to, ina czej mó wiąc, błę dy gra ma tycz ne oraz in ter punk cyj ne .

Są to mię dzy in ny mi bra ku ją ce na wia sy lub nie wła ści wa licz ba ar gu men tów prze-ka za nych do funk cji . W na szym przy kła dzie błąd skład ni do ty czy po dwój ne go wpi sa nia zna ku rów no ści, co skut ku je po ja wie niem się okien ka z ko mu ni ka tem o błę dzie kom pi la cji (wi­dok­2) .

2 . Błę­dy­kom­pi­la­cji — wy stę pu ją, gdy kod jest nie po praw nie skon stru owa ny . Mo gło się zda rzyć, że użyt kow nik za po mniał uzu peł nić da ną in struk cję in ną, wy stę pu ją cą z nią w pa rze (np . If­i­End­If­lub For­i Next) al bo omył ko wo na ru szył w ko dzie za sa dy ję zy ka Mi cro soft Vi su al Ba sic (np . błąd li te ro wy w sło wie, bra ku ją cy se pa ra tor, nie zgod ność ty pów) . W na szym przy kła dzie pró bu je my przy-pi sać zmien nej obiek to wej ty pu Work bo ok war tość tek sto wą (na zwę sko ro szy tu), a po win ni śmy jej przy pi sać kon kret ny plik .

3 . Błę­dy­cza­su­wy­ko­na­nia­— wy stę pu ją po uru cho mie niu apli ka cji (kom pi la cja prze bie gła pra wi dło wo) . Przy kła da mi błę dów wy ko ny wa nia są nie do pusz czal ne ope ra cje, ta kie jak dzie le nie przez ze ro lub za pi sy wa nie do pli ku, któ ry nie ist nie je . W na szym przy kła dzie ma my do czy nie nia wła śnie z tym dru gim przy pad kiem . W ko dzie ma kra po da je my ad res do pli ku, któ ry tak na praw dę nie ist nie je . Po peł ni li śmy li te rów kę i za miast sło wa Bu­dżet wpi sa li śmy Du­dżet . Wi dok 3 po ka zu je ta ki błąd .

rozwiązanie

Widok 1 Widok 2

42

Widok 3

4 . Błę dy lo gicz ne — są to błę dy ludz kie — po mył ki pro gra mi sty, któ re po wo du ją, że kod pro gra mu ge ne-ru je złe wy ni ki . Więk szość wy sił ków przy usu wa niu błę dów kon cen tru je się na znaj dy wa niu błę dów lo gicz nych wpro wa dzo nych przez pro gra mi stę . Błąd lo gicz ny jest czę sto naj trud niej szy do zlo ka li zo wa nia i po pra wie nia, po nie waż z punk tu wi dze-nia Exce la ża den błąd nie wy stą pił . Excel nie ma moż li wo ści spraw dze nia za mia ru, ja ki chcie li śmy osią gnąć (w związ ku z tym błę dy lo gicz ne nie są błę da mi cza su wy ko na nia w tra dy cyj nym te go sło wa zna cze niu, mi mo że wy stę pu ją pod czas dzia ła nia pro gra mu) . W na szym przy kła dzie użyt kow nik pra wi dło wo od wo łał się w zmien nej obiek to wej do ak tyw ne go pli-ku, któ ry na zy wa się Ra­por­ty . Błęd nie jed nak ob li czył dłu gość na zwy pli ku i do myśl nie wpi sał 6 za miast 7 . n

kwiecień 2012 43

Wy sy ła nie pli ku Exce la e-m ailem

Za sta na wiam się czy ist nie je moż li wość wy sła nia ak tyw ne go pli ku Exce la e-m ailem do kon kret ne go użyt kow ni ka. Je że li tak, to czy jest też moż li wość wy sła nia po je dyn cze go ar ku sza, kil ku ar ku szy lub za kre su ko mó rek?

Za gad nie nie wy sy ła nia pli ków z po zio mu Exce la jest dość zło żo ne . Ist nie je wie le róż nych spo so bów na wy sła nie e-m aila z po zio mu VBA . Mo że to być me to da Send-Ma­il, na któ rej bę dzie my ba zo wać w tej po ra dzie, me to da Fol­lo­wHy­per­link,­któ ra po zwa la na wy sła nie za awan so wa nej wia do mo ści tek sto wej, lub ma kro, któ re od wo-łu je się do ak tyw nej se sji pro gra mu Outlo ok lub two rzy no wą w przy pad ku, gdy pro gram jest za mknię ty (Mi cro soft Outl lok w prze ci wień stwie do in nych pro gra mów Of fi ce po zwa la na otwar cie tyl ko po je dyn czej in stan cji pro gra mu) .

● Aby wy słać plik, z któ re go uru cha mia ne jest ma kro, moż na sko rzy stać z ta kie go ko du:

Pierw szy ar gu ment me to dy Send­Ma­il okre śla ad res e-m ail od bior cy, dru gi ar gu ment – te mat wia do-mo ści .

● Aby wy słać ak tyw ny w da nej chwi li sko ro szyt, wy star czy drob na ko rek ta w ko dzie:

Dal sze przy kła dy ba zu ją wła śnie na opcji wy sła nia ak tyw ne go sko ro szy tu .

● Aby wy słać po je dyn czy (ak tyw ny) ar kusz, moż na wy ko rzy stać ta ki kod:

problem

rozwiązanie

44

Naj pierw ko pio wa ny jest ak tyw ny ar kusz (me to da Co­py­bez po da nych pa ra me trów ko piu je ar kusz do myśl nie do no we go pli ku, któ ry sta je się ak tyw nym) . Na stęp nie do zmien nej str­Da­ta po bie ra my ak tu-al ną da tę i czas i za pi su je my ak tyw ny plik z ar ku szem pod kon kret ną lo ka li za cją . Na stęp nie wy sy ła my ten plik pod wska za ny ad res, zmie nia my je go tryb na tyl­ko­dla­od­czy­tu i ka su je my z dys ku twar de go .

● Aby wy słać kil ka ar ku szy, moż na wy ko rzy stać ta ki kod:

Jest to kod nie mal iden tycz ny z po przed nim . Je dy na róż ni ca po le ga na tym, że ko piu je my nie po je-dyn czy ar kusz, ale kil ka ar ku szy (w tym ce lu wy ko rzy stu je my ta bli cę Ar­ray) .

● Aby wy słać wy bra ny za kres ko mó rek, na le ży sko pio wać go do no we go sko ro szy tu:

Naj pierw ko pio wa ny jest wła ści wy za kres ko mó rek (uży li śmy na zwy ko do wej ar ku sza) . Na stęp nie do da wa ny jest no wy sko ro szyt z jed nym ar ku szem, do któ re go wkle ja na zo sta je za war tość schow ka . No wy sko ro szyt sta je się ak tyw nym, za pi su je my go pod kon kret ną na zwą, wy sy ła my i ka su je my tak jak w po przed nich przy kła dach .� n

kwiecień 2012 45

w Exceluproblem

rozwiązanie

Osadzenie pliku wideo

Czasami spotykam się z plikami, które zawierają animację wideo. Zastanawiam się, w jaki sposób to działa. Szczerze mówiąc, nie spodziewałem się, że w arkuszu kalkulacyjnym można zamieszczać animacje flash. Czy istnieje sposób na to, aby zamieszczać w nim pliki wideo, np. z serwisu YouTube?

Osadzenie plików wideo i animacji flash w Excelu jest stosunkowo proste i sprowadza się do zastosowania tego samego mechanizmu w obu przypadkach .

Krok 1Pierwszym i najważniejszym krokiem jest osadzenie w arkuszu obiektu/kon-

trolki Shockwave­Flash­Object . Aby to zrobić, należy (dla Excela w wersji 2007 lub 2010) uaktywnić wstążkę Deweloper­ i wybrać menu rozwijane Wstaw, a następnie kliknąć pierwszą opcję w prawym dolnym rogu Formanty­ActiveX/Więcej­formantów… (widok 1) .

Wyświetli się lista niestandardowych kontrolek, na której należy poszukać Shockwave­Flash­Object i wstawić ją do arkusza . Po skończeniu rysowania zobaczymy prostokąt, który wyglądem będzie przy-pominać kopertę (widok 2) .

Krok 2W kroku drugim naszym zadaniem jest kliknięcie prawym przyciskiem na kontrolce i wybranie

pozycji Właściwości . Tam należy znaleźć opcję Movie i wpisać lokalizację pliku .A) W przypadku animacji flash znajdującej się na dysku twardym sprawa jest bardzo prosta – wystarczy

podać ścieżkę do pliku wraz z jego rozszerzeniem, np . C:\Animacje\Piesek.swfB) Nieco inaczej sytuacja przedstawia się w przypadku, gdy chcemy zamieścić wideo z serwisu

YouTube . W tym przypadku należy podać ścieżkę do pliku wraz z dodatkowymi informacjami, np . http://www.youtube.com/v/bESGLojNYSo&ob=av2n?fs=1&amp;hl=en_US

Widok 1

Widok 2

46

Chcąc podstawić inny film, należy zmienić jedynie podkreśloną frazę bESGLojNYSo&ob=av2n na inną . Przykładowo, jeżeli adres pliku to: http://www.youtube.com/watch?v=nhBorPm6JjQ­wówczas należy wkleić taką ścieżkę: http://www.youtube.com/v/nhBorPm6JjQ?fs=1&amp;hl=en_US

Krok 3Po zamknięciu trybu projektowania można już oglądać film/teledysk . Oto widok dla adresu http://

www.youtube.com/v/bESGLojNYSo&ob=av2n?fs=1&amp;hl=en_US (widok 3) . Jest to teledysk do utworu Lady Gagi . n

Widok 3

kwiecień 2012 47

Obliczanie kosztów czasu pracy maszyny

ed3babb1-d62b-4c90–8cfe-0a4a6f30e791

Wiele razy zastanawiałem się, czy istnieje możliwość napisania w Excelu uniwersalnej formu-ły lub makra, które rozwiązywałoby problem ruchomego czasu pracy przy różnych stawkach godzinowych. Sprawa jest dość prosta w przypadku, gdy godziny pracy są zawsze takie same lub gdy stała jest stawka godzinowa. Co jednak w sytuacji, gdy obie zmienne są rucho-me? Weźmy „na tapetę” taki oto przykład. W mojej pracy trzy razy w ciągu dnia uruchamiana jest pewna maszyna. Czas włączenia każdego dnia jest dość podobny. Problem polega na tym, że maszyna jest wyłączana o różnych porach, przez co generuje różne koszty. Czy mimo tak dużej ilości zmiennych jest jakiś sposób, aby obliczyć dzienny koszt działania maszyny?

Rozwiązanie takiego zadania jest dość trudne z poziomu samego Excela, nato-miast można w tym celu napisać makro, którego celem byłoby obliczenie łącznego kosztu pracy maszyny dziennie . Przyjmijmy następujące warunki zadania .

Stawki za pracę maszyny zależnie od czasu:0:00–5:59 – 8,50 zł6:00–11:59 – 9,25 zł12:00–17:59 – 10,50 zł18:00–23:59 – 7,70 zł .

Czas pracy maszyny w konkretnym dniu:5:38–7:1811:29–14:1217:52–21:39 .

problem

rozwiązanie

48

Widok 1 pokazuje makro, które wylicza dzienny koszt pracy maszyny . Wynikiem jest kwota 72,53 zł, wyświetlana w okienku MsgBox . Idea działania makra jest bardzo prosta . Korzystamy w niej z faktu, że Excel traktuje daty jak liczby . Skoro doba jest tak naprawdę liczbą 1, to pojedyncza godzina odpo-wiada liczbie 0,0416666666666667 . Idąc dalej tym tropem, jedna minuta to tak naprawdę 0,000694444444444444 . Jedna doba składa się z 1440 minut . W pętli sprawdzamy każdą z tych minut . Za pomocą instrukcji Select­Case sprawdzamy, czy w danej minucie maszyna pracowała . Jeżeli maszy-na była uruchomiona w jednym z trzech przedziałów czasowych, wówczas sczytujemy za pomocą funkcji WYSZUKAJ .PIONOWO odpowiednią stawkę godzinową pracy maszyny . Następnie dzielimy tę stawkę przez 60, wyliczając w ten sposób stawkę minutową . Zmienna dblKoszt to tak naprawdę suma wszystkich stawek minutowych i to jej ostateczna wartość jest wynikiem działania makra .

n

kwiecień 2012 49

Utworzeniei instalacja dodatku

–2cfa-4535–84a3-c302879392a7

Mam kilka ciekawych makr, z których regularnie korzystam.Niestety, są one umieszczo-ne w skoroszycie makr osobistych, a co za tym idzie, mogę z nich korzystać tylko w poje-dynczym pliku. Zastanawiam się, co trzeba zrobić, aby móc ich używać w innych plikach lub aby inne osoby w pracy również miały do nich dostęp. Słyszałem, że istnieją w Excelu dodat-ki, które to umożliwiają, jednak kompletnie nie wiem, jak się je tworzy, instaluje i jak się z nich korzysta. Czy jest to trudne? Czy mógłbym poprosić o jakiś konkretny przykład?

Rzeczywiście, dodatki pozwalają w bardzo łatwy sposób rozpowszechniać makra wśród innych osób . Jedynym warunkiem korzystania z nich jest prosta instalacja . Czym więc jest tak naprawdę dodatek? Otóż mówiąc najprościej, jest to ukryty skoroszyt Excela z rozszerzeniem * .xla (dla Excela 2003 i wersji wcześniejszych) lub * .xlam (dla Excela 2007/2010) . Korzystanie z dodatków ma kilka niezaprzeczalnych zalet:l po jego zainstalowaniu będzie on zawsze dostępny dla każdego nowo otwartego

pliku,l makra w dodatkach będą działały nawet wtedy, gdy poziom zabezpieczeń usta-

wiono na wysoki (blokowanie makr),l użytkownik nie może odkryć skoroszytu poprzez opcję Okna/Odkryj .

Utworzenie dodatkuUtworzenie dodatku jest bardzo proste . Gdy mamy już w module zestaw swoich ulubionych makr,

powinniśmy wprowadzić kilka informacji na temat dodatku . W naszym przykładzie utworzymy dodatek o nazwie Nazwa­arkusza.xla . Dodatek zawiera tylko jedną prostą funkcję, która zwraca nazwę aktyw-nego arkusza typu Worksheet . Kod naszej funkcji wygląda jak na widoku 1 .

Informacje na temat samego dodatku wpisujemy we właściwościach skoroszytu w zakładce Podsumowanie .

Ostatnim etapem tworzenia dodatku jest jego zapisanie w odpowiednim rozszerzeniu . W tym celu wybieramy opcję Zapisz­jako… i z listy wybieramy Dodatek­programu­Excel­2003–97 .

problem

rozwiązanie

Widok 1

50

Widok 2

Widok 3

Instalacja dodatku

Skoro utworzyliśmy już dodatek, pozostaje nam go zainstalować . Aby to zrobić, należy przejść do listy wszyst-kich dodatków i tam w okienku Przeglądaj­wybrać plik Nazwa­arkusza.xla:l dla Excela 2003 (Excel/Narzędzia/Do-­

datki…/Przeglądaj),l dla Excela 2007/2010 (Opcje­progra-

mu­Excel/Dodatki/Przejdź/Przeglądaj) .Po zainstalowaniu dodatku możemy

już z niego korzystać . Widok 3 pokazu-je komórkę C3, która zawiera formułę =NazwaArkusza() . Zwraca ona w wyni-ku prawidłową nazwę arkusza, w któ-rym znajduje się formuła – czyli słowo Wejście . n

kwiecień 2012 51

Kalendarz w komórce

Natrafiłem kiedyś na bardzo ciekawy plik Excela, w którym była możliwość wyświetlenia kalendarza. Bardzo mnie to zaciekawiło, bo wprowadzanie dat do komórek arkusza jest wbrew pozorom dosyć skomplikowane – nie każdy wie, w jakiej kolejności trzeba wpisywać dane, a także jakiego separatora użyć. Czy jest możliwość, aby np. po dwukrotnym kliknię-ciu w dowolną komórkę wyświetlał się kalendarz z aktualną datą, a po wybraniu odpowied-niej daty była ona wpisywana do aktywnej komórki?

Temat jest bardzo ciekawy i uważam, że zdecydowanie warto wprowadzić takie udogodnienie użytkownikom z dwóch prostych powodów . Po pierwsze, faktycznie wprowadzanie dat jest bardzo problematyczne zwłaszcza dla początkujących osób . Po drugie, napisanie makra pod taką operację jest dosyć proste . Sprawdźmy, co należy zrobić, aby taki kalendarz utworzyć:

Krok 1: wstaw formularzW okienku Project Explorer należy wybrać odpowiedni plik Excela VBAProject­

(Kalendarz­w­komórce.xls) i wstawić nowy formularz Insert­>­UserForm (widok 1) .

Krok 2: wstaw do formularza kontrolkę kalendarzaTeraz należy narysować kontrolkę Calendar­Control, ale jest to kontrolka, która nie jest widoczna

w Toolbox . Aby ją dodać, musimy się najpierw upewnić, że Toolbox jest widoczny (View­>­Toolbox) . Następnie dodajemy ją (Tools­ >­Additional­Controls) . Ta opcja dodaje nowy przycisk do Toolbox, nazwany Calendar . Po wprowadzeniu pewnych drobnych zmian związanych z wyglądem możemy uzyskać efekt taki jak na widoku 2 .

Krok 3: kod dla kalendarzaTeraz, gdy mamy już gotowy formularz, pozostaje nam go oprogramować . W module formularza

umieszczamy dwa makra (widok 3) . W module zwykłym jedno (widok 4) i jedno w module arkusza (widok 5) .

problem

rozwiązanie

Widok 1 Widok 2

52

Działa to w ten sposób . Po dwukrotnym kliknięciu dowolnej komórki w arkuszu wyświetlany jest formularz wraz z kalendarzem .W przypadku gdy została zaznaczona komórka z datą, na kalendarzu wyświetla się właśnie ta data, w przeciwnym razie wyświetlana jest aktualna data . Po kliknięciu w kalendarz aktualna data zapisywana jest do komórki arkusza, a formularz zostaje zamknięty . n

Widok 4

Widok 3

Widok 5

kwiecień 2012 53

Ilość danej litery w arkuszu

Zastanawiam się, czy istnieje jakiś sposób na to, aby sprawdzić, ile razy pojedyncza litera (np. a) występuje we wszystkich komórkach arkusza. Rozróżnianie wielkości liter jest opcjo-nalne. Jeżeli w jednej komórce znajduje się słowo Azja, a w innej Australia, to chciałbym, aby funkcja zwracała wynik 3 (tylko litery małe) lub 5 (litery małe i wielkie). Czy można to rozwią-zać za pomocą formuły, bez makra?

Excel nie posiada w swojej kolekcji funkcji arkuszowej, która w prosty sposób pozwalałaby obliczyć, ile razy konkretna litera występuje w podanym tekście . Aby wykonać to zadanie, trzeba się trochę wysilić i zastosować rozwiązanie okrężne . Najpierw należy za pomocą funkcji PODSTAW zamienić szukaną w tekście literę (np . a) na pusty ciąg (np . słowo Europa zamieni się na Europ), a następnie spraw-dzić długość nowo powstałego ciągu za pomocą funkcji DŁ (dla słowa Europ będzie to 5) . Wynikiem funkcji jest różnica pomiędzy długością ciągu bazowego (6) a dłu-gością nowego ciągu (5) . W naszym przypadku jest to 1 i faktycznie litera a wystę-

puje w słowie Europa tylko jeden raz .W przypadku gdy chcemy zastosować taką operację dla większego zakresu komórek, należy posłużyć

się formułą tablicową . Nie będzie to jednak rozwiązanie, które sprawdzi się w przypadku wszystkich komórek arkusza . Nawet w przypadku Excela 2003 wszystkich komórek w arkuszu jest aż 16 777 216 (65 536 wierszy × 256 kolumn) . W takiej sytuacji Excel najprawdopodobniej zawiesi się, ponieważ musi przetworzyć w pamięci ponad 16 milionów komórek . Na dodatek jest to formuła tablicowa, która dzia-ła dużo wolniej niż zwykła formuła .

Załóżmy jednak, że w zakresie A1:B10 znajdują się nazwy wszystkich siedmiu kontynentów, tj . Europa, Azja, Ameryka Północna, Ameryka Południowa, Afryka, Australia, Antarktyda . Chcąc spraw-dzić, ile razy występuje w tym zakresie litera a (bez rozróżniania liter), należy zastosować formułę tablicową (zatwierdzaną przez kombinację klawiszy Ctrl+Alt+Enter):

=SUMA(DŁ(zakres)-DŁ(PODSTAW(LITERY.MAŁE(zakres);"a";"")))

Nazwa zakres odnosi się w tym przypadku właśnie do komórek A1:B10 .Taka formuła zwraca w wyniku 17, ponieważ litera a (zarówno duża jak i mała) występuje

w nazwach kontynentów właśnie tyle razy .Chcąc zliczyć wszystkie litery małe a, należy zastosować taką formułę tablicową:

=SUMA(DŁ(zakres)-DŁ(PODSTAW(zakres;"a";"")))

Taka formuła zwróci w wyniku liczbę 11 .Chcąc zliczyć wszystkie litery wielkie A, należy zastosować taką formułę tablicową:

=SUMA(DŁ(zakres)-DŁ(PODSTAW(zakres;"A";"")))

Taka formuła zwróci w wyniku liczbę 6 (wszystkie kontynenty poza Europą zaczynają się na literę A) . n

problem

rozwiązanie

54

Zaawansowane formatowaniewarunkowe

Korzystam z Excela 2003. W tej wersji programu istnieje możliwość zastosowania maksy-malnie trzech kryteriów dla formatowania warunkowego. Czy istnieje sposób, aby tę wartość jakoś zwiększyć i zastosować więcej kryteriów? Może być makro. Wiem, że nowsze wersje Excela pozwalają na zaawansowane formatowanie, ale to rozwiązanie odpada. Proszę o jakiś ciekawy przykład.

Problem można rozwiązać jedynie za pomocą zastosowania odpowiedniego makra działającego w konkretnej procedurze zdarzeniowej . Widok 1 pokazuje tabelę największych pod względem liczby ludności miast w Polsce wraz z dany-mi odnośnie do powierzchni . Załóżmy, że chcielibyśmy określić pięć przedzia-łów . Kolor wypełnienia (odcień) uzależniony będzie od tego, w którym przedzia-le znajduje się wartość powierzchni miasta . Tabelka pomocnicza znajduje się po prawej stronie widoku .

Aby uzyskać efekt taki jak na rysunku, najpierw potrzebujemy uruchomić makro, które doda nowe style do naszej kolekcji już istniejących, wbudowanych

stylów . Widok 2 pokazuje takie makro . Przechodzi ono po wszystkich komórkach w zakresie Nazwa_stylu (kolumna G), i dodaje nowy styl na bazie konkretnej komórki, z konkretną nazwą . Przykładowo, makro najpierw dodaje styl o nazwie najmniejszy, z bardzo jasnym odcieniem brązu i czarną czcion-ką . Zarezerwowany on będzie dla tych miast, które mają powierzchnię poniżej 101 km2 .

Skoro mamy już zdefiniowane nowe style, to trzeba teraz napisać makro, tak aby wykonywało się w momencie, gdy wartość którejś z powierzchni ulegnie zmianie . W tym celu należy je umieścić w module arkusza, w którym znajduje się tabela, i zastosować konkretne zdarzenie,

Widok 1

problem

rozwiązanie

kwiecień 2012 55

Widok 2

Widok 3

np . Worksheet_SelectionChange, które zadziała po zmianie zaznaczenia komórki w arkuszu . Makro, które pokazuje widok 3, sprawdzi w pętli po kolei powierzchnię dla każdego miasta i przypisze każdej komórce właściwy styl . n

56

Najpopularniejszefunkcje VBA

Zauważyłem, że każdy programista piszący makra ma swój ulubiony zestaw funkcji VBA, które stosuje w różnych projektach. Są to funkcje uniwersalne, które pozwalają np. znaleźć ostatni niepusty wiersz w kolumnie lub przyspieszyć makro poprzez wyłączenie odświeżania ekranu i ustawienie przeliczania na manualne. Czy ekspert „Controllingu i Rachunkowości Zarządczej” też ma taką listę, czy też za każdym razem każde makro pisze od nowa?

Rzeczywiście, praktycznie każdy większy projekt VBA wymaga od programisty odwołania się w wielu miejscach kodu do tej samej funkcji . Przykładowo może to być właśnie obliczenie ostatniego niepustego wiersza, przyspieszenie makra na star-cie programu czy na przykład pobranie adresu pliku z okienka dialogowego . Mam kilka swoich własnych ulubionych, przygotowanych funkcji, które stosuję . W dalszej części porady pokrótce je opiszę .

W ostatnich dwóch artykułach publikowanych w „Controllingu i Rachunkowości Zarządczej” przedstawiłem aż osiem sposobów na ustalenie pozycji ostatniego nie-pustego wiersza w pojedynczej kolumnie danych . Rozwiązaniem, które okazało się dla mnie najbardziej efektywne (bardzo szybkie, uwzględnia ukryte komórki, nie

bierze pod uwagę pustych wierszy w tabeli), było zastosowanie funkcji arkuszowej PODAJ .POZYCJĘ (MATCH) . Na bazie tej funkcji stworzyłem funkcję VBA LastRow, która sprawdza pozycję ostatniego niepustego wiersza w pojedynczej kolumnie danych .

Drugim makrem, z którego bardzo często korzystam w swojej pracy, jest procedura SpeedUpMacro z parametrem State, który przyjmuje wartości typu logicznego Boolean . Sama nazwa procedury oznacza tyle, co „przyspiesz makro” . Jeżeli użytkownik wywoła tę procedurę z argumentem ustawionym na TRUE, wówczas przeliczanie zostanie ustawione na ręczne, kształt kursora zmieni się w klepsydrę (sugeruje, że makro zaczyna swój bieg), wyłączone zostaną zdarzenia i odświeżanie ekranu . Po zakoń-

Widok 1 . Funkcja do sprawdzenia pozycji ostatniego niepustego wiersza w kolumnie

problem

rozwiązanie

kwiecień 2012 57

czeniu działania makra należy tę funkcję wywołać z parametrem ustawionym na FALSE, aby przywró-cić ustawienia domyślne .

Następną bardzo ważną funkcją jest­GetDistinct,­czyli w wolnym tłumaczeniu „weź unikaty” . Funkcja jest niezwykle przydatna i znajduje zastosowanie w większości projektów . Przyjmuje ona parametr wejściowy Target typu Range, natomiast zwraca w wyniku tablicę unikatów typu Variant . Funkcja może działać na zakresie wielokolumnowym, wykorzystujemy w niej obiekt Dictionary biblioteki­Microsoft­Scripting­Runtime.

Bardzo często w trakcie projektu pojawia się potrzeba pobrania od użytkownika ścieżki do katalogu lub pliku zawierającego interesujące nas dane . W tym celu można wyświetlić okienko dialogowe z proś-bą o dokonanie wyboru . Jeżeli użytkownik nie dokonał żadnego wyboru (tj . kliknął przycisk Anuluj lub zamknął okienko krzyżykiem), to funkcja zwraca w wyniku pusty ciąg tekstowy, w przeciwnym wypad-ku wynikiem funkcji jest pełna ścieżka do katalogu/pliku . W makrze głównym możemy sprawdzić, czy długość tego ciągu tekstowego jest większa od zera . Jeżeli nie, możemy wyświetlić komunikat, że użyt-kownik nie dokonał wyboru, i zakończyć makro .

Widok 2 . Funkcja przyspieszająca działanie makra

Widok 3 . Pobranie wartości unikatowych z dowolnego zakresu komórek

58

Bardziej zaawansowani programiści VBA wiedzą, że formularze powinny być traktowane w ten sam sposób jak klasy i należy się do nich odwoływać poprzez właściwości i metody, a nie stan czy wartość odpowiednich kontrolek . Klikając w krzyżyk, użytkownik zamyka formularz, w ten sposób niszcząc egzem-plarz . Powoduje to, że po ponownym uruchomieniu nowej formy dane z poprzedniej nie zostaną zapamię-tane (ponieważ faktycznie uległa ona zniszczeniu) . Aby temu zapobiec, przychwytuje się moment kliknięcia w krzyżyk i ukrywa formularz, a nie usuwa z pamięci . W ten sposób formularz, mimo że jest niewidoczny, to wciąż przechowuje informacje dotyczące jego kontrolek . n

Widok 5 . Uniknięcie zamknięcia formularza

Widok 4 . Pobranie ścieżki do katalogu

kwiecień 2012 59

Edytor VB

Dopiero poznaję świat makr i chciałbym uzyskać nieco więcej informacji na temat samego edytora VB. Z tego, co widzę, to każdy programista ma nieco inny układ okienek w VB, co więcej – niektórzy mają wyświetlone tylko trzy okienka, a inni aż sześć. Z czego to wynika? Czy mógłbym otrzymać jakieś dokładne instrukcje na temat tego, czego dotyczą poszcze-gólne okienka?

To prawda . Układ okienek i ich ilość w edytorze VB zależy zarówno od osobis-tych preferencji, jak i od wielkości monitora, który posiada programista . W poradzie tej omówię najważniejsze elementy edytora VB .

Okno Project ExplorerElementem, który standardowo wyświetla się przy pierwszym otwarciu edy-

tora VB, jest Project Explorer . Pełni on kluczową rolę nawigacji pomiędzy modułami . W zależności od tego, w której sekcji chcemy umieścić kod (moduł skoroszytu, arkusza, formularza, klasy czy zwykły moduł), musimy kliknąć właś-

ciwą nazwę . Domyślnie wszystkie moduły wyświetlane są osobno – w formie drzewka, jednak po kliknięciu na ikonę folderu (żółty przycisk) wszystkie moduły zostaną umieszczone w jednym drzewku (widok 2) .

Wybór układu jest sprawą indywidualną . Osobiście preferuję ten drugi . W takiej sytuacji pamiętać jednak należy, aby typ każdego modułu poprzedzić prefiksem, co umożliwi porządek w wyświetleniu wszystkich modułów . Nazwy formularzy poprzedzam literą U (od słowa Userform), moduły zwykłe literą M (od słowa Module), moduły klas literą C (od słowa Class), moduł ThisWorkbook prefiksem wkb (od słowa Workbook), moduły arkuszy prefiksem wks (od słowa Worksheet) .

problem

rozwiązanie

Widok 1 . Project Explorer (rozwinięte drzewko)

Widok 2 . Project Explorer (zwinięte drzewko)

60

Widok 3 . Okno Code

Okno Code

Jak wskazuje sama nazwa, jest to okienko, w którym znajduje się kod VBA . Widok 3 pokazuje dwie funkcje znajdujące się w module MFunkcjeMakra .

Pole kombi, które znajduje się na górze po prawej stronie, zawiera listę wszystkich makr znajdują-cych się w module . Chcąc przejść do jednego z nich, należy je wybrać właśnie stąd . W lewym dolnym rogu znajdują się dwa przyciski, które pozwalają programiście na wyświetlenie w oknie kodu wszyst-kich makr (tak jak na screenie) lub tylko pojedynczego makra .

Okno PropertiesSłowo properties oznacza tyle, co właściwości . Jak pokazuje widok 4, okienko z właściwościami

dostarcza programiście wielu użytecznych informacji na temat obiektu, na którym pracuje . Co więcej – jest to miejsce, w którym programista może zmieniać domyślne wartości tych właściwo-ści, np . nazwę czy tekst na etykiecie .

Okno LocalsTrudno wyobrazić sobie pisanie kodu bez monitorowania wartości zmiennych użytych w makrze .

Edytor VB dysponuje świetnym narzędziem, jakim jest okienko Locals . Pozwala ono szybko się

kwiecień 2012 61

Widok 4 . Okno Properties

Widok 5 . Okno Locals

zorientować, jakie konkretnie wartości przybiera-ją zmienne użyte w procedurze, w której akurat się znajdujemy (widok 5) . Oprócz tego (po klik-nięciu plusa) możemy monitorować wartości zmiennych poziomu modułu dla bieżącej proce-dury – stąd właśnie nazwa Locals, która odnosi się do zmiennych lokalnych .

Okno WatchesOkienko Locals – mimo że daje programiście

bardzo duże możliwości – to jednak nie jest narzędziem zbyt elastycznym . Przede wszystkim nie jest możliwy stały dostęp do zmiennej, ponie-waż wraz z wywołaniem procedury znajdującej się w innym module to właśnie zmienne z tej nowej procedury pojawią się w okienku Locals. Po drugie, możemy monitorować tylko wartości zmiennych, nie możemy śledzić np . wartości komórek czy nazw dynamicznych . Po trzecie, nie możemy automatycznie przerwać makra w sytua-cji, gdy np . zmieni się wartość zmiennej . Te wszystkie rzeczy są jednak możliwe do wykonania z poziomu okienka Watches­ (widok 6) . Mankamentem w tym przypadku jest jednak fakt, że każde wyrażenie należy dodać do okienka osobno (ręcznie), podczas gdy w przypadku Locals wszystko odbywa się automatycznie . Osobiście korzystam zarówno z Locals jak i Watches, ponieważ oba panele bardzo dobrze się uzupełniają .

Okno ImmediatePanel Immediate pełni dwie bardzo ważne

role . Po pierwsze, pozwala sprawdzić wartość dowolnego wyrażenia lub zmiennej . W tym celu pojedynczą linię kodu należy poprzedzić znakiem zapytania lub wyrazem Print . W naszym przykła-

62

dzie (widok 7) pokazaliśmy, jak szybko sprawdzić stan właściwości ScreenUpdating­obiektu Application, a także wartość komórki A1 . Drugą możliwością, jaką daje wykorzystanie okienka Immediate, jest uru-chomienie kodu . Może to być pojedyncza instrukcja (np . Application.ScreenUpdating­=­False bez znaku zapytania) lub po prostu nazwa makra (np . SelectFolder) . n

Widok 6 . Okno Watches

Widok 7 . Okno Immediate

kwiecień 2012 63

Problem plecakowy

Od jakiegoś czasu interesuje mnie temat rozwiązywania zadań za pomocą dodatku Solver. Zauważyłem, że można dzięki niemu wykonywać obliczenia matematyczne (np. równania), ale też może to być źródło wiedzy pomocne w podejmowaniu decyzji biznesowych, np. w kwestii zoptymalizowania pewnych procesów. Na forach bardzo często pojawia się wątek tzw. problemu plecakowego. Czy mógłbym otrzymać informacje na temat problemu plecakowego, jak również konkretny przykład, który pokazałby, w jaki sposób zastosować Solver w tym konkretnym przypadku?

W Wikipedii możemy znaleźć następującą definicję problemu plecakowego . Dyskretny­problem­plecakowy­(discrete­knapsack­problem)­jest­jednym­z­najczęściej­poruszanych­problemów­optymalizacyjnych.­Nazwa­zagadnienia­pochodzi­od­maksy-malizacyjnego­problemu­wyboru­przedmiotów,­tak­by­ich­sumaryczna­wartość­była­jak­największa­i­jednocześnie­mieściły­się­w­plecaku.­Przy­podanym­zbiorze­elemen-tów­o­podanej­wadze­i­wartości,­należy­wybrać­taki­podzbiór,­by­suma­wartości­była­możliwie­jak­największa,­a­suma­wag­była­nie­większa­od­danej­pojemności­plecaka.

Problem­plecakowy­często­przedstawia­się­jako­problem­złodzieja­rabującego­sklep­–­znalazł­on­N­towarów;­j–ty­przedmiot­jest­wart­cj­oraz­waży­wj.­Złodziej­dąży­do­zabrania­ze­sobą­jak­najwartościowszego­łupu,­przy­czym­nie­może­zabrać­więcej­niż­B­kilogramów.­Nie­może­też­zabierać­ułam-kowej­części­przedmiotów­(byłoby­to­możliwe­w­ciągłym­problemie­plecakowym).

Podobny­problem­pojawia­ się­ często­w­kombinatoryce,­ teorii­ złożoności­ obliczeniowej,­ kryptografii­oraz­matematyce­stosowanej.

Widok 1 pokazuje dane wejściowe . Mamy siedem towarów o różnej cenie i różnym ciężarze . Maksymalna pojemność plecaka to 5 kg . Każdy towar może być wybrany tylko jeden raz . Formuły wyliczeniowe znajdują się w wierszu 11 . Jedyną zmienną jest ilość każdego z produktów .

problem

rozwiązanie

Widok 1 . Solver – dane wejściowe

64

Widok 3 . Solver – rozwiązanie

Widok 2 . Solver – warunki ograniczające

Komórka C11 zawiera formułę =SUMA.ILOCZYNÓW(Waga;Ilość). Oblicza ona łączny ciężar wszyst-kich towarów wrzuconych do plecaka . W tej chwili wynikiem jest zero, ponieważ plecak jest pusty . Maksymalnie może on pomieścić 5 kg .

Komórka D11 zawiera formułę =SUMA.ILOCZYNÓW(Cena;Ilość). Oblicza ona łączną cenę wszystkich towarów wrzuconych do plecaka . W tej chwili wynikiem jest zero, ponieważ plecak jest pusty . Dążymy do tego, aby ta cena była jak najwyższa (by łup był jak najbardziej wartościowy) . Widok 2 przedstawia warunki zdefiniowane w Solverze .

Komórką celu jest D11, czyli komórka obliczająca łączną cenę wszystkich wrzuconych do plecaka produktów – parametr ustawiamy na Maks . Warunkiem ograniczającym jest pojemność plecaka (maks . 5 kg), fakt, że ilość musi być liczbą całkowitą (nie możemy wrzucić np . pół produktu), a także to, że każdy produkt możemy wykorzystać tylko jeden raz (czyli de­facto mamy tutaj do czynienia z systemem zero-jedynkowym – albo wrzucamy produkt do plecaka, albo nie) . Widok 3 przedstawia osiągnięte wyniki .

Okazało się, że najbardziej opłacalne będzie wrzucenie do plecaka trzech produktów o łącznej masie 4,7 kg, co przekłada się na kwotę 352 zł . n

kwiecień 2012 65

Nowe funkcje obliczeniowew Excelu 2007

W wersji 2007 Excela pojawiły się nie tylko wstążki, ale także bardzo przydatne nowe funk-cje. Mam tu na myśli nie tylko JEŻELI.BŁĄD, ale przede wszystkim funkcje obliczeniowe,tj. ŚREDNIA.JEŻELI, LICZ.WARUNKI, SUMA.WARUNKÓW, ŚREDNIA.WARUNKÓW. Nie do końca jednak rozumiem, jak one działają. Czy mógłbym otrzymać jakieś przykłady? Czy jest w ogóle sens korzystania z nich, jeżeli nie działają one w wersji 2003 Excela? Zdania na ten temat są podzielone. Jedni mówią, aby nie korzystać z tych funkcji ze względu na kompaty-bilność, inni natomiast twierdzą, że Excel 2003 to już przeżytek i należy śmiało się nimi posługiwać.

Rzeczywiście, Excel 2007 dostarczył użytkownikom kilku nowych funkcji i przy-znam szczerze, że jestem ogromnym zwolennikiem korzystania z nich . Oczywiście kwestia kompatybilności z wcześniejszymi wersjami Excela jest bardzo ważna i musimy wziąć ją pod uwagę, ale nie możemy z tego względu rezygnować z całej palety świetnych funkcji, które mogą nam ułatwić życie .

Co zatem rekomenduję? Rozwiązania za pomocą formuł tablicowych dla Excela 2003 oraz rozwiązania za pomocą nowych funkcji dla Excela 2007 . W przypadku bardziej zaawansowanych aplikacji należy na starcie uprzedzić użytkownika, że

wymagana jest wersja Excela co najmniej 2007 .Widok 1 oraz 2 pokazują tabelę państw europejskich wraz z informacjami na temat każdego

z nich . Widok 3 natomiast pokazuje wyniki formuł tablicowych (dla Excela 2003) oraz nowych formuł (dla Excela 2007) . Zarówno w jednym, jak i w drugim przypadku uzyskujemy identyczne wyniki, które omówimy poniżej . Badaną właściwością jest liczba mieszkańców kraju, natomiast dwiema zmiennymi są: przynależność do Unii Europejskiej i fakt posługiwania się walutą euro .

W pierwszych dwóch przypadkach użyliśmy tych samych formuł dla Excela 2003 i Excela 2007 . Pierwsza formuła =LICZ.JEŻELI(UE;”tak”) zwróciła w wyniku 27 i jest to liczba krajów należących do Wspólnoty Europejskiej .

Druga formuła =SUMA.JEŻELI(UE;”tak”;Ludność) zwróciła w wyniku kwotę prawie 500 milionów i jest to suma wszystkich mieszkańców państw należących do UE .

Różnice w formułach pojawiają się już w trzecim przypadku, gdy obliczamy średnią liczbę miesz-kańców przypadających na jeden kraj UE .

{=ŚREDNIA(JEŻELI(UE=”tak”;Ludność;””))}=ŚREDNIA.JEŻELI(UE;”tak”;Ludność)

Dla Excela 2003 jest to formuła tablicowa, w której musimy użyć funkcji JEŻELI, aby zamienić niepasujące dane na puste pola, a nie na zera (spowodowałoby to błędne wyliczenie średniej) . W przy-padku Excela 2007 możemy wykorzystać wbudowaną funkcję ŚREDNIA .JEŻELI, która do złudzenia przypomina popularną funkcję SUMA .JEŻELI, dzięki czemu jest prosta w zrozumieniu i nie trzeba jej zatwierdzać w sposób tablicowy .

Częstym problemem występującym w Excelu 2003 było zliczenie komórek na podstawie dwóch kryteriów, ponieważ funkcja LICZ .JEŻELI standardowo pozwala zastosować tylko pojedyncze kryte-rium . W tym celu trzeba było utworzyć formułę tablicową, która mnożyła przez siebie elementy dwóch tablic i na końcu je sumowała . Naszą intencją było zliczenie krajów, które należą do Unii Europejskiej i jednocześnie operują walutą euro . Tradycyjna formuła tablicowa (alternatywnie

problem

rozwiązanie

66

Widok 1 . Tabela krajów europejskich – część pierwsza

Widok 2 . Tabela krajów europejskich – część druga

kwiecień 2012 67

zamiast SUMY można wykorzystać funkcję SUMA .ILOCZYNÓW, której nie trzeba zatwierdzać tab-licowo) zwróciła w wyniku 16, podobnie jak nowa funkcja LICZ .WARUNKI .

{=SUMA((UE=”tak”)*(Euro=”tak”))}=LICZ.WARUNKI(UE;”tak”;Euro;”tak”)

Podobnie w Excelu 2003 problemem było zsumowanie danych według konkretnej kolumny w przypadku dwóch lub więcej niż dwóch kryteriów, ponieważ funkcja SUMA .JEŻELI standardowo pozwala na sumowanie tylko według jednego kryterium . I tutaj uniwersalną formułą sprawdzającą się zarówno w Excelu 2003, jak i Excelu 2007 była formuła tablicowa, która działała w oparciu o funkcję SUMA . W naszym przypadku chcieliśmy zsumować liczbę mieszkańców wszystkich krajów UE, w których płaci się walutą euro . Identyczny wynik osiągnęliśmy, korzystając z wbudowanej w Excelu 2007 formuły SUMA .WARUNKÓW .

{=SUMA((UE=”tak”)*(Euro=”tak”)*Ludność)}

=SUMA.WARUNKÓW­(Ludność;UE;”tak”;Euro;”tak”)

W ostatnim przypadku chcieliśmy obliczyć średnią liczbę mieszkańców kraju Unii Europejskiej, w którym walutą obowiązującą jest euro . W przypadku Excela 2003 formuła zmieniła się dość wyraź-nie . Funkcję SUMA zastąpiła kombinacja funkcji ŚREDNIA i JEŻELI, natomiast w przypadku Excela 2007 skorzystaliśmy z funkcji ŚREDNIA .WARUNKÓW .

{=ŚREDNIA(JEŻELI((UE=”tak”)*(Euro=”tak”);Ludność;””))}

=ŚREDNIA.WARUNKÓW(Ludność;UE;”tak”;Euro;”tak”) n

Widok 3 . Porównanie wyników w Excelu 2003 i Excelu 2007

68

Wyszukiwaniewedług dwóch kryteriów

Czy istnieje jakiś sposób, aby za pomocą funkcji WYSZUKAJ.PIONOWO możliwe było zna-lezienie odpowiedniej wartości na podstawie dwóch kryteriów? Funkcja ta świetnie spisuje się, gdy szukamy pojedynczej wartości, ale na tym poprzestaje. Czy jest jakiś inny sposób, aby wykonać to zadanie? Może być formuła lub inne narzędzie, natomiast odpada rozwią-zanie za pomocą makra.

Funkcja WYSZUKAJ .PIONOWO została wprowadzona do Excela w konkretnym celu, aby sczytywać informacje, które znajdują się w tym samym wierszu tabeli co wartość szukana, licząc na prawo od niej . W sytuacji gdy chcemy, aby funkcja wyszukała nam wartość na podstawie dwóch kryteriów, musimy dostawić dodatko-wą kolumnę pomocniczą lub skorzystać z kombinacji innych funkcji .

Widok 1 pokazuje przykładową tabelę danych z rejestrem czasu pracy . Naszym celem jest wyszukanie czasu pracy pracownika na podstawie jego imienia i nazwi-ska, a także daty . Kolumna A zawiera dodatkową kolumnę pomocniczą, która tworzy ciąg tekstowy, tzw . zbitkę, na podstawie daty (zapisanej jako liczba) i danych pra-

cownika . Dostawienie tej kolumny na lewo od kolumny Czas­pracy jest koniecznym wymogiem, jeśli chcemy skorzystać z funkcji WYSZUKAJ .PIONOWO .

Zadanie to możemy rozwiązać jeszcze co najmniej na dwa sposoby: za pomocą funkcji tablicowej z wykorzystaniem funkcji: INDEKS­i PODAJ .POZYCJĘ, a także z wykorzystaniem tabeli przestawnej, co pokazuje widok­2 .

problem

rozwiązanie

Widok 1 . Tabela danych

kwiecień 2012 69

W pierwszym przypadku wykorzystaliśmy funkcję WYSZU-

KAJ .PIONOWO . Formuła w komór-ce I2 to:

=JEŻELI.BŁĄD(WYSZUKAJ.PIO-­NOWO(TEKST($G2&$H2;”@”);Ta-­

bela;4;FAŁSZ);”-----”)

Funkcja najpierw szuka ciągu tekstowego 40940­ Marcin­ Wojtala w pierwszej kolumnie tabeli, czyli w tzw . zbitce . Następnie w wyniku przypisuje jej wartość z czwartej kolumny tabeli tego samego wier-sza (czyli tak naprawdę sczytuje czas pracy) . W sytuacji gdy szuka-na wartość nie znajduje się w pierwszej kolumnie, funkcja wyrzuca w wyniku błąd . Możemy się przed tym zabezpieczyć, poprzedzając naszą formułę funk-cją JEŻELI .BŁĄD, która wyświetli odpowiedni komunikat w przypad-ku błędu .

W drugim przypadku uzyskuje-my dokładnie takie same wyniki, ale za pomocą zupełnie innego rozwiązania . Zamiast funkcji

WYSZUKAJ .PIONOWO możemy zastosować formułę tablicową . Dla pracownika Marcina­Wojtali­daje ona prawidłowy wynik 08:18 i wygląda tak:

=JEŻELI.BŁĄD(INDEKS(Czas_pracy;PODAJ.POZYCJĘ(1;(Data=$G9)*(Pracownik=$H9);0));”-----”)

Jest to funkcja tablicowa, w której porównujemy ze sobą dwie tablice . W pierwszej części wszystkie komórki z kolumny Data porównujemy z datą 1­lutego­2012­r. – w ten sposób tworzymy w pamięci Excela tablicę wartości PRAWDA/FAŁSZ . W drugiej części wszystkie komórki z zakresu Pracow-­nik zestawiamy z ciągiem znaków Marcin­Wojtala, tworząc de­facto drugą tablicę typu PRAWDA/FAŁSZ . Po przemnożeniu dwóch tablic przez siebie otrzymujemy tablicę składającą się z zer i jednej jedynki . Ta jedynka to pozycja, z której będziemy sczytywać dane z zakresu Czas_pracy . Pozycja jedynki w tym przypadku to 3, a to oznacza, że wynikiem funkcji będzie trzeci wiersz zakresu Czas_pracy, czyli tak naprawdę czwarty wiersz kolumny D .

W trzecim przypadku możemy zastosować tabelę przestawną . Analizę zalet i wad tej opcji zostawiam pod rozwagę Czytelnikom . n

812ac588-b8b7-494e-9f46-e44a70e3b96c

Widok 2 . Rozwiązanie zadania