FreshMail.pl

Jak stworzyć własną
funkcję w Excelu?

MS Excel proponuje bardzo ciekawą możliwość tworzenia i wykorzystywania samodzielnie stworzonych funkcji. Wydawać by się mogło, że sam arkusz oferuje wystarczającą liczbę możliwości, które są w stanie spełnić oczekiwania użytkowników. Jednak niejednokrotnie zastosowanie tzw. własnej funkcji pozwala wygodniej posługiwać się Excelowymi formułami. Sprawdź, o co w nich chodzi i jak, krok po kroku, stworzyć takie rozwiązanie. 

Czy znasz funkcję WYSZUKAJ.PIONOWO?

Jeżeli odpowiedź na powyższe pytanie brzmi „tak”, to mam kolejne: Czy też irytuje Cię to, że używając tej funkcji, nie możesz pobrać danych z lewej strony przeszukiwanej kolumny?

Moja rada: przestań się irytować. Napisz własne wyszukaj pionowo. Dzisiaj przygotowałem dla Ciebie, krótki i prosty temat, czyli pisanie własnych funkcji z wykorzystaniem języka VBA.

Czym jest własna funkcja w Excelu?

Jest to rodzaj makra, który działa jak wbudowane w Excela formuły, czyli tak jak znane Ci funkcje: SUMA, JEŻELI, czy chociażby wspomniane wcześniej WYSZUKAJ.PIONOWO.

Działają tak… Czyli dokładnie jak?

Co charakteryzuje funkcje użytkownika?

Cechy funkcji użytkownika:

 Używa się ich tak, jak funkcji wbudowanych, wprowadzając w komórki arkusza znak „=”, a następnie podaje nazwę (lub wybiera z listy podpowiedzi).
 Funkcje przeliczają się z każdą zmianą argumentów (danych wejściowych). Nie musisz ich uruchamiać tak jak makr.
 Funkcje dostępne są w każdym arkuszu, ale tylko w pliku, w którym zostały napisane.
 Pisze się je w modułach edytora VBA.
 Każda funkcja musi mieć przynajmniej jeden argument.

Zalety stosowania własnych funkcji:

 Zastąpienie długich, zagnieżdżonych funkcji, prostymi bardziej wydajnymi
 Automatyzacja pracy
 Możliwość tworzenia funkcji pomocniczych do funkcji logicznych i wyszukiwania
 W wielu przypadkach mniej skomplikowane niż zwyczajne makro
 Dynamiczne odświeżanie wyników

Wady funkcji użytkownika:

  Nie są dostępne globalnie – możesz używać funkcji tylko w pliku, w którym znajduje się moduł z kodem źródłowym. Żeby użyć funkcji w innym arkuszu, musiałbyś mieć stale otwarty plik z kodem źródłowym i wybierać ją z poziomu okna Wstaw funkcję.
 Są pozbawione paska podpowiedzi. Aby zobaczyć składnię i opis argumentów, musisz wejść w okno kreatora funkcji. Ikonka fx obok paska formuł.

Jak stworzyć własną funkcję w Excelu?

Przygotuj pusty moduł w edytorze VBA i śledź poniższe kroki.

Pisanie funkcji rozpoczynamy od zastosowania słowa kluczowego Function. Następnie musisz zapisać nazwę swojej funkcji. Podobnie jak w zwyczajnych makrach, nazwa nie może zawierać spacji. Zamiast tego musisz użyć tzw. podłogi „_”. Nie sugeruj się też tym, że funkcje wbudowane mają w nazwie kropki. Funkcje użytkownika zostały pozbawione tego przywileju. Ja nazwałem moją funkcję NOWE_WYSZUKAJ_PIONOWO.
Function NOWE_WYSZUKAJ_PIONOWO() 

Własne WYSZUKAJ.PIONOWO – instrukcja krok po kroku

W nawiasie po nazwie funkcji musimy zadeklarować argumenty, czyli zestaw danych wejściowych, jakie musi podać użytkownik. To nic innego jak dane, które podajesz i oddzielasz średnikami wewnątrz nawiasów funkcji arkuszowych.

Aby zadeklarować argument, nie potrzebujesz słowa kluczowego Dim, tak jak w przypadku zmiennych. Wystarczy podać nazwę argumentu, a następnie po słowie kluczowym As podać typ danych. Zacznijmy od zadeklarowania trzech argumentów.
  Function NOWE_WYSZUKAJ_PIONOWO (Szukana_wartość As Variant, Szukaj_zakres As Range, Kolumna As Range) 

Często pewne argumenty funkcji arkuszowych zawarte są wewnątrz nawiasów kwadratowych, jak np.: ostatni argument funkcji wyszukaj pionowo – [przeszukiwany_zakres]. Taki zapis oznacza, że dany argument jest opcjonalny, czyli jeżeli go nie podasz, zostanie nadana mu wartość domyślna. Pisząc własną funkcję, również możesz skorzystać z argumentów opcjonalnych. Ich deklarację rozpoczynamy od słowa kluczowego „Optional”. Kolejne kroki to nadanie nazwy i typu danych. Aby zapisać wartość domyślną dla argumentu wystarczy, że użyjesz znaku „=” po typie danych i po prostu ją wprowadzisz.
 Function NOWE_WYSZUKAJ_PIONOWO(Szukana_wartość As Range, Szukaj_zakres As Range, kolumna As Range, Optional Format_danych As Byte = 0

W tej chwili nasza funkcja ma 4 argumenty, w tym jeden opcjonalny.

Dane, które będą przechowywane w poszczególnych argumentach:

 Szukana_wartość – będzie to dokładnie taki sam argument, jak w przypadku wyszukaj pionowo. Będziesz mógł tu podać komórkę z szukaną wartością lub zwyczajnie ją wpisać.
 Szukaj_zakres – tu będziesz podawał zakres, w którym znajduje się szukana wartość.
 Kolumna – komórka lub kolumna danych, w której znajduje się wartość, jaką chcesz pozyskać.
 Format_danych ­– argument opcjonalny. Umożliwi nam dodanie mechanizmu zmiany formatu danych zwracanych przez funkcję.

Mechanizm działania funkcji

Funkcja zacznie od znalezienia pozycji, według kryteriów podanych w argumentach. Poniższe dwie linie kodu stanowią cały mechanizm szukania.
 Dim pozycja As Range
Po pierwsze musisz zadeklarować dodatkową zmienną, w której przechowamy wynik wyszukiwania. Zmienna ma typ danych Range, więc pobierze cały zakres tak, abyś w mechanizmie zwracania wartości, mógł wykorzystać numer wiersza tej pozycji i połączyć go z numerem kolumny dla komórki podanej w argumencie kolumna. Taki zabieg, pozwoli nam wyszukiwać dowolne wartości zarówno po prawej, jak i lewej stronie zakresu wyjściowego. Pozwoli Ci też swobodniej przeciągać formuły, ponieważ używasz odwołania do komórki, a nie tak jak w przypadku wyszukaj pionowo indeksu kolumny. Nie ogranicza Cię zakres i kolejność. Najważniejszą zaletą takiego rozwiązania jest odporność na zmianę struktury tabeli. Indeksy kolumn w wyszukaj pionowo są przypisywane na sztywno i jeżeli do zakresu, w którym działa taka funkcja, dodasz kolumnę, przestanie ona działać lub zwróci błędne wyniki. Oczywiście zamiast przypisywać indeks ręcznie, mógłbyś użyć pomocniczych wierszy z numerami i użyć odwołania do komórek. Nie zawsze jest to możliwe, ponieważ mógłbyś w ten sposób popsuć strukturę tabeli, pogorszyć jej wizualne aspekty lub utrudnić wprowadzanie danych. W pliku, który dziś dla Ciebie przygotowałem, pokazałem wariant z wierszami pomocniczymi. Wracając do naszej najważniejszej zalety, eliminuje ona konieczność używania wszystkich półśrodków, ponieważ bazujemy na indeksie kolumny, do której odwołania użyłeś.

 Set pozycja = Szukaj_zakres.Find(What:=Szukana_wartość, MatchCase:=True) 

Zauważ, że nie używamy żadnych pętli. Nie wertujemy wszystkich komórek w arkuszu, przez co nasza funkcja nie będzie mocno obciążała komputera. Oczywiście, jeżeli użyjesz jej w 10 arkuszach na 10 000 pozycji, Twoja stacja robocza będzie musiała zaangażować więcej zasobów do pracy i odczujesz spowolnioną pracę arkusza.

Co do samej składni przed nazwą zadeklarowanej zmiennej używamy słowa kluczowego Set, ponieważ będą tam przechowywane dane typu Range. Mam nadzieję, że pamiętasz, dlaczego przed tym typem zmiennej musimy stosować słowo Set. Jeżeli nie, możesz odświeżyć tę wiedzę w pierwszym artykule poświęconym programowaniu w VBA.

Następnie używamy metody Find dostępnej dla zmiennych typu Range. Tej metody możemy użyć tylko na zdefiniowanym zakresie. Wywołujemy ją dostawiając kropkę na końcu nazwy zmiennej lub tak jak w naszym przypadku argumentu funkcji – Szukaj_zakres.Find. W kolejnym kroku musimy podać parametry metody. Zauważ, że po wpisaniu pierwszego nawiasu (o ile wszystkie poprzednie kroki wykonałeś poprawnie), pojawi się lista, parametrów, jakie przyjmuje instrukcja Find.

Jak widzisz jedyny argument, który potrzebujesz podać, to wartość, którą chcesz odszukać. Pozostałe argumenty są opcjonalne, o czym świadczą nawiasy kwadratowe. Istnieją dwa sposoby, przypisywania wartości poszczególnym parametrom.

 Możesz oddzielać je przecinkami i jeżeli chcesz pozostawić jakiś argument w opcji domyślnej, po prostu nie wpisuj w jego miejscu nic.

 Zdecydowanie rozsądniejszą opcją jest zapisanie konkretnych odwołań do parametrów metody. Robi się to poprzez dodanie „:=” po nazwie argumentu – MatchCase:= True

Opcjonalna konwersja na inne typy danych 

Użyjemy teraz instrukcji, która w zależności od wybrane opcji, zwróci w wyniku sformatowane dane. Będzie się ona opierać na naszym opcjonalnym argumencie, więc cała funkcjonalność również będzie opcjonalna.

Jak możesz wnioskować z powyższego fragmentu kodu, mamy 5 wariantów danych, które nasza funkcja może zwrócić:

0 – opcja domyślna. Format ogólny,
1 – liczba całkowita,
2 – liczba zmiennoprzecinkowa podwójnej precyzji,
3 – tekst,
4 – Data w formacie – 01.01.1900.

 WAŻNE!

Jak widzisz, wynik zwracasz do zmiennej z taką samą nazwą jak nazwa funkcji. Nazwa funkcji jest jednocześnie właśnie taką zmienną przechowującą wynik formuły. Jest to sprawa, którą musisz bezwzględnie zapamiętać.

Co do samego wyniku, spójrz proszę na wariant podstawowy:

  NOWE_WYSZUKAJ_PIONOWO = Cells(pozycja.Row, Kolumna.Column).Value 

Instrukcję cells miałeś już okazję poznać, więc mam nadzieję, że wiesz, jak działa. Ta prosta linijka kodu to klucz do eliminacji wad wyszukaj pionowo. Odwołujemy się zawsze do wiersza znalezionej pozycji i do numeru kolumny podanej w argumentach komórki.

Tak jak makra kończyły się słowem klucz End Sub, tak i funkcje należy zakończyć zasadniczym zwrotem End Function.

Twoja funkcja jest już gotowa do użycia, jednak pojawia się jeszcze jeden malutki problem. Ty wiesz, jak jej użyć, bo jesteś jej autorem, ale skąd inni użytkownicy mają wiedzieć jak zastosować daną funkcję?

Jak widzisz, w oknie edycji funkcji nie ma żadnych podpowiedzi. Tak samo, próżno szukać paska argumentów przy wpisywaniu funkcji.

Muszę Cię zmartwić, ponieważ paska postępów nie da się dodać z poziomu programowania VBA. Możesz jednak uzupełnić okno edycji o pomocne opisy.

Aby to zrobić, musisz wykorzystać instrukcję Application.MacroOptions. Funkcja ta ma na celu właśnie uzupełnienie okna edycji funkcji o niezbędne teksty pomocy.

Elementy instrukcji Application.MacroOptions:

 Macro – nazwa makra – przechowywana jako tekst;
 Description – opis makra, który wyświetli się po wejściu w okno edycji funkcji;
 Category – kategoria, do której przypisana zostanie dana funkcja:

|1| Finansowe
|2| Daty i czasu
|3| Matematyczne
|4| Statystyczne
|5| Wyszukiwanie
|6| Bazy danych

|7| Tekstowe
|8| Logiczne
 |9| Informacyjne
|10| Komendy
|14| Zdefiniowane przez użytkownika
|15 i dalej| Własne kategorie

 ArgumentDescriptions – opisy poszczególnych argumentów. Tutaj musisz użyć instrukcji Array, która stworzy tablicę danych.
Array (1 element, 2 element, 3 element, …, n element) – pamiętaj, że jeżeli wrzucasz do tablicy tekst, np: imiona, musisz umieszczać je w cudzysłowie

Taką instrukcję wystarczy uruchomić raz, używając klawisza F5 lub przycisku Run w edytorze VBA.

To wszystko, co musisz wiedzieć o pisaniu własnych funkcji w Excelu. W przygotowanym na dzisiaj pliku znajdziesz jeszcze trzy przykładowe funkcje, które mam nadzieje zainspirują Cię do jeszcze mądrzejszej pracy. Jak zwykle opisałem dla Ciebie kod wewnątrz edytora VBA.

Jakie problemy udało Ci się rozwiązać? W jakich sytuacjach planujesz wykorzystać napisane przez siebie funkcje?
Napisz poniżej, jestem bardzo ciekawy i chętnie włączę się w dyskusję! 

To był już 13 artykuł z naszego cyklu poświęconego narzędziom Excel na produkcji.
Uważam, że udało nam się razem przejść przez naprawdę dużą porcję wiedzy. Dlatego w przyszłym tygodniu podsumujemy wszystkie te wiadomości i podam Ci kilka, moim zdaniem, najskuteczniejszych sposobów nauki pracy w Excelu.

Do zobaczenia!

Zobacz pozostałe artykuły dotyczące opracowania narzędzi Excel:

Wykonywanie operacji na wielu arkuszach Excel jednocześnie

Intro to Excel | Szkolenie Excel | Wstęp do szkolenia

Pod tym artykułem znajdziesz dodatkowe przykłady własnych funkcji, które powinny zainspirować Cię do tworzenia własnych rozwiązań.

Autor:
Marcin Wawer
Senior Excel Design Developer
mwawer@leanactionplan.pl

Pytanie lub komentarz? Daj nam znać!

Wszystko co tworzymy jest po to, aby pomóc Ci rozwiać się nie tylko w na drodze zawodowej, ale i w życiu prywatnym.

Będę Ci wdzięczny za każdy komentarz. Napisz proszę, czy podobał Ci się ten tekst. A może masz pewne wątpliwości lub zastrzeżenia? Chętnie wdamy się z Wami w dyskusję i odpowiemy na wszystkie pytania.

Mamy do Ciebie jeszcze serdeczną prośbę. Jeśli uważasz ten wpis za wartościowy, to podziel się nim ze znajomymi, którym mógłby pomóc sprawniej realizować swoje cele prywatne lub zawodowe.

Pamiętaj –  Pracujmy mądrzej, a nie więcej.

PLIKI DO POBRANIA

SPRAWDŹ, JAKIE PROBLEMY MOŻESZ ROZWIĄZAĆ STOSUJĄC PRZYGOTOWANE WŁASNE FUNKCJE

Więcej plików do pobrania.

     ZOBACZ WIĘCEJ:

2018-06-26T15:51:30+00:00

Dodaj komentarz

avatar
  Subscribe  
Powiadom o

Spokojnie, jestem tutaj, żebyś nie przegapił czegoś ważnego.

Informacja o gotowych do użycia plikach Excel, szablonach wykresów i prezentacji, raportach w formie pdf, zupełnie za darmo, prosto na Twoją skrzynkę.

Chcę otrzymywać wiadomości (w każdej chwili możesz zrezygnować).

FreshMail.pl
 

Potrzebujesz więcej informacji?

Wartościowe artykuły i gotowe do użycia pliki bezpośrednio na skrzynkę e-mailową?    

FreshMail.pl