☰

как преобразовать Timestring в секунды

tag-icon • tag-icon microsoft-excel
как преобразовать Timestring в секунды

У меня есть CSV-файл, содержащий около 5000 строк, импортированный в Excel, и моя проблема в том, что у меня есть несколько строк времени, 1h1m1sи мне нужно преобразовать их в секунды.

Вот несколько примеров:

Данные в столбце А

0m11s         
2m32s        
3m10s        
1h2m35s

Результат в Col B

11 seconds
152 seconds  
190 seconds    
3755 seconds

Я пробовал форматировать ячейки, но в итоге получились странные результаты!

Возможно ли это сделать, и если да, то как преобразовать строку времени в секунды?

решение1

Это относительно просто сделать с помощью VBA.

Один из способов — использовать регулярные выражения для анализа строки, а затем умножить каждую часть на соответствующее преобразование.

Чтобы ввести эту пользовательскую функцию (UDF), alt-F11откройте редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите Insert/Moduleи вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу, например:

=convSeconds(A1)

в какой-то камере.

Это просто вернет количество секунд, как показано ниже. Если вы хотите добавить слово Seconds, вы можете либо объединить формулу со строкой; либо использовать пользовательское форматирование чисел (которое сохранит числовое качество результата).

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

введите описание изображения здесь

Вот объяснение регулярного выражения

Извлечь ч/м/с

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

Создано сRegexBuddy

решение2

Вот простой способ сделать это. Все это можно было бы сделать в одной формуле, но я использовал несколько вспомогательных столбцов, чтобы избежать повторений.

введите описание изображения здесь

В ваших примерах всегда есть как минимум заполнители для минут и секунд, но часы включаются только если есть часы. Первый шаг ищет "h". Формула в B1:

=FIND("h",A1)

Если "h" отсутствует, возвращается ошибка. В противном случае возвращается положение h в строке (предполагая, что значение часа может превышать 9, в противном случае можно просто взять левый символ).

Столбец C отделяет часть, которая всегда будет одинаковой. Формула в C1:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

Если «h» не найдено, используется исходная строка, в противном случае берется все после «h».

В столбце D находится «m». Формула в D1 аналогична формуле в B1:

=FIND("m",C1)

В столбце E используются части для расчета секунд. Формула в E1:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

Если "h" не найдено, то вклад часов равен нулю, в противном случае он равен 3600 от того, что было найдено слева от "h". Вклад минут равен 60 от того, что было найдено слева от "m". Вклад секунд — это число, найденное после "m" и перед последним символом ("s").

Вы можете скрыть столбцы B:D. Если вы действительно хотите все в одной формуле, просто замените ссылки на вспомогательные ячейки соответствующей формулой вспомогательной ячейки.

Предостережение: Эта формула имеет дело с характеристиками данных в вопросе, которые всегда содержат значения минут и секунд. Для ситуации, когда минуты и/или секунды не обязательно включены, потребуется более сложная формула.

решение3

Я предполагаю, что входные данные находятся в столбце A строки 1.

Вы можете попробовать следующую формулу (скопируйте и вставьте в ячейку B1 и перетащите вниз по мере необходимости):

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

решение4

На этот вопрос уже есть ответы, которые решают проблему в вопросе, который содержит упрощение, что минуты и секунды всегда включены. Люди могут оказаться здесь с похожей, но более общей проблемой, что включены только необходимые компоненты времени, поэтому любой или несколько компонентов могут отсутствовать.

Отличный ответ Рона Розенфельда уже справляется с этим. Для читателей, не знакомых с VBA или регулярными выражениями, которые могут предпочесть решение на основе формулы, которое они могут легче адаптировать и поддерживать, другие текущие ответы не справляются с общим случаем. Я опубликую это решение на основе формулы как отдельный ответ, потому что оно сложнее, чем требуется для ограниченного случая в этом вопросе, и объединение его с моим другим ответом сделает оба решения менее доступными.

введите описание изображения здесь

Это решение также использует некоторые вспомогательные столбцы, чтобы избежать повторения формул, но использует немного другой подход. Столбцы B–D каждый ищет разные буквы компонента времени. Формулы в первой строке:

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

Если буква присутствует, возвращается положение этой буквы в исходной строке, в противном случае ошибка. Если присутствует "s", то она всегда будет последним символом, но нахождение ее положения упрощает извлечение значения секунд, а FIND служит двойной цели: определить, присутствует ли она.

Столбец E извлекает каждое значение компонента времени, умножает его на коэффициент преобразования для секунд и складывает их. Он использует MID для извлечения значений на основе местоположений маркеров, найденных в столбцах B–D.

Сложность в том, что для минут и секунд некоторые или все предыдущие компоненты времени могут отсутствовать; формула должна определить, где в строке начинается текущий компонент, на основе того, какие из предыдущих компонентов присутствуют. Формула в E1:

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

Для удобства чтения я добавил в формулу переносы строк между компонентами времени. Удалите их, чтобы скопировать и вставить формулу.

Часы довольно просты. Берутся символы слева до "h" или ноль, если "h" отсутствует, и умножаются на 3600.

Значение минут может предшествовать только часам. Если значение минут присутствует, оно вычисляет начальную точку и длину для MID на основе того, есть ли компонент часов.

Расположение значения секунд, если оно присутствует, зависит от наличия одного или обоих компонентов часов и минут. Расположение "m", если оно присутствует, всегда будет позже в строке, чем расположение "h", если оно присутствует. Если ни один из них отсутствует, формула использует для этого компонента значение ноль. Затем функция MAX выдает самую дальнюю позицию в исходной строке, которая предшествует значению секунд, и определяет, какую часть длины строки занимают предыдущие компоненты времени.

Вспомогательные столбцы можно скрыть. Их можно объединить в формулу в столбце E, но если посмотреть, сколько раз используются эти значения, то полученная формула будет безумно длинной и сложной в обслуживании.

Связанный контент