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

Blätter sortierenMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 108) Nachricht zum Beitrag an Autor Nach oben

Die Tabellenblätter einer Mappe sind mit Tab1, Tab2, ..., Tab50, Tab51, usw. durchnummeriert. Wie kann ich sie sortieren?

Zum Sortieren der Blätter gibt es verschiedene Möglichkeiten, was auch etwas von den Rahmenbedingungen abhängig ist. Was soll zum Beispiel mit Blättern passieren, deren Namen nicht der Syntax der zu sortierenden Blattnamen entsprechen? Hier sind drei Beispiele, die ggf. noch angepasst werden müssen.

In dem Fall werden unterwegs (also nicht am Anfang) Blätter mit Namen, die nicht der Syntax entsprechen, nach hinten verschoben - ansonsten wird so lange sortiert, bis die Zahl im folgenden Blattnamen nicht mehr gößer ist:

Sub BlaetterSortieren() Dim bolSortiert As Boolean Dim intBlatt As Integer, intBlatt1 As Integer Dim varAkt, varNaechst bolSortiert = False Do While bolSortiert = False bolSortiert = True For intBlatt = 1 To Sheets.Count varAkt = Replace(Sheets(intBlatt).Name, "Tab", "") If IsNumeric(varAkt) Then For intBlatt1 = intBlatt To Sheets.Count varNaechst = Replace(Sheets(intBlatt1).Name, "Tab", "") If IsNumeric(varNaechst) Then If varNaechst * 1 < varAkt * 1 Then Sheets(intBlatt1).Move Before:=Sheets(intBlatt) bolSortiert = False End If Else Sheets(intBlatt1).Move after:=Sheets(Sheets.Count) End If Next End If Next Loop End Sub

Ein Beispiel mit Sprungmarken, Reihenfolge Tab1, Tab2, Tab11:

Sub Blattsort() Dim intAnzahl As Integer, intN As Integer, intM As Integer, intI As Integer, intZahlM As Integer, intZahlN As Integer Dim WS As Worksheet intAnzahl = ActiveWorkbook.Worksheets.Count For intM = 1 To intAnzahl For intN = intM To intAnzahl On Error Resume Next For intI = 1 To Len(Worksheets(intN).Name) If IsNumeric(Right(Worksheets(intN).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlN = Right(Worksheets(intN).Name, intI) Exit For End If Next intI For intI = 1 To Len(Worksheets(intM).Name) If IsNumeric(Right(Worksheets(intM).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlM = Right(Worksheets(intM).Name, intI) Exit For End If Next intI If CInt(intZahlN) < CInt(intZahlM) Then Worksheets(intN).Move Before:=Worksheets(intM) GoTo NAECHSTE TEXT: If Worksheets(intN).Name < Worksheets(intM).Name Then Worksheets(intN).Move Before:=Worksheets(intM) NAECHSTE: Next intN Next intM MsgBox "Anzahl der Tabellen: " & intAnzahl End Sub

Ein kurzes Beispiel, aber die Reihenfolge ist Tab1, Tab11, Tab2:

Sub Blattsort1() Dim intX As Integer Dim bolY As Boolean Do bolY = True For intX = 1 To Sheets.Count - 1 If Sheets(intX).Name > Sheets(intX + 1).Name Then Sheets(intX + 1).Move Before:=Sheets(intX) bolY = False End If Next intX Loop Until bolY = True End Sub

Pause nur berechnen, wenn anwesendUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 170) Nachricht zum Beitrag an Autor Nach oben

Pausenregelungen von 9:00 - 9:15 Uhr und 13:00 - 13:30 Uhr. Pausen dürfen nur von der Arbeitszeit abgezogen werden wenn derjenige anwesend ist. Das Pause-Feld muss sich also nach den Komm- u. Geht-Feldern richten.

Das sind verschiedene Konstellationen, die ausgewertet werden müssen. Dazu gibt es natürlich verschiedene Möglichkeiten, hier wird mal der Einsatz von Select Case demonstriert.

Das Problem ist, dass es hier keine Variable gibt, deren Wert ausgewertet werden kann. Es sind immer Bedingungen, die in Kombination zutreffen oder eben nicht. Das heißt, statt der Variablen wird True im Select verwendet:

Function Pausenzeit(kommt, geht, P1Beginn, P1Ende, P2Beginn, P2Ende) Dim datErsteZeit As Date, datZweiteZeit As Date Select Case True Case geht < P1Beginn Or kommt > P2Ende Pausenzeit = 0: Exit Function 'geht vor erster Pause oder kommt nach zweiter Pause Case kommt >= P1Beginn And kommt <= P1Ende And geht > P1Ende datErsteZeit = P1Ende - kommt 'kommt in erster Pause, geht nach erster Pause Case kommt < P1Beginn And geht > P1Ende datErsteZeit = P1Ende - P1Beginn 'kommt vor erster Pause, geht nach erster Pause Case kommt >= P2Beginn And kommt <= P2Ende And geht > P2Ende datErsteZeit = 0: datZweiteZeit = P2Ende - kommt 'kommt in zweiter Pause, geht nach zweiter Pause Case kommt < P1Beginn And geht < P1Ende datErsteZeit = geht - P1Beginn: datZweiteZeit = 0 'kommt vor erster Pause, geht in erster Pause Case kommt >= P1Beginn And geht <= P1Ende datErsteZeit = geht - kommt: datZweiteZeit = 0 'kommt und geht in erster Pause End Select Select Case True Case geht < P2Beginn: datZweiteZeit = 0 'geht vor zweiter Pause Case geht >= P2Beginn And geht < P2Ende: datZweiteZeit = geht - P2Beginn 'geht in zweiter Pause Case Else: datZweiteZeit = P2Ende - P2Beginn End Select Pausenzeit = datErsteZeit + datZweiteZeit End Function

In die Zelle muss dann nur noch:

=Pausenzeit(A4;B4;B1;C1;D1;E1)

SchleifeMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Schleifen und Programmiertechnik ▸ Schleife

(Tipp 95) Nachricht zum Beitrag an Autor Nach oben

Was ist eine Schleife und wie funktioniert sie?

Eine Schleife ist das wiederholte Abarbeiten eines Befehls oder einer Folge von Befehlen, bis eine bestimmte Bedingung erfüllt ist. Wird keine oder eine falsche Bedingung vorgegeben, wird die Schleife nicht abgebrochen. Dann spricht man von einer Endlosschleife. In VBA kann sie dafür sorgen, dass die ausführende Anwendung (hier also z. B. Excel) einfriert, also nicht mehr reagiert.

1. For-Schleife

In dieser Schleife verändert die Variable i ihren Wert von 1 bis 5 in Einerschritten, der letzte Wert ist also vorgegeben (Abbruchbedingung). Für jeden Wert wird eine Meldung ausgegeben.

Sub Schleife() For i = 1 To 5 MsgBox i Next i End Sub

2. While-Schleife:

Auch in dieser Schleife verändert die Variable i ihren Wert, der zuerst mit 1 festgelegt ist. Aber mit Do While i <= 5 wird festgelegt, daß die Schleife so lange zu durchlaufen ist, wie der Wert von i kleiner oder gleich 5 ist. Bei jedem Durchlauf soll die Meldung i ausgegeben und i um den Wert 1 erhöht werden.

Sub Schleife2() i = 1 Do While i <= 5 MsgBox i i = i + 1 Loop End Sub

In VBA gibt es bei While noch einige andere Varianten. So kann die Ausführungsdauer am Ende definiert werden (Loop While …) oder es kann auch eine Bis-Bedingung festgelegt werden (Do Until … oder Loop Until).

3. Sprungmarke:

Hier hat die Variable i auch den Startwert 1. Aber im Unterschied zu den anderen Beispielen wird hier mit einer If-Then-Else-Abfrage gearbeitet. Wenn i kleiner oder gleich 5 ist, soll die Meldung i erscheinen, anschließend zu i der Wert 1 addiert und bei der Sprungmarke nochmal: fortgesetzt werden. Wenn i im Beispiel den Wert 6 hat, ist die Bedingung i <= 5 nicht erfüllt, die Abfragebedingung ist nicht mehr erfüllt; somit werden die Schritte zwischen If und End If nicht mehr durchgeführt.

Sub Schleife3() i = 1 nochmal: If i <= 5 Then MsgBox i i = i + 1 GoTo nochmal End If End Sub

Hinweis: Die Arbeit mit Sprungmarken ist veraltet und kein guter Stil. Außerdem handelt es sich dabei eigentlich nicht um eine wirkliche Schleife.

4. Endlosschleife:

Bei dieser Schleife handelt es sich um eine Endlosschleife. Es wurde eine falsche Abbruchbedingung festgelegt (i >= 1); i wird in diesem Beispiel immer größer oder gleich 1 sein. Deshalb wird die Schleife ohne Ende weitergeführt. Hier kann die Schleife nur mit Esc abgebrochen werden (wenn kein Dialogfeld aktiv ist) bzw. mit Strg + Pause. Oder im Ernstfall mit Strg + Alt + Entf.

Sub Schleife4() i = 1 Do While i >= 1 i = i + 1 Loop End Sub

Wenn Quellzelle leer dann Zielzelle leerFormellösung

Kategorie: Tabelle ▸ Datenüberprüfung

(Tipp 186) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß bei einer Berechnung eine Zahl nur dann erscheint, wenn eine andere Zelle eine Zahl enthält?

Je nachdem, was den Gegebenheiten am besten entspricht:

=WENN(UND(ISTZAHL(A1);ISTZAHL(A2));A1+A2;"")

=WENN(A1="";"";WENN(A2="";"";A1+A2))

=WENN(A1+A2>0;A1+A2;"")