Cálculo de finalización de procesos con Excel

Supongamos que tenemos una planilla en Excel donde queremos calcular la fecha de finalización de un proceso a partir de la fecha de comienzo y la duración en días. Excel permite hacer esto con facilidad sumando el número de días a la fecha de comienzo

Pero si queremos calcular la fecha de finalización del proceso tomando en cuenta los feriados (el 16/01/2010 es sábado y el 17/01/2010 domingo), tendremos que usar la función DIA.LAB

El resultado aparece en rojo para resaltar el hecho que es incorrecto. El resultado correcto es



20/01/2010 08:00

Esto se debe a que DIA.LAB calcula días enteros, sin tomar en cuenta las horas, minutos y segundos.

Para calcular el resultado correcto usamos esta fórmula



=DIA.LAB(B5;C5)+(B5-ENTERO(B5))

Como ya hemos explicado en el pasado, Excel usa una serie de números para los cálculos de fechas. La parte entera del número representa el día y la parte decimal la fracción del día (horas, minutos y segundos).
En nuestro caso 15/01/2010 08:00 está representado por el número 40193,3333333333 donde 40193 son los días transcurridos desde el 1ro. de enero de 1900 y 0.333333 (es decir, 1/3) es la tercera parte del día, 8 horas (24 x 1/3 = 8)

Lo que hacemos en nuestra fórmula es extraer la parte decimal y agregarla al resultado de DIA.LAB.

Supongamos que la duración del proceso no es un número entero de días sino, por ejemplo, 3 días, 10 horas y 20 minutos. Si ignoramos los feriados podemos esta fórmula

=B9+C9+NSHORA(D9;E9;F9)

Usamos NSHORA para convertir los parámetros de tiempo que ponemos como números enteros en el número serial correspondiente al lapso (10:20:00 es representado por el número 0,430555555555556).

Si nos gusta complicarnos la vida o le tenemos antipatía a la función NSHORA, podemos usar en su lugar la expresión

=B10+C10+(D10*3600+E10*60+F10)/86400

Convertimos las horas y los minutos a segundos y los dividimos por 86400 que es la cantidad de segundos que hay en un día (24 x 60 x 60 = 86400)

En caso que tengamos que tomar en cuenta los feriados, tendremos que usar DIA.LAB y corregir el resultado tal como hicimos en el ejemplo anterior

Usos de Listas (Excel 2003) o Tablas (Excel 2007)

En Excel 2003 Microsoft introdujo una nueva funcionalidad: las listas. En Excel 2007 las listas evolucionaron en funcionalidad y eficiencia y pasaron a llamarse Tablas. Las Listas/Tablas son una de las funcionalidades más subestimadas por los usuarios de Excel.

En la nota anterior sobre rangos dinámicos vimos qué fácil es crearlos usando Listas o Tablas y cómo nos permiten sobreponernos a las limitaciones de la función INDIRECTO para crear listas desplegables dependientes.

En esto nota me extenderé sobre las otras bondades de esta funcionalidad.

Empecemos por la más trivial de las preguntas: ¿qué es una Lista/Tabla? Volvamos al ejemplo de la cadena de tiendas que mostramos en las notas sobre tableros de comandos (dashboards)

Las columnas F y G son índices que nos muestras las compras y la ganancia por cliente que calculamos con una sencilla operación aritmética.

La matriz B2:G6 es una rango rectangular de datos ordenados, pero aún no es una Lista /Tabla en términos de Excel. Para convertir este rango en una Tabla (Excel 2007), hacemos lo siguiente:

1 – seleccionamos alguna de las celdas del rango

2 – en la cinta activamos la pestaña Insertar y elegimos Tabla

3 – Seleccionamos el rango de la tabla y marcamos la opción “La tabla tiene encabezados”

4 – Excel convierte el rango seleccionado en una Tabla y abre la pestaña Diseño de Herramientas de tablas.

Como vimos en la nota anterior, aquí podemos darle un nombre significativo a la tabla

En Excel 2003 el proceso es similar, pero algo diferente



Las Tablas/Listas se diferencian de los rangos normales en, entre otras cosas:

• al crear una Lista/Tabla Excel agrega automáticamente el Autofiltro
• la Lista/Tabla se expande automáticamente al agregar una celda. Todas las referencias ligadas a la Lista/Tablas se adaptan al cambio automáticamente (gráficos, nombres, fórmulas)
• al agregar valores a la Lista/Tabla, todas las fórmulas en columnas con fórmulas son copiadas automáticamente
• si usamos la tecla TAB para navegar, Excel selecciona automáticamente la celda siguiente de izquierda a derecha y de arriba hacia abajo

Todo esto hace que el uso de Listas/Tablas sea muy eficiente para construcción de modelos dinámicos, en especial tableros de comandos.

Veamos algunos ejemplos. Vamos a agregar la sucursal 5 a nuestra base de datos



Primero hemos seleccionado la celda B6; luego nos movemos pulsando la tecla TAB (esto no es necesario en la vida real, pero quería demostrar cómo navegamos la tabla con TAB). Al alcanzar la celda G6 y pulsar TAB, Excel nos lleva automáticamente a la celda B7 y expande la tabla. Vemos que esto es cierto para el formato de las celdas y también para las fórmulas en las celdas F7 y G7.
Como todavía no hemos introducido datos, el resultado de las fórmulas es DIV/0. Al introducir los datos, vemos el error desaparece. Lo mismo sucede si copiamos/pegamos datos



Otra característica importante es la posibilidad de agregar una fila de totales al final de la Tabla. Esta fila se adapta automáticamente a los cambios en las dimensiones de la tabla Para agregar la fila de totales en Excel 2007 vamos a la pestaña de Herramientas de tabla y marcamos la opción Fila de Totales.

En nuestro caso, Excel pone el total sólo en la última columna con datos numéricos

Podemos ver que Excel ha agregado la función SUBTOTALES con la opción 109. Es decir que si filtramos la tabla, el resultado mostrará sólo el total de las filas visibles.

Podemos cambiar esta función por otras pulsando la flecha en el borde izquierda de la celda

Para poner totales en las otras columnas con valores numéricos seleccionamos la última celda de la columna lo que hace aparecer la flecha de opciones de totales 

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.