☰

Формат Excel «буква плюс число»

tag-icon • tag-icon microsoft-excel worksheet-function
Формат Excel «буква плюс число»

У меня возникла проблема с определением способа принудительного применения определенного формата к определенному столбцу.

Я имею дело с электронной таблицей, которую использую для отслеживания запасов. Ее используют несколько человек, и в результате форматы местоположений не согласованы.

(например: А1, А01, А-1, А-01)

Это делает сортировку немного болезненной, имея A1 над A10, а не A1,A2. Я хочу исправить это так, чтобы независимо от того, что набирается, оно исправлялось в формате числа «Буква+2 цифры» (A01).

заранее спасибо

решение1

В столбце ввода это невозможно.

  • Вы могли бы добавитьдополнительный (вспомогательный) столбецкоторый содержит формулу, которая разделяет части вашей строки, соответствующим образом форматирует каждую из них и аккуратно объединяет их.
  • если все ваши строки начинаются с буквы A, вы можете просто сказать всем:падение А(и тире), и просто введите число (но, возможно, вы просто привели пример)
  • Вы могли бы иметьдвастолбцы, один для альфа-части и один для числа
  • Вы могли бы написатьмакроскоторый анализирует все, что они вводят, и выполняет сортировку. Но по опыту, в мире нет макроса, который мог бы справиться со всеми абсурдными вариациями, которые потенциально может придумать пользователь — они всегда найдут вариацию, которая его сломает.

решение2

Вы также можете использовать Data Validation с понятными подсказками и сообщениями. Формула проверки данных (предполагая, что вы хотите использовать заглавные буквы) будет выглядеть так:

=AND(CODE(A1)>=65,CODE(A1)<=90,CODE(MID(A1,2,1))>=48,CODE(MID(A1,2,1))<=57,CODE(MID(A1,3,1))>=48,CODE(MID(A1,3,1))<=57)

решение3

Поместите следующий макрос события в область кода рабочего листа:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B As Range, s As String
    Set B = Range("B:B")
    If Intersect(Target, B) Is Nothing Then Exit Sub
    s = Target.Value
    If Len(s) <> 3 Then GoTo fixit
    If Not Left(s, 1) Like "[A-Z]" Then GoTo fixit
    If Not IsNumeric(Mid(s, 2, 2)) Then GoTo fixit
    Exit Sub
fixit:
    Application.EnableEvents = False
        Target.Value = "A00"
    Application.EnableEvents = True
End Sub

Этот пример макроса отслеживает и корректирует записи в столбцеБ. Если записи действительны, они остаются в покое. Если запись недействительна, она заменяется наА00.

решение4

Если вы просто хотите проверить значение, а логика проверки не слишком сложна, это можно сделать с помощью стандартной проверки данных Excel. Однако, поскольку вы хотите переформатировать значение, чтобы оно стало правильным, вам нужно будет использовать VBA или Visual Studio Tools for Office. Ниже приведен простой макрос VBA, который делает то, что вам нужно.

Волшебство происходит, когдаРабочий лист_Изменениесобытие запускается. Для примера я предполагаю, что вы вводите отдельные значения в первый столбец.ПравильныйНомерДеталиФункция выполняет тяжелую работу. Она анализирует введенное значение и возвращает правильно отформатированное значение или ничего, чтобы указать на ошибку проверки (она проверяет только максимальную длину). Обработка работает с вашим примером, но, вероятно, слишком проста для реального мира. Ее можно легко улучшить с помощью регулярного выражения или другой аналогичной мощной обработки.

Обработчик событий продолжает работу, либо обновляя ячейку правильно отформатированным номером детали, либо выделяя введенное пользователем значение жирным шрифтом и красным цветом, чтобы указать на ошибку.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim NewValue As String

    If (Target.Column = 1 And Target.Count = 1) Then
        Application.EnableEvents = False
        NewValue = CorrectPartNo(Target.Value)
        If (Len(NewValue) = 0) Then
            Target.Font.Color = vbRed
            Target.Font.Bold = True
        Else
            Target.Value = NewValue
        End If
        Application.EnableEvents = True
    End If
End Sub

Function CorrectPartNo(PartNo As String)
    Dim StartPartNo As String
    Dim EndPartNo As String
    Dim EndPartNoPosition As Integer

    StartPartNo = Left(PartNo, 1)
    If (Mid(PartNo, 2, 1) = "-") Then
        If (Len(PartNo) > 4) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 3
        End If
    Else
        If (Len(PartNo) > 3) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 2
        End If
    End If
    EndPartNo = Right("0" + Mid(PartNo, EndPartNoPosition), 2)

    CorrectPartNo = StartPartNo + EndPartNo
End Function

Связанный контент