Calcular registros únicos en rangos extensos - otra versión

Supongamos que tenemos una tabla de datos de ventas donde cada registro (fila) nos muestra el país, la ciudad, el cliente, el producto, la cantidad y, por suspuesto, el importe.
Ahora supongamos que nuestro jefe (o jefa) nos pide saber cuantos clientes hay en cada país. Esto significa hacer un recuento de valores únicos.

Cuando se trata de rangos extensos nada mejor que usar tablas dinámicas. Hemos mostrado en el pasado cómo hacerlo, como pueden ver en esta nota.

Excel 2013 cuenta con la función incorparada para registros únicos tal como mostré en esta nota.

Si todavía usamos Excel 2010 y la tabla de datos es extensa, por ejemplo 500 K filas, las técnicas que usan CONTAR.SI son prácticamente inútiles por el tiempo de proceso que demandan. Tampoco Filtro Avanzado con la opción Registro Únicos ofrece una solución eficiente.

El MVP Roger Govier propuso una solución que consiste en construir una tabla dinámica cuya base de datos es otra tabla dinámica; es decir, pivotear una pivot table.

Veamos como es esta técnica. Esta es nuestra tabla de datos

Como nos piden el "recuento distinto", cuántos clientes hay en cada país, construimos esta tabla dinámica

Para que la etiqueta del país se repita en todas las filas usamos Configuración de campo - Diseño e Impresión - Repetir etiquetas de elementos

Otros detalles importantes:

• dar a la tabla formato tabular (por defecto la tabla tiene el formato Compacto);
• quitar los Subtotales del campo País;
• quitar los Totales de las filas y las columnas.

Ahora vamos a construir una segunda tabla dinámica basada en la primera

No es indispensable ubicar la segunda tabla dinámica en la misma hoja como la primera, pero lo hacemos por comodidad.
En esta tabla dinámica ponemos el campo País en el área de las filas y el campo Cliente en el área de los datos. Como Clliente no es un campo numérico, Excel usa la función Cuenta lo que nos da el número de clientes únicos por país

Para completar nuestro modelo debemos agregarle dinamismo, es decir, la capacidad de ampliarse dinámicamente (en ingles: "scalabilty", la capacidad de ir acomodándose a incrementos de datos; la palabra "escalabilidad" no existe en castellano, por lo menos por ahora).
Para hacerlo tenemos que crear un rango dinámico que se refiera a la primer tabla dinámica. Si intentamos definir la tabla dinámica como Tabla (Insertar-Tabla) veremos que esto no es posible. Así que echaremos manos a las técnicas "tradicionales", creando un nombre que se refiera al rango en forma dinámica usando la INDICE y CONTARA. En nuestro ejemplo definimos el nombre "rngTablaDatos" que se refiere a esta fórmula:

=Hoja1!$A$3:INDICE(Hoja1!$C:$C,CONTARA(Hoja1!$A:$A)+2)

Otra mejora posible es crear un evento que actualize la tabla cada vez que se genera un cambio en la base de datos, como muestro en esta nota.

Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico

Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna

Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula

=INDIRECTO("Paises["&valdat!$B$2&"]")

donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.

El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.

La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto

Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Descargar el archivo del ejemplo.

La función INDICE con áreas - segundo episodio

Unos días después de haber publicado el post sobre la función INDICE con areas, entra en mi oficina el mencionado compañero de trabajo:

Compañero - Hola Jorge. Leí el post. Muy bueno...

JLD - Hola. Muy bueno...pero?

Compañero - Mirá, no si será abusar pero quisiera que en el gráfico la columna del mes analizado aparezca con otro color y también sería bueno si hubiera una línea horizontal que muestre el promedio.

JLD - ¡Ah! Vos querés ésto:

Compañero - ¡Si! ¿Cómo se hace?

JLD - Sentate que te explico

El gráfico de la nota anterior contenía una única serie; éste contiene tres:

1. la serie de las ventas sin el mes elegido (las columnas que aparecen en celeste)
2. la serie que contiene el mes elegido (aparece en  verde)
3. la serie que representa el promedio.

Como mostramos en la nota anterior, la serie de las ventas la generamos dinámicamente con un nombre definido. Ahora tenemos que crear columnas auxiliares para definir las tres series requeridas. Esto lo haccemos agregando columnas a las izquierda de la tabla de datos de manera que luego podamos ocultarlas.
Estas son las columnas:

La celda B5 contiene la fórmula =COINCIDIR(F19,E4:E15,0). El resultado, el número de orden  del mes elegido, lo usamos como parámetro en las fórmulas de las columnas A y B.

El rango A4:A15 contiene la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)<>$B$2,INDICE(F4:H4,,$F$18),ND())

El objetivo de esta fórmula es dar las ventas del mes de la tabla de ventas a condición que no sea el mes elegido (mayo, 5, en nuestro ejemplo). Em caso de tratarse del mes elegido el resultado es #N/A, lo que logramos con la función ND().

En modo similar ponemos en el rango B4:B15 la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)=$B$2,INDICE(F4:H4,,$F$18),ND())

Esta complementa la anterior: si no es el mes elegido el resultado es #N/A.

Los valores #N/A son ignorados en los gráficos de Excel (no aparecen).

El rango C4:C15 contiene sencillamente una referencia a la celda F22 donde calculamos el promedio.

La celda B17 contiene la fórmula matricial (se ingresa apretando Ctrl-Mayúsculas-Enter):

=MAX(SI(ESERROR(A4:A15),"",A4:A15))

El objetivo de esta fórmula es garantizar que ambos ejes Y en  el gráfico (el principal y el secundario) tengan la misma escala (el mismo valor máximo) para evitar distorsiones en la presentación de los valores. La técnica está explicada en esta nota.

Una vez que hemos definido las series creamos el gráfico asegurándonos de usar el eje Y principal para las ventas y el eje Y secundario para el mes elegido. y para el valor máximo.

Las series de ventas son gráficos de columnas; el promedio es un gráfico de línea y el valor máximo, para volverlo invisible, es un grafico de línea sin marcadores y sin línea

El último detalle a tener en cuenta es, por defecto, Excel no muestra los datos de series que se encuentras en filas o columnas ocultas. Para volverlos visibles tenemos que assgurarnos de cambiar la definición en "Seleccionar Datos-Celdas Ocultas y Vacías"

El archivo con el ejemplo se puede descargar aquí.

La función INDICE con áreas

Transcripción más o menos real de un diálogo con un compañero de trabajo:

Compañero: Hace unos días atrás leí tu post sobre la función INDICE. Desde entonces la uso todo el tiempo; no me había dado cuenta de las posibilidades que tiene.

JLD: me alegro!

Compañero: pero, ¿qué es esa segunda sintaxis con las áreas, para qué sirve?

JLD: ah!, ¿te referís a esto?:

Aquí va la explicación.

Supongamos este cuadro de ventas por mes y sucursal

Habiendo definido nombres que se refieren a los rangos de ventas de las sucursales (Sucursal_1 se refiere al rango C3:C14, por ejemplo), podemos obtener las ventas de mes de abril de las sucursal 1 con esta fórmula

=INDICE(Sucursal_1,COINCIDIR("abril",B3:B14,0))

Si queremos obtener las ventas de otra sucursal tendríamos que cambiar el rango de referencia en la fórmula.

Si usamos la segunda sintaxis de la función INDICE, podemos crear una fórmula que se refiera a las distintas sucursales dinámicamente. Consideremos este ejemplo

La fórmulas en la celda C20 (Ventas del mes) es la siguiente:

=INDICE((Sucursal_1,Sucursal_2,Sucursal_3),COINCIDIR(C18,$B$3:$B$14,0),,C17)

donde (Sucursal_1,Sucursal_2,Sucursal_3) es la referencia a las distintas áreas, en nuestro caso el rango de las ventas de cada sucursal;

COINCIDIR(C18,$B$3:$B$14,0) calcula el número de fila en el rango correspondiente al mes buscado

y C17 se refiere a la celda que contiene el número de área de donde queremos extraer el dato..

Las áreas no deben tener necesariamente el mismo tamaño o ser adyacentes.

La celda C21 contiene la fórmula

=PROMEDIO(INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17))

La particularidad aquí es que INDICE usa sólo los argumentos "ref" y "num_area". Como hemos explicado, ésto hace que INDICE considere todo el rango de la referencia; así, al combinarla con la función PROMEDIO obtenemos el promedio de las ventas de la sucursal. De esta manera podemos comparar las ventas del mes con el promedio.

Como puede apreciarse, esta técnica es muy útil cuando creamos dashboards y reportes dinámicos.

Como bono adicional, podemos usar INDICE con áreas para crear gráficos dinámicos. Siguiendo con nuestro ejemplo, podemos crear un nombre definido "grfVentas" que se refiera a la fórmula

INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17)

Después de crear un gráfico con una de las sucursales, reemplazamos la referencia en "Modificar serie-valores  de la serie"

por el nombre definido "grfVentas"

A partir de este momento, cada vez que cambiemos la referencia a la sucursal en la celda C17, el gráfico mostrará los datos de la sucursal elegida



El cuaderno con el ejemplo puede descargarse aquí.

Como sumar los ultimos n valores de un rango con celdas vacías

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?

La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango

El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente

En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula

La función INDICE, todo (o casi todo) lo que hay que saber

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo

INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo

usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:

• que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector

• cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.

Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:

Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con

34+44+50+26+79+51+57 = 341

De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115

Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))

donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando

Pasar parámetros a una consulta en MS Query desde celdas de Excel

Esta nota viene a colación de la consulta de un lector que me preguntaba si era posible pasar parámetros a una consulta en MS Query desde una celda de Excel.

La respuesta es afirmativa y en esta entrada mostraré cómo hacerlo.

Empecemos por recordar que una de las herramientas para extraer datos de fuentes externas en Excel es el MS Query

Primer paso: crear la consulta en el MS Query

Antes de empezar el proceso hemos definido que las celdas B1:B3 contendrán los parámetros.

Para nuestro ejemplo usaremos la base de datos Northwind (el archivo no viene con el paquete de Office 2010).

Elegimos la tabla de la base de datos (en nuestro caso: Invoices) y los campos que queremos importar a la hoja de Excel

Apretamos “next” hasta que llegamos a la última etapa (Finalizar) y allí elegimos la opción “ver datos en MS Query”

Segundo paso: agregar parámetros a la consulta

Primero debemos hacer visible el área de criterios

Para ingresar criterios en forma de parámetros debemos usamos los paréntesis “[“ y “]” de esta manera

En la línea de Criterios ingresamos el campo, en nuestro ejemplo usamos Country (país) y Shipped Date (fecha de despacho). En la línea de Valor ponemos

[ingrese Pais] para hacer la consulta según país

Between ]fecha de inicio] and [fecha de cierre] para definir el rango de fechas.

Tercer paso: transferir los resultados a Excel

Al hacerlo se abre un diálogo para ingresar los valores de los parámetros; a esta altura del proceso no es necesario ingresar ningún valor, sencillamente apretamos Aceptar

En la hoja de Excel elegimos la ubicación de la tabla que será importada

Cuarto paso: definir las celdas de los parámetros en Excel

Antes de finalizar el proceso apretamos el botón Propiedades

Luego activamos la pestaña Definición para acceder al botón Parámetros

En el formulario que se abre definimos la opción “Tomar el valor de la siguiente celda” y también marcamos la opción “Actualizar automáticamente…”

Volvemos a este paso para cada uno de los parámetros y apretamos Aceptar para los siguientes tres pasos
Excel importa la tabla filtrada por los criterios que hemos definido en B1:B3

Como hemos elegido la opción de actualizar automáticamente al cambiar los valores en la celdas del rango B1:B3, tenemos una consulta que actúa dinámicamente