☰

强制在单元格中输入格式

tag-icon • tag-icon microsoft-excel data-validation date-format
强制在单元格中输入格式

我需要我的电子表格只允许用户以 hh.mm/hh.mm 格式输入他们的工作模式，例如某人每周工作 37 小时，工作 12.5 小时，只能输入 12.50/37.00

答案1

在 VBA 编辑器中，在工作簿中创建一个新模块（您需要将其另存为.xls或.xlsm，而不是.xlsx）并粘贴如下代码：

Function RegEx(Pattern As String, TextToSearch As String) As String 'vv
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = False
        .Pattern = Pattern
    End With

    Set REMatches = RE.Execute(TextToSearch)
    If REMatches.Count > 0 Then
        RegEx = REMatches(0)
    Else
        RegEx = vbNullString
    End If
End Function '^^

Public Function IsGood(stir As String)
    If RegEx("[0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9]", stir) = stir Then
        IsGood = "GOOD"
    Else
        IsGood = "BAD"
    End If
End Function

第一个函数RegEx执行正则表达式匹配。第二个函数如果输出字符串与输入字符串匹配（即整个字符串与正则表达式完全匹配），则IsGood返回该字符串，否则返回。GOODBAD

现在，在工作表的某个备用列中（可以是隐藏列），写入如下公式：

=IsGood(A1)（如果需要对多行执行此操作，请适当使用锚点）

然后转到功能区上的“数据”->“数据验证”->“数据验证...”，在工作表中选择包含要测试的数据的单元格。

将“允许：”下拉框更改为“自定义”，并将“公式”更改为，例如：

=B1="GOOD"

将“B1”替换为包含公式的第一个单元格的引用=IsGood——不是包含输入数据的单元格。

现在，测试您的结果：

  • 在需要进行数据验证的单元格中输入“你好”之类的内容，您将收到一条错误消息，并且不允许进行编辑。
  • 输入类似“12.34/56.78”的内容，它就能正常工作，就像在 Excel 中一样。
  • 输入类似“12.34,56.78”的内容，您会收到数据验证错误，因为逗号与正则表达式不匹配。

如果您可以接受“12”而不是“12.00”这样的值，您可以调整我的代码中的正则表达式以使其更加宽松，但这取决于您的确切要求和您理解正则表达式的能力。

基本上，[0-9][0-9]\.[0-9][0-9]/[0-9][0-9]\.[0-9][0-9]这是一个正则表达式，表示“匹配一个 2 位数字，后跟一个句点，然后是另一个 2 位数字，然后是一个正斜杠，然后是另一个 2 位数字，然后是一个句点，然后是另一个 2 位数字”。这相当严格：

  • 任何空格都将无法匹配
  • 如果任何数字包含 0、1 或 3 位或更多数字，则匹配失败。例如，12.375/37.00会因为多出一位数字而失败5。
  • 使用反斜杠\代替正斜杠/会导致匹配失败。您可以|在正则表达式中使用交替字符来表示两种可能性中的任一种都是可以接受的，这样您就可以允许使用正斜杠或反斜杠。
  • 可能性还在继续。你可以更放松一点，允许任何数字使用量词的数字。我不会在这个答案中涵盖正则表达式的所有可能性。

相关内容