Das Tutorial zeigt, wie Sie die neue SEQUENCE-Funktion nutzen können, um schnell eine Liste von Datumsangaben in Excel zu erstellen und mithilfe der AutoFill-Funktion eine Spalte mit Datumsangaben, Arbeitstagen, Monaten oder Jahren zu füllen.
Bis vor kurzem gab es nur eine einfache Möglichkeit, Daten in Excel zu generieren – die AutoFill Feature. Die Einführung des neuen dynamischen Arrays SEQUENCE-Funktion hat es möglich gemacht, auch eine Reihe von Daten mit einer Formel zu erstellen. In diesem Tutorial werden beide Methoden eingehend untersucht, damit Sie die für Sie am besten geeignete auswählen können.
So füllen Sie Datumsreihen in Excel
Wenn Sie eine Spalte in Excel mit Datumsangaben füllen müssen, verwenden Sie am schnellsten die AutoFill-Funktion.
Füllen Sie eine Datumsreihe automatisch in Excel aus
Das Füllen einer Spalte oder Zeile mit Daten, die um einen Tag erhöht werden, ist sehr einfach:
- Geben Sie das Anfangsdatum in die erste Zelle ein.
- Wählen Sie die Zelle mit dem Anfangsdatum aus und ziehen Sie den Füllgriff (ein kleines grünes Quadrat in der unteren rechten Ecke) nach unten oder rechts.
Excel generiert sofort eine Reihe von Datumsangaben im selben Format wie das erste Datum, das Sie manuell eingegeben haben.
Füllen Sie eine Spalte mit Wochentagen, Monaten oder Jahren
Führen Sie einen der folgenden Schritte aus, um eine Reihe von Arbeitstagen, Monaten oder Jahren zu erstellen:
- Füllen Sie eine Spalte mit aufeinander folgenden Daten wie oben beschrieben. Klicken Sie danach auf AutoFill-OptionenKlicken Sie auf die Schaltfläche und wählen Sie beispielsweise die gewünschte Option aus Monate füllen::
- Oder Sie können Ihr erstes Datum eingeben, mit der rechten Maustaste auf den Füllpunkt klicken, so viele Zellen wie nötig halten und durchziehen. Wenn Sie die Maustaste loslassen, wird ein Kontextmenü angezeigt, in dem Sie die gewünschte Option auswählen können. Jahre füllen in unserem Fall:
Füllen Sie eine Reihe von Daten aus, die um N Tage erhöht werden
So generieren Sie automatisch eine Reihe von Tagen, Wochentagen, Monaten oder Jahren mit a spezifischer Schritt, das müssen Sie tun:
- Geben Sie das Anfangsdatum in die erste Zelle ein.
- Wählen Sie diese Zelle aus, klicken Sie mit der rechten Maustaste auf den Füllpunkt, ziehen Sie ihn durch so viele Zellen wie nötig und lassen Sie ihn dann los.
- Wählen Sie im Popup-Menü die Option Serie (der letzte Punkt).
- In dem Serie Wählen Sie im Dialogfeld die Datumseinheit von Interesse und setzen die Schrittwert.
- OK klicken.
So erstellen Sie eine Datumsfolge in Excel mit einer Formel
In einem der vorherigen Tutorials haben wir uns mit der Verwendung der neuen SEQUENCE-Funktion für dynamische Arrays befasst Generieren Sie eine Zahlenfolge. Da intern in Excel Daten als Seriennummern gespeichert werden, kann die Funktion auch problemlos eine Datumsreihe erstellen. Sie müssen lediglich die Argumente wie in den folgenden Beispielen erläutert korrekt konfigurieren.
Erstellen Sie eine Reihe von Daten in Excel
Richten Sie die folgenden Argumente der Funktion SEQUENCE ein, um eine Folge von Datumsangaben in Excel zu generieren:
SEQUENZ (Zeilen, [columns], [start], [step])
- Reihen – Die Anzahl der Zeilen, die mit Datumsangaben gefüllt werden sollen.
- Säulen – die Anzahl der Spalten, die mit Datumsangaben gefüllt werden sollen.
- Anfang – das Startdatum in dem Format, das Excel verstehen kann, wie „8/1/2020“ oder „1-Aug-2020“. Um Fehler zu vermeiden, können Sie das Datum mit dem eingeben DATUM Funktion wie DATE (2020, 8, 1).
- Schritt – das Inkrement für jedes nachfolgende Datum in einer Sequenz.
Um beispielsweise eine Liste von 10 Daten zu erstellen, die am 1. August 2020 beginnen und um einen Tag ansteigen, lautet die Formel:
=SEQUENCE(10, 1, "8/1/2020", 1)
oder
=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)
Alternativ können Sie die Anzahl der Daten (B1), das Startdatum (B2) und den Schritt (B3) in vordefinierte Zellen eingeben und auf diese Zellen in Ihrer Formel verweisen. Da wir eine Liste erstellen, ist die Spaltennummer (1) fest codiert:
=SEQUENCE(B1, 1, B2, B3)
Geben Sie die folgende Formel in die oberste Zelle ein (in unserem Fall A6) und drücken Sie die Taste Eingeben Wenn Sie diese Taste drücken, werden die Ergebnisse automatisch über die angegebene Anzahl von Zeilen und Spalten verteilt.
Erstellen Sie eine Reihe von Arbeitstagen in Excel
Um nur eine Reihe von Arbeitstagen zu erhalten, wickeln Sie SEQUENCE wie folgt in die Funktion WORKDAY oder WORKDAY.INTL ein:
WERKTAG(Anfangsdatum -1, SEQUENZ (Anzahl der Tage))
Da die WORKDAY-Funktion die im zweiten Argument angegebene Anzahl von Tagen zum Startdatum hinzufügt, subtrahieren wir 1 davon, um das Startdatum selbst in die Ergebnisse aufzunehmen.
Um beispielsweise eine Folge von Arbeitstagen ab dem Datum in B2 zu generieren, lautet die Formel:
=WORKDAY(B2-1, SEQUENCE(B1))
Wobei B1 die Sequenzgröße ist.
- Wenn ein Starttermin Samstag oder Sonntag ist, beginnt die Serie am nächsten Arbeitstag.
- Das Excel WERKTAG Die Funktion geht davon aus, dass Samstag und Sonntag Wochenenden sind. Verwenden Sie die, um benutzerdefinierte Wochenenden und Feiertage zu konfigurieren WORKDAY.INTL Funktion stattdessen.
Generieren Sie eine Monatssequenz in Excel
Mit dieser generischen Formel können Sie eine Reihe von Daten erstellen, die um einen Monat erhöht sind:
DATUM(Jahr, SEQUENZ (12), Tag)
In diesem Fall setzen Sie das Zieljahr in die 1st Argument und Tag in der 3rd Streit. Für die 2nd Argument, die SEQUENCE-Funktion gibt fortlaufende Zahlen von 1 bis 12 zurück. Basierend auf den obigen Parametern erzeugt die DATE-Funktion eine Reihe von Daten, wie im linken Teil des folgenden Screenshots gezeigt:
=DATE(2020, SEQUENCE(12), 1)
Nur die anzeigen MonatsnamenStellen Sie eine der folgenden Optionen ein benutzerdefinierte Datumsformate für den Überlaufbereich:
- mmm – Kurzform wie Jan., Feb., Beschädigen, etc.
- mmmm – Vollform wie Januar, Februar, März, etc.
Infolgedessen werden nur die Monatsnamen in den Zellen angezeigt, die zugrunde liegenden Werte sind jedoch weiterhin vollständige Daten. Beachten Sie in beiden Serien im folgenden Screenshot die für Zahlen und Daten in Excel typische Standardausrichtung nach rechts:
So generieren Sie eine Datumssequenz, die um einen Monat und erhöht wird beginnt mit einem bestimmten DatumVerwenden Sie die SEQUENCE-Funktion zusammen mit EDATE:
EDATE (Anfangsdatum, SEQUENZ (12, 1, 0))
Das EDATE Die Funktion gibt ein Datum zurück, das der angegebenen Anzahl von Monaten vor oder nach dem Startdatum entspricht. Und die SEQUENCE-Funktion erzeugt ein Array von 12 Zahlen (oder so vielen, wie Sie angeben), um EDATE zu zwingen, in Schritten von einem Monat vorwärts zu gehen. Bitte beachten Sie, dass die Anfang Das Argument wird auf 0 gesetzt, damit das Startdatum in die Ergebnisse aufgenommen wird.
Mit dem Startdatum in B1 nimmt die Formel folgende Form an:
=EDATE(B1, SEQUENCE(12, 1, 0))
Erstellen Sie eine Jahresfolge in Excel
Verwenden Sie diese generische Formel, um eine Reihe von Daten nach Jahr zu erhöhen:
DATUM (SEQUENZ (n, 1 JAHR(Anfangsdatum)), MONAT (Anfangsdatum), TAG(Anfangsdatum))
Wo n ist die Anzahl der Daten, die Sie generieren möchten.
In diesem Fall erstellt die Funktion DATE (Jahr, Monat, Tag) ein Datum auf folgende Weise:
- Jahr wird von der SEQUENCE-Funktion zurückgegeben, die zum Generieren eines konfiguriert ist n Zeilen für 1 Spalte Array von Zahlen, beginnend mit dem Jahreswert von Anfangsdatum.
- Monat und Tag Werte werden direkt ab dem Startdatum abgerufen.
Wenn Sie beispielsweise das Startdatum in B1 eingeben, gibt die folgende Formel eine Reihe von 10 Daten in Schritten von einem Jahr aus:
=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))
Nach dem Sein als Datum formatiertDie Ergebnisse sehen wie folgt aus:
Generieren Sie eine Zeitfolge in Excel
weil Zeiten werden in Excel gespeichert Als Dezimalzahlen, die einen Bruchteil des Tages darstellen, kann die SEQUENCE-Funktion direkt mit Zeiten arbeiten.
Angenommen, die Startzeit liegt in B1, können Sie eine der folgenden Formeln verwenden, um eine Serie von 10 Mal zu erstellen. Der Unterschied liegt nur in der Schritt Streit. Da ein Tag 24 Stunden hat, verwenden Sie 1/24, um eine Stunde zu erhöhen, 1/48, um 30 Minuten zu erhöhen, und so weiter.
30 Minuten auseinander:
=SEQUENCE(10, 1, B1, 1/48)
1 Stunde auseinander:
=SEQUENCE(10, 1, B1, 1/24)
2 Stunden auseinander:
=SEQUENCE(10, 1, B1, 1/12)
Der Screenshot unten zeigt die Ergebnisse:
Wenn Sie den Schritt nicht manuell berechnen möchten, können Sie ihn mithilfe von definieren TIME-Funktion::
SEQUENCE (Zeilen, Spalten, Start, TIME (Stunde, Minute, zweite))
In diesem Beispiel geben wir alle Variablen in separaten Zellen ein, wie im folgenden Screenshot gezeigt. Anschließend können Sie mit der folgenden Formel eine Zeitreihe mit einer beliebigen Schrittgröße erstellen, die Sie in den Zellen E2 (Stunden), E3 (Minuten) und E4 (Sekunden) angeben:
=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))
So erstellen Sie einen Monatskalender in Excel
In diesem letzten Beispiel verwenden wir die SEQUENCE-Funktion zusammen mit DATEVALUE und WOCHENTAG um einen Monatskalender zu erstellen, der automatisch basierend auf dem von Ihnen angegebenen Jahr und Monat aktualisiert wird.
Die Formel in A5 lautet wie folgt:
=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1, 1)
Wie diese Formel funktioniert:
Mit der SEQUENCE-Funktion generieren Sie ein 6-Zeilen-Array (die maximal mögliche Anzahl von Wochen in einem Monat) mit 7 Spalten (die Anzahl der Tage in einer Woche) von Datumsangaben, die um 1 Tag erhöht werden. Daher die Reihen, Säulen und Schritt Argumente werfen keine Fragen auf.
Der schwierigste Teil in der Anfang Streit. Wir können unseren Kalender nicht mit der 1 beginnenst Tag des Zielmonats, da wir nicht wissen, welcher Wochentag es ist. Wir verwenden also die folgende Formel, um den ersten Sonntag vor dem 1 zu findenst Tag des angegebenen Monats und Jahres:
DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
Die erste DATEVALUE-Funktion gibt eine Seriennummer zurück, die im internen Excel-System die 1 darstelltst Tag des Monats in B2 und das Jahr in B1. In unserem Fall ist es 44044 entsprechend dem 1. August 2020. Zu diesem Zeitpunkt haben wir:
44044 - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
Die WEEKDAY-Funktion gibt den Wochentag zurück, der der 1 entsprichtst Tag des Zielmonats als Zahl von 1 (Sonntag) bis 7 (Samstag). In unserem Fall ist es 7, weil der 1. August 2020 Samstag ist. Und unsere Formel reduziert sich auf:
44044 - 7 + 1
44044 – 7 ist 4403, was dem Samstag, dem 25. Juli 2020, entspricht. Da wir den Sonntag benötigen, fügen wir die + 1-Korrektur hinzu.
Auf diese Weise erhalten wir eine einfache Formel, die ein Array von Seriennummern ab 4404 ausgibt:
=SEQUENCE(6, 7, 4404, 1)
Wenn Sie die Ergebnisse als Datum formatieren, wird im obigen Screenshot ein Kalender angezeigt. Sie können beispielsweise eine der folgenden Optionen verwenden Datumsformate::
- d-mmm-yy um Daten wie anzuzeigen 1-Aug-20
- mmm d Monat und Tag wie anzeigen 20. August
- d um nur den Tag anzuzeigen
Warten Sie, aber wir möchten einen Monatskalender erstellen. Warum werden einige Daten des vorherigen und nächsten Monats angezeigt? Um diese irrelevanten Daten zu verbergen, richten Sie eine bedingte Formatierungsregel mit der folgenden Formel ein und wenden Sie die an weiße Schrift Farbe:
=MONTH(A5)<>MONTH(DATEVALUE($B$2 & "1"))
Dabei ist A5 die Zelle ganz links in Ihrem Kalender und B2 der Zielmonat.
Die detaillierten Schritte finden Sie unter So erstellen Sie eine formelbasierte bedingte Formatierungsregel in Excel.
Übungsarbeitsbuch zum Download
Datumsfolge in Excel – Formelbeispiele (XLSX-Datei)
Sie könnten auch interessiert sein an:
Der Beitrag Erstellen Sie eine Datumsfolge in Excel und füllen Sie Datumsreihen automatisch aus erschien zuerst auf Anfänger Kurse.
from
https://fuer-anfaenger.info/erstellen-sie-eine-datumsfolge-in-excel-und-fuellen-sie-datumsreihen-automatisch-aus/
No comments:
Post a Comment