lunes, febrero 06, 2017

Reemplazar nombres en formulas de Excel por sus referencias.

Hace varios meses que no publico notas en el blog, no por pereza (bueno, tal vez un poco) ni por desidia y espero que mis pocos, pero leales, seguidores sepan disculparme.

Para retomar el diálogo empiezo con una solución a una consulta que suelo recibir cada tanto: ¿cómo reemplazar los nombres definidos por sus referencias? Como sabemos, los nombres definidos nos permiten, entro otras cosas, volver más legibles nuestras fórmulas (aquí pueden ver todos los posts en mi blog sobre el tema).

Por ejemplo, si en lugar de =SUMA(A2:A13) usamos =SUMA(ventas), quedará inmediatamente claro qué está calculando la fórmula.

En esta nota del 2014 mostré como Excel nos permite reemplazar las referencias en las fórmulas por nombres definidos. Pero Excel no incluye ninguna herramienta para hacer lo opuesto, es decir, reemplazar los nombres definidos en las fórmulas por sus referencias.

Si por algún motivo queremos reemplazar nombres por referencias a rangos, tendremos que echar mano a una macro.

Este código reemplazará los nombres por los rangos referidos en las celdas seleccionadas.

 Sub change_to_ref()  
   Dim Nme As Name  
   Dim rngCell As Range  
   For Each Nme In Names  
   For Each rngCell In Selection  
     If rngCell.HasFormula Then  
       If InStr(1, rngCell.Formula, Nme.Name, vbTextCompare) > 0 Then  
         rngCell.Formula = Replace(rngCell.Formula, Nme.Name, Mid(Nme.RefersTo, 2, 9999))  
       End If  
     End If  
   Next rngCell  
   Next Nme  
 End Sub  

Para usarlo lo copiamos a un módulo común en el editor de Vb, preferentemente en el cuaderno Personal.xlsb de manera que podamos utilizarlo en todos los cuadernos activos de Excel.


8 comentarios:

  1. Siempre son muy útiles tus posts. No importa si son antiguos o nuevos. Muchas gracias por eso. Welcome back again!

    ResponderBorrar
  2. Bienvenido. Tus soluciones, por repetidas que sean (yo no la recordaba esta ultima), siempre son y seran muy bien recibidas.
    Gracias por tu ayuda continua en nuestro buen hacer con el excel.

    ResponderBorrar
  3. Hola Jorge, gracias por compartirlo. Saludos y feliz día.

    ResponderBorrar
  4. Hola Jorge, ya se extrañaban tus post por acá. Bienvenido de vuelta al rodeo y gracias por tus continuos aportes!
    Saludos

    ResponderBorrar
  5. Siempre es interesante observar tus posts. El recurso del video me parece muy acertado pero ¿por qué no le colocas audio? Sería muy beneficioso escuchar las explicaciones.

    ResponderBorrar
  6. Gracias por estar nuevamente allí aportando. De verdad muchìsimas gracias.

    ResponderBorrar
  7. Y gracias a ustedes por impulsarme a seguir publicando.

    ResponderBorrar
  8. No Pares.. Aqui uno de los leales !!!

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.