Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Bedingte Formatierung: Drei Preise - günstigsten farbig kennzeichnenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorien: Format ▸ Bedingt und Tabelle ▸ Matrix

(Tipp 326) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die günstigsten Preise hervorheben?

In einer Tabelle stehen in einer Spalte (hier: D) untereinander verschiedene Produktnamen. Bei jedem Produkt stehen rechts daneben (hier: E, F und G) die Preise für drei verschiedene Länder.

Die Preise bei jedem Produkt sollen automatisch farbig markiert werden: der günstigste grün, der höchste rot und ansonsten gelb.

Lösung


Die Aufgabe wird mit Bedingte Formatierung im Ribbon Start erfüllt.

Dazu müssen für jede der drei Preisspalten jeweils drei Regeln erstellt werden - eben eine für den günstigsten, eine für den höchsten und eine für den restlichen Preis. Wir fangen an mit der ersten Spalte, in der Preise enthalten sind, hier also E2:E19 und markieren diese. Anschließend rufen wir die bedingte Formatierung auf und wählen dort Neue Regel.

In der Auswahlliste wählen wir den Punkt Nur Zellen formatieren, die enthalten. Dies ist die Basis für die weiteren Formatierungen.

Grüne Formatierung

Die grüne Formatierung soll erscheinen, wenn der Preis am niedrigsten ist. Hier bietet sich also die Funktion MIN zum Vergleich mit den anderen beiden Preisen an. Wir stellen also beim Zellwert auf kleiner als und tragen rechts ein: =MIN(F2:G2). Mit dem Button Formatieren legen wir die grüne Hintergrundfarbe fest.

Mit OK übernehmen wir die Regel.

Rote Formatierung

Mit rotem Hintergrund soll gekennzeichnet werden, wenn der Preis am höchsten ist. Dazu ist die Funktion MAX sinnvoll.

Wir bleiben also im Dialog und wählen Neue Regel. Anschließend führen wir die gleichen Schritte wie bei der grünen Formatierung durch, nur eben mit größer als, MAX und der roten Formatierung.

Gelbe Formatierung

Einen gelben Hintergrund soll die Zelle bekommen, wenn der Preis nicht am höchsten und nicht am niedrigsten ist, wenn er also zwischen den beiden anderen Preisen liegt.

Wir bleiben weiterhin im Dialog und wählen wieder Neue Regel. Wenn wir nun wieder Nur Zellen formatieren, die enthalten anklicken, müsste beim Zellwert schon zwischen ausgewählt sein - wenn nicht, dies nachholen.

In die Felder schreiben wir jeweils =F2 und =G2. Gelben Hintergrund auswählen und bestätigen. Nun müssten die drei Regeln im Manager angezeigt werden.

Wenn nun Zahlen eingetragen werden, sollte das beim ersten Land funktionieren, die Hintergründe müssten automatisch entsprechend der Zahlen formatiert werden. Allerdings müssen die Schritte noch für die anderen beiden Länder wiederholt werden. Beim mittleren Land aufpassen; die Zellen in MIN und MAX müssen hier mit Semikolon getrennt werden, weil es keine Bis-Bereiche sind, sondern auseinanderliegende Zellen.


Auswertung: Matrixformel

Unter der Tabelle sollen nun noch zu jedem Land die grünen, roten und gelben Zellen gezählt werden. Das Problem: Mit einer reinen Formellösung können keine farbigen Zellen gezählt werden.

Ein Lösungsansatz ist, zu zählen, wie viele Zellen in der jeweiligen Spalte größer bzw. kleiner als die Zellen daneben sind. Damit wir nicht jede Zeile einzeln berücksichtigen müssen, verwenden wir dazu eine Matrixformel, die den Bereich einer Spalte über alle Zeilen hinweg erfasst.

Hinweis: Die geschweiften Klammern nicht eingeben, sondern die Eingabe der Formel mit Strg + Umschalt + Enter abschließen. Damit erscheinen die geschweiften Klammern automatisch. Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig.

Wir beginnen beim ersten Land, hier mit der Formel in E24. Es sollen die Zellen gezählt werden, die in den Zeilen die niedrigsten Preise haben. Dabei zählen wir aber nicht, sondern wir addieren für jede dieser niedrigsten Zellen die 1. Wir bilden also die Summe, hier das Grundgerüst:

=SUMME( wenn Zahl in der Zeile am niedrigsten ; dann addiere 1; sonst addiere 0))

Wir verwenden hier diese Logik (andere Varianten gibt es natürlich auch):

Wenn die Zahl beim Land 1 (E) kleiner als die Zahl beim Land 2 (F) ist, dann wenn die Zahl beim Land 1 (E) auch kleiner als die beim Land 3 (G) ist, dann addiere 1, sonst 0, sonst 0.

Wir addieren hier also zweimal 0 - einmal für die erste Bedingung (Land 1 nicht kleiner als Land 2) und einmal für die zweite Bedingung (Land 1 nicht kleiner als Land 3).

Diese Struktur bauen wir in die Formel ein, so dass die (an Strg + Umschalt + Enter denken!) nun so aussieht:

{=SUMME(WENN(E2:E19<F2:F19;WENN(E2:E19<G2:G19;1;0);0))}

Damit haben wir die Anzahl der grünen Zellen beim Land 1 in E24. Die gleiche Formel kommt zu den anderen Ländern. Vorsicht, die kann aber nicht gezogen werden, weil die Zellen in den Formeln angepasst werden müssen.

Ebenfalls die gleiche Formel, nur mit dem größer als >, kommt in die Zeile 26, wo die roten Zellen gezählt werden:

{=SUMME(WENN(E2:E19>F2:F19;WENN(E2:E19>G2:G19;1;0)))}

Bei den gelben Zellen geht es einfacher - einfach alle Zellen in der Spalte zählen und die grünen und roten subtrahieren:

=ANZAHL(E2:E19)-E24-E26

Eine Beispieldatei mit dieser Lösung: 326_preisvergleiche.xlsx

Datum per Drehfeld auswählenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Datum/Zeit ▸ Steuerelemente

(Tipp 79) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Datum per Drehfeld in einer Userform auswählen?

Der Code benötigt eine Userform1, darauf eine TextBox1 und einen SpinButton1 (Drehfeld).

Für den Aufruf der Userform legen wir fest, dass das Maximum des SpinButtons gesetzt wird, hier 401768 für den 31.12.2999. Außerdem soll in der TextBox das aktuelle Datum angezeigt und beim SpinButton der Wert des aktuellen Datums eingestellt werden:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub

Dem SpinButton weisen wir zu, dass beim Betätigen dessen Wert im Datumsformat in die Textbox eingetragen wird:

Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub

Fehlt noch die TextBox - wenn dort ein gültiges Datum (Angaben durch Punkt oder Minus getrennt) eingetragen wird, soll der Wert dieses Datums dem SpinButton zugewiesen werden, damit bei dessen Betätigen in der Textbox das vorherige oder nächste Datum eingetragen wird:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

Der Code zum Kopieren:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

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

Wert in der Zelle neben dem Maximalwert ermittelnFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 209) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den Wert einer Zelle rechts neben der Zelle mit dem Maximalwert ermitteln?

SVERWEIS()

=SVERWEIS(MAX(A:A);A:B;2;0)


Ab Excel 365: FILTER()

Hier steht die Funktion FILTER() zur Verfügung, die den Eintrag aus Spalte B zurückgibt, wo in A das Maximum steht:

=FILTER(B1:B5;A1:A5=MAX(A1:A5))

Auf den ersten Blick scheint es das gleiche Verhalten wie beim SVERWEIS zu sein. Deutlich wird es jedoch, wenn das Maximum in zwei verschiedenen Zellen enthalten ist. Dann gibt der SVERWEIS die erste Fundstelle zurück, die Filter-Funktion jedoch alle.

Werte in einem Bereich zählen (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(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?

UDF - benutzerdefinierte Funktion

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

Function Zaehlen(Bereich As Range, Minimum, Maximum) Dim objZelle As Object, intI As Integer intI = 0 For Each objZelle In Bereich If objZelle.Value >= Minimum And objZelle.Value <= Maximum Then intI = intI + 1 End If Next Zaehlen = intI End Function

In die Zelle muß dann eingegeben werden:

=Zählen(Bereich;kleinste Zahl;größte Zahl)

=Zählen(A1:A100;10;20)


Formel

Am einfachsten ist jedoch, wenn die integrierte Funktion ZÄHLENWENNS() verwendet wird:

=ZÄHLENWENNS(A1:A10;">2";A1:A10;"<7")


Dynamische Arrayfunktion (ab Excel 365)

Sollen die Zahlen, die zu den Kriterien passen, auch gleich ausgegeben werden, kann das mit der Funktion FILTER() erfolgen:

=FILTER(A1:A10;(A1:A10>2)*(A1:A10<7);"")

Die Zahlen stehen dann untereinander an der Zelle mit der Formel und können auch mit ANZAHL() gezählt werden. Dabei gibt es jedoch eine Besonderheit.

Gibt man =ANZAHL( ein und zieht dann über den Bereich mit den gefundenen Zahlen, wird in die Funktion nicht der Bereich eingetragen, sondern eine ID wie zum Beispiel C3#:

=ANZAHL(C3#)

Die vergibt Excel selbst. Dabei handelt es sich um eine Referenz auf das Ergebnis der Funktion FILTER(), denn die Größe der Ergebnismenge dieser Funktion kann sich ja ändern. Durch diese Referenz wird immer richtig gezählt - egal, ob FILTER() 0, 5 oder sonst wie viele Ergebnisse liefert.

Natürlich ist auch sowas möglich:

=ANZAHL(FILTER(A1:A10;(A1:A10>2)*(A1:A10<7);""))

Zufallszahlen sortiert in einem bestimmten Bereich generieren (mit Arrayfunktion)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Filter/Sortieren

(Tipp 139) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zwischen 6 und 15 Zufallszahlen zwischen 1 und 49 generieren? Die Zahlen sollen auf dem Blatt Tab1 in der Zeile 1 ab A1 stehen. Keine Zahl darf sich wiederholen.

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub


Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stößt man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.


Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den großen Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das heißt, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler ÜBERLAUF.