Формула извлечения данных Excel

Question

Я пытаюсь запустить отчет, чтобы оценить время простоя устройств в сети. В отчете OpManagement отсутствуют настройки для создания выходных отчетов. Поэтому я ищу способ извлечения данных и преобразования результатов для обеспечения еженедельного процента доступности. Все формулы не выполняются и возвращают только # #VALUE! ,

Например, в ячейке A1: «Линия по производству журналов (общее время простоя: 3d 2 часа 8 минут 23 секунды)»

Формула должна вернуть 55,869%, потому что 3d = 259200 секунд, 2 часа = 7200 секунд, 8 минут = 480 секунд, 23 секунды, что составляет в общей сложности 266903 секунды за 604800 "секунд в неделю" или 44,131% времени простоя. Одна формула выполнит преобразование, но я должен сначала вручную извлечь «D, H, M, & S».

=IF(ISNUMBER(FIND("d",A1)),LEFT(A1,FIND("d",A1)-1)*86400,0)
+IF(ISNUMBER(FIND("h",A1)),MID(0&A1,FIND("h",0&A1)-2,2)*3600,0)
+IF(ISNUMBER(FIND("m",A1)),MID(0&A1,FIND("m",0&A1)-2,2)*60,0)
+IF(ISNUMBER(FIND("s",A1)),MID(0&A1,FIND("s",0&A1)-2,2)/604800)

Есть идеи?

Answer 1

Этот занял немного работы!

Форматирование вашего вопроса немного отклонилось, поэтому я ВЕРИТЬ, что это ваше первоначальное содержание ячейки:

Magazine Production line (Total Downtime :3d 2h 8m 23s)

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

=DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2))

Причина, по которой это делается в формате даты Excel, заключается в том, что он облегчает получение процента. Поскольку все это основано на 1900 году, это позволяет легко делать математику. Если вы разделите приведенную выше формулу на 1/7/1900, то вы получите свой процент:

=(DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2)))/DATE(0,1,7)

Это приводит к 44.13% простоя.

Просто вычтите все из 1 и вы получите конечный результат:

=1-((DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2)))/DATE(0,1,7))

Или 55.87%