☰

So sortieren Sie in Excel nach hh:mm:ss (Dauer)

tag-icon • tag-icon microsoft-excel sorting
So sortieren Sie in Excel nach hh:mm:ss (Dauer)

Ich habe eine Datenspalte, die die Dauer darstellt, z. B. 33:15 – 30 Minuten und 15 Sekunden; 1:05:00 – 1 Stunde und 5 Minuten usw.

Wenn ich versuche, es von A bis Z zu sortieren, wird 1 Stunde vor 30 Minuten sortiert.

Gibt es eine Möglichkeit, die Daten so zu formatieren, dass sie richtig sortiert werden? Eine Formatierungslösung ist besser als die Konvertierung dieser Daten in Sekunden oder Ähnliches.

Antwort1

Willkommen in der wunderbaren Welt der Zeitangaben in Excel. Anfangs verwirrend, aber leistungsstark, wenn Sie erst einmal wissen, wie sie funktionieren.

Ich glaube nicht, dass es in Ihrer Situation eine Möglichkeit gibt, dies nur durch Formatierung zu tun. Dies sollte jedoch funktionieren (ich gehe davon aus, dass alle Ihre Zeiten in Spalte A stehen).

-Create this formula in B1 and copy it all the way down:
=IF(A1>=1,A1/60,A1)
-Format Column B as h:mm:ss
-Select Column B and Copy, then Paste Special, Values.
-Sorting Column B should now work fine.

Das ist die kurze Antwort. Wenn Sie verstehen möchten, was hier vor sich geht und wie diese Formel hergeleitet wurde, lesen Sie weiter:

1.

  -Start a new sheet.
    -In A1, type 1:05:00
    -Click on A1, then Format, Cells. Note it has applied a custom format of h:mm:ss

Excel ist ziemlich clever und diese Zahl ist ziemlich eindeutig, es geht also davon aus, dass Sie Stunden:Minuten:Sekunden gemeint haben und formatiert sie entsprechend.

2.

-In A2, type 33:15
-Note how it automagically changed it to 33:15:00
-Click on A2, then Format, Cells. Note a custom format of [h]:mm:ss

Das ist nicht eindeutig. Meinten Sie „33 Minuten und 15 Sekunden“ oder „33 Stunden und 15 Minuten“? Excel ist sich nicht sicher. Es geht davon aus, dass Sie Stunden und Minuten gemeint haben. Die [] um das h bedeuten im Grunde „mehr als 24 Stunden im Stundenabschnitt anzeigen“.

3.

-In A3, type 0:33:15 (note the 0: before)
-Click on A3, then Format, Cells. Note a custom format of h:mm:ss

Da Sie die Mehrdeutigkeit beseitigt haben, wird erneut davon ausgegangen, dass Sie Stunden:Minuten:Sekunden gemeint haben, und die Formatierung erfolgt entsprechend.

4.

-In A4, type 23:15
-Note how it leaves it as 23:15
-Click on A4, then Format, Cells. Note a custom format of h:mm

W..T..F? Wieso ist das Format anders als in #2? Weil Sie eine Zahl kleiner als 24 eingegeben haben (also Stunden) – immer noch mehrdeutig, und es wird immer noch angenommen, dass Sie Stunden und Minuten gemeint haben … aber das Format ist anders.

5.

-In A5, type 1:00:00
-Click on A5, then Format, Cells. Note a custom format of h:mm:ss
-Change the format to General and note that the underlying number is .041667 (i.e. the percentage of a day)

6.

    -In A6, type 24:00:00
    -Click on A6, then Format, Cells. Note a custom format of [h]:mm:ss
    -Change the format to General and note that the underlying number is 1 (i.e. a full day)

Fast dort...

7.

-Now click on B2 and enter this formula:
=A2/60 (i.e. convert from hours to minutes)
-Click on B2, then Format, Cells. Note a custom format of [h]:mm:ss
-Note that it now shows 0:33:15, which is what you want

8.

-Now click on B1 and enter the same formula:
=A1/60 (i.e. convert from hours to minutes)
-Click on B1, then Format, Cells. Note a custom format of h:mm:ss
-Note that it shows 0:01:05 - damn - that's *not* what you want.

Bleib fokussiert...

9.

-Click on B1 again and enter this formula instead:
=IF(A1>=1,A1/60,A1)
-Click on B1, then Format, Cells. Enter a custom format of h:mm:ss
-Note that it still shows 1:05:00 (i.e. it didn't change it to 0:01:05)

Im Wesentlichen lautet die Formel also:

-Checks to see if the number in a cell is greater than or equal to 1
-If it is greater than 1, divide by 60 (i.e. convert hours to minutes)
-If it's less than 1, leave it alone.

Antwort2

Wenn Ihre Werte von Excel als tatsächliche Zeiten interpretiert werden, werden sie numerisch und nicht alphabetisch sortiert. Dabei gibt es jedoch Probleme.

Wenn Sie Minuten und Sekunden als 30:00 eingeben, interpretiert Excel dies als 30 Stunden und 0 Minuten. Sie müssen 30 Minuten als 0:30:00 oder 0:30 eingeben. 30 Minuten, die auf diese Weise eingegeben werden, werden also als größer interpretiert als eine Stunde und dreißig Minuten, die als 1:30 eingegeben werden. Sie sollten nach der Eingabe von Zahlen immer die Formelleiste überprüfen, um sicherzustellen, dass Excel den Wert aufgezeichnet hat, den Sie eingeben wollten.

CompWiz hat diese Komplexität übersehen und Craigs Routinen sind genau richtig für Ihre spezifischen Probleme. Aber wenn Sie wissen, dass Sie Zeiten als h:mm:ss eingeben müssen, auch wenn der Wert weniger als eine Stunde beträgt, bedeutet das, dass Ihre Werte immer richtig interpretiert werden.

Antwort3

Kurz gesagt... Zeiten... als Anzahl von Stunden/Minuten/Sekunden werden tatsächlich als Zeichendaten sortiert. 1 ist kleiner als 30 und 1h ist kleiner als 30m, weil 1 kleiner als 3 ist. Sie müssen Ihre Daten als reine Sekunden speichern... d. h. 30 Minuten werden als 1800 und 1 Stunde als 3600 eingegeben und Sie müssen eine komplexe Zellenformatierung verwenden, um sie als Stunde/Minute/Sekunde anzuzeigen...oderGeben Sie die Daten als 00:30:00 bzw. 01:00:00 ein.

Das erste ist dasrichtigMethode, aber sehr komplex zu implementieren ... die zweite ist viel einfacher und funktioniert immer, da 00: jedes Mal vor 01: kommt.

verwandte Informationen