☰

解析 Excel 电子表格单元格中的非标准日期/时间数据

tag-icon • tag-icon microsoft-excel worksheet-function
解析 Excel 电子表格单元格中的非标准日期/时间数据

我最想寻求的是这方面的一些建议。我有一个电子表格，里面包含工作地点每个营业时间。格式如下：

"Mon-Sat 9:30am-9:00pm 
Sunday 11:00am-6:00pm"

极端一点来说：

"Mon-Thurs 9:30am-9:00pm 
Fri & Sat 9:30am-11:00pm
Sunday 9:30am-6:00pm
Stat Hol 11:00am-6:00pm"

这个单元格的优先级是采用人类可读的格式，但我需要解析这些数据以显示商店每周每天的哪个小时关门（总是下午 6 点、晚上 9 点或晚上 11 点）。

是否最好将这些数据修改为 Excel 喜欢的日期/时间格式，然后将其连接起来形成人类可读的单元格，或者是否可以（甚至是首选）解析现有数据以获取这些结束时间而不改变格式？

答案1

这是一个用户定义函数，可以解析您显示的格式的数据并“填补空白”。它返回一个值数组——每周每天一个值，加上“hol”的条目。如果未定义某一天的结束时间，则该项目将包含一个空字符串""。

要使用此公式，您需要=ClosingTime($B2)在水平方向上输入八个单元格的数组公式。在下面的示例中，您需要在 中输入公式C2。然后选择C2:J2。最后，按住ctrl+shift并点击进行确认enter。然后，您可以在c2:j2选定后向下拖动到所需的任意多个位置。

UDF 使用正则表达式来解析数据。如果一行中有两个星期几的名称，它会填写它们之间具有相同结束时间的日期；如果只有一个星期几，则只填写该星期几。

要输入此用户定义函数 (UDF)，alt-F11请打开 Visual Basic 编辑器。确保您的项目在 Project Explorer 窗口中突出显示。然后，从顶部菜单中选择插入/模块，并将下面的代码粘贴到打开的窗口中。

Option Explicit
Function ClosingTime(S As String)
    Dim RE As Object, MC As Object, M As Object
    Const sPat As String = "(?:(?:\b(mon|tue|wed|thu|fri|sat|sun|hol).*\b(mon|tue|wed|thu|fri|sat|sun|hol))|\b(mon|tue|wed|thu|fri|sat|sun|hol)).*?(\b\d+:?\d*)p"
    Dim arWkDays As Variant
    Dim I As Long, J As Long
    Dim sTemp As String
    Dim V(0 To 7) As Variant

For I = 0 To 7
    V(I) = ""
Next I

arWkDays = VBA.Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Hol")

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .MultiLine = True
    .ignorecase = True
    .Pattern = sPat
    If .test(S) = True Then
        Set MC = .Execute(S)
        For Each M In MC
            With WorksheetFunction
            'submatches 0-1 is range; 2 is single day; 3 is the closing time
            If M.submatches(0) <> "" And M.submatches(1) <> "" Then
                For I = .Match(M.submatches(0), arWkDays, 0) To .Match(M.submatches(1), arWkDays, 0)
                    V(I - 1) = CDate(M.submatches(3) & "PM")
                Next I
            ElseIf M.submatches(2) <> "" Then
                V(.Match(M.submatches(2), arWkDays, 0) - 1) = CDate(M.submatches(3) & "PM")
            End If
            End With
        Next M
    End If
End With

ClosingTime = V

End Function

在此处输入图片描述

答案2

我拼凑了一些东西来演示这个概念。我相信这可以通过数组公式大大简化，但我追求的是快速和易于理解。

截图1

结构说明

A 列和 B 列是您的样本数据。请注意，我在每行中都包含了商店 ID。这只是一个好习惯，以防数据混乱，但不是必需的。您希望每家商店每天的关门时间，并且商店的记录数量各不相同，因此我添加了一个商店摘要行，最终将包含每天的条目。摘要行可以放在您输出的单独“报告”中。

这项任务需要将日信息转换为可用形式，这很麻烦。考虑到格式的多样性，尝试编写公式来解析记录将是一场噩梦。我使用的方法是，在遇到“日”短语时建立一个列表，然后手动翻译每个短语一次。

这也允许您使用最小公分母短语来节省一些条目。例如，您在第一个示例中使用了三个字母的日期缩写，但在第二个示例中使用了“Thurs”。使用“Mon-Thu”将匹配这两种情况。

您的大多数日期跨度都带有连字符，但您使用“&”表示“Fri & Sat”。考虑到可能还存在“Fri-Sat”，我将其添加到列表中。

如果大小写不全部相同，您可以手动清理，或者使用文本函数强制所有内容为大写或小写。

在此屏幕截图中，我暂时隐藏了一些列。日短语列表从 N 列开始，日短语位于第 1 行。您只需根据需要在下一列中添加另一个短语即可。第 2 行包含与短语相关的日期列表，使用标准 Excel 日编号。我将假期设为第 0 天（我发现它没有显示在屏幕截图中，但 S2 会显示0。周一/周三/周五将是 246。

第一个公式

每日翻译下的单元格都使用相同的公式，因此根据需要复制和粘贴：

=IF(ISERROR(FIND(N$1,$B3)),"",N$2)

这将查找该行的计划记录中列标题的日期短语。如果找到该短语，单元格将从第 2 行获取包含的日期列表，否则为空白。因此，对于每个计划记录，应该有一个短语匹配，这就是该行将显示的日期列表。

辅助列

屏幕截图 2

此屏幕截图显示了两个辅助列，以使解释简单易懂。L 列总结了日期列表。只有一个匹配的条目，这只是将其合并。单元格 L3 包含：

=SUM(N3:AB3)

您可以任意设置范围，这样您就无需在每次添加日词时进行调整。可以根据需要将其复制到列中。

K列包含从计划记录中提取的关闭时间。L3中的公式为：

=IF(ISBLANK(B3),"",REPT(MID(B3,LEN(TRIM(B3))-5,1),IF(MID(B3,LEN(TRIM(B3))-5,1)="1",2,1))&":00pm")

任何解析此问题的方法都会很麻烦，因为时间的位数不同。此公式定位结束小时单位数字。如果是6或，则使用一次；如果时间是，9则重复。然后连接“:00pm”。我添加了 ISBLANK 测试，因为我的布局有一些空白行。111

每日解码时间

这就引出了问题的核心。

屏幕截图 3

此屏幕截图显示了一周中的每一天以及节假日的列。同样，有一个公式用于除摘要行之外的所有单元格。单元格 C3 包含：

=IF(ISERROR(FIND(COLUMN()-3,$L3)),"",$K3)

我的示例从第三列开始计算日期，其中第一列是假日，编码为第 0 天。此公式计算与该列关联的日期编号，并检查它是否在该行的日期列表中。如果是，则获取该行的提取时间值，否则为空白。因此，记录的日期短语中包含的每一天都会获得记录的结束时间。该行的时间表未涵盖的日期则不会获得任何信息。

一周的完整日程安排是该商店行的合并。如果摘要行位于其他地方，则可以使用商店 ID 来识别每个摘要中的行。

对于这个概念演示，我没有花心思设计摘要行。单元格 C5 为：

=C3&C4

单元格 C11 为：

=C7&C8&C9&C10

这可以根据行数自动完成，或者可以直接创建摘要行，而不是合并组件行。

答案3

我将使用 Power Query 插件来实现这一点。从 Excel 2016 开始，Power Query 内置于“获取和转换”部分下的数据功能区中。

Power Query 可以从现有的 Excel 表开始。关键技巧是使用“按分隔符拆分列”选项在最右侧的分隔符上拆分。然后，您可以反向操作，将字符串的时间部分拆分为新的时间列。

查询结果可以作为 Excel 表提供。您无需编写任何宏或函数代码即可构建此表 - 只需在 Power Query 窗口中单击即可。

我构建了一个可行的解决方案，您可以从我的 OneDrive 下载并尝试：

http://1drv.ms/1AzPAZp

这是文件：Power Query 演示 - 解析非标准日期时间数据

相关内容