Archive for the ‘Excel 2003’ Category

Odwoływanie się do komórek w innych arkuszach

Friday, June 22nd, 2012

Pierwotnie artykuł miał mieć inny tytuł, ale chciałem wrócić jeszcze do tego co ostatnio napisałem o nazywaniu komórek i zakresów.

Otóż jeśli chcemy przykładowo w komórce C9 arkusza Arkusz2 odnieść się do komórki AX11 w arkuszu Arkusz1 to musimy napisać w komórce C9 coś takiego:

=Arkusz1!AX11

Z kolei jeśli zastosujemy nazwę dla komórki to piszemy po prostu tę nazwę. Przykładowo komórkę AX11 w arkuszu Arkusz1 nazwiemy PRĘDKOŚĆ (wielkość liter nie ma znaczenia) to w komórce C9 arkusza Arkusz2 piszemy

=PRĘDKOŚĆ

i wyświetla nam się wartość tej komórki

Ponieważ temat odwoływania do komórek jest dość szerokim tematem to szkoda się ograniczać tyllko do tego przypadku.
Niegdyś omawiałem na tej stronie temat tworzenia listy rozwijanej na podstawie wartości w zaznaczonej grupie komórek.

sprpopr1.png

Formalnie w Excelu nosi to nazwę sprawdzanie poprawności

Jeśli chodzi o najnowszą wersję Excela (2010) to nie ma żadnych ograniczeń aby dane do listy pochodziły z innego arkusza. Po prostu przy wyborze źródła danych zaznaczamy inny arkusz i na nim zakres komórek. W wersji 2003 i starszych ograniczenie jest takie że dane do listy można pobrać wyłącznie z aktywnego w danym momencie arkusza.

chyba że …
No właśnie

…zastosujemy nazwę dla pożądanego zakresu komórek i do tej nazwy w okienku sprawdzania poprawności się odniesiemy pisząc:

=MOJA_NAZWA

oczywiście MOJA_NAZWA zastąp tą właściwą

Abyś to zrozumiał i mógł zastosować wystarczy że przeglądniesz poprzedni post oraz ten o sprawdzaniu poprawności

Aby sobie utrwalić zrób to najlepiej teraz

I jeszcze ostatni temat

To że nazwę przypisujemy do komórki pewnie Cię już nie dziwi, ale czy wiedziałeś(aś) że tą samą metodą możemy nazwę przypisać nie do komórki tylko do wartości.

nazwadostalej1.png

Można powiedzieć że to takie definiowanie stałej, która może mieć zasięg dla całego skoroszytu. Przykładem może być jakiś współczynnik do obliczeń matematycznych (dzięki czemu reguła ze wzorem staje się bardziej czytelna) lub stopa procentowa, może to być nawet stała tekstowa np nazwa ZUS przypisane do stałej “Zakład Ubezpieczeń Społecznych”
Jeśli to zdefiniujesz zobaczysz że w miejsce napisanego słowa ZUS podstawi się automatycznie Zakład Ubezpieczeń Społecznych

Spróbuj i przekonaj się :)

Jak utworzyć własną formułę w Excelu

Tuesday, May 1st, 2012

Do tej pory często poruszałem temat procedur Sub, które wykonywały określone zadanie po czym ich rola zwykle się kończyła lub ograniczała się do wielokrotnego wykonania sekwencji identycznych czynności. Teraz omówię sposób utworzenia własnej formuły do zastosowania w komórkach Excela. Zakładam że masz problem ze znalezieniem tej właściwej formuły lub chcesz się po prostu dowiedzieć jak to zrobić.

Istotą działania formuły jest to że zwraca ona jakąś wartość w zależności od argumentów lecz niczego nie robi z formatem komórki.
Do utworzenia formuły potrzebujemy więc utworzenia funkcji w kodzie VBA. Robi się to podobnie jak w przypadku procedur Sub z tą różnicą że zamiast wyrażenia Sub nazwa () piszemy Function nazwa () funkcję zamykamy wyrażeniem End function

Mniej więcej tak

Function mojafunkcja () as Type
kod funkcji
End Function

oczywiście zamiast słowa type wpisujemy prawidłowy typ danych jaki ma zwracać nasza funkcja np Byte albo String albo Variant itp

i jeszcze coś ważnego:
zawsze przed słowami End Function przekazujemy wynik obliczeń lub działania formuły ze zmiennej do nazwy funkcji

wygląda to tak

Function mojafunkcja (jakaśzmienna1) as Type

‘coś tam się dzieje w naszej funkcji ze zmienną jakaśzmienna1

mojafunkcja=jakaśzmienna1
End Function

Przykładowa banalnie prosta funkcja zamieniająca mile na kilometry wyglądałaby tak

Function milenakm (mile as integer) as single
milenakm=mile*1,6
End function

Kod funkcji podobnie jak kod procedur Sub umieszczamy w Module VBE
ownformula1.png

ownformula2.png

ownformula3.png

ownformula4.png

ownformula5.png

W trakcie pisania artykułu posiłkowałem się tą książką
Vademecum Walkenbacha

Formuły tablicowe

Wednesday, April 25th, 2012

Formuły tablicowe nazywane są także formułami CSE od skrótu klawiszowego (Ctrl SHIFT Enter) jakiego należy użyć w celu ich uaktywnienia.
Czy formuły tablicowe to jakiś specjalny rodzaj formuł ?
I tak i nie - są to zwykłe funkcje Excelowe, z tą różnicą że kiedy chcemy ich używać warto odnosić ją do zakresów komórek zamiast pojedynczych komórek (nie jest to konieczne). Formuły takie są traktowane przez Excela jako tablicowe dopiero po oznaczeniu ich skrótem CSE.

Formuła tablicowa zadziała oczywiście nawet wtedy gdy zwykłą formułę oznaczymy jako CSE. Z kolei gdy użyjemy zakresu komórek a nie oznaczymy reguły jako tablicowej to pojawi się błąd (na przykład taki)

cseformula2err.png

Zaletą formuł tablicowych jest to że ułatwiają lub wręcz umożliwiają pewne obliczenia, które bez nich byłyby trudne. Zajmują one dużo mniej miejsca w pliku ze względu na fakt że dana reguła tablicowa obejmuje swym zasięgiem cały wskazany zakres komórek w przeciwieństwie do zwykłych formuł, które wpisujemy z osobna dla każdej komórki (możemy też przeciągnąć lub skopiować, ale rozmiarowo na jedno wychodzi).

Najczęstsze zastosowanie formuły tablicowe znajdują w ekonomii i analizie biznesowej, ale nic nie stoi na przeszkodzie aby użyć ich do tak prozaicznych celów jak szybkie wygenerowanie tabliczki mnożenia, symulacje obliczeniowe dla matematycznych modeli układów elektrycznych, podliczanie sprzedaży w sklepie i wszelkie raporty i podsumowania. Wynik formuły tablicowej może być wyświetlany w danym zakresie lub w jednej komórce. W przypadku wyniku wyświetlanego w zakresie komórek istotną zaletą jest brak możliwości przypadkowego skasowania formuły w jednej z komórek zakresu. Pojawi się wówczas komunikat że nie można zmienić części tablicy.

cseformula3err.png

Dopiero po zaznaczeniu całości obszaru objętego formułą da się skasować zawartość.

Formuła tablicowa pozwala uniknąć stosowania obliczeń pośrednich, które często stosują początkujący w Excelu (też do niedawna nie wyobrażałem sobie że można inaczej) a od których to obliczeń zwykle “puchnie” plik skoroszytu

Sposób zapisu formuły tablicowej
Przykładowo
=SUMA(JEŻELI(A1:A10>0,A1:A10,0))
Zaznaczamy całość i używamy skrótu Ctrl Shift Enter, w wyniku tego powyższy zapis zmieni się następująco
{=SUMA(JEŻELI(A1:A10>0,A1:A10,0))}
Ręczne wpisywanie nawiasów klamrowych mija się z celem i nie przyniesie żadnych rezultatów obliczeniowych
Powyższy przykład w anglojęzycznej wersji wygląda tak
{=SUM(IF(A1:A10>0;A1:A10;0))}

Dla przykładu mamy takie dane

cseformula1.png

Zauważmy że bez obliczeń pośrednich dla każdego wiersza od A1 do A10 Formuła wyliczyła prawidłowo sumę elementów, które spełniają warunek że liczby w tych komórkach są większe od zera. Chcąc to robić “na piechotę” musielibyśmy warunek taki skopiować w wierszach od B1 do B10 a dopiero wyniki podsumować. Jest to banalnie prosty przypadek, ale jest cała masa podobnych o nieco większej złożoności, które przydają się w codziennym życiu osób zajmujących się Excelem.

To na razie tyle odnośnie formuł tablicowych. Przypuszczalnie do tego tematu jeszcze nie jeden raz wrócę.

Excel i VBA - formularz UserForm jako interfejs aplikacji excelowej

Thursday, April 19th, 2012

Czym są formularze Userform w Excelu ?

Formularze służą do budowania interfejsu użytkownika. W Excelu ilość dostępnych elementów kontrolnych jest niewielka, wystarcza to jednak do zbudowania całkiem skomplikowanych aplikacji. Do dyspozycji mamy między innymi przyciski, pola tekstowe, listy, etykiety, pola wyboru, suwaki.

Idea korzystania z przycisków jest podobna jak w przypadku przycisków uruchamiania makra, z tą różnicą że są bardziej konfigurowalne. To znaczy że we właściwościach możemy ustawić widoczność przycisku, stan zablokowania (wyszarzenia) kolor i inne.

Jak wygląda dodawanie formularza do projektu aplikacji

userform_create1.png

Po utworzeniu widzimy coś takiego:

userform_idea.png

Tworzenie projektu formularza

Kontrolki przeciągamy na formularz a następnie zmieniamy ich parametry aby w końcu przypisać do nich wykonanie określonej akcji (makra) przez podwójne kliknięcie na kontrolce i napisanie makra (makro może być też gdzie indziej napisane a my odnosimy się do niego przez podanie jego nazwy)

Dobrą praktyką jest zmiana nazw (właściwość Name) kontrolek na bardziej adekwatne do tego jaką akcję wywołują. Dla przykładu nazwałem przycisk ukrycia makra cmdHide. Nazewnictwo jest dowolne, ale nie powinniśmy stosować polskich znaków takich jak ąęćśźżó. Jeśli chodzi o właściwość Caption to tutaj nie ma żadnych przeciwwskazań do stosowania polskich znaków. Jest to opis przycisku czyli to co będzie widoczne na formularzu.

userform_kod.png

Nie wszystkie kontrolki wymagają przypisania makra
Nie wszystkie kontrolki muszą mieć przypisaną procedurę zdarzenia. Na przykład Etykieta Label ma zadanie bardziej statyczne czyli ma wyświetlać napis który zdefiniujemy we właściwości Caption, oczywiście nic nie stoi na przeszkodzie by ta właściwość uległa zmianie w trakcie działania programu. Można ją zmodyfikować za pomocą makra jeśli wymaga tego sytuacja. Podobnie jest z kontrolkami Text, Ramka, pole wyboru

Brak styczności z arkuszami w trakcie pracy z aplikacją

Choć brzmi to dziwnie można zbudować aplikację w Excelu w której prawie ani przez chwilę nie będzie widać żadnego arkusza a mimo to operacje na arkuszach będą przeprowadzane. Ma to znaczenie, kiedy chcemy utworzyć aplikację bezpieczną i szczególnie prostą w obsłudze.
Ograniczenie dostępu do arkuszy zabezpiecza (przynajmniej częściowo) przed ich modyfikacją która mogłaby doprowadzić do uszkodzenia aplikacji przez użytkownika.

Co zrobić aby po załadowaniu skoroszytu pojawiło się okno formularza
Należy wkleić następujący kod

UserForm1.Show

Należy to zrobić w określonym miejscu o czym pisałem w artykule Excel i VBA automatyczne uruchomienie makra przy otwarciu skoroszytu: sposób 2

Natomiast gdybyśmy chcieli dodatkowo ukryć okno skoroszytu przy starcie to musimy użyć następujących poleceń

ThisWorkbook.Activate
ActiveWindow.Visible = False

userform_kod3.png

Zabezpieczenie przed uszkodzeniem (zapisanie konfiguracji ustawień pasków menu i ich ukrycie)

Samo zabezpieczenie przed użytkownikiem nie wystarczy powinniśmy też uważać na swoje działania w programowaniu.
Jeśli chcemy przeprowadzać jakiekolwiek operacje odnoszące się do interfejsu excela to powinniśmy zapisać aktualny stan parametrów jakie zamierzamy zmieniać. W każdym razie należy upewnić się podczas kończenia pracy aplikacji że zmienione parametry odzyskały swój dawny status. Przywracanie ustawień interfejsu tradycyjną metodą mogłoby użytkownika naszej aplikacji doprowadzić do szewskiej pasji (mam na myśli w szczególności wersję 2010 ze wstążkami jako menu).

Mam świadomość że ten artykuł sam z siebie nie wnosi wielu konkretów dlatego mam zamiar w przyszłości opisać niektóre elementy bardziej szczegółowo i z przykładami.

W trakcie pisania artykułu posiłkowałem się tą książką
Vademecum Walkenbacha

Excel - Przycisk do uruchamiania makra - inne podejście

Thursday, March 29th, 2012

W artykule Dodawanie przycisku do uruchamiania makra w Excelu 2003 opisałem metodę przypisania przycisku (takiego jak w większości aplikacji windows) do uruchomienia makra czyli tego typu

button.png

Można jednak utworzyć dużo prościej taki przycisk w oparciu o dowolny element graficzny w excelu jak pokazane jest niżej

przycisk_do_makra1.png

Wybieramy kształt (ja wybrałem prostokąt i prostokąt z zaokrąglonymi rogami , ale można nawet te najbardziej udziwnione - jak chmurki czy gwiazdki)

przycisk_do_makra2.png

Przypisujemy makro (ja miałem napisane jedno dla przykładu)

przycisk_do_makra3.png

Pomijam oczywiście etap tworzenia makra, gdyż był on omawiany wielokrotnie w poprzednich wpisach
Zaletą tego rozwiązania jest większa estetyka i większa dowolność formatowania kształtu i koloru tła przycisku
O ile w zwykłych “windowsowych” przyciskach nie można było zmienić koloru tła a jedynie tekstu o tyle tutaj jest wielkie pole do popisu i przede wszystkim jest to rozwiązanie o wiele szybsze i bardziej intuicyjne w realizacji.

Polecam spróbować.

Książka, która może pomóc w lepszym zrozumieniu tego tematu:

Pobierz fragment