Замена похожих терминов одним словом в Excel

Question

Я заранее прошу прощения, потому что я не совсем уверен, как сформулировать этот вопрос.

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

В организации есть несколько должностей, и у каждой из них есть бюджеты на определенные вещи.

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

Бюджет ----- Суббюджет ---- Описание транс. ---- Количество
Президент - Лидерство ----- Билеты на самолет -------------- $

С помощью раскрывающегося списка пользователи могут либо вводить текст, либо выбирать из списка. (Я использую опцию «Проверка данных» с выбранным «списком».) Я не хочу ограничивать данные не из списка, потому что хочу, чтобы лист был прост в использовании. С другой стороны, я не хочу свободного господства с именами, потому что названия довольно длинные, и я хочу, чтобы все можно было учесть автоматически.

Поэтому я подумал, что хорошим промежуточным положением было бы заменить аналогичные данные данными, которые я хочу.

Так, например, пользователь может ввести в поле Бюджет:

• президент
• Pres
• п
• Пра

Я могу признать, что все это относится к «президенту», и поэтому я хочу, чтобы вклад был заменен на «президент».

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

В то же время я хотел бы, чтобы любому другому термину, который не был охвачен, было присвоено значение изменения по умолчанию; Любое другое значение, которое не является термином «Президент» или любая другая должность, классифицируйте его как «Другое».

Возможно ли это сделать? Для этого потребуется VBA или эта функция встроена в существующие настройки?

Answer 1

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

Единственный известный мне способ изменения данных в ячейке - через VBA. Вы можете либо использовать скрытое поле, как вы упомянули, либо использовать VBA. В любом случае вам понадобится таблица со всеми неофициальными терминами и соответствующим официальным термином.

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

Если вы согласны с этим, дайте нам знать, и мы сможем собрать пример того, как заставить это работать.

---

РЕДАКТИРОВАТЬ: OP в порядке с решением VBA

Во-первых, вот настройка:

• У меня есть скрытый лист с именем WordTable , кодовое имя которого (в VBA) - shtWordTable
• В этом скрытом листе находится таблица (или список, если вы старшая школа), имя которой - tblWordTable
• Эта таблица имеет два поля: Nickname и Proper Name
• Nickname имеет такие значения, как Pres, P, pRez (да поможет Бог этому пользователю)
• Proper Name имеет такие ценности, как президент, президент и президент
• У меня есть видимый лист с именем Ledger чье кодовое имя shtLedger
• Этот лист имеет таблицу с именем tblLedger
• Эта таблица имеет 4 поля, которые вы упоминаете в своем вопросе

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

С этой настройкой перейдите в VBA и можете создать событие Worksheet_Change в shtLedger . Это будет запускаться каждый раз при изменении данных в любой ячейке на этом листе. Мы проверим, находится ли он в пределах диапазона, который мы хотим исправить автоматически, и, если это так, ищем замену в WordTable . Если он найден, мы его поменяем.

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

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    'Declarations
    Dim tbl As ListObject
    Dim rngAutocorrect As Range
    Dim rngFound As Range
    Dim cell As Range

    'Setup what cells we want to check for nicknames
    'It will error if the table has no data in it because DataBodyRange will be nothing
    On Error Resume Next
        Set rngAutocorrect = ListObjects("tblLedger").ListColumns("Budget").DataBodyRange
    On Error GoTo 0

    'If it did error out so there is no data in the table so we exit
    If rngAutocorrect Is Nothing Then Exit Sub

    'If you're not using a table, you can do something like this:
    ' Set rngAutocorrect = Range("A:A")
    'The only important thing is that you end up with a range object

    'If there are multiple areas to check, one method is to use Union to add them
    'You can also define them all at once, but the Union method trades longer code for easier debugging
    ' Set rngAutocorrect = Union(rngAutocorrect, ListObjects(1).ListColumns("Sub-Budget").DataBodyRange)
    ' Set rngAutocorrect = Union(rngAutocorrect, Range("B:B"))

    'Check if what was changed (Target) is within the range we want to Autocorrect
    If Not Intersect(Target, rngAutocorrect) Is Nothing Then
        'Store the lookup table in an easy-to-reference format
        Set tbl = shtWordTable.ListObjects("tblWordTable")

        'Target might be a range of cells if, for instance, they have pasted a lot of text in
        'Loop through each in turn
        For Each cell In Target.Cells
            'Check that this particular cell needs autocorrecting
            If Not Intersect(Target, rngAutocorrect) Is Nothing Then
                'Look for the value in the word table
                With shtWordTable.ListObjects(1)
                    'Change the parameters here if you want to match case or something
                    Set rngFound = .ListColumns("Nickname").DataBodyRange.Find( _
                        cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                    'If we found it, then grab the Proper Name
                    If Not rngFound Is Nothing Then
                        'Turn off events or else this code will trigger itself in an infinite loop
                        Application.EnableEvents = False

                        'Make the switch
                        cell.Value = rngFound.Offset(0, 1).Value

                        'Turn events back on
                        Application.EnableEvents = True
                    End If
                End With
            End If
        Next
    End If


    'Cleanup
    Set tbl = Nothing
    Set rngFound = Nothing
    Set rngAutocorrect = Nothing
    Set cell = Nothing

End Sub