¿Cómo reemplazar 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 rangos nombrados con sus correspondientes referencias de celda en Excel.
Reemplazar nombres de rango con referencias de celda mediante código VBA
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 de una fórmula 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 referencia 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 SubCódigo VBA: Reemplazar nombres de rango con referencia 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 Sub3. 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.
Reemplaza rápidamente y fácilmente nombres de rango con referencias de celda usando Kutools para Excel
Aunque 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 de forma rápida y sencilla.
1. Por favor, 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 con 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, compatibilidad y facilidad de 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 usar para completar 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
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos