BUSCARV (VLOOKUP) con texto dentro de texto

Uno de mis clientes implementó una aplicación para la administración de la planta de producción. En la planta hay una cierta cantidad de máquinas, que fueron asignadas a "centros". Cada centro puede tener una o más máquinas. Para crear la base de datos el ingeniero de producción preparó una tabla con las máquinas asignadas a cada centro

El departamento de computación pidió recibir una tabla con una línea para cada máquina

por lo que hubo que transformar la tabla de nuestro buen ingeniero en la tabla requerida por el departamento de computación (en la realidad hay en la planta 75 máquinas asignadas a 15 centros).

No podemos usar BUSCARV o INDICE con COINCIDIR ya que en una misma celda de la primer tabla hay más de una máquina. De hecho tenemos que extraer el valor correspondiente a un "sub-texto" dentro del texto.

Podemos hacerlo usando funciones nativas de Excel o con una UDF (función definida por el usuario). Con funciones nativas de Excel usamos esta fórmula matricial

 =INDICE(Hoja1!$A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;Hoja1!$B$4:$B$9));0))

Esta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

La función HALLAR busca el número de máquina (E4) en el rango de máquinas de la tabla de ingeniero (B4:B9). De hecho, HALLAR busca el "texto" 251 dentro del texto de las celdas del rango B4:B9. Si lo halla, el resultado es un número (la posición donde comienza el texto buscado dentro del texto de la celda de búsqueda).
La expresión ESNUMERO(HALLAR… genera un vector de resultados VERDADERO o FALSO. COINCIDIR calcula la posición del primer valor VERDADERO dentro de ese vector. Este resultado, a su vez, es utilizado en la función INDICE para calcular el centro correspondiente.

También podemos crear esta UDF, usando la función InStr de Vba. La función calcula la posición de la máquina en la lista, es decir, reemplaza la expresión

COINCIDIR(VERDADERO;ESNUMERO(HALLAR…

en la fórmula anterior. El resultado de la función lo usamos como argumento en la función INDICE, sin necesidad de usarla en forma matricial

 El código de la función match_sub_string es el siguiente:

 Function match_sub_string(SearchValue, rngSearchRange As Range)

    Dim lRangeRows As Long 'numero de filas en el rango
    Dim iR As Long
  
    lRangeRows = rngSearchRange.Cells.Count
  
    For iR = 1 To lRangeRows
        If InStr(rngSearchRange.Item(iR), SearchValue) > 0 Then
            match_sub_string = iR
            Exit Function
        Else
            'do nothing
        End If
    Next iR
  
       
End Function

Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno

Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub

En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.

Totales en gráficos de columnas apiladas

Sin lugar a dudas no es comparable a la invención de la rueda o de la pólvora, pero aquí les traigo un tip sobre como agregar automáticamente totales en gráficos de columnas apiladas en Excel.

Supongamos esta tabla de datos que muestra las ventas del año de las tres sucursales de una empresa imaginaria

Con los datos creamos este gráfico que muestra las ventas por mes

Hemos eliminado el eje de los valores (Y) y puesto etiquetas con los valores (en miles) en las porciones de las columnas. Lo que falta es una etiqueta para cada mes que muestre el total de las ventas.

Podemos, por supuesto, poner cuadros de texto sobre cada columna e inclusive ligarlos a los datos en la tabla. Pero queremos algo más automático.

Empezamos por seleccionar el rango F3:F14 (el total de ventas), lo copiamos y agregamos la serie al gráfico con Pegado Especial

Al hacer esto, Excel agrega la serie a las columnas apiladas deformando nuestro gráfico

Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas

La línea es innecesaria y la hacemos desaparecer poniendo la opción “color de línea” a “sin línea”

Para que las etiquetas queda inmediatamente por encima de las columnas, usamos la opción “encima” de alineación de las etiquetas

Para quitar “serie 4” de la leyenda, lo seleccionamos y lo borramos (un clic para seleccionar la leyenda y un clic más para seleccionar la etiqueta “serie 4”)

Hacemos desaparecer los ejes de valores poniendo las opciones de marcas de graduación y etiquetas del eje a “ninguno”

Con esto terminamos. Podemos agregar un borde y sombra a las etiquetas del total