Ordenar listas numéricas con fórmulas en Excel

Nada más sencillo que ordenar listas o tablas en Excel. Un clic al icono correspondiente (ascendente o descendente) y Excel ordena la lista. Pero en ciertas situaciones queremos que la lista se ordene automáticamente al ir agregando o quitando datos.

Podemos programar un evento que se ocupe de ordenar nuestra lista con cada cambio, pero también podemos hacerlo con fórmulas. En esta nota veremos cómo hacerlo en el caso de listas numéricas (cómo hacerlo con listas de texto he mostrado en esta nota).

Supongamos un rango donde vamos agregando fechas (recordemos que la fechas son números en Excel)

Como puede verse estoy usando ALEATORIO.ENTRE para generar fechas en forma aleatoria.

Para ordenar esta lista en orden ascendente usamos esta fórmula

=K.ESIMO.MENOR(lstFechas,FILA()-1)

donde “lstFechas” es un nombre que define el rango de las fechas en forma dinámica, con la fórmula

=fechas!$A$2:INDICE(fechas!$A:$A,CONTARA(fechas!$A:$A))

Para ordenar la lista en orden descendente usamos esa otra fórmula

=K.ESIMO.MENOR(lstFechas,CONTARA(lstFechas)-FILA()+2)

Una desventaja de este método es que cada vez que agregamos un valor a lista debemos copiar las fórmulas. Podemos superar este inconveniente convirtiendo el rango en “tabla”



Al convertir el rango en tabla, las fórmulas son copiadas automáticamente.

Formato condicional en los últimos n valores de una serie

El lector Willy me consultaba en la nota de ayer cómo aplicar un fondo con formato condicional a las celdas que participan en el cálculo (las últimas n celdas de la serie). La consulta es interesante ya que esto nos permite un control visual efectivo de los valores comprendidos en el cálculo. Si bien le respondí en un comentario en la nota, vale la pena ampliar el tema.

Formato condicional funciona con fórmulas booleanas, es decir, que el resultado debe ser VERDADERO o FALSO. Sólo cuando el resultado es VERDADERO, se aplica el formato elegido.

Dado esto, nuestra táctica será calcular si la fila de la celda evaluada cae dentro del rango de la fórmula. Por ejemplo, si queremos marcar las últimas 10 filas

seleccionamos el rango C5:C160 (siguiendo con nuestro ejemplo) y en “formato condicional-nueva regla-utilice fórmula…” usamos

Y(FILA(C5)>=CONTAR($C$5:$C$160)+5-$C$1,FILA(C5)<=CONTAR($C$5:$C$160)+4)

donde C1 contiene el número de valores a incluir en el cálculo.

La fórmula funciona así: calculamos el número fila de la primer celda en el rango con

 CONTAR($C$5:$C$160)+5-$C$1

 Calculamos el número de fila del última valor en el rango con

 CONTAR($C$5:$C$160)+4

 donde 4 es el número de filas por encima de la primer fila del rango de valores.

 Ahora usamos la función FILA para comparar si el número de fila de la celda evaluada cae dentro del rango relevante. Esto lo hacemos con la función Y, que da VERDADERO sólo si todas las condiciones dentro la fórmula se cumplen.

 Personalmente prefiero una técnica distinta. Por algún motivo que no termino de comprender, muchos usuarios evitan usar columnas auxiliares en los modelos de Excel. Esto nos lleva casi siempre a crear fórmulas complicadas cuyo principal problema reside en la dificultad de controlar los resultados (o recordar que quisimos hacer cuando volvemos a trabajar con el modelo dos semanas más tarde).

 En el caso que estamos analizando, mi propuesta es la siguiente:

 1 – insertamos 3 columnas a la izquierda de la tabla de datos (dos columnas son suficientes)

 2 – En la celda B2 ponemos la fórmula “=CONTAR(E5:E160)+4”, que calcula la última fila con valores en el rango (tal como hicimos en la fórmula anterior)

 3 – En la celda A2 ponemos “=B2-E1+1” que calcula la primer fila del rango (E1 contiene el número de valores que queremos incluir en el cálculo)

 4 – En la celda B5 ponemos la fórmula “=Y(FILA(E5)>=$A$2,FILA(E5)<=$B$2)” y la copiamos a lo largo del rango a evaluar (en nuestro ejemplo B5:B160)

 Como se ve, creamos una serie de valores VERDADERO (cuando la fila cae dentro del rango) o FALSO (cuando está fuera del rango). Ahora usamos esto valores para accionar el formato condicional 

Como puede apreciarse, la fórmula en el formato condicional es obviamente sencilla, lo mismo que las fórmulas en las columnas auxiliares.







El último toque es ocultar las columnas auxiliares

Descarga del archivo del ejemplo

Cálculos con los últimos n valores de una serie en Excel

Un compañero de trabajo quería calcular el promedio de los últimos 12 valores de una serie. En su caso, el precio promedio de una serie de productos

Para calcular el promedio de los últimos doce meses usaremos esta fórmula

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-12,0,12,1))

A medida que agreguemos (o quitemos) valores, la fórmula se ajustará automáticamente.

Sobre el funcionamiento de la función DESREF ya hemos escrito en este blog. El “truco” aquí es que usamos CONTAR para encontrar la última celda no vacía (suponemos que contienen números) y luego “retrocedemos” 12 puntos atrás.
En nuestro ejemplo

CONTAR(B5:B160)-12

da 2 (14-12) lo que nos “lleva” a B7; luego nos extendemos hasta B18 usando 12 como argumento en DESREF.

Un detalle a tomar en cuenta es que el rango de valores debe ser continuo (sin celdas vacías entre dos o más valores); en caso contrario el resultado será incorrecto.

Podemos modificar la fórmula de manera que la cantidad de valores a considerar en el cálculo se determine de forma dinámica

Usamos el valor en la celda B1 como argumento para determinar la cantidad de meses a tomar en cuenta

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-B1,0,B1,1))

Apéndice: en esta nota muestro como resaltar las celdas comprendidas en el cálculo con formato condicional

Gráfico dinámico con lista desplegable - segunda nota

En la nota anterior mostramos un modelo sencillo para crear un gráfico dinámico según el valor elegido de una lista desplegable. Señalamos en esa nota alguna de sus limitaciones: la escalabilidad. Si bien esta palabra no figura en el diccionario de la Real Academia Española, Wikipedia la define como " la capacidad del sistema informático de cambiar su tamaño o configuración para adaptarse a las circunstancias cambiantes”.

Si queremos usar este tipo de reporte a lo largo del tiempo, agregando datos, tenemos que crear un modelo dinámico.
Excel permite hacer esto con facilidad, pero para lograrlo tenemos que organizar nuestro modelo en una forma distinta. El principio básico es separar los datos de los cálculos y de la presentación del reporte (en nuestro caso, el gráfico y la matriz de ventas)

Nuestra base de datos está en la hoja “BD”. El rango de los datos está definido como tabla. Todos los objetos o fórmulas que se refieren a la tabla se adaptan automáticamente a los cambios en los datos de ésta. Esto nos libera de la necesidad de crear rangos dinámicos con DESREF o INDICE.

En la hoja “cálculos” creamos nuestro “motor”. Este consiste en una tabla dinámica que resume los datos de la base de datos

La hoja reporte resume los datos en una tabla que nos servirá también para crear el gráfico dinámico

En la celda C3 ponemos una lista desplegable con los nombres de los vendedores; en la celda C4 una lista desplegable con los años disponibles. Los valores de estas listas están definidos con nombres que se refieren a rangos en la hoja “auxiliar”.

Para poner los datos de la tabla en forma dinámica usamos la función IMPORTARDATOSDINAMICOS,

Para crear la función con facilidad, definimos en Opciones de la tabla dinámica la opción “Generar GetPivotData”

Este video muestra el funcionamiento del modelo



Un último toque. Las tablas dinámicas no se actualizan automáticamente. En esta nota muestro una técnica para lograr la actualización automática de tablas dinámicas.

El archivo con el modelo se puede descargar aquí.

Gráficos dinámicos según valor de lista desplegable

Los más memoriosos lectores de este blog recordarán seguramente aquella nota que describía la técnica para crear un gráfico interactivo según el valor de la celda activa (la nota completa aparece en mi blog sobre gráficos, actualmente inactivo). También recordarán que esa técnica no funciona en las versiones posteriores a Excel 2003.

En esta nota mostraré una técnica que funciona con todas las versiones de Excel. El modelo es distinto y se basa en los valores de una lista desplegable. En esta nota mostraré un modelo sencillo y señalaremos sus limitaciones. En las próximas notas veremos otras soluciones que superaran esas limitaciones.

Y yendo al grano, supongamos esta matriz que muestra las ventas por trimestres de los vendedores de una firma

Queremos crear un modelo que permita representar en gráfico las ventas por vendedor, eligiéndolos de una lista desplegable. Las técnicas para lograr esto ya han sido expuestas en este blog en el pasado, por lo que haremos una explicación sucinta.

Lo que queremos lograr es esto:



Lo primero es crear un nombre que se refiera al rango que contiene los nombres de los vendedores. Lo hacemos fácilmente usando la opción “crear desde la selección” del grupo “nombres definidos” en el menú “Fórmulas”

Creamos la lista desplegable en la celda C5 y definimos el nombre “grfTitulo” que se refiere a esta celda. Para crearlo usamos el cuadro de nombres (seleccionamos al celda, escribimos el nombre en el cuadro y apretamos Enter)

Ahora definimos un nombre para cada uno de los rangos que contienen las ventas de los distintos vendedores. Esto también lo haremos usando la opción “crear desde la selección”

Ahora podemos usar los nombres de los vendedores para definir los rangos de los valores que queremos ver en el gráfico. Pero aquí se nos presenta un problema. Dado que los espacios no están permitidos en los nombres definidos, Excel crea los nombres poniendo un “_” (underline) entre el nombre y el apellido. El valor que obtenemos de la lista desplegable no contiene el guión. La solución es transformar el valor obtenido de la lista desplegable agregándole el guión. Esto la hacemos con la función SUSTITUIR en una celda oculta (en este ejemplo en la celda A5)

El próximo paso es crear un gráfico, de líneas en nuestro caso, usando la primer fila de la tabla

Para que nuestro gráfico sea dinámico creamos este nombre definido

grfSeriesX =INDIRECTO(reporte!$A$5)

La función INDIRECTO interpreta el texto en la celda A5 y lo convierte en el rango que hemos definido previamente.

Ahora reemplazamos los rangos en la función SERIES del gráfico de la siguiente manera

Para ver los nombres definidos apretamos F3.

Nuestro modelo funciona de la siguiente manera:

• Elegimos un valor de la lista deplegable
• El valor es transformado en la celda A5
• La función INDIRECTO en el nombre grfSeriesX lo transforma en el rango del vendedor elegido
• El nombre grfTitulo pone el rango que contiene el nombre del vendedor de manera que aparezca en el título del gráfico.

Este modelo tiene varias limitaciones; la más grave es que si agregamos trimestres y/o vendedores tenemos que modificar los nombres definidos. Podemos, por supuesto, crear nombres dinámicos con DESREF o INDICE como ya hemos mostrado en varias oportunidades en este blog. Pero hay soluciones mejores que mostraremos en las próximas notas.

El archivo con el ejemplo se puede descargar aquí.

Rangos dinámicos y funciones volátiles

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.

Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).

Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula

Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)

Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)

Ahora podemos escribir nuestra fórmula de esta manera:

Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.

La receta clásica es usar la función DESREF como en este ejemplo

DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.

Para crear listas desplegables dependientes usamos la función INDIRECTO.

Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?

No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.

Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.

Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.

Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:

1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.

2 – Usar una combinación de INDICE y CONTARA:

=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))

Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.

Rangos dinámicos con la función INDIRECTO de Excel.

Excel permite construir rangos dinámicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinámicos son aquellos cuya referencia (dirección) se expande o contrae con los cambios en el número de miembros del rango. Estos rangos se definen con fórmulas, por lo general con la función DESREF. También hemos visto que podemos referirnos dinámicamente a un rango usando su nombre como argumento de la función INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinámicos: INDIRECTO no acepta fórmulas como argumentos, sólo texto.
En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.




Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red

Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13

La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas

La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5

Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”

Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia

Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).

Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen

Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar

Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte

Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)

Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir

Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente

El archivo del ejemplo se puede descargar aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Rangos dinámicos con Listas

Una tarea frecuente en Excel es crear rangos dinámicos. La técnica más difundida es crear un nombre (Insertar-Nombres-Definir) con una fórmula que combine DESREF y CONTARA.

Una técnica alternativa más sencilla es usar Listas (Excel 2003) o Tablas (Excel 2007). Esta funcionalidad es muy útil y permite simplificar nuestros modelos en Excel.

En una nota anterior mostramos como crear con facilidad un gráfico dinámico usando Listas. En esta nota mostraremos cómo crear un modelo dinámico.

Como ejemplo construiremos un modelo para manejar el inventario de un almacén/depósito. En un cuaderno Excel creamos dos hojas: “movimientos” y “saldos”. En la primera anotamos los movimientos de los productos en el almacén (entradas – salidas); en mostramos los saldos actualizados de los productos.





En la hoja “movimientos” tenemos ahora un cuadro de datos en el rango A1:D31. Para transformar este rango en Lista, usamos el menú Datos-Lista (o Ctrl+Q)






Al apretar Aceptar veremos que Excel selecciona todo el rango, activa Autofiltro y en la primer fila libre aparece un asterisco azul. A partir de este momento, cada vez que agreguemos datos a la lista, ésta se expandirá automáticamente.

En la celda A1 de la hoja “saldos” combinamos texto y funciones para crear un título dinámico


="Saldos a la fecha "&TEXTO(MAX(movimientos!C2:C31),"dd/mm/yyyy")

Como pueden ver usamos una referencia estática al rango de las fechas en la hoja “movimientos”.
Para calcular los saldos actualizados usamos la fórmula

=SUMAR.SI(movimientos!$A$2:$A$31,saldos!A4,movimientos!$D$2:$D$31)


También aquí usamos rangos “normales”.

Ahora agregamos los movimientos del día 08/01/2009



Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!



En Excel 2007, el mecanismo es similar, pero la funcionalidad Lista ha pasado a llamarse Tabla. Para convertir un rango en Tabla usamos el icono Tabla en la pestaña Insertar



Tanto en Excel 2003 como en Excel 2007, la forma más cómoda y eficiente de agregar datos en la lista/tabla, es usando Tab.



Technorati Tags: MS Excel