In einem Excel-Arbeitsblatt habe ich einige Spalten, die ich chronologisch nach Monaten neu anordnen möchte. Bisher mache ich das manuell und es wird ziemlich mühsam.
Ich habe versucht, mein Problem hier zu veranschaulichen:
Monat und Jahr wurden als Textformat statt als Datumsformat geschrieben (in der Form mmm'yy – einschließlich Apostroph). Ich habe versucht, ein benutzerdefiniertes Format für die Monate zu erstellen und die Zeilen auf dieser Grundlage neu anzuordnen, aber die benutzerdefinierte Sortierung scheint ausgegraut zu sein. Ich bin ein Anfänger in Excel, daher wäre ich für jede Hilfe dankbar.
Antwort1
Der Trick für eine gute Sortierung besteht darin, dass Excel wissen muss, dass es sich um ein Datum handelt.
Den Text in Zelle C3 können Sie beispielsweise mit dieser Formel in Zelle C4 in ein Datum umwandeln:
=DATE(2000+RIGHT(C3,2),XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)),1)
Es sieht lang und unhandlich aus, ist aber in seinen Grundzügen relativ einfach.
Wir verwenden die DATEFunktion. Diese nimmt drei Argumente an (durch Kommas getrennt):
- Jahr
- Monat
- Tag
Das Jahr ist 2000+RIGHT(C3,2). Dazu werden einfach die beiden am weitesten rechts stehenden Zeichen aus Ihrem Text zu 2000 addiert, um das Jahr zu erhalten. Wenn Sie Daten vor 2000 haben, müssen Sie diesen Teil anpassen.
Der Monat ist XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)). Dies bedeutet: „Suchen Sie in dieser Monatsliste nach den ersten drei Zeichen in Zelle C3. Geben Sie den Wert von derselben Position zurück (also bei April die 4. Position in der Liste), von SEQUENCE(1,12).“ Diese SEQUENZ ist nur eine Liste von Zahlen von 1 bis 12. Daher konvertieren wir den dreistelligen Monat wieder in eine Zahl, um ihn in der DATE-Funktion zu verwenden.
Der Tag beträgt einfach 1. Dies kann jede Zahl kleiner oder gleich 28 sein, da alle Monate mindestens so viele Tage haben.
Für C3 ist das Jahr also 2000+21, der Monat ist April – also der 4. Monat, also 4, und der Tag ist 1. Wir haben also DATE(2021,4,1).
Antwort2
Sie können diese Formel auch in Zelle C4 ausprobieren
=DATE(2000+RIGHT(C3,2),MONTH(1&LEFT(C3,3)),1)
Antwort3
Es fehlt an der Funktion, die für diesen Job spezifisch ist.
Wählen Sie eine „Hilfszeile“. Es kann Zeile 4 oder 999 sein oder wo immer Sie sie platzieren möchten.
Geben Sie in Spalte C dieser Zeile (z. B. in Zelle C4) Folgendes ein:
=DATEVALUE(SUBSTITUTE(C3, "'", " 20"))
Das SUBSTITUTEersetzt das Apostroph durch ein Leerzeichen und 20. Dies ändert sich beispielsweise Apr'21in Apr 2021. Die DATEVALUEFunktion wandelt dann diesen Textwert in den entsprechenden Datumswert um.
Dann – der „offensichtliche“ Teil, den niemand sonst erwähnt hat – können Sie Ihre Daten nach der Hilfszeile sortieren.
Wenn Sie möchten, können Sie
=DATEVALUE(LEFT(C3,3) & " 20" & RIGHT(C3,2))
Wenn Sie Daten haben, die nicht im 21. Jahrhundert liegen (d. h. das Jahr ist nicht 20JJ), müssen Sie eine Logik hinzufügen, um die Jahrhundertzahl zu bestimmen.