¿De quién es el próximo cumpleaños?

Ya he comentado que cálculos de fechas y horas es uno de los temas más consultados por los lectores de este blog. No es casualidad. La forma en que Excel trata las fechas y las horas no es intuitiva.
Un lector me pedía ayuda con un modelo para encontrar el nombre de la persona cuyo cumpleaños fuera el más cercano a la fecha corriente.

Consideremos esta lista de nombres con sus respectivas fechas de nacimiento



Si la fecha corriente es el 7 de octubre, la persona cuyo cumpleaños es el más cercano es, obviamente, María. Si le fecha fuera el 10 de octubre, el próximo cumpleañero sería Pedro.

Hacer este cálculo con Excel parece sencillo, diferencia en días entre dos fechas, pero tiene sus bemoles.
Excel representa cada fecha con un número entero que indica la cantidad de días transcurridos desde el 1ro. de enero de 1900. Por lo tanto, no podemos usar SIFECHA o usar una resta para calcular el intervalo entre la fecha actual y la fecha del cumpleaños de los miembros de la lista.
Tampoco podemos representar fechas en Excel sin el año, por lo que explico más arriba. Así que nuestra aproximación al problema será convertir todas las fechas de nacimiento a fechas del año corriente. Esto lo hacemos con la función FECHA creando una columna auxiliar (Aux 1)



Ahora creamos la columna auxiliar Aux2 donde calculamos la diferencia con la fecha de referencia, que hemos puesto en la celda B9



En la columna Aux2 vemos claramente que María es la cumpleañera, ya que la diferencia es 0 (el cumpleaños coincide con la fecha de referencia).
Como no siempre hay alguien que cumpleaños, necesitamos una forma de encontrar quien es el más cercano. A esta altura de los acontecimientos estaríamos tentados a usar la función MIN() lo cual es una buena idea pero con un pequeño problema. Pongamos como fecha de referencia el 10 de octubre



Si observamos con atención veremos que el próximo cumpleaños es el de Pedro. Pero si usamos el resultado de MIN(D2:D6) para encontrar el cumpleañero, el resultado será José ya que -252 es el menor de los números en el rango. Para corregir esto tenemos que sumar 365 (un año, expresado en días) a los resultados de Aux2 cuya fecha de nacimiento ya ha pasado.
En lugar de esto, cambiaremos de aproximación. Lo que haremos es crear una columna auxiliar donde calculamos la fecha (incluido el año) del próximo cumpleaños. Para esto usamos esta fórmula

=FECHA(2008+(C2<$B$9),MES(B2),DIA(B2))



La expresión lógica (C2<$B$9) calcula si la fecha del cumpleaños es anterior o posterior a la fecha de referencia. En caso de ser posterior (menor que la fecha de referencia) la expresión da VERDADERO que es sumado como 1 al año corriente.

Con esta aproximación, la columna Aux2 es innecesaria. Para obtener el nombre del cumpleañero usamos esta fórmula con INDICE y COINCIDIR

=INDICE(A2:A6,COINCIDIR(MIN(D2:D6),D2:D6,0))



Podemos reducir el número de columnas auxiliares a una reemplazando C2 en la fórmula de Aux3 por la fórmula de Aux1

=FECHA(2008+(FECHA(2008,MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))



Otra corrección necesaria, si queremos usar este modelo también el próximo año, es reemplazar el "2008" en la fórmula por un calculo dinámico del año corriente

=FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2),DIA(B2))<$B$9),MES(B2),DIA(B2))

Finalmente, si queremos prescindir totalmente de columnas auxiliares, podemos usar esta fórmula matricial (matricial = introducir pulsando simultáneamente Ctrl+Enter+Mayúsculas)

={INDICE(A2:A6,COINCIDIR(MIN(FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6))),FECHA(AÑO(HOY())+(FECHA(AÑO(HOY()),MES(B2:B6),DIA(B2:B6))<$B$9),MES(B2:B6),DIA(B2:B6)),0))}

Technorati Tags: MS Excel

Corrección automática de rangos en fórmulas

Supongamos esta sencilla tabla que muestra las ventas de una tienda por departamento



Vemos que faltan los datos del Departamento 3. Al insertar una línea entre los departamentos 2 y 4, el rango de la fórmula =SUMA(B2:B5) se corrige automáticamente



Veamos qué pasa si queremos agregar el Departamento 6, inmediatamente debajo del Departamento 5. Seleccionamos la fila 7 e insertamos una nueva fila. En este caso, Excel no corrige el rango de la fórmula



En ciertos casos, al agregar los datos del nuevo departamento, éstos no serán incluidos en la suma! Si tenemos habilitada la opción Extender formato de rangos y fórmulas en el menú Opciones y la tabla tiene más de cinco líneas, Excel modificará el rango de la fórmula automáticamente. Pero si pegamos los datos que hemos copiado de otra hoja o fuente, o no tenemos habilitada la opción, Excel no modificará el rango de la fórmula.

Cuando creamos cuadernos que serán usados por otros, no podemos estar seguros que al agregar líneas o hacer otras modificaciones los usuarios presten atención a este potencial problema.

La solución es usar la función DESREF para crear un rango que se adapte automáticamente cada vez que se agreguen líneas en cualquier ubicación del rango de la fórmula.

En nuestro caso la fórmula será

=SUMA(B2:DESREF(B8,-1,0))



Como ven, el primer argumento de la función, el "ancla", se refiera a la celda que contiene la fórmula. El segundo indica una referencia a una fila hacia arriba.

El mismo caso puede darse cuando insertamos una columna en un rango donde sumamos a los largo de una fila. Por ejemplo, si transponemos nuestra tabla, la fórmula será

=SUMA(B2:DESREF(G2,0,-1))

Technorati Tags: MS Excel

Cuadro de texto flotante en Excel

En ésta y en las siguientes notas me ocuparé de responder a consultas que recibo con cierta frecuencia de mis lectores. Una de ellas es cómo crear un cuadro de texto flotante en una hoja de Excel.
La idea es crear un cuadro de texto flotante que se actualice de acuerdo al valor presente en una celda o en un rango de ellas.
Empecemos por el caso más sencillo: un cuadro de texto flotante ligado a una única celda.

Para ligar el valor de la celda A1 a un cuadro de texto empezamos por crear el cuadro de texto con el icono correspondiente de la barra de dibujo. Luego, con el cuadro de texto seleccionado, introducimos manualmente la referencia a la celda A1 en la barra de fórmulas



A partir de ese momento todo valor en la celda A1 parecerá automáticamente en el cuadro de texto.

Para convertirlo en flotante programamos un evento, de manera que el cuadro siempre aparezca en la vecindad de la celda activa en la hoja. En el módulo de la hoja correspondiente ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Shapes("Text Box 1").Top = Target.Top
End Sub


Este método no crea un cuadro realmente flotante, ya que si navegamos en la hoja usando la barra de navegación, la rueda del Mouse o cualquier otro método que no implique seleccionar una celda en la zona visible de la hoja, el cuadro desaparece junto con la celda activa. Pero basta con seleccionar una celda en la zona visible para que el cuadro vuelva a aparecer.

Para complicar un poco las cosas supongamos que queremos ligar no una celda sino un rango de celdas al cuadro de texto.
Posiblemente estén tentados a emplear el método descrito más arriba poniendo a la referencia al rango. Por ejemplo, supongamos que tenemos una tabla en el rango A1:A6 con los datos de ventas de cinco años



Al apretar Enter veremos que sólo el contenido de A1 (o de la celda superior izquierda del rango) aparece en el cuadro de texto.
La solución es usar la herramienta llamada cámara fotográfica de Excel. Lo primero que hacemos es poner el icono de la cámara fotográfica en alguna de las barras de herramientas, tal como lo explico en la nota del enlace.
Seleccionamos el rango A1:A6 y apretamos el icono de la cámara



El marcador del Mouse se tomará la forma de una cruz pequeña. Señalamos algún área de la hoja para pegar la imagen generada por la cámara



En la barra de fórmulas podemos ver que Excel ha creado una referencia absoluta al rango. Cada vez que cambiemos algún valor en el rango, éste se reflejará automáticamente en la imagen.

Ahora tenemos que adaptar el código del evento para que se refiera a este objeto. Al seleccionar la imagen el nombre del objeto aparece en el cuadro de nombres



Pero estamos usando la versión en castellano de Excel y Visual Basic sólo "habla" ingles. Así que tenemos que a averiguar el nombre en inglés. Una forma de hacerlo es grabar una macro en la cual seleccionamos el objeto (cuidándonos que antes de empezar a grabar la imagen no esté seleccionada). En nuestro caso el nombre es, obviamente, "Picture 2" (=Imagen 2 que aparece en el cuadro de nombres).
El código del evento Worksheet_SelectionChange será ahora

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Shapes("Picture 2").Top = Target.Top
End Sub


Como en el caso del cuadro de texto, la imagen aparecerá siempre en la vecindad de la celda activa en la hoja.

Esta técnica puede ser útil cuando queremos que ciertos datos estén permanentemente visibles sin necesidad de movernos en la hoja todo el tiempo.

Actualización: una técnica mejorada para que el objeto se desplace también horizontalmente puede verse en esta nota

Technorati Tags: MS Excel