Datenschutzerklärung


Excel-Beispiele:
Erklärungen, Formeln, VBA-Code und mehr

Hinweise zu den Beispielen finden Sie hier: Beispiele


Kategorie: Beispiel > Excel > VBA > UDF (18)

Direktnachricht



UDF - Benutzerdefinierte Funktionen

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Benutzerdefinierte Funktionen, also UDF, sind Funktionen, die man sich selbst im VBA-Editor erstellt. Dazu benennt man sie nicht mit Sub, sondern mit Function. Der Name der Funktion liefert dabei den Rückgabewert. Heißt also eine Funktion "MeineFunktion(...)", schreibt man in die Zelle "=MeineFunktion(...)". Ebenfalls kann man Funktionen einsetzen, um durch Subs bestimmte Berechnungen durchführen zu lassen.

Um zu gewährleisten, dass eine in einem Tabellenblatt eingesetzte UDF immer rechnet, sollte man an den Anfang der Funktion schreiben:

Quersumme bilden

(Tipp 165) Nachricht zum Beitrag an Autor Nach oben

Wie kann man die Quersumme einer Zahl in einer Zelle bilden?

In die Zellen braucht man dann nur die Formel einzugeben:

Zahlen zerlegen in Euro + Cent (in zwei Zellen)

(Tipp 166) Nachricht zum Beitrag an Autor Nach oben

In einer Zelle steht ein Betrag. In einer anderen Zelle möchte ich nur den Eurobetrag, in einer weiteren nur den reinen Centbetrag. Aus 3,25 Euro müßte also in der einen zelle die 3, in der anderen Zelle die 25 stehen. Wie kann man das mittels einer benutzerdefinierten Funktion lösen?

In die Zellen braucht man dann nur die Formeln einzugeben:



SVERWEIS: Suchkriterium als Teil

(Tipp 167) Nachricht zum Beitrag an Autor Nach oben

Der SVERWEIS sucht in der ersten Spalte einer Matrix nach einem Suchkriterium, das dem SVERWEIS vollständig mitgeteilt werden muß. Wie kann ich nach einer Zeichenfolge suchen, von der nur der erste Teil bekannt ist?

Die Syntax ist die gleiche wie bei der integrierten Funktion SVERWEIS:

Werte in einem Bereich zählen

(Tipp 168) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einem Bereich die Zahlen zählen, die größer/gleich einer Zahl und kleiner gleich einer anderen Zahl sind?

Wenn der Bereich nicht zu groß ist, kann folgende benutzerdefinierte Funktion verwendet werden:

In die Zelle muß dann eingegeben werden:

Hinweis: Ist der Bereich größer als 1000 Zellen sollte in Extras - Optionen - Berechnen auf manuelles Berechnen gestellt werden.

Kalenderwoche nach DIN

(Tipp 169) Nachricht zum Beitrag an Autor Nach oben

Wie kann die Kalenderwoche eines Datums nach DIN berechnet werden?

Diese Funktion stammt von Christoph Kremer:

Zweite Möglichkeit:

Pause nur berechnen, wenn anwesend

(Tipp 170) Nachricht zum Beitrag an Autor Nach oben

Ich habe eine Datei zum Führen der Arbeitszeit aufgebaut. Mein Problem sind unsere Pausenregelungen, von 9:00 - 9:15 Uhr und 13:00 - 13:30 Uhr. Die Pausen dürfen nur abgezogen werden wenn derjenige anwesend ist. Das Pause-Feld muß sich also nach den Komm- u. Geht-Feldern richten.

In die Zelle braucht man dann nur noch einzugeben: =Pausenzeit(A4;B4;B1;C1;D1;E1)

Letzter Wert einer Spalte

(Tipp 171) Nachricht zum Beitrag an Autor Nach oben

Wie bekomme ich den letzten Wert einer Spalte z. B. von B210 nach z. B. A1, wenn die Spalte jeden Tag um einen Wert erweitert wird?

In die Zelle kann dann z. B. eingegeben werden: =LetzterWert(B1:B1000)

Formel zeigen und rechnen

(Tipp 172) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einer Zelle die Formel anzeigen, die in einer anderen Zelle enthalten ist?

Eine Möglichkeit ist eine UDF, eine benutzerdefinierte Funktion:

In die Zelle kann dann z. B. eingegeben werden: =Bezug(A1)

Eine Datei dazu steht bei den Beispieldateien zur Verfügung (formelzeigen.zip, Klick auf das Diskettensymbol)

Eine andere Möglichkeit ist:

Ist das Gleichheitszeichen am Anfang nicht erwünscht, kann es z. B. auch mit der Funktion Right() entfernt werden.

Erster Buchstabe in Zeichenfolge

(Tipp 173) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den ersten Buchstaben in einer Zeichenfolge auslesen lassen?

Die folgende Funktion liefert das erste nichtnumerische Zeichen:

Die nächste Funktion liefert den ersten Buchstaben einer Zeichenfolge:

In die Zelle muß dann entweder =ErsteNichtZahl(A1) oder =ErsterBuchstabe(A1) eingegeben werden.

Datum aus Kalenderwoche errechnen

(Tipp 174) Nachricht zum Beitrag an Autor Nach oben

Wie kann man aus einer angegebenen Kalenderwoche das Datum bestimmen? Folgende Daten sind gegeben: 1999 (Jahr) 42 (Kalenderwoche) 1 (Tag)

In die Zelle muß dann eingegeben werden: =TagAusKW(Jahr;KW;Tag).

Berechnung der Feiertage

(Tipp 336) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zu einer Jahreszahl die Feiertage berechnen?

Siehe Beispieldatei.

Formel als Ergebnis einer Formel

(Tipp 418) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel eine Formel aus einer anderen Zelle anzeigen lassen?

Siehe Beispieldatei.

Zellen mit Farbe zählen

(Tipp 429) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel Zellen mit Hintergrundfarben zählen?

Siehe Beispieldatei.



Wochendaten zur Zahl einer Kalenderwoche bestimmen

(Tipp 430) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus der Zahl einer Kalenderwoche die entsprechenden Daten der Woche errechnen?

In eine Zelle wird die Zahl einer Kalenderwoche eingetragen. Nach der Eingabe sollen Beginn und Ende der Woche ergänzt werden.

Siehe Beispieldatei.

Letzten Wert in einem Bereich ermitteln

(Tipp 431) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den letzten Wert in einem Bereich ermitteln?

Siehe Beispieldatei.

Zahl mit Trennzeichen trennen

(Tipp 553) Nachricht zum Beitrag an Autor Nach oben

Eine Zahl, z. B. 8070110, soll nach jeder 0 einen Bindestrich haben, also so: 80-70-110.

Hier ist eine benutzerdefinierte Funktion:

Dazu mit Alt und F11 den Editor aufrufen, ein Modul einfügen und die Function eingeben. In die Zelle kommt dann z. B. die folgende Formel:

String aufteilen

(Tipp 567) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen String aufgrund eines Trennzeichens aufteilen?

Die hier dargestellte Funktion stellt eine Alternative zur Funktion Split() dar, die es in niedrigeren Excelversionen noch nicht gab. Alternativ kann die Funktion auch als Tabellenblattfunktion verwendet werden, wenn man Daten - Text in Spalten nicht verwenden kann.

An die Funktion wird übergeben, welcher String aufgeteilt werden soll (strString), der wievielte Eintrag zurückgegeben werden soll (intWelcher) und um welches Trennzeichen es sich handelt (strTrenner).

So könnte die Funktion wie folgt eingesetzt werden:

Das Ergebnis wäre in diesem Fall 78,9bb.