Como substituir nomes de intervalos por referências de células no Excel?

Ao trabalhar com intervalos nomeados no Excel, você pode achá-los úteis para legibilidade e gerenciamento de fórmulas. No entanto, há situações em que você pode querer remover os nomes dos intervalos e restaurar as referências de células reais, especialmente ao compartilhar arquivos, solucionar problemas ou padronizar modelos. Neste artigo, vamos explorar diferentes maneiras de substituir intervalos nomeados por suas respectivas referências de células no Excel.

Substituir nomes de intervalos por referências de células usando código VBA

Substituir rapidamente e facilmente nomes de intervalos por referências de células com o Kutools para Excel

Por exemplo, nesta planilha, vou nomear o intervalo A2:A6 como saleprice e nomear B2 como discount, e depois usar ambos os nomes de intervalo em uma fórmula para calcular o novo preço. Veja as capturas de tela:

Depois de seguir os truques rápidos, o nome do intervalo será substituído pela referência da célula conforme mostrado abaixo:

Substituir nomes de intervalos por referências de células usando código VBA

Usando o VBA para substituir os nomes de intervalo nas fórmulas por referências de células, você pode fazer isso passo a passo da seguinte forma:

1. Selecione o intervalo contendo as fórmulas que você deseja substituir seus nomes por referências de células.

2. Pressione Alt + F11 para abrir uma nova janela do Microsoft Visual Basic for Applications, clique em Inserir > Módulo e copie e cole os seguintes códigos no módulo:

Código VBA: Substituir nomes de intervalos por referência absoluta

Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
        End If
    Next
Next
End Sub

Código VBA: Substituir nomes de intervalos por referência relativa

Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
        End If
    Next
Next
End Sub

3. Em seguida, pressione F5 para executar o código, uma caixa de diálogo será exibida para selecionar um intervalo para substituição. Veja a captura de tela:

4. Em seguida, clique em OK, e todos os nomes de intervalo das fórmulas selecionadas terão sido substituídos.

Substituir rapidamente e facilmente nomes de intervalos por referências de células com o Kutools para Excel

Embora fazer isso manualmente ou através do VBA possa ser demorado e propenso a erros, o Kutools para Excel oferece uma solução de um clique para simplificar todo o processo. Se você tiver o Kutools para Excel, sua ferramenta Converter Nome em Intervalo de Referência ajudará você a substituir nomes de intervalos por referências de células de forma rápida e fácil.

1. Por favor, aplique esta ferramenta clicando em Kutools > Ferramentas de Nome > Converter Nome em Intervalo de Referência.

2. Uma caixa de diálogo será exibida, clique na aba Intervalo e selecione o intervalo que deseja substituir pelas referências de células a partir do Intervalo Base. Em seguida, clique no botão Substituir. Veja a captura de tela:

3. Outra caixa de diálogo será exibida informando o número de fórmulas modificadas e inalteradas, clique em OK, e os nomes de intervalo no intervalo selecionado terão sido substituídos por referências de células.

🔚 Conclusão

Se você está lidando com algumas fórmulas ou uma pasta de trabalho inteira, substituir intervalos nomeados por referências de células reais é uma ótima maneira de melhorar a transparência, compatibilidade e facilitar a colaboração.

• Para usuários avançados, o VBA oferece uma solução flexível e programável para automatizar o processo de substituição.
• Para todos os outros, o Kutools para Excel oferece a maneira mais rápida e amigável de concluir a tarefa em poucos cliques - sem necessidade de codificação ou edição de fórmulas.

Ao escolher o método que melhor se adapta ao seu fluxo de trabalho, você pode limpar eficientemente suas fórmulas e garantir que suas planilhas permaneçam fáceis de entender e manter. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais para ajudá-lo a dominar o Excel.

Artigo Relacionado:

Alterar referência de célula em fórmulas para nomes de intervalos no Excel