☰

Excel 数据提取公式

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
Excel 数据提取公式

我正在尝试运行一份报告来评估网络中设备的停机时间。OpManagement 报告没有任何可用于输出报告的自定义功能。因此，我正在寻找一种方法来提取数据并转换结果以提供每周可用性百分比。所有公式都失败并且仅返回#VALUE!。

例如在单元格 A1 中：“杂志生产线（总停机时间：3 天 2 小时 8 分 23 秒）”

公式应该返回 55.869%，因为 3d=259200 秒，2h=7200 秒，8m=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)

有任何想法吗？

答案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%

相关内容