So erstellen Sie eine benutzerdefinierte Funktion in Microsoft Excel

Anonim

In diesem Artikel erfahren Sie, wie Sie mit VBA benutzerdefinierte Funktionen in Microsoft Excel erstellen.

Benutzerdefinierte Funktion: - Microsoft Excel hat bereits viele Funktionen, aber jeder hat unterschiedliche Anforderungen. Situation, wir können unsere eigene Funktion gemäß der Anforderung erstellen, die als benutzerdefinierte Funktion bezeichnet wird. Wir können die benutzerdefinierte Funktion wie andere Funktionen in Excel verwenden.

Nachfolgend sind die Themen aufgeführt, für die wir die benutzerdefinierte Funktion erstellen:

1). Wie zähle ich die Anzahl der Wörter in einer Zelle oder einem Bereich?

2). Wie extrahiere ich ein Wort aus einem Satz oder einer Zelle in Excel?

3). Wie erstelle ich die Formel für ISO?

4). Wie erkenne ich den Arbeitsblatt- und Arbeitsmappennamen mit VBA?

5). Wie extrahiere ich das erste und letzte Wort aus einer Zelle in Excel?

Wie erstelle ich die benutzerdefinierte Funktion zum Zählen der Anzahl der Wörter in Zelle oder Bereich?

Wir haben Daten in Blatt 1, in denen wir einige Adressen haben, also möchten wir die Wörter in einer Zelle oder einem Bereich zählen, indem wir die benutzerdefinierte Funktion über VBA in Excel erstellen.

Um die benutzerdefinierte Funktion zu erstellen, befolgen Sie bitte die folgenden Schritte:

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.

Schreiben Sie den unten genannten Code:

 Funktion WORDSCOUNT(rRange As Range) As Long Dim rCell As Range Dim Count As Long für jedes rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1 Next rCell WORDSCOUNT = lCount End Function

Code-Erläuterungen: - Um die benutzerdefinierte Funktion zu erstellen, starten wir den Code mit dem Funktionsnamen und definieren die Variablen. Wir haben im Code „For Each-Schleifen“ verwendet, um die Wörter im Bereich zu zählen.
Wie verwende ich diese Funktion in Excel?

Um diese Funktion zu verwenden, folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Um die Wörter für eine Zelle zu zählen, geben Sie die Formel in Zelle D7 ein.
  • =WORDSCOUNT(C7), Zelle C7 ist die Zelle, in der wir die Wörter berechnen möchten.
  • Die Funktion gibt 6 zurück, was bedeutet, dass Zelle C7 6 Wörter enthält.
  • Um dieselbe Berechnung für den Rest der Zellen durchzuführen, kopieren Sie dieselbe Formel und fügen Sie sie in den Bereich ein.

  • Um die Wörter im Bereich zu zählen, verwenden Sie die Formel als =WÖRTERZAHL(C7:C16) und drücken Sie die Eingabetaste.
  • Die Funktion gibt die Anzahl der Wörter zurück.


Hinweis:- Diese UDF ist hilfreich, um die Wörter in einem Bereich oder in einer einzelnen Zelle zu zählen.

Jetzt schreiben wir den Code, um das Wort mit dem angegebenen Trennzeichen (,) zu zählen. Folgen Sie den unten angegebenen Schritten:

 Funktion SEPARATECOUNTWORDS(rRange als Bereich, optionales Trennzeichen als Variante) As Long Dim rCell As Range Dim Count As Long If IsMissing(separator) Then separator = "," End If For Each rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function

Um diese Funktion zu verwenden, folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Um die spezifischen Trennzeichen im Wort zu zählen, verwenden wir diese definierte Funktion.
  • =ZÄHLWÖRTER SEPARATE(C7) und drücken Sie die Eingabetaste.
  • Die Funktion gibt die Anzahl der spezifischen Trennzeichen zurück.


Wie extrahiert man ein Wort aus einem Satz oder einer Zelle in Microsoft Excel mit VBA?

Wir haben Daten in Blatt1. In denen wir einige Adressen haben, möchten wir die Wörter aus einem Satz oder einer Zelle oder einem Bereich extrahieren, indem wir die benutzerdefinierte Funktion über VBA in Excel erstellen.


Um die benutzerdefinierte Funktion zu erstellen, befolgen Sie bitte die folgenden Schritte:

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) Als String If IsMissing(Delimiter) Then Delimiter = " " End If GETWORD = Split(Text, Delimiter)(N - 1) End Function

Code-Erklärung:- Im oben genannten Code haben wir den Funktionsnamen mit den Variablen erwähnt. Und dann hatten wir die Kriterien definiert, um das Wort aus einem Satz oder einer Zelle zu extrahieren.

Jetzt lernen wir, wie man diese Formel verwendet. Folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Verwenden Sie diese Formel in Zelle D7.
  • =GETWORD(C7,2) und drücken Sie die Eingabetaste.
  • Die Funktion gibt das zweite Wort aus der Zelle zurück, da wir in der Formel für 2 . erwähnt hattennd das Wort der Zahl. Wenn Sie das Wort abrufen möchten, das bei 3 . platziert istrd Position müssen Sie die Zahl in der Formel von 2 auf 3 ändern.


Wie erstelle ich eine ISO-Wochenzahlformel in Microsoft Excel mit VBA?

Wir werden lernen, wie wir mit diesem UDF eine ISO-Wochennummerformel in Excel erstellen können. Mit dieser Funktion erkennen wir, ob das genannte Datum zu welcher Wochennummer des Jahres gehört.

Wir haben eine Datumsliste im Blatt und in der zweiten Spalte möchten wir die Wochennummern abrufen.

Um die UDF für diese Anforderung zu erstellen, befolgen Sie die unten angegebenen Schritte:-

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion 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) Endfunktion

Code-Erklärung:- :- Im obigen Code haben wir den Funktionsnamen mit den Variablen erwähnt. Und dann hatten wir den Datumswert gesetzt und dann die Kriterien der Funktion „ISOWEENUMBER“ definiert.
Wie können wir diese Funktion in unserer Excel-Datei verwenden?

  • Gehen Sie zu Excel-Tabelle.
  • Geben Sie die Formel in Zelle D7 ein.
  • =ISOWOCHENNUMMER(C7) und drücken Sie die Eingabetaste.
  • Die Funktion gibt die Woche für das eingegebene Datum in der Zelle zurück. Um nun die Wochennummer für jedes Datum abzurufen, kopieren Sie dieselbe Formel in den Bereich.


Jetzt lernen wir, wie man die ISO-Standards zum Jahresbeginn in Excel zurückgibt - Erster Montag des Jahres.

Diese Funktion prüft grundsätzlich, ob 1NS Auf welches Datum fällt der Montag des Jahres und dann wird die Anzahl der Wochen ab diesem Datum berechnet. Sehen wir uns an, wie wir die UDF für diese Anforderung erstellen können.


Befolgen Sie die unten angegebenen Schritte:-

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion ISOSTYR(Year As Integer) As Date Dim WD As Integer Dim NY As Date NY = DateSerial(Year, 1, 1) WD = (NY - 2) Mod 7 If WD < 4 Then ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End Funktion

Code-Erklärung: - Im obigen Code haben wir den Funktionsnamen mit den Variablen erwähnt. Und dann hatten wir die Kriterien für die Variablen festgelegt und dann die Formeleingabe definiert.

Sie müssen nur das Jahr 2001 in diesem Format angeben und die Formel gibt Ihnen 1NS Montag des Jahres.

Jetzt lernen wir, wie Sie die UDF in einer Excel-Datei verwenden. Folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Geben Sie die Formel in Zelle D7 ein.
  • =ISOSTYR(C7) und drücken Sie die Eingabetaste.
  • Die Funktion gibt das Datum des 1 . zurückNS Montag der ersten Neujahrswoche.
  • Um das Datum des 1.NS Kopieren Sie am Montag der ersten Woche des neuen Jahres dieselbe Formel und fügen Sie sie in den Bereich ein.


Wie erkenne ich den Arbeitsblatt- und Arbeitsmappennamen mithilfe der VBA in Microsoft Excel?

Befolgen Sie den unten angegebenen Code und die Schritte:-

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion Worksheetname() Worksheetname = Range("A1").Parent.Name End Function

Code-Erklärung:- Im obigen Code haben wir den Funktionsnamen erwähnt und dann definiert, wie man den Blattnamen kennt.

Um diese Formel zu verwenden, müssen Sie die Formel nur auf diese Weise in eine beliebige Zelle eingeben: -=Arbeitsblattname(). Die Funktion gibt den Blattnamen zurück.


Um die Funktion für den Arbeitsmappennamen zu erstellen, befolgen Sie die folgenden Schritte und den folgenden Code:

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion Workbookname() Workbookname = ThisWorkbook.Name End Function

Code-Erklärung:- :- Im obigen Code haben wir den Funktionsnamen erwähnt und dann definiert, wie man den Arbeitsmappennamen erkennt.

Um diese Formel zu verwenden, müssen Sie die Formel nur auf diese Weise in eine beliebige Zelle eingeben: - =Arbeitsbuchname(). Die Funktion gibt den Blattnamen zurück.


Wie extrahiere ich das erste und letzte Wort aus einer Zelle mithilfe von VBA in Microsoft Excel?

Wir haben Daten in Blatt1, in denen wir einige Adressen haben, also möchten wir das letzte und erste Wort aus einem Satz oder einer Zelle oder einem Bereich extrahieren, indem wir die benutzerdefinierte Funktion über VBA in Excel erstellen.

Zuerst schreiben wir die Funktion, um das erste Wort zu extrahieren. Bitte folgen Sie den unten angegebenen Schritten:-

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Ein Modul einfügen

Schreiben Sie den unten genannten Code:-

 Funktion GETFW(Text As String, Optional Separator As Variant) Dim FW As String If IsMissing(Separator) Then Separator = " " End If FW = Left(Text, InStr(1, Text, Separator, vbTextCompare)) GETFW = Replace(FW , Trennzeichen, "") Endfunktion

Code-Erklärung: - Im oben genannten Code haben wir den Funktionsnamen mit den Variablen erwähnt. Und dann haben wir die Kriterien definiert, um das Wort aus einem Satz oder einer Zelle zu extrahieren.

Jetzt lernen wir, wie man diese Formel verwendet. Folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Verwenden Sie diese Formel in Zelle D9.
  • =GETFW(C9) und drücken Sie die Eingabetaste.
  • Die Funktion gibt das erste Wort aus den Daten zurück. Um nun das erste Wort für alle Zellen abzurufen, kopieren Sie dieselbe Formel in den Bereich.

Jetzt schreiben wir den Code, um das letzte Wort aus der Zelle zu extrahieren. Folgen Sie unten genanntem Code:-

  • Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt+F11.
  • Fügen Sie ein Modul ein.
  • Schreiben Sie den unten genannten Code:-
 Funktion GETLW(Text As String, Optional Separator As Variant) Dim LW As String If IsMissing(Separator) Then Separator = " " End If LW = StrReverse(Text) LW = Left(lastword, InStr(1, LW, Separator, vbTextCompare) ) GETLW = StrReverse(Replace(LW, Separator, "")) End Function

Jetzt lernen wir, wie man diese Formel verwendet. Folgen Sie den unten angegebenen Schritten:

  • Gehen Sie zu Excel-Tabelle.
  • Verwenden Sie diese Formel in Zelle D9.
  • =GETLW(C9) Drücken Sie die Eingabetaste.
  • Die Funktion gibt das letzte Wort aus den Daten zurück. Um nun das letzte Wort für alle Zellen abzurufen, kopieren Sie dieselbe Formel in den Bereich.

Dies sind die Funktionen, die wir über VBA definieren und dann als Excel-Formel verwenden können. Außerdem können wir viele weitere benutzerdefinierte Funktionen erstellen. Lernen Sie mit uns weiter, wir werden uns noch kniffligere Formeln einfallen lassen.