Обеспечение ввода формата в ячейку

Question

Мне нужна моя электронная таблица, чтобы пользователи могли вводить только в формате hh.mm/hh.mm для своего рабочего шаблона, например, если кто-то работает 12,5 часов в течение 37 часов в неделю, его можно ввести только как 12,50/37,00.

Answer 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 , возвращает строку GOOD если выходная строка соответствует входной строке (т. Е. Вся строка соответствует точно регулярному выражению), и BAD противном случае.

Теперь в запасном столбце где-то (это может быть скрытый столбец) на рабочем листе напишите формулу, например:

=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-значное число, затем косая черта, затем еще одно двузначное число, затем точка, затем еще одно двузначное число ". Это довольно строгое:

• Любые пробелы не будут совпадать
• Если любое из чисел содержит 0, 1 или 3 или более цифр, оно не будет совпадать. Например, 12.375/37.00 потерпит неудачу из-за дополнительной цифры 5 .
• Использование обратной косой черты \ вместо прямой косой черты / не сможет соответствовать. Вы можете использовать символ чередования | в регулярном выражении, чтобы указать, что любая из двух возможностей является приемлемой, и таким образом вы могли бы разрешить ЛЮБОЙ прямой или обратный слеш.
• Возможности продолжаются. Вы можете быть более слабыми и использовать любое количество цифр, используя квантификаторы. Я не собираюсь покрывать все возможности регулярных выражений в этом ответе.