¿Cómo reemplazar los nombres de rango con referencias de celda en Excel?

Al trabajar con rangos nombrados en Excel, podrías encontrarlos útiles para mejorar la legibilidad y la gestión de fórmulas. Sin embargo, hay situaciones en las que es posible que desees eliminar los nombres de rango y restaurar las referencias de celda reales, especialmente al compartir archivos, solucionar problemas o estandarizar plantillas. En este artículo, exploraremos diferentes formas de reemplazar los rangos nombrados por sus correspondientes referencias de celda en Excel.

Reemplazar nombres de rango con referencias de celda mediante código VBA

Reemplazar rápidamente y fácilmente nombres de rango con referencias de celda usando Kutools para Excel

Por ejemplo, en esta hoja de cálculo, nombraré el rango A2:A6 como saleprice, y B2 como discount, y luego usaré ambos nombres de rango en una fórmula para calcular el nuevo precio. Ver capturas de pantalla:

Después de seguir estos trucos rápidos, el nombre del rango se reemplazará con la referencia de celda como se muestra a continuación:

Reemplazar nombres de rango con referencias de celda mediante código VBA

Usando VBA para reemplazar los nombres de rango en las fórmulas con referencias de celda, puedes hacerlo paso a paso de la siguiente manera:

1. Selecciona el rango que contiene las fórmulas en las que deseas reemplazar sus nombres por referencias de celda.

2. Presiona Alt + F11 para abrir una nueva ventana de Microsoft Visual Basic para aplicaciones, luego haz clic en Insertar > Módulo, y copia y pega el siguiente código en el módulo:

Código VBA: Reemplazar nombres de rango con referencias absolutas

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: Reemplazar nombres de rango con referencias relativas

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. Luego presiona F5 para ejecutar el código, aparecerá un cuadro de diálogo para seleccionar un rango a reemplazar. Ver captura de pantalla:

4. Luego haz clic en Aceptar, y todos los nombres de rango de las fórmulas seleccionadas habrán sido reemplazados.

Reemplazar rápidamente y fácilmente nombres de rango con referencias de celda usando Kutools para Excel

Si bien hacer esto manualmente o a través de VBA puede ser lento y propenso a errores, Kutools para Excel proporciona una solución de un solo clic para simplificar todo el proceso. Si tienes Kutools para Excel, su utilidad Convertir Nombre a Rango de Referencia te ayudará a reemplazar nombres de rango con referencias de celda rápidamente y sin complicaciones.

1. Aplica esta utilidad haciendo clic en Kutools > Herramientas de nombres > Convertir Nombre a Rango de Referencia.

2. Aparecerá un cuadro de diálogo, haz clic en la pestaña Rango y luego selecciona el rango que deseas reemplazar con referencias de celda desde el Rango Base. Luego, haz clic en el botón Reemplazar. Ver captura de pantalla:

3. Aparecerá otro cuadro de diálogo indicándote el número de fórmulas modificadas y no modificadas, haz clic en Aceptar, y luego los nombres de rango en el rango seleccionado habrán sido reemplazados por referencias de celda.

🔚 Conclusión

Ya sea que estés trabajando con unas pocas fórmulas o con un libro completo, reemplazar rangos nombrados con referencias de celda reales es una excelente manera de mejorar la transparencia, la compatibilidad y facilitar la colaboración.

• Para usuarios avanzados, VBA ofrece una solución flexible y programable para automatizar el proceso de reemplazo.
• Para todos los demás, Kutools para Excel ofrece la forma más rápida y fácil de realizar la tarea en solo unos clics, sin necesidad de codificación ni edición de fórmulas.

Al elegir el método que mejor se adapte a tu flujo de trabajo, puedes limpiar eficientemente tus fórmulas y asegurarte de que tus hojas de cálculo sigan siendo fáciles de entender y mantener. Si estás interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales para ayudarte a dominar Excel.

Artículo relacionado:

Cambiar la referencia de celda en fórmulas por nombres de rango en Excel