W tym odcinku przedstawiam kilka po-rad praktycznych dotycz▒cych optymalizacji arkuszy.

Optymalizacja arkusza

Arkusz optymalny to taki, kt≤ry robi wszystko, do czego zosta│ stworzony, przy czym robi to szybko i zajmuje ma│o miejsca na dysku. Je┐eli arkusz jest bardzo du┐y i przelicza siΩ d│ugo, nale┐y zastanowiµ siΩ czy istniej▒ miejsca, w kt≤rych grupΩ formu│ mo┐na zast▒piµ jedn▒ formu│▒ tablicow▒ lub w og≤le zast▒piµ formu│y ich warto╢ciami (przez skopiowanie i wklejenie specjalne z opcj▒ "warto╢ci"). Og≤lnie powodem d│ugiego przeliczania mo┐e byµ nieefektywna struktura arkusza, u┐ycie du┐ej liczby powolnych funkcji typu: WYSZUKAJ.PIONOWO (VLOOKUP), WYSZUKAJ.POZIOMO (HLOOKUP), WYSZUKAJ (LOOKUP), PODAJ.POZYCJ╩ (MATCH), ADR.POªR (INDIRECT) lub te┐ wielu wielokrotnie zagnie┐d┐onych funkcji JE»ELI (IF). Szczeg≤lnie powolna przy przeliczaniu jest funkcja ADR.POªR. Niestety, b│Ωdy strukturalne s▒ trudne do wykrycia, poniewa┐ nie mog▒ byµ w ┐aden spos≤b zg│aszane przez Excela.

Arkusz optymalny to taki, kt≤ry robi wszystko, do czego zosta│ stworzony, przy czym robi to szybko i zajmuje ma│o miejsca na dysku.

Du┐y rozmiar pliku staje siΩ uci▒┐liwy przy otwieraniu i zachowywaniu skoroszytu. Przed zachowaniem skoroszytu nale┐y zwr≤ciµ uwagΩ na to, gdzie ko±czy siΩ ka┐dy arkusz tego skoroszytu. Mo┐na to sprawdziµ przez wci╢niΩcie klawiszy CTRL+END, kt≤re powoduje przej╢cie do ostatniej kom≤rki arkusza (jest to kom≤rka le┐▒ca na przeciΩciu ostatniego u┐ytego rzΩdu i ostatniej u┐ytej kolumny). Je┐eli nasz arkusz zawiera niepotrzebne dane lub formatowania w kolumnach na lewo od ostatniej u┐ytej kolumny lub w rzΩdach powy┐ej ostatniego rzΩdu, to wszystkie niepotrzebne rzΩdy i kolumny nale┐y usun▒µ przez wybranie polecenia Edycja\Usu± i zaznaczenie opcji ca│a kolumna lub ca│y rz▒d. Po zachowaniu Excel automatycznie przesunie ostatni▒ kom≤rkΩ i nie zachowa informacji o pustych rzΩdach lub kolumnach. Na rozmiar pliku wp│ywaj▒ oczywi╢cie r≤wnie┐ same formu│y. Je┐eli nie potrzebujemy formu│, nale┐y je zmieniµ na ich warto╢ci, co z regu│y powoduje zauwa┐alne zmniejszenie rozmiaru pliku.
Na niezbyt szybkich komputerach (kartach graficznych) mo┐na spotkaµ siΩ z bardzo powolnym od╢wie┐aniem ekranu lub jego migotaniem przy przesuwaniu okna. NajczΩ╢ciej przyczyn▒ jest u┐ycie zbyt du┐ej liczby format≤w wzor≤w t│a i ramek. Mo┐na tego unikn▒µ ograniczaj▒c liczbΩ ramek (je╢li ka┐da kom≤rka w jakim╢ zakresie sp≤jnym (prostok▒tnym) ma wszystkie cztery ramki to ramki wewn▒trz zakresu rysuj▒ siΩ podw≤jnie).

Ustawienia ╢rodowiska

Na wygodΩ i szybko╢µ pracy w Excelu maj▒ wp│yw r≤wnie┐ ustawienia opcji ╢rodowiska samej aplikacji. Dotyczy to przede wszystkim ustawie± Przeliczania, kt≤re mo┐emy zmieniaµ w oknie dialogowym Opcje w zak│adce "Przeliczanie". Z regu│y przeliczanie w ma│ych arkuszach jest ustawione na automatyczne, co powoduje natychmiastowe przeliczenie ca│ego arkusza po wprowadzeniu dowolnej zmiany. W przypadku du┐ego arkusza opcja przeliczania automatycznego mo┐e doprowadziµ do bia│ej gor▒czki û ka┐da najmniejsza zmiana powoduje d│ugotrwa│e (czasem wielominutowe) przeliczanie. W tym przypadku nale┐y przeliczanie zmieniµ na rΩczne.

Na wygodΩ i szybko╢µ pracy w Excelu maj▒ wp│yw r≤wnie┐ ustawienia opcji ╢rodowiska samej aplikacji.

Mimo wprowadzanych zmian arkusz nie bΩdzie siΩ przelicza│ a┐ do chwili wci╢niΩcia klawisza funkcyjnego F9. Istnieje te┐ bardzo po┐yteczna mo┐liwo╢µ przeliczenia tylko wybranego fragmentu arkusza przez wybranie zakresu kom≤rek i wci╢niΩcie klawiszy SHIFT+F9. W opcjach dotycz▒cych przeliczania znajduje siΩ check box "przelicz przed zapisaniem". Rozs▒dne jest pozostawienie tej opcji jako aktywnej. Skoroszyty zawieraj▒ce │▒cza do innych skoroszyt≤w zachowanych bez uprzedniego przeliczenia bΩd▒ zg│aszaµ uci▒┐liwe komunikaty o tym fakcie. Je┐eli chcemy automatycznie aktualizowaµ formu│y z pominiΩciem tablicowych, to w przeliczaniu ustawiamy opcjΩ "automatycznie z wyj▒tkiem tablic". Na szybko╢µ otwierania siΩ samego Excela ma wp│yw liczba i rozmiar automatycznie │adowanych dodatk≤w (okno NarzΩdzia\Dodatki...). Je┐eli nie korzystamy z dodatk≤w znajduj▒cych siΩ na li╢cie, warto je "poodhaczaµ".

Po┐yteczne wiadomo╢ci

Specjalne formaty kom≤rek
Kom≤rki mo┐na blokowaµ lub ukrywaµ ich zawarto╢µ, ale zabiegi te ukrywaj▒ zawarto╢µ kom≤rki tylko na pasku formu│y, a nie w samej kom≤rce. Je┐eli u┐ywamy formu│y, kt≤ra zawiera jakie╢ "tajne" informacje (np. warto╢µ prowizji albo nazwisko pracownika itd.) mo┐e powstaµ konieczno╢µ ukrycia warto╢ci zwracanej w kom≤rce. Aby to uczyniµ nale┐y ustawiµ format kom≤rki jako ;;; (trzy ╢redniki). Format ustawia siΩ przez polecenie Formatuj\Kom≤rki, wybranie formatu u┐ytkownika i wprowadzenie ;;;. Po tej operacji kom≤rka nie bΩdzie niczego wy╢wietlaµ bez wzglΩdu na to czy jest ukryta i arkusz zabezpieczony, czy te┐ u┐ytkownik zmienia opcjΩ z wy╢wietlania warto╢ci na wy╢wietlanie formu│ (przez CTRL + ~).

SHIFT + Przeci▒gnij i upu╢µ
Mo┐na w szybki spos≤b zmieniµ kolejno╢µ pionowej listy warto╢ci w kom≤rkach przez wci╢niΩcie klawisza SHIFT podczas przeci▒gania kom≤rki za pomoc▒ myszy. Przy tej operacji kursor zmienia siΩ w grub▒ poziom▒ szar▒ kreskΩ stanowi▒c▒ wska╝nik miejsca wstawiania. Technika ta pozwala na ogromn▒ oszczΩdno╢µ czasu traconego na zwyczajowe wielokrotne kopiowanie i wklejanie poszczeg≤lnych czΩ╢ci listy.

Tablice przestawne (Pivot tables)
Je┐eli mamy do czynienia z du┐▒ liczb▒ danych w postaci rekord≤w, najlepiej utworzyµ bazΩ danych w Accessie, przenie╢µ do niej dane, a nastΩpnie w Excelu utworzyµ tablicΩ przestawn▒, pod│▒czon▒ do zewnΩtrznego ╝r≤d│a danych. (Przez du┐▒ liczbΩ rozumiem wiΩcej ni┐ 16384 rekordy, gdy┐ tylko tyle pomie╢ci arkusz excelowy.) Wygoda i efektywno╢µ manipulacji danymi za pomoc▒ tablicy przestawnej nie daj▒ siΩ por≤wnaµ z ┐adnym innym narzΩdziem. Do tworzenia tablic s│u┐y specjalny bardzo prosty kreator tablic, wywo│ywany poleceniem Dane\Tablica przestawna. ZachΩcam do przeczytania pomocy dotycz▒cej pivot≤w i oswojenia tego potΩ┐nego narzΩdzia excelowego. Je┐eli kto╢ zna Accessa lub inny program do tworzenia relacyjnych baz danych, to │atwo siΩ domy╢li, ┐e tablice przestawne to nic innego jak zapytania krzy┐owe (crosstab queries), kt≤re w Excelu modyfikuje siΩ przez przeci▒ganie nag│≤wk≤w mysz▒.

Nazwa Database
Je┐eli zakres kom≤rek tworz▒cy tablicΩ z nag│≤wkami nazwiemy Database (du┐e i ma│e litery s▒ r≤wnoprawne), to dodaj▒c rekordy przez polecenie Dane\Formularz spowodujemy, ┐e Excel automatycznie bΩdzie rozszerzaµ zakres, do kt≤rego odnosi siΩ nazwa Database. Korzy╢ci polegaj▒ na tym, ┐e tablice przestawne zbudowane na tym zakresie, jak r≤wnie┐ wykresy bΩd▒ siΩ same aktualizowaµ przy wprowadzaniu lub usuwaniu rekord≤w. Mo┐na zdefiniowaµ kilka zakres≤w jako Database, ale jak pamiΩtamy z cz. 8 kursu ka┐d▒ nazwΩ musimy poprzedziµ nazw▒ arkusza tak, aby sta│y siΩ nazwami lokalnymi.

Rysowanie wykres≤w
tylko z widocznych kom≤rek Czasami trzeba tymczasowo schowaµ wybrane serie danych na wykresie bez ingerencji w ustawienia wykresu (np. aby wydrukowaµ tylko niekt≤re serie). Aby tego dokonaµ wystarczy schowaµ wybrane kolumny w arkuszu, z kt≤rego wykres pobiera dane. Szybkie chowanie i odkrywanie rzΩd≤w i kolumn zapewniaj▒ skr≤ty: CTRL + 9, CTRL + SHIFT + 9 (rzΩdy), CTRL + 0, CTRL + SHIFT + 0 (kolumny).

Specjalne techniki nawigacyjne
Mo┐na zmieniµ kom≤rkΩ aktywn▒ w wybranym zakresie przez wci╢niΩcie CTRL + . (kropka). Powoduje to przeskakiwanie kom≤rki aktywnej przez kolejne wierzcho│ki wybranego zakresu. Mo┐na w≤wczas zmieniµ wymiary zakresu wciskaj▒c SHIFT + strza│ki kursora. Uwaga! Klawisz z kropk▒ na klawiaturze numerycznej dzia│a tak samo tylko wtedy, gdy jest aktywny klawisz NumLock, inaczej dzia│a jak klawisz Delete!

Inne po┐yteczne skr≤ty klawiszowe
Kombinacja	Dzia│anie
CTRL+ *	po wybraniu dowolnej kom≤rki wewn▒trz tablicy wybiera zakres obejmuj▒cy ca│▒ tablicΩ
CTRL+SHIFT * ?	wybiera kom≤rki zawieraj▒ce notatki
CTRL+[	je╢li aktywna kom≤rka zawiera formu│Ω, to wybiera kom≤rki, do kt≤rych dana formu│a odnosi siΩ bezpo╢rednio
CTRL+]	wybiera kom≤rki, kt≤rych formu│y zawieraj▒ bezpo╢rednie odniesienie do aktywnej kom≤rki
CTRL+SHIFT+{	wybiera wszystkie kom≤rki, do kt≤rych odnosi siΩ dana formu│a (r≤wnie┐ po╢rednio)
CTRL+SHIFT+}	wybiera wszystkie kom≤rki, kt≤rych formu│y zawieraj▒ odniesienie do aktywnej kom≤rki (r≤wnie┐ po╢rednio)

Przywi▒zywanie tekstu

Rys. 1

z kom≤rki do Okienka tekstowego Mo┐emy spowodowaµ, ┐e okienko tekstowe, kt≤re tworzy siΩ narzΩdziem pokazanym na rys. 1 bΩdzie pobieraµ warto╢µ z kom≤rki. W tym celu po narysowaniu okienka tekstowego nale┐y je wybraµ, wcisn▒µ klawisz F2 i wpisaµ formu│Ω =adres kom≤rki. Zmiany w kom≤rce bΩd▒ powodowaµ aktualizacjΩ okienka tekstowego.

Wi▒zanie obszaru arkusza do okienka graficznego

Rys. 2

To jest naprawdΩ bardzo u┐yteczna choµ ma│o znana opcja. Polega ona na utworzeniu "fotografii" wybranego zakresu kom≤rek jako bitmapy. Aby sfotografowaµ obszar kom≤rek, trzeba je wybraµ, a nastΩpnie klikn▒µ narzΩdzie pokazane na rys. 2, zwane kamer▒. NastΩpnie wystarczy klikn▒µ w dowolne miejsce arkusza i mamy gotow▒ fotografiΩ zakresu kom≤rek arkusza, kt≤r▒ mo┐emy teraz przeci▒gaµ w dowolne miejsce, albo wrΩcz wykopiowaµ z arkusza na zewn▒trz, np. do programu graficznego lub edytora tekst≤w i wykorzystaµ jako ilustracjΩ. úatwo siΩ domy╢liµ, ┐e narzΩdzie to jest nieocenione przy tworzeniu wszelkich drukowanych instrukcji dla u┐ytkownik≤w Excela. Okienkiem z fotografi▒ mo┐na wywo│aµ "efekt lusterka" przez przeci▒gniΩcie go nad fotografowany zakres û wtedy okienko fotografuje samo siebie i powstaje pΩtla. Dotychczas nie zajmowali╢my siΩ najciekawsz▒ stron▒ Excela, czyli programowaniem w excelowym Visual Basicu (VB). W czterech kolejnych numerach ENTER-a zapoznamy siΩ z programem Visual Basic 5.0 dla aplikacji 32-bitowych, kt≤ry stanowi ╢rodowisko programistyczne pozwalaj▒ce na tworzenie pe│nowarto╢ciowych aplikacji bez konieczno╢ci korzystania z jakiejkolwiek platformy typu Excel czy Access.

Je┐eli kto╢ z Pa±stwa ma jakie╢ pytania lub uwagi zwi▒zane z kursem Excela, proszΩ kierowaµ je pod adres:
piotr_pisarewicz@yr.com.
ChΩtnie odpowiem na ka┐d▒ korespondencjΩ.

(c) Copyright LUPUS