Formeln und benutzerdefinierte Funktionen, die in Excel eingebunden werden können.

letzte benutzte Spalte oder Zeile ermitteln ohne VBA
diese geniale Formel nennt sich auch Frank Kabel - Lösung
'Im Beispiel wird die letzte Zeile von A bzw. die letzte Spalte von Zeile 1 gesucht
=VERWEIS(2;1/(1:1<>"");SPALTE(1:1))
=VERWEIS(2;1/(1:1<>"");1:1)
sowie
=VERWEIS(2;1/(A1:A65535<>"");ZEILE(A:A))
=VERWEIS(2;1/(A1:A65535<>"");A:A) Der dynamische Bereich lässt sich dann z.B. mit INDIREKT("A1:A"&VERWEIS(2;1/(F1:F65535<>"");ZEILE(F:F)))) ansprechen. Hier wird die letzte Zeile von Spalte F gesucht.
DIN Kalenderwoche eine weitere Möglichkeit
'Im Beispiel steht das Datum in A1
=KÜRZEN((A1-WOCHENTAG(A1;2)+11-("1/"&JAHR(A1+4-WOCHENTAG(A1;2))))/7)
Berechnung von Zellfarbe abhängig
Summe aller Zellinhalte mit entsprechender Hintergrund- oder Textfarbe
'es werden nur die Zellinhalte mit der entsprechenden Hintergrundfarbe summiert. Aufruf durch =Farbsumme(Tabellenbereich;Farbnummer)
wenn die Summe der Textfarbe gesucht wird, die Zeile "If Zelle.Interior.ColorIndex abändern in If Zelle.Font.ColorIndex
Function Farbsumme(Bereich As Range, Farbe As Integer)
Dim Zelle As Object
Application.Volatile
For Each Zelle In Bereich
If Zelle.Interior.ColorIndex = Farbe Then
Farbsumme = Farbsumme + Zelle
End If
Next
End Function
auf Excel-Kommentare zugreifen
mit einer benutzerdefinierten Funktion den Kommentar an anderer Stelle anzeigen lassen
'den Kommentar an anderer Stelle anzeigen lassen mit dem Aufruf der Funktion z.B. =Kommentar(Tabelle1!A3)
Public Function Kommentar(Bezug As Range) As String
Kommentar = Bezug.Comment.Text
End Function
nach Geburtsdaten sortieren
'in einer Zusatzspalte nachfolgende Formel eingeben und diese Spalte sortieren lassen. Im Beispiel steht das Datum in Zelle A1

=DATUM(0;MONAT(A1);TAG(A1))

Euro runden
'Die Funktion VRUNDEN ist nur vorhanden, wenn die Analyse-Funktion im Add-In-Manager aktiviert ist
=VRUNDEN(Wert;0,5)
oder
=Runden(Wert*2;0)/2
Zeiträume auf Stunden oder Minuten runden
'die Funktion ist enthalten, wenn bei der Installation die Analyse-Funktionen mit angegeben wurden
zum Runden von Minuten = VRUNDEN(A1;1/1440)
bei Stunden die Formel =VRUNDEN(A1;1/24)
Differenz zweier Zeitangaben
bei negativen Zeiten
'mit der folgenden Formel.
Achtung! Das Ergebnis liegt als Text vor und kann in dieser Form für eine Weiterberechnung nicht mehr verwendet werden.
=WENN(A1-B1<0;"-";"+")&TEXT(ABS(A1-B1);"[h]:mm")
oder als Tipp von Peter zum weiterberechnen:
=WENN(B1>A1;B1-A1;(B1-A1)+1)
Zahl als Text anzeigen
mit einer benutzerdefinierten Funktion von Rene Probst
'mit einer benutzerdefinierten Funktion. Der Aufruf erfolgt mit z.B. =zahltext(2100)
komplette Funktion als Textdatei zum Download (2KB)
Hetterich-Osterformel
zur Berechnung des Ostersonntags
' j steht für das Jahr
=KÜRZEN(DATUM(j;3;56-REST(REST(j;19)*10,63+5;29))/7)*7+1
Alter berechnen
mit einer benutzerdefinierten Funktion
'Aufruf erfolgt mit =Alter(Geburtsdatum). Die Funktion berechnet das Alter verglichen mit dem heutigen Tag
Function Alter(Gebdatum)
jalter = Year(Date) - Year(Gebdatum)
If Month(Date) < Month(Gebdatum) Then
jalter = jalter - 1
Else
If Month(Date) = Month(Gebdatum) Then
If Day(Date) < Day(Gebdatum) Then
jalter = jalter - 1
End If
End If
End If
Alter = jalter
End Function
Zufallszahl erzeugen
'Die Formel in eine Zelle eingeben und nach unten bis zur gewünschten Anzahl ausfüllen.
=GANZZAHL(ZUFALLSZAHL()*10000+1)
doppelte Datensätze finden
mit einer verschachtelten Wenn-Funktion
'mit einer verschachtelten Wenn-Funktion. Die Formel in eine Spalte einfügen und nach unten ausfüllen. Anschließend die Spalte sortieren oder über Autofilter die doppelten Datensätze anzeigen lassen
=WENN(A2=A3;WENN(B2=B3;WENN(C2=C3;"doppelt";"");"");"")
Teilergebnis von gefilterten Daten
erhalten Sie mit der gleichnamigen Funktion
'die 9 steht in diesem Fall für Summe, D1:D20 ist der Bereich der ungefilterten Daten
Um ein Teilergebnis zu erhalten, verwenden Sie die Funktion
=TEILERGEBNIS(9;D1:D20)

folgende Möglichkeiten können sie ebenfalls mit Teilergebnis verwenden:
1 MITTELWERT
2 ANZAHL
3 ANZAHL2
4 MAX
5 MIN
6 PRODUKT
7 STABW
8 STABWN
9 SUMME
10 VARIANZ
11 VARIANZEN
Quersumme berechnen
mit einer benutzerdefinierten Function
'Aufruf erfolgt mit =Quersumme(Zahl), wobei Zahl für die Zahl oder den Bereich steht
Function QUERSUMME(zahl)
Dim i%
For i = 1 To Len(zahl)
QUERSUMME = QUERSUMME + Val(Mid(zahl, i, 1))
Next i
End Function
Formeln einer Zelle auslesen
Diese Funktion zeigt die Formeln an und nicht die Ergebnisse
'Aufruf erfolgt mit =zeigeformel(Zelle)
Function zeigeformel(ziel As Excel.range) As Variant
If ziel.Count = 1 Then
If ziel.HasFormula = True Then
zeigeformel = ziel.FormulaLocal
Else
zeigeformel = "#keine Formel in " & ziel.Address & "!"
End If
Else
zeigeformel = CVErr(xlErrRef)
End If
End Function
Akustische Tabellenfunktion
Mit dieser Funktion können Sie "hören" ob das Ergebnis zutrifft oder nicht
'der Aufruf erfolgt mit =SpielWAV(A1<100;"c:\sound1.wav";"c:\sound2.wav")
Private Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal WAVDatei As String, ByVal WiedergabeModus As Long) As Long Function SpielWAV(Vergleich As Boolean, DieseWAV As String, JeneWAV As String) As String
'Für: Excel 2000
'Funktion: Neue Tabellenfunktion. Wenn der übergebene Vergleich
' wahr ist, wird die in DieseWAV benannte WAV-Datei abgespielt.
' Ist der Vergleich falsch, bringt die Funktion die in
' JeneWAV angegebene WAV-Datei zu Gehör.
'© 2000, Ralf Nebelo

If Vergleich = True And DieseWAV > "" Then
If Dir(DieseWAV) > "" Then
sndPlaySound DieseWAV, 1
Else
SpielWAV = "(DieseWAV nicht gefunden)"
End If
ElseIf Vergleich = False And JeneWAV > "" Then
If Dir(JeneWAV) > "" Then
sndPlaySound JeneWAV, 1
Else
SpielWAV = "(JeneWAV nicht gefunden)"
End If
End If
End Function
Zwischensumme
nicht mit in das Ergebnis einbeziehen
möchte ich in Berechnungen eine Zwischensumme einfügen, die in der Endsumme aber nicht enthalten sein darf, gebe ich für die Zwischensumme folgende Formel ein:
=Text(Summe(A1:A25);"#.##0,00") Soll mit diesem Ergebnis jedoch weitergerechnet werden, nicht die Funktion Summe(A1:A2) eingeben, sondern mit Hilfe des Plus-Zeichens, also =A1+A2
Datumsangaben-Differenz
gibt die Differenz in Tagen, Monaten und Jahren zwischen zwei Datumgsangaben an
folgende Funktion berechnet die Differenz zweier Datumsangaben: =datedif(a1;b1;"d")

Das "d" steht für die Differenz in Tagen

folgende Variablen sind möglich:
"y" - Jahre
"m" - Monate
"d" - Tage
"ym" - Monate nach Abzug Jahre
"yd" - Tage nach Abzug Jahre
"md" - Tage nach Abzug Monate und Jahre
Tabellenblattname in Zelle
=RECHTS(ZELLE("Dateiname");LÄNGE(ZELLE("Dateiname"))-FINDEN("]";ZELLE("Dateiname")))
Terminkalender erstellen
Matrixformel für einen einfachen Monatskalender zu erstellen
um einen Terminkalender zu erstellen nachfolgende Formel als Matrix* eingeben:

=WENN(MONAT(Start)<>MONAT(Start-WOCHENTAG(Start;3)+{0;1;2;3;4;5}*7+_
{1.2.3.4.5.6.7}-1);"";Start-WOCHENTAG(A2;3)+{0;1;2;3;4;5}*7+{1.2.3.4.5.6.7}-1)

start =Zelle mit dem gewünschten Datum
0,1,2,3,4,5, usw*7 bedeutet= Tabelle 5 Zeilen(wochen) und 7 spalten(Tage)
1.2.3.4.5.6.7 -1 bedeutet= 7 spalten in einer Zeile

wenn ich von 1-31 untereinander haben möchte:
0,1,2,3,4,5,6,7,8...bis37*1 = 37 Zeilen und 1 Spalte
1-1 bedeutet 1 spalte in einer Zeile

*Bei einer Matrix werden die zu füllenden Zellen markiert, die Formel eingegeben und mit STRG+Umschalt+Eingabe beendet.
(diesen Kalender zum Download finden Sie unter download)
Zellinhalte aufteilen II
Name und Vorname stehen z.B. in A1

in B1 soll Vorname stehen
in C1 der Nachname

in B1 folgende Formel:=LINKS(A1;SUCHEN(" ";A1))
in Zelle C1 =RECHTS(A1;LÄNGE(A1)-SUCHEN(" ";A1)) Wichtig ist, daß zwischen Vor- und Nachname eine Leerzeichen ist.
Spitzenwerte addieren
Die Funktion KGrößte liefert den k-größten Wert einer Datengruppe, wobei k frei gewählt werden kann. Mit K=2 wird also der zweitgrößte Wert ermittelt:

Wenn nur die Spitzenwerte addiert werden sollen, folgendes eingeben:

=Summe(Kgrößte(B2:Q2;1);Kgrößte(B2:Q2;2);Kgrößte(B2:Q2;3))
Rundungszahlen
Werte auf ein Vielfaches auf- oder abrunden
Die Zahl X wird innterhalb der Grenzen eines beliebigen Intervalls Y auf das Vielfache auf- bzw. abgerundet
z.B.Intervall=25, wenn x = 112,49 dann wert 100, wenn x=112,50, dann wert 125

=Ganzzahl((x+y/2)/y)*y
Balkendiagramme
ein einfaches Balkendiagramm ohne Diagramm-Modus
in Zelle eingeben:
=Wiederholen("Zeichen";Multiplikator)

als Zeichen kann jedes beliebige zeichen verwendet werden
Multiplikator ist die Zelle mit der Stückzahl
Euro-Umrechnung
automatisches Umrechnen von DM in Euro
'Aufruf durch Eingabe von dmwert bzw. euwert'
Private Const umrechfak As Double = 1.95583
Function euro(dmwert)
euro = dmwert / umrechfak
End Function

Function dem(euwert)
dem = euwert * umrechfak
End Function
Monatsletzten ermitteln
'Aufruf von: Ultimo(A1), wobei in A1 das Datum des gew.Monats steht'
Function Ultimo(MeinDatum)
Dim NächsterMonat, Monatsende
'"m" gibt Monate als Intervall an
NächsterMonat = DateAdd("m", 1, MeinDatum)
Monatsende = NächsterMonat - DatePart("d", NächsterMonat)
Ultimo= Monatsende
End Function
Kalenderwoche berechnen
nach DIN-Norm
'Funktionsaufruf mit =din_kw(Datum)'
Function din_kw(datum As Date) As Integer
Dim i, j, k As Integer
i = datum - DateSerial(Year(datum), 1, 1)
k = WeekDay(DateSerial(Year(datum), 1, 1), vbMonday)
j = Int((i - (8 - k)) / 7) + 1
If k <= 4 Then j = j + 1
If j = 0 Then
j = din_kw(DateSerial(Year(datum) - 1, 12, 31))
ElseIf j = 53 And WeekDay(DateSerial(Year(datum), 12, 31), vbMonday) <= 3 Then
j = 1
End If
din_kw = j
End Function
Uhrzeit in Dezimalzeit umrechnen
'die Uhrzeit mit einem Doppelpunkt eingeben z.B. 13:45
Function DezimalZeit(Uhrzeit As Date)
DezimalZeit = (Uhrzeit) * 24
DezimalZeit = Format((DezimalZeit), "##0.00")
End Function
Kalenderwoche anzeigen II
'Den Jahresersten ebenfalls als Datum eingeben z.B. 01.01.98
Function KalenderWoche(datum As Date, Jahreserster As Date)
KalenderWoche = ((datum) - (Jahreserster)) * 52 / 365 + 1
KalenderWoche = Format((KalenderWoche), "##")
End Function
Osterdatum errechnen
Ausgangsbasis für Feiertage
'Das Jahr als vierstellige Zahl eingeben
Function Osterdatum(Jahr As Variant) As Date
Dim A, B, C, D, E, J, M, N, O, Monat As Integer
If IsDate(Jahr) = True Then
J = Int(Year(Jahr))
ElseIf IsNumeric(Jahr) Then
J = Int(Jahr)
Else
Exit Function
End If
If J < 1900 Or J > 2078 Then
Exit Function
End If
M = 24
N = 5
A = J Mod 19
B = J Mod 4
C = J Mod 7
D = (19 * A + M) Mod 30
E = ((2 * B) + (4 * C) + (6 * D) + N) Mod 7
O = 22 + D + E
If O > 31 Then
O = D + E - 9
Monat = 4
If O = 26 Then O = 19
If O = 25 And D = 28 And (J Mod 19) > 10 Then O = 18
Else
Monat = 3
End If
Osterdatum = DateSerial(J, Monat, O)
End Function
Zelleninhalte aufteilen
'Aufruf durch Zelleingabe z.B. =Nachname(A1)
Function Nachname(GanzerName)
For I=0 to Len(GanzerName)-1
Umk=Umk & Mid(GanzerName,Len(GanzerName)-I,1)
Next I
Nachname=Right(GanzerName,InStr(Umk," "))
End Function