Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins
Excel/VBA

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

Hinweise zu den Beispielen finden Sie hier: Beispiele


Erweiterung für MAX(), z. B. dritthöchster Wert?FormellösungArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 176) Nachricht zum Beitrag an Autor Nach oben

Kann ich mit einer Formel/Funktion (=max) auch den zweit- oder dritthöchsten Wert ziehen, ohne sortieren zu müssen?

Dafür gibt es eine weitere Funktion:

=KGRÖSSTE(B1:B14;2)
ermittelt den zweithöchsten Wert in B1 bis B14,
=KGRÖSSTE(B1:B14;3)
den dritthöchsten.

Dementsprechend funktioniert auch die Funktion KKLEINSTE, sie sucht nach den kleinsten Zahlen.


Mit der Funktion FILTER() kann man sich (ab Excel 365) auch gleich die Datensätze ausgeben lassen, die zu den entsprechenden Kriterien passen:

=FILTER(B1:C14;B1:B14=KGRÖSSTE(B1:B14;D1))

Geokoordinaten/Großkreis: Entfernung berechnen

Kategorie: Berechnungen

(Tipp 154) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Entfernungen zwischen Geokoordinaten berechnen?

Folgende Funktion in ein Standardmodul kopieren:

Function Koordinatenentfernung(ByVal lat1 As Single, ByVal lon1 As Single, ByVal lat2 As Single, ByVal lon2 As Single) Koordinatenentfernung = Application.WorksheetFunction.Acos(Sin((lat1 * Application.WorksheetFunction.Pi() / 180)) * Sin((lat2 * Application.WorksheetFunction.Pi() / 180)) + Cos((lat1 * Application.WorksheetFunction.Pi() / 180)) * Cos((lat2 * Application.WorksheetFunction.Pi() / 180)) * Cos((lon2 * Application.WorksheetFunction.Pi() / 180) - (lon1 * Application.WorksheetFunction.Pi() / 180))) * 6378.137 End Function

In die Zelle kommt die Formel:

=Koordinatenentfernung(F1;F2;D5;E5)

Mit Prüfung auf leere Zellen und zum Ziehen kann ab Excel 365 die Formel verwendet werden:

=@WENN(ISTZAHL(D5:E5);Koordinatenentfernung($F$1;$F$2;D5;E5);"")

Oder in älteren Versionen:

=WENN(UND(ISTZAHL(D5);ISTZAHL(E5));Koordinatenentfernung($F$1;$F$2;D5;E5);"")

Die Zellen mit den Ergebnissen können mit 0,00 "km" formatiert werden.

Quersumme bildenUDF - benutzerdefinierte Funktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 165) Nachricht zum Beitrag an Autor Nach oben

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

Function Quersumme(Zelle As Range) As Integer Dim intI As Integer Application.Volatile Quersumme = 0 If IsNumeric(Zelle) Then For intI = 1 To Len(Zelle) Quersumme = Quersumme + CInt(Mid(Zelle, intI, 1)) Next End If End Function

In die Zellen braucht man dann nur die Formel einzugeben:

=Quersumme(A1)

Eine interessante Lösung per Formel, die die Zeilennummern verwendet, finden Sie hier: exceltricks.blog.

Runden auf volle Fünfer oder fünf CentFormellösung

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 196) Nachricht zum Beitrag an Autor Nach oben

Gibt es eine EXCEL-Funktion, um auf fünf Cent zu runden oder auf volle Fünfer, z. B. immer auf 5 oder auf 0?

Auf fünf Cent:

=VRUNDEN(A1;0,05)

Auf volle Fünfer:

=VRUNDEN(A1;5)

Summewenn mit mehreren KriterienFormellösungArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 181) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit der Funktion SUMMEWENN auch mehrere Kriterien bestimmen?

Dazu bietet sich eine Array-Funktion an. Die erreicht man, indem man erst die normale Formel eingibt und die Eingabe mit Strg + Umschalttaste + Enter beendet. Dann werden automatisch um die Formel geschweifte Klammern ({}) gesetzt, woran man die Array-Funktion erkennt.

Beispiel: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, dass es ein Array ist - diese nicht mit kopieren/eingeben, sondern im Bearbeitungsmodus mit Strg + Umschalttaste + Enter erzeugen.

{=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))}

Ab Excel 365

Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig, die Formel kann ganz normal eingetragen und die Eingabe einfach mit Enter abgeschlossen werden:

=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))

Hier geht allerdings auch die Funktion SUMMEWENNS(), in die mehrere Kriterien als Parameter eingetragen werden können. Im Beispiel wäre das:

=SUMMEWENNS(C1:C11;A1:A11;"a";B1:B11;"h")