Tworzenie funkcji niestandardowych w programie Excel

Chociaż program Excel zawiera wiele wbudowanych funkcji arkusza, to nie ma on funkcji dla każdego typu obliczeń, które wykonujesz. Projektanci programu Excel nie mogą być w stanie przewidzieć potrzeby obliczania każdego użytkownika. Zamiast tego program Excel oferuje możliwość tworzenia funkcji niestandardowych, które omówiono w tym artykule.

Szukasz informacji na temat tworzenia niestandardowej funkcji JavaScript, którą można uruchamiać w programie Excel dla systemu Windows, Excel dla komputerów Mac lub Excel dla sieci Web ? Jeśli jesteś, zobacz Omówienie funkcji niestandardowychw artykule Excel.

Funkcje niestandardowe, na przykład makra, są używane w języku programowania Visual Basic for Applications (VBA) . Różnią się one od makr na dwa znaczące sposoby. Najpierw używa procedur funkcyjnych zamiast procedur podrzędnych . Oznacza to, że zaczynają się od instrukcji funkcyjnej zamiast podinstrukcji i kończą się na funkcji koniec zamiast końca. Po drugie, wykonują obliczenia zamiast podejmowania działań. Niektóre rodzaje instrukcji, takie jak instrukcje SELECT i format, są wykluczone z funkcji niestandardowych. W tym artykule dowiesz się, jak tworzyć i korzystać z funkcji niestandardowych. Aby tworzyć funkcje i makra, możesz pracować z edytorem Visual Basic (VBE), który jest otwierany w nowym oknie oddzielnie od programu Excel.

Załóżmy, że Twoja firma oferuje rabat ilościowy o wartości 10 procent na sprzedaż produktu, pod warunkiem, że zamówienie dotyczy ponad 100 jednostek. W poniższych akapitach zostanie wykazana funkcja obliczania tego rabatu.

W poniższym przykładzie pokazano formularz zamówienia z listą każdy towar, ilość, cenę, Rabat (jeśli istnieje) oraz uzyskaną cenę rozszerzoną.

Przykładowy formularz zamówienia bez funkcji niestandardowej

Aby utworzyć niestandardową opcję RABATu w tym skoroszycie, wykonaj następujące czynności:

  1. Naciśnij klawisze ALT + F11 , aby otworzyć Edytor Visual Basic (na komputerze Mac, naciśnij klawisze Fn + ALT + F11), a następnie kliknij pozycję Wstaw > module. Po prawej stronie edytora Visual Basic zostanie wyświetlone nowe okno modułu.

  2. Skopiuj poniższy kod i wklej go do nowego modułu.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Uwaga: Aby kod był bardziej czytelny, za pomocą klawisza Tab możesz wciąć wiersze. Wcięcie dotyczy tylko świadczenia, które jest opcjonalne, ponieważ kod będzie działać lub bez niego. Po wpisaniu wiersza z wcięciem Edytor Visual Basic przyjmie, że następny wiersz będzie podobnie wcięty. Aby przenieść (czyli po lewej stronie) znak tabulacji, naciśnij klawisze SHIFT + TAB.

Teraz możesz już korzystać z nowej funkcji RABATu. Zamknij Edytor Visual Basic, zaznacz komórkę G7, a następnie wpisz następujące polecenie:

= RABAT (D7, E7)

Program Excel oblicza 10-procentowy rabat na 200 jednostki na $47,50 za jednostkę i zwraca $950,00.

W pierwszym wierszu kodu VBA, Rabat funkcyjny (ilość, Cena) wskazuje, że funkcja rabat wymaga dwóch argumentów: ilość i Cena. Po połączeniu funkcji w komórce arkusza należy uwzględnić te dwa argumenty. W formule = rabat (D7, E7), D7 jest argumentem ilość , a E7 jest argumentem Cena . Teraz możesz skopiować formułę RABATu na G8: G13, aby uzyskać wyniki podane poniżej.

Załóżmy, jak program Excel interpretuje tę procedurę funkcji. Po naciśnięciu klawisza Enterprogram Excel wyszukuje Rabat nazw w bieżącym skoroszycie i stwierdza, że jest to funkcja niestandardowa w module VBA. Nazwy argumentów ujęte w nawiasy, ilość i Cenasą symbolami zastępczymi dla wartości, na podstawie których jest obliczany rabat.

Przykładowy formularz zamówienia z funkcją niestandardową

Instrukcja Jeżeli w poniższym bloku kodu sprawdza argument ilość i określa, czy liczba sprzedanych elementów jest większa niż lub równa 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Jeśli liczba sprzedanych elementów jest większa lub równa 100, program VBA wykonuje następującą instrukcję, która mnoży wartość ilościową przez wartość ceny , a następnie mnoży wynik przez 0,1:

Discount = quantity * price * 0.1

Wynik jest przechowywany jako Rabatzmienny. Instrukcja języka VBA przechowująca wartość w zmiennej jest nazywana instrukcją przypisania , ponieważ oblicza wyrażenie po prawej stronie znaku równości i przypisuje wynik do nazwy zmiennej z lewej strony. Ponieważ Rabat zmienny ma taką samą nazwę jak procedura funkcji, wartość przechowywana w zmiennej jest zwracana do formuły arkusza o nazwie funkcja rabat.

Jeśli ilość jest mniejsza niż 100, program VBA wykonuje następującą instrukcję:

Discount = 0

Na koniec Poniższa instrukcja Zaokrągla wartość zmiennej rabatowej do dwóch miejsc dziesiętnych:

Discount = Application.Round(Discount, 2)

Język VBA nie zawiera funkcji ZAOKR, ale program Excel robi to. W związku z tym, aby użyć funkcji ROUND (zapoznaj się z tą instrukcją), należy określić, że kod VBA ma szukać metody Round (funkcja) w obiekcie Application (program Excel). Możesz to zrobić, dodając aplikację Word przed wyrazami okrągłymi. Tej składni należy używać w dowolnym momencie, gdy trzeba uzyskać dostęp do funkcji programu Excel z poziomu modułu VBA.

Funkcja niestandardowa musi zaczynać się instrukcją funkcyjną i kończyć się instrukcją funkcji end. Oprócz nazwy funkcji instrukcja funkcyjna zwykle określa jeden lub więcej argumentów. Możesz jednak utworzyć działanie bez argumentów. Program Excel zawiera kilka wbudowanych funkcji — Los i teraz, na przykład, które nie używają argumentów.

Po instrukcji Function procedura zawiera co najmniej jedno instrukcje języka VBA, które podejmują decyzje i wykonuje obliczenia przy użyciu argumentów przekazanych do funkcji. Wreszcie, w ramach procedury funkcyjnej należy uwzględnić instrukcję przypisującą wartość zmiennej o takiej samej nazwie jak funkcja. Ta wartość jest zwracana do formuły, która odwołuje się do funkcji.

Liczba słów kluczowych języka VBA, których można używać w funkcjach niestandardowych, jest mniejsza niż liczba, której można używać w makrach. Funkcje niestandardowe nie mogą wykonywać żadnych innych czynności niż zwraca wartość do formuły w arkuszu lub do wyrażenia użytego w innym makrze lub funkcji języka VBA. Na przykład funkcje niestandardowe nie mogą zmieniać rozmiaru okien, edytować formuł w komórce ani zmieniać czcionek, kolorów i opcji deseni dla tekstu w komórce. W przypadku dołączenia do tego rodzaju kodu "Action" w procedurze funkcji funkcja zwraca #VALUE! .

W przypadku jednej akcji może być wyświetlane okno dialogowe (poza wykonywaniem obliczeń). Instrukcji InputBox można użyć w funkcji niestandardowej jako metody uzyskiwania danych wprowadzanych przez użytkownika wykonującego tę rolę. Instrukcja OknoKomunikatu może być używana jako sposób przekazywania informacji użytkownikowi. Możesz również użyć niestandardowych okien dialogowych lub formularzy użytkownika, ale jest to temat wykraczający poza zakres tego wprowadzenia.

Nawet proste makra i funkcje niestandardowe mogą być trudne do odczytania. Można ułatwić zrozumienie, wpisując tekst objaśniający w formie komentarzy. Komentarze są dodawane przed tekstem wyjaśniającym z apostrofem. Na przykład w poniższym przykładzie przedstawiono działanie funkcji rabat z komentarzami. Dodanie komentarzy, takich jak te, ułatwia Tobie i innym osobom prowadzenie kodu VBA w czasie przechodzenia. Jeśli chcesz wprowadzić zmianę w kodzie w przyszłości, będziesz w stanie łatwiej zrozumieć to, czego pierwotnie używasz.

Przykładowa funkcja języka VBA z komentarzami

Apostrof informuje program Excel o zignorowaniu wszystkiego po prawej stronie w tym samym wierszu, aby można było tworzyć Komentarze zarówno na wierszach, jak i po prawej stronie wierszy zawierających kod języka VBA. Możesz zacząć używać stosunkowo długich bloków kodu, dodając komentarz objaśniający jego ogólny cel, a następnie używając komentarzy wewnętrznych do dokumentowania poszczególnych instrukcji.

Innym sposobem na dokumentowanie makr i funkcji niestandardowych jest nadawanie opisowych nazw. Na przykład zamiast nazywania etykietmakr możesz nadać mu nazwę MonthLabels , aby dokładniej opisać przeznaczenie makra. Korzystanie z opisowych nazw makr i funkcji niestandardowych jest szczególnie przydatne, jeśli utworzono wiele procedur, szczególnie w przypadku tworzenia procedur o podobnych, ale nieidentycznych celach.

Sposób dokumentowania makr i funkcji niestandardowych jest kwestią osobistego uprzywilejowania. Co jest ważne, aby zastosować pewną metodę dokumentacji, i stosować ją konsekwentnie.

Aby można było użyć funkcji niestandardowej, skoroszyt zawierający moduł, w którym została utworzona, musi być otwarty. Jeśli ten skoroszyt nie jest otwarty, uzyskasz #NAME? błąd podczas próby użycia funkcji. Jeśli użytkownik odwołuje się do funkcji w innym skoroszycie, należy poprzedzić nazwę funkcji nazwą skoroszytu, w którym znajduje się ta funkcja. Jeśli na przykład w skoroszycie o nazwie Personal. xlsb zostanie utworzona funkcja o nazwie rabat, a ta funkcja jest wywoływana z innego skoroszytu, musisz wpisać = Personal. xlsb! Discount (), a nie po prostu = Discount ().

Możesz zapisać niektóre nacionięcia klawiszy (i możliwe błędy pisania), wybierając funkcje niestandardowe w oknie dialogowym Wstawianie funkcji. Funkcje niestandardowe są wyświetlane w kategorii zdefiniowane przez użytkownika:

okno dialogowe Wstawianie funkcji

Łatwiejszym sposobem na udostępnienie funkcji niestandardowych przez cały czas jest zapisanie ich w osobnym skoroszycie, a następnie zapisanie tego skoroszytu jako dodatku. Po uruchomieniu programu Excel dodatek może być dostępny. Poniżej opisano, jak to zrobić:

  1. Po utworzeniu potrzebnych funkcji kliknij pozycję plik > Zapisz jako.

    W Excel 2007 kliknij przycisk pakietu Microsoft Office, a następnie kliknij polecenie Zapisz jako

  2. W oknie dialogowym Zapisywanie jako Otwórz listę rozwijaną Zapisz jako typ i wybierz pozycję dodatek programu Excel. Zapisz skoroszyt pod rozpoznawalną nazwą, na przykład myFunction, w folderze Dodatki . W oknie dialogowym Zapisywanie jako zostanie zaproponowany ten folder, więc wystarczy zaakceptować lokalizację domyślną.

  3. Po zapisaniu skoroszytu kliknij pozycję plik > Opcje programu Excel.

    W Excel 2007 kliknij przycisk pakietu Microsoft Office, a następnie kliknij pozycję Opcje programu Excel.

  4. W oknie dialogowym Opcje programu Excel kliknij kategorię Dodatki .

  5. Z listy rozwijanej Zarządzaj wybierz pozycję Dodatki programu Excel. Następnie kliknij przycisk Przejdź .

  6. W oknie dialogowym Dodatki zaznacz pole wyboru obok nazwy użytej do zapisania skoroszytu, jak pokazano poniżej.

    Okno dialogowe Dodatki

  1. Po utworzeniu potrzebnych funkcji kliknij pozycję plik > Zapisz jako.

  2. W oknie dialogowym Zapisywanie jako Otwórz listę rozwijaną Zapisz jako typ i wybierz pozycję dodatek programu Excel. Zapisz skoroszyt pod rozpoznawalną nazwą, na przykład myFunction.

  3. Po zapisaniu skoroszytu kliknij pozycję narzędzia > Dodatki programu Excel.

  4. W oknie dialogowym Dodatki wybierz przycisk Przeglądaj, aby znaleźć swój dodatek, kliknij przycisk Otwórz, a następnie zaznacz pole wyboru obok dodatku w polu dostępne dodatki .

Po wykonaniu tych czynności niestandardowe funkcje będą dostępne przy każdym uruchomieniu programu Excel. Jeśli chcesz dodać do biblioteki funkcji, Wróć do edytora Visual Basic. Jeśli przeszukasz Eksploratora projektu w języku Visual Basic pod nagłówkiem VBAProject, zobaczysz moduł o nazwie po pliku dodatku. Dodatek będzie miał rozszerzenie. xlam.

Moduł nazwany w języku VBA

Dwukrotne kliknięcie tego modułu w Eksploratorze projektu powoduje wyświetlenie kodu funkcji w edytorze Visual Basic. Aby dodać nową funkcji, umieść punkt wstawiania po instrukcji End, która kończy wykonywanie ostatniej funkcji w oknie kodu, a następnie zacznij pisać. W ten sposób można utworzyć dowolną liczbę funkcji, które będą zawsze dostępne w kategorii zdefiniowane przez użytkownika w oknie dialogowym Wstawianie funkcji .

Ta zawartość została pierwotnie utworzona przez oznaczenie rozjaśnienia i Craiga Stinson w ramach swojej książki Microsoft Office Excel 2007. Aktualizacja została również zastosowana do nowszych wersji programu Excel.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Uwaga:  Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×