Alle Freitage ziehen (Microsoft Excel)
Wenn Sie ein Arbeitsblatt zur Verfolgung von Geschäftsinformationen entwickeln, müssen Sie möglicherweise alle Freitage in einer Reihe von Daten festlegen. Der beste Weg, dies zu tun, hängt von den Daten in Ihrem Arbeitsblatt und der Art und Weise ab, in der die Ergebnisse angezeigt werden sollen.
Wenn Sie eine Liste mit Daten in einer Spalte haben, können Sie mithilfe verschiedener Arbeitsblattfunktionen bestimmen, ob diese Daten freitags sind oder nicht.
Die WEEKDAY-Funktion gibt abhängig vom Wochentag des als Argument verwendeten Datums eine Zahl von 1 bis 7 zurück:
=WEEKDAY(A2)
Diese Verwendung gibt die Nummer 6 zurück, wenn das Datum in A2 ein Freitag ist. Wenn diese Formel neben einer Datumsspalte kopiert wird, können Sie mit der AutoFilter-Funktion von Excel nur die Daten anzeigen, an denen der Wochentag 6 (Freitag) ist.
Sie können auch die bedingte Formatierungsfunktion von Excel verwenden, um einfach alle Freitage in einer Datumsliste hervorzuheben. Befolgen Sie diese Schritte:
-
Wählen Sie die Liste der Daten.
-
Stellen Sie sicher, dass die Registerkarte Home des Menübands angezeigt wird.
-
Klicken Sie in der Gruppe Stile auf das Werkzeug Bedingte Formatierung. Excel zeigt eine Reihe von Auswahlmöglichkeiten an.
-
Klicken Sie auf Neue Regel. Excel zeigt das Dialogfeld Neue Formatierungsregel an.
(Siehe Abbildung 1.)
-
Wählen Sie im Bereich Regeltyp auswählen oben im Dialogfeld die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen. (Siehe Abbildung 2.)
-
Geben Sie im Formelbereich die folgende Formel ein und ersetzen Sie A2 durch die Adresse der in Schritt 1 ausgewählten aktiven Zelle: = WOCHENTAG (A2) = 6. Klicken Sie auf Formatieren, um das Dialogfeld Zellen formatieren anzuzeigen.
-
Stellen Sie die Formatierungsoptionen so ein, dass die Freitage wie gewünscht hervorgehoben werden.
-
Klicken Sie auf OK, um das Dialogfeld Zellen formatieren zu schließen.
-
OK klicken.
Wenn Sie eine Reihe von Freitagen basierend auf einem Anfangs- und Enddatum bestimmen möchten, können Sie eine Reihe von Formeln einrichten, um sie herauszufinden.
Angenommen, das Anfangsdatum liegt in A2 und das Enddatum in A3, können Sie das Datum des ersten Freitags mit der folgenden Formel ermitteln:
=IF(A2+IF(WEEKDAY(A2)<=6,6-WEEKDAY(A2),6)>A3, "",A2+IF(WEEKDAY(A2)<=6,6-WEEKDAY(A2),6))
Wenn Sie diese Formel in Zelle C2 einfügen und dann als Datum formatieren, können Sie die folgende Formel verwenden, um den nächsten Freitag im Bereich zu bestimmen:
=IF(C2="","",IF(C2+7>$A$3,"",C2+7))
Wenn Sie diese Formel für eine Reihe von Zellen nach unten kopieren, erhalten Sie eine Liste von Freitagen zwischen den von A2 und A3 angegebenen Datumsbereichen.
Wenn Sie tatsächlich freitags in einem bestimmten Datumsbereich „ziehen“ möchten, müssen Sie ein Makro verwenden. Es gibt verschiedene Möglichkeiten, dies zu tun. Dieses einfache Makro untersucht alle Daten im Bereich A2: A24.
Wenn es sich um Freitage handelt, wird das Datum ab C2 in Spalte C kopiert. Das Ergebnis ist natürlich, dass die Liste ab C2 nur Daten enthält, die freitags sind.
Sub PullFridays1()
Dim dat As Range Dim c As Range Dim rw As Integer
Set dat = ActiveSheet.Range("A2:A24")
rw = 2 For Each c In dat If Weekday(c) = vbFriday Then Cells(rw, 3).Value = Format(c)
rw = rw + 1 End If Next End Sub
Falls gewünscht, können Sie den vom Makro untersuchten Bereich einfach durch Ändern der Referenz A2: A24 ändern, und Sie können ändern, wo die Daten geschrieben werden, indem Sie den Wert von rw (die Zeile) und den Wert 3 (die Spalte) in der ändern Zellen funktionieren.
Wenn Sie lieber mit einem Anfangs- und einem Enddatum arbeiten möchten, können Sie das Makro so ändern, dass es die Daten schrittweise durchläuft. Das folgende Makro geht davon aus, dass sich das Anfangsdatum in Zelle A2 und das Enddatum in Zelle A3 befindet.
Sub PullFridays2()
Dim dStart As Date Dim dEnd As Date Dim rw As Integer
dStart = Range("A2").Value dEnd = Range("A3").Value
rw = 2 While dStart < dEnd If Weekday(dStart) = vbFriday Then Cells(rw, 3).Value = dStart Cells(rw, 3).NumberFormat = "m/d/yyyy"
rw = rw + 1 End If dStart = dStart + 1 Wend End Sub
Das Makro zieht die Freitage weiterhin aus dem Bereich und platziert sie ab C2 in einer Liste.
Ein weiterer Makroansatz besteht darin, eine benutzerdefinierte Funktion zu erstellen, die bestimmte Freitage innerhalb eines Bereichs zurückgibt. Das Folgende macht genau das:
Function PullFridays3(dStartDate As Date, _ dEndDate As Date, _ iIndex As Integer) Dim iMaxDays As Integer Dim dFirstday As Date Application.Volatile If dStartDate > dEndDate Then PullFridays3 = CVErr(xlErrNum) Exit Function End If dFirstday = vbFriday - Weekday(dStartDate) + dStartDate If dFirstday < dStartDate Then dFirstday = dFirstday + 7 iMaxDays = Int((dEndDate - dFirstday) / 7) + 1 PullFridays3 = "" If iIndex = 0 Then PullFridays3 = iMaxDays ElseIf iIndex <= iMaxDays Then PullFridays3 = dFirstday + (iIndex - 1) * 7 End If End Function
Sie verwenden diese Funktion in einer Zelle Ihres Arbeitsblatts folgendermaßen:
=PULLFRIDAYS3(A2,A3,1)
Das erste Argument für die Funktion ist das Startdatum und das zweite das Enddatum. Das dritte Argument gibt an, welcher Freitag innerhalb des angegebenen Bereichs zurückgegeben werden soll. Wenn Sie 1 verwenden, erhalten Sie den ersten Freitag, 2 gibt den zweiten Freitag zurück usw. Wenn Sie für das dritte Argument eine 0 verwenden, gibt die Funktion die Anzahl der Freitage im angegebenen Bereich zurück. Wenn das angegebene Anfangsdatum größer als das Enddatum ist, gibt die Funktion einen # NUM-Fehler zurück.
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (8147) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tippes für die ältere Menüoberfläche von Excel finden Sie hier: