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

Análisis de encuestas con Formato Condicional

Las mejoras e innovaciones introducidas  han convertido a Formato Condicional en el nuevo Excel (2007/2010) en una aun más excelente herramienta para analizar datos. En esta nota mostraré como usar Formato Condicional para resumir en forma eficiente y sencilla los resultados de una encuesta.

Hace unas semanas recibí una consulta sobre como resumir concisamente una encuesta sobre el nivel de servicio de ciertos departamentos de una empresa. La encuesta fue montada usando la herramienta Formularios de la aplicación Documentos de Google.

Google va acumulando los resultados en una hoja de cálculo que puede descargarse a Excel. Los encuestados debían calificar en una escala de 0 (pésimo) a 5 (excelente) el funcionamiento de ciertos departamentos de servicios de la empresa.

Veremos como podemos crear una “termo-mapa” para mostrar en forma concisa y práctica los resultados de la encuesta.

Empezamos por convertir la matriz de los resultados en una tabla (Insertar-Tabla)

El próximo paso es crear una tabla dinámica, que ubicamos en una nueva hoja. Personalmente no me agrada el diseño por defecto de las tablas dinámicas en el nuevo Excel, por lo que uso Opciones de tablas-Mostrar para definir el diseño clásico de tabla dinámica que también permite arrastrar los campos a la cuadrícula

Arrastramos le campo de departamentos al área de las filas y cada uno de los campos de las preguntas al área de los valores. Resumimos los valores con la función Promedio y ajustamos el formato de los números.

Quitamos “Promedio de” en los encabezados de la columna para hacerlos más legibles.

Seleccionamos el área de valores y aplicamos Formato Condicional –Escala de Color

El resultado habla por si mismo.

Cuanto más verde es el fondo, mejor la calificación y, por lo contrario, cuando más rojo peor la calificación. Claramente podemos ver que el mejor departamento en la calificación general es Recursos Humanos. El nivel de servicio del Taller es claramente deplorable!