☰

Como classificar hh:mm:ss (duração) no Excel

tag-icon • tag-icon microsoft-excel sorting
Como classificar hh:mm:ss (duração) no Excel

Tenho uma coluna de dados que representa a duração, por exemplo, 33:15 - 30 minutos e 15 segundos; 1:05:00 - 1 hora e 5 minutos, etc.

Se eu tentar classificar de AZ, 1 hora será classificada antes de 30 minutos.

Existe uma maneira de formatar os dados para classificá-los corretamente? A solução de formatação é preferível à conversão desses dados em segundos ou outros enfeites.

Responder1

Bem-vindo ao maravilhoso mundo dos tempos no Excel. Enigmático no início, mas poderoso quando você sabe como eles funcionam.

Não acho que haja uma maneira de fazer isso apenas formatando na sua situação. No entanto, isso deve funcionar (presumo que todos os seus tempos estejam na coluna A)

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

Essa é a resposta curta. Se você quiser entender o que está acontecendo e como essa fórmula foi derivada, continue lendo:

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

O Excel é muito inteligente e esse número é bastante inequívoco, portanto, presume-se que você quis dizer horas: minutos: segundos e formata adequadamente.

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

Isto é ambíguo. Você quis dizer “33 minutos e 15 segundos” ou “33 horas e 15 minutos”? O Excel não tem certeza. Seu comportamento é assumir que você quis dizer horas e minutos. O [] ao redor do h significa basicamente "mostrar mais de 24 horas na seção de horas".

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

Como você eliminou a ambiguidade, ele mais uma vez assume que você quis dizer horas: minutos: segundos novamente e formata de acordo.

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

O que..T..F? Por que ele foi formatado de maneira diferente do item 2? Porque você inseriu um número menor que 24 (ou seja, horas) - ainda ambíguo e ainda pressupõe que você quis dizer horas e minutos... mas formata de forma diferente.

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)

Quase lá...

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.

Fique no alvo...

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)

Então basicamente essa fórmula:

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

Responder2

Se seus valores forem interpretados pelo Excel como tempos reais, eles serão classificados numericamente, não em ordem alfabética. Mas existem problemas.

Se você inserir minutos e segundos como 30:00, o Excel interpretará como 30 horas e 0 minutos. Você deve inserir 30 minutos como 0:30:00 ou 0:30. Portanto, 30 minutos inseridos desta forma serão interpretados como maiores que uma hora e trinta minutos inseridos como 1:30. Você deve sempre verificar a barra de fórmulas após inserir números para ter certeza de que o Excel registrou o valor que você pretendia inserir.

O CompWiz não percebeu essa complexidade e as rotinas de Craig apenas corrigem seus problemas específicos. Mas saber que você deve inserir horários como h:mm:ss mesmo que o valor seja inferior a uma hora significará que seus valores sempre serão interpretados corretamente.

Responder3

Resumindo... tempos... como em número de horas/minutos/segundos, na verdade são classificados como dados de caracteres. 1 é menor que 30 e 1h é menor que 30m porque 1 é menor que 3. Você precisa armazenar seus dados como todos os segundos... ou seja, 30 minutos são inseridos como 1800 e 1 hora como 3600 e usar algumas células complexas. formatação para exibi-los como hora/minuto/segundos...ouinsira os dados como 00:30:00 e 01:00:00 respectivamente.

o primeiro é oapropriadométodo, mas muito complexo de implementar... o segundo é muito mais simples e sempre funcionará porque 00: vem antes de 01: todas as vezes.

informação relacionada