El curioso caso de las tablas dinámicas que no se actualizan.

A las disculpas presentadas en el postanterior corresponde también alguna explicación. En los últimos meses he estado desarrollando modelos con las (no tan) nuevas herramientas de Excel, Power Query y PowerPivot, lo cual consumió el poco tiempo libre que puedo dedicarle al blog.

Para quien haya trabajado exclusivamente con Excel el aprendizaje de estas nuevas herramientas presupone un esfuerzo adicional: dejar de “pensar Excel”. Además cada una de estas aplicaciones viene con sus propios lenguajes (“M” para Power Query; “DAX” para PowerPivot) y sus propias funciones. Pero, el tema de este post no es “Introducción a Power Query…” o “Como PowerPivot cambió mi vida”, sino compartir un aprendizaje relacionado con Power Query.

Hace unos días finalicé el desarrollo de un modelo para controlar lotes en una empresa farmacéutica. La herramienta ideal era PowerPivot pero por motivos que no detallaré aquí, decidí hacer todo el desarrollo con Power Query. El modelo recibe datos, parte de ellos en forma manual; consultas en Power Query realizan las transformaciones necesarias, combinan y unen datos de distintas consultas y crean una serie de tablas planas que alimentan varios reportes creados con tablas dinámicas.

Mi intención era que el usuario apretará el botón “Actualizar todo” para asegurar que los reportes reflejaran siempre los últimos datos. Pero prudentemente desconfiando de mis usuarios, decidí incluir un evento que disparara una rutina ThisWorkbook.RefreshAll cada vez que el usuario accediera a la hoja de los reportes. Para mi desconcierto, si bien las tablas de datos se actualizaban, no así las tablas dinámicas.

Ahorrándoles la vía crucis de descubrir dónde estaba el problema, voy directamente al grano. El problema pasa por la actualización en segundo plano de las consultas (background refresh), que por definición está habilitado para toda conexión que creamos

Para permitir que todas las consultas se actualicen antes que las tablas dinámicas debemos deshabilitar la actualización en segundo plano. Una posibilidad es hacerlo manualmente, pero más eficiente es hacerlo con esta macro y al mismo tiempo actualizar todas las consultas y las tablas dinámicas

 Sub Refresh_All()  
   Dim iConnectionsCount As Integer, iX As Integer  
   iConnectionsCount = ThisWorkbook.Connections.Count  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = False  
   Next iX  
   ActiveWorkbook.RefreshAll  
   For iX = 1 To iConnectionsCount  
     ThisWorkbook.Connections(iX).OLEDBConnection.BackgroundQuery = True  
   Next iX  
 End Sub  

La primer parte de la macro deshabilita la actualización en segundo plano de todas las conexiones. Luego realiza la actualización (Refresh.All) y finalmente rehabilita la actualización en segunda plano.

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.

La función UNIRCADENAS en versiones anteriores a Excel 365

Una de las novedades en Excel 365 (2016) es la función UNIRCADENAS(), como ya mencioné en esta nota. Esta función es muy útil cuando queremos unir los valores de distintas celdas en un único valor textual. En las versiones anteriores de Excel podíamos hacerlo usando el operador "&" (que en inglés se llama ampersand y en castellano "et.") o la función CONCATENAR().
En ambos casos, cuando queremos unir valores de varias celdas, se trata de una tarea tediosa, en particular cuando queremos usar un separador entre los textos.
UNIRCADENAS() permite definir el separador e ingresar un rango de celdas lo cual facilita enórmemente la tarea. El problema es que esta función está disponible sólo para los usuarios de Excel 365 (creo que hasta hoy Microsoft no ha actualizado la versión stand-alone de Excel 2016).
La solución para los usuarios de versiones anteriores es programar un función definida por el usuario (UDF) o una macro.
La ventaja de la función UDF es que se actualizará automáticamente con cada cambio en alguna de las celdas del rango; la desventaja es que puede afectar la velocidad de recálculo del cuaderno, en particular si usamos muchas de estas funciones.
La ventaja de la macro es que no afectará la velocidad de recálculo del cuaderno, pero tendremos que activarla cada vez que efectuemos un cambio en los valores del rango.

Publico aquí ambos códigos que sugiero guardar en el cuaderno PERSONAL de manera que puedan ser usados en cualquier cuaderno.

Función UDF Unir_Cadenas

Function Unir_Cadenas(varSep As Variant, rngVals As Range)
    Dim strTemp As String
    Dim rngCell As Range
  
    For Each rngCell In rngVals
        strTemp = strTemp & rngCell & varSep
    Next rngCell
  
    Unir_Cadenas = Mid(strTemp, 1, Len(strTemp) - 1)
  
End Function

Una vez guardado el código usamos el asistente de fórmulas para activar la función

Y definimos el separador y el rango de celdas que contiene los valores a unir

Si queremos un espacio como separador, pondremos " " (espacio encerrado entre dos comillas); si no queremos ningún separador usaremos "" (dos comillas).

Macro concatenatar_rango

Sub concatenatar_rango()
    Dim strTemp As String
    Dim rngVals As Range, rngCell As Range
    Dim varSep As Variant
    Dim rngDest As Range
  
    On Error GoTo errCancel
  
    Set rngVals = Application.InputBox("Seleccione el rango de celdas a unir", "Rango a unir", Type:=8)
    varSep = Application.InputBox("Entre el separador", "Separador", Type:=2)
    Set rngDest = Application.InputBox("Seleccione la celda de destino", "Destino", Type:=8)
  
    For Each rngCell In rngVals
        strTemp = strTemp & rngCell & varSep
    Next rngCell
  
    rngDest = Mid(strTemp, 1, Len(strTemp) - 1)
  
    Exit Sub
  
errCancel:
Exit Sub
  
End Sub

Al activar la macro debemos seleccionar el rango de celdas, el separador y finalmente la celda de destino, como puede verse en este video