Wie man benutzerdefinierte Funktion in Microsoft Excel erstellen

Posted on: 2020年10月23日 2020年11月12日
Tags: custom-functions-in-vba

_In diesem Artikel erfahren Sie, wie Sie mithilfe von VBA benutzerdefinierte Funktionen in Microsoft Excel erstellen. _

Benutzerdefinierte Funktion: – Microsoft Excel verfügt bereits über viele Funktionen, aber jeder hat unterschiedliche Anforderungen. In der jeweiligen Situation können wir 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 finden Sie die Themen, für die wir die benutzerdefinierte Funktion erstellen: 1). Wie zähle ich die Anzahl der Wörter in Zelle oder 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). Woher kennen Sie den Namen des Arbeitsblatts und der Arbeitsmappe mithilfe des VBA?

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

Wie erstelle ich eine benutzerdefinierte Funktion, um die Anzahl der Wörter in Zelle oder Bereich zu zählen? 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.

Führen Sie die folgenden Schritte aus, um die benutzerdefinierte Funktion auszuführen: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.

Schreiben Sie den folgenden Code:

Function WORDSCOUNT(rRange As Range) As Long

Dim rCell As Range

Dim Count As Long

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Next rCell

WORDSCOUNT = lCount

End Function

Code-Erklärungen: – Um die benutzerdefinierte Funktion zu erstellen, starten wir den Code zum Funktionsnamen und definieren die Variablen. Wir haben „For Each Loops“ im Code verwendet, um die Wörter im Bereich zu zählen.

Wie verwende ich diese Funktion in Excel?

Führen Sie die folgenden Schritte aus, um diese Funktion zu verwenden: – * Gehen Sie zu Excel Sheet.

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 = WORDSCOUNT (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 zu zählen, indem wir das angegebene Trennzeichen (,) verwenden. Befolgen Sie die unten angegebenen Schritte: –

Function SEPARATECOUNTWORDS(rRange As Range, Optional separator As Variant) 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, gehen Sie wie folgt vor: –

Gehen Sie zu Excel Sheet.
Um die spezifischen Trennzeichen im Wort zu zählen, verwenden wir diese definierte Funktion.
= SEPARATECOUNTWORDS (C7) und drücken Sie die Eingabetaste.
Die Funktion gibt die Anzahl der spezifischen Trennzeichen zurück.

Wie extrahiere ich ein Wort aus einem Satz oder einer Zelle in Microsoft Excel mit dem VBA? Wir haben Daten in Blatt1. In denen wir einige Adressen haben, möchten wir die Wörter aus einem Satz, einer Zelle oder einem Bereich extrahieren, indem wir die benutzerdefinierte Funktion über VBA in Excel erstellen.

Um die benutzerdefinierte Funktion zu erstellen, führen Sie bitte die folgenden Schritte aus: –

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

Schreiben Sie den unten angegebenen Code *: –

Function GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) As String

If IsMissing(Delimiter) Then

Delimiter = " "

End If

GETWORD = Split(Text, Delimiter)(N - 1)

End Function

Code Erläuterung: – 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. Befolgen Sie die unten angegebenen Schritte: – * Gehen Sie zu Excel Sheet.

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 das Wort der 2. Nummer erwähnt haben. Wenn Sie das Wort abrufen möchten, das an der 3. Position steht, müssen Sie die Zahl in der Formel von 2 auf 3 ändern.

Wie erstelle ich eine ISO-Wochennummernformel in Microsoft Excel mit dem VBA? Wir werden lernen, wie wir mit dieser UDF eine ISO-Wochenzahlformel in Excel erstellen können. Mit dieser Funktion identifizieren wir, dass 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.

Führen Sie die folgenden Schritte aus, um die UDF für diese Anforderung zu erstellen: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.
Schreiben Sie den unten angegebenen Code: –

Function 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)

End Function

Code Erläuterung: -: – Im obigen Code haben wir den Funktionsnamen mit den Variablen erwähnt. Dann hatten wir den Datumswert festgelegt und dann die Kriterien der Funktion „ISOWEENUMBER“ definiert.

Wie können wir diese Funktion in unserer Excel-Datei verwenden?

Gehen Sie zu Excel Sheet.
Geben Sie die Formel in Zelle D7 ein.
= ISOWEEKNUMBER (C7) und drücken Sie die Eingabetaste.
Die Funktion gibt die Woche für das in der Zelle eingegebene Datum zurück.

Um nun die Wochennummer für jedes Datum abzurufen, kopieren Sie dieselbe Formel im Bereich.

Jetzt lernen wir, wie Sie die ISO-Standards zu Beginn des Jahres in Excel – Erster Montag des Jahres – zurückgeben können.

Diese Funktion prüft grundsätzlich, ob der 1. Montag des Jahres auf welches Datum fällt, und beginnt dann mit der Berechnung der Anzahl der Wochen ab diesem Datum. Mal sehen, wie wir die UDF für diese Anforderung erstellen können.

Führen Sie die folgenden Schritte aus: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.

Schreiben Sie den unten angegebenen Code *: –

Function 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 Function

Code Erläuterung: – 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 den 1. Montag des Jahres.

Jetzt lernen wir, wie man die UDF in einer Excel-Datei verwendet. Befolgen Sie die unten angegebenen Schritte: – * Gehen Sie zu Excel Sheet.

Geben Sie die Formel in Zelle D7 ein.
= ISOSTYR (C7) und drücken Sie die Eingabetaste.
Die Funktion gibt das Datum des 1. Montags der ersten Woche des neuen Jahres zurück.
Um das Datum des ersten Montags der ersten Woche des neuen Jahres zurückzugeben, kopieren Sie dieselbe Formel und fügen Sie sie in den Bereich ein.

Woher kennen Sie den Namen des Arbeitsblatts und der Arbeitsmappe mithilfe des VBA in Microsoft Excel? Befolgen Sie die unten angegebenen Codes und Schritte: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.

Schreiben Sie den unten angegebenen Code *: –

Function Worksheetname()

Worksheetname = Range("A1").Parent.Name

End Function

Code Erläuterung: – Im obigen Code haben wir den Funktionsnamen erwähnt und dann definiert, wie der Blattname zu kennen ist.

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

Führen Sie die folgenden Schritte und den folgenden Code aus, um die Funktion für den Namen der Arbeitsmappe zu erstellen: – * Öffnen Sie die VBA-Seite. Drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.
Schreiben Sie den unten angegebenen Code: –

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Code Erläuterung: -: – Im obigen Code haben wir den Funktionsnamen erwähnt und dann definiert, wie der Name der Arbeitsmappe kennengelernt werden soll.

Um diese Formel zu verwenden, müssen Sie die Formel nur auf folgende Weise in eine beliebige Zelle eingeben: – = Arbeitsmappenname (). 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 Blatt 1, 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 befolgen Sie die folgenden Schritte: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Einfügen eines Moduls Schreiben Sie den unten genannten Code *: –

Function 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, Separator, "")

End Function

Code Erläuterung: – 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 benutzt. Befolgen Sie die unten angegebenen Schritte: –

Gehen Sie zu Excel Sheet.
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.

Befolgen Sie den unten angegebenen Code: – * Öffnen Sie die VBA-Seite und drücken Sie die Taste Alt + F11.

Fügen Sie ein Modul ein.

Schreiben Sie den unten angegebenen Code *: –

Function 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. Befolgen Sie die unten angegebenen Schritte: – * Gehen Sie zu Excel Sheet.

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 viel mehr benutzerdefinierte Funktionen erstellen.

Lernen Sie weiter mit uns, wir werden schwierigere Formeln entwickeln.