Tworzenie niestandardowych funkcji w programie Excel

Uwaga:  Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. 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. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

Chociaż program Excel zawiera wiele funkcji wbudowanych arkusza, prawdopodobnie oznacza to, że nie ma funkcji dla każdego typu obliczeń, które możesz wykonać. Projektanci programu Excel prawdopodobnie nie przewiduje się potrzeby obliczeń każdego użytkownika. Zamiast tego programu Excel oferuje możliwość tworzenia niestandardowych funkcje, które zostały omówione w tym artykule.

Funkcje niestandardowe, podobnie jak makra, za pomocą języka programowania Visual Basic for Applications (VBA). Różnią się od makr na dwa sposoby znaczące. Najpierw mogą używać funkcji procedury zamiast procedury Sub . Oznacza to, że na początku instrukcję Funkcja zamiast instrukcji Sub i zakończenia End Function zamiast End Sub. Drugi ich wykonywanie obliczeń zamiast podjęciem działań. Niektóre rodzaje instrukcji, takich jak instrukcje, które Zaznaczanie i formatowanie zakresów, są wykluczone z funkcji niestandardowych. W tym artykule dowiesz się, jak tworzyć i za pomocą funkcji niestandardowych. Aby utworzyć funkcje i makra, Praca z Edytora Visual Basic (VBE), który zostanie otwarty w nowym oknie innych niż w programie Excel.

Załóżmy, że firma prowadzi rabat ilość 10 procent w sprzedaży produktu podany kolejności dotyczy więcej niż 100 sztuk. W sekcjach zostanie przedstawiona funkcji do obliczania ten rabat.

W poniższym przykładzie pokazano formularz zamówienia, zawierającym każdego elementu, ilość, cena, rabat (jeśli istnieje), a wynikowa cena rozszerzona.

Przykładowy formularz zamówienia bez funkcji niestandardowej

Aby utworzyć niestandardowe funkcji RABAT w tym skoroszycie, wykonaj następujące kroki:

  1. Naciśnij Klawisze Alt + F11, aby otworzyć Edytor Visual Basic (na komputerze Mac, naciśnij klawisz FN + ALT + F11 ), a następnie kliknij pozycję Wstaw > modułu. Nowe okno modułu pojawia się po prawej stronie edytora Visual Basic.

  2. Skopiuj i wklej następujący kod 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 zwiększyć czytelność kodu, służy klawisz Tab do tworzenia wcięć. Wcięcie dotyczy tylko korzyści i jest opcjonalne, jak kod spowoduje uruchomienie z lub bez niego. Po wpisaniu wciętym wierszu edytora Visual Basic założono, że do następnego wiersza będzie podobnie wcięciami. Aby przenieść (oznacza to, że z lewej strony) znak jeden tabulator, naciśnij klawisze Shift + Tab.

Teraz możesz korzystać z nowych funkcji RABAT. Zamknij Edytor Visual Basic, zaznacz komórkę G7 i wpisz następujące polecenie:

=DISCOUNT(D7,E7)

Program Excel oblicza rabat 10 procent w 200 jednostek u 47.50 $ na jednostkę i zwraca wartość $950.00.

W pierwszym wierszu kod języka VBA, funkcja DISCOUNT(quantity, price), należy wskazać, że funkcja RABAT wymaga dwóch argumentów, Ilość i Cena. Podczas połączenia funkcji w komórce arkusza, musi zawierać tych dwóch argumentów. W obrębie formuły = DISCOUNT(D7,E7), D7 jest argumentem Ilość i E7 jest argumentem ceny . Teraz możesz skopiować formułę RABAT do G8:G13 w celu uzyskania wyników, jak pokazano poniżej.

Przypatrzmy się interpretowanie tej procedury funkcji programu Excel. Po naciśnięciu klawisza Enter, program Excel wyszukuje nazwę RABATU w bieżącym skoroszycie i znajduje się, że jest to funkcja niestandardowego w VBA module. Nazwy argumentów ujęte w nawiasy, Ilość i Cenasymboli zastępczych dla wartości, które podstawą obliczeń rabat.

Przykładowy formularz zamówienia przy użyciu funkcji niestandardowych

Jeśli instrukcji w bloku następujący kod sprawdza, czy w argumencie Ilość i określa, czy liczba sprzedanych jednostek 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 jednostek jest większa niż lub równa 100, VBA wykonuje następującą instrukcję, która Mnoży wartość Ilość przez wartość Cena , a następnie mnoży wynik przez 0,1:

Discount = quantity * price * 0.1

Wynikiem jest przechowywana jako zmiennej Rabat. Instrukcja języka VBA, przechowującego wartości w zmiennej jest nazywany instrukcji przypisania , ponieważ oblicza wyrażenie po prawej stronie znaku równości i przypisuje wynik do nazwy zmiennej po lewej stronie. Zmienna Rabat ma taką samą nazwę jak procedurę funkcji, więc wartości przechowywanej w zmiennej są zwracane do arkusza w formule, która o nazwie funkcji RABAT.

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

Discount = 0

Na koniec następująca instrukcja zaokrągla wartość przypisana do zmiennej Rabat do dwóch miejsc dziesiętnych:

Discount = Application.Round(Discount, 2)

Języka VBA nie ma żadnych funkcji ZAOKR, ale nie w programie Excel. Aby użyć ROUND w niniejszych zasadach, możesz stwierdzić, VBA szukać metodę Round (funkcja) w obiekcie aplikacji (Excel). Można to zrobić przez dodanie wyrazu aplikacji przed tym wyrazem ZAOKR. W dowolnym momencie możesz uzyskać dostęp do funkcji programu Excel z poziomu modułu języka VBA, użyj następującej składni.

Funkcja niestandardowa należy uruchomić instrukcję funkcji i kończy się instrukcji End Function. Oprócz nazwy funkcji instrukcji funkcji zazwyczaj określa co najmniej jeden argument. Można jednak utworzyć funkcji bez argumentów. Program Excel zawiera kilka wbudowanych funkcji — LOS i teraz, na przykład, które nie używają argumenty.

Po instrukcji Function procedurę funkcji zawiera jedną lub więcej instrukcji języka VBA, podejmowania decyzji, które obliczeń na podstawie argumentów przekazane do funkcji. Na koniec gdzieś w procedurze funkcji należy uwzględnić instrukcję, która powoduje przypisanie wartości do zmiennej o takiej samej nazwie jak funkcja. Ta wartość jest zwracana w formule, która wywołuje funkcję.

Liczba słów kluczowych języka VBA, możesz użyć w funkcji niestandardowych jest mniejsza niż liczba można użyć makr. Funkcje niestandardowe nie są dozwolone nic robić niż zwracana wartość formuły w arkuszu lub wyrażenie zastosowane w innej makr języka VBA lub funkcji. Liczba funkcji niestandardowych nie można na przykład rozmiar okien, Edytuj formułę w komórce lub zmiana czcionki, koloru lub deseniu opcje tekstu w komórce. Jeśli dołączysz kodu "działania" tego rodzaju procedurę funkcji, funkcja zwraca wartość błędu #VALUE!. błąd.

Akcja jeden wykonać procedurę funkcji (z wyjątkiem wykonywanie obliczeń) jest wyświetlane okno dialogowe. Za pomocą instrukcji InputBox funkcji niestandardowej jako sposób wprowadzania wprowadzenie do wybranego użytkownika wykonywania funkcji. Za pomocą instrukcji MsgBox jako sposób przekazywania informacji do użytkownika. Możesz też użyć niestandardowych okien dialogowych, formularze użytkownika, ale jest to temat poza zakres to wprowadzenie.

Nawet proste makra i funkcje niestandardowe może być trudne do odczytania. Można wprowadzać łatwiejszy do zrozumienia, wpisując tekst objaśniający w formularzu Komentarze. Możesz dodać komentarze, poprzedzając tekst objaśniający z apostrofem. Na przykład w poniższym przykładzie pokazano funkcji RABAT z komentarzami. Dodawanie komentarzy, takie jak te ułatwia Tobie lub innym użytkownikom Obsługa kod języka VBA w miarę upływu czasu. Jeśli chcesz wprowadzić zmiany w kodzie w przyszłości, konieczne jest łatwiejsze czasu opis pierwotnie czy.

Przykład funkcji języka VBA z komentarzami

Apostrof informuje Ignoruj wszystko w prawo, w tym samym wierszu, aby można było tworzyć komentarze w wierszach samodzielnie i po prawej stronie wiersze zawierające VBA kod w programie Excel. Może rozpocząć stosunkowo długich blok kodu jest dołączony komentarz objaśniający celowi ogólne, a następnie użyj komentarzy wewnętrznych do instrukcji poszczególnych dokumentów.

Innym sposobem dokumentu z makr i funkcje niestandardowe jest nadać im nazwy opisowe. Na przykład zamiast nazwy makra etykiety, możesz może nadaj mu nazwę MonthLabels dokładniej przeznaczenie, które pełni makro. Przy użyciu nazwy opisowe dla makr i funkcje niestandardowe jest szczególnie pomocne po utworzeniu wiele procedur, szczególnie jeśli Tworzenie procedury, które mają podobne, ale nie identyczne celów.

Jak dokument z makr i funkcje niestandardowe to kwestia osobistych preferencji. Co to jest ważne jest przyjęcie niektóre metody dokumentacji i spójne używanie.

Aby użyć funkcji niestandardowej, musi być otwarty skoroszyt zawierający moduł, w którym został utworzony funkcję. Jeśli ten skoroszyt nie jest otwarty, możesz uzyskać #NAME? Błąd podczas próby korzystania z funkcji. Jeśli odwołanie dotyczy funkcji w innym skoroszycie, musi poprzedzać nazwę funkcji o nazwie skoroszytu, w której znajduje się funkcja. Na przykład jeśli tworzysz funkcji o nazwie RABAT w skoroszycie o nazwie Personal.xlsb i tego wywołaj z innego skoroszytu, należy wpisać =personal.xlsb!discount(), nie tylko =discount().

Możesz zapisać sobie niektóre naciśnięć klawiszy (i możliwych błędów pisowni), wybierając funkcje niestandardowe w oknie dialogowym Wstawianie funkcji. Funkcje niestandardowe są wyświetlane w kategorii zdefiniowane przez użytkownika:

okno dialogowe Wstawianie funkcji

Łatwiejsze sposobem udostępnienia swojego funkcje niestandardowe na zawsze należy przechowywać je w innym skoroszycie, a następnie zapisz ten skoroszyt jako dodatek. Można następnie udostępnić dodatku przy każdym uruchomieniu programu Excel. Oto jak to zrobić:

  1. Po utworzeniu zadania, 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ę listy rozwijanej Zapisz jako typ i wybierz Dodatek programu Excel. Zapisz skoroszyt w obszarze rozpoznać nazwę, na przykład MyFunctionsw folderze Dodatki . Okno dialogowe Zapisywanie jako będzie zaproponować tego folderu tak wszystko, co należy zrobić to 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 przycisk Opcje programu Excel.

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

  5. Na liście rozwijanej Zarządzaj wybierz pozycję Dodatki programu Excel. Następnie kliknij przycisk Przejdź.

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

    Okno dialogowe Dodatki

  1. Po utworzeniu zadania, kliknij pozycję plik > Zapisz jako.

  2. W oknie dialogowym Zapisywanie jako Otwórz listę listy rozwijanej Zapisz jako typ i wybierz Dodatek programu Excel. Zapisz skoroszyt w obszarze rozpoznać nazwę, na przykład MyFunctions.

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

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

Po wykonaniu tych czynności niestandardowe funkcje będą dostępne każdym uruchomieniu programu Excel. Jeśli chcesz dodać do biblioteki funkcji, wróć do edytora Visual Basic. Jeśli Szukaj w Edytorze Visual Basic Project Explorer pod nagłówkiem projekt VBA: zobaczysz module o nazwie po pliku dodatku. Dodatek ma rozszerzenie xlam.

Moduł nazwany w języku VBA

Dwukrotne kliknięcie tego modułu w programie Project Explorer powoduje edytora Visual Basic, aby wyświetlić kod funkcji. Aby dodać nową funkcję, umieść punkt wstawiania po wykonaniu instrukcji End Function kończącego funkcja last w oknie kodu i zacznij pisać. Można utworzyć wiele funkcji potrzebne w ten sposób, a zostaną zawsze będzie dostępny w kategorii zdefiniowane przez użytkownika w oknie dialogowym Wstawianie funkcji.

Tej zawartości został pierwotnie autorem znacznik Rozjaśnianie i Craig Stinson jako część ich książki Microsoft Office Excel 2007 Inside Out. Ponieważ została zaktualizowana do zastosowania do także nowszych wersjach 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.

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.

×