Jak utworzyć funkcję zdefiniowaną przez użytkownika w programie Microsoft Excel

Anonim

W tym artykule dowiemy się, jak tworzyć funkcje zdefiniowane przez użytkownika w programie Microsoft Excel za pomocą VBA.

Funkcja zdefiniowana przez użytkownika: - Microsoft Excel ma już wiele funkcji, ale wciąż każdy ma inne wymagania, sytuację, możemy stworzyć własną funkcję zgodnie z wymaganiem zwanym funkcją zdefiniowaną przez użytkownika. Możemy używać funkcji zdefiniowanej przez użytkownika, podobnie jak innych funkcji w programie Excel.

Poniżej znajdują się tematy, dla których stworzymy funkcję zdefiniowaną przez użytkownika:

1). Jak policzyć liczbę słów w komórce lub zakresie?

2). Jak wyodrębnić słowo ze zdania lub komórki w programie Excel?

3). Jak stworzyć formułę dla ISO?

4). Jak poznać nazwę arkusza roboczego i skoroszytu za pomocą VBA?

5). Jak wyodrębnić pierwsze i ostatnie słowo z komórki w programie Excel?

Jak utworzyć funkcję zdefiniowaną przez użytkownika, aby zliczyć liczbę słów w komórce lub zakresie?

Mamy dane w arkuszu 1, w którym mamy kilka adresów, więc chcemy policzyć słowa w komórce lub zakresie, tworząc funkcję zdefiniowaną przez użytkownika za pomocą VBA w Excelu.

Aby wykonać funkcję zdefiniowaną przez użytkownika, wykonaj poniższe czynności:-

  • Otwórz stronę VBA i naciśnij klawisz Alt + F11.
  • Włóż moduł.

Napisz poniższy kod!:

 Function WORDSCOUNT(rRange As Range) As Long Dim rCell As Range Dim Licz tak długo dla każdego rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1 Next rCell WORDSCOUNT = lCount End Function

Objaśnienia kodu: - Aby utworzyć funkcję zdefiniowaną przez użytkownika zaczynamy kod od nazwy funkcji i definiujemy zmienne. W kodzie użyliśmy „Pętla For Each” do policzenia słów w zakresie.
Jak korzystać z tej funkcji w programie Excel?

Aby skorzystać z tej funkcji, wykonaj poniższe czynności:-

  • Przejdź do arkusza Excel.
  • Aby policzyć słowa dla komórki, wprowadź formułę w komórce D7.
  • =WORDSCOUNT(C7), komórka C7 to komórka, w której chcemy obliczyć słowa.
  • Funkcja zwróci 6, co oznacza, że ​​komórka C7 zawiera 6 słów.
  • Aby wykonać te same obliczenia dla pozostałych komórek, skopiuj tę samą formułę i wklej w zakresie.

  • Aby policzyć słowa z zakresu, użyj formuły =WORDSCOUNT(C7:C16) i naciśnij klawisz Enter.
  • Funkcja zwróci liczbę słów.


Uwaga: – Ten UDF będzie pomocny w liczeniu słów w zakresie lub w pojedynczej komórce.

Teraz napiszemy kod, który zliczy słowo, używając określonego ogranicznika (,). Postępuj zgodnie z poniższymi instrukcjami:-

 Funkcja SEPARATECOUNTWORDS(rRange As Range, opcjonalny separator As Variant) As Long Dim rCell As Range Dim Count As Long If Is Missing(separator) Then separator = "," End If For Each rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function

Aby skorzystać z tej funkcji, wykonaj poniższe czynności:-

  • Przejdź do arkusza Excel.
  • Aby policzyć określone ograniczniki w słowie, użyjemy tej zdefiniowanej funkcji.
  • =SEPARATECOUNTWORDS(C7) i naciśnij klawisz Enter.
  • Funkcja zwróci liczbę określonych ograniczników.


Jak wyodrębnić słowo ze zdania lub komórki w programie Microsoft Excel za pomocą VBA?

Mamy dane w arkuszu1. W którym mamy kilka adresów, więc chcemy wyodrębnić słowa ze zdania lub komórki lub zakresu, tworząc funkcję zdefiniowaną przez użytkownika za pomocą VBA w programie Excel.


Aby wykonać funkcję zdefiniowaną przez użytkownika, wykonaj poniższe czynności:-

  • Otwórz stronę VBA i naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod!:-
 Function GETWORD(Tekst jako wariant, N jako liczba całkowita, opcjonalny ogranicznik jako wariant) As String If Is Missing(Delimiter) Then Delimiter = " " End If GETWORD = Split(tekst, separator)(N - 1) Zakończ funkcję

Kod Wyjaśnienie:- W powyższym kodzie wymieniliśmy nazwę funkcji ze zmiennymi. A potem określiliśmy kryteria wyodrębniania słowa ze zdania lub komórki.

Teraz nauczymy się korzystać z tej formuły. Postępuj zgodnie z poniższymi instrukcjami:-

  • Przejdź do arkusza Excel.
  • Użyj tej formuły w komórce D7.
  • =GETWORD(C7,2) i naciśnij klawisz Enter.
  • Funkcja zwróci drugie słowo z komórki, ponieważ we wzorze wspomnieliśmy o 2NS słowo liczby. Jeśli chcesz odzyskać słowo, które jest umieszczone na 3r & D pozycja, musisz zmienić liczbę z 2 na 3 w formule.


Jak utworzyć formułę numeru tygodnia ISO w programie Microsoft Excel za pomocą VBA?

Dowiemy się, jak możemy stworzyć formułę numeru tygodnia ISO w Excelu za pomocą tego UDF. Funkcji tej użyjemy do określenia, że ​​wspomniana data należy do numeru tygodnia w roku.

W arkuszu mamy listę dat, aw drugiej kolumnie chcemy pobrać numery tygodni.

Aby utworzyć UDF dla tego wymagania, wykonaj poniższe kroki:-

  • Otwórz stronę VBA i naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod:-
 Funkcja ISOWEEKNUMBER(Indate As Date) As Long Dim Dt As Date Dt = DateSerial(Year(Indate - Weekday(Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int((Indate - Dt + Weekday(Dt) + 5 ) / 7) Koniec funkcji

Kod Wyjaśnienie:- :- W powyższym kodzie wymieniliśmy nazwę funkcji ze zmiennymi. Następnie ustawiliśmy wartość daty, a następnie zdefiniowaliśmy kryteria funkcji „ISOWEENUMBER”.
Jak możemy wykorzystać tę funkcję w naszym pliku Excel?

  • Przejdź do arkusza Excel.
  • Wprowadź formułę w komórce D7.
  • = NUMER ISOWEK(C7) i naciśnij klawisz Enter.
  • Funkcja zwróci tydzień dla wprowadzonej daty w komórce. Teraz, aby pobrać numer tygodnia dla każdej daty, skopiuj tę samą formułę z zakresu.


Teraz dowiemy się, jak zwrócić normy ISO na początek roku w Excelu – Pierwszy poniedziałek roku.

Ta funkcja zasadniczo sprawdzi, czy 1NS Poniedziałek roku wypada w którym dniu, a następnie zacznie obliczać liczbę tygodni od tej daty. Zobaczmy, jak możemy stworzyć UDF dla tego wymagania.


Postępuj zgodnie z poniższymi instrukcjami:-

  • Otwórz stronę VBA i naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod!:-
 Funkcja ISOSTYR(Year As Integer) As Date Dim WD As Integer Dim NY As Date NY = DateSerial(Year, 1, 1) WD = (NY - 2) Mod 7 Jeśli WD < 4 Wtedy ISOSTYR = NY - WD W przeciwnym razie ISOSTYR = NY -WD + 7 End, jeśli koniec funkcji

Kod Wyjaśnienie: - W powyższym kodzie wymieniliśmy nazwę funkcji ze zmiennymi. Następnie ustaliliśmy kryteria dla zmiennych, a następnie zdefiniowaliśmy dane wejściowe formuły.

Wystarczy podać rok 2001 w tym formacie, a formuła da 1NS Poniedziałek roku.

Teraz dowiemy się, jak korzystać z UDF w pliku Excel. Postępuj zgodnie z poniższymi instrukcjami:-

  • Przejdź do arkusza Excel.
  • Wprowadź formułę w komórce D7.
  • =ISOSTYR(C7) i naciśnij klawisz Enter.
  • Funkcja zwróci datę 1NS Poniedziałek pierwszego tygodnia Nowego Roku.
  • Aby zwrócić datę 1NS W poniedziałek pierwszego tygodnia Nowego Roku skopiuj tę samą formułę i wklej do zakresu.


Jak poznać nazwę arkusza roboczego i skoroszytu za pomocą VBA w programie Microsoft Excel?

Postępuj zgodnie z poniższym kodem i krokami:-

  • Otwórz stronę VBA i naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod!:-
 Function Nazwa arkusza roboczego() Nazwa arkusza roboczego = Zakres("A1").Nazwa.Nazwa.Zakończ funkcję

Kod Wyjaśnienie:- W powyższym kodzie wspomnieliśmy o nazwie funkcji, a następnie zdefiniowaliśmy sposób poznania nazwy arkusza.

Aby użyć tej formuły, wystarczy wpisać formułę w dowolnej komórce w ten sposób: -=Nazwa arkusza roboczego(). Funkcja zwróci nazwę arkusza.


Aby utworzyć funkcję dla nazwy skoroszytu, wykonaj poniższe kroki i kod:-

  • Otwórz stronę VBA naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod:-
 Function Workbookname() Workbookname = ThisWorkbook.Name Funkcja zakończenia

Wyjaśnienie kodu:- :- W powyższym kodzie wspomnieliśmy o nazwie funkcji, a następnie zdefiniowaliśmy sposób poznania nazwy skoroszytu.

Aby użyć tej formuły, wystarczy wpisać formułę w dowolnej komórce w następujący sposób: - =Nazwa skoroszytu(). Funkcja zwróci nazwę arkusza.


Jak wyodrębnić pierwsze i ostatnie słowo z komórki za pomocą VBA w programie Microsoft Excel?

Mamy dane w arkuszu 1, w którym mamy kilka adresów, więc chcemy wyodrębnić ostatnie i pierwsze słowo ze zdania lub komórki lub zakresu, tworząc funkcję zdefiniowaną przez użytkownika za pomocą VBA w programie Excel.

Najpierw napiszemy funkcję wyodrębniania pierwszego słowa. Proszę postępować zgodnie z poniższymi podanymi krokami:-

  • Otwórz stronę VBA naciśnij klawisz Alt + F11.
  • Włóż moduł

Napisz poniższy kod!:-

 Funkcja GETFW(Tekst jako ciąg, opcjonalny separator jako wariant) Dim FW jako ciąg If IsMissing(Separator) Then Separator = " " End If FW = Left(Text, InStr(1, Text, Separator, vbTextCompare)) GETFW = Replace(FW) , Separator, "") Funkcja zakończenia

Kod Wyjaśnienie: - W powyższym kodzie wymieniliśmy nazwę funkcji ze zmiennymi. A potem zdefiniowaliśmy kryteria wyodrębniania słowa ze zdania lub komórki.

Teraz nauczymy się korzystać z tej formuły. Postępuj zgodnie z poniższymi instrukcjami:-

  • Przejdź do arkusza Excel.
  • Użyj tej formuły w komórce D9.
  • =GETFW(C9) i naciśnij klawisz Enter.
  • Funkcja zwróci pierwsze słowo z danych. Teraz, aby pobrać pierwsze słowo dla wszystkich komórek, skopiuj tę samą formułę z zakresu.

Teraz napiszemy kod, aby wyodrębnić ostatnie słowo z komórki. Postępuj zgodnie z poniższym kodem:-

  • Otwórz stronę VBA naciśnij klawisz Alt + F11.
  • Włóż moduł.
  • Napisz poniższy kod!:-
 Function GETLW(Tekst jako ciąg, opcjonalny separator jako wariant) Dim LW jako ciąg If IsMissing(Separator) Then Separator = " " End If LW = StrReverse(Text) LW = Left(lastword, InStr(1, LW, Separator, vbTextCompare) ) GETLW = StrReverse(Zamień(LW, Separator, "")) Funkcja zakończenia

Teraz nauczymy się korzystać z tej formuły. Postępuj zgodnie z poniższymi instrukcjami:-

  • Przejdź do arkusza Excel.
  • Użyj tej formuły w komórce D9.
  • =GETLW(C9) Naciśnij klawisz Enter.
  • Funkcja zwróci ostatnie słowo z danych. Teraz, aby pobrać ostatnie słowo dla wszystkich komórek, skopiuj tę samą formułę z zakresu.

Są to funkcje, które możemy zdefiniować za pomocą VBA, a następnie użyć ich jako formuły Excela. Ponadto możemy stworzyć znacznie więcej funkcji zdefiniowanych przez użytkownika. Ucz się z nami dalej, wymyślimy bardziej skomplikowane formuły.