Como (casi) deje de usar VLOOKUP (usando Datos-Relaciones)

Excel 2013 introdujo muchas innovaciones que, más aún que en las versiones anteriores, lo convierten en la herramienta ideal para los analistas de datos. Algunas, como PowerPivot, ya se han ganado su fama. Otras han pasado desapercibidas, como la nueva funcionalidad Datos-Relaciones

Una de las situaciones más corrientes cuando analizamos datos es la necesidad de unificar en una única tabla datos que se encuentran en dos o más tablas. Esto es necesario, en particular, cuando queremos analizar los datos con tablas dinámicas.

Veamos este ejemplo: en una hoja tenemos una tabla con los datos de ventas

y en otra una tabla con las categorías de los productos (madera, electricidad, etc.)

Para crear reportes con tablas dinámicas lo que haríamos con las versiones anteriores de Excel es usar VLOOKUP para combinar las categorías en la tabla de ventas. De esta manera podemos crear un informa de ventas por categorías y períodos, categorías y clientes, etc.

Con la nueva funcionalidad podemos combinar los datos de ambas tablas como en una consulta (query) en Access, sin necesidad de cargar nuestro modelo con miles de fórmulas VLOOKUP.

El primer paso es convertir las listas de datos en Tablas (Insertar-Tabla). Para facilitar el trabajo posterior cambiamos el nombre por defecto (Tabla1) por algo más significativo (tblVentas)

Hacemos lo mismo con la tabla de las categorías (tblCategorias)

El segundo paso es crear las relaciones entre las tablas. En la cinta activamos Datos-Herramientas de Datos-Relaciones y apretamos la opción Nuevo. En el formulario "Crear relación" definimos el campo (columna) común a ambas tabla (en nuestro ejemplo Producto)

Apretamos "Aceptar" y "Cerrar".

Para crear la tabla dinámica con los datos combinados seleccionamos alguna de las tablas y creamos la tabla dinámica (Insertar-Tabla dinámica)

En el formulario que se abre marcamos la opción "Agregar estos datos al modelo de datos"

Excel abre una nueva hoja con la plantilla de la tabla dinámica

En el área de definiciones "Campo de tabla…" activamos la opción "Todos". Esto nos permite ver todos los campos de ambas tablas y usarlas en nuestro informe dinámico.
Ahora podemos crear el informe Ventas por categorías

Matrices de valores únicos en funciones de Excel

El origen de esta nota es esta consulta:

"tengo una tabla de alumnos con las calificaciones de nueve asignaturas. Quiero extraer las tres mejores notas en orden decreciente, sin repetición".

Para ejemplificar veamos parte de esta tabla:

La función que nos permite extraer el valor de la lista de acuerdo a su jerarquía en la lista es K.ESIMO.MAYOR (orden decreciente) y K.ESIMO.MENOR (orden ascendente). La sintaxis de estas funciones es sencilla:

=K.ESIMO.MAYOR(matriz de valores, número de orden)

Así, para el alumno 1 (fila 2) la fórmula

=K.ESIMO.MAYOR($B2:$J2,1)

da como resultado 95.

El problema con esta función es que no ignora los valores repetidos. Si usamos ahora

=K.ESIMO.MAYOR($B2:$J2,2)

para obtener la segunda mejor notas en orden decreciente, el resultado será nuevamente 95, cuando el resultado que buscamos es 86.

Para obtener los resultados esperados necesitamos que el argumento "matriz de valores" de la función sea una matriz de valores únicos.

En esta nota mostraré dos soluciones posibles a este problema: 1) usar columnas auxiliares; 2) usar una FDU (función definida por el usuario).

Solución con columnas auxiliares.

A la izquierda de la matriz insertamos un número de columnas equivalente al número de columnas de la matriz

La celda A2 contiene la fórmula

 =SI(CONTAR.SI($L2:L2,L2)=1,L2,"#")

Esta fórmula evalúa si el valor de la celda L2 aparece por primera vez en la fila; en caso afirmativo da el valor de la celda L2; en caso negativo da como resultado el símbolo #.

EL valor 95 aparece por segunda vez en la celda N2 y por eso el valor resultante en la celda C2 es #.

En la función CONTAR.SI anclamos la columna en la referencia a la primer celda del rango ($L2) de manera que al copiar la fórmula a lo largo de las columnas el rango de evaluación se va extendiendo ($L2:M2 en la celda B2, $L2:N2 en la celda C2 y así sucesivamente).

Ahora podemos usar los rangos de las columnas A:I para nuestros cálculos

En la fórmula

=K.ESIMO.MAYOR($A2:$I2,V$1) 

usamos los valores de la fila 1 como el argumento de orden, fieles al principio de no evitar el uso de constantes en las fórmulas.

Podemos mejorar esta solución funcionalmente convirtiendo todo el rango (columnas auxiliares, matriz de datos y columnas con resultados) en una tabla (Insertar-Tablas-Tabla). De esta manera al agregar alumnos, todas las fórmulas se copian automáticamente

Solución FDU (función definida por el usuario – macro).

La función valUnicos crea una matriz de valores únicos que podemos usar dentro de las funciones de Excel, en nuestro caso en K.ESIMO.MAYOR.

Esta función usa la técnica que ya mostramos en la nota sobre cómo extraer valores únicos.

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

Function valUnicos(rngValores As Range)
    Dim iX As Integer
    Dim arrTemp()
    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range
   
    On Error Resume Next
    For Each rngCell In rngValores
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0
       
    ReDim arrTemp(collUnicos.Count)
    For iX = 1 To collUnicos.Count
        arrTemp(iX - 1) = collUnicos.Item(iX)
    Next iX
   
    valUnicos = arrTemp
   
End Function

Como siempre recomiendo, guardamos el código en un módulo del cuaderno Personal.xls(b) de manera de poder usar la función en todo cuaderno abierto.

Esta función utiliza un único argumento: un rango de la hoja. En la función usamos el objeto Collection para descartar los valores repetidos en el rango; luego pasamos los ítems de la colección a una matriz (array).

En nuestro ejemplo, combinamos esta función con K.ESIMO.MAYOR para obtener los resultados deseados

De la misma manera podemos combinarla con otras funciones (K.ESIMO.MENOR, CONTAR, etc). Por ejemplo

=CONTAR(valUnicos(B2:J2))

da como resultado 7 ya que el 95 y el 86 se repiten.


El cuaderno con el código y los ejemplos se puede descargar aquí.

Tablas dinámicas – cambiar la función CONTAR por SUMA

Para totalizar los valores en los campos introducidos en el área de valores Excel utiliza la siguiente regla:

• Si todos los valores en el campo (columna de la tabla de datos) de origen son numéricos, utiliza SUMA.
• Si alguno de los valores no es numérico, utiliza CONTAR.

El problema con esta regla es que, a diferencia de la función SUMA usada en una celda de la hoja, las celdas vacías son consideradas no numéricas. Esto produce no pocos dolores de cabeza ya que basta que una de las celdas no contenga ningún valor para que la función usada para resumir los valores sea CONTAR.

El problema se agrava cuando tenemos varios campos de valores ya que Excel no cuenta con un método para cambiar la función de resumen de todos los campos de una sola vez. Si tenemos una tabla con muchos campos de valores, nuestro sufrido usuario deberá cambiar manualmente la función de cada uno de los campos.

En este ejemplo tenemos una tabla con los datos de una cadena de comercios para los meses de enero, febrero y marzo. Los datos son: zona, mes, número de visitantes, número de compradores (cuantos de los visitantes realizaron alguna compra) y ventas.

Como puede apreciarse, faltan los datos de marzo de la zona Sur, por lo que las celdas están vacías.

Al crear un reporte dinámico, veremos que Excel usa CONTAR para resumir los datos en lugar de SUMA, que era lo que esperábamos

En este estado de cosas podemos optar por una de estos tres caminos:

1. modificar una a una la función de resumen
2. rellenar las celdas vacías con ceros (lo que se puede hacer eficientemente con Ir a-Especial)
3. usar esta macro para cambiar la función programáticamente:

Sub change_pt_function()
'Cambia la funcion de los campos de valor de CONTAR a SUMA
'Desarrollada por Jorge Dunkelman - JLDExcelsp.blogspot.com

    Dim pt As PivotTable
    Dim iDataFieldsCount As Integer
    Dim iX As Integer
       
    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0
   
    If pt Is Nothing Then
        MsgBox "La celda seleccionada no pertenece a una tabla dinamica", _
                    vbCritical, _
                    "Cambiar funcion en TD"
        Exit Sub
    End If
   
    iDataFieldsCount = pt.DataFields.Count
   
    For iX = 1 To iDataFieldsCount
        With pt.DataFields(iX)
            If .Function = xlCount Then
            .Function = xlSum
            .Name = Replace(.Name, "Cuenta de", "Suma de")
            End If
        End With
    Next iX
       
End Sub

Esta macro recorre todos los campos en el área de los valores; en caso que la función de resumen sea CONTAR, la reemplaza por SUMA.

Para usar la macro hay que seleccionar previamente alguna celda de la tabla dinámica, por eso usamos un MSgBox para informar al usuario en caso que no se haya seleccionado un celda de la tabla dinámica.
Otro detalle es que al usar la propiedad DataFields, el nombre del campo (label) no cambia al cambiar la función. Por eso, suponiendo que el usuario no ha cambiado el nombre por defecto del campo ("Cuenta de…"), reemplazamos "Cuenta de…" por "Suma de…" programáticamente.
Si no queremos cambiar los nombres de los campos, podemos poner un apóstrofe (') al comienzo de la sentencia .Name = Replace(.Name, "Cuenta de", "Suma de"), de manera que no sea ejecutada.

Para usar este código con comodidad podemos agregar un icono en la barra de acceso rápido.