David hat ein Arbeitsblatt, das nur zwei Spalten enthält: ein Datum und einen Portfolio-Wert für jedes Datum. Das Arbeitsblatt enthält Zeilen für jeden Handelstag von Januar 1999 bis heute. David muss alle Zeilen außer denen löschen, die für den letzten Handelstag eines jeden Monats gelten. Er hat versucht zu filtern, aber das hat nicht geholfen, daher ist er verwirrt darüber, wie die nicht benötigten Zeilen am besten gelöscht werden können.

Bei der Bereitstellung von Möglichkeiten zur Lösung dieses Problems ist einer der Schlüssel in der Frage, auf die ich mich konzentrieren werde, dass David sagte, seine Daten hätten „Zeilen für jeden Handelstag“. Für mich bedeutet dies, dass einige Daten (Nicht-Handelstage) nicht in seinen Daten enthalten sind. Dies ist wichtig, da wir keine Lösung finden müssen, die vor der Entscheidung, ob eine Zeile beibehalten werden soll oder nicht, bestimmt, ob ein Datum ein Handelstag ist.

Dies erleichtert die Arbeit erheblich. Jetzt können wir anhand der Daten in Spalte A einfach die Zeilen finden, die tatsächlich das letzte (oder „höchste“) Datum in einem bestimmten Monat enthalten. Ich werde mich zunächst auf einen manuellen Ansatz konzentrieren, der auf einer Hilfssäule beruht. Da David sagte, dass seine Daten nur aus den Spalten A (Datum) und B (Wert) bestehen, werde ich vorschlagen, Spalte C als Hilfsspalte zu verwenden. (Ich gehe auch davon aus, dass Zeile 1 Spaltenüberschriften enthält und die realen Daten in Zeile 2 beginnen.)

Stellen Sie sicher, dass Ihre Daten so sortiert sind, dass die Daten in aufsteigender Reihenfolge vorliegen, und fügen Sie die folgende Formel in Zelle C2 ein:

=IF(DAY(A3)<DAY(A2),"EOM","X")

Kopieren Sie diese Formel für alle Ihre Daten nach unten, und Sie sind im Wesentlichen fertig. Falls gewünscht, können Sie jetzt die Filterung basierend auf Spalte C verwenden. Wenn Sie so filtern, dass nur Zeilen mit „EOM“ angezeigt werden, haben Sie Ihre endgültigen Werte für jeden Monat. Wenn Sie so filtern, dass nur Zeilen mit „X“ angezeigt werden, können Sie diese Zeilen löschen, den Filter entfernen und nur Zeilen mit den Werten zum Monatsende in Ihren Daten haben.

Wie so oft gibt es eine Vielzahl von Formeln, die Sie in Spalte C anstelle der von mir vorgeschlagenen verwenden können. Ich habe diesen jedoch vorgeschlagen, da er einen sehr einfachen Vergleich darstellt, der immer überprüfbar ist – ob der Tag in der Zeile nach dem aktuellen Tag „an Wert verliert“. In jedem möglichen Szenario gilt dies erst am Ende eines Monats. Diese Zeile ist also mit „EOM“ und der Rest mit „X“ markiert.

Ich möchte darauf hinweisen, dass wenn Sie eine andere Formel verwenden, stellen Sie sicher, dass diese nicht testet, ob das Datum in Spalte A der letzte Tag des Monats ist. Warum? Weil dies möglicherweise nicht der Fall ist – denken Sie daran, dass in Davids Daten Spalte A die Daten der Handelstage enthält und es sehr wahrscheinlich ist, dass der letzte Handelstag eines Monats nicht auf den letzten Tag des Monats fällt. (Mit anderen Worten, Wochenenden und Feiertage sind per Definition von Davids Daten ausgeschlossen.)

Wenn Sie Office 365 verwenden (oder was Microsoft heutzutage als Microsoft 365 bezeichnet), können Sie auch nur die Monatsenddaten und deren Werte abrufen. Nehmen wir an, Ihre Daten befinden sich in A2: B5000. (Denken Sie daran, dass A1: B1 Spaltenüberschriften enthält.) Fügen Sie die folgende Formel in Zelle E2 ein:

=FILTER(A2:B5000,DAY(A3:A5001)<DAY(A2:A5000),1)

Das ist es; eine einzelne Formel in einer einzelnen Zelle. Möglicherweise müssen Sie Spalte E formatieren, um Datumsangaben ordnungsgemäß anzuzeigen. Bei Verwendung der Funktion FILTER wird jedoch der bereits beschriebene Vergleich durchgeführt, und es werden nur die Daten und Werte zum Monatsende abgerufen. (Dies ist ziemlich schwierig, wenn Sie darüber nachdenken.) Denken Sie jedoch daran, dass es nur in Office 365 funktioniert. Es funktioniert nicht in Excel 2019, Excel 2016 oder einer früheren Version des Programms.

Wenn Sie einen makrobasierten Ansatz bevorzugen, reicht das folgende kurze Makro aus:

Sub DelRows()

Dim LastRow As Long     Dim J As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row     LastRow = LastRow - 1     For J = LastRow To 2 Step -1         If Month(Cells(J, 1)) = Month(Cells(J + 1, 1)) Then             Rows(J).EntireRow.Delete         End If     Next J End Sub

Das Makro bestimmt die letzte Zeile im Arbeitsblatt (gespeichert in der LastRow-Variablen) und verwendet dann eine For …​ Next-Schleife, um die Zeilen rückwärts zu durchlaufen. Wenn der Monat der aktuellen Zeile dem Monat der folgenden Zeile entspricht, wird die Zeile gelöscht. Beachten Sie, dass das Makro LastRow dekrementiert, bevor Sie in die For …​ Next-Schleife springen. Dies geschieht, weil davon ausgegangen wird, dass die letzte Datenzeile immer der letzte Handelstag Ihrer Daten ist und daher immer bei den abgespeckten Daten bleibt.

Dieses Makro kann langsam ausgeführt werden, da die meisten Zeilen im Arbeitsblatt nacheinander gelöscht werden. Wenn dies abgeschlossen ist, bleiben jedoch nur noch Ihre Daten zum Monatsende übrig.

Ein letzter Hinweis: Die in diesem Tipp verwendeten Ansätze zerstören mit Ausnahme der Funktion FILTER Ihre Daten. Wenn Sie sie verwenden, gehen die Daten in Ihrem Arbeitsblatt für immer verloren. Dies bedeutet, dass Sie zweimal (oder dreimal) überlegen sollten, bevor Sie sie auf etwas anderem als einer Kopie Ihrer Originaldaten ausführen.

_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.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13768) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.