Filtrado de fechas en Excel con Autofiltro o Filtro Avanzado

Supongamos que queremos filtrar una lista de fechas de manera de dejar visibles sólo las fechas que caen en miércoles. Podemos hacer esto con Autofiltro o con Filtro Avanzado, pero cuál sería el criterio?
Si usamos Autofiltro necesitamos crear una columna auxiliar que nos dé el día de la semana para cada una de las fechas del rango. Luego filtramos de acuerdo a esta columna auxiliar.



¿Cómo calculamos el día de la semana para cada fecha? Dos posibilidades, una sencilla y otra complicada:

1 - La complicada, con funciones. Con la función DIASEM, por ejemplo

=DIASEM(A2,2) da 3.

Para transformar el resultado 3 en "miércoles" usamos la función ELEGIR de esta manera

=ELEGIR(DIASEM(A2,2),"lunes","martes","miércoles","jueves","viernes","sábado","domingo")

O con la función INDICE:
=INDICE(semana,DIASEM(A2,2)),

donde "semana" es un nombre que contiene un rango con los días de la semana o directamente los días de la semana.

2 - La posibilidad sencilla. Creamos en la columna auxiliar una referencia a la celda con la fecha (en B2 ponemos =A2) y cambiando el formato de B con el formato personalizado "dddd".

Si queremos usar Filtro Avanzado, tenemos que usar una fórmula lógica, es decir, que dé como resultado VERDADERO o FALSO. Agregamos algunas filas en blanco para poner las filas de criterios y en la celda A2 ponemos esta fórmula

=DIASEM(A5,2)=3



Luego usamos el menú de Filtro avanzado






La ventaja de usar Filtro Avanzado es que nos permite copiar los resultados a otro rango de la hoja o usar más de dos criterios para filtrar la lista.

Ahora veamos otros casos que se presentan.

Para dejar visibles sólo fechas que sean el primer día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)

Si la fecha en A2 coincide con el primer día del mes el resultado será VERDADERO. En caso contrario, FALSO. Luego filtramos la lista usando como criterio VERDADERO en la columna auxiliar.



Si queremos usar Filtro Avanzado, usamos la misma fórmula como criterio






Para filtrar las fechas que sean el último día del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)

Para filtrar el primer día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2),1)+ELEGIR(DIASEM(FECHA(AÑO(A2),MES(A2),1),2),0,0,0,0,0,2,21

La primer parte de esta fórmula calcula la fecha del primer día del mes. En la segunda parte de la fórmula DIASEM calcula el número de día de la semana de esta fecha. Este resultado es usado como parámetro en la función ELEGIR, que agrega 0 (cero) si el día de semana cae entre lunes y viernes, suma 2 si el día de semana del primer día del mes cae un sábado o 1 si es domingo.

Para filtrar el último día hábil del mes usamos la fórmula

=A2=FECHA(AÑO(A2),MES(A2)+1,0)-(MAX(0,DIASEM(FECHA(AÑO(A2),MES(A2)+1,0),2)-5))

Como en los casos anteriores, si queremos usar Autofiltro usamos la fórmula para construir una columna auxiliar. Si queremos usar Filtro Avanzado usamos la fórmula en la celda de criterio.

Technorati Tags: MS Excel

Gráfico de columnas con banda sombreada.

La misma lectora que me consultaba cómo poner un fondo en un gráfico de columnas, me presenta un nuevo desafío: cómo poner una banda sombreada en un gráfico de columnas.
En mi blog sobre gráficos y presentación de datos, que seguirá inactivo por ahora, ya había publicado una nota sobre cómo colorear un área entre dos líneas de un gráfico.
Aquí usaremos esa técnica, pero con algunas adaptaciones. Empecemos por plantear el problema. Tenemos esta tabla de velocidad de lectura de cuatro alumnos



Con la cual hemos creado este gráfico



Queremos crear este gráfico, donde una banda de colar señala la zona comprendida entre un límite inferior y uno superior



Los pasos a seguir son los siguientes:

1 - Agregamos una fila antes de la primer fila de datos e incluimos una fila en blanco después de la última fila de la tabla; en nuestro ejemplo la fila 2 y la fila 7



2 - Modificamos la serie de del gráfico para que incluya las filas que acabamos de agregar



Ahora el gráfico se ve así



3 - Agregamos dos columnas, una "limite inferior" y la otra "límite superior". Estas columnas contienen los puntos de dos series que agregaremos al gráfico para crear la banda



4 - Agregamos las series "límite inferior" y "límite superior" al gráfico en el
formulario de Datos de origen, asegurándonos que la serie "limite superior" sea la segunda y "límite inferior la tercera" (el orden de las series es importante!)



No asustarse del resultado y seguir adelante!

5 - Seleccionamos la serie "limite superior" y en el menú Formato serie de datos-Eje, la relacionamos al eje secundario



Esto hará que aparezca un eje Y secundario. Seleccionamos la serie "límite inferior" y la relacionamos al eje secundario.

Un paso importante es asegurarnos que ambos ejes Y tengan exactamente la misma escala.

6 - Seleccionamos la serie "limite superior" y cambiamos el tipo de gráfico a Área. Luego hacemos lo mismo con la serie "límite inferior". Nuestro gráfico se ve ahora así



7 - Elegimos un color más "blando" para la serie "límite superior" y para "limite inferior elegimos el color del fondo del gráfico. A esta altura del partido el gráfico debe verse así



8 - Seleccionamos el eje de las X y abrimos el menú de Formato de ejes. En la pestaña Escala quitamos la marca de la opción "Eje de valores Y cruza entre categorías"



Finalmente llegamos a nuestra meta:



Este gráfico es totalmente dinámico. Si cambiamos los valores de las series límite superior e inferior, la banda sombreada se "moverá" de acuerdo a los nuevos valores.

Technorati Tags: MS Excel

Poner fondos en gráficos de columnas Excel

Supongamos, como me consultaba una lectora, que tenemos esta tabla que mide la velocidad de lectura de los alumnos de un curso



Hemos puesto un fondo para resaltar los datos del rango entre 45 palabras a 100 palabras.

A partir de estos datos creamos este gráfico de columnas



¿Cómo hacemos para poner un fondo, tal como en la tabla, en el gráfico? Es decir, queremos obtener este gráfico



La técnica consiste en crear una serie auxiliar para definir el fondo. Éste esta formado por una serie de columnas, donde hemos cambiado la definición de ancho del rango a 0 y hemos cancelado los bordes, para crear la ilusión de una única columna.
Veamos los pasos:

1 - creamos una columna auxiliar en el rango C2:C22



En las celdas paralelas a los valores del rango que queremos resaltar ponemos el valor 70 (el valor máximo del eje Y del gráfico). En las celdas fuera del rango ponemos 0 (o dejamos en blanco).

2 - Agregamos la nueva serie al gráfico. Seleccionamos la serie y la copiamos (Ctrl+C), seleccionamos el gráfico y abrimos el menú Edición-Pegado Especial y en el formulario seleccionamos "nueva serie" y "columnas"



3 - Seleccionamos la primer serie (no la que acabamos de agregar). Abrimos el menú de Formato de Series de Datos y en la pestaña Eje la relacionamos al eje secundario. Nos aseguramos que ambos ejes tengan la misma escala y formato



4 - Seleccionamos la serie que acabamos de agregar y abrimos el menú Formato Serie de Datos. En Tramos ponemos Bordes a Ninguno y elegimos un color claro



En la pestaña Opciones definimos Ancho de rango como 0



En la pestaña Eje nos aseguramos que la serie pertenece al eje principal. A pesar que el sentido común parece decir lo contrario, en los gráficos de Excel las series del eje secundario son representadas en el frente y las del primario en el fondo.

Una técnica similar es crear una columna auxiliar donde todos los valores equivalen al valor superior del eje de la Y del gráfico original



Repetimos los pasos anteriores: copiamos la nueva serie y relacionamos la serie original al eje secundario. Cambiamos las definiciones de la nueva serie, como hicimos en el paso 4. El resultado será



A pesar que vemos sólo una única columna en el fondo, en realidad la serie incluye 21 columnas. Seleccionamos la columna correspondiente al valor 45 (el primero que queremos resaltar en la serie) y le cambiamos el color. Para seleccionamos un punto de la serie (columna en el gráfico) hacemos un clic para seleccionar la serie y un segundo para seleccionar el punto/columna



A continuación seleccionamos las otras columnas y apretamos F4 para copiar el nuevo formato. El resultado será



La primer técnica nos permite refinar nuestro modelo para hacer aún más dinámico. En las celdas C3 y D3 ponemos los límites de los valores del fondo



En el rango E3:E22 introducimos esta fórmula (la fórmula original era matricial y fue reemplazada por esta más sencilla)

=(A3>=$C$3)*(A3<=$D$3)*MAX($B$3:$B$23)



Agregamos la serie al gráfico y seguimos todos los pasos señalados en la primer técnica. Luego movemos el gráfico de manera que oculte la columna. De esta manera podemos cambiar los parámetros en las celda C3 y D3 y veremos los cambios en el gráfico en forma instantánea

Technorati Tags: MS Excel

Actualización simultánea de tablas dinámicas en Excel.

Después de realizar cambios en la base de datos de una tabla dinámica tenemos que apretar el botón de actualización para que los cambios se reflejen en la tabla



Si tenemos más de una tabla dinámica en el cuaderno, tenemos que actualizar cada una por separado. Además de la molestia de tener que hacer varios clics existe también el riesgo de olvidar de actualizar alguna de las tablas.
Aparentemente Excel no tiene ningún método para actualizar todas las tablas simultáneamente, pero podemos usar el botón Actualizar Todo, que por defecto aparece en la barra de herramientas de Datos Externos, para esta tarea. Para agregar el botón a la barra de herramientas Tabla Dinámica hacemos lo siguiente:

1 - pulsamos la flecha de Opciones de la barra de Tablas Dinámicas para abrir la posibilidad Agregar o quitar botones



2 - Elegimos Tabla Dinámica y señalamos el botón Actualizar todo



Al apretar el botón se actualizarán todas las tablas dinámicas del cuaderno. Hay que tomar en cuenta que si hay rangos externos en el cuaderno, éstos se actualizarán.

Technorati Tags: MS Excel

Cuadro de texto flotante en Excel - segunda nota

En la nota anterior sobre cómo crear cuadros de texto flotantes en Excel mostramos una técnica para simular el efecto de un objeto flotante.
La técnica consiste en crear una cuadro de texto y ligarlo a una celda, o mejor aún usar la cámara fotográfica de Excel para crear una imagen dinámica de un rango.
El problema con la técnica que describimos en esa nota es que el objeto se desplaza sólo verticalmente. Es decir que si creamos el objeto con la cámara y lo ubicamos en el área de la columna D, cuando nos movamos hacia la derecha hasta la columna AB, por ejemplo, no veremos el objeto ya que éste no se desplaza horizontalmente.

Para lograr que el objeto se desplace también horizontalmente creamos este evento en el módulo de Vba de la hoja correspondiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    With Shapes("Picture 2")
        .Left = Target.Offset(0, 1).Left
        .Top = Target.Offset(-1, 0).Top
    End With
    On Error GoTo 0
End Sub


Usamos la propiedad OFFSET para colocar el objeto a la izquierda y arriba de la celda activa. Así por ejemplo, si tenemos una tabla de cotizaciones en el rango A1:B4, después de crear el objeto con la cámara y poner el código en el módulo de la hoja, al seleccionar la celda C6 el cuadro se desplaza de esta manera



Si seleccionamos la celda N3, el cuadro se moverá para sobreponerse al rango O2:P5.



Usamos "On Error Resume Next" para evitar que el código produzca un error y se detenga en caso de seleccionar alguna celda en la fila 1.

Technorati Tags: MS Excel

Cálculo de interés con Excel - versión mejorada

En la nota anterior sobre el tema de cálculo de intereses mostramos cómo construir una fórmula para esta tarea. El modelo que mostramos en la nota tiene un serio inconveniente: sólo sirve si las fechas del comienzo y fin del período caen en el mismo año. Por ejemplo, si la fecha de vencimiento del pago cae el 15/02/2008 y nuestro deudor pagó efectivamente la cuenta el 22/08/2009, la tabla de la nota anterior nos dará un resultado erróneo



Dado que varios lectores me han pedido que les envíe el archivo, y supongo que querrán usarlo para calcular intereses de morosos, mostraremos en esta nota cómo construir un real modelo de cálculo intereses.

Empezamos por diseñar nuestro modelo. Queremos que:

1 - Ingresado el monto adeudado, la fecha de vencimiento y la fecha de pago real, el modelo calcule los intereses por mora.

2- El modelo debe mostrar el detalle del cálculo por mes (estamos suponiendo que las tasas de interés cambian solo por meses).

3- El detalle del cálculo mostrar sólo los meses relevantes al cálculo. Es decir, sólo los meses del período del cálculo.

4 - Las tasas de interés mensuales deben ser definidas en forma dinámica, es decir, no serán ingresadas por el usuario sino definidas automáticamente por el modelo de acuerdo al período relevante (mes/año).

Nuestro primer paso será crear el "esqueleto" de nuestro modelo



Partiendo de la base que el número máximo de meses que puede haber en un cálculo sea 60 (5 años), el rango de la tabla del detalle será A10:A69 (sin incluir los encabezamientos)

Nuestro segundo paso será crear las fórmulas necesarias en las celdas B4, B5 y en la tabla del detalle del cálculo.

La fórmula de B4 es sencilla: =SUMA(intereses), donde "intereses" es el nombre del rango D10:D69.

La fórmula de B5 es obviamente =B3+B4.

Ahora nos ocuparemos de la tabla de cálculo de intereses. En A10 ponemos

=FECHA(AÑO(B1),MES(B1),1)

Esta fórmula determina cuál es el primer día del mes de la fecha de vencimiento. Usamos formato personalizado para que se vea en pantalla como Mes-Año. A partir de esta celda calculamos los 59 meses subsiguientes, con la fórmula

=EDATE(A10,1)

Para usar la función EDATE (o FECHA:MES, según las definiciones del sistema) tiene que estar instalado el complemento Analysis ToolPak. FECHA.MES calcula el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada (argumento fecha_inicial). Copiamos esta fórmula en todo el rango A11:A69.

Para calcular la cantidad de días en cada mes de acuerdo a las fechas de inicio y fin del período de cálculo de intereses no podemos usar la fórmula que desarrollamos en la nota anterior. En su lugar usaremos esta otra fórmula desarrollada a partir de una sugerida por un compañero de trabajo de uno de mis lectores (y como no sé su nombre no puedo darle el crédito)

=SI((MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1))+1>0,MIN($B$2,EOMONTH(A10,0))-MAX(EOMONTH(A10,-1)+1,$B$1)+1,0)

Esta fórmula usa las funciones MIN y MAX para determinar si las fechas de vencimiento ($B$1) y la de pago ($B$2) caen dentro del mes de la fila. En caso afirmativo calcula cuantos días caen dentro del mes considerado, En caso negativo el resultado es 0.
Para evitar el uso de columnas auxiliares usamos la función FIN.MES (EOMONTH) para determinar el primer y el último día del mes de la fila en la tabla en forma dinámica.

Ahora tenemos que ocuparnos de la columna C en la tabla del detalle. Primero creamos una hoja adiciones en la cual ponemos una lista de las tasa de interés por mes y año



Esta tabla nos sirve como argumento en la fórmula que ponemos en las celdas de la columna C de la tabla

=BUSCARV(A10,tabla_de_interes,2,0)

La fórmula en la columna D de la tabla es obvia: =$B$3*(C10/365*B10)

Antes de seguir adelante probamos nuestras fórmulas:



Vemos con satisfacción que las fórmulas funcionan correctamente. El primer mes en la tabla del detalle (A10) es diciembre de 2007, la cantidad de días de cada mes ha sido calculada correctamente y la tasa de interés corresponde a los valores de la tabla de tasas.

Sólo nos queda por resolver cómo ocultar las filas no relevantes al cálculo. Esto lo hacemos con facilidad usando Formato Condicional. De hecho, queremos ocultar una fila en la tabla si en la cantidad de días en la fila es 0. Seleccionamos todo el rango de la tabla (A10:D69) y aplicamos formato condicional con esta fórmula



con estas definiciones para fuente



y estas para los bordes



Ahora nuestro modelo cumple todos los requisitos.




Technorati Tags: MS Excel