Substituindo termos semelhantes por uma palavra no Excel

Question

Peço desculpas antecipadamente, porque não tenho certeza de como formular essa pergunta.

Estou mantendo registros financeiros para uma organização estudantil, e meu objetivo é criar uma planilha que seja fácil de usar, de modo que um formato sólido e atualizado possa ser transmitido por muitos anos.

Existem várias posições dentro da organização e cada uma delas tem orçamentos para certas coisas.

Neste momento, tenho um conjunto de registros que é configurado da seguinte forma:

Orçamento ----- Sub-orçamento ---- Descrição do trans. ---- Quantidade de Presidente - Liderança ----- Bilhetes de avião -------------- $

Com a lista suspensa, os usuários podem inserir texto ou selecionar na lista. (Eu estou usando a opção de validação de dados, com 'lista' selecionada.) Eu não quero restringir os dados não-lista, porque eu quero que a planilha seja fácil de usar. Por outro lado, eu não quero reinar livremente com nomes, porque os títulos são bem longos, e eu quero ser capaz de explicar tudo automaticamente.

Então, pensei que o bom meio termo seria substituir dados semelhantes aos dados que eu quero.

Assim, por exemplo, um usuário pode digitar em Orçamento:

• Presidente
• Pres
• P
• pRez

Eu posso reconhecer que todos eles estão se referindo a 'Presidente', e então eu quero que a entrada seja substituída por 'Presidente'.

Eu sei que posso adicionar uma coluna oculta que interpreta isso usando uma fórmula básica, mas gostaria de adicionar algo que faça essa substituição automaticamente; Assim que o usuário clicar em "Enter", eu gostaria que a planilha mudasse isso automaticamente para um termo aprovado.

Ao mesmo tempo, gostaria que qualquer outro termo não coberto tivesse um valor de alteração padrão; qualquer outro valor que não seja um termo para Presidente ou qualquer outra posição, classifique-o como 'Outro'.

Isso é possível? Isso exigiria o VBA ou essa funcionalidade é incorporada nas configurações existentes?

    

Answer 1

Se bem entendi, você deseja que o usuário insira dados em uma célula específica e, uma vez que eles tenham terminado, você deseja que a mesma célula seja alterada para atender a uma lista sanitizada de termos permitidos.

A única maneira que eu conheço para alterar os dados em uma célula é através do VBA. Suas opções são usar um campo oculto como você mencionou ou usar o VBA. De qualquer maneira, você precisará de uma tabela mostrando todos os termos não oficiais e qual é o termo oficial correspondente.

O resto da abordagem depende do que é mais importante: Facilidade de implementação ou aparência do produto acabado. Por exemplo, se é realmente importante que você possa imprimir e exibir os termos oficiais ou se quiser garantir que seus usuários vejam exatamente o que eles digitaram aparecendo em outros lugares, a coluna auxiliar pode não funcionar e você Teremos que ir com o VBA.

Se você está bem com isso, deixe-nos saber e podemos montar um exemplo de como fazer isso funcionar.

EDIT: OP está OK com a solução VBA

Primeiro, aqui está a configuração:

• Eu tenho uma planilha oculta chamada WordTable cujo codinome (no VBA) é shtWordTable
• Nessa folha oculta é uma tabela (ou lista, se você é da velha escola) cujo nome é tblWordTable
• Essa tabela tem dois campos: Nickname e Proper Name
• Nickname tem valores como Pres, P, pRez (deus ajuda esse usuário)
• Proper Name tem valores como Presidente, Presidente e Presidente
• Eu tenho uma planilha visível chamada Ledger cujo codinome é shtLedger
• Essa planilha tem uma tabela chamada tblLedger
• Essa tabela tem os 4 campos que você mencionou na sua pergunta

Note que a solução funcionará se seus dados não estiverem em uma tabela oficial e forem apenas um bloco de células, mas eu gosto de tabelas e são úteis para muitas coisas, então estou usando isso no exemplo. Vou comentar o código para que você possa dizer quais linhas editar.

Com essa configuração, entre no VBA e crie um evento Worksheet_Change em shtLedger . Isso será acionado sempre que os dados forem alterados em qualquer célula dessa planilha. Vamos verificar se está dentro do intervalo que queremos corrigir automaticamente e, se estiver, procurar uma substituição no WordTable . Se um for encontrado, vamos trocá-lo.

O código é provavelmente maior do que o necessário para a sua situação exata, mas como não sei como é a configuração da , tentei torná-lo o mais robusto possível. Há também algumas seções de comentários apenas para ajudar. Seu resultado final deve ser um pouco menor do que isso:

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