Otro uso del Power Query - Dividir columna de ancho variable

Esta vez Eduardo entró a mi oficina sonriendo.

- El jefe me pidió que separe esta lista en dos columnas: una con el nombre del cliente y la otra con el país

- Usá Texto en Columnas, le dije sin mirar la lista ocupado como estaba con mis propios asuntos.
- No se puede. Fijate que el país es la última palabra en la celda pero cada celda contiene un número distinto de palabras.
- Y si no se puede por qué sonreís.
- Porque encontré la solución. Me fijé en el post que publicaste hace unos años sobre cómo extraer el último elemento de una celda  y apliqué la que mostrabas allí a mi problema.
- Ah, que bueno. Mostrame lo que hiciste.

Lo que hizo Eduardo es crear una columna auxiliar con esta fórmula

=SUSTITUIR(A2," ","#",LARGO(A2)-LARGO(SUSTITUIR(A2," ","")))

Lo que hace esta fórmula es poner un símbolo # en lugar del último espacio en la celda. De esta manera creamos un criterio para encontrar la última palabra o valor en la celda

La expresión LARGO(A2)-LARGO(SUSTITUIR(A2," ","")) calcula la cantidad de espacios entre palabras que hay en la celda y este resultado lo usamos como argumento en la función SUSTITUIR para poner el # en el último espacio.

Para poder usar Texto en Columnas, Eduardo tuvo que eliminar las fórmulas de la columna auxiliar con Copiar-Pegado Especial-Valores. Y finalmente

La misma tarea puede hacerse con Power Query. El Power Query tiene también un método para dividir columnas pero con la ventaja de ser más flexible ya que nos permite determinar la ubicación del separador (en nuestro ejemplo el espacio) si éste se repite dentro del registro (celda).

Veamos el proceso. Empezamos por convertir la lista de clientes en Tabla (Insertar-Tablas-Tabla) para poder usar el Power Query con facilidad. Activamos la pestaña del Power Query en la cinta y elegimos Excel Data-From Table

Excel exporta los datos de la tabla a la ventana del editor del Power Query. En la ventana del editor de la consulta seleccionamos la columna y apretamos el icono Split Column- By Delimiter

En el diálogo que se abre elegimos las opciones Espacio (Space) y "el último a la derecha" (At the right-most delimiter) y apretamos OK

Todo lo que nos queda por hacer es transferir el resultado a una hoja de Excel, lo que hacemos con el menú  Close&Load

Los nombres de las columnas los podemos cambiar en la ventana del editor del Power Query antes de transferir la consulta la hoja de Excel (usando Rename) o directamente en la hoja de Excel.

Contar registros únicos en rangos grandes

En la nota anterior sobre registros únicos vimos cómo encarar el recuento usando tablas dinámicas. Esta vez Eduardo, el de la nota anterior, apareció en mi oficina y sin mucha ceremonia se sentó del otro lado del escritorio mirándome con el ceño fruncido.

- ¿Te acordás del asunto de contar registros únicos con tablas dinámicas?
- Si, seguro. Publiqué una nota en el blog.
- La leí, pero tengo otro problema.
- Si...
- Tengo una tabla con ventas a clientes. Cada cliente aparece muchas veces. Quiero poner en una celda cuántos clientes hay la lista, es decir, sin repeticiones.
- Ah! podés leer mi nota sobre contar valores únicos en Excel.
- La leí y apliqué la fórmula, pero lo lleva mucho tiempo calcular.
- ¿Cuántos registros hay en tu tabla?
- Más o menos, cuatrocientos mil.
- ¿¡¡Cua-tro-cientos-mil!!?, respondí pronunciando cada una de las sílabas por separado para enfatizar.
- Si, y cada mes agrego más.

Lo que Eduardo descubrió es que CONTAR.SI no es la más veloz de las funciones de Excel. Aplicar CONTAR.SI a un rango de 400 mil celdas es una de las mejores maneras de explicar el concepto de eternidad.

Como Eduardo insiste en no usar Filtro Avanzado o Quitar Duplicados y, además, está prohibido mencionar Access en su presencia, tuve que buscar alguna otra solución.

La fórmulas que intentaba usar Eduardo son:

=SUMA(1/CONTAR.SI(miRango,miRango)) en forma matricial
=SUMAPRODUCTO((miRango<>"")/CONTAR.SI(miRango,miRango))

Como puede observarse, ambas fórmulas utilizan la función CONTAR.SI, por lo que la solución que le propuse fue esta UDF (función definida por el usuario)

Function contar_unicos(rngSeleccion As Range)

    Dim collUnicos As New Collection
    Dim rngCell As Range


    On Error Resume Next
    For Each rngCell In rngSeleccion
        collUnicos.Add rngCell, CStr(rngCell)
    Next rngCell
    On Error GoTo 0

    contar_unicos = collUnicos.Count

End Function

Usando una macro que Charles Williams de Decisions Models tuvo la gentileza de colgar en el sitio de artículos técnicos del Office Dev Center, medí el tiempo de cálculo de las distintas fórmulas.

Para investigar el tiempo de cálculo de las distintas opciones usé un ejemplo con un rango de 20 mil celdas conteniendo números aleatorios. El examen del tiempo de cálculo de las fórmulas lo hice usando rangos de 5 mil, 10 mil y 20 mil registros

El valor en la celda G2 permite controlar el tamaño del rango; la celda G3 contiene la fórmula a examinar y la celda G4 recibe el valor del tiempo de cálculo hecho con la macro (apretando el botón "Tiempo de cálculo").

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), estos fueron los resultados en segundos:

Podemos ver que la UDF Contar_Unicos es mucho más rápida que las que usan CONTAR.SI. Además, la diferencia crece con la cantidad de celdas a procesar. Con 5 mil celdas, Contar_Unicos es casi 33 veces más rápida que las otras; con 10 mil celdas la diferencia llega a 78 veces y con 20 mil celdas 160 veces.

Otro detalle interesante es que el tiempo de cálculo de las fórmulas no es proporcional a la cantidad de registros. Al aumentar la cantidad de registros en un 100% (de 5000 a 10000), el tiempo de cálculo de las fórmulas con CONTAR.SI crece en un 300%; un aumento del 300% en los registros (de 5000 a 20000 celdas) resulta en un aumento del 1500% en el tiempo de cálculo.

El tiempo de cálculo de la UDF con 400 mil registros fue 3 segundos.

Como contar registros únicos en tablas dinámicas

Eduardo, colega de trabajo, es inteligente, aplicado y no le gusta depender del departamento de informática. Por eso, maneja una serie de pequeñas bases de datos en Excel (para el horror del departamento de IT ya mencionado). Para generar sus reportes usa principalmente tablas dinámicas de las cuales, para ponerlo de alguna manera, está perdídamente enamorado. A tal punto que todos mis intentos de mostrarle las bondades de otras herramientas como, por poner un ejemplo, Filtro Avanzado, siempre fracasan en forma rotunda.
Por eso cuando Eduardo entró ayer en mi oficina, sólo por la mirada, me di cuenta que algo le estaba pasando. No era una visita de cortesía.

- ¿Cómo hago para saber cuántos clientes tengo en mi base de datos?
 - Dado que todo lo hacés con tablas dinámicas, te sugiero que arrastres el campo de clientes al área de los datos usando la función Contar para totalizar.
- Si, es lo que hice; pero cada cliente aparece en más de una fila en la base de datos y entonces me cuenta la cantidad de veces que cada cliente aparece, no la cantidad de clientes.
- Por supuesto, así funcionan las tablas dinámicas.
- Pero, ¿cómo, no hay una función para contar registros únicos?
- Si y no...
- Uf, ya empezaste. ¿Si o no?
- En Excel 2013 hay una función para contar registros únicos en un reporte dinámico; en Excel 2010 y anteriores, no.
- ¡Ah! Yo uso Excel 2010, ¿cómo hago?
- Creando un campo auxiliar en la base de datos.

Supongamos que nuestra base de datos es la tabla de facturas de la base de datos Northwind

Para contar cuántos clientes hay en la base de datos creamos una tabla dinámica arrastrando los campos País y Cliente al área de las filas y nuevamente el campo Cliente al área de datos usando la función Contar (dado que el campo Cliente no contiene valores numéricos, Excel usará esta función en forma automática)

Inmediatamente podemos ver que en Argentina hay 3 clientes, pero la tabla dinámica muestra 11.
En las versiones de Excel anteriores a Excel 2013, tenemos que usar una columna auxiliar.
Insertamos la columna Auxiliar entre los campos Cliente y Dirección; en esta columan ponemos la fórmula =CONTAR.SI($B$3:B3,B3) que copiamos a todas las filas

El campo Auxiliar muestra el número de orden de aparación de cada cliente. Ahora podemos usar este campo como filtro de la tabla dinámica para que muestre sólo los registro donde el valor de Auxiliar es 1

Ahora podemos ver que la cuenta es correcta

Con Excel 2013, las cosas son más sencillas. No necesitamos crear ningún campo auxiliar. Sencillamente creamos la tabla dinámicas a partir de la base de datos. En el asistente de tablas dinámicas nos aseguramos de marcar la opción "Agregar estos datos al Modelo de datos" (esta opción sólo existe en Excel 2013)

Una vez creada la tabla, arrastramos el campo País al área de filas y el campo Cliente al área de los datos; seleccionamos el área de datos y abrimos el menú de configuración del campo. En la casilla de elección del tipo de cálculo tenemos una nueva función: "Recuento distinto"

Elegimos esta función y apretamos aceptar

Podemos ver que el encabezamiento del campo ha cambiado de "Recuento de cliente" a "Recuento distinto de Cliente" y que efectivamente tenemos 84 clientes en la base de datos.

Personalmente pienso que la traducción tendría que haber sido "Recuento único".