Formato condicional para resaltar máximos y mínimos según criterios

Hace casi un mes que no publico nada en el blog. No por pereza o desidia sino por una inesperada carga de trabajo. Cuento con la comprensión de los lectores que han quedado sin respuesta a sus consultas.

Uno de mis lectores me consulta cómo usar formato condicional para resaltar mínimos (o máximos). Supongamos una lista de precios donde varios productos aparecen con precios distintos

Cómo extraer los máximos y mínimos según criterios (producto, en nuestro caso), ya hemos visto en la nota que publiqué en abril de año pasado. En nuestro ejemplo la solución sería

Las fórmulas para calcular los máximos y mínimos son matriciales (se introducen apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter):

• para los máximos: =MAX(($A$2:$A$13=D2)*$B$2:$B$13)

• para los mínimos: =MIN(SI(($A$2:$A$13=D2),$B$2:$B$13))

Podemos usar las mismas fórmulas para aplicar el formato condicional. Por ejemplo, para resaltar los mínimos en la lista de precios

En formato condicional no hace falta introducir la fórmula en forma matricial.

Calcular temporadas altas y bajas con Excel

La consulta que generó esta nota es:

[…si introduzco una fecha de entrada y una de salida a un hotel que devuelva un mensaje como temporada alta, media o baja.]

Hay varias formas de hacer el cálculo y si bien ninguna es demasiado complicada, la explicación excede el marco de un comentario (y el lector se merece una respuesta).

El asunto de las temporadas de turismo (alta, media o baja) no parece seguir ninguna regla establecida fuera de una muy elemental: cuando hay mucha demanda, la temporada es alta; cuando hay poca demanda es baja y en los demás casos es media. Así que las fechas de las temporadas dependen no sólo de las estaciones del año, sino del la ubicación geográfica y de la rama de la industria turística, por lo menos.

Dicho esto veamos cómo resolver la cuestión con Excel. Empecemos por el caso sencillo en que el año está dividido en dos temporadas: alta y baja. Podemos expresar las temporadas en esta tabla

Como vemos, las fechas en la matriz son calculadas con la fórmula

=FECHANUMERO("01/07/"&$C$2)

El número de año en la celda C2 lo calculamos con la fórmula =AÑO(B10)

Finalmente, en la celda C10 usamos la fórmula

=SI(Y(B10>=C5;B10<=D5);"Alta";"Baja")

Dado que nuestro modelo es simplista, toda fecha que no caiga dentro del intervalo 01-Jul-2012 a 30-Sep-2012 (la temporada alta), es temporada baja.

Pero, por supuesto, la realidad es más complicada y el año puede dividirse en varias temporadas altas, bajas y medias. En ese caso debemos usar otro modelo.

Nuestra primera aproximación es un modelo sencillo donde usamos una tabla de 366 días (para cubrir también los años bisiestos) y a cada día le asignamos la temporada correspondiente

En la celda A4 usamos nuevamente la función FECHANUMERO y en la subsiguiente =A4+1. Al copiar esta última en las próximas 364 filas, obtenemos el año. Luego ponemos en la columna B el nombre de la temporada.

En la celda F4 usamos =BUSCARV(E4;$A$4:$B$369;2;0)

Como en el caso anterior usamos =AÑO(E4) para extraer el año de la fecha investigada y el resultado lo usamos para armar la tabla anual.


Una solución más elegante es la siguiente

En el rango A5:A11 ponemos un texto con el día y mes de comienzo de la temporada, como 01/01/

En el rango B5:B11 usamos la fórmula =FECHANUMERO(A5&$B$2), que nos asegura obtenerla fecha con el año correspondiente.

En B4 volvemos a usar =AÑO(F4).

Finalmente, en G4 usamos =INDICE(C5:C11;COINCIDIR(F4;B5:B11))

Como usamos COINCIDIR con el argumento "Tipo_de_coincidencia" omitido, es importante que el rango B5:B11 esté ordenado en forma ascendente.

El cuaderno con los ejemplos puede descargarse aquí.

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